MENU


MySQL in the Cloud and Compared with Other Databases

Most production LAMP sites end up on a managed database, and every team eventually asks whether MySQL is the right engine at all. This section answers both: what Amazon RDS and Aurora take off your hands, how to move the shop database there, and how MySQL's forks and rivals treat the same SQL. It assumes the AWS account and cost habits of Sections 1.12 to 1.14.

3.18.1 Managed MySQL: What You Gain and What You Give Up

A managed database is MySQL that a provider installs, patches, backs up and fails over for you. You still design tables and tune queries; you no longer own the operating system or mysqld. Concern Self-managed (Section 1.13.8) Managed (RDS, Aurora) Backups, patches, failover Yours to script (3.17) Automatic; Multi-AZ is one flag Root, SUPER, my.cnf Yours Admin user, parameter groups, no shell Server version Any, including 9.7 LTS The provider's list (8.4 on RDS) Cost The instance About twice the same-size EC2 instance Table 3.12: What a managed MySQL service changes The version row matters for this book. On 23 September 2026 RDS for MySQL offered 8.4 in production and 9.7 only in its Database Preview environment, which deletes instances after 60 days. Code that relies on 9.x features, such as VECTOR (Section 3.3.7), will not run there; everything else in Sections 3.2 to 3.16 does. Missing privileges bite too: killing another user's query becomes CALL mysql.rds_kill(id), and a dump whose DEFINER accounts do not exist on the target fails to load.

3.18.2 Amazon RDS for MySQL

RDS runs community MySQL on an EC2 instance you never see, with its data on EBS (Section 1.13.6). Ask the API which versions it sells; this call only reads, so it costs nothing:

aws rds describe-db-engine-versions --engine mysql --region us-east-1 --output text \

--query "join(' ', DBEngineVersions[?starts_with(EngineVersion, '8.4')].EngineVersion)"

Listing 3.167: The RDS for MySQL 8.4 versions in us-east-1 (read-only call) 8.4.5 8.4.6 8.4.7 8.4.8 8.4.9 8.4.10 8.4.11 Output of Listing 3.167 The default for new instances was 8.4.9, not the newest, so name the version. MySQL 8.0 left RDS standard support on 31 July 2026 and now runs only under paid Extended Support; 8.4 is supported until 31 July 2029. The next command is shown without output because it starts billing the account the moment it succeeds:

aws rds create-db-instance --db-instance-identifier shop-db --engine mysql \

--engine-version 8.4.11 --db-instance-class db.t4g.micro --storage-type gp3 \

--allocated-storage 20 --backup-retention-period 7 --master-username admin \

--manage-master-user-password --no-publicly-accessible

Listing 3.168: Creating an RDS for MySQL 8.4 instance (not run here: it is billable) --manage-master-user-password keeps the password in AWS Secrets Manager, and --no-publicly-accessible limits connections to your VPC: open port 3306 in the instance's security group to the web server's group only (Section 1.13.4). Add --multi-az for production. It keeps a synchronous standby in a second Availability Zone and fails over by repointing the endpoint's DNS name, so connect by name, never by IP. Prices in us-east-1 on 23 September 2026: db.t4g.micro USD 0.016 an hour (0.032 Multi-AZ) and gp3 USD 0.115 per GB-month, about USD 14 a month for this command.

3.18.3 Amazon Aurora MySQL-Compatible Edition

