menu close

[Moodle Migration][Phase 2] MariaDB 11.4 LTS

mariadb, linux, moodle, vps, newroad

Goal: A local-only database with full Unicode and a least-privilege app user; secure defaults and a clean backup/restore story.

Steps

  • Install MariaDB 11.4 LTS
  • Local-only listen + Unicode defaults
  • Baseline hardening
  • Create the Moodle DB + app user
  1. Install MariaDB 11.4 LTS
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=11.4
sudo apt update
sudo apt -y install mariadb-server
sudo systemctl enable --now mariadb
mariadb --version   # expect 11.4.x
  1. Local-only listen + Unicode defaults
sudo tee /etc/mysql/mariadb.conf.d/60-moodle.cnf >/dev/null <<'EOF'
[mysqld]
bind-address = 127.0.0.1
character-set-server = utf8mb4
collation-server      = utf8mb4_unicode_ci
innodb_file_per_table = 1
EOF
sudo systemctl restart mariadb

Why: DB not exposed to the internet; guarantee utf8mb4 for content; cleaner storage per table.

  1. Baseline hardening
sudo mariadb-secure-installation

Recommended: remove anonymous users, disallow remote root, drop test DB, reload privileges. (Optional but recommended) root via unix_socket:

sudo mariadb -e "ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES;"

Why: tie SQL root to Linux sudo; no separate SQL root password to manage.

  1. Create the Moodle DB + app user
sudo mariadb <<'SQL'
CREATE DATABASE moodle
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'moodleuser'@'localhost' IDENTIFIED BY 'CHANGE_ME_STRONG';
GRANT ALL PRIVILEGES ON moodle.* TO 'moodleuser'@'localhost';
FLUSH PRIVILEGES;
SQL

Why: principle of least privilege; user scoped to localhost and the single schema.

Acceptance checks

mariadb --version                         # 11.4.x
ss -ltnp | grep 3306                      # 127.0.0.1:3306 only
sudo mariadb -e "SHOW VARIABLES LIKE 'character_set_server'; \
                 SHOW VARIABLES LIKE 'collation_server';"
sudo mariadb -e "SELECT user,host,plugin FROM mysql.user WHERE user='root';"
sudo mariadb -e "SELECT user,host FROM mysql.user WHERE user='moodleuser'; \
                 SHOW GRANTS FOR 'moodleuser'@'localhost';"

Expect: utf8mb4/unicode_ci; root via unix_socket (if you flipped it); moodleuser scoped to moodle.*.