MySQL with phpMyAdmin 8.4 on AWS User Guide

Overview

This guide covers launching and using the MySQL with phpMyAdmin 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. phpMyAdmin is the long established browser console for MySQL: create and browse databases and tables, run SQL, import and export dumps, and manage user accounts and privileges without a desktop client.

What the cloudimg image adds:

• No shared password, anywhere. The image ships an empty MySQL data directory: nothing in it was created on the build machine. On first boot your instance initialises its own data directory and creates every secret: the dbadmin MySQL administrator password, the web sign in password, phpMyAdmin's control user password and its cookie encryption secret. The two logins you need are written to /root/mysql-phpmyadmin-credentials.txt (root only).
• Two logins, both unique to your instance. A browser sign in prompt (HTTP Basic authentication in nginx) protects the whole phpMyAdmin site, and phpMyAdmin's own login then signs you in as dbadmin, a full MySQL administrator. phpMyAdmin refuses root and blank passwords.
• 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 everywhere, required. A MySQL TLS key and certificate are generated on your instance before MySQL first starts, and every TCP connection to MySQL must use TLS 1.2 or 1.3: plain text connections are refused. phpMyAdmin itself connects to MySQL over TCP with TLS and checks MySQL's certificate against the instance's own CA file. MySQL's automatic key generation is switched off, so no key is ever written into the data directory.
• HTTPS from the first boot. nginx serves phpMyAdmin on port 443 with a certificate generated on your instance, port 80 redirects to HTTPS, requests for a host name the instance does not serve are dropped, and phpMyAdmin's cookies are Secure, HttpOnly and SameSite=Strict.
• No setup script on the web. phpMyAdmin's setup script, which lets whoever reaches it first write the configuration, is deleted along with its examples. nginx serves only phpMyAdmin's entry pages and static files; its libraries, templates, Composer vendor/ tree, SQL scripts and configuration file answer 404. nginx allows 10 login attempts a minute from one address (HTTP 429 after that).
• 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, verified software. 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. phpMyAdmin is the current upstream release, checked against its published SHA-256 checksum and its OpenPGP signature by the phpMyAdmin release key (the Ubuntu package is the older 5.2.1).
• Hardened defaults. MySQL, nginx, PHP-FPM and the first boot services are blocked from the instance metadata service, the AMI requires IMDSv2, and PHP is handed the configured host name and the real client address from the connection (never values a client sends).
• 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), phpMyAdmin 5.2.3, nginx, PHP 8.3 FPM, 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, and TCP 443 (phpMyAdmin) and TCP 80 (redirects to HTTPS) from the networks your administrators work from. Never open 3306 or 33060: applications on other hosts reach MySQL through an SSH tunnel or a private network design of your own (Step 7).

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick MySQL with phpMyAdmin 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-phpmyadmin}]'

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 with phpMyAdmin 8.4 on Ubuntu 24.04	ubuntu

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

Step 3: Check the services

First boot creates the data directory, the passwords and the certificates, then nginx and PHP-FPM start:

systemctl is-active mysql nginx php8.3-fpm mysql-phpmyadmin-firstboot
sudo test -f /var/lib/cloudimg/mysql-phpmyadmin-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 and nginx listen on network ports; MySQL listens on the loopback address only (no X Protocol port 33060) and PHP-FPM uses a local socket:

sudo ss -lntp | awk 'NR>1 {print $4, $6}' | grep -v '127.0.0.5' | sed -E 's/,pid=[0-9]+,fd=[0-9]+//g' | sort

0.0.0.0:22 users:(("sshd"),("systemd"))
0.0.0.0:443 users:(("nginx"),("nginx"),("nginx"))
0.0.0.0:80 users:(("nginx"),("nginx"),("nginx"))
127.0.0.1:3306 users:(("mysqld"))
[::]:22 users:(("sshd"),("systemd"))
[::]:443 users:(("nginx"),("nginx"),("nginx"))
[::]:80 users:(("nginx"),("nginx"),("nginx"))

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  203M   19G /var/lib/mysql