Aurora keeps MySQL's SQL layer and replaces the storage. Every instance in a cluster reads one shared cluster volume that keeps copies of the data in three Availability Zones and grows by itself (up to 256 TiB, depending on the engine version). Adding a reader copies no data, and a failover promotes a reader that already sees every page:Figure 3.14: RDS Multi-AZ copies a disk; Aurora shares one volume across three zonesThe same describe-db-engine-versions call with --engine aurora-mysql listedn8.0.mysql_aurora.3.13.0 and 8.4.mysql_aurora.8.4.8` as the newest versions: the prefix is the MySQL line. Aurora MySQL version 3 matches MySQL 8.0 (3.13 is 8.0.45) with standard support to 30 April 2028; Aurora MySQL 8.4 matches the 8.4 LTS. Neither runs 9.7. A reader endpoint spreads reads over up to 15 replicas. In us-east-1 on 23 September 2026 a db.t4g.medium cost USD 0.073 an hour, plus USD 0.10 per GB-month and 0.20 per million I/O requests; I/O-Optimized drops the I/O charge and pays off once I/O is 25% of the bill. Serverless v2 (USD 0.12 per ACU-hour) can pause at 0 ACUs from Aurora MySQL 3.08.0, but the next connection waits about 15 seconds: fine for staging, not for a shop. History: Older tutorials describe Aurora multi-master clusters with several writers. That mode existed only in Aurora MySQL version 1 (MySQL 5.6), which reached end of life on 28 February 2023, and current AWS documentation no longer describes it. An Aurora MySQL cluster today has one writer instance.

3.18.4 Migrating a Database to the Cloud

Pipe a small database straight into RDS with mysqldump --single-transaction --routines --set-gtid-purged=OFF shop | mysql -h <endpoint> -u admin -p shop. MySQL Shell (Section 3.17.5) is faster for large ones. Here a mysql:8.4 container on port 3418 stands in for RDS:

mysqlsh root@127.0.0.1:3318 --passwords-from-stdin -- util dump-schemas shop \

--outputUrl=shop-dump --threads=4 <<< secret 2>&1 | grep -E '^(Schemas|Rows)'

mysqlsh root@127.0.0.1:3418 --passwords-from-stdin -- util load-dump shop-dump \

--threads=4 <<< secret 2>&1 | grep -E '^(Target|[0-9]+ (chunks|warn))' | cut -c1-75

Listing 3.169: Dumping shop from MySQL 9.7 and loading it into 8.4 with MySQL Shell

Schemas dumped: 1

Rows written: 53

Target is MySQL 8.4.11. Dump was produced from MySQL 9.7.2

6 chunks (53 rows, 2.08 KB) for 6 tables in 1 schemas were loaded in 0 sec

0 warnings were reported during the load.

Output of Listing 3.169 No ignoreVersion option was needed, because 8 and 9 are consecutive major versions; it works only while the schema uses nothing newer than 8.4, such as VECTOR. It runs LOAD DATA LOCAL INFILE, so the target needs local_infile set to 1.For near-zero downtime, load the dump, make RDS a replica of the old server with CALL mysql.rds_set_external_source_with_auto_position(...) (Section 3.17.8), wait for zero lag, set the old server read_only, and point PHP at the new endpoint. AWS DMS does the same across engines: a full load, then ongoing changes. Compare CHECKSUM TABLE on both sides first.

3.18.5 MariaDB and Percona Server as Drop-In Alternatives

Both speak MySQL's protocol, so PHP's mysqli and PDO connect unchanged. MariaDB, a fork led by MySQL's original author, has its own version line: 12.3 and 11.8 are the newest long-term series (community support to June 2029 and June 2028). Percona Server for MySQL tracks Oracle's releases: 9.7.1-1 (5 August 2026) is MySQL 9.7.1 plus free versions of Enterprise-only features such as a thread pool, the audit log filter, data masking and the MyRocks engine. Run one script on both (containers mariadb:11.8 on port 3318 and percona/percona-server:9.7 on 3418, started as in Section 3.1.6):

cat > compat.sql <<'SQL'

CREATE DATABASE shop;

USE shop;

CREATE TABLE t (id INT PRIMARY KEY, doc JSON);

INSERT INTO t VALUES (1, '{"format": "ebook"}');

SELECT VERSION(), DATA_TYPE FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'doc';

SELECT doc->>'$.format' FROM t;

DELETE FROM t WHERE id = 1 RETURNING id;

SQL

for c in mariadb118:mariadb percona97:mysql; do

