MySQL主從複製詳細介紹
- 2019 年 11 月 6 日
- 筆記
MySQL主從複製詳細介紹
一、主從複製的目的
MySQL內建的複製功能是構建基於MySQL的大規模、高性能應用的基礎，複製功能的目的是構建高性能的應用，同時也是高可用性、可擴展性、災難恢復、備份以及數據倉庫等工作的基礎。比較常見的用途有以下幾種：
數據分布：備份特定資料庫
負載均衡：讀寫分離
高可用性和故障切換：從庫的存在可以縮短宕機時間
MySQL升級測試：使用一個更高版本的MySQL作為備庫，保證在升級全部實例前，查詢能夠在備庫按照預期進行
二、主從複製的原理和步驟
簡單的說就是master將資料庫的改變寫入binary log二進位日誌，這個日誌會記錄下所有修改了資料庫的SQL語句（insert,update,delete,grant等）,slave同步這些二進位日誌，並根據這些二進位日誌進行數據操作,其實就是把主伺服器上的binary log複製到從伺服器上執行一遍，這樣從伺服器上的數據就和主伺服器上的數據相同了。
整體來說，主從複製有以下步驟：
1.主節點必須啟用二進位日誌，記錄任何修改資料庫數據的事件。
2.從節點開啟一個執行緒I/O Thread把自己扮演成mysql的客戶端，通過mysql協議，請求主節點的二進位日誌文件中的事件
3.主節點啟動一個執行緒(dump Thread)，檢查自己二進位日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主節點就會從第一個日誌文件中的第一個事件一個一個發送給從節點。
4.從節點接收到主節點發送過來的數據把它放置到中繼日誌（Relay log）文件中。並記錄該次請求到主節點的具哪個二進位日誌文件的哪個位置。
5.從節點啟動另外一個執行緒(sql Thread )，把replaylog中的事件讀取出來，並在本地再執行一次。
其原理圖如下：
三、複製中執行緒的作用
從節點：
I/O Thread:從Master請求二進位日誌事件，並保存於中繼日誌中。
Sql Thread:從中繼日誌中讀取日誌事件，在本地完成更新。
主節點：
Dump Thread:為每個Slave的I/O Thread啟動一個dump執行緒，用於向從節點發送二進位事件。 如果從節點需要作為其他節點的主節點時，是需要開啟二進位日誌文件的。這種情況叫做級聯複製。如果只是作為從節點，則不需要創建二進位文件。
四、主從複製配置過程
主節點：
1.啟用二進位日誌。
2.為當前節點設置一個全局唯一的server_id。
3.創建有複製許可權的用戶帳號 replication slave。
從節點：
1.啟動中繼日誌。
2.為當前節點設置一個全局唯一的server_id。
3.使用有複製許可權的用戶帳號連接至主節點，並啟動複製執行緒。
4.1 測試環境
4.2 主節點配置過程
4.2.1 編輯主節點配置文件
在Centos中打開my.cnf文檔：
vi /etc/my.cnf |
|---|
· 添加：log-bin = mysql-bin（開啟二進位日誌）
· 添加：server-id =4（設置伺服器id,主節點和從節點的id需要設為不同）
· 添加：binlog-do-db=DBAs（確定需要同步的資料庫）
· 添加：binlog-ignore-db=mysql(此處可以實際需求添加需要忽略的資料庫)
· 添加：expire_logs_days=7(自動清理 7 天前的log文件,可根據需要修改)
4.2.2 啟動主節點mysql服務，並連接mysql
正常情況下，mysql服務啟動命令為：
cd /usr/local/mysql/./bin/mysqld./bin/mysql -uroot -p****** |
|---|
為了更方便的啟動mysql服務，為mysql創建軟連接
ln -s /usr/local/mysql/bin/mysql /usr/binln -s /usr/local/mysql/bin/mysqld /usr/bin |
|---|
此時，啟動命令變為：
mysqldmysql -uroot -p****** |
|---|
4.2.3 測試日誌資訊
(1)查看二進位日誌是否開啟
命令：
show variables like '%log_bin%'; |
|---|
(2)查看主節點二進位日誌列表
命令：
show master logs |
|---|
(3)查看主節點的server_id
命令：
show global variables like '%server%'； |
|---|
4.2.4 在主節點上創建有複製許可權的用戶
命令：
grant replication slave on *.* to 'repluser'@'%' identified by '123456' |
|---|
其中，repluser代表用戶名稱，%指的是所有的IP都可以訪問，123456是用戶密碼，
4.2.5 查看主伺服器上當前的二進位日誌名和偏移量值
命令：
show master statusG |
|---|
從節點同步時，File 和Position 要和主節點上的一致
4.3 從節點配置過程
4.3.1 編輯從節點配置文件
在Centos中打開my.cnf文檔。
命令：
vi /etc/my.cnf |
|---|
· 添加：log-bin = mysql-bin(開啟從節點的二進位日誌)
· 添加：server_id=5（設置從節點的id，需要和主節點區分開）
· 添加：read_only=1（設定從節點為只讀模式）
· 添加：relay_log=mysql-relay-bin（開啟中繼日誌）
4.3.2 開啟從節點mysql服務，連接mysql
啟動命令為：
mysqldmysql -uroot -p****** |
|---|
4.3.3 查看從節點日誌資訊
(1)查看中繼日誌是否開啟
(2)查看伺服器相關資訊
4.3.4 在從節點配置訪問主節點的參數資訊 添加 主節點主機，訪問主節點的用戶名及密碼，主節點二進位文件資訊。
命令：
change master to master_host='192.168.190.132',master_user='repluser',master_password='123456',master_log_file='mysql-bin.000023',master_log_pos=154; |
|---|
此處的master_log_file和master_log_pos需要和主節點狀態保持一致。
4.3.5 查看從節點的狀態資訊 因為沒有啟動 從節點的複製執行緒，I/O執行緒和SQL 執行緒都為NO.
使用start slave命令啟動從節點的複製執行緒，再利用show slave status命令查看當前的從節點狀態。
4.4 功能測試
1）在主節點創建資料庫，並查看主節點二進位日誌資訊
create database DBAs;use DBAscreate table DBA_inf (name varchar(20),number int);insert into DBA_inf (name ,number) values ('baojun',001),('jianrong',002), ('shuyang',003),('yuanzheng',004), ('yvbin',005),('chicheng',006), ('yicong',007),('yeyi',008), ('yingliang',009),('yazhou',010); |
|---|
查看主節點的狀態，
2）在從節點查找二進位日誌資訊，並查看mydb資料庫是否複製成功
最後在從節點上查看數據是否已經同步，命令：
select * from DBA_inf; |
|---|
經過驗證，證明主從複製同步成功！！！
五、錯誤排查總結
5.1 Connecting錯誤
操作過程中有時候出現如下所述錯誤：
排錯思路如下：
1.二進位日誌沒有開啟
2.IPTABLES 沒有放開埠
3.對應的主機 IP地址寫錯了
實際操作：關閉主節點防火牆，重新開啟從節點，即可連接上。
5.2 Slave_SQL_Running:NO
操作過程中還出現了如下所示的從節點的SQL執行緒運行錯誤
排錯思路如下：
1. 查看是否使用了nat的網路結構到導致網路有問題，連接不上。
2. 有可能my.cnf有問題，配置文件
3. 授權的問題，replication slave和file許可權是必須的。
實際情況，換工位後網路IP出現問題，連接不上，重新配置網路之後問題解決。
