MySQL 5.6 to 8.0 Migration with AWS DMS and the Objects It Leaves Behind

MySQL does not support upgrading a 5.6 server to 8.0 in place, and its upgrade documentation uses that exact jump as its example of a skipped version. A client had a production MySQL 5.6 database on EC2 that had to move to MySQL 8.0 on new instances. We moved it with AWS DMS: a full load, then change data capture (CDC) until the cutover. DMS is built to move rows. Some of what sits around the rows stays behind, and on this project that meant ON UPDATE timestamp defaults, triggers and a view, all restored by hand after the cutover.

Our RDS PostgreSQL 14 post compares in-place, blue/green and logical replication upgrades on a managed engine. This one stays with a self-managed server on EC2, where DMS does the copy and the team owns every object DMS does not create.

Which DMS feature takes a 5.6 source

For MySQL to MySQL, the DMS user guide recommends homogeneous data migrations, which run native database tools. Their source list starts at MySQL 5.7. The classic replication task, built from a source endpoint and a target endpoint, accepts MySQL 5.5, 5.6, 5.7, 8.0 and 8.4 on EC2 as a source and as a target. So a 5.6 source means a classic full load and CDC task. The other route is two in-place upgrades, because MySQL only upgrades to 8.0 from a 5.7 GA release, 5.7.9 or higher. We took DMS. The destination was a new set of instances either way, and a replication task can be run against them as many times as the schedule allows before anyone commits to a window.

CDC reads the source's binary logs. For a self-managed source, the DMS MySQL page asks for binlog_format set to ROW, binlog_row_image set to FULL, and on MySQL 5.x an expire_logs_days of 1 or more.

One date matters if you are planning this move now. MySQL 8.0 has been in Oracle Sustaining Support since April 21, 2026, and the MySQL end-of-life notice encourages users to upgrade to MySQL 8.4 LTS or 9.7 LTS. MySQL 5.6 has been in Sustaining Support since February 1, 2021.

What DMS creates on the target, and what it skips

The DMS troubleshooting guide says the service "creates tables, primary keys, and in some cases unique indexes," and it does not create "secondary indexes, non-primary key constraints, or data defaults." The limitations for a MySQL source add to that list:

• The AUTO_INCREMENT attribute on a column is not migrated.
• An index on part of a column value, such as name(10), is not created on the target.
• During CDC, changes to a column's default value, nullability or character set are not supported.
• ON DELETE CASCADE and ON UPDATE CASCADE changes to child tables do not reach the target, because MySQL writes no binlog events for the cascaded operations.

Ongoing replication also leaves out "indexes, users, privileges, stored procedures," and other changes not tied to table data. A view is picked up only when the table mapping sets the object-locator to view or all, per the troubleshooting guide, and DMS does not validate views at all.

ON UPDATE defaults, triggers and a view were the three kinds of object we had to restore. Each has a row in INFORMATION_SCHEMA. Take the inventory on the source before the first full load, take it again on the target after the cutover, and the gap is a diff:

