mysql> create database lanlan;

Query OK, 1 row affected (0.00 sec)

主庫插入完數據1秒以后,從庫執行show databases;查看數據是否及時同步了,結果如下：

mysql> show databases;

+——————–+

| Database?????????? |

+——————–+

| information_schema |

| alex_python??????? |

| mysql????????????? |

| performance_schema |

+——————–+

在從庫上并沒有看到在主庫上創建的數據庫lanlan,此時執行間歇性的執行show slave status\G查看延遲的參數狀態如下輸出.

mysql> show slave status\G

…省略若干…

SQL_Delay: 20

SQL_Remaining_Delay: 13

#剩于13秒執行復制.

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

…省略若干…

1 row in set (0.00 sec)

mysql> show slave status\G

…省略若干…

SQL_Delay: 20

SQL_Remaining_Delay: 9

#剩于9秒執行復制.

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

…省略若干…

1 row in set (0.00 sec)

mysql> show slave status\G

…省略若干…

SQL_Delay: 20

SQL_Remaining_Delay: NULL

#復制完成后,沒有新數據更新的狀態.

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

…省略若干…

1 row in set (0.00 sec)

在從庫沒有更新數據處于延遲復制沒到時間期間,查看從庫的中繼日志.

[root@db02 data]# pwd

/application/mysql/data

[root@db02 data]# mysqlbinlog db02-relay-bin.000002

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create database lanlan

#中繼日志確已經有了創建的語句,說明IO線程還是實時在工作的.

3.MySQL延遲復制原理解析

MySQL的延遲復制實際上影響的只是SQL線程將數據應用到從數據庫,而I/O線程早已經把主庫更新的數據寫入到了從庫的中繼日志中,因此,在延遲復制期間即使主庫宕機了,從庫到了延遲復制的時間,依然會把數據更新到和主庫宕機時一致.

特別提示：其實MySQL的延遲復制的功能早在幾年前,老男孩老師就已經用思想實現了這個功能, 并應用于企業生產備份和恢復中了,方法如下：

1)15.2節已經介紹過的,執行mysql> stop slave sql_thread;把SQL線程停掉,然后進行備份,備份期間主庫宕機,但是主庫的Binlog依然會及時發到從庫,最終從庫依然可以恢復到和主庫宕機前的狀態.

2)寫一個腳本,利用定時任務控制sql_thread的停止和運行,進而庫就可以控制實現簡單的從庫延遲復制功能了,這就是思想的重要性.當然了5.6版本就用軟件提供的功能吧,5.6以前的數據庫要想實現延遲復制,可以思考下老男孩曾經用過的延遲備份以及延遲復制的思路.

4.MySQL延遲復制恢復案例實踐

在企業中,我們要根據業務需求給延遲復制指定一個時間段,例如1個小時后進行該從庫復制,那么在這一個小時內,如果主庫誤更新了數據,那么其他的從庫也都傻傻地誤更新了數據,如何將這個延遲的從庫恢復正常沒有誤更新數據前的完整狀態呢?且看下文的實踐.

(1)將從庫延遲調整為1小時

模擬環境,將從庫延遲調整為3600秒；

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY = 3600;

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

mysql> show slave status\G

…省略若干…

SQL_Delay: 3600

SQL_Remaining_Delay: 2414

Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

…省略若干…

1 row in set (0.00 sec)

(2)模擬在主庫寫入數據

每隔5秒寫入1個庫,就當模擬用戶寫入數據了.

[root@db01 ~]# for n in {1..5}

> do

>???? mysql -e “create database oldboy$n”

>???? sleep 5

> done

提示：Shell腳本知識可參考《跟老男孩學習Linux運維:Shell編程實戰》一書.

(3)模擬人為破壞數據

模擬人為破壞數據也可以是不帶where的update語句.

mysql> drop database oldboy5; ??

#刪除oldboy5數據庫,后面就是把這個數據恢復回來,別的數據還得有.

Query OK, 0 rows affected (0.00 sec)

mysql> show databases like ‘oldboy%’;

+——————–+

| Database (oldboy%) |

+——————–+

| oldboy1???? ???????|

| oldboy2??????????? |

| oldboy3??????????? |

| oldboy4??????????? |

+——————–+

4 rows in set (0.00 sec)

#此時,所有的從庫都已經是壞數據了,只有延遲從庫是好的,但是是一小時前的數據.

(4)停止寫庫恢復數據

當數據庫出現誤刪數據情況時,要想完整恢復數據(特別是update不加條件破壞數據),最好選擇對外停止訪問措施,需要犧牲用戶體驗,除非業務可以忍受數據不一致,并且不被二次破壞.從庫可以適當繼續開放給用戶讀訪問,但是也可能會導致用戶讀到的數據是壞的數據,需要讀者去衡量數據一致性和用戶體驗的問題.本例使用iptables封堵用戶對主庫的所有訪問.