Step 4: Get your URL and passwords

sudo cat /root/mysql-phpmyadmin-credentials.txt

The file is readable by root only:

# MySQL with phpMyAdmin 8.4 on Ubuntu 24.04 - generated on first boot by mysql-phpmyadmin-firstboot.service
# These credentials are unique to this instance; none was baked into the image. Keep this file secret.

PHPMYADMIN_URL=https://ec2-<public-ip-dashed>.compute-1.amazonaws.com/

# Step 1 - the browser's sign-in prompt (HTTP Basic gate in front of phpMyAdmin)
WEB_GATE_USER=admin
WEB_GATE_PASSWORD=<WEB_GATE_PASSWORD>

# Step 2 - the phpMyAdmin login form (and the mysql client over TLS on the instance)
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 is not accepted by phpMyAdmin and has no password: on the instance use  sudo mysql  (unix socket only).
# Every TCP connection to MySQL must use TLS (require_secure_transport=ON); MySQL listens on 127.0.0.1:3306 only.
# The web certificate is self signed (minted on this instance); port 80 redirects to https.
# Change the name phpMyAdmin is served as:  sudo phpmyadmin-set-hostname <your-dns-name>
# The datadir /var/lib/mysql is its own EBS volume.
# User guide: https://www.cloudimg.co.uk/guides/mysql-with-phpmyadmin-8-4-on-ubuntu-24-04-aws/

The URL uses the instance's public DNS name from the EC2 instance metadata. The certificate also names the public and private IP addresses, so https://<public-ip>/ works too. If you stop and start the instance without an Elastic IP, the boot service picks up the new address and issues a new certificate for it. To serve phpMyAdmin under your own DNS name, see Step 8.

Step 5: Sign in

Open the PHPMYADMIN_URL in your browser. The certificate is self signed, so your browser asks you to confirm it the first time (or install your own, Step 8).

1. The browser shows its own sign in prompt: enter admin and the WEB_GATE_PASSWORD.
2. phpMyAdmin's login page opens. Sign in as dbadmin with the MYSQL_ADMIN_PASSWORD.

The home page shows the database server: MySQL 8.4.13 reached over TCP/IP on 127.0.0.1, with Server connection: SSL is used, signed in as dbadmin@127.0.0.1. It also shows the web server and the phpMyAdmin version, 5.2.3. The databases are listed on the left:

You can also check both logins from the instance. This self test writes and reads a table as dbadmin over verified TLS and shows that a wrong password, a plain text connection and root over the network are refused by MySQL; it then signs in through the web gate and phpMyAdmin's real login form over HTTPS, and shows that no or a wrong web gate password gets HTTP 401, and that a wrong dbadmin password, root and a blank password are refused by phpMyAdmin:

sudo mysql-phpmyadmin-cred-roundtrip.sh

OK dbadmin round-trips a table over verified TLS (wrong password, plaintext TCP and root over TCP refused; root only via the socket); the web gate refuses no/wrong credentials (401); dbadmin signs in to phpMyAdmin over HTTPS; wrong password, root and blank password refused

Step 6: Work with your databases

