How to compare two large database using mysql utilities

Question

I have two database servers those are replicated in Master to Master replication process. For some reason replication got stopped. Now I want to compare same database, EXAMPLE on both server for data consistency. But the problem is the database has a very large table. The table has 60 Millions rows. The mysql data size is 10GB around. I have tried to use mysqldbcompare command of MySQL utilities. This tools works very well in low data size. But in this case after an hour connection is dropped eventually by MySQL utilities.

Can anyone help me in this problem? Is there anyone analyzed large number of MySQL data and have any experience?

Please tell me the best way to start with. What tools should I use and how to use that, because I need to do this in very less amount of time.

Answer 1

Below script should solve your problem of comparison. Its divide and conquer algorithm, I'm applying here. This code is for Windows, slight change will work for any other OS.

Basically, here your data will be exported per table one file and then comparison will be done file by file i.e. table by table. This will reduce the data size.

mysql -h master-database -u masteruser -pmasteruser_Password masterdatabase --skip-column-names --execute='SHOW TABLES;' > tables.tmp
echo "Start!"
 while read p; do
  echo "Exporting Master" $p
  mysqldump -h master-database -u masteruser -pmasteruser_Password master_database $p > master_$p.sql
  echo "Exporting Slave" $p
  mysqldump -h slave-database -u slaveuser -pslaveuser_Password slave_database $p > slave_$p.sql
  echo "Doing Diff"
  ##I'm less familiar with this, but I believe it should work.
  mysqldiff master_$p.sql slave_$p.sql > diff_$p.sql
 done <tables.tmp

 rm tables.tmp

I hope this should solve your problem.