The Database Upgraded. Did the System? Lessons from a Production RDS MySQL 8.4 Migration
A real MySQL 8.0-to-8.4 migration: tracing hidden database consumers, testing deployed clients, draining persistent connections, and validating recovery without using customer records as test fixtures.
The RDS console said the database was available. That was one checkpoint—not the finish line.
In a production upgrade from Amazon RDS for MySQL 8.0 to 8.4, the difficult work was not submitting the engine change. It was proving that the application, queue workers, scheduled commands, reporting clients, and backup tooling would still behave correctly afterward.
This case study comes from my migration records and rehearsal evidence. Infrastructure names and application-specific identifiers are omitted. It was a planned maintenance-window upgrade, not a zero-downtime migration. The examples below explain the checks; they are not a copy-and-run production runbook.
1. The dependency graph was more important than the upgrade command
The initial question sounded simple: which applications use this database?
Repository searches did not give a sufficient answer. Configuration can be stale, a deployment can differ from the current branch, and a process can retain an old connection after its configuration changes.
I compared three kinds of evidence: configured destinations, deployed runtime artifacts, and live database sessions. Together, they identified the main PHP/Laravel application, background consumers, command-line tooling, and a Java reporting client. Other nearby services used separate database targets and were excluded from this change.
A process list was useful corroboration, not a complete inventory: a monthly job may be absent during a five-minute observation. That is why the inventory also included scheduler definitions and worker configuration.
The resulting matrix asked a different question for each consumer:
- Web application: can the deployed PHP/PDO client authenticate, negotiate TLS, and execute the SQL patterns the application uses?
- Queue workers: do they load the same connection configuration, and can they restart without consuming production work during validation?
- Scheduled commands: which processes must stop, and which services must be restored afterward?
- Reporting: does the actual JDBC driver reconnect, rather than merely keeping an existing session alive?
- Backup tooling: can the installed command-line client still connect and produce a schema dump?
The unit of compatibility was a deployed consumer—not a repository and not a successful connection from my laptop.
2. Rehearse the real client stack against the target engine
The rehearsal used a separate RDS test instance upgraded to MySQL 8.4. Production was not the place to discover whether an older client could speak to the new server.
The recorded client stack included PHP 8.1 with mysqlnd/PDO, Laravel 10, an installed MySQL 5.7-era command-line client, and a MySQL Connector/J 8.0-series driver. Those versions describe the tested environment, not recommended versions for a new deployment.
The older CLI mattered. An upgrade can leave web traffic healthy while silently breaking the backup command someone only runs at night. The rehearsal therefore checked the deployed dump tooling as well as application connections. A successful schema-only dump established connectivity and schema extraction—not that a full backup and restore had been proven.
Authentication needed similar precision. I inspected authentication-related settings and tested the existing application account against the upgraded rehearsal instance. The evidence showed that this account and these clients worked; it did not establish universal compatibility for every MySQL 8.4 installation or every authentication plugin.
MySQL 8.4's default authentication behavior and provider configuration deserve explicit review. A temporary compatibility path is not a substitute for a separately tested client and account modernization plan.
I also checked SQL features found in the codebase: JSON_TABLE, TIMESTAMPDIFF, regular expressions, upserts, full-text expressions, views, and a session-variable-backed function. These are more useful targets than a generic SELECT 1 because they reflect the application's actual dependency surface.
3. Configuration parity is a set of assertions
A restored database is not automatically a faithful rehearsal. The target major version needs a compatible parameter group, and important settings must be deliberately carried forward or deliberately changed.
I compared source and target settings rather than relying on a console label. For the eventual production change, the recorded assertions included:
- the same application-facing database endpoint and port;
- the intended instance class and storage configuration;
- the intended subnet placement and restored security-group association;
- the expected backup retention;
- a target-version parameter group with its final apply status verified.
The upgrade was not bundled with a resize, storage redesign, or application rewrite. Keeping those dimensions stable reduced the number of explanations for a failure and avoided introducing an unrelated recurring cost.
For a rehearsal, any deliberate capacity difference must remain visible. A small clone can establish some functional compatibility; it cannot establish production upgrade duration or workload latency.
4. A write fence needs evidence, not just maintenance mode
Putting the web application into maintenance mode did not stop the whole system. Queue workers, the scheduler, and reporting clients had their own lifecycles.
The production sequence stopped the application writers and background services, inspected database sessions, and applied a temporary maintenance network boundary. An important complication followed: existing pooled reporting connections remained visible after the network change.
That behavior is consistent with AWS connection-tracking documentation: changing security-group rules does not necessarily interrupt existing tracked connections immediately. A rule change is therefore not proof that a database is drained.
The remaining sessions were handled through the approved targeted drain, followed by another inspection. The recorded pre-upgrade state showed only the operator/admin session.
I would not translate that into a generic “kill every connection” script. A drain must distinguish application sessions from operational sessions, handle reconnection, and check active work. Nor would I introduce a broad subnet-level deny as an improvised shortcut: its blast radius can extend beyond this database.
The useful invariant was: expected writers are stopped, new application access is fenced, and remaining sessions have been explicitly accounted for. Only then did the sequence proceed.
5. The snapshot defined a recovery point—not an undo button
A fresh manual snapshot was taken after the drain and verified available before the engine change. That positioned the recovery point after application writes had been stopped.
Two different recovery situations must not be confused. RDS can automatically roll back certain failed upgrade attempts. That does not mean an operator can simply undo a successful major-version upgrade after the application resumes writing.
Restoring a pre-upgrade snapshot creates another database instance. Recovery then includes reconnecting consumers, validating the restored system, and deciding what happens to writes accepted after the snapshot.
The critical boundary is the first accepted application write after reopening. Before that boundary, the frozen recovery point can still represent the application state at shutdown. After it, switching back to that recovery point can discard newer work unless there is an explicit reconciliation strategy.
This is why I kept application traffic closed through the initial post-upgrade validation rather than treating RDS availability as permission to reopen immediately.
6. Test useful behavior without using customer records as fixtures
The post-upgrade harness bootstrapped the deployed Laravel application and obtained its PDO connection. That exercised the real configuration path instead of a separately assembled connection string.
Writes were limited to synthetic values in session-scoped temporary tables. The harness checked bound inserts, reads, updates, deletes, transaction commit and rollback, upserts, joins, aggregation, JSON_TABLE, date arithmetic, collations, and regular expressions.
A minimal illustration of the transaction check is:
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TEMPORARY TABLE upgrade_probe (
id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO upgrade_probe VALUES (1, 12.50);
ROLLBACK;
SELECT COUNT(*) AS remaining_rows FROM upgrade_probe;
-- Assert remaining_rows = 0 in the harness.
DROP TEMPORARY TABLE upgrade_probe;Temporary-table cleanup was explicit. Transaction rollback is not a universal undo mechanism for DDL, and synthetic checks still consume resources on the server. “No customer-row mutation” is more precise than “zero impact.”
The harness also compiled 15 application views using EXPLAIN with LIMIT 0. This was a bounded compatibility check, not execution of their business queries. It did not prove result correctness, query-plan performance, or behavior under production cardinality. EXPLAIN still requires metadata access and can interact with locking and optimizer statistics; it should not be described as literally no work.
Likewise, a SELECT FOR UPDATE check on a temporary table established that the tested statement path worked. It did not validate contention or deadlocks between independent application sessions.
7. The test harness had bugs too
The first useful failure was in the validation code, not necessarily in MySQL.
One prepared regular-expression query had a placeholder quoting problem. The corrected version bound the pattern as a parameter rather than accidentally turning the placeholder into part of a SQL string literal.
A collation assertion also needed an explicit character set. If a test is meant to distinguish case-sensitive and case-insensitive utf8mb4 behavior, it should not depend on an implicit connection character set:
1
2
3
4
5
6
7
SELECT
CAST('abc' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_0900_as_cs
=
CAST('ABC' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_0900_as_cs;
-- Expected: 0After those corrections, the historical run recorded 16 successful checks and 15 compiled views.
There is an important qualification in the implementation: a full-text compilation subcheck was optional and could be skipped on failure. The final success marker therefore cannot be interpreted as proof that every optional feature passed. A stronger harness would report required, optional, passed, failed, and skipped checks separately.
That is a lesson I would carry forward: a green status is only as strong as its assertion accounting.
8. Separate application recovery from engine recovery
The production record put the main engine-upgrade interval at about 18 minutes, from roughly 19:32 to 19:50 UTC. The broader recorded controlled maintenance interval was about 28 minutes.
Those are operational timestamps from this change—not a universal RDS duration estimate or an end-user outage measurement. A rehearsal's storage state, capacity, and workload can differ.
Recovery checks covered more than the engine version: the intended parameter group was in sync, the original network association was restored, web service probes succeeded, worker processes were running, and the scheduler was enabled. The reporting client's JDBC reconnection and metadata access were also verified.
One worker runtime was tested with a read-only connection probe while production queue execution remained paused for that check. That established boot and database connectivity, not successful processing of an arbitrary business job. Similarly, a public HTTP 200 did not establish that every authenticated application route worked.
These distinctions make the evidence more useful. They tell the next operator which failures were ruled out and which still require workload-specific observation.
What I would reuse on the next upgrade
- Build the consumer inventory from configuration, deployed artifacts, schedules, and live sessions.
- Test actual client versions—including backup and reporting tools—against the target engine.
- Keep configuration differences explicit and avoid bundling unrelated infrastructure changes.
- Prove the write fence and drain; do not infer them from maintenance mode or a security-group update.
- Define the recovery point and the consequences of reopening writes before the cutover.
- Use bounded synthetic checks, with separate reporting for required and skipped assertions.
- Restore and verify the background system, not just the web endpoint.
The database upgrade succeeded. The more valuable outcome was a record of why I believed the system could be reopened—and the limits of that belief.
If you are planning a major-version upgrade, which consumer would your current test plan miss first: a queue worker, a reporting client, a scheduled job, or the backup tool?
References
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article