MySQL主從複製一致性檢驗和同步修復

Master和Slave可能會出現數據不一致:

- 寫入Master時使用了對複製不安全的語句(如:CURRENT_USER(), UUID())

- 不正確的故障轉移(failover)流程

- 開發者直接寫入Slave

- 持續的升級更新(Rolling upgrades)

- 混合使用事務引擎和非事務引擎的表

- 跳過了複製事件 (SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N)

- 使用臨時表(Temporary Tables)

- 複製過濾(Replication Filters)

- 使用含LIMIT且沒有order by的更新語句(update/delete with LIMIT clause without order by)

1. pt-table-checksum可在線進行主從複製一致性檢驗

步驟如下:

1)在Master上創建用於檢驗的mysql用戶

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'ptcheck'@'192.168.1.190' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

在用戶根目錄創建.my.cnf保存用戶和密碼:

[root@centos190 www]# cat ~/.my.cnf

[client]

user=ptcheck

password=123456

2)使用pt-table-checksum進行主從複製一致性檢查

pt-table-checksum的一些參數說明：

DSN h='192.168.1.190',u='ptcheck',p='123456',P=3306

--databases 指定校驗的庫,多個庫使用逗號分隔

--tables 指定檢查的表,多個表使用逗號分隔

--ignore-tables 忽略對某些表的檢查,多個表使用逗號分隔

--nocheck-replication-filters 忽略replication-do-db規則

--replicate=percona.checksums 在percona庫下創建checksums表,並將數據寫入

--recursion-method 指定複製檢查的方式,默認為processlist,使用非3306埠的時候為hosts.推薦手動指定dsn.

METHOD USES

=========== ==================

processlist SHOW PROCESSLIST

hosts SHOW SLAVE HOSTS

dsn=DSN DSNs from a table

none Do not find slaves

*只檢查資料庫testdb的表t1和tbl.

[root@centos190 www]# pt-table-checksum h='192.168.1.190',u='ptcheck',p='123456',P=3306 --databases testdb --tables t1,tbl --nocheck-replication-filters --create-replicate-table --replicate=test.checksums

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

02-27T01:28:25 0 1 7 1 0 1.720 testdb.t1

02-27T01:28:26 0 1 13 1 0 0.662 testdb.tbl

註：--create-replicate-table僅在第一次檢測的時候使用,第二次檢測使用--no-create-replicate-table或是乾脆不使用這個參數.

*對除tbl表之外其他表的一致性檢查

[root@centos190 www]# pt-table-checksum h='192.168.1.190',u='ptcheck',p='123456',P=3306 --databases testdb --ignore-tables tbl --nocheck-replication-filters --no-create-replicate-table --replicate=pts.checksums

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

02-27T01:30:11 0 1 7 1 0 1.711 testdb.t1

02-27T01:30:13 0 1 8 1 0 1.641 testdb.test

*使用用戶根目錄下.my.cnf保存的用戶和密碼:

[root@centos190 www]# pt-table-checksum --databases testdb --replicate=pts.checksums h=192.168.1.190,P=3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

02-27T01:31:03 0 1 7 1 0 0.687 testdb.t1

02-27T01:31:04 0 1 13 1 0 0.765 testdb.tbl

02-27T01:31:06 0 1 8 1 0 1.745 testdb.test

*使用dsn方式做一致性檢查

創建保存從庫dsn信息的數據表

mysql> create schema if not exists pts;

mysql> use pts;

mysql> CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );

插入從庫的dsn信息:從庫dsn格式為: 『h=replica_host,u=repl_user,p=repl_pass』

mysql> insert into dsns (parent_id,dsn) values(1, 'h=192.168.1.192,u=ptcheck,p=123456,P=3306');

mysql> insert into dsns (parent_id,dsn) values(1, 'h=192.168.1.193,u=ptcheck,p=123456,P=3306');

pt-table-checksums使用dsn的方式為:

--recursion-method dsn=h=host,D=percona,t=dsns

[root@centos190 www]# pt-table-checksum --databases testdb --replicate=pts.checksums --recursion-method=dsn=D=pts,t=dsns h=192.168.1.190,P=3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

02-27T01:46:44 0 1 7 1 0 1.739 testdb.t1

02-27T01:46:46 0 1 13 1 0 1.688 testdb.tbl

02-27T01:46:48 0 1 8 1 0 1.686 testdb.test

備註:在運行pt腳本之前使用export PTDEBUG=1,可以獲得運行的debug信息.

2. 使用pt-table-sync同步修復主從不一致的表數據

pt-table-sync只同步數據表的數據,不同步表的結構.

基本用法：

./pt-table-sync master-host slave-host

./pt-table-sync --replicate master-host

./pt-table-sync --sync-to-master slave-host

*基於checksums信息對Slave 192.168.1.192同步testdb.tbl表數據

[root@centos190 www]# pt-table-sync --execute --replicate=pts.checksums --databases=testdb --tables=tbl --sync-to-master h=192.168.1.192,u=root,p=123456

*基於checksums信息對所有的Slave同步testdb.tbl表不一致的數據

[root@centos190 www]# pt-table-sync --execute --replicate=pts.checksums --databases=testdb --tables=tbl h=192.168.1.190,u=root,P=3306 --ask-pass

Enter password for 192.168.1.190:

*全量同步修復Slave的testdb.tbl表數據

pt-table-sync --execute --sync-to-master h=192.168.1.193,u=root,p=123456 --databases=testdb --tables=tbl

或是

pt-table-sync --execute --sync-to-master h=192.168.1.193,D=testdb,t=tbl,u=root,p=123456

*災難修復,直接同步主從資料庫testb

pt-table-sync --execute --sync-to-master h=192.168.1.193,P=3306,u=root,p=123456 --database testdb

或使用--print獲得操作的SQL語句,檢查後手動在Slave執行操作:

[root@centos190 www]# echo "SET SQL_LOG_BIN=0;" > ./SQLChanges.sql

[root@centos190 www]# pt-table-sync --print --sync-to-master h=192.168.1.193,P=3306,u=root,p=123456 --databases testdb >> ./SQLChanges.sql

[root@centos190 www]# echo "SET SQL_LOG_BIN=1;" >> ./SQLChanges.sql

[root@centos190 www]# mysql -h 192.168.1.193 -uroot -p testdb < SQLChanges.sql