percona工具使用一 pt-table-sync

现在主从库准备一张表
mysql> select * from t1;
+-------+------+
| empno | name |
+-------+------+
|     1 | yao  |
|     2 | xin  |
|     3 | sun  |
|     4 | hai  |
|     5 | ying |
+-------+———+

在从库把这张表删了模拟数据的不一致
mysql> select * from t1;
+-------+------+
| empno | name |
+-------+------+
|     1 | yao  |
|     2 | xin  |
|     3 | sun  |
|     4 | hai  |
|     5 | ying |
+-------+------+
5 rows in set (0.00 sec)

mysql> delete from t1;
Query OK, 5 rows affected (0.01 sec)

mysql> select * from t1;
Empty set (0.00 sec)

此时主库表中有数据，从库的表中没有数据
我们使用pt-sync-table工具还同步这张表的数据
 pt-table-sync --charset=utf8 --print --no-check-slave -d test -t t1 h='10.37.129.3',u='root',p='oracle' h='10.37.129.4',u='root',p=‘oracle'
前面填主库的内容，后面填从库的内容

自动比较两边的数据，然后自动生成sql语句
[11:06:38 root@slave ~]# pt-table-sync --charset=utf8 --print --no-check-slave -d test -t t1 h='10.37.129.3',u='root',p='oracle' h='10.37.129.4',u='root',p='oracle'
INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('1', 'yao') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('2', 'xin') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('3', 'sun') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('4', 'hai') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('5', 'ying') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;

我们把这个sql语句再拷贝到从库上去执行
mysql> INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('1', 'yao') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('2', 'xin') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('3', 'sun') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('4', 'hai') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`t1`(`empno`, `name`) VALUES ('5', 'ying') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:A=utf8,h=10.37.129.3,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:A=utf8,h=10.37.129.4,p=...,u=root lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:30663 user:root host:slave*/;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-------+------+
| empno | name |
+-------+------+
|     1 | yao  |
|     2 | xin  |
|     3 | sun  |
|     4 | hai  |
|     5 | ying |
+-------+———+

主从表的不一致恢复
[11:26:46 root@slave ~]# pt-table-sync --charset=utf8 --execute --no-check-slave -d test -t t1 h='10.37.129.3',u='root',p='oracle' h='10.37.129.4',u='root',p='oracle'