How to use percona toolkit to fix broken mysql replication
host1=my slave server (name or ip)
host2=my master server (name or ip)
Following will syncronizing missed data from master to slave
Thefore we need to take the last binlog file and key position on the master:
# mysql
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000022
Position: 474336476
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
On the slave:
mysql> slave stop;
mysql>CHANGE MASTER TO master_log_file='mysql-bin.000022',master_log_pos=474336476;
mysql> slave start;
Next , we going to synchronize all missed data from master
# pt-table-sync --verbose --print --execute h=host2 --database test h=host1
Sample out is:
DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
UPDATE `test`.`demo_test` SET `c2`=’core|a:3:{s:23:”_session_validator_data”;a:4:{s:11:”remote_addr”;s:13:”5.255.253.127″;s:8:”http_via”;s:0:””;s:20:”http_x_forwarded_for”;s:0:””;s:15:”http_user_agent”;s:64:”Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)”;}s:12:”visitor_data”;a:16:{s:0:””;N;s:11:”server_addr”;i:1054544702;s:11:”remote_addr”;i:100662655;s:11:”http_secure”;b:0;s:9:”http_host”;s:15:”musicgate.co.il”;s:15:”http_user_agent”;s:64:”Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)”;s:20:”http_accept_language”;s:42:”ru, uk;q=0.8, be;q=0.8, en;q=0.7, *;q=0.01″;s:19:”http_accept_charset”;s:0:””;s:11:”request_uri”;s:58:”/wishlist/index/add/product/942/form_key/jgjZFa9Idi12wjim/”;s:10:”session_id”;s:26:”00013fgiur7cecou15b8iu7j62″;s:12:”http_referer”;s:0:””;s:14:”first_visit_at”;s:19:”2015-05-12 18:36:27″;s:14:”is_new_visitor”;b:0;s:13:”last_visit_at”;s:19:”2015-05-12 18:36:27″;s:10:”visitor_id”;s:7:”2835829″;s:11:”last_url_id”;s:7:”2881777″;}s:8:”last_url”;s:82:”http://musicgate.co.il/core/index/noCookies/’, `c3`=’0′, `c4`=’8288′, `c5`=’101′ WHERE `c1`=’00013fgiur7cecou15b8iu7j62′ LIMIT 1 /*percona-toolkit src_db:test src_tbl:demo_test src_dsn:h=xx.xxx.xx.xx dst_db:test dst_tbl:demo_test dst_dsn:h=xx.xxx.xx.xx lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:19564 user:user host:myhost*/;
# 0 0 0 1 Chunk 17:04:11 17:04:26 2 test.demo_test
We need to stop/strt the slave
mysql> slave stop;
mysql> slave start;
To be sure all going well:
mysql> show slave status\G;
If:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
All work as expected.
The advantage of this method is not complex mysqldump and restore needed. No downtime.No table locks.
