When data is moved from one database or server to another, we need to compare two tables to identify if the data is valid to be migrated or not. If we have a legacy database whose data has to be migrated to a new database, with a different SCHEMA, we can use UNION ALL statement. When we have to compare databases from different servers mysqldbcompare utility should be used.

UNION ALL

In the example below, the UNION ALL statement is used to compare data of 'tblmigrate' and 'tblpatients' tables:

SELECT tblmigrate.id, tblmigrate.Name FROM tblmigrate

UNION ALL

SELECT tblpatients.PatientID, tblpatients.NameOfPatient FROM tblpatients;

Such a code will compare the 'PatientID' and 'NameOfPatient' columns of the table 'tblpatients' to 'id', 'Name' columns of 'tblmigrate' table. To check if there are any repetitions of rows we can use count(*) function grouped by id's. If there are two row with same id, count(*) returns 2. Else it returns 1:

SELECT id, name, count(*) FROM

(SELECT tblmigrate.id, tblmigrate.Name FROM tblmigrate

UNION ALL

SELECT tblpatients.PatientID, tblpatients.NameOfPatient FROM tblpatients)

AS t

GROUP BY id;

The result from wil produce something like:

id name count(*)
1 Shahanaj 2
2 David 2
3 Ramesh 2
4 Saif 1
5 Roshan 1

 

 

 

 

 

Count(*) as 2 indicates that the entries are present in both of the tables.

The mysqldbcompare

The mysqldbcompare utility compares the objects and data from two different databases and tries to find differences. It identifies objects having different definitions in two databases and presents in 'diff-style' way in GRID, CSV, TAB or VERTICAL format. This utility is handy when we are checking master-slave consistency, generating report on difference in data between two databases... etc. Syntax for mysqldbcompare is:

mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql database1:database2

Where:

  • database1 is in server 1
  • database2 is in server 2.
  • difftype specifies the display format of the differences.

The objects considered in databases include tables, views, triggers, procedures, functions, and events. When the test is run the object definitions are compared first, then the row count test is performed for both tables. Any differences found in these phases the test halts saying the check is failed and the data are different. If we want the utility to continue running even if an error ocurres, we use --run-all-tests options in the command. Following example shows how to do it:

mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql database1:database2 ?run-all-tests