docker exec -i -e MYSQL_PWD=secret ${c%:*} ${c#*:} -uroot -Ns -n --force < compat.sql 2>&1 \

| sed 's/: You have an error.* near/: near/; s/\t/ /g'

done

Listing 3.170: One script, two MySQL-compatible servers

11.8.9-MariaDB-ubu2404 longtext

--------------

SELECT doc->>'$.format' FROM t

--------------

ERROR 1064 (42000) at line 6: near '>>'$.format' FROM t' at line 1

1

9.7.1-1 json

ebook

ERROR 1064 (42000) at line 7: near 'RETURNING id' at line 1

Output of Listing 3.170 Percona behaved exactly like MySQL 9.7. MariaDB stores JSON as checked LONGTEXT and rejects ->> (use JSON_UNQUOTE(JSON_EXTRACT(...))), but offers DELETE ... RETURNING, sequences and system-versioned tables. Its GTIDs, VECTOR functions and default collation differ too: moving to MariaDB is a migration, not a package swap. Percona's image loads component_percona_telemetry, which sends usage data daily; add percona_telemetry_disable=1 under [mysqld] to turn it off.

3.18.6 MySQL Versus PostgreSQL, SQLite, and MongoDB

Standard SQL travels well; the edges do not. The file mini-shop.sql holds the shop's categories and products rows with portable types only (no UNSIGNED, AUTO_INCREMENT or JSON). Load it into PostgreSQL 18.6 (container pg18) and SQLite (Ubuntu's 3.46.1), run one report, and\nthen three small tests:

cat > report.sql <<'SQL'

SELECT c.name, SUM(p.price * p.stock) AS stock_value FROM products p

JOIN categories c ON c.id = p.category_id GROUP BY c.name ORDER BY 2 DESC LIMIT 2;

SQL

docker exec -i pg18 psql -U postgres -q < mini-shop.sql

docker exec -i pg18 psql -U postgres -At < report.sql

sqlite3 shop.db < mini-shop.sql

sqlite3 shop.db < report.sql

mysql -N shop -e "SELECT 7 / 2, 'PHP' || '8.5'" | tr '\t' '|'

docker exec pg18 psql -U postgres -At -c "SELECT 7 / 2, 'PHP' || '8.5'"

sqlite3 shop.db <<'SQL' 2>&1

INSERT INTO products VALUES ('BK-TYPO-1', 'Price Typo', 2, 'free', '12 copies');

SELECT price, typeof(price), typeof(stock) FROM products WHERE sku = 'BK-TYPO-1';

CREATE TABLE products_strict (sku TEXT PRIMARY KEY, price REAL NOT NULL) STRICT;

INSERT INTO products_strict VALUES ('BK-TYPO-1', 'free');

SQL

Listing 3.171: One report, then three small tests, on PostgreSQL and SQLite

Programming|2257.50

Accessories|1498.50

Programming|2257.5

Accessories|1498.5

3.5000|1

3|PHP8.5

free|text|text

Runtime error near line 4: cannot store TEXT value in REAL column products_strict.price (19)

Output of Listing 3.171 PostgreSQL's totals match MySQL's to the cent. SQLite printed 2257.5 because it has no DECIMAL storage: the column gets numeric affinity and holds a floating-point number, so keep money in integer cents there. Next, MySQL divides integers into a decimal and reads || as a deprecated OR, so 'PHP' becomes 0 and '8.5' true; PostgreSQL truncates and concatenates. Last, SQLite stored the words free and 12 copies in numeric columns without complaint, and only a STRICT table refused them; MySQL's strict mode rejects that insert outright (ERROR 1366). Upserts differ too: PostgreSQL and SQLite use ON CONFLICT ... DO UPDATE, MySQL ON DUPLICATE KEY UPDATE (Section 3.2.6). MongoDB is not SQL at all: it stores BSON documents and scales writes by sharding. Volume 2 of this series covers it; in a LAMP shop, MySQL's JSON columns (Section 3.9) usually suffice. Engine Licence Strengths Fits MySQL 9.7 GPLv2 or commercial Everywhere, replication, InnoDB LAMP, WordPress, Laravel PostgreSQL 18 PostgreSQL (permissive) Strict types, rich SQL, extensions Complex queries, GIS SQLite 3.53 Public domain One file, no server Tests, apps, small sites MongoDB SSPL (not OSI-approved) Documents, sharding Schema-light, huge writes Table 3.13: MySQL and its main alternatives, September 2026