Archive for the ‘MySQL’ Category

Skipping records with MySQL replication

Tuesday, October 18th, 2011

I manage several replicated MySQL DB servers. It seems like they break anytime we have a glitch in the network or with our hardware. I used to rebuild MySQL from scratch, now I just skip a record or two and see if it starts replicating properly.  Here how I skip records.  First log into MySQL as root then…

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

wait a second or two then…
SHOW SLAVE STATUS\G

If your slave status shows Yes/Yes and your Seconds behind is 0 or greater and not NULL then you are in good shape!
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
                         ...
      Seconds_Behind_Master: 0