1

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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.