MariaDB with phpMyAdmin 10.11

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick MariaDB 10.11.19 + phpMyAdmin 5.2.3 on Ubuntu 24.04 as the software version, pick your Region, and 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.

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

Connect over SSH with your key pair. For MariaDB 10.11.19 + phpMyAdmin 5.2.3 on Ubuntu 24.04, the SSH user is ubuntu.

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

First boot creates MariaDB's TLS certificate and sets the passwords and the web certificate. Then nginx and PHP-FPM start:

systemctl is-active nginx php8.3-fpm mariadb-tls-init mariadb mariadb-phpmyadmin-firstboot
sudo test -f /var/lib/sac/data/mariadb-phpmyadmin-firstboot.done && echo FIRSTBOOT_DONE

All five units are active and the first-boot marker is present:

active 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

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

The file is readable by root only:

# MariaDB 10.11.19 + phpMyAdmin 5.2.3 - 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> # MariaDB over TCP (127.0.0.1:3306 only; every TCP connection must use TLS, require_secure_transport=ON) MARIADB_HOST=127.0.0.1 MARIADB_PORT=3306 MARIADB_TLS_CA=/etc/mysql/ssl/server-cert.pem # root is not accepted by phpMyAdmin; on the instance use:
sudo mariadb (unix socket)
# The certificates are 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>
# Re-issue the MariaDB TLS certificate for your own DNS name:
sudo mariadb-tls-cert --renew <your-dns-name>
# The datadir /var/lib/mysql is its own EBS volume.
# User guide: https://secureanycloud.com/stacks/mariadb-with-phpmyadmin-10-11/

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.

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

• The browser shows its own sign-in prompt. Enter admin and the WEB_GATE_PASSWORD.
• phpMyAdmin's login page opens. Sign in as dbadmin with the MARIADB_ADMIN_PASSWORD.

The home page shows the database server (MariaDB 10.11.19 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. The script first signs in to MariaDB as dbadmin over TCP with a verified TLS session, and writes, reads and drops a test table. It shows that a wrong password, a plain-text connection and root over TCP are refused.

The script then signs in through the web gate and phpMyAdmin's real login form over HTTPS. It shows that a missing or 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 dbadmin round-trips a table over verified TLS (wrong password, plaintext TCP and root over TCP refused; root only via the socket); web gate refuses no/wrong credentials (401); dbadmin signs in over HTTPS; wrong password, root and blank password refused

• Databases lists every database and creates new ones. The phpmyadmin database holds phpMyAdmin's own settings, bookmarks and history, so 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.

You can do the same from the command line on the instance. The sudo mariadb command signs in as MariaDB's root over the local socket, with 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.19-MariaDB-ubu2404 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

The root and mysql accounts have no usable password, because they authenticate by the Linux user over the socket. mariadb.sys is a locked system account. 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.

For example, to create a database and an application user, 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

MariaDB is not exposed to the network, and every TCP connection must use TLS. You can use a desktop client from your computer, such as MySQL Workbench, DBeaver, HeidiSQL, or the mariadb or mysql command line.

Copy the instance's MariaDB certificate so your client can verify the server. Open an SSH tunnel, and connect through it as dbadmin (or an application user created for 127.0.0.1):

scp -i <your-key.pem> ubuntu@<public-ip>:/etc/mysql/ssl/server-cert.pem ./mariadb-ca.pem ssh -i <your-key.pem> -N -L 3307:127.0.0.1:3306 ubuntu@<public-ip> mariadb -h 127.0.0.1 -P 3307 -u dbadmin -p --ssl-ca=./mariadb-ca.pem --ssl-verify-server-cert

Graphical tools offer the same SSH tunnel and "require SSL" settings. Give them mariadb-ca.pem as the CA file.

Applications on this instance connect to 127.0.0.1:3306 with TLS, or to the local socket /run/mysqld/mysqld.sock. Most MySQL and MariaDB drivers have a "require SSL" or ssl-mode=REQUIRED option.

Allow connections from your VPC. 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/mariadb.conf.d/99-sac-phpmyadmin.cnf
sudo systemctl restart mariadb
sudo mariadb -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.

If clients connect by a DNS name, add it to the MariaDB certificate with sudo mariadb-tls-cert --renew <your-dns-name>. MariaDB loads the new certificate without a restart.

Point a DNS record at the instance. An Elastic IP keeps the address stable. Then set the name that 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, copy it over the generated pair and reload nginx. An example is a certificate issued by Let's Encrypt for <your-domain>. 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

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). 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 SecureAnyCloud settings and requires TLS on every TCP connection:

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;"
sudo mariadb -e "SELECT @@have_ssl, @@require_secure_transport, @@tls_version;" cat /etc/sac/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/ @@have_ssl @@require_secure_transport @@tls_version YES 1 TLSv1.2,TLSv1.3 sac: phpMyAdmin 5.2.3 (GPL-2.0-only) from files.phpmyadmin.net, sha256 12ba1c425fa4071abbd4e7668c9ebdeac0b0755a467a6d6d5026122bb47c102b, OpenPGP signature by 3D06A59ECE730EB71B511C17CE752F178259BD92; setup/ and examples/ removed. sac: MariaDB Server 1:10.11.19+maria~ubu2404 (GPL-2.0-only) from the official MariaDB 10.11 repository (dlm.mariadb.com), signing key 177F4010FE56CA3336300305F1656F24C74CD1D8.

