MariaDB with phpMyAdmin 10.11 on AWS User Guide

Overview

This guide covers launching and using the MariaDB with phpMyAdmin 10.11 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.

MariaDB is the open source relational database server that began as a fork of MySQL and stays compatible with MySQL clients, drivers and SQL. phpMyAdmin is the long established browser console for MySQL and MariaDB: 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:

• 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 MariaDB administrator. Both passwords are generated on first boot and written to /root/mariadb-phpmyadmin-credentials.txt (root only). phpMyAdmin refuses root and blank passwords; MariaDB's root account works only from the instance itself (sudo mariadb).
• 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.
• 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.
• Login rate limiting. nginx allows 10 login attempts a minute from one address (HTTP 429 after that), on top of the per instance passwords.
• A hardened MariaDB. MariaDB listens on 127.0.0.1 only, with no anonymous accounts, no test database, LOAD DATA LOCAL off, server side file access confined to /var/lib/mysql-files, and no binary or general query log. phpMyAdmin talks to it over the local socket and cannot be pointed at any other server.
• Your data on its own disk. The MariaDB data directory /var/lib/mysql is a separate 20 GiB EBS volume you can grow or snapshot on its own.
• Hardened defaults. nginx, PHP-FPM, MariaDB and the first boot service 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: MariaDB 10.11.14 (the Ubuntu 24.04 package, security updates from Ubuntu), phpMyAdmin 5.2.3 (the current upstream release, checksum verified; the Ubuntu package is the older 5.2.1), 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: applications on other hosts reach MariaDB 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 MariaDB with phpMyAdmin 10.11 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=mariadb-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
MariaDB with phpMyAdmin 10.11 on Ubuntu 24.04	ubuntu

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

Step 3: Check the services

First boot sets the passwords and the certificate, then nginx and PHP-FPM start:

systemctl is-active nginx php8.3-fpm mariadb mariadb-phpmyadmin-firstboot
sudo test -f /var/lib/cloudimg/mariadb-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; MariaDB listens on the loopback address 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:(("mariadbd"))
[::]: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  127M   19G /var/lib/mysql

Step 4: Get your URL and passwords

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

The file is readable by root only:

# MariaDB with phpMyAdmin - generated on first boot by mariadb-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 mariadb -u dbadmin -p on the instance)
MARIADB_ADMIN_USER=dbadmin
MARIADB_ADMIN_PASSWORD=<MARIADB_ADMIN_PASSWORD>

# root is not accepted by phpMyAdmin; on the instance use:  sudo mariadb   (unix socket)
# The 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>
# MariaDB listens on 127.0.0.1:3306 only; the datadir /var/lib/mysql is its own EBS volume.
# User guide: https://www.cloudimg.co.uk/guides/mariadb-with-phpmyadmin-10-11-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 MARIADB_ADMIN_PASSWORD.

The home page shows the database server (MariaDB 10.11.14 over the local socket, signed in as dbadmin@localhost), 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 script signs in through the web gate and phpMyAdmin's real login form over HTTPS, then 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 mariadb-phpmyadmin-cred-roundtrip.sh

OK web gate refuses no/wrong credentials (401); dbadmin signs in 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 MariaDB 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 mariadb signs in as MariaDB's root over the local socket (no password, and only from the instance):

sudo mariadb -e "SELECT CURRENT_USER(), VERSION();"
sudo mariadb -e "SELECT User, Host, JSON_VALUE(Priv,'$.plugin') AS plugin FROM mysql.global_priv ORDER BY User, Host;"

CURRENT_USER()  VERSION()
root@localhost  10.11.14-MariaDB-0ubuntu0.24.04.1
User    Host    plugin
dbadmin 127.0.0.1   mysql_native_password
dbadmin ::1 mysql_native_password
dbadmin localhost   mysql_native_password
mariadb.sys localhost   mysql_native_password
mysql   localhost   mysql_native_password
pma localhost   mysql_native_password
root    localhost   mysql_native_password

root and mysql have no usable password (they authenticate by the Linux user over the socket), mariadb.sys is a locked system account, and pma is phpMyAdmin's control user, limited to the phpmyadmin database. To sign in as dbadmin from a shell, run mariadb -u dbadmin -p and enter the password when prompted.

A database and an application user, for example (replace <new-password> with a strong password of your own):

