Databases AWS

MariaDB 11.8.9 on AWS User Guide

| Product: MariaDB 11.8.9 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using the MariaDB 11.8.9 on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg MySQL, MariaDB and Percona Server listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

MariaDB Server is the open source relational database that began as a fork of MySQL and stays compatible with MySQL clients, drivers and SQL. The 11.8 series is a long term support release, maintained by the MariaDB Foundation until February 2030, and adds features such as vector search (the VECTOR type and indexes) to the InnoDB, Aria and replication engine you already know.

What the cloudimg image adds:

  • No shared password, anywhere. The image ships a freshly initialised data directory with no password account at all. First boot creates dbadmin, a full MariaDB administrator, with a password unique to your instance, and writes it to /root/mariadb-credentials.txt (root only).
  • Root only from the instance. MariaDB's root account has no password and no network access: it signs in only through the local socket as the operating system's root user (sudo mariadb).
  • TLS from the first boot, and required. A TLS key and certificate are generated on your instance before MariaDB first starts (MariaDB will not start without them), and every TCP connection must use TLS 1.2 or 1.3: plain text connections are refused.
  • A hardened MariaDB. MariaDB listens on 127.0.0.1 only, with no anonymous accounts, no test database, LOAD DATA LOCAL off, server side file access confined to /var/lib/mysql-files, host name lookups off, the usage feedback plugin off, and no binary or general query log.
  • Your data on its own disk. The data directory /var/lib/mysql is a separate 20 GiB EBS volume you can grow or snapshot on its own.
  • Signed, pinned packages. MariaDB comes from the official MariaDB 11.8 repository, checked against the MariaDB signing key and pinned to that repository, so updates stay on the 11.8 long term support line.
  • Hardened defaults. MariaDB and its first boot services are blocked from the instance metadata service and the AMI requires IMDSv2.
  • Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on.

What is on the image: MariaDB Server, client and Mariabackup 11.8.9 (package 1:11.8.9+maria~ubu2404 from the MariaDB repository), Ubuntu 24.04 LTS, cloud-init and the AWS Systems Manager agent.

Default user: ubuntu · Architecture: x86_64 · Recommended instance type: m5.large

Prerequisites

  • An AWS account subscribed to the listing, an EC2 key pair and a VPC subnet with a route to the internet (or a private route you use for management).
  • A security group that allows TCP 22 (SSH) from your admin network. MariaDB listens on the loopback address only, so port 3306 stays closed unless you choose to open it to your application subnet (Step 8).

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick MariaDB 11.8.9 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large, your key pair and a security group as above. The AMI brings two volumes: the 8 GiB root volume and a 20 GiB data volume (/dev/sdf) for /var/lib/mysql. Make the data volume as large as your databases need at launch.

With the AWS CLI (replace the values in angle brackets; this example grows the data volume to 100 GiB):

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --block-device-mappings 'DeviceName=/dev/sdf,Ebs={VolumeSize=100,VolumeType=gp3}' \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=mariadb-11-8}]'

If you make the data volume larger at launch, grow the file system once after first boot with sudo resize2fs "$(findmnt -no SOURCE /var/lib/mysql)". First boot takes well under a minute after the instance is running.

Step 2: Connect to your instance

Version SSH user
MariaDB 11.8.9 on Ubuntu 24.04 ubuntu 
ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the services

First boot creates the certificate, starts MariaDB and then creates the administrator account:

systemctl is-active mariadb-tls-init mariadb mariadb-firstboot
sudo test -f /var/lib/cloudimg/mariadb-firstboot.done && echo FIRSTBOOT_DONE

All three units are active and the first boot marker is present:

active
active
active
FIRSTBOOT_DONE

Only SSH listens on a network port; MariaDB listens on the loopback address:

sudo ss -lntp | awk 'NR>1 {print $4, $6}' | sed -E 's/,pid=[0-9]+,fd=[0-9]+//g' | grep -v -E '^127\.0\.0\.5[34]' | sort

0.0.0.0:22 users:(("sshd"),("systemd"))
127.0.0.1:3306 users:(("mariadbd"))
[::]:22 users:(("sshd"),("systemd"))

The data directory is the separate data volume:

findmnt -no SOURCE,FSTYPE /var/lib/mysql
df -h --output=size,used,avail,target /var/lib/mysql

