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

總結

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