1、MySQL主從同步原理

image-20220714133617856.png

基本原理：

slave會從master讀取binlog來進行數據同步

具體步驟：

• step1：master將數據改變記錄到二進制日志（binary log）中。

• step2： 當slave上執行 start slave 命令之后，slave會創建一個 IO 線程用來連接master，請求master中的binlog。

• step3：當slave連接master時，master會創建一個 log dump 線程，用于發送 binlog 的內容。在讀取 binlog 的內容的操作中，會對主節點上的 binlog 加鎖，當讀取完成并發送給從服務器后解鎖。

• step4：IO 線程接收主節點 binlog dump 進程發來的更新之后，保存到 中繼日志（relay log） 中。

• step5：slave的SQL線程，讀取relay log日志，并解析成具體操作，從而實現主從操作一致，最終數據一致。

2、一主多從配置

服務器規劃：1主2從

image.png

• step1：準備三臺數據庫服務器：端口3306、3307、3308，這里使用的版本號為 8.0.29

  image.png

• step2：修改數據庫配置文件的server-id，值不允許重復

[mysqld]
# 服務器唯一id，默認值1
server-id=1
# 設置日志格式，默認值ROW
binlog_format=STATEMENT
# 二進制日志名，默認binlog
# log-bin=binlog
# 設置需要復制的數據庫，默認復制全部數據庫
#binlog-do-db=mytestdb
# 設置不需要復制的數據庫
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema

binlog格式說明：

• binlog_format=STATEMENT：日志記錄的是主機數據庫的寫指令，性能高，但是now()之類的函數以及獲取系統參數的操作會出現主從數據不同步的問題。

• binlog_format=ROW（默認）：日志記錄的是主機數據庫的寫后的數據，批量操作時性能較差，解決now()或者  user()或者  @@hostname 等操作在主從機器上不一致的問題。

• binlog_format=MIXED：是以上兩種level的混合使用，有函數用ROW，沒函數用STATEMENT，但是無法識別系統變量

binlog-ignore-db和binlog-do-db的優先級問題：

0.08703112216569037.png

• step3：在主庫中創建slave用戶，供從庫連接使用：

-- 創建slave用戶CREATE USER 'slave_test'@'%';-- 設置密碼ALTER USER 'slave_test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';-- 授予復制權限GRANT REPLICATION SLAVE ON *.* TO 'slave_test'@'%';-- 刷新權限FLUSH PRIVILEGES;

• step4：主機中查詢master狀態：

執行完此步驟后不要再操作主服務器MYSQL，防止主服務器狀態值變化

SHOW MASTER STATUS;

記下File和Position的值。執行完此步驟后不要再操作主服務器MYSQL，防止主服務器狀態值變化。

image-20220804191852164.png

• step5：在從機上配置主從關系：

在從機上執行以下SQL操作

CHANGE MASTER TO MASTER_HOST='192.168.101.117', MASTER_USER='slave_test',MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1357;

• step6：啟動主從同步：
  啟動從機的復制功能，執行SQL：

START SLAVE;-- 查看狀態（不需要分號）SHOW SLAVE STATUS\G

兩個關鍵進程：下面兩個參數都是Yes，則說明主從配置成功！

image.png

• step7：實現主從同步：

在主機中執行以下SQL，在從機中查看數據庫、表和數據是否已經被同步

CREATE DATABASE db_user;USE db_user;CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30), PRIMARY KEY (id));INSERT INTO t_user(uname) VALUES('zhang3');INSERT INTO t_user(uname) VALUES(@@hostname);

停止和重置

需要的時候，可以使用如下SQL語句

-- 在從機上執行。功能說明：停止I/O 線程和SQL線程的操作。stop slave; -- 在從機上執行。功能說明：用于刪除SLAVE數據庫的relaylog日志文件，并重新啟用新的relaylog文件。reset slave;-- 在主機上執行。功能說明：刪除所有的binglog日志文件，并將日志索引文件清空，重新開始所有新的日志文件。-- 用于第一次進行搭建主從庫時，進行主庫binlog初始化工作；reset master;

常見問題

問題1

啟動主從同步后，常見錯誤是Slave_IO_Running： No 或者 Connecting 的情況，此時查看下方的 Last_IO_ERROR錯誤日志，根據日志中顯示的錯誤信息在網上搜索解決方案即可

image-20220714235426120.png

問題2

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

解決方案：

-- 在從機停止slaveSLAVE STOP;-- 在主機查看mater狀態SHOW MASTER STATUS;-- 在主機刷新日志FLUSH LOGS;-- 再次在主機查看mater狀態（會發現File和Position發生了變化）SHOW MASTER STATUS;-- 修改從機連接主機的SQL，并重新連接即可

遺留問題

讀寫分離時，其中一個讀庫出現異常，會發生什么？

廣播表的其中一個數據庫出現異常了，會發生什么？

1.出現異常的數據庫重新運行后，是否會發起同步
2.查詢的第一個數據庫出現異常了，是否會查詢另外一個數據庫
作者：Lanjerry
鏈接：https://www.jianshu.com/p/6b0b1e89535c