Skip to main content

​
Database Setup

Sakai requires a relational database for storing system data, user information, and content metadata. This guide covers setup and configuration for supported databases.

​
Supported Databases

Sakai officially supports:
  • MariaDB 10.x (Recommended)
  • MySQL 8.x
  • Oracle 12c and later
  • HSQLDB (Development/testing only)
HSQLDB is suitable only for development and testing. Never use it in production environments.
MariaDB is the recommended database for Sakai due to its performance, reliability, and open-source nature.

​
Installation

1

Install MariaDB Server

On Ubuntu/Debian:
sudo apt-get update
sudo apt-get install mariadb-server mariadb-client
On RHEL/CentOS:
sudo yum install mariadb-server mariadb
sudo systemctl start mariadb
sudo systemctl enable mariadb
2

Secure MariaDB Installation

Run the security script:
sudo mysql_secure_installation
Follow prompts to:
  • Set root password
  • Remove anonymous users
  • Disallow root login remotely
  • Remove test database
3

Configure MariaDB

Edit /etc/mysql/mariadb.conf.d/50-server.cnf (Ubuntu) or /etc/my.cnf (RHEL):
[mysqld]
# Character set configuration
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Case-insensitive table names (required for Sakai)
lower_case_table_names=1

# Performance tuning
max_allowed_packet=64M
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2

# Connection settings
max_connections=200
wait_timeout=600

# Binary logging (for replication/backup)
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
Restart MariaDB:
sudo systemctl restart mariadb
4

Create Sakai Database

Connect to MariaDB:
mysql -u root -p
Create database and user:
-- Create database
CREATE DATABASE sakai DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user and grant privileges
CREATE USER 'sakai'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON sakai.* TO 'sakai'@'localhost';

-- For remote connections (adjust hostname as needed)
CREATE USER 'sakai'@'%' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON sakai.* TO 'sakai'@'%';

FLUSH PRIVILEGES;
5

Verify Database Creation

Confirm the database exists:
SHOW DATABASES;
USE sakai;
SHOW TABLES;
Initially, the tables list will be empty. Sakai creates tables on first startup.

​
MariaDB Configuration in Sakai

Add to sakai.properties: 
# MariaDB Configuration
vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=org.mariadb.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MariaDBDialect
url@javax.sql.BaseDataSource=jdbc:mariadb://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8
validationQuery@javax.sql.BaseDataSource=
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

# Database credentials
username@javax.sql.BaseDataSource=sakai
password@javax.sql.BaseDataSource=STRONG_PASSWORD_HERE

# Auto-create schema on first startup
auto.ddl=true

​
MySQL 8 Setup

MySQL 8.x is also well-supported by Sakai.

​
Installation

1

Install MySQL Server

On Ubuntu/Debian:
sudo apt-get update
sudo apt-get install mysql-server
On RHEL/CentOS:
sudo yum install mysql-server
sudo systemctl start mysqld
sudo systemctl enable mysqld
2

Secure MySQL Installation

sudo mysql_secure_installation
3

Configure MySQL

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
lower_case_table_names=1

max_allowed_packet=64M
innodb_buffer_pool_size=1G
max_connections=200

# MySQL 8 specific
default_authentication_plugin=mysql_native_password
Restart MySQL:
sudo systemctl restart mysql
4

Create Database

CREATE DATABASE sakai DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'sakai'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON sakai.* TO 'sakai'@'localhost';
FLUSH PRIVILEGES;

​
MySQL 8 Configuration in Sakai

# MySQL 8 Configuration
vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
url@javax.sql.BaseDataSource=jdbc:mysql://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8
validationQuery@javax.sql.BaseDataSource=
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

username@javax.sql.BaseDataSource=sakai
password@javax.sql.BaseDataSource=STRONG_PASSWORD_HERE

auto.ddl=true

​
Oracle Database Setup

For enterprise deployments using Oracle.

​
Prerequisites

  • Oracle Database 12c or later
  • Oracle JDBC driver (ojdbc8.jar or later)

​
Database Configuration

1

Create Tablespace

Connect as SYSDBA:
CREATE TABLESPACE sakai_data
  DATAFILE '/u01/app/oracle/oradata/sakai_data01.dbf'
  SIZE 1G
  AUTOEXTEND ON
  NEXT 100M
  MAXSIZE UNLIMITED;

CREATE TEMPORARY TABLESPACE sakai_temp
  TEMPFILE '/u01/app/oracle/oradata/sakai_temp01.dbf'
  SIZE 512M
  AUTOEXTEND ON;
2

Create User

CREATE USER sakai
  IDENTIFIED BY STRONG_PASSWORD_HERE
  DEFAULT TABLESPACE sakai_data
  TEMPORARY TABLESPACE sakai_temp
  QUOTA UNLIMITED ON sakai_data;

GRANT CONNECT, RESOURCE TO sakai;
GRANT CREATE SESSION TO sakai;
GRANT CREATE TABLE TO sakai;
GRANT CREATE SEQUENCE TO sakai;
GRANT CREATE VIEW TO sakai;
GRANT CREATE TRIGGER TO sakai;
3

Install JDBC Driver

Copy Oracle JDBC driver to Tomcat:
cp ojdbc8.jar /opt/tomcat9/lib/

