MySQL 8.4

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, choose your Region, then choose Launch through EC2.

Choose m5.large, your key pair and a security group as described 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.

Version SSH user

MySQL 8.4 on Ubuntu 24.04 ubuntu

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

First boot creates the certificate, initializes 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/sac/data/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

sudo cat /root/mysql-credentials.txt

Only root can read the file. It looks like this:

# 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://secureanycloud.com/stacks/mysql-8-4/

As the operating system's root user, sign in to MySQL's root account through the local socket. It needs 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.

The command below writes ~/.mysql-dbadmin.cnf. It points the client at the instance's own certificate and verifies the server's certificate and name. Rename the file 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 runs as 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

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 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, or to the local socket /var/run/mysqld/mysqld.sock. 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.

New accounts use the caching_sha2_password plugin, which every current MySQL connector supports.

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 and HeidiSQL offer the same SSH tunnel and "require SSL" settings.

To let application servers in your VPC connect directly, change the listening address and open 3306 to your application subnet only. Then 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-sac.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.

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 at 0600 and owned by mysql. If a CA issued your certificate, point ssl_ca in /etc/mysql/mysql.conf.d/99-sac.cnf at your CA bundle. Then run sudo mysql -e "ALTER INSTANCE RELOAD TLS;".

MySQL runs with the SecureAnyCloud 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 own system 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. Only MySQL 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 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/sac/mysql-release.txt apt-cache policy mysql-community-server | sed -n '1,3p'

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

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

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, use:

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-sac.cnf and restart MySQL. 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 you apply its point releases (8.4.14 and later) 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-sac.cnf and restart MySQL.

To use the MySQL Shell document store, set mysqlx = ON in the same file and restart MySQL. The X Protocol 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-sac.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/sac-mysql.pref

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