WO Blog

Slave out of sync with your master?

Hello, I am not growing a Mo. So I have to blog about something technical…

One of our major clients has a Master-Replication setup which we use for failover and backups. However it managed to get itself out of sync. A small example is that it the user table is missing a single user. The database is so large it is impossible to be sure that this is the only thing missing.

Thankfully there are tools to help in this situation provided by Maatkit, sadly the documentation is a bit vague and lacks direct examples.

Here are my simple steps to get a slave in sync with its master. I would make sure that try this is a demo environment first as during my experiments with these tools it managed to remove data from the master…which is bad!

Make sure that replication is working before you start this.

1) In the database (mydb) on the master I want to create the following table, this stores the checksum data:

CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)
);

2) On the master run the following command, which stores the checksum data in the table we just created:

mk-table-checksum -d mydb --replicate mydb.checksum 127.0.0.1

3) On the slave run the following command, what this does…I am a touch vague about but it works…read the docs see if you can explain it:

mk-table-sync -d mydb --replicate mydb.checksum --sync-to-master --no-foreign-key-checks --execute 127.0.0.1

I hope that this is of some use to someone!

Post a Comment

Your email address is never published nor shared. Required fields are marked *

*
*

WO BlogGreat stuff about the web

Book Club – ‘Getting Things Done’ by David Allen

by Frances

Introducing DO PM: Oxford’s First Digital Project Management Meet-Up

by Stephen

BIRDIE – A new photography conference from White October Events!p

by Vicky

See WO blog

Careers at WOJoin the team

We are currently hiring:

Not quite what you're looking for? If you'd like to hear about opportunities to join our team in the future, you can sign up to receive email notifications

Our next eventsWe organise and sponsor events

  1. Emerging Tech Weekender

    A hack weekend bringing together Oxford’s finest business brains designers and developers to explore new opportunities for bleeding edge digital tech.

  2. DO PM Gets Lean

    We’re really excited to announce that Frank Nigriello - Director of Corporate Affairs at Unipart Group Ltd is our guest speaker this month.