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=123qwe
192.168.90.128
Version 5.6.19-log
Server ID 100
Uptime 02:13:42 (started 2015-03-19T09:52:44)
Replication Is not a slave, has 1 slaves connected, is not read_only
Filters
Binary logging ROW
Slave status
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB 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: 0
master:
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=1
2015-03-19T15:10:29 h=192.168.90.129,p=...,u=svoid rac2-relay-bin.000015 1442 1062
mysql> 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=1062
slave:
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-pass
shell> pt-slave-delay --delay=2m --interval=15s --run-time=8m u=root,h=192.168.90.129,P=3306 --ask-pass
Enter password for 192.168.90.129:
2015-03-19T15:39:28 slave running 0 seconds behind
2015-03-19T15:39:28 STOP SLAVE until 2015-03-19T15:41:28 at master position mysql-info.000006/2213
2015-03-19T15:39:43 slave stopped at master position mysql-info.000006/2213
master:
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-pass
Enter password for 192.168.90.129:
2015-03-19T15:39:28 slave running 0 seconds behind
2015-03-19T15:39:28 STOP SLAVE until 2015-03-19T15:41:28 at master position mysql-info.000006/2213
2015-03-19T15:39:43 slave stopped at master position mysql-info.000006/2213
2015-03-19T15:39:58 slave stopped at master position mysql-info.000006/2213
2015-03-19T15:40:13 slave stopped at master position mysql-info.000006/2213
2015-03-19T15:40:28 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:40:43 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:40:58 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:41:13 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:41:28 no new binlog events
2015-03-19T15:41:43 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:41:58 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:42:13 slave stopped at master position mysql-info.000006/2296
2015-03-19T15:42:28 START SLAVE until master 2015-03-19T15:40:28 mysql-info.000006/2296
2015-03-19T15:42:43 slave running 0 seconds behind
2015-03-19T15:42:43 STOP SLAVE until 2015-03-19T15:44:43 at master position mysql-info.000006/2296
2015-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/2483
2015-03-19T15:47:28 slave stopped at master position mysql-info.000006/2483
2015-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