sudo mariadb <<'SQL'
CREATE DATABASE appdb CHARACTER SET utf8mb4;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '<new-password>';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
SQL

Step 7: Connect from your own computer

MariaDB is not exposed to the network. To use a desktop client (MySQL Workbench, DBeaver, the mariadb or mysql command line) from your computer, open an SSH tunnel and point the client at 127.0.0.1:3306 on your side, signing in as dbadmin (or an application user created for 127.0.0.1):

ssh -i <your-key.pem> -N -L 3306:127.0.0.1:3306 ubuntu@<public-ip>

For applications on other EC2 instances, run them on this instance, tunnel the same way, or put MariaDB behind your own private network design; if you change bind-address in /etc/mysql/mariadb.conf.d/99-cloudimg-phpmyadmin.cnf, open 3306 only to those application hosts' security group, never to the internet, and create users for their addresses.

Step 8: Your own DNS name and certificate

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 certificate for the name, updates nginx and the URL in /root/mariadb-phpmyadmin-credentials.txt, and keeps the name across reboots:

sudo phpmyadmin-set-hostname <your-domain>

To use a 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

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/mariadb-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 and can only reach the local server; MariaDB runs with the cloudimg settings:

sudo grep -E "AllowRoot|AllowNoPassword|AllowArbitraryServer|connect_type" /usr/share/phpmyadmin/config.inc.php
sudo mariadb -e "SELECT @@bind_address, @@local_infile, @@log_bin, @@general_log, @@secure_file_priv;"
cat /etc/cloudimg/phpmyadmin-release.txt

$cfg['Servers'][$i]['connect_type']    = 'socket';
$cfg['Servers'][$i]['AllowRoot']       = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$cfg['AllowArbitraryServer'] = false;
@@bind_address  @@local_infile  @@log_bin   @@general_log   @@secure_file_priv
127.0.0.1   0   0   0   /var/lib/mysql-files/
cloudimg: phpMyAdmin 5.2.3 (GPL-2.0-only) from files.phpmyadmin.net, sha256 12ba1c425fa4071abbd4e7668c9ebdeac0b0755a467a6d6d5026122bb47c102b; setup/ and examples/ removed.

The certificate was generated on this instance and names its own addresses:

H=$(sudo grep '^PHPMYADMIN_URL=' /root/mariadb-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

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

None of the services can reach the instance metadata service:

systemctl show nginx php8.3-fpm mariadb mariadb-phpmyadmin-firstboot -p Id -p IPAddressDeny

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

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

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

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
Id=mariadb-phpmyadmin-firstboot.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 "";

Step 10: Changing passwords

• dbadmin: in phpMyAdmin choose Change password on the home page, or run the statement below for each of the three dbadmin host entries (localhost, 127.0.0.1, ::1). Then update or delete MARIADB_ADMIN_PASSWORD in /root/mariadb-phpmyadmin-credentials.txt.

sudo mariadb <<'SQL'
ALTER USER 'dbadmin'@'localhost' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'127.0.0.1' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'::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 (with the instance stopped, or after FLUSH TABLES WITH READ LOCK for a consistent copy). For a logical backup while MariaDB is running:

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

phpMyAdmin's Export and Import tabs do the same for single databases from the browser (uploads up to 256 MB).

Operating system and MariaDB security updates install automatically from Ubuntu; 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).

Component	Path
MariaDB data directory	/var/lib/mysql (separate EBS volume)
MariaDB settings	/etc/mysql/mariadb.conf.d/99-cloudimg-phpmyadmin.cnf
phpMyAdmin	/usr/share/phpmyadmin
phpMyAdmin configuration	/usr/share/phpmyadmin/config.inc.php (mode 0640)
nginx site	/etc/nginx/sites-available/cloudimg-phpmyadmin
Web gate password file	/etc/nginx/cloudimg-phpmyadmin.htpasswd (mode 0640)
Certificate and key	/etc/ssl/phpmyadmin/
Boot service	/usr/local/sbin/mariadb-phpmyadmin-firstboot.sh
Host name helper	/usr/local/sbin/phpmyadmin-set-hostname
Credentials	/root/mariadb-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. Never open 3306 to the internet.
• Use your own DNS name and a certificate your browsers trust (Step 8).
• Create a MariaDB 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), keep the operating system patched, and snapshot the data volume regularly.

Support and licensing

MariaDB 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.

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