MySQL 8.4 LTS

Prerequisites

Before you deploy this image, make sure you have:

• An Azure subscription with permission to create virtual machines
• An SSH key pair (or a password) for the azureuser admin account
• The MySQL client on any machine you want to connect from later (only needed if you open remote access)

Step 1 - Deploy from the Azure Portal

• Find MySQL 8.4 LTS on Ubuntu 24.04 by SecureAnyCloud in the Azure Marketplace and select Create.
• On the Basics tab, choose a resource group, a region and a VM size. Standard_D2s_v3 (2 vCPU, 8 GB RAM) is a good starting point. Size up for larger working sets.
• Under Administrator account, use the username azureuser with an SSH public key (recommended) or a password. The image honours whichever you choose.
• On the Networking tab, allow SSH (22) only, and preferably only from your own address. Do not open 3306 or 33060 to the internet. MySQL is bound to localhost anyway. The Allow remote access section below shows how to open it to your own network ranges only.
• Select Review + create, then Create.

Step 2 - Deploy with the Azure CLI

Accept the Marketplace terms once per subscription, then create the VM.

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:mysql-8-4-lts-ubuntu-24-04:standard:latest
az vm create \
  --resource-group <your-resource-group> \
  --name mysql-84-lts \
  --image sac1647283583153:mysql-8-4-lts-ubuntu-24-04:standard:latest \
  --size Standard_D2s_v3 \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_rsa.pub \
  --public-ip-sku Standard \
  --nsg-rule SSH

--nsg-rule SSH creates a Network Security Group that allows SSH only. Do not run az vm open-port --port 3306. That command opens MySQL to every address on the internet.

Step 3 - Connect over SSH and confirm first boot finished

Connect to the VM over SSH.

ssh azureuser@<vm-ip>

First boot creates the TLS certificate, the data directory and the root password. It then starts MySQL and checks that the new credential works over the socket and over TLS. This normally finishes within a minute of the VM starting.

Confirm that all four units are active and that first boot completed.

systemctl is-active mysql-tls-init mysql-datadir-init mysql mysql-firstboot
sudo test -f /var/lib/sac/data/mysql-firstboot.done && echo "first boot complete"

active active active active first boot complete

Step 4 - Your credentials

The root password was created on this VM's first boot and is unique to it. It is recorded in a file that only root can read.

sudo cat /root/mysql-credentials.txt

# MySQL 8.4 LTS on Ubuntu 24.04 - generated on first boot by mysql-datadir-init.service
# This password is unique to this VM; none was baked into the image. Keep this file secret. MYSQL_ROOT_USER=root MYSQL_ROOT_PASSWORD=<28 random letters and digits> MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_SOCKET=/var/run/mysqld/mysqld.sock MYSQL_DEFAULT_DATABASE=sac MYSQL_TLS_CA=/etc/mysql/ssl/server-cert.pem MYSQL_CLIENT_OPTIONS=/root/.my.cnf

The same password is in /root/.my.cnf, MySQL's own client option file for root (mode 0600). The MySQL client reads that file, so sudo mysql signs you in as MySQL's root without you typing the password on a command line.

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

CURRENT_USER() VERSION() root@localhost 8.4.13

Step 5 - TLS is required on every TCP connection

Connect over TCP to 127.0.0.1 and verify the server's certificate against this VM's own CA file.

sudo mysql --protocol=TCP -h 127.0.0.1 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ssl/server-cert.pem \
  -e "SELECT CURRENT_USER(); SHOW SESSION STATUS LIKE 'Ssl_version';"

CURRENT_USER() root@127.0.0.1 Variable_name Value Ssl_version TLSv1.3

The server refuses a connection without TLS.

sudo mysql --protocol=TCP -h 127.0.0.1 --ssl-mode=DISABLED -e "SELECT 1" || echo "plaintext connection refused (expected)"

ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON. plaintext connection refused (expected)

The certificate was created on this VM at first boot. Its names cover localhost, 127.0.0.1, the VM's host name and its private address. To inspect it, run:

openssl x509 -in /etc/mysql/ssl/server-cert.pem -noout -subject -enddate -ext subjectAltName

Step 6 - Run a SQL round-trip

A SecureAnyCloud database is created for you on first boot. Create a table, insert a row, read it back and clean up.

sudo mysql sac <<'SQL' CREATE TABLE IF NOT EXISTS greetings ( id INT PRIMARY KEY AUTO_INCREMENT, msg VARCHAR(128) ); INSERT INTO greetings (msg) VALUES ('Hello from sac'); SELECT * FROM greetings; DROP TABLE greetings; SQL

id msg 1 Hello from sac

Check the security settings in effect.

sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('bind_address','require_secure_transport','tls_version','log_bin','skip_name_resolve','local_infile');"

Variable_name Value bind_address 127.0.0.1 local_infile OFF log_bin ON require_secure_transport ON skip_name_resolve ON tls_version TLSv1.2,TLSv1.3

Step 7 - What is listening

List the ports MySQL is listening on.

