How to Compare Two MySQL Databases and Find the Differences

Last updated: July 2026

When a project runs on multiple environments, the database schemas drift apart: a column added on development never reaches production, an index exists on one server only. Here are three reliable ways to find (and fix) the differences — starting with MySQL Workbench’s built-in tool.

Method 1: MySQL Workbench Schema Synchronization

MySQL Workbench compares two schemas and generates the ALTER statements needed to make them match.

1. Open MySQL Workbench and connect to the server holding your source schema.
2. Go to Database → Synchronize With Any Source.
3. Select the source connection and schema (e.g. myapp_dev), then the target connection and schema (e.g. myapp_prod).
4. Click through to the Select Changes to Apply screen. Workbench lists every difference: missing tables, changed column definitions, different indexes, foreign keys, and routines.
5. For each difference choose the direction — update the target, update the source, or ignore.
6. Review the generated SQL on the final screen. Copy it and run it manually on a staging copy first rather than executing directly against production.

If both schemas are on the same server, Database → Compare Schemas does a read-only comparison and simply reports differences without generating migration SQL — safer when you only need a report.

(Add your own screenshot of the diff screen here.)

Method 2: mysqldump + diff (no GUI needed)

Dump only the schema of both databases and compare the files:

mysqldump --no-data --skip-comments --skip-dump-date -u root -p db_one > one.sql
mysqldump --no-data --skip-comments --skip-dump-date -u root -p db_two > two.sql
diff one.sql two.sql

--skip-dump-date and --skip-comments keep timestamps out of the files so diff shows only real schema changes. Drop --no-data if you also want to compare data — but only for small tables.

Method 3: Comparing data, not just structure

To verify whether the rows differ, use MySQL’s built-in checksum:

CHECKSUM TABLE db_one.orders, db_two.orders;

Identical checksums mean identical data. If they differ and you need the exact rows, see my guide on getting row-level differences between two MySQL tables.

Which method should you use?

Use Workbench when you need migration SQL generated for you; use mysqldump + diff on servers where you only have SSH; use checksums when structure matches but you suspect data drift. For automated CI checks, script Method 2 — it needs nothing but the MySQL client.

FAQ

Does this work with MariaDB? Yes — Workbench connects to MariaDB for schema comparison, and the mysqldump method is identical (use mariadb-dump).

Can Workbench sync data too? No, Schema Synchronization covers structure only. For data sync, use mysqldbcompare-style tooling or replication.

Comments

comments