《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。默認自動開啟。

總結

數據安全非常重要，所以資料庫保證數據的安全是必須要實現的，這也是為什麼會出現主從複製的原因，備份恢復操作比較麻煩，而且物理損壞修復也比較麻煩。主從演變到現在已經比較靠譜和完善了。

Tags: 《MySQL資料庫》 MySQL