Comparing Mysql databases

This is often a need to compare two databases in terms of schema and data. This might be required to compare with production databases or in test environment to ensure that the data values has not changed. For the same, Mysql has a utility called mysqldbcompare. Let's see how to use it. First the installation.

Installation

You can either download from you os repositories or can download it directly from the mysql site. Do a search for mysql-utitlies and you can reach to the page. One way to do it in Linux is as follows

wget https://dev.mysql.com/downloads/file/?id=458907wget https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.6.tar.gz

This will download the file in your current directory. Than unzip it and run the python command to install it.

tar -xvf mysql-utilities-1.5.6.tar.gz

cd mysql-utilities-1.5.6

sudo python setup.py install

Now let's say we want to compare two database db1 and db2 in a machine called s1. The command goes like

mysqldbcompare --server1=<user>:<password>@s1:3306 db1:db2

Here <user> is the db user and <pass> is the password of the user. If the db are residing in two different machine called s1 and s2 the command becomes

mysqldbcompare --server1=<user>:<password>@s1:3306 --server2=<user>:<password>@s2:3306 db1:db2

By default it stops at first failure. If you want to run it complete than provide --run-all-tests option

mysqldbcompare --server1=<user>:<password>@s1:3306 --server2=<user>:<password>@s2:3306 db1:db2  --run-all-tests

It comes with a lot of switches. For details refer to the mysql documentation page. 

The downside of this approach seems to be that the comparison cannot be configured to handle certain columns of the table only. This becomes important as sometimes one wants to ignore audit columns like create and update dates.