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=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