sudo ss -ltnp | grep -E ':(3306|33060)\b'

LISTEN 0 151 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=...,fd=...))

MySQL accepts connections from this VM only. The X Protocol (33060) is off. If you need it, set mysqlx = ON in /etc/mysql/mysql.conf.d/99-sac.cnf. It then listens on the loopback interface only, with the same TLS requirement.

Allow remote access (to your own network only)

Applications on the same VM can connect to 127.0.0.1:3306 straight away. For applications on other machines, open MySQL to your own address ranges only, never to Any or the internet. Give each application its own account that must use TLS.

1. Allow port 3306 in the Network Security Group from your range only. Replace 10.20.0.0/24 with the subnet or address your application servers use.

az network nsg rule create --resource-group <your-resource-group> --nsg-name <your-nsg> \
  --name allow-mysql-from-app-subnet --priority 1010 --direction Inbound --access Allow --protocol Tcp \
  --source-address-prefixes 10.20.0.0/24 --destination-port-ranges 3306

2. Bind MySQL to the VM's private address and restart it. Find the address with hostname -I.

sudo sed -i 's/^bind-address = 127.0.0.1$/bind-address = <private-ip>/' /etc/mysql/mysql.conf.d/99-sac.cnf
sudo systemctl restart mysql

3. Add the name or address your clients will use to the certificate, so they can verify it (VERIFY_IDENTITY). The certificate is re-issued and MySQL reloads it without a restart.

sudo mysql-tls-cert --renew <your-domain>

4. Create an account for the application that must use TLS. The password is read from the terminal and passed to MySQL on standard input, so it never appears on a command line. Use letters, digits and punctuation other than quotes.

read -rs -p 'Password for the app account (<new-password>): ' APP_PW; echo printf "CREATE USER 'app'@'10.20.0.%%' IDENTIFIED BY '%s' REQUIRE SSL;\nGRANT ALL PRIVILEGES ON sac.* TO 'app'@'10.20.0.%%';\n" "$APP_PW" | sudo mysql unset APP_PW

5. Connect from an application server, using the CA file copied from /etc/mysql/ssl/server-cert.pem on the VM. It is a public certificate, so it is safe to copy. The -p option makes the client prompt for the password.

mysql -h <private-ip> -u app -p --ssl-mode=VERIFY_IDENTITY --ssl-ca=server-cert.pem sac

Root can only sign in from the VM itself (root@localhost and root@127.0.0.1). Keep it that way, and use per-application accounts over the network.

Binary logging and replication

Binary logging is on (the MySQL 8.4 default), so the server is ready to act as a replication source.

sudo mysql -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'server_id';"

Variable_name Value log_bin ON Variable_name Value server_id 1

Give every server in a replication topology its own server_id. Set the format and retention in a file of your own, for example /etc/mysql/mysql.conf.d/50-replication.cnf, then restart MySQL.

[mysqld] server_id = 1 binlog_format = ROW binlog_expire_logs_seconds = 604800 gtid_mode = ON enforce_gtid_consistency = ON

On the source, create a replication account that must use TLS. The password is read from the terminal.

read -rs -p 'Password for the repl account (<new-password>): ' REPL_PW; echo printf "CREATE USER 'repl'@'10.20.0.%%' IDENTIFIED BY '%s' REQUIRE SSL;\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.20.0.%%';\n" "$REPL_PW" | sudo mysql unset REPL_PW

On the replica (a second VM from this image, with server_id = 2 and remote access opened from the replica to the source as above), point it at the source over TLS.

read -rs -p 'Password of the repl account (<new-password>): ' REPL_PW; echo printf "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.20.0.4', SOURCE_USER='repl', SOURCE_PASSWORD='%s', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1;\nSTART REPLICA;\n" "$REPL_PW" | sudo mysql unset REPL_PW
sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E 'Replica_(IO|SQL)_Running:'

Tune the InnoDB buffer pool

The buffer pool is MySQL's most important memory setting. On Standard_D2s_v3 (8 GB RAM), 5 GB is a reasonable starting point. Put the setting in your own file, for example /etc/mysql/mysql.conf.d/60-tuning.cnf, and restart MySQL.

[mysqld] innodb_buffer_pool_size = 5G

Confirm the setting in effect.

sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Change the root password

The new password is read from the terminal and passed to MySQL on standard input. Update /root/.my.cnf at the same time so that sudo mysql keeps working.

read -rs -p 'New MySQL root password (<new-password>): ' NEWPW; echo printf "ALTER USER 'root'@'localhost', 'root'@'127.0.0.1' IDENTIFIED BY '%s';\n" "$NEWPW" | sudo mysql printf '[client]\nuser=root\npassword="%s"\n' "$NEWPW" | sudo tee /root/.my.cnf >/dev/null unset NEWPW
sudo sed -i 's|^MYSQL_ROOT_PASSWORD=.*|MYSQL_ROOT_PASSWORD=<set-at-first-boot> - see /root/.my.cnf)|' /root/mysql-credentials.txt

Back up your databases

