Unique Marketing, Guaranteed Results.

Database syncronization woes

November 3rd, 2009 by Narshlob

Database resyncronization depends on what went wrong but the steps below will most likely solve most issues.
Run these commands on the slave database

  1. STOP SLAVE; # stop the Slave I/O threads
  2. RESET SLAVE; # forget about all the relay log files

Then go to the master database and run these

  1. RESET MASTER; # reset the bin log counter and wipe out bin log files
  2. FLUSH TABLES WITH READ LOCK; # flush buffers and LOCK tables
  3. show master status\G

Note what the show master status command returns. You’ll need to know the file name and the position.
You can do one of two things here, make a dump of the entire master database (in which case I suggest you follow this)
or you can just update the tables.
Usually we just need to update the tables so release the lock on the master database tables (UNLOCK TABLES;) and then run this command on the slave database (download maatkit tools here),

  • cd ~/maatkit-5014/bin && sudo ./mk-table-sync –[print][execute] u=[user],p=[pass],h=[master_host_name] –databases [database_name(s)] localhost

I suggest you run –print before you run –execute. If you run –execute first, you have no idea what just happened. –print will let you know what it’ll do without actually doing anything.
Back to the slave database mysql client, issue these commands,

  1. CHANGE MASTER TO MASTER_LOG_FILE='[file name from show master status command]’, MASTER_LOG_POS=[pos];

Run this command,

  • show slave status\G

And check that these aren’t NO or NULL,

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 1634

If things aren’t back to normal, follow the instructions on this website.

Copyright © 2005-2016 PMA Media Group. All Rights Reserved &nbsp