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 Setup (Recommended)

MariaDB is the recommended database for Sakai due to its performance, reliability, and open-source nature.

Installation

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

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

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

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;

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

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

Secure MySQL Installation

sudo mysql_secure_installation

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

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

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;

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;

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

​Database Setup

​Supported Databases

​MariaDB Setup (Recommended)

​Installation

​MariaDB Configuration in Sakai

​MySQL 8 Setup

​Installation

​MySQL 8 Configuration in Sakai

​Oracle Database Setup

​Prerequisites

​Database Configuration

​Oracle Configuration in Sakai

​Docker Database Setup

​MariaDB Docker Container

​Initialize Database with Script

​Database Optimization

​Performance Tuning for MariaDB/MySQL

​Index Optimization

​Table Maintenance

​Backup and Recovery

​Automated Backup Script

​Restore from Backup

​Monitoring Database Health

​Key Metrics to Monitor

​Event Table Management

​Troubleshooting

​Connection Issues

​Performance Issues

​Next Steps

Configuration

Security

User Management

Deployment

Database Setup

Supported Databases

MariaDB Setup (Recommended)

Installation

MariaDB Configuration in Sakai

MySQL 8 Setup

Installation

MySQL 8 Configuration in Sakai

Oracle Database Setup

Prerequisites

Database Configuration

Oracle Configuration in Sakai

Docker Database Setup

MariaDB Docker Container

Initialize Database with Script

Database Optimization

Performance Tuning for MariaDB/MySQL

Index Optimization

Table Maintenance

Backup and Recovery

Automated Backup Script

Restore from Backup

Monitoring Database Health

Key Metrics to Monitor

Event Table Management

Troubleshooting

Connection Issues

Performance Issues

Next Steps