percona之复制工具介绍

1. pt-slave-find

功能介绍：

连接master寻找和打印mysql slave复制拓扑

使用说明

shell> pt-slave-find [OPTIONS] [DSN]详情可参考shell> pt-slave-find --help不能通过连接到slave，打印其master

使用示例

shell> pt-slave-find --host=192.168.90.128 --user=svoid --password=123qwe192.168.90.128Version         5.6.19-logServer ID       100Uptime          02:13:42 (started 2015-03-19T09:52:44)Replication     Is not a slave, has 1 slaves connected, is not read_onlyFilters         Binary logging  ROWSlave status    Slave mode      STRICTAuto-increment  increment 1, offset 1InnoDB version  5.6.19+- 192.168.90.129   Version         5.6.19-log   Server ID       200   Uptime          02:13:29 (started 2015-03-19T09:52:57)   Replication     Is a slave, has 0 slaves connected, is not read_only   Filters            Binary logging  ROW   Slave status    0 seconds behind, running, no errors   Slave mode      STRICT   Auto-increment  increment 1, offset 1   InnoDB version  5.6.19

2. pt-slave-restart

功能介绍：

监视mysql复制错误，并尝试重启mysql复制当复制停止的时候

使用说明

shell> pt-slave-restart  [OPTIONS] [DSN]详情可参考shell> pt-slave-restart  --help监视一个或者多个mysql复制错误，当从停止的时候尝试重新启动复制。你可以指定跳过的错误并运行从到指定的日志位置。常用参数：--always   ： 即使复制没有错误仍重启从库--daemonize ：后台进程--[no]check-relay-log:检查复制错误前先检查最新的中继日志文件及位置--error-numbers=h：只重启指定的错误列表，多个用逗号间隔--error-text=s ：只重启匹配该模式的错误--master-uuid=s： 使用GTID复制，创建空的事务跳过--skip-count=i ： 重启复制调过语句数量，默认为1--until-master=s ： 运行到指定的主库日志文件及位置--until-relay=s  ： 运行到指定的中继日志文件及位置

使用示例

master:mysql> create table t (id int);Query OK, 0 rows affected (0.03 sec)slave:mysql> alter table t add primary key pk_id(id);Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0master:mysql> insert into t value(1);Query OK, 1 row affected (0.01 sec)mysql> insert into t value(1);Query OK, 1 row affected (0.02 sec)mysql> insert into t value(2),(3);Query OK, 2 row affected (0.01 sec)slave:mysql> show slave status \G*************************** 1. row ***************************             Slave_IO_Running: Yes            Slave_SQL_Running: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1062               Last_SQL_Error: Could not execute Write_rows event on table test.t; Duplicate entry '1' for key 'PRIMARY',                  Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-info.000006,                  end_log_pos 1434跳过1个错误shell> pt-slave-restart --host=192.168.90.129 --user=svoid --password=123qwe --skip-count=12015-03-19T15:10:29 h=192.168.90.129,p=...,u=svoid rac2-relay-bin.000015        1442 1062mysql> show slave status \G*************************** 1. row ***************************             Slave_IO_Running: Yes            Slave_SQL_Running: Yes                    Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error: mysql> select * from t;+----+| id |+----+|  1 ||  2 ||  3 |+----+3 rows in set (0.00 sec)master:mysql> insert into t value(3);Query OK, 1 row affected (0.01 sec)mysql> insert into t value(4);Query OK, 1 row affected (0.01 sec)跳过错误代码为1062的错误shell> pt-slave-restart --host=192.168.90.128 --user=svoid --password=123qwe --error-numbers=1062slave:mysql> select * from t;+----+| id |+----+|  1 ||  2 ||  3 ||  4 |+----+4 rows in set (0.00 sec)

3. pt-slave-delay

功能介绍：

指定master上的操作延迟指定的时间写入到slave

工作原理

