MySQL Compare Databases

Sometimes you may need to compare two databases in MySQL. Here’s how to compare databases in MySQL using command line utility.

How to Compare databases in MySQL

Here are the steps to compare databases in MySQL.

Let us say you want to compare two MySQL databases db1 and db2.

First, we will take backup of both databases, that is, create .sql files for each database

# mysqldump --skip-comments --skip-extended-insert -u root -p db1>db_file1.sql
# mysqldump --skip-comments --skip-extended-insert -u root -p db2>db_file2.sql

Bonus Read : MySQL Alter Stored Procedure

In the above commands, –skip-comments skips comments and –skip-extended-insert creates separate insert statement for each row, making it easier to compare databases.

Second we use the diff tool to compare these two files

# diff db_file1.sql db_file2.sql

If you want to use a GUI tool to compare MySQL databases, try TOAD for MySQL. It is free.

Hopefully, now you can easily compare databases in MySQL.