[root@db01 ~]# iptables -I INPUT -p tcp –dport 3306 ! -s 172.16.1.51 -j DROP?

#非172.16.1.51禁止訪問數據庫3306端口,51是主庫IP.

(5)檢查Binlog發送和接收是否完成

登錄主庫執行show processlist;對Binlog是否全部發送到該延遲從庫進行確認,當然了,也可以登錄延遲從庫執行show processlist;對從庫IO線程是否接收完全部Binlog進行狀態查詢確認.

mysql> show processlist;

+—-+—————————————+—————+

| 12 | rep? | 172.16.1.52:39043 | NULL | Binlog Dump |? 709 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL???????????? |

+—-+————————————+——————+

2 rows in set (0.00 sec)

#上述提示表示主庫已經發送完所有Binlog日志到從庫了.

(6)從庫暫停主從復制,并檢查數據

從庫上執行stop slave;暫停主從復制,并查看數據庫是否同步過來.

mysql> stop slave;

mysql> show databases;

+——————–+

| Database?????????? |

+——————–+

| information_schema |

| alex_python??????? |

| mysql????????????? |

| performance_schema |

+——————–+

4 rows in set (0.00 sec)

#提示：因為延遲時間還未到,因此數據不會同步到該延遲從庫.

(7)定位恢復數據對應中繼日志?

根據relay-log.info記錄的SQL線程讀取relay-log的的位置解析未應用到從庫的relay-bin日志.

[root@db02 data]# pwd ??????????

#進入到中繼日志所在的目錄.

/application/mysql/data

[root@db02 data]# ls -l *relay*?

#查看中繼日志相關信息.

-rw-rw—-. 1 mysql mysql 172 9月? 13 17:32 db02-relay-bin.000001

-rw-rw—-. 1 mysql mysql 993 9月? 13 17:37 db02-relay-bin.000002

-rw-rw—-. 1 mysql mysql? 48 9月? 13 17:32 db02-relay-bin.index

-rw-rw—-. 1 mysql mysql? 61 9月? 13 17:32 relay-log.info

#SQL線程讀取中繼日志位置信息.

[root@db02 data]# cat relay-log.info ?

#查看中繼日志應用的位置信息.

7

./db02-relay-bin.000002? #SQL線程讀取中繼日志的文件名信息.

284? ????????????????????#SQL線程讀取中繼日志位置點信息.

oldboy-bin.000024

309

3600

0

1

(8)解析需要的中繼日志

解析SQL線程未解析的全部剩余relay-bin中繼日志數據,由于模擬數據量不夠大,因此本例里只有db02-relay-bin.000002一個中繼日志,實際工作中可能有多個,一并解析到一個指定文件或者分不同的文件存放也可.

[root@db02 data]# mysqlbinlog –start-position=284 db02-relay-bin.000002 >relay.sql?

#根據上述的relay-log.info的中級日志文件和位置信息進行解析中繼日志,此命令的用法前面章節已經講解過了,此不累述.

(9)從解析文件中刪除問題SQL

將破壞數據庫數據的SQL語句找到并從已解析的SQL語句中刪除,這里就是“drop database oldboy5”.

[root@db02 data]# egrep “drop database oldboy5” relay.sql ??

#檢查是否存在誤刪的SQL語句.

drop database oldboy5

[root@db02 data]# sed -i ‘/drop database oldboy5/d’ relay.sql

#刪除,注意別刪多了.

[root@db02 data]# egrep “^drop database oldboy5” relay.sql???

#檢查刪除結果.

(10)將處理好的SQL文件恢復到數據庫

將解析后并處理好的relay.sql數據文件恢復到延遲從庫.

[root@db02 data]# mysql<relay.sql ??

?#這步就是從停止slave復制開始,根據relay-log.info位置手工將剩下的所有日志數據恢復到數據庫中,需要注意就是提前要清理破壞數據庫的語句,在恢復.

[root@db02 data]# mysql -e “show databases like ‘oldboy%’;”

+——————–+

| Database (oldboy%) |

+——————–+

| oldboy1??????????? |

| oldboy2??????????? |

| oldboy3??????????? |

| oldboy4??????????? |

| oldboy5??????????? |?? #被刪除的oldboy5數據庫已經找回.

+——————–+

到此,利用延遲數據庫恢復數據完畢,將此庫提升為主庫(見手工實現主從角色切換章節內容),將VIP指向該“延遲從庫”,即新主庫提供用戶訪問,然后,在對其他的破壞的主從數據庫進行修復.