The web 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 = sac 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

MariaDB serves its own per-instance certificate on port 3306:

echo | openssl s_client -starttls mysql -connect 127.0.0.1:3306 2>/dev/null | openssl x509 -noout -subject -ext subjectAltName

subject=CN = ip-<private-ip-dashed>.ec2.internal, O = sac MariaDB 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

None of the services can reach the instance metadata service:

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

IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32 Id=nginx.service IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32 Id=php8.3-fpm.service IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32 Id=mariadb.service IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32 Id=mariadb-tls-init.service IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32 Id=mariadb-phpmyadmin-firstboot.service

SSH accepts keys only, refuses root and does not forward X11. The following shows the effective settings, as sshd reads them:

sudo sshd -T | grep -E '^(permitrootlogin|passwordauthentication|kbdinteractiveauthentication|x11forwarding) '

permitrootlogin no passwordauthentication no kbdinteractiveauthentication no x11forwarding no

nginx hands PHP the configured host name and the client address from the connection, never values a client sends. It also 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/sac-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 "";

• dbadmin: In phpMyAdmin, choose Change password on the home page. Alternatively, 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/sac-phpmyadmin.htpasswd >/dev/null
sudo chown root:www-data /etc/nginx/sac-phpmyadmin.htpasswd && sudo chmod 0640 /etc/nginx/sac-phpmyadmin.htpasswd unset NEWPW

Every database lives in /var/lib/mysql on the data volume. The simplest backup is an EBS snapshot of that volume. For a consistent copy, take the snapshot with the instance stopped, or after FLUSH TABLES WITH READ LOCK.

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, with uploads up to 256 MB.

Ubuntu security updates install automatically. MariaDB comes from the MariaDB 10.11 repository, so its point releases (10.11.20 and later) are applied when you choose. A database upgrade restarts the server:

sudo apt-get update && sudo apt-get install --only-upgrade mariadb-server mariadb-client

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

These are the main components and their paths:

• MariaDB data directory: /var/lib/mysql (separate EBS volume)
• MariaDB settings: /etc/mysql/mariadb.conf.d/99-sac-phpmyadmin.cnf
• MariaDB TLS certificate and key: /etc/mysql/ssl/ (re-issue with sudo mariadb-tls-cert --renew <name>)
• MariaDB repository and pin: /etc/apt/sources.list.d/mariadb.sources, /etc/apt/preferences.d/sac-mariadb.pref
• phpMyAdmin: /usr/share/phpmyadmin
• phpMyAdmin configuration: /usr/share/phpmyadmin/config.inc.php (mode 0640)
• nginx site: /etc/nginx/sites-available/sac-phpmyadmin
• Web gate password file: /etc/nginx/sac-phpmyadmin.htpasswd (mode 0640)
• Web 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)

• 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. If you open it to your VPC, keep require_secure_transport on and create accounts with REQUIRE SSL (Step 7).
• Use your own DNS name and a certificate that 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.