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

Press Release: Lambeth Library Challenge wins Nominet Internet Award

by Clare

Knowledge sharing!

by Rich

Press Release: European Leadership Network launches new website.

by Clare

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. Venturefest 2013, Said Business School, Oxford

    Find us on Stand #6 at THE business creation networking event of the year.

  2. UX Oxford #17

    The speaker of this month will be Tom Fletcher, UX Designer at BBC.

  3. All Your Base Conference

    AYB is a database conference for web developers