MySQL 9.7.4

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick MySQL 9.7.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: an 8 GiB root volume and a 20 GiB data volume (/dev/sdf) for /var/lib/mysql. Size the data volume at launch to fit your databases.

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=mysql-9-7}]'

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 as the user for your version:

Version SSH user

MySQL 9.7.4 on Ubuntu 24.04 ubuntu

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

On first boot, the instance creates the certificate, initializes the data directory, starts MySQL and then creates the administrator account. Confirm that all four units are active and that the first-boot marker exists:

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

Expected output:

active active active active FIRSTBOOT_DONE

Check the listeners. Only SSH listens on a network port, while 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"))

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

Read the credentials file as root:

sudo cat /root/mysql-credentials.txt

Only root can read this file. Its contents look like this:

# MySQL 9.7 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-9-7-4/

As the operating system's root user, sign in to MySQL's root account through the local socket. No password is needed:

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

CURRENT_USER() VERSION() root@localhost 9.7.4

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 following commands write ~/.mysql-dbadmin.cnf. The file points the client at the instance's own certificate and verifies the server's certificate and name. Rename it to ~/.my.cnf if you want the client to use it 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. To verify the server as well, give the driver /etc/mysql/ssl/server-cert.pem as the CA file.

New accounts use the caching_sha2_password plugin, which every current MySQL connector supports. MySQL 9.7 has no mysql_native_password, so IDENTIFIED WITH mysql_native_password is refused (Step 10). An application whose driver speaks only that older method needs a current connector.

MySQL is not exposed to the network, so open an SSH tunnel from your computer and connect through it. Copy the instance's certificate first so that 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, 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-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 must still 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 disabled. The 9.7 server has no mysql_native_password plugin at all, so the query finds no row for it. New accounts default to caching_sha2_password (authentication_policy *,,):

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','caching_sha2_password') ORDER BY 1;"
sudo mysql -e "SELECT @@authentication_policy;"

@@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 caching_sha2_password ACTIVE mysqlx DISABLED @@authentication_policy *,,

MySQL refuses to create an account with the legacy method, and creates nothing:

sudo mysql -e "CREATE USER 'legacyapp'@'localhost' IDENTIFIED WITH mysql_native_password BY 'not-used';" 2>&1 || true
sudo mysql -N -e "SELECT COUNT(*) FROM mysql.user WHERE User='legacyapp';"

ERROR 1524 (HY000) at line 1: Plugin 'mysql_native_password' is not loaded 0

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 password 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)

The root account authenticates only through the socket, MySQL's built-in 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=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32 Id=mysql.service IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32 Id=mysql-tls-init.service IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32 Id=mysql-datadir-init.service IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32 Id=mysql-firstboot.service

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

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

Confirm that the packages come from the signed MySQL 9.7 LTS repository:

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

sac: MySQL Community Server 9.7.4-1ubuntu24.04 (GPL-2.0-only) from the official MySQL 9.7 LTS APT repository (repo.mysql.com), signing key BCA43417C3B485DD128EC6D4B7B3B788A8D3785C. mysql-community-server: Installed: 9.7.4-1ubuntu24.04 Candidate: 9.7.4-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 this command:

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, restart MySQL, and keep the binary logs on the data volume with your backups.

Ubuntu security updates install automatically. MySQL comes from the MySQL 9.7 LTS repository, so you apply its point releases (9.7.5 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

Moving databases from MySQL 8.4. Oracle supports upgrading from 8.4 LTS to 9.7 LTS. Before you load a dump from an 8.4 server, check the old server for accounts that use mysql_native_password (SELECT User, Host FROM mysql.user WHERE plugin='mysql_native_password';). Switch them to caching_sha2_password there, because 9.7 cannot authenticate them.

Tuning. 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. The X Protocol then listens on 127.0.0.1:33060 only, with the same TLS requirement.

Key files and services:

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). Never open it to the internet.
• Create a MySQL user for each 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.