MySQL是現在普遍使用的資料庫，但是如果宕機了必然會造成數據丟失。為了保證MySQL資料庫的可靠性，就要會一些提高可靠性的技術。MySQL主從複製可以做到實時熱備數據。本文介紹MySQL主從複製原理及其配置過程。

術語：主從複製——master slave replication（M-S模式）。

用途

• 備份
• 讀寫分離
• 高可用和故障切換（需要做HA）
• MySQL升級測試

原理

實際上，它的原理思路很簡單。MySQL 中有一種日誌叫做 bin 日誌（二進位日誌），這個日誌會記錄下所有修改了資料庫的SQL語句。主從複製的原理其實就是"從"伺服器向"主"伺服器請求這個日誌文件，"主"伺服器會把這個 bin 日誌複製到"從"伺服器上執行一遍，這樣"從"伺服器上的數據就和"主"伺服器上的數據相同了。

流程圖：

圖解：

1. 主伺服器必須啟用二進位日誌，記錄任何修改了資料庫數據的事件；
2. 從伺服器開啟一個線程（I/O Thread)把自己扮演成 MySQL 的客戶端，通過 MySQL 協議，請求主伺服器的二進位日誌文件中的事件；
3. 主伺服器啟動一個線程（Dump Thread），檢查自己二進位日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主伺服器就會從第一個日誌文件中的第一個事件一個一個發送給從伺服器；
4. 從伺服器接收到主伺服器發送過來的數據把它放置到中繼日誌（relay log）文件中。並記錄該次請求到主伺服器的具體哪一個二進位日誌文件內部的哪一個位置（主伺服器中的二進位文件會有多個，其名結尾以6位數遞增）；
5. 從伺服器啟動另外一個線程（SQL Thread ），把 relay log 中的事件讀取出來，併在本地再執行一次。

配置過程

主伺服器（Master）

• 啟用二進位日誌 log-bin；
• 設置一個全局唯一的 server_id；
• 提前準備好一個有複製許可權（replication slave，replication client）的用戶。

從伺服器（Slave）

• 啟動中繼日誌 relay-log；
• 設置一個全局唯一的 server_id；
• 使用主伺服器提供的有複製許可權的用戶連接至 Master；
• 啟動複製線程。

配置演示

測試環境

主伺服器：192.168.43.75
從伺服器：192.168.43.111
mysql版本：mysql-8.0
系統：主 Windows10，從 Windows7

主伺服器配置

1.配置 my.ini 

log-bin=master-log-bin
server-id=1
innodb-file-per-table=ON

2.重啟服務，併進入MySQL

查看主伺服器狀態信息：

註：這裡的文件名和位置值會在後面的配置中用到。

 3.給複製用戶授權

grant replication slave, replication client on *.* to [email protected];

一定要刷新許可權：

flush privileges;

主伺服器配置完成。

從伺服器配置

1.配置 my.ini

log-bin=slave-log-bin
relay-log=relay-log
relay-log-index=relay-log.index
server-id=2
innodb_file_per_table=ON

2.重啟併進入MySQL服務（略）

3.用主伺服器提供的複製用戶連接主伺服器

change master to master_host='192.168.43.75',master_port=3306,master_user='repl_user',master_password='123456',master_log_file='master-log-bin.000005',master_log_pos=155;　　# 這裡的 master 日誌文件和位置必須與主伺服器當前狀態一致！

4.啟動複製線程

此時會啟動 IO Thread 和 SQL Thread 這兩個線程。 

（停止複製線程：stop slave）

查看從伺服器狀態信息：

MySQL主從配置到此完成。

測試

在主伺服器上新建一個資料庫test：

在從伺服器上查看：

測試通過，完美。

常見問題處理

當我們配置好MySQL主從同步時，原本是可以實現主從同步的，但是重啟機器後就發現無法同步了。

MySQL Replication 中 slave 機器上有兩個關鍵的線程，死一個都不行，一個是 Slave_IO_Running，一個是 Slave_SQL_Running。一個負責與主機的 IO 通信，一個負責自己的 slave mysql 語句執行。

1）如果是Slave_SQL_Running：No，如圖。

解決辦法：

> stop slave;
> set global sql_slave_skip_counter=1;
> start slave;

2）如果是Slave_IO_Running：No，如圖。

解決辦法：
查看主伺服器狀態：

查看從伺服器狀態：

找到問題所在：Master_Log_File沒有對應。

> stop slave;
> change master to master_log_file='mysql-bin.000026',master_log_pos=0;
> slave start;
> show slave status\G;

得解。

　　至此，轉載請註明出處。