實現Mysql延時複製

Oracle可以透過設定歸檔的delay來設定DataGuard的Apply延時，達到某些人為錯誤發生後的容災。Mysql一直沒有這個功能，目前已知的大概也只有上的mk-slave-delay，不過透過一些辦法可以山寨延時複製，研究這個功能也是沒辦法，最近老有手下做錯事情，老是用mysqlbinlog挖的累了－ －，而某些業務資料庫是mysql 4.02的，沒有觸發器可以保護表。[@more@]

首先研究下mysql的複製結構，4.0以上開始，複製分為2個程式，io程式和sql程式。其中io程式連線到master讀取binlog，寫入relaylog，而sql程式讀取relaylog後apply到slave上。

binlog和relaylog格式dump出來是這樣的：

#090108 20:24:17 server id 1 log_pos 9466422 Query thread_id=34456 exec_time=0 error_code=0
SET TIMESTAMP=1231417457;
insert into xxxx (UDusedo,UDdirect,UDuserid,UDusername,UDgetuserid,UDgetusername,UDcoins,UDtype,UDzone1,UDtargetvalue,UDdate,UD
ip,UDstatus) values ('33','n','7495715','LWGZOY','7495715','LWGZOY','1000','prop','3','56009376',now(),'116.53.1.144','00');

log中有SET TIMESTAMP=1231417457是為了防止slave和master之間時間不同造成某些時間欄位值不一致的情況。其實也等同於這句sql在master上執行的時間,那麼我們只要獲取到它再和當前slave上的時間比較,如果少於我們需要的延時就讓複製停下,這不就實現了延時複製麼.

在mysql中可以透過函式UNIX_TIMESTAMP獲取到和上面的TIMESTAMP同樣的結果。

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1231750815 |
+------------------+
1 row in set (0.00 sec)

如何挖relaylog的尾部是個問題，因為日誌可能很大，所以我們要藉助下面的命令：

[root@HB-150-189 data]# mysql -e "show slave statusG"
*************************** 1. row ***************************
Master_Host: 192.168.1.104
Master_User: rep
Master_Port: 3306
Connect_retry: 60
Master_Log_File: HBDB104-bin.104
Read_Master_Log_Pos: 534427423
Relay_Log_File: HB-150-189-relay-bin.070
Relay_Log_Pos: 20200284
Relay_Master_Log_File: HBDB104-bin.104
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db: aushop,auhj
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 534427423
Relay_log_space: 20200284

其中得到Relay_Log_File的名字，Relay_Log_Pos: 20200284,透過這個可以得到一個offset,以此來挖掘relaylog來獲取到最近的TIMESTAMP,然後使用：

mysqlbinlog -j 20200284./HB-150-189-relay-bin.070|grep "SET TIMESTAMP"|sed -n '1p'

在加上shell的處理就可以輕鬆獲取最近的TIMESTAMP了

再看下show slave status的輸出，版本為4.0.26：

mysql> show slave statusG
*************************** 1. row ***************************
Master_Host: 192.168.1.184
Master_User: rep
Master_Port: 3306
Connect_retry: 60
Master_Log_File: HBDB184-bin.072
Read_Master_Log_Pos: 358310392
Relay_Log_File: HB150-130-relay-bin.076
Relay_Log_Pos: 348847513
Relay_Master_Log_File: HBDB184-bin.072
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db: money
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 358310392
Relay_log_space: 348847513
1 row in set (0.00 sec)

輸出資訊包括了Slave_IO_Running， Slave_SQL_Running的狀態。

mysql 4以後，可以透過STOP SLAVE IO_THREAD來停止io程式的活動，STOP SLAVE SQL_THREAD來停止sql程式的活動,大家只要使用shell結合crontab和nohup，加上對

[root@HB-150-189 data]# mysqladmin extended-status|grep Slave_running
的監視就可以達到mysql延時複製的功能，達到防止人為錯誤的作用。