mysqldump signs in through /root/.my.cnf too. Write the dump somewhere that only root can read.

sudo sh -c 'umask 077; mysqldump --single-transaction --routines --events --all-databases | gzip > /root/mysql-backup-$(date +%F).sql.gz'

Service management

Check the service status, restart the service, or read the recent journal.

sudo systemctl status mysql --no-pager

sudo systemctl restart mysql

sudo journalctl -u mysql -n 50 --no-pager

The start order is fixed: mysql-tls-init (this VM's certificate), then mysql-datadir-init (this VM's data directory and root password, first boot only), then mysql. MySQL requires both, so it never starts without a certificate or a root password. To check the dependencies, run:

systemctl show -p Requires mysql.service | tr ' =' '\n\n' | grep -E '^mysql-(tls|datadir)-init' | sort

mysql-datadir-init.service mysql-tls-init.service

Files and settings

These are the paths that matter most, and what each one is for.

• /etc/mysql/mysql.conf.d/99-sac.cnf: SecureAnyCloud security defaults (bind address, TLS, X Protocol off)
• /etc/mysql/ssl/server-cert.pem: this VM's TLS certificate, also the CA file for clients
• /etc/mysql/ssl/server-key.pem: this VM's TLS private key (0600 mysql)
• /root/mysql-credentials.txt: the root password created on first boot (0600 root)
• /root/.my.cnf: root's MySQL client options, used by sudo mysql (0600 root)
• /var/lib/mysql/: the data directory, created on first boot
• /var/lib/mysql-files/: the only directory that SELECT ... INTO OUTFILE and LOAD DATA may use
• /var/log/mysql/error.log: the MySQL error log
• /usr/local/sbin/mysql-tls-cert: re-issues the certificate with sudo mysql-tls-cert --renew <name>

The MySQL package and repository

MySQL comes from Oracle's repository at repo.mysql.com, pinned with Signed-By to the MySQL Release Engineering key. An APT preference keeps the MySQL packages on that repository (8.4 point releases only, never Ubuntu's 8.0 packages). Automatic security updates remain on.

apt-cache policy mysql-community-server cat /etc/apt/sources.list.d/mysql.sources

# sac: MySQL Community Server 8.4 LTS - Oracle's official MySQL APT repository Types: deb URIs: https://repo.mysql.com/apt/ubuntu Suites: noble Components:
mysql-8.4-lts Architectures: amd64 Signed-By: /etc/apt/keyrings/mysql-keyring.gpg

SSH and system hardening

Root logins, empty passwords and X11 forwarding are switched off in /etc/ssh/sshd_config.d/00-sac.conf, which sorts before every other SSH setting. Whether SSH accepts a password follows the choice you made when you created the VM (SSH key or password). To check the effective values, run:

sudo sshd -T | grep -E '^(permitrootlogin|permitemptypasswords|x11forwarding|pubkeyauthentication) ' | sort

permitemptypasswords no permitrootlogin no pubkeyauthentication yes x11forwarding no

The firmware update refresh timer (fwupd-refresh) is masked. An Azure VM has no firmware that LVFS can update, so the timer would only add an outbound call. The fwupd package itself stays installed.

systemctl show -p Id -p UnitFileState fwupd-refresh.timer fwupd-refresh.service

Id=fwupd-refresh.timer UnitFileState=masked Id=fwupd-refresh.service UnitFileState=masked

Nothing from the image build ships on your VM. There is no data directory, root password, TLS key or certificate (yours are created on first boot), no firmware client key and no SSH host key. The free space of the disk was zeroed before the image was captured. The MySQL units cannot reach the Azure instance metadata service.

Troubleshooting

MySQL did not start on first boot. It is designed to stay stopped if first boot could not create the certificate, the data directory or the root password. The journal of the first-boot units shows why.

sudo journalctl -u mysql-tls-init -u mysql-datadir-init -u mysql -u mysql-firstboot -n 50 --no-pager

Reading the MySQL error log.

sudo tail -n 50 /var/log/mysql/error.log

"Connections using insecure transport are prohibited". Your client connected over TCP without TLS. Use --ssl-mode=VERIFY_IDENTITY --ssl-ca=server-cert.pem (or at least --ssl-mode=REQUIRED). Alternatively, connect through the socket with sudo mysql on the VM.

A client cannot verify the certificate name. Add the name or address the client uses with sudo mysql-tls-cert --renew <name>.

Cannot connect from another machine. Check all three of these:

• The NSG rule allows your source range on port 3306.
• bind-address in /etc/mysql/mysql.conf.d/99-sac.cnf is the private address.
• The account's host (for example 'app'@'10.20.0.%') matches the client's address.

Support

SecureAnyCloud provides and supports this image, with 24/7 support. Contact us at sales@secureanycloud.com. For upstream documentation, see the MySQL 8.4 Reference Manual.

MySQL is a registered trademark of Oracle Corporation and/or its affiliates. SecureAnyCloud is not affiliated with Oracle.

Deploy on Azure

Launch mysql-8-4-lts with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com