MySQL migration: check performance and data consistency

A successful import is not the acceptance criterion

A MySQL migration is usable only when the data is correct, the application works and agreed performance remains achievable. An import finishing without errors does not fully prove any of these conditions. Inventory versions, storage engines, schemas, indexes, collations, character sets, SQL modes, time zones, privileges, triggers, routines and events beforehand. Include external jobs and application connections as well. Define what falls outside the migration and who can approve completion. A platform change can alter behavior even when table names and row counts remain unchanged, so retain a written acceptance checklist.

Verify restore and snapshot boundaries

Test restoring the backup into a separate environment. The presence of a backup file does not establish that required schemas, routines and privileges can be restored correctly. Consistent comparison requires a defined snapshot boundary. With ongoing writes, two correct systems may legitimately contain different row counts. A mysqldump using --single-transaction does not automatically provide a consistent backup for nontransactional tables or concurrent DDL. Choose the method according to storage engines, volume and acceptable downtime. Document any replication lag and the last applied change position before comparing the source and destination.

Check content, not only counts

This isolated example creates two temporary tables with the same three IDs. ID 2 deliberately has a different amount on the target side. COUNT(*) matches, but the content is incorrect. Amounts use integer cents here to avoid comparing floating-point values. Run the block only in a fresh connection to a separate MySQL test database and stop at the first error. It is a fixture, not a script that migrates production. Close the session to remove the temporary tables. Real financial or domain-specific precision rules still need to be documented independently.

-- Synthetic data only. NEW session in a separate MySQL test database.
-- Never run in production. Stop at the first error; close the session afterwards.
CREATE TEMPORARY TABLE cg_migration_source (
    id INTEGER NOT NULL PRIMARY KEY, amount_cents INTEGER NULL
);
CREATE TEMPORARY TABLE cg_migration_target (
    id INTEGER NOT NULL PRIMARY KEY, amount_cents INTEGER NULL
);
INSERT INTO cg_migration_source VALUES (1,1000),(2,2500),(3,NULL);
INSERT INTO cg_migration_target VALUES (1,1000),(2,2501),(3,NULL);

SELECT 'source' AS dataset, COUNT(*) AS row_count FROM cg_migration_source
UNION ALL
SELECT 'target' AS dataset, COUNT(*) AS row_count FROM cg_migration_target;

-- Expected: one difference, id 2. NULL mismatches are tested explicitly.
SELECT s.id, s.amount_cents AS source_amount, t.amount_cents AS target_amount
FROM cg_migration_source s
LEFT JOIN cg_migration_target t ON t.id = s.id
WHERE t.id IS NULL
   OR s.amount_cents <> t.amount_cents
   OR (s.amount_cents IS NULL AND t.amount_cents IS NOT NULL)
   OR (s.amount_cents IS NOT NULL AND t.amount_cents IS NULL)
ORDER BY s.id;

-- Also check unexpected target IDs; a one-direction comparison is insufficient.
-- Expected: no rows for this fixture.
SELECT t.id FROM cg_migration_target t
LEFT JOIN cg_migration_source s ON s.id = t.id
WHERE s.id IS NULL
ORDER BY t.id;

For the real migration, check missing and additional keys in both directions, null values, amounts, dates and relationships within the same agreed snapshot. A sample or a simple checksum gives different assurance from complete content comparison. Record exactly which columns and records were checked and which differences are intentionally expected. A passing result on this small fixture says nothing about the completeness of a real customer database. Include application-level invariants, such as ownership and referential consistency, and investigate discrepancies before enabling new production writes on the destination.

Compare performance under matching conditions

Before the rehearsal, select representative searches, reports and write flows. Measure source and target with matching data, parameters, cache conditions and load profiles. Review errors, locks, query plans and resource usage as well as response time. A faster SELECT does not compensate for a missing background job or a failing transaction. Refresh statistics when required by the migration method and server version, and test application settings intended for cutover. Record repeated measurements and agreed thresholds instead of deciding retrospectively that any difference is acceptable because the import completed.

Plan cutover and rollback explicitly

Agree stop criteria, the maintenance window, acceptable downtime, decision authority and post-cutover checks. Once the destination accepts new writes, pointing traffic back to an old source can lose recent changes. A rollback plan must therefore address those new records through a tested reconciliation path or a controlled write pause. Do not promise zero downtime without a demonstrated design. Complete the migration only after data, application and performance checks are documented and an accountable owner approves the result. Preserve the evidence of what was checked rather than describing an untested recovery procedure as guaranteed.

Continue the investigation

A query plan is one part of the diagnosis. The following references connect the technical investigation to a scoped SQL service. Demonstrations use fictional data, not the results of a client engagement. For an enquiry, start with a problem description and anonymised context. Do not send passwords, complete database dumps or customer personal data.

• Investigate a slow MySQL query with EXPLAIN
• Choose database indexes from real Laravel queries
• Discuss SQL query optimisation
• Reproducible SQL demonstration
• Request a SQL analysis