I saw a post by Baron mentioning that his tool maatkit is best for handling situations where a master-master replication setup has got out of sync.
If you think Baron was blowing his own trumpet he has good reason to. I have used his mk-archiver tool as part of the Maatkit to make the problem of archiving and purging data much easier. This was much easier than rolling my own solution.
Anyhow. I have a master-master replication just lying around to test this kind of stuff after finishing the multi-instance master-master replication pair last week. Plus I have already had some past experience using table-checksum tool which is part of Maatkit (or MySQL toolkit as it used to be known). Amazing how you publish some stuff and then you get the next idea for an article almost immediately.
Overview:
To test Baron’s assertion that Maatkit mk-table-sync is the best tool to re-sync a master-master replication pair.
Install:
- Setup a master-master replication pair.
- Download and install Maatkit.
- Load the Sakila sample database.
- Make some changes on one master which are not replicated to the other.
- Verify that the tables are out-of-sync using mk-table-checksum
- Use mk-table-sync to re-sync the tables.
Summary:
Again Maatkit lives up to my expectations. Both mk-table-checksum and mk-table-sync discovered and re-synced the tables without any issues.
On a side note, without this toolkit, you could still have rebuilt the other master from the dedicated slave, but this is so much faster and easier.
Ideas for the motivated reader:
- As always be careful when using set global sql_slave_skip_counter = 1;
- Download and test the Maatkit tools for yourself.
- Run mk-table-checksum periodically to discover if your slaves are actually consistent with your master.
- Having multiple recovery methods is good, you should be experienced and confident to use them all.
Detailed Screen Dump with comments:
Maatkit Dependencies
yum install perl-DBI perl-DBD-MySQL gcc
Time::HiRes
wget http://search.cpan.org/CPAN/authors/id/J/JH/JHI/Time-HiRes-1.9712.tar.gz
download maatkit-1753 from Sourceforge
perl Makefile.PL
make install
Break the master-master replication
On master2:
stop slave;
On master1:
mysql> use sakila
Database changed
mysql> insert into film values(1001,'Welcome to DBA Dojo','A place on the way towards being a DBA',2007,1,1,30,45,25.00,90000.00,'G','Behind the Scenes',now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from film where film_id = 1001;
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features | last_update |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| 1001 | Welcome to DBA Dojo | A place on the way towards being a DBA | 2007 | 1 | 1 | 30 | 45.00 | 25 | 999.99 | G | Behind the Scenes | 2008-03-03 05:42:58 |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)
On master 2
mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from film where film_id = 1001\G
Empty set (0.00 sec)
On Master 1 run a simple table checksum
mk-table-checksum h=master1,u=root,p=$PASSWD h=master1 --databases=sakila
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 1 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
Check the local slave
mk-table-checksum h=master1,u=root,p=$PASSWD h=slave1 --databases=sakila
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila actor 0 slave1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila address 0 slave1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila category 0 slave1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila city 0 slave1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila country 0 slave1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila customer 0 slave1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 slave1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_actor 0 slave1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_category 0 slave1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila film_text 0 slave1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila inventory 0 slave1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila language 0 slave1 MyISAM NULL 19972916 0 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 0 0 NULL NULL
sakila payment 0 slave1 MyISAM NULL 684052380 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila rental 0 slave1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila staff 0 slave1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
sakila store 0 slave1 MyISAM NULL 1107595282 0 0 NULL NULL
Creating the CHECKSUM table
mysql> use mysql
Database changed
mysql> CREATE TABLE checksum (
-> db char(64) NOT NULL,
-> tbl char(64) NOT NULL,
-> chunk int NOT NULL,
-> boundaries char(64) 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)
-> ) ENGINE=InnoDB;
Forget the port and you will get this error
mk-table-checksum h=master1,u=root,p=$PASSWD h=master2,u=root,p=$PASSWD \
--databases=sakila
DBI connect(';host=master2;mysql_read_default_group=mysql','root',...) failed: Can't connect to MySQL server on 'master2
And we have a winner, mk-table-checksum has found the issue with the film table
mk-table-checksum h=master1,u=root,p=$PASSWD \
h=master2,u=root,p=$PASSWD,P=3308 --databases=sakila
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
...
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 master2 MyISAM NULL 1421174266 0 0 NULL NULL
...
Now use Baron's mk-table-sync example... testing first.
mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--test
# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
# 0 0 0 0 Chunk sakila.film
Run the real thing
mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--verbose --execute
# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
# 0 1 0 0 Chunk sakila.film
On master2
mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)
On Slave 2, which is a cascade slave of master 1
mysql -u root -p$PASSWD -S /tmp/mysql2.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.20-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use sakila
Database changed
mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)
DBA Dojo 