/dev/nvme1n1 ext4
 Size  Used Avail Mounted on
  20G  155M   19G /var/lib/mysql

Step 4: Get your administrator password

sudo cat /root/mariadb-credentials.txt

The file is readable by root only:

# MariaDB 11.8.9 on Ubuntu 24.04 - generated on first boot by mariadb-firstboot.service
# This password is unique to this instance; none was baked into the image. Keep this file secret.

MARIADB_ADMIN_USER=dbadmin
MARIADB_ADMIN_PASSWORD=<MARIADB_ADMIN_PASSWORD>
MARIADB_HOST=127.0.0.1
MARIADB_PORT=3306
MARIADB_TLS_CA=/etc/mysql/ssl/server-cert.pem

# root has no password: on the instance use  sudo mariadb  (unix socket only, never over the network).
# Every TCP connection must use TLS (require_secure_transport=ON); MariaDB listens on 127.0.0.1:3306 only.
# Re-issue the TLS certificate for your own DNS name:  sudo mariadb-tls-cert --renew <your-dns-name>
# The datadir /var/lib/mysql is its own EBS volume.
# User guide: https://www.cloudimg.co.uk/guides/mariadb-11-8-9-on-ubuntu-24-04-aws/

Step 5: Sign in

As the operating system's root user, MariaDB's root account signs in through the local socket with no password:

sudo mariadb -e "SELECT CURRENT_USER(), VERSION();"

CURRENT_USER()  VERSION()
root@localhost  11.8.9-MariaDB-ubu2404

To work as dbadmin over TCP, keep its password in a client option file that only you can read, so it never appears on a command line. This writes ~/.mariadb-dbadmin.cnf, pointing the client at the instance's own certificate and verifying it (rename it to ~/.my.cnf if you want it used by default):

( umask 077; sudo awk -F= '/^MARIADB_ADMIN_PASSWORD=/{print "[client]\nuser=dbadmin\npassword=" $2 "\nhost=127.0.0.1\nssl-ca=/etc/mysql/ssl/server-cert.pem\nssl-verify-server-cert"}' /root/mariadb-credentials.txt > ~/.mariadb-dbadmin.cnf )
ls -l ~/.mariadb-dbadmin.cnf | awk '{print $1, $NF}'
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -e "SELECT CURRENT_USER(); SHOW SESSION STATUS LIKE 'Ssl_version'; SHOW SESSION STATUS LIKE 'Ssl_cipher';"

The session is dbadmin over a verified TLS 1.3 connection:

-rw------- /home/ubuntu/.mariadb-dbadmin.cnf
CURRENT_USER()
dbadmin@127.0.0.1
Variable_name   Value
Ssl_version TLSv1.3
Variable_name   Value
Ssl_cipher  TLS_AES_256_GCM_SHA384

The image starts with only the system databases; nothing from the build is left behind:

mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -e "SHOW DATABASES;"

Database
information_schema
mysql
performance_schema
sys

Step 6: Create a database and an application user

Give each application its own database and an account with rights on that database only. Run this as root through the socket (choose your own strong password; the statements go to MariaDB on standard input):

sudo mariadb <<'SQL'
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '<app-password>';
CREATE USER 'appuser'@'127.0.0.1' IDENTIFIED BY '<app-password>';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost', 'appuser'@'127.0.0.1';
SQL

An application on the same instance connects to 127.0.0.1:3306 with TLS (most MySQL and MariaDB drivers have a "require SSL" or ssl-mode=REQUIRED option; give them /etc/mysql/ssl/server-cert.pem as the CA file to verify the server too), or to the local socket /run/mysqld/mysqld.sock.

Step 7: Connect from your own computer

MariaDB is not exposed to the network. From your computer, open an SSH tunnel and connect through it; copy the instance's certificate first so your client can verify the server:

scp -i <your-key.pem> ubuntu@<public-ip>:/etc/mysql/ssl/server-cert.pem ./mariadb-ca.pem
ssh -i <your-key.pem> -N -L 3307:127.0.0.1:3306 ubuntu@<public-ip>
mariadb -h 127.0.0.1 -P 3307 -u dbadmin -p --ssl-ca=./mariadb-ca.pem --ssl-verify-server-cert

