Databases AWS

MariaDB 10.11.19 on AWS User Guide

| Product: MariaDB 10.11.19 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using the MariaDB 10.11.19 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 10.11 series is a long term support release, maintained by MariaDB until February 2028. It is the long term support line many applications and frameworks still certify against, with the InnoDB, Aria and replication engine you already know, system versioned tables, JSON functions and Mariabackup for hot physical backups.

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 10.11 repository, checked against the MariaDB signing key and pinned to that repository, so updates stay on the 10.11 long term support line. Ubuntu's own archive build of 10.11 is older; the MariaDB repository carries the latest 10.11 point release with its security fixes.
  • Hardened defaults. MariaDB and its first boot services are blocked from the instance metadata service and the AMI requires IMDSv2.
  • SSH keys only. Password and keyboard-interactive logins, direct root login and X11 forwarding are switched off (pinned in /etc/ssh/sshd_config.d/00-cloudimg.conf), and the operating system's root account has no password.
  • Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on. The firmware update timer (fwupd-refresh) is masked, because EC2 instances have no firmware it can update.

What is on the image: MariaDB Server, client and Mariabackup 10.11.19 (package 1:10.11.19+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 10.11.19 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-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.

Step 2: Connect to your instance

Version SSH user
MariaDB 10.11.19 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  126M   19G /var/lib/mysql

Step 4: Get your administrator password

sudo cat /root/mariadb-credentials.txt

The file is readable by root only:

# 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://www.cloudimg.co.uk/guides/mariadb-10-11-19-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  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 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 even with the correct dbadmin password, 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 --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), and 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; 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=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/cloudimg/mariadb-release.txt
apt-cache policy mariadb-server | sed -n '1,3p'

cloudimg: 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

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 10.11 repository, so its point releases (10.11.20 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