How to test mysql database and records have migrated server correctly?

Question

I have to move database to a new server. I do a mysqldump of the database from server A and import it into server B. I then do a dump from server B and compare this with the dump from server A, via diff on bash. This fails, some lines are mismatched, although manual check shows no data lines involved. Both servers run slightly different versions of mysql 5.0.x.

mysqldump command used:

mysqldump -u user -p mydb > mydbA.sql

Then after some research I did this, using tools on bash:

1. mysqldump on old server A , a full dump and data only dump.
2. create database on new server B and import full dump from A.
3. then data only dump from server B.
4. do a diff comparing data dump A and data dump B.
5. should exit with 0 - from echo $?
6. MD5 also gives same hash.

Both servers run slightly different versions of mysql 5.0.x.

'Full' dump

mysqldump -u user -p mydb > mydbFULL.sql

Data only dump

mysqldump --no-create-info --skip-opt --skip-comments --skip-extended-insert --compact -u user -p mydb > mydbDATAonly.sql

Answer 1

You may want to check into Percona Toolkit for MySQL. The pt-table-checksum tool will run checksums on a per table basis. While this is designed for a MySQL replication, I suspect you can also use it to compare migrated data.

Reference: http://www.percona.com/software/percona-toolkit