Graphical tools such as DBeaver, HeidiSQL or MySQL Workbench offer the same SSH tunnel and "require SSL" settings.

Step 8: Allow connections from your VPC

To let application servers in your VPC connect directly, change the listening address, open 3306 to your application subnet only, and create accounts for that subnet that require TLS:

sudo sed -i 's/^bind-address = 127.0.0.1/bind-address = 0.0.0.0/' /etc/mysql/mariadb.conf.d/99-cloudimg.cnf
sudo systemctl restart mariadb
sudo mariadb -e "CREATE USER 'appuser'@'10.0.1.%' IDENTIFIED BY '<app-password>' REQUIRE SSL; GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'10.0.1.%';"

Then add an inbound rule for TCP 3306 from the application subnet (for example 10.0.1.0/24) to the instance's security group. Never open 3306 to the internet. Connections still have to use TLS, because require_secure_transport stays on.

Step 9: Your own DNS name and certificate

The certificate generated on first boot names localhost, 127.0.0.1 and the instance's private and public EC2 names and addresses. If clients reach the database by a DNS name of your own, re-issue the certificate with that name added. MariaDB loads the new certificate at once, without a restart (FLUSH SSL); give clients the new /etc/mysql/ssl/server-cert.pem:

sudo mariadb-tls-cert --renew db.example.internal

To use a certificate from your own certificate authority, replace server-cert.pem and server-key.pem in /etc/mysql/ssl/ (keep the key 0600 and owned by mysql; if your certificate is issued by a CA, point ssl_ca in /etc/mysql/mariadb.conf.d/99-cloudimg.cnf at your CA bundle), then run sudo mariadb -e "FLUSH SSL;".

Step 10: Security checks

MariaDB runs with the cloudimg settings: loopback only, TLS required, LOAD DATA LOCAL and the binary log off, file access confined:

sudo mariadb -e "SELECT @@bind_address, @@require_secure_transport, @@tls_version, @@local_infile, @@log_bin, @@secure_file_priv;"

@@bind_address  @@require_secure_transport  @@tls_version   @@local_infile  @@log_bin   @@secure_file_priv
127.0.0.1   1   TLSv1.2,TLSv1.3 0   0   /var/lib/mysql-files/

A plain text connection is refused, root cannot sign in over the network with any password, and a wrong dbadmin password is refused:

mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" --skip-ssl -e 'SELECT 1' 2>&1 || true
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -u root --password=anything -e 'SELECT 1' 2>&1 || true
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" --password=wrong-password -e 'SELECT 1' 2>&1 || true

ERROR 3159 (08004): Connections using insecure transport are prohibited while --require_secure_transport=ON.
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
ERROR 1045 (28000): Access denied for user 'dbadmin'@'127.0.0.1' (using password: YES)

root has no usable password and authenticates only through the socket, the internal mariadb.sys account is locked, and dbadmin is the only password account:

sudo mariadb -e "SHOW CREATE USER 'root'@'localhost'; SHOW CREATE USER 'mariadb.sys'@'localhost';"
sudo mariadb -N -e "SELECT CONCAT(User,'@',Host) FROM mysql.global_priv ORDER BY 1;"

CREATE USER for root@localhost
CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket
CREATE USER for mariadb.sys@localhost
CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
dbadmin@127.0.0.1
dbadmin@::1
dbadmin@localhost
mariadb.sys@localhost
mysql@localhost
root@localhost

The certificate was generated on this instance and names its own addresses; the key is readable by MariaDB only:

openssl x509 -in /etc/mysql/ssl/server-cert.pem -noout -subject -ext subjectAltName
ls -l /etc/mysql/ssl | awk 'NR>1{print $1, $3, $4, $NF}'

subject=CN = ip-<private-ip-dashed>.ec2.internal, O = cloudimg MariaDB
X509v3 Subject Alternative Name:
    DNS:ip-<private-ip-dashed>.ec2.internal, IP Address:<private-ip>, DNS:ec2-<public-ip-dashed>.compute-1.amazonaws.com, IP Address:<public-ip>, DNS:localhost, IP Address:127.0.0.1, IP Address:0:0:0:0:0:0:0:1
-rw-r--r-- root root server-cert.pem
-rw------- mysql mysql server-key.pem

MariaDB and its first boot services cannot reach the instance metadata service:

systemctl show mariadb mariadb-tls-init mariadb-firstboot -p Id -p IPAddressDeny

IPAddressDeny=168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128
Id=mariadb.service

IPAddressDeny=168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128
Id=mariadb-tls-init.service

IPAddressDeny=168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128
Id=mariadb-firstboot.service

The image's own self test signs in as dbadmin, writes and reads a table over verified TLS, and proves the refusals:

sudo bash /usr/local/sbin/mariadb-cred-roundtrip.sh

OK dbadmin signs in over verified TLS and round-trips a table; wrong password, plaintext TCP and root over TCP are refused; root works only via the unix socket

The packages come from the signed MariaDB 11.8 repository:

cat /etc/cloudimg/mariadb-release.txt
apt-cache policy mariadb-server | sed -n '1,3p'

cloudimg: MariaDB Server 1:11.8.9+maria~ubu2404 (GPL-2.0-only) from the official MariaDB 11.8 repository (dlm.mariadb.com), signing key 177F4010FE56CA3336300305F1656F24C74CD1D8.
mariadb-server:
  Installed: 1:11.8.9+maria~ubu2404
  Candidate: 1:11.8.9+maria~ubu2404

Step 11: Changing the administrator password

Change it for each of the three dbadmin host entries (localhost, 127.0.0.1, ::1), then update ~/.mariadb-dbadmin.cnf and update or delete MARIADB_ADMIN_PASSWORD in /root/mariadb-credentials.txt:

sudo mariadb <<'SQL'
ALTER USER 'dbadmin'@'localhost' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'127.0.0.1' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'::1' IDENTIFIED BY '<new-password>';
SQL

Step 12: Backup, updates and maintenance

Every database lives in /var/lib/mysql on the data volume. The simplest backup is an EBS snapshot of that volume (with MariaDB stopped, or taken with Mariabackup for a consistent copy). For a logical backup while MariaDB is running:

sudo mariadb-dump --all-databases --single-transaction --routines --triggers --events | gzip > <backup-dir>/mariadb-$(date +%F).sql.gz

For a physical hot backup with Mariabackup:

sudo mariadb-backup --backup --target-dir=<backup-dir>/full-$(date +%F)
sudo mariadb-backup --prepare --target-dir=<backup-dir>/full-$(date +%F)

Ubuntu security updates install automatically. MariaDB comes from the MariaDB 11.8 repository, so its point releases (11.8.10 and later) are applied when you choose, because a database upgrade restarts the server:

sudo apt-get update && sudo apt-get install --only-upgrade mariadb-server mariadb-client mariadb-backup

The default InnoDB buffer pool is 128 MB. On an m5.large (8 GiB) a good starting point is about half the memory: add innodb_buffer_pool_size = 4G to /etc/mysql/mariadb.conf.d/99-cloudimg.cnf and restart MariaDB.

Component Path
MariaDB data directory /var/lib/mysql (separate EBS volume)
MariaDB settings /etc/mysql/mariadb.conf.d/99-cloudimg.cnf
Certificate and key /etc/mysql/ssl/server-cert.pem, /etc/mysql/ssl/server-key.pem
Certificate helper /usr/local/sbin/mariadb-tls-cert
First boot services mariadb-tls-init.service, mariadb-firstboot.service
Server side file import/export directory /var/lib/mysql-files
Credentials /root/mariadb-credentials.txt (mode 0600)
Repository and pin /etc/apt/sources.list.d/mariadb.sources, /etc/apt/preferences.d/cloudimg-mariadb.pref

Step 13: Security recommendations

  • Allow port 22 from your admin network only. Keep 3306 closed, or open it to your application subnet only (Step 8); never to the internet.
  • Create a MariaDB user per application with rights on its own database only, and keep dbadmin for administration.
  • Give clients the instance's certificate as their CA file and turn on server certificate verification, or issue a certificate from your own CA (Step 9).
  • Change the administrator password if it may have been shared (Step 11), apply MariaDB point releases, and snapshot the data volume regularly.

Support and licensing

MariaDB Server is open source under the GNU General Public License version 2; there is no per user or per CPU licence fee. The AMI price covers cloudimg's hardening, first boot setup and support.

  • Email: support@cloudimg.co.uk
  • Website: https://www.cloudimg.co.uk
  • Support: 24/7, with a 24 hour response SLA