​
Oracle Configuration in Sakai

# Oracle Configuration
vendor@org.sakaiproject.db.api.SqlService=oracle
driverClassName@javax.sql.BaseDataSource=oracle.jdbc.OracleDriver
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
url@javax.sql.BaseDataSource=jdbc:oracle:thin:@oracle.example.edu:1521:ORCL
validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
testOnBorrow@javax.sql.BaseDataSource=false

username@javax.sql.BaseDataSource=sakai
password@javax.sql.BaseDataSource=STRONG_PASSWORD_HERE

auto.ddl=true

​
Docker Database Setup

For development or containerized deployments.

​
MariaDB Docker Container

docker run -d \
  --name sakai-mariadb \
  -p 3306:3306 \
  -e MARIADB_ROOT_PASSWORD=rootpass \
  -e MARIADB_DATABASE=sakai \
  -e MARIADB_USER=sakai \
  -e MARIADB_PASSWORD=sakaipass \
  -v mariadb-data:/var/lib/mysql \
  mariadb:10 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci \
  --lower-case-table-names=1

​
Initialize Database with Script

Create init.sql: 
CREATE DATABASE IF NOT EXISTS sakai DEFAULT CHARACTER SET utf8mb4;
GRANT ALL ON sakai.* TO 'sakai'@'%' IDENTIFIED BY 'sakaipass';
Mount as init script: 
docker run -d \
  --name sakai-mariadb \
  -p 3306:3306 \
  -e MARIADB_ROOT_PASSWORD=rootpass \
  -v ./init.sql:/docker-entrypoint-initdb.d/init.sql \
  -v mariadb-data:/var/lib/mysql \
  mariadb:10 --lower-case-table-names=1

​
Database Optimization

​
Performance Tuning for MariaDB/MySQL

# Increase buffer pool (set to 70-80% of available RAM)
innodb_buffer_pool_size=4G

# Optimize log files
innodb_log_file_size=512M
innodb_log_buffer_size=16M

# Flush optimization
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT

# Query cache (MySQL 5.7 and earlier)
query_cache_type=1
query_cache_size=64M

# Connection pooling
max_connections=500
thread_cache_size=50

# Table cache
table_open_cache=4000
table_definition_cache=2000

​
Index Optimization

Monitor slow queries: 
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- Review slow queries
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

​
Table Maintenance

Regular maintenance commands: 
-- Analyze tables
ANALYZE TABLE sakai_table_name;

-- Optimize tables
OPTIMIZE TABLE sakai_table_name;

-- Check table integrity
CHECK TABLE sakai_table_name;

​
Backup and Recovery

​
Automated Backup Script

Create /usr/local/bin/backup-sakai-db.sh: 
#!/bin/bash

BACKUP_DIR="/var/backups/sakai"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="sakai"
DB_USER="sakai"
DB_PASS="YOUR_PASSWORD"

mkdir -p $BACKUP_DIR

# Dump database
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME | gzip > $BACKUP_DIR/sakai_$DATE.sql.gz

# Keep only last 30 days
find $BACKUP_DIR -name "sakai_*.sql.gz" -mtime +30 -delete

echo "Backup completed: sakai_$DATE.sql.gz"
Make executable and schedule: 
chmod +x /usr/local/bin/backup-sakai-db.sh

# Add to crontab (daily at 2 AM)
crontab -e
0 2 * * * /usr/local/bin/backup-sakai-db.sh

​
Restore from Backup

# Decompress and restore
gunzip < /var/backups/sakai/sakai_20260304_020000.sql.gz | \
  mysql -u sakai -p sakai

​
Monitoring Database Health

​
Key Metrics to Monitor

-- Connection statistics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- InnoDB buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Table sizes
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = 'sakai'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- Event table size check
SELECT COUNT(*) FROM SAKAI_EVENT;

​
Event Table Management

The SAKAI_EVENT table can grow large. Archive old events: 
-- Archive events older than 1 year
CREATE TABLE SAKAI_EVENT_ARCHIVE AS 
SELECT * FROM SAKAI_EVENT 
WHERE EVENT_DATE < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Delete archived events
DELETE FROM SAKAI_EVENT 
WHERE EVENT_DATE < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Optimize table
OPTIMIZE TABLE SAKAI_EVENT;
Always backup before performing bulk deletes or table modifications.

​
Troubleshooting

​
Connection Issues

Problem: “Too many connections” 
-- Increase max connections
SET GLOBAL max_connections = 500;

-- Make permanent in my.cnf
max_connections=500
Problem: “Access denied” Check user privileges: 
SHOW GRANTS FOR 'sakai'@'localhost';

-- Re-grant if needed
GRANT ALL PRIVILEGES ON sakai.* TO 'sakai'@'localhost';
FLUSH PRIVILEGES;

​
Performance Issues

Problem: Slow queries Enable and review slow query log: 
[mysqld]
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql/slow.log
Problem: High memory usage Adjust buffer pool: 
innodb_buffer_pool_size=2G  # Reduce if needed

​
Next Steps

Configuration

Configure database properties in Sakai

Security

Secure your database connections

User Management

Set up user accounts

Deployment

Complete deployment setup

Build docs developers (and LLMs) love

Get started for freeTalk to us