-- Run on the source before the first full load and on the target
-- after cutover, save both outputs, and diff them.
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA LIKE '%on update%'
  AND TABLE_SCHEMA NOT IN ('mysql', 'sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE,
       ACTION_TIMING, EVENT_MANIPULATION
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA NOT IN ('mysql', 'sys')
ORDER BY TRIGGER_SCHEMA, TRIGGER_NAME;

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

The column names are from the MySQL 8.0 reference pages for COLUMNS, TRIGGERS and VIEWS. The EXTRA column reads on update CURRENT_TIMESTAMP for a TIMESTAMP or DATETIME column with that attribute. The sys schema is excluded because MySQL 8.0 ships it with views and triggers of its own.

Creating the target schema yourself shortens the list. With TargetTablePrepMode set to DO_NOTHING, DMS leaves the data and metadata of an existing target table alone, so column attributes you created stay in place. Triggers remain a cutover item either way. AWS recommends dropping DML triggers for a full load and says to "enable triggers right before the cutover."

The rehearsal that moved the first window

The first cutover window was postponed because the data migration was not ready. Before the cutover that did run, we rehearsed the whole migration:

• full loads
• a clone of the reporting database with replication
• index builds
• a fix for a stalled CDC task
• validation tuning until every table passed

The rehearsal ended with 14 of 14 tables validated and 0 failures.

Plan the index timing on paper before the first full load. For a full load plus CDC task, AWS recommends adding secondary indexes before the CDC phase, because DMS applies changes through logical replication and indexes that support DML prevent full table scans. The task can stop itself at that point. The full-load settings StopTaskCachedChangesNotApplied and StopTaskCachedChangesApplied stop it after the full load, before or after the cached changes are applied, which leaves a gap to build indexes and resume.

We would postpone a window again for the same reason. A stalled CDC task is a rehearsal finding when it turns up early, and a rollback decision when it turns up inside the window. Other cutovers get the same treatment from us; our kube-proxy IPVS to nftables migration post runs pre-flight checks before a node-by-node cutover.

What a passing validation depends on

Validation is off by default, with EnableValidation set to false. Once on, it compares each source row with its target row, and its limits set what a pass covers. A table needs a primary key or a unique index. Rows that change continuously during validation cannot be validated. Views are not validated. More than 10,000 failed or suspended records stop validation altogether.

Six settings decide how fast validation runs and when it gives up. These are their documented defaults:

| Setting | Default | What it controls | |---|---|---| | ThreadCount | 5 | Validation threads; more finish sooner and put more queries on both databases | | PartitionSize | 10,000 | Records read per comparison batch from source and target | | FailureMaxCount | 10,000 | Failed records before validation is suspended for the task | | TableFailureMaxCount | 1,000 | Failed rows in one table before validation is suspended for that table | | RecordFailureDelayLimitInMinutes | 0 | A longer delay before failures are reported, overriding the CDC latency value | | HandleCollationDiff | false | Whether validation accounts for collation differences between source and target |

The state of every table is one CLI call away, using the fields of describe-table-statistics:

aws dms describe-table-statistics \
  --replication-task-arn "$TASK_ARN" \
  --query 'TableStatistics[].[TableName,ValidationState,ValidationFailedRecords,ValidationSuspendedRecords]' \
  --output table

A table has passed when it shows Validated. A table showing No primary key has not been checked at all and needs a check of its own. The troubleshooting guide also warns that a full load and CDC task can create duplicate records on target tables that have neither a primary key nor a unique index.

A cutover sequence for a 2.5-hour window

The production cutover ran in a 2.5-hour evening window, in this order:

1. Pause the heaviest CDC producers, to keep the backlog under control.
2. Put the source behind a read-only gate.
3. Let the DMS tasks drain.
4. Repoint the applications to the 8.0 instances.
5. Apply the grants.

It completed in the planned window. We also found about 288,000 duplicate rows during the cutover and documented them.

The read-only gate needs care on 5.6. The read_only variable still lets users with the SUPER privilege write, and super_read_only, which blocks them too, arrived in MySQL 5.7.8. On a 5.6 source, list the accounts that hold SUPER before you rely on the gate.

Draining has a number you can watch. The DMS task metric CDCIncomingChanges counts change events waiting to be applied to the target, and once the source is read-only it should fall to zero and stay there before anything is repointed. If CDCLatencyTarget is higher than CDCLatencySource, the same page points you at the target side.

Grants get their own step because DMS does not carry users or privileges, and a grant script written for 5.6 may not run on 8.0. MySQL 8.0 removed the use of GRANT to create users, and the IDENTIFIED BY PASSWORD syntax with it, so accounts are created with CREATE USER first. The same release made caching_sha2_password the default authentication plugin in place of mysql_native_password, so log in as each application account during the rehearsal.

The first days on 8.0

Four pieces of work followed in the days after the cutover.

The new reporting server had a binlog overflow. We purged about 110 GB of binary logs and corrected its retention. Give every new 8.0 server a retention value someone chose. From MySQL 8.0, binary logging is enabled by default whether or not --log-bin is given, and binlog_expire_logs_seconds defaults to 2592000 seconds, which is 30 days. expire_logs_days, the variable 5.6 used, is deprecated in 8.0 and removed in 8.4.

We set up nightly snapshots for the new instances and repointed the monitors to them. A missing snapshot and a monitor still watching the old host both stay silent on the day they matter, so we would put both on the cutover plan with a named owner.

The last item was replica lag, which we traced to locking from an export job.

For a 5.6 or 5.7 server that still has to move, we start by listing the tables with no primary key or unique index, then the triggers, views and ON UPDATE columns that DMS will not recreate. That list sets the length of the rehearsal and the size of the post-cutover checklist. Get in touch and we will run that inventory on yours first.