MariaDB 10.11.19

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick MariaDB 10.11.19 on Ubuntu 24.04 as the software version and select your Region. Then choose 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.

To launch with the AWS CLI instead, 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-10-11}]'

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.

Connect over SSH. The login user for MariaDB 10.11.19 on Ubuntu 24.04 is ubuntu:

ssh -i <your-key.pem> ubuntu@<public-ip>

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

systemctl is-active mariadb-tls-init mariadb mariadb-firstboot
sudo test -f /var/lib/sac/data/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 126M 19G /var/lib/mysql

sudo cat /root/mariadb-credentials.txt

Only root can read the file:

# MariaDB 10.11.19 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://secureanycloud.com/stacks/mariadb-10-11-19/

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 10.11.19-MariaDB-ubu2404

To work as dbadmin over TCP, keep its password in a client option file that only you can read, so that it never appears on a command line.

This command writes ~/.mariadb-dbadmin.cnf. The file points the client at the instance's own certificate and verifies 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

Give each application its own database and an account with rights on that database only. Run the following 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, or to the local socket /run/mysqld/mysqld.sock.

Most MySQL and MariaDB drivers have a "require SSL" or ssl-mode=REQUIRED option. To verify the server as well, give them /etc/mysql/ssl/server-cert.pem as the CA file.

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 that 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.

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. Run:

sudo sed -i 's/^bind-address = 127.0.0.1/bind-address = 0.0.0.0/' /etc/mysql/mariadb.conf.d/99-sac.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.

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-sac.cnf at your CA bundle.

Then run sudo mariadb -e "FLUSH SSL;".

MariaDB runs with the SecureAnyCloud settings: loopback only, TLS required, LOAD DATA LOCAL and the binary log off, and 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/

Three connections are refused. A plain-text connection is refused even with the correct dbadmin password. root cannot sign in over the network with any password. A wrong dbadmin password is refused:

mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" --skip-ssl --skip-ssl-verify-server-cert -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 1045 (28000): Access denied for user 'dbadmin'@'127.0.0.1' (using password: YES) 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)

The first line uses the correct password. MariaDB 10.11 answers a plain-text session with "Access denied" while require_secure_transport is on. MariaDB 11 and later name the reason instead. The same option file signs in as soon as TLS is used, as in Step 5.

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. Only MariaDB can read the key:

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 = sac 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=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 Id=mariadb.service IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128 Id=mariadb-tls-init.service IPAddressDeny=169.254.169.254/32 168.63.129.16/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 10.11 repository:

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

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

Change the password 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

Every database lives in /var/lib/mysql on the data volume. The simplest backup is an EBS snapshot of that volume. Take it with MariaDB stopped, or use 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 10.11 repository, so you apply its point releases (10.11.20 and later) when you choose. 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-sac.cnf and restart MariaDB.

Key paths on the instance:

• MariaDB data directory: /var/lib/mysql (separate EBS volume)
• MariaDB settings: /etc/mysql/mariadb.conf.d/99-sac.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/sac-mariadb.pref

• Allow port 22 from your admin network only. Keep 3306 closed, or open it to your application subnet only (Step 8). Never open it 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.