《MySQL資料庫》MySQL主從複製搭建與原理
- 2020 年 8 月 30 日
- 筆記
- 《MySQL資料庫》, MySQL
前言
主從複製:兩台或者更多的資料庫實例,通過二進位日誌,實現數據同步。為什麼需要主從複製,主從複製的作用是什麼,答:為了預防災難。
搭建
第一步:準備多實例環境。如何創建多實例見:
第二步:確保每一個實例的server_id 不同。
檢查各自實例圖中(my.cnf)的配置是否不同。
第三步:主庫檢查binlog 是否開啟
第四步:主庫創建複製用戶
grant replication slave on *.* to repl@'%' identified by 'repl';
第五步:主庫和從庫在主從之前要保證數據結構一致。
主庫備份,恢復到從庫://www.cnblogs.com/jssj/p/13514597.html
第六步:主從連接(重點)
從庫中執行以下語句,告知從庫連接資訊,同步開始點等(我這裡有兩個從庫就兩個從庫都登入執行)
CHANGE MASTER TO MASTER_HOST='127.0.0.1', -- 主庫ip MASTER_USER='repl', -- 主從專用用戶 MASTER_PASSWORD='repl', -- 用戶密碼 MASTER_PORT=3307, -- 主庫ip MASTER_LOG_FILE='mysql-bin.000001', --複製開始點的binlog文件 MASTER_LOG_POS=653, --binlog 文件中的開始點 MASTER_CONNECT_RETRY=10; -- 重連次數 MASTER_DELAY = 300; -- 從庫SQL執行緒延時時間設置,(一般一主二從的時候,一從不加延時,一從加延時,這樣邏輯誤操作可以及時修復)
第七步:從庫中啟動專用主從執行緒
start slave ; -- 啟動主從
stop slave; -- 關閉主從
擴展:
start slave sql_thread -- 單獨啟動從庫sql執行緒 start slave io_thread -- 單獨啟動從庫I/O執行緒 stop slave sql_thread -- 單獨停從庫sql執行緒 stop slave io_thread -- 單獨停從庫I/O執行緒
第八步:驗證主從複製是否正常
查看從庫執行緒
mysql -uroot -S /usr/local/mysql/data/3308/mysql.sock -e "show slave status\G | grep Running"
還有就是在主庫上創建資料庫,創建表等操作,看看從庫是否和主庫一致。
如果搭建失敗:
執行:(沒有問題別執行),然後重新執行以上步驟。 reset slave all 表示重置主從配置資訊。
mysql -uroot -S /usr/local/mysql/data/3308/mysql.sock -e "stop slave; reset slave all;"
好了,到這裡我們已經搭建完畢。
原理
文件部分
從庫文件(以下文件都默認放在數據文件目錄下):
主機名-relay-bin.000001 — 默認叫這個名字,從主庫接收到的binlog資訊記錄在這裡
主機名-relay-bin.000002 — 默認叫這個名字,從主庫接收到的binlog資訊記錄在這裡
主機名-relay-bin.index — 默認叫這個名字,從主庫接收到的binlog資訊記錄在這裡
master.info –從庫的配置資訊: CHANGE MASTER TO … 的資訊。
relay-log.info –存儲接收的binlog
mysql> select @@master_info_repository; -- 設置從庫配置資訊存放的方式:文件/表
mysql> select @@relay_log_info_repository; -- relay-log存放方式:文件/表,默認文件
mysql> select @@relay_log_purge; -- relay_log是否開啟刪除已經被使用過的relay_log
主庫文件就是binlog 已經再之前文檔講過,這裡不在說明//www.cnblogs.com/jssj/p/13472394.html
執行緒:
主庫下執行以下命令,查詢執行緒:
show processlist;
圖中可以看到兩個 Binlog Dump的主從執行緒。
從庫執行緒:搭建的第八步已經給出。兩個執行緒一個I/O,一個SQL。
主從複製的原理圖:
額外補充: 主庫有變化會通過從庫,從庫就會及時去主庫獲取新的變化,MySQL的主從複製是通過這種方式來保證實時性的。
參數
mysql> show slave hosts; -- 查看主庫中被連接的從庫資訊
mysql> show slave status \G; --查看從庫資訊
重點欄位說明
change master to 的配置資訊
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File的執行情況:
Relay_Log_File: iZm5e5v2zi93osbr5z21fvZ-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 154
從庫執行緒狀態:
Slave_IO_Running: Yes — no 或者 connecting 都表示不正常。 網路,埠,防火牆,用戶密碼 ,許可權replication slave,連接數上限,版本不一致等。
Slave_SQL_Running: Yes
mysql> select @@max_connections; -- 最大連接數
從庫執行緒報錯資訊:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
過濾複製相關資訊:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
主從延時
Seconds_Behind_Master: 0 — 單位為秒(主二進位文件的事件和從庫獲取二進位文件的事件差,所以這個時間並不能說明主從沒有延時)
延時從庫的配置資訊:
SQL_Delay: 0 — 通過主從配置的時候設置的 MASTER_DELAY;
SQL_Remaining_Delay: NULL
GTID相關複製資訊:從庫顯示的就是主庫的GTID 和從庫執行到的GTID。
Retrieved_Gtid_Set:
Executed_Gtid_Set:
故障
主從不一致的時候,會出現從庫SQL執行緒down掉。
先看參數: 報錯內容
Last_SQL_Errno: 0
Last_SQL_Error:
處理方式一:
stop slave; -- 停主從 set global sql_slave_skip_counter = 1; -- 設置參數跳過本次主從同步操作。
start slave;
處理方式二:
從庫反操作一下:比如從庫中已經存在主鍵記錄,先把主鍵記錄刪除,重啟start slave; 讓其再同步一次。
處理方式三(統一處理一下重複報錯,不推薦):
在MySQL參數配置文件:/etc/my.cnf 中設置
slave-skip-errors = 1032,1062,1007 -- 常見錯誤程式碼:1007:對象已存在;1032:無法執行DML;1062:主鍵衝突,或約束衝突
終極方式:重新搭建主從。
一般情況:MySQL主從搭建從庫是不需要有其他的操作的,也是為了減少不必要的主從問題,所以會設置一個參數:
mysql> select @@read_only; -- 1:只讀,0:不是 (針對普通用戶)
mysql> select @@super_read_only; -- 1:只讀,0:不是 (針對普通管理員用戶)
主從延時
主從延時監控:
主庫binlog執行到的位置點:
從庫relaylog執行到的位置點,
是否存在差異,差異越大延時越嚴重。
主從延時原因:
1. 網路太慢。
2. 硬體性能。
3. 主庫業務繁忙。
4. 從庫太多。
5. 5.6版本沒有開啟GTID(串列傳輸日誌)。 — 開啟GTID,或者升級5.7 ,5.7默認支援並發傳輸日誌。
6. 鎖也會導致延時。
7.從庫SQL執行緒串列執行,效率低,導致延時, 需要開啟多個SQL執行緒來保證效率(必須開啟GTID).並且5.7 版本還有邏輯時鐘保證並發執行。 MTS
恢復
1. 如果是物理損壞, 主從非常簡單的就可以恢複數據。 直接將從庫數據導出,導入主庫, 或者直接將從庫當成主庫使用。
stop slave; -- 停掉主從服務 reset slave all; -- 去掉主從配置
然後就可以臨時當主庫使用。
2. 如果數邏輯損壞,比如drop了資料庫。
上面的情況下,我們就需要使用延時從庫的功能了, 因為該從庫是延時執行操作的, 故主庫出現問題的時候從庫是正常的。 所以可以通過從庫恢複數據:
CHANGE MASTER TO MASTER_DELAY = 300; -- 設置延時
1. 登錄從庫資料庫停從庫sql執行緒:
stop slave sql_thread
2. 查看relay.info 的位置點是否主庫一致,表示日誌文件已經同步:
3. 恢復從庫:
截取relay_bin log位置點的起點
show slave status \G;
截取relay_bin log位置點的終點
找到使用的relay-bin 文件,然後使用下面的命令找到終點
show relaylog events in 'iZm5e5v2zi93osbr5z21fvZ-relay-bin.000002' -- 查看relaybinlog 文件
獲取pos欄位就可以了,
起點,終點都有了,然後截取命令:
mysqlbinlog --start-position=634 --stop-position=861 /usr/local/mysql/data/3309/data/iZm5e5v2zi93osbr5z21fvZ-relay-bin.000002 > /usr/local/mysql/relay.sql
恢復從庫數據:
mysql> set sql_log_bin = 0; -- 關閉binlog日誌 mysql> source /usr/local/mysql/relay.sql; -- 導入sql腳本(通過binlog截取出來的) mysql> set sql_log_bin = 1; -- 開啟binlog日誌
檢查數據:
4. 恢復主庫:
參考://www.cnblogs.com/jssj/p/13514597.html 的恢復章節。 導出從庫資料庫文件,導入主庫。
5. 恢復主從:
從新設置主從:參考本文第一部分。
過濾主從
圖中 資料庫C 不需要主從複製。
1. 主庫設置不同步,不產生binlog 即可(不推薦)
其中:binlog_Do_DB 是包含哪些庫需要生成binlog日誌; binlog_ignore_DB 忽略掉一些資料庫產品binlog日誌
2. 從庫設置SQL執行緒不執行不需要複製的資料庫(推薦)。
[root@db01 ~]# vim my.cnf -- 打開參數文件設置 replicate_do_db=test -- 需要寫入從庫的資料庫 replicate_do_db=test1 -- 需要寫入從庫的資料庫
重啟資料庫實例生效。
擴展:
replicate_do_db=test -- 需要寫入從庫的資料庫 replicate_ignore_db=test1 -- 需要忽略寫入從庫的資料庫 replicate_do_table=test.test -- 需要寫入從庫的資料庫的表 replicate_ignore_db=test1.test -- 需要忽略寫入從庫的資料庫的表 replicate_wild_do_db=test.t* -- 需要寫入從庫的資料庫的模糊的表 replicate_wild_ignore_db=test1.t* -- 需要忽略寫入從庫的資料庫模糊的表
半同步主從
在上面的主從複製的框架中有一個問題,就是主庫不關心從庫是否接收到數據,寫入磁碟,容易出現從庫和主庫不一致的情況。
已經屬於歷史功能,基本已經不使用了。
原理:
1. 主庫執行新的事務,commit時,更新 show master status\G ,觸發一個訊號給
2. binlog dump 接收到主庫的 show master status\G資訊,通知從庫日誌更新了
3. 從庫IO執行緒請求新的二進位日誌事件
4. 主庫會通過dump執行緒傳送新的日誌事件,給從庫IO執行緒
5. 從庫IO執行緒接收到binlog日誌,當日誌寫入到磁碟上的relaylog文件時,給主庫ACK_receiver執行緒
6. ACK_receiver執行緒觸發一個事件,告訴主庫commit可以成功了
7. 如果ACK達到了我們預設值的超時時間,半同步複製會切換為原始的非同步複製.
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; -- 主庫載入插件 INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; --從庫載入插件
show plugins; -- 查看載入情況
SET GLOBAL rpl_semi_sync_master_enabled = 1; -- 主庫設置半同步 SET GLOBAL rpl_semi_sync_slave_enabled = 1; -- 從庫設置半同步
STOP SLAVE IO_THREAD; -- 停止I/O執行緒 START SLAVE IO_THREAD; -- 啟動I/O執行緒
show status like 'Rpl_semi_sync_master_status'; -- 主庫查看半同步狀態 show status like 'Rpl_semi_sync_slave_status'; -- 從庫查看半同步狀態
GTID 主從複製(推薦)
搭建GTID主從要注意一點:MySQL的data文件需要清理之後,從新搭建。
my.cnf的配置文件如下:
cat > /usr/local/mysql/data/3307/my.cnf <<EOF [mysqld] basedir=/usr/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64 datadir=/usr/local/mysql/data/3307data socket=/usr/local/mysql/data/3307/mysql.sock log_error=/usr/local/mysql/data/3307/mysql.log port=3307 server_id=7 log_bin=/usr/local/mysql/log/3307/mysql-bin secure-file-priv=/tmp binlog_format=row autocommit=0 gtid-mode=on #開啟GTID enforce-gtid-consistency=true #開啟GTID log-slave-updates=1 #主從一至 [mysql] prompt=db01 [\\d]> EOF
重新初始化數據:
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64 --datadir=/usr/local/mysql/data/3307/data
啟動實例:
mysqld --defaults-file=/usr/local/mysql/data/3309/my.cnf &
重新構建主從:主庫
grant replication slave on *.* to repl@'%' identified by 'repl';
從庫執行命令(和普通主從有區別):
-- 主從配置資訊 change master to master_host='127.0.0.1', MASTER_PORT=3307, master_user='repl', master_password='repl' , MASTER_AUTO_POSITION=1; -- 主動獲取主庫的的位置點,根據從庫的relay-bin.info去判斷 -- 開啟主從 start slave;
原理:
GTID 備份數據,然後複製到從庫,從庫啟動後會自動判斷到那些GTID被執行過,可以自動獲取下一個GTID. 需要結合–set-gtid-purged。 默認自動開啟。
總結
數據安全非常重要,所以資料庫保證數據的安全是必須要實現的,這也是為什麼會出現主從複製的原因,備份恢復操作比較麻煩,而且物理損壞修復也比較麻煩。主從演變到現在已經比較靠譜和完善了。