pt-slave-delay工具通过启动和停止复制sql线程来设置从库落后与主库的时间。默认是基于从库上relay日志的binlog的位置来判断，因此不需要连接到主库，如果IO进程不落后与主库太多的话这个方式有效 ，如果IO线程延时过大，pt-slave-delay也可以连接到主库来获取binlog的位置信息。pt-slave-delay一般是通过 --delay参数和--interval参数来控制。执行该命令链接数据库的账号需要有 PROCESS、REPLICATION CLIENT、and SUPER权限。如果正在运行这个工具，Ctrl+C后他是友好退出，在退出前他会启动复制sql线程。

使用说明

shell>  pt-slave-delay [OPTIONS] SLAVE_DSN [MASTER_DSN]详情可参考shell> pt-slave-delay --help--delay：从库延迟主库的时间，默认为1小时，默认单位为s，s=seconds, m=minutes, h=hours, d=days--interval：检查是否启动或者停止从库上sql线程的间隔时间，默认是一分钟，注意：延迟的时间实际为 delay+interval。

使用示例

pt-slave-delay --delay=2m --interval=15s --run-time=8m u=root,h=192.168.90.128,P=3306 --ask-passshell> pt-slave-delay --delay=2m --interval=15s --run-time=8m u=root,h=192.168.90.129,P=3306 --ask-passEnter password for 192.168.90.129: 2015-03-19T15:39:28 slave running 0 seconds behind2015-03-19T15:39:28 STOP SLAVE until 2015-03-19T15:41:28 at master position mysql-info.000006/22132015-03-19T15:39:43 slave stopped at master position mysql-info.000006/2213master:mysql> truncate table t;Query OK, 0 rows affected (0.03 sec)mysql> select * from t;Empty set (0.00 sec)slave:mysql> select * from t;+----+| id |+----+|  1 ||  2 ||  3 ||  4 |+----+4 rows in set (0.00 sec)shell> pt-slave-delay --delay=2m --interval=15s --run-time=8m u=root,h=192.168.90.129,P=3306 --ask-passEnter password for 192.168.90.129: 2015-03-19T15:39:28 slave running 0 seconds behind2015-03-19T15:39:28 STOP SLAVE until 2015-03-19T15:41:28 at master position mysql-info.000006/22132015-03-19T15:39:43 slave stopped at master position mysql-info.000006/22132015-03-19T15:39:58 slave stopped at master position mysql-info.000006/22132015-03-19T15:40:13 slave stopped at master position mysql-info.000006/22132015-03-19T15:40:28 slave stopped at master position mysql-info.000006/22962015-03-19T15:40:43 slave stopped at master position mysql-info.000006/22962015-03-19T15:40:58 slave stopped at master position mysql-info.000006/22962015-03-19T15:41:13 slave stopped at master position mysql-info.000006/22962015-03-19T15:41:28 no new binlog events2015-03-19T15:41:43 slave stopped at master position mysql-info.000006/22962015-03-19T15:41:58 slave stopped at master position mysql-info.000006/22962015-03-19T15:42:13 slave stopped at master position mysql-info.000006/22962015-03-19T15:42:28 START SLAVE until master 2015-03-19T15:40:28 mysql-info.000006/22962015-03-19T15:42:43 slave running 0 seconds behind2015-03-19T15:42:43 STOP SLAVE until 2015-03-19T15:44:43 at master position mysql-info.000006/22962015-03-19T15:42:58 slave stopped at master position mysql-info.000006/2296...2015-03-19T15:47:13 slave stopped at master position mysql-info.000006/24832015-03-19T15:47:28 slave stopped at master position mysql-info.000006/24832015-03-19T15:47:28 Setting slave to run normally

补充：
从 MySQL 5.6开始支持延时备份，增加了新参数MASTER_DELAY：

mysql> change master to master_delay = N；N为从库延迟主库的秒数，默认是0。如果想要从库延迟1分钟，则可：mysql> stop slave;mysql> change master to master_delay = 60;mysql> start slave;

整理自网络

Svoid
2015-03-19