Databases lists every database and creates new ones (phpmyadmin holds phpMyAdmin's own settings, bookmarks and history; leave it in place):

Click a table to browse, edit, insert and export rows:

The SQL tab runs any statement and shows the results, which you can export, chart or save as a view:

User accounts manages MySQL users and their privileges. Create a user per application with rights on its own database only, rather than using dbadmin from applications:

The same from the command line on the instance. sudo mysql signs in as MySQL's root over the local socket (no password, and only from the instance):

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

CURRENT_USER()  VERSION()
root@localhost  8.4.13
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
pma@127.0.0.1   caching_sha2_password   N
root@localhost  auth_socket N

root authenticates only through the socket, MySQL's internal accounts are locked, dbadmin is the administrator, and pma is phpMyAdmin's control user, limited to the phpmyadmin database.

To work as dbadmin with the mysql client, 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 MySQL certificate and verifying the server's certificate and name:

( 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-phpmyadmin-credentials.txt > ~/.mysql-dbadmin.cnf )
ls -l ~/.mysql-dbadmin.cnf | awk '{print $1}'
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-------
CURRENT_USER()
dbadmin@127.0.0.1
Variable_name   Value
Ssl_version TLSv1.3
Variable_name   Value
Ssl_cipher  TLS_AES_128_GCM_SHA256

A database and an application user, for example (replace <new-password> with a strong password of your own; 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 '<new-password>';
CREATE USER 'appuser'@'127.0.0.1' IDENTIFIED BY '<new-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. To use a desktop client (MySQL Workbench, DBeaver, the mysql command line) from your computer, copy the instance's MySQL certificate so your client can verify the server, open an SSH tunnel and point the client at the local end of it, signing in as dbadmin (or an application user created for 127.0.0.1). 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

For applications on other EC2 instances, run them on this instance, tunnel the same way, or put MySQL behind your own private network design: change bind-address in /etc/mysql/mysql.conf.d/99-cloudimg.cnf, open 3306 only to those application hosts' security group (never to the internet), and create accounts for their addresses with REQUIRE SSL. Connections still have to use TLS, because require_secure_transport stays on.

Step 8: Your own DNS name and certificates

Point a DNS record at the instance (an Elastic IP keeps the address stable), then set the name phpMyAdmin is served as. This mints a new self signed web certificate for the name, updates nginx and the URL in /root/mysql-phpmyadmin-credentials.txt, and keeps the name across reboots:

sudo phpmyadmin-set-hostname <your-domain>

To use a web certificate from a public or internal certificate authority instead (for example one issued by Let's Encrypt for <your-domain>), copy it over the generated pair and reload nginx (running phpmyadmin-set-hostname again replaces it with a self signed one):

sudo install -m 0644 <your-domain>.crt /etc/ssl/phpmyadmin/phpmyadmin.crt
sudo install -m 0600 <your-domain>.key /etc/ssl/phpmyadmin/phpmyadmin.key
sudo nginx -t && sudo systemctl reload nginx

MySQL has its own certificate (/etc/mysql/ssl/). It names localhost, 127.0.0.1 and the instance's private and public EC2 names and addresses. If database clients reach MySQL by a DNS name of your own, re-issue it with that name added; MySQL loads the new certificate at once (ALTER INSTANCE RELOAD TLS) and phpMyAdmin verifies it from then on, with no restart:

sudo mysql-tls-cert --renew db.example.internal

Step 9: Security checks

Without the web gate login the phpMyAdmin page answers 401. The setup script, phpMyAdmin's libraries, the Composer vendor/ tree, the configuration file and the SQL scripts are not served at all (404), and plain HTTP redirects to HTTPS:

H=$(sudo grep '^PHPMYADMIN_URL=' /root/mysql-phpmyadmin-credentials.txt | cut -d= -f2- | sed -E 's#https://([^/]+)/#\1#')
for p in /index.php /setup/ /libraries/classes/Version.php /vendor/autoload.php /config.inc.php /sql/create_tables.sql; do
  curl -sk -o /dev/null -w "%{http_code}  $p\n" --resolve "$H:443:127.0.0.1" "https://$H$p"
done
curl -s -o /dev/null -w 'http:// -> %{http_code} %{redirect_url}\n' --resolve "$H:80:127.0.0.1" "http://$H/"
ls /usr/share/phpmyadmin/setup 2>&1 | sed 's/^ls: //'
[ "$(curl -sk -o /dev/null -w '%{http_code}' --resolve "$H:443:127.0.0.1" "https://$H/setup/")" = "404" ]

401  /index.php
404  /setup/
404  /libraries/classes/Version.php
404  /vendor/autoload.php
404  /config.inc.php
404  /sql/create_tables.sql
http:// -> 301 https://ec2-<public-ip-dashed>.compute-1.amazonaws.com/
cannot access '/usr/share/phpmyadmin/setup': No such file or directory

phpMyAdmin refuses root and password-less logins, can only reach the local server, and connects to it over TCP with TLS, verifying MySQL's certificate against the instance's CA file. Its configuration file holds no secret: the cookie encryption secret and the control user's password live in /etc/phpmyadmin/cloudimg-secrets.ini, generated on first boot and readable by root and the web server only:

sudo grep -E "'(host|port|connect_type|ssl|ssl_ca|ssl_verify|AllowRoot|AllowNoPassword)'\]|AllowArbitraryServer'\]" /usr/share/phpmyadmin/config.inc.php
sudo stat -c '%A %U %G %n' /etc/phpmyadmin/cloudimg-secrets.ini /usr/share/phpmyadmin/config.inc.php /etc/nginx/cloudimg-phpmyadmin.htpasswd

$cfg['Servers'][$i]['host']            = '127.0.0.1';
$cfg['Servers'][$i]['port']            = '3306';
$cfg['Servers'][$i]['connect_type']    = 'tcp';
$cfg['Servers'][$i]['ssl']             = true;
$cfg['Servers'][$i]['ssl_ca']          = '/etc/mysql/ssl/server-cert.pem';
$cfg['Servers'][$i]['ssl_verify']      = true;
$cfg['Servers'][$i]['AllowRoot']       = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$cfg['AllowArbitraryServer'] = false;
-rw-r----- root www-data /etc/phpmyadmin/cloudimg-secrets.ini
-rw-r----- root www-data /usr/share/phpmyadmin/config.inc.php
-rw-r----- root www-data /etc/nginx/cloudimg-phpmyadmin.htpasswd

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, and root cannot sign in over the network with any password (the client also warns that a password on its command line is insecure, which is why Step 6 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

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)

Both certificates were generated on this instance and name its own addresses; the MySQL key is readable by MySQL only:

H=$(sudo grep '^PHPMYADMIN_URL=' /root/mysql-phpmyadmin-credentials.txt | cut -d= -f2- | sed -E 's#https://([^/]+)/#\1#')
echo | openssl s_client -connect 127.0.0.1:443 -servername "$H" 2>/dev/null | openssl x509 -noout -subject -ext subjectAltName
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 = ec2-<public-ip-dashed>.compute-1.amazonaws.com, O = cloudimg
X509v3 Subject Alternative Name:
    DNS:ec2-<public-ip-dashed>.compute-1.amazonaws.com, IP Address:<public-ip>, IP Address:<private-ip>, IP Address:127.0.0.1, DNS:localhost
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

None of the services can reach the instance metadata service:

systemctl show mysql mysql-tls-init mysql-datadir-init mysql-phpmyadmin-firstboot nginx php8.3-fpm -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-phpmyadmin-firstboot.service

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

IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32
Id=php8.3-fpm.service

nginx hands PHP the configured host name and the client address from the connection, never values a client sends, and keeps the web gate's credentials away from PHP:

grep -E 'fastcgi_param (HTTP_HOST|HTTPS|HTTP_X_FORWARDED_FOR|HTTP_X_REAL_IP|HTTP_AUTHORIZATION) ' /etc/nginx/cloudimg-phpmyadmin-fastcgi.conf

fastcgi_param HTTP_HOST $pma_host;
fastcgi_param HTTPS on;
fastcgi_param HTTP_X_FORWARDED_FOR $remote_addr;
fastcgi_param HTTP_X_REAL_IP $remote_addr;
fastcgi_param HTTP_AUTHORIZATION "";

The packages come from the signed MySQL 8.4 LTS repository, and phpMyAdmin is the verified upstream release:

cat /etc/cloudimg/mysql-release.txt /etc/cloudimg/phpmyadmin-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.
cloudimg: phpMyAdmin 5.2.3 (GPL-2.0-only) from files.phpmyadmin.net, sha256 12ba1c425fa4071abbd4e7668c9ebdeac0b0755a467a6d6d5026122bb47c102b, OpenPGP signature by 3D06A59ECE730EB71B511C17CE752F178259BD92; setup/ and examples/ removed.
mysql-community-server:
  Installed: 8.4.13-1ubuntu24.04
  Candidate: 8.4.13-1ubuntu24.04

Step 10: Changing passwords

• dbadmin: in phpMyAdmin choose Change password on the home page (it changes the dbadmin entry you signed in as, dbadmin@127.0.0.1), or run the statements below 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-phpmyadmin-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

• Web gate (admin): write a new hash into the nginx password file (the password stays in a shell variable, never on a command line), then update or delete WEB_GATE_PASSWORD in the credentials file. nginx reads the file on every request, so no reload is needed:

NEWPW='<new-password>'
printf 'admin:%s\n' "$(printf '%s\n' "$NEWPW" | openssl passwd -6 -stdin)" | sudo tee /etc/nginx/cloudimg-phpmyadmin.htpasswd >/dev/null
sudo chown root:www-data /etc/nginx/cloudimg-phpmyadmin.htpasswd && sudo chmod 0640 /etc/nginx/cloudimg-phpmyadmin.htpasswd
unset NEWPW

Step 11: Backup 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

phpMyAdmin's Export and Import tabs do the same for single databases from the browser (uploads up to 256 MB). 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

Run sudo apt-get update && sudo apt-get upgrade to apply everything else. phpMyAdmin is installed from the upstream release, so upgrade it by replacing /usr/share/phpmyadmin with a newer release (keep config.inc.php, and delete the new release's setup/ and examples/ directories).

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.

Component	Path
MySQL data directory	/var/lib/mysql (separate EBS volume)
MySQL settings	/etc/mysql/mysql.conf.d/99-cloudimg.cnf
MySQL certificate and key	/etc/mysql/ssl/server-cert.pem, /etc/mysql/ssl/server-key.pem
MySQL certificate helper	/usr/local/sbin/mysql-tls-cert
MySQL repository and pin	/etc/apt/sources.list.d/mysql.sources, /etc/apt/preferences.d/cloudimg-mysql.pref
phpMyAdmin	/usr/share/phpmyadmin
phpMyAdmin configuration	/usr/share/phpmyadmin/config.inc.php (mode 0640, no secrets)
phpMyAdmin secrets	/etc/phpmyadmin/cloudimg-secrets.ini (mode 0640)
nginx site	/etc/nginx/sites-available/cloudimg-phpmyadmin
Web gate password file	/etc/nginx/cloudimg-phpmyadmin.htpasswd (mode 0640)
Web certificate and key	/etc/ssl/phpmyadmin/
Boot services	mysql-tls-init.service, mysql-datadir-init.service, mysql-phpmyadmin-firstboot.service
Host name helper	/usr/local/sbin/phpmyadmin-set-hostname
Error log	/var/log/mysql/error.log
Credentials	/root/mysql-phpmyadmin-credentials.txt (mode 0600)

Step 12: Security recommendations

• Allow port 22 from your admin network only, and ports 80 and 443 only from the networks your administrators work from. Keep 3306 closed, or open it to your application hosts only (Step 7); never to the internet.
• Use your own DNS name and a certificate your browsers trust (Step 8).
• Create a MySQL user per application with rights on its own database only, and keep dbadmin for administration.
• Change both passwords if they may have been shared (Step 10), apply MySQL point releases, keep the operating system patched, and snapshot the data volume regularly.

Support and licensing

MySQL Community Server and phpMyAdmin are 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 or the phpMyAdmin project.

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