Databases AWS

MySQL 8.4 on AWS User Guide

| Product: MySQL 8.4 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using the MySQL 8.4 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.

MySQL Community Server is the open source relational database. The 8.4 series is a long term support (LTS) release line: it receives security and bug fix point releases without new features, so applications get a stable server for years. This image ships MySQL Community Server 8.4.13 from the official MySQL APT repository.

What the cloudimg image adds:

  • No shared password, anywhere. The image ships an empty data directory: nothing in it was created on the build machine. On first boot your instance initialises its own data directory and creates dbadmin, a full MySQL administrator, with a password unique to your instance, written to /root/mysql-credentials.txt (root only).
  • Root only from the instance. MySQL's root account has no password and no network access: it uses the auth_socket plugin and signs in only through the local socket as the operating system's root user (sudo mysql).
  • TLS from the first boot, and required. A TLS key and certificate are generated on your instance before MySQL first starts (MySQL will not start without them), and every TCP connection must use TLS 1.2 or 1.3: plain text connections are refused. MySQL's own automatic key generation is switched off, so no key is ever written into the data directory.
  • A hardened MySQL. MySQL listens on 127.0.0.1 only, the X Protocol port 33060 is off, there are no anonymous accounts and no test database, LOAD DATA LOCAL is off, server side file access is confined to /var/lib/mysql-files, host name lookups are off, mysql_native_password is off, and there is 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. MySQL comes from the official MySQL 8.4 LTS APT repository, checked against the MySQL release signing key and pinned to that repository, so updates stay on the 8.4 long term support line.
  • Hardened defaults. MySQL 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: MySQL Community Server and client 8.4.13 (package 8.4.13-1ubuntu24.04 from the MySQL APT 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. MySQL 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 MySQL 8.4 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=mysql-8-4}]'

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
MySQL 8.4 on Ubuntu 24.04 ubuntu 
ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the services

First boot creates the certificate, initialises the data directory, starts MySQL and then creates the administrator account:

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

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

active
active
active
active
FIRSTBOOT_DONE

Only SSH listens on a network port; MySQL 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:(("mysqld"))
[::]: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  201M   19G /var/lib/mysql

Step 4: Get your administrator password

sudo cat /root/mysql-credentials.txt

The file is readable by root only:

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

MYSQL_ADMIN_USER=dbadmin
MYSQL_ADMIN_PASSWORD=<MYSQL_ADMIN_PASSWORD>
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_TLS_CA=/etc/mysql/ssl/server-cert.pem

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

Step 5: Sign in

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

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

CURRENT_USER()  VERSION()
root@localhost  8.4.13

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 ~/.mysql-dbadmin.cnf, pointing the client at the instance's own certificate and verifying the server's certificate and name (rename it to ~/.my.cnf if you want it used by default):

( umask 077; sudo awk -F= '/^MYSQL_ADMIN_PASSWORD=/{print "[client]\nuser=dbadmin\npassword=" $2 "\nhost=127.0.0.1\nssl-mode=VERIFY_IDENTITY\nssl-ca=/etc/mysql/ssl/server-cert.pem"}' /root/mysql-credentials.txt > ~/.mysql-dbadmin.cnf )
ls -l ~/.mysql-dbadmin.cnf | awk '{print $1, $NF}'
mysql --defaults-file="$HOME/.mysql-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/.mysql-dbadmin.cnf
CURRENT_USER()
dbadmin@127.0.0.1
Variable_name   Value
Ssl_version TLSv1.3
Variable_name   Value
Ssl_cipher  TLS_AES_128_GCM_SHA256

The new data directory holds only the system databases; nothing from the build is in it:

mysql --defaults-file="$HOME/.mysql-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 MySQL on standard input):

sudo mysql <<'SQL'
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_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 drivers have an ssl-mode=REQUIRED or "require SSL" option; give them /etc/mysql/ssl/server-cert.pem as the CA file to verify the server too), or to the local socket /var/run/mysqld/mysqld.sock. New accounts use the caching_sha2_password plugin, which every current MySQL connector supports.

Step 7: Connect from your own computer

MySQL 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 (the certificate names 127.0.0.1, so verification works through the tunnel):

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

Graphical tools such as MySQL Workbench, DBeaver or HeidiSQL 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/mysql.conf.d/99-cloudimg.cnf
sudo systemctl restart mysql
sudo mysql -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. MySQL loads the new certificate at once, without a restart (ALTER INSTANCE RELOAD TLS); give clients the new /etc/mysql/ssl/server-cert.pem:

sudo mysql-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/mysql.conf.d/99-cloudimg.cnf at your CA bundle), then run sudo mysql -e "ALTER INSTANCE RELOAD TLS;".

Step 10: Security checks

MySQL runs with the cloudimg settings: loopback only, TLS required, LOAD DATA LOCAL and the binary log off, file access confined, and the X Protocol and mysql_native_password plugins disabled:

sudo mysql -e "SELECT @@bind_address, @@require_secure_transport, @@tls_version, @@local_infile, @@log_bin, @@secure_file_priv;"
sudo mysql -e "SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME IN ('mysqlx','mysql_native_password','auth_socket') ORDER BY 1;"

@@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/
PLUGIN_NAME PLUGIN_STATUS
auth_socket ACTIVE
mysqlx  DISABLED
mysql_native_password   DISABLED

A plain text connection is refused, root cannot sign in over the network with any password, and a wrong dbadmin password is refused (the client also warns that a password on its command line is insecure, which is why Step 5 keeps the real one in a file):

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

WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY.
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'dbadmin'@'127.0.0.1' (using password: YES)

root authenticates only through the socket, MySQL's internal accounts are locked, and dbadmin is the only password account:

sudo mysql -e "SELECT CONCAT(User,'@',Host) AS account, plugin, account_locked FROM mysql.user ORDER BY 1;"

account plugin  account_locked
dbadmin@127.0.0.1   caching_sha2_password   N
dbadmin@localhost   caching_sha2_password   N
mysql.infoschema@localhost  caching_sha2_password   Y
mysql.session@localhost caching_sha2_password   Y
mysql.sys@localhost caching_sha2_password   Y
root@localhost  auth_socket N

The certificate was generated on this instance and names its own addresses; the key is readable by MySQL 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 MySQL
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

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

systemctl show mysql mysql-tls-init mysql-datadir-init mysql-firstboot -p Id -p IPAddressDeny

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

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

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

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
Id=mysql-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/mysql-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 MySQL 8.4 LTS repository:

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

cloudimg: MySQL Community Server 8.4.13-1ubuntu24.04 (GPL-2.0-only) from the official MySQL 8.4 LTS APT repository (repo.mysql.com), signing key BCA43417C3B485DD128EC6D4B7B3B788A8D3785C.
mysql-community-server:
  Installed: 8.4.13-1ubuntu24.04
  Candidate: 8.4.13-1ubuntu24.04

Step 11: Changing the administrator password

Change it for both dbadmin host entries (localhost and 127.0.0.1), then update ~/.mysql-dbadmin.cnf and update or delete MYSQL_ADMIN_PASSWORD in /root/mysql-credentials.txt:

sudo mysql <<'SQL'
ALTER USER 'dbadmin'@'localhost' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'127.0.0.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, taken with MySQL stopped for a consistent copy. For a logical backup while MySQL is running:

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

The binary log is off, so point in time recovery is not available out of the box. To use replication or point in time recovery, remove disable_log_bin from /etc/mysql/mysql.conf.d/99-cloudimg.cnf, restart MySQL, and keep the binary logs on the data volume with your backups.

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

sudo apt-get update && sudo apt-get install --only-upgrade mysql-server mysql-community-server mysql-client

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/mysql.conf.d/99-cloudimg.cnf and restart MySQL. To use the MySQL Shell document store, set mysqlx = ON in the same file and restart; it then listens on 127.0.0.1:33060 only, with the same TLS requirement.

Component Path
MySQL data directory /var/lib/mysql (separate EBS volume)
MySQL settings /etc/mysql/mysql.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/mysql-tls-cert
First boot services mysql-tls-init.service, mysql-datadir-init.service, mysql-firstboot.service
Server side file import/export directory /var/lib/mysql-files
Error log /var/log/mysql/error.log
Credentials /root/mysql-credentials.txt (mode 0600)
Repository and pin /etc/apt/sources.list.d/mysql.sources, /etc/apt/preferences.d/cloudimg-mysql.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 MySQL 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 MySQL point releases, and snapshot the data volume regularly.

Support and licensing

MySQL Community 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. MySQL is a trademark of Oracle and/or its affiliates; cloudimg is not affiliated with or endorsed by Oracle.

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