0

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Hasanuzzaman Sattar
  • 592
  • 1
  • 5
  • 20
  • 1
    Questions asking us to recommend or find a tool or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers – Raymond Nijland May 30 '18 at 13:44

1 Answers1

0

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.

Red Boy
  • 5,429
  • 3
  • 28
  • 41
  • Thank you very much for your valuable reply. How can I write the script in Windows and run? Could please explain some more details about the procedure. Excuse me as I am very new in this type of task. – Hasanuzzaman Sattar May 31 '18 at 07:04
  • Just copy, paste and it to save the above code say file with name `C:\somepath\compare.cmd`. Then, open cmd prompt on your windows, go `cd` to `C:\somepath\` and call `compare.cmd` Thats it. – Red Boy May 31 '18 at 07:10
  • I have dumped database from master and slave as well. After that I run the command as `mysqldiff macmillan-server9.sql macmillan-server8.sql > diff.sql`. But I am getting error, **ERROR: Can't connect to MySQL server on 'localhost:3306' (10061 No connection could be made because the target machine actively refused it)**. FYI these two files are raw dumped SQL file from servers. Should I need to import it in local mysql? – Hasanuzzaman Sattar May 31 '18 at 09:22
  • It simple means, you database on localhost is not working – Red Boy May 31 '18 at 09:49
  • I am trying to run **mysqldiff** on dumped.sql file. Is not it possible? – Hasanuzzaman Sattar May 31 '18 at 10:41
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172165/discussion-between-hasanuzzaman-sattar-and-red-boy). – Hasanuzzaman Sattar May 31 '18 at 13:10