Step 1 — Verify Ubuntu 24.04 and the MariaDB package
Confirm the operating system and architecture:
cat /etc/os-release
uname -m
Refresh package metadata:
Inspect the MariaDB candidate:
apt-cache policy mariadb-server mariadb-server-10.11
Ubuntu 24.04 currently tracks MariaDB 10.11. The noble-updates package is 10.11.14 at this revision.
Check resource and disk headroom:
Do not choose a production VM size from database size alone. MariaDB memory and I/O demand depends on the active working set, buffer pool, connection count, temporary tables, query shape, writes, and other services on the VM.
Verify: Ubuntu reports 24.04, APT exposes a MariaDB 10.11 candidate from Ubuntu repositories, and the VM has enough free disk for live data plus backup growth.
Install the server, client, OpenSSL, and UFW:
sudo apt install -y mariadb-server mariadb-client openssl ufw
Make sure the service is enabled and running:
sudo systemctl enable --now mariadb
Verify the client and server versions:
mariadb --version
sudo mariadb -NBe 'SELECT VERSION();'
Confirm Ubuntu still owns the package path:
apt-cache policy mariadb-server
Do not add MariaDB's upstream repository as an incidental update step. A move from Ubuntu's 10.11 packages to another repository or major version needs a tested migration plan.
Verify: the package installs successfully, MariaDB is running, SELECT VERSION() returns 10.11.x, and APT identifies Ubuntu as the package source.
Step 3 — Verify the service and port 3306
Check the service:
systemctl is-active mariadb
systemctl is-enabled mariadb
sudo systemctl status mariadb --no-pager
Verify the local server through its Unix socket:
Expected output:
Inspect TCP listeners:
sudo ss -lntp | grep ':3306' || true
Read the runtime bind address and port:
sudo mariadb -NBe 'SELECT @@bind_address, @@port;'
Verify: MariaDB is active and enabled, mariadb-admin ping succeeds, and there is no unintended public listener on TCP 3306.
Step 4 — Verify local root administration
Open the local administrative client:
Inspect the actual root account definition instead of assuming its authentication plugin:
SHOW CREATE USER 'root'@'localhost';
SELECT User, Host
FROM mysql.user
WHERE User = 'root';
MariaDB commonly uses unix_socket authentication for local root administration on Linux. Keep root restricted to localhost and use separate database accounts for applications.
Exit:
Verify: sudo mariadb works, the root account remains local-only, and no remote root account is needed by the application.
Step 5 — Run mariadb-secure-installation
Use MariaDB's current hardening command:
sudo mariadb-secure-installation
The old MySQL-style command name may not exist. If mysql_secure_installation returns command not found, use mariadb-secure-installation on this MariaDB installation.
Focus on the final state rather than memorizing prompt answers:
- preserve local root administration;
- remove anonymous users;
- prevent remote root access;
- remove the
test database;
- reload privileges when prompted.
Verify the result directly:
sudo mariadb -NBe \
"SELECT User, Host FROM mysql.user WHERE User = '';"
sudo mariadb -NBe \
"SELECT User, Host FROM mysql.user WHERE User = 'root';"
sudo mariadb -NBe \
"SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'test';"
The anonymous-user and test-database queries should return no rows.
Verify: anonymous accounts and the test database are absent, and every root account is local-only.
Step 6 — Create an application database and user
Generate an application password without putting a fixed password in the tutorial:
DB_PASSWORD="$(openssl rand -hex 32)"
printf 'Store this MariaDB password securely: %s\n' "$DB_PASSWORD"
Store the value in a secrets manager, then create the database and local account:
sudo mariadb <<SQL
CREATE DATABASE raffapp
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
CREATE USER 'raffappuser'@'localhost'
IDENTIFIED BY '${DB_PASSWORD}';
GRANT ALL PRIVILEGES ON raffapp.*
TO 'raffappuser'@'localhost';
SQL
unset DB_PASSWORD
Inspect the account and grants:
sudo mariadb -e \
"SHOW CREATE USER 'raffappuser'@'localhost';"
sudo mariadb -e \
"SHOW GRANTS FOR 'raffappuser'@'localhost';"
Normal CREATE USER and GRANT statements take effect immediately. A ritual FLUSH PRIVILEGES is not required after these account-management statements.
Verify: raffappuser exists only for localhost and has privileges on raffapp.* rather than global administrative privileges.
Step 7 — Run an authenticated CRUD test
Connect as the application user:
mariadb -u raffappuser -p raffapp
Create a disposable InnoDB table:
CREATE TABLE tutorial_check (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
status VARCHAR(32) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY unique_name (name)
) ENGINE=InnoDB;
Insert, read, update, and delete a row:
INSERT INTO tutorial_check (name, status)
VALUES ('mariadb-tutorial-test', 'created');
SELECT name, status
FROM tutorial_check
WHERE name = 'mariadb-tutorial-test';
UPDATE tutorial_check
SET status = 'verified'
WHERE name = 'mariadb-tutorial-test';
SELECT name, status
FROM tutorial_check
WHERE name = 'mariadb-tutorial-test';
DELETE FROM tutorial_check
WHERE name = 'mariadb-tutorial-test';
INSERT INTO tutorial_check (name, status)
VALUES ('mariadb-restore-marker', 'verified');
EXIT;
The second SELECT should return verified.
Verify: the non-root application account completes CRUD operations inside its own database without administrative privileges.
Step 8 — Keep MariaDB off the public network
Inspect the live listener:
sudo mariadb -NBe 'SELECT @@bind_address, @@port;'
sudo ss -lntp | grep ':3306' || true
For an application on the same VM, keep MariaDB on loopback. Do not switch bind-address to 0.0.0.0 just to make a remote GUI convenient.
Before changing UFW from a remote shell, open a second SSH session and inspect the current rules:
If UFW is already active and there is no intentional private MariaDB rule, add a defense-in-depth deny:
If UFW is inactive, follow Set Up UFW Firewall on Ubuntu 24.04 rather than enabling it blindly from one SSH connection.
Verify: MariaDB stays local for a single-VM application and no public firewall rule allows TCP 3306.
Step 9 — Allow one private application VM with TLS
Skip this step when the application and database share the same VM. For a separate application server connected through Raff VPC, replace these example addresses with your actual private IPs:
Database private IP: 10.0.0.5
Application private IP: 10.0.0.10
Before enabling a private listener, require an active firewall and review existing rules. If UFW is inactive, complete the linked UFW setup first while preserving SSH. Place the exact-source rule before the earlier blanket deny; remove any conflicting broad allow rule only after reviewing its purpose.
sudo ufw insert 1 allow from 10.0.0.10 to 10.0.0.5 port 3306 proto tcp
sudo ufw status numbered
MariaDB 10.11 supports multiple comma-separated bind addresses. Create a custom override rather than editing packaged defaults:
sudo tee /etc/mysql/mariadb.conf.d/z-raff-private.cnf >/dev/null <<'EOF'
[mariadb]
bind-address = 127.0.0.1,10.0.0.5
EOF
For production TLS, use a CA and server certificate your clients can validate. Store them at controlled paths such as:
/etc/mysql/tls/ca-cert.pem
/etc/mysql/tls/server-cert.pem
/etc/mysql/tls/server-key.pem
Protect the files:
sudo chown -R root:mysql /etc/mysql/tls
sudo chmod 750 /etc/mysql/tls
sudo chmod 640 /etc/mysql/tls/server-key.pem
sudo chmod 644 /etc/mysql/tls/ca-cert.pem /etc/mysql/tls/server-cert.pem
Add TLS configuration:
sudo tee -a /etc/mysql/mariadb.conf.d/z-raff-private.cnf >/dev/null <<'EOF'
ssl_ca = /etc/mysql/tls/ca-cert.pem
ssl_cert = /etc/mysql/tls/server-cert.pem
ssl_key = /etc/mysql/tls/server-key.pem
require_secure_transport = ON
EOF
Inspect effective startup options, then restart:
mariadbd --print-defaults
mariadbd --help --verbose >/dev/null
sudo systemctl restart mariadb
systemctl is-active mariadb
Verify TLS is active:
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'have_ssl';"
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';"
Create a remote account restricted to the application's exact private IP:
REMOTE_DB_PASSWORD="$(openssl rand -hex 32)"
printf 'Store this remote MariaDB password securely: %s\n' "$REMOTE_DB_PASSWORD"
sudo mariadb <<SQL
CREATE USER 'raffappuser'@'10.0.0.10'
IDENTIFIED BY '${REMOTE_DB_PASSWORD}'
REQUIRE SSL;
GRANT ALL PRIVILEGES ON raffapp.*
TO 'raffappuser'@'10.0.0.10';
SQL
unset REMOTE_DB_PASSWORD
Recheck the firewall rules configured before enabling the private listener.
Copy only the CA certificate to the application VM, then connect with server-certificate verification:
mariadb \
--host=10.0.0.5 \
--user=raffappuser \
--password \
--ssl-ca=/path/to/ca-cert.pem \
--ssl-verify-server-cert \
raffapp
Inside the session:
SHOW STATUS LIKE 'Ssl_cipher';
The cipher value must be non-empty.
Verify: MariaDB listens only on loopback plus the intended private IP, TLS is enabled, insecure TCP is rejected, the account matches the exact source IP, and the remote session reports a TLS cipher.
Step 10 — Create a consistent logical backup
Create a protected root-owned backup directory:
sudo install -d -m 700 /var/backups/mariadb
Build a backup filename:
STAMP="$(date -u +%Y%m%d-%H%M%S)"
BACKUP_FILE="/var/backups/mariadb/raffapp-${STAMP}.sql.gz"
Dump the InnoDB application database with a consistent transaction snapshot. sudo tee writes the compressed output into the root-owned backup directory without weakening directory permissions:
(
set -euo pipefail
sudo mariadb-dump \
--single-transaction \
--quick \
--routines \
--triggers \
--events \
raffapp \
| gzip \
| sudo tee "$BACKUP_FILE" > /dev/null
sudo chmod 600 "$BACKUP_FILE"
sudo gzip -t "$BACKUP_FILE"
)
# Continue only if the block above exits successfully.
Restrict and validate the backup:
sudo chmod 600 "$BACKUP_FILE"
sudo gzip -t "$BACKUP_FILE"
sudo zcat "$BACKUP_FILE" | head
--single-transaction provides a consistent snapshot for transactional tables such as InnoDB. It does not make concurrently written non-transactional tables consistent.
Keep at least one production backup outside the database VM. Raff Object Storage can be used by S3-compatible backup tooling, while Data Protection provides an additional VM-level recovery layer.
Verify: the dump exists with restrictive permissions, gzip -t succeeds, and your production recovery plan includes an off-server copy.
Step 11 — Restore into a disposable database
Never prove a backup by overwriting production.
Create an isolated restore target:
sudo mariadb -e \
"CREATE DATABASE raffapp_restore_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
Select the newest backup:
LATEST_BACKUP="$(sudo find /var/backups/mariadb -maxdepth 1 -type f -name 'raffapp-*.sql.gz' -print | LC_ALL=C sort | tail -n 1)"
# Stop here if no readable backup was selected.
if [ -z "$LATEST_BACKUP" ] || ! sudo test -s "$LATEST_BACKUP"; then
printf '%s\n' 'No non-empty backup found. Do not continue with restore.' >&2
false
fi
echo "$LATEST_BACKUP"
Restore it directly into the disposable database:
( set -o pipefail; sudo zcat "$LATEST_BACKUP" | sudo mariadb raffapp_restore_test )
Inspect the restored tables:
sudo mariadb -e \
"SHOW TABLES FROM raffapp_restore_test; SELECT name, status FROM raffapp_restore_test.tutorial_check WHERE name='mariadb-restore-marker';"
The restore must contain mariadb-restore-marker with status verified. An empty table listing is not a successful recovery test. For production recovery rehearsals, also validate representative row counts and application-level records.
Remove only the disposable target after verification:
sudo mariadb -e 'DROP DATABASE raffapp_restore_test;'
Verify: expected tables and data exist in raffapp_restore_test, production raffapp remains untouched, and the test target can be removed cleanly.
Step 12 — Apply Ubuntu MariaDB updates safely
Check for pending packages:
apt list --upgradable 2>/dev/null | grep -E '^mariadb|^galera' || true
Before updating:
- create a fresh logical backup;
- confirm an off-server copy exists;
- review the Ubuntu package changelog and MariaDB release notes;
- check free disk space;
- choose an appropriate maintenance window.
Apply normal distro updates:
sudo apt update
sudo apt upgrade
Verify afterward:
systemctl is-active mariadb
sudo mariadb -NBe 'SELECT VERSION();'
sudo mariadb-admin ping
sudo journalctl -u mariadb --no-pager -n 100
For normal Ubuntu package maintenance, remain on the distro-managed 10.11 branch. Do not turn a patch cycle into an upstream repository migration accidentally.
Verify: MariaDB starts cleanly, remains on the expected Ubuntu 10.11 package path, and passes the application login and CRUD checks after updating.
Step 13 — Treat MariaDB 12.3 LTS as a major-version migration
MariaDB upstream currently lists 12.3.3 as the latest 12.3 LTS maintenance release. The upstream 10.11 series is also newer than Ubuntu's current Noble package, but Ubuntu 24.04 intentionally follows its own package lifecycle.
Before moving from Ubuntu's 10.11 packages to a newer upstream LTS:
- read the target-version upgrade notes;
- inventory storage engines and plugins;
- test application queries and connectors;
- restore a current production backup in staging;
- review changed or removed configuration options;
- plan the repository transition;
- plan
mariadb-upgrade where required;
- define rollback as restore onto the previous known-good version.
Check whether mariadb-upgrade sees a requirement:
sudo mariadb-upgrade --check-if-upgrade-is-needed; echo $?
Do not perform the major upgrade on the only production copy.
Verify: a major-version move has a staging rehearsal, a verified backup, application compatibility checks, and a restore-based rollback plan before repositories or binaries change.
Step 14 — Verify MariaDB end to end
Run the final service, account, network, TLS, and recovery checks:
echo 'Service:'
systemctl is-active mariadb
systemctl is-enabled mariadb
echo 'Version:'
sudo mariadb -NBe 'SELECT VERSION();'
echo 'Root accounts:'
sudo mariadb -NBe \
"SELECT User, Host FROM mysql.user WHERE User='root';"
echo 'Application accounts:'
sudo mariadb -NBe \
"SELECT User, Host FROM mysql.user WHERE User='raffappuser';"
echo 'Listener:'
sudo ss -lntp | grep ':3306' || true
echo 'TLS:'
sudo mariadb -NBe \
"SHOW GLOBAL VARIABLES LIKE 'have_ssl'; SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';"
echo 'Firewall:'
sudo ufw status numbered
echo 'Recent backups:'
sudo find /var/backups/mariadb -maxdepth 1 -type f -name 'raffapp-*.sql.gz' -ls
Confirm all of these are true:
- Ubuntu 24.04 manages the expected MariaDB 10.11 package branch;
- the service is active and enabled;
- root administration is local-only;
- anonymous users and the test database are gone;
- the application uses a separate database-scoped account;
- TCP 3306 is not publicly exposed;
- optional remote access is private, source-restricted, and encrypted;
- a logical backup exists off-server;
- a restore has succeeded in an isolated target;
- routine patching stays on the Ubuntu package path;
- a future major upgrade has a tested migration and rollback plan.
Verify: the installation passes every check above, including the CRUD test from Step 7 and isolated restore from Step 11.
Cleanup (optional)
Use this section only when the database, users, firewall rules, TLS override, and backup files were created solely for this tutorial.
Warning
These commands can permanently delete tutorial data and credentials.
Drop the tutorial database and users:
sudo mariadb <<'SQL'
DROP DATABASE IF EXISTS raffapp;
DROP USER IF EXISTS 'raffappuser'@'localhost';
DROP USER IF EXISTS 'raffappuser'@'10.0.0.10';
SQL
Remove the tutorial private-network override only if you created it for this guide:
sudo rm -f /etc/mysql/mariadb.conf.d/z-raff-private.cnf
sudo systemctl restart mariadb
Delete only the UFW rules added by this tutorial:
sudo ufw delete allow from 10.0.0.10 to 10.0.0.5 port 3306 proto tcp
sudo ufw delete deny 3306/tcp
Warning: The next commands permanently delete all backups matching these tutorial filenames. Keep an off-server copy of anything you need. Run them only in a disposable tutorial environment:
sudo find /var/backups/mariadb -maxdepth 1 -type f -name 'raffapp-*.sql.gz' -print -delete
Verify the database and users are gone:
sudo mariadb -NBe \
"SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='raffapp';"
sudo mariadb -NBe \
"SELECT User, Host FROM mysql.user WHERE User='raffappuser';"
Both queries should return no rows.
Troubleshooting
mysql.service is not found
MariaDB's systemd service is normally named mariadb, not mysql. Check the correct unit:
systemctl status mariadb --no-pager
If it does not exist, verify the server package is installed:
dpkg -l | grep -E '^ii\s+mariadb-server'
apt-cache policy mariadb-server
Install it if necessary:
sudo apt update
sudo apt install -y mariadb-server
mysql_secure_installation: command not found
Use MariaDB's current command:
sudo mariadb-secure-installation
Verify it exists:
command -v mariadb-secure-installation
mariadb client command is missing
Install only the client when you do not need a local database server:
sudo apt update
sudo apt install -y mariadb-client
mariadb --version
sudo mariadb returns access denied
Inspect service health and the error log before attempting a root-account reset:
systemctl is-active mariadb
sudo journalctl -u mariadb --no-pager -n 100
sudo tail -n 100 /var/log/mysql/error.log
Confirm you are following MariaDB 10.11 guidance rather than a MySQL-specific recovery procedure.
MariaDB does not start after a configuration change
Inspect effective options and logs:
mariadbd --print-defaults
mariadbd --help --verbose >/dev/null
sudo journalctl -u mariadb --no-pager -n 100
sudo tail -n 100 /var/log/mysql/error.log
If the failure followed z-raff-private.cnf, move that file out of the configuration directory and restart from the prior known-good state.
A private remote connection times out
Check all four layers:
sudo mariadb -NBe 'SELECT @@bind_address, @@port;'
sudo ss -lntp | grep ':3306'
sudo ufw status numbered
sudo mariadb -NBe \
"SELECT User, Host FROM mysql.user WHERE User='raffappuser';"
The client source address must match the listener, firewall rule, and MariaDB account host.
Check certificate paths, permissions, and the error log:
sudo ls -l /etc/mysql/tls
sudo journalctl -u mariadb --no-pager -n 100
sudo tail -n 100 /var/log/mysql/error.log
Do not create a production remote path that depends on TLS until have_ssl reports YES.
The backup command returns permission denied
/var/backups/mariadb is root-owned in this tutorial. Use the sudo tee pipeline from Step 10 rather than normal shell redirection into that directory.
FAQ
How do I install MariaDB on Ubuntu 24.04?
Run sudo apt update and sudo apt install -y mariadb-server mariadb-client, then enable the service and verify the server version.
What MariaDB version does Ubuntu 24.04 install?
Ubuntu 24.04 currently uses the MariaDB 10.11 branch. Noble-updates provides MariaDB 10.11.14 at this revision.
Is MariaDB 10.11 still supported?
Yes. MariaDB 10.11 remains a maintained long-term series, while Ubuntu 24.04 also maintains its own distro package lifecycle.
Why does sudo mariadb work without a root password?
MariaDB commonly uses local unix_socket authentication on Linux. Verify the actual root account definition with SHOW CREATE USER instead of assuming the plugin.
Should MariaDB port 3306 be public?
No. Keep it local for same-VM applications. For a separate app VM, use private networking, an exact source rule, and verified TLS.
How do I install only the MariaDB client on Ubuntu?
Run sudo apt install -y mariadb-client. This installs the command-line client without requiring a local MariaDB server.
Is mariadb-dump enough for production recovery?
It can be one layer. Production recovery also needs off-server copies, retention, restore testing, monitoring, and workload-specific RPO and RTO targets.
Conclusion
You now have MariaDB 10.11 running on Ubuntu 24.04 with local administration, a dedicated application account, verified CRUD, private-by-default networking, logical backups, and an isolated restore test.
The important package distinction is clear: Ubuntu 24.04 currently provides MariaDB 10.11.14, while upstream MariaDB has newer 10.11 maintenance releases and the newer 12.3 LTS line. Keep routine distro patching separate from a deliberate major-version migration.
For related production decisions, continue with MySQL vs MariaDB for Production Apps, Install MySQL on Ubuntu 24.04, and Set Up UFW Firewall on Ubuntu 24.04.
Sources
