實現MySQL資料庫的實時備份
使用MySQL Replication
吳劍 2018-08-03
原創文章，轉載必需註明出處：http://www.cnblogs.com/wu-jian
吳劍 http://www.cnblogs.com/wu-jian
前言
資料庫實時備份的需求很常見，MySQL本身提供了 Replication 機制，摘譯官方介紹如下：
MySQL Replication 可以將一個主資料庫中的資料同步到一個或多個從資料庫中。並且這個同步過程預設以非同步方式工作，不需要保持主從資料庫的實時連線（即允許連線中斷）。同時允許自定義配置需同步的資料庫及資料表。
MySQL Replication 的優點及應用場景如下：
1、通過 MySQL Replication 實現負載均衡與讀寫分離（主資料庫僅更新，從資料庫僅讀取），提升資料庫效能。
2、通過 MySQL Replication 實現資料的實時備份，保證資料安全。
3、通過 MySQL Replication 實現資料的離線分析（主資料庫生成資料，從資料庫分析計算不影響主資料庫效能）。
4、資料分發。
MySQL Replication完整的官方文件請參閱：https://dev.mysql.com/doc/refman/5.7/en/replication.html
劍 http://www.cnblogs.com/wu-jian
工作原理
1、Master中的所有資料庫變更事件寫入Binary Log檔案
2、當在Slave中執行“SLAVE START”命令時，開啟Slave I/O Thread，並連線Master
3、Master偵測到Slave I/O Thread的連線，開啟Log Jump Thread進行響應
4、Master Binary Log經Master Log Jump Thread和Slave I/O Thread傳輸至Slave Relay Log
5、Slave SQL Thread將Relay Log還原至資料，同步完成
注：可使用“SHOW PROCESSLIST”命令在Master和Slave中檢視對應執行緒的執行情況
吳劍 http://www.cnblogs.com/wu-jian
配置Master
開啟Binary Log並設定ServerID，ServerID必須唯一，取值範圍1至232-1
[mysqld]
# 開啟Binary Log log-bin=mysql-bin
# 設定全域性ID server-id=1
# 指定需同步的資料庫（因為資料庫名稱可能包含逗號，因此多個資料庫必須重複配置多次而不能以逗號分隔）
binlog-do-db=database_name
# 指定禁止同步的資料庫
binlog-ignore-db=database_name
# 指定Binary Log格式
binlog_format=MIXED
建立同步帳號
因為每個Slave均需要使用帳號密碼連線至主資料庫，所以在主資料庫上必須提供帳號。建議使用一個獨立帳號，僅授權資料同步許可權。
CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';
獲取Binary Log資訊
Slave啟動I/O Thread時需要傳入Binary Log部分資訊，因此需要獲取一下Binary Log資訊：
SHOW MASTER STATUS;
使用“SHOW MASTER STATUS”命令獲取Binary Log資訊，並記錄File和Position欄位值。
同步前保證Master與Slave的資料一致
在Slave啟動I/O Thread前，需確保Master與Slave的資料一致，因此先對Master進行鎖定（防止資料變更），手動同步並確保資料一致後再解鎖。
FLUSH TABLES WITH READ LOCK;
手動資料同步相關操作略...
UNLOCK TABLES;
配置Slave
設定ServerID，可不必開啟BinLog：
[mysqld]
# 設定全域性ID server-id=2
# 指定同步的資料庫
replicate-do-db=database_name
# 指定禁止同步的資料庫
replicate_ignore_db=database_name
設定Master資訊，執行如下命令：
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_PORT='master_host_port',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
啟動I/O Thread
START SLAVE;
檢視同步狀態：
SHOW SLAVE STATUS;
吳劍 http://www.cnblogs.com/wu-jian
Master的binlog_format 引數
binlog_format用於配置Binary Log的格式，支援如下三種型別：
Row
按資料行的變化進行記錄，該模式與SQL語句、儲存過程、函式、觸發器等無關，它只關心每一行的資料是否發生變化，如變化則記錄，因此Row模式是準確度最高的。但它的缺點是某些情況下會產生大量內容而導致效率下降，比如表結構發生變更時。
Statement
按SQL語句進行記錄，很明顯這個解決了Row模式的短板，但問題是準確度不夠高，因為SQL語句可以非常複雜並且容易出現意外情況。
Mixed
Row與Statement混合模式，由MySQL自動決定什麼時候使用Row，什麼時候使用Statement，這也是預設模式。
吳劍 http://www.cnblogs.com/wu-jian
replicate-do-db注意事項
當在Slave中使用replicate-do-db和replicate-ignore-db配置項時，需特別注意，跨資料庫的SQL語句將不會被同步，如：
replicate-do-db=a
use b; update a.some_table set some_field = 'some value';
解決方案是使用replicate_wild_do_table和replicate_wild_ignore_table，如：
replicate_wild_do_table=database_name.% replicate_wild_ignore_table=database_name.%
吳劍 http://www.cnblogs.com/wu-jian
<全文完>
吳劍 http://www.cnblogs.com/wu-jian
作者：吳劍
出處：http://www.cnblogs.com/wu-jian/
本文版權歸作者所有，歡迎轉載，但必需註明出處，並且在轉載頁面明顯位置給出原文連線，否則保留追究法律責任的權利。
