master ip:192.168.136.135
slave ip:192.168.136.136
/*************************************************** pt-table-checksum *********************************/
/*************************************************** autocommit = 1 ***********************************/
SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM percona.checksums
WHERE (
master_cnt <> this_cnt
OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'percona'@'%' identified by 'Percona.0101';
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'percona'@'%';
flush privileges;
set autocommit=on;
刷新checksums:
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=percona.checksums --recursion-method=processlist --databases=test h=192.168.136.135,u=percona,p=Percona.0101,P=13306
显示差异数据:
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=percona.checksums --replicate-check-only --databases=test h=192.168.136.135,u=percona,p=Percona.0101,P=13306 --recursion-method=processlist
replicate-check-only : 当DIFFS为默认0时,需要将replicate-check-only添加上.
pt-table-checksum -uroot -p123456 --create-replicate-table --no-check-binlog-format
pt-table-checksum -uroot -p123456 --no-check-binlog-format --databases=flow,jlp --tables=flow.Rec_LineCommand_bak,jlp.Sys_ModalCfg
/*************************************************** pt-table-sync ************************************/
e.g:
Sync on 192.168.44.129 to 192.168.44.128:
/************************************************* 表有主键或只唯一性索引 **********************************/
pt-table-sync --execute h=host1,D=db,t=tbl h=host2
e.g:
----------------------------------------------------------------------------------------------------------------------------------------
cmd | option | checksum | master-host,db,table | slave-host
----------------------------------------------------------------------------------------------------------------------------------------
pt-table-sync --print --replicate=percona.checksums h=192.168.136.135,P=13306,u=percona,p=Percona.0101,D=test,t=m h=192.168.136.136
修复数据
pt-table-sync --execute --replicate=percona.checksums h=192.168.136.135,P=13306,u=percona,p=Percona.0101,D=test,t=m h=192.168.136.136
/************************************************* 表无主键或只唯一性索引 **********************************/
pt-table-sync --replicate=percona.checksums no-check-slave h=192.168.136.135,P=13306,u=percona,p=Percona.0101,D=test,t=t01 h=192.168.136.136 --print
pt-table-sync --no-check-slave h=192.168.136.135,P=13306,u=percona,p=Percona.0101,D=test,t=t01 h=192.168.136.136,P=13306,u=percona,p=Percona.0101,D=test,t=t01 --print
pt-table-sync --no-check-slave h=192.168.136.135,P=13306,u=percona,p=Percona.0101,D=test,t=t01 h=192.168.136.136,P=13306,u=percona,p=Percona.0101,D=test,t=t01 --execute
e.g:Make slave1 have the same data as its replication master:
pt-table-sync -uroot -p123456 --execute --execute --sync-to-master 192.168.44.128
e.g:Resolve differences that pt-table-checksum found on all slaves of master1:
pt-table-sync -uroot -p123456 --execute --replicate percona.checksums 192.168.44.129