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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29733787/viewspace-1466183/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29733787/viewspace-1466183/