MySQL---DBA---捌（主從復(fù)制基礎(chǔ)）

一. 主從復(fù)制簡介

  1. 主從復(fù)制是基于二進(jìn)制日志進(jìn)行復(fù)制
  2. 主庫的修改操作都會記錄二進(jìn)制日志
  3. 從庫會請求新的二進(jìn)制日志并回放，最終達(dá)到主從數(shù)據(jù)同步
  4. 主從復(fù)制核心功能：輔助備份，處理物理損壞，擴(kuò)展新型架構(gòu)，高可用，高性能的分布式架構(gòu)

二. 主從復(fù)制前提（搭建主從規(guī)劃）

  1. 兩臺以上mysql實例。server_id,server_uuid不同
  2. 主庫開啟二進(jìn)制日志
  3. 主庫要有專用的復(fù)制用戶（replication slave）
  4. 保證主從開始之前的某個時間點，從庫和主庫數(shù)據(jù)是一樣的（備份主庫資源到從庫）
  5. 告知從庫，主庫的復(fù)制用戶，password，IP，port，以及復(fù)制起點（change master to等信息）
  6. 保證線程開啟（三個）：Dump thread ， IO thread ， SQL thread（start slave;）

三. 主從復(fù)制搭建（Classic replication）

3.1 實現(xiàn)多實例環(huán)境

1.1 MySQL的多實例管理
1.1.1 準(zhǔn)備多個目錄
mkdir -p /data/330{7,8,9}/data
1.1.2 準(zhǔn)備配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
1.1.3 初始化三套數(shù)據(jù)
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
1.1.4 systemd管理多實例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim mysqld3307.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf
1.1.5 授權(quán)
chown -R mysql.mysql /data/*
1.1.6 啟動
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
1.1.7 驗證多實例
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"

3.2 主從環(huán)境搭建

1. 啟動2個多實例環(huán)境
systemctl start mysqld3307 （主）
systemctl start mysqld3308  （從）

2. 登錄mysql實例進(jìn)行檢查
mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock

3. 檢查主從server_id，以及主庫二進(jìn)制日志是否開啟
mysql -S /data/3307/mysql.sock -e "select @@server_id; select @@log_bin;"
mysql -S /data/3308/mysql.sock -e "select @@server_id;"

4. 主庫3307創(chuàng)建復(fù)制用戶
grant replication slave on *.* to repl@'10.0.0.5%' identified by '123';
select user,host  from mysql.user where user='repl';

5. 備份主庫資源告知從庫
主庫備份：mysqldump -uroot -p456  -A  -R -E --triggers --master-data=2 --single-transaction >/tmp/full.sql
再推送到從庫
從庫激活：1. mysql -S  /data/3308/mysql.sock  </tmp/full.sql
         2. mysql -S /data/3308/mysql.sock
            mysql> set sql_log_bin=0;
            mysql> source /tmp/full.sql


6. 告知從庫關(guān)鍵信息（change master to），從庫操作
起點: vim /tmp/full.sql 
顯示：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=445;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',   #主庫的ip
  MASTER_USER='repl',   #主庫的復(fù)制用戶
  MASTER_PASSWORD='123',   #復(fù)制用戶密碼
  MASTER_PORT=3307,   #主庫端口
  MASTER_LOG_FILE='mysql-bin.000001',   #起點二進(jìn)制文件
  MASTER_LOG_POS=445,   #起點position號
  MASTER_CONNECT_RETRY=10;

7. 開啟主從專用線程（從庫操作）
start slave;
監(jiān)測：mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep Running:
顯示：Slave_IO_Running: Yes
Slave_SQL_Running: Yes

四. 主從復(fù)制文件，線程，原理

4.1 主從復(fù)制涉及到的文件

1. 主庫：
---binlog二進(jìn)制文件（mysql-bin.000001）

2. 從庫：
---relay log中繼日志（db01-relay-bin.000001）,存儲請求的binlog
---master.info信息文件（change master to），存放的是主庫復(fù)制用戶信息
---relay-log.info信息文件（上次已經(jīng)回放relay的位置點）存放中繼日志信息

4.2 主從復(fù)制涉及到的線程

1. 主庫：
---Binlog_Dump_Thread：二進(jìn)制日志投遞線程（show processlist;）查看

2. 從庫：
---Slave_IO_Thread
---Slave_SQL_Thread

4.3 主從復(fù)制原理

image.png
1.change master to 時，ip pot user password binlog position寫入到master.info進(jìn)行記錄
2. start slave 時，從庫會啟動IO線程和SQL線程
3.IO_T，讀取master.info信息，獲取主庫信息連接主庫
4. 主庫會生成一個準(zhǔn)備binlog DUMP線程，來響應(yīng)從庫
5. IO_T根據(jù)master.info記錄的binlog文件名和position號，請求主庫DUMP最新日志
6. DUMP線程檢查主庫的binlog日志，如果有新的，TP(傳送)給從從庫的IO_T
7. IO_T將收到的日志存儲到了TCP/IP 緩存，立即返回ACK(網(wǎng)絡(luò)層面)給主庫 ，主庫工作完成
8.IO_T將緩存中的數(shù)據(jù)，存儲到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T讀取relay-log.info文件，獲取到上次執(zhí)行到的relay-log的位置，作為起點，回放relay-log
10.SQL_T回放完成之后，會更新relay-log.info文件。
11. relay-log會有自動清理的功能。
細(xì)節(jié)：
12.主庫一旦有新的日志生成，會發(fā)送“信號”給binlog dump ，IO線程再請求獲取新數(shù)據(jù)

五. 主從復(fù)制監(jiān)控

5.1 主從狀態(tài)監(jiān)控

1. 主庫： show processlist;

2. 從庫：show slave status \G;（顯示所有主從有關(guān)信息）

（1）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Running:"
線程狀態(tài)：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


（2）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Last"
線程報錯信息：
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 

（3）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Master"
顯示主庫master各種信息：
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 445

（4）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Relay_log"
顯示relay_log中繼日志信息：
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 445

（5）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Seconds_Behind_Master:"
顯示非人為操作的延時信息（需要避免）
Seconds_Behind_Master: 0

（6）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Delay: "
顯示延時從庫狀態(tài)信息（認(rèn)為設(shè)置）
SQL_Delay: 0
SQL_Remaining_Delay: NULL

（7）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Replicate "
顯示過濾復(fù)制相關(guān)信息
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 

（8）---mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Gtid "
顯示GTID復(fù)制相關(guān)狀態(tài)信息
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

5.2 日志量監(jiān)控

  1. 實際上就是如何判定主庫二進(jìn)制文件master_log與從庫接收的二進(jìn)制文件relay_log是不是同步執(zhí)行的
  2. 主庫： show master status;
    從庫： show slave status \G;
    判斷其中master_log與relay_log。
    其中master_log是IO線程向主庫請求的二進(jìn)制日志信息
    而relay_log是SQL線程進(jìn)行回放的二進(jìn)制日志。對比這兩個即可
  3. 直接在從庫（cat relay-log.info）文件，里邊存放著回放日志，其中的relay-bin對應(yīng)著mysql-bin。這兩個相當(dāng)于是一樣的。直接那mysql-bin的數(shù)據(jù)與上述（ show slave status \G;）命令結(jié)果的Master_Log數(shù)據(jù)進(jìn)行對比，數(shù)據(jù)相同則日志同步，不同則說明有差異。

六. 主從復(fù)制故障分析

6.1 IO線程故障（由功能參考）

1 .讀取master.info（change master to...）
2. 連接主庫：網(wǎng)絡(luò)，防火墻，主庫沒啟動，連接數(shù)上線等
總括：以上問題都會有Slave_IO_Running:Connection的線程錯誤，同時Last_IO_Error:xxx（會顯示一些錯誤）
解決1：連接密碼，用戶，地址，端口等報錯
從庫：
---mysql -urepl  -p123 -h 10.0.0.51  -P3307

stop slave;   #首先停止線程
reset slave all;   #重置（之前操作都排除，謹(jǐn)慎操作）
CHANGE MASTER TO....   #重新告知從庫主庫信息
start slave;   #再次開啟線程

解決2：連接數(shù)上限（Too Many  Connections）
set global max_connections = 300;（修改連接數(shù)上限）

3. 請求日志錯誤
故障原因：
---主庫確實日志，1236的報錯
---從庫方面二進(jìn)制日志位置點不對

注意：
在主從復(fù)制環(huán)境中，嚴(yán)令禁止主庫中reset master;  可以選擇expire進(jìn)行定期清理主庫二進(jìn)制日志

解決：
若主庫reset master重置了二進(jìn)制日志，他的文件就會從000001開始，name之前change master to的信息則會不存在，發(fā)生錯誤，需要重新告知
stop slave;   #首先停止線程
reset slave all;   #重置（之前操作都排除，謹(jǐn)慎操作）
CHANGE MASTER TO....   #重新告知從庫主庫信息
start slave;   #再次開啟線程

4. 接收日志
故障原因：
relay-log損壞，短節(jié)，找不到
解決方案：
重新change master to

5. 更新master.info
解決：重新搭建主從

6.2 SQL線程故障（由功能參考）

1. SQL線程功能：
---讀寫relay-log.info
---relay-log損壞，短節(jié)，找不到
---接收到的SQL無法執(zhí)行

2. 導(dǎo)致SQL線程故障原因分析
---版本差異，參數(shù)設(shè)定不同，數(shù)據(jù)類型差異，SQL_MODE影響
---要創(chuàng)建的數(shù)據(jù)庫對象已經(jīng)存在
---要刪除或者修改的對象不存在
---DML語句不符合表定義以及約束
歸根揭底是因為從庫發(fā)生了寫入操作：
Last_SQL_Error:Error 'Can't create database  'db';database  exits'  on query
Default database: 'db'  Query: 'create database db'

3. 解決方法：
方法一：刷寫同步
若從庫和主庫的二進(jìn)制文件不一致，可以進(jìn)行一下命令
stop slave;
set global sql_skip_counter = 1;   #可以一直往下刷，直到和主庫一致
start slave;

方法二：修改配置
從庫（/etc/my.cnf）
slave-skip-errors = 1032,1062,1007
---1032: 無法執(zhí)行DML
---1062：主鍵沖突或約束沖突
---1007：對象已經(jīng)存在
但是以上操作會有風(fēng)險，最安全的做法就是重新搭建主從，把握一個原則，一切以主庫為主

方法三：（一勞永逸）
1. 設(shè)置從庫只讀：
show variables like '%read_only%';
flush table with read lock;
set global read_only=on;
set global super_read_only=on;
注意：只會影響普通用戶，對管理員用戶無效

2. 加中間件：
讀寫分離

七. 主從延時

1. 主從延時簡介
---主庫做什么，從庫就跟著做什么，但從庫是在主庫執(zhí)行后的一段時間之后才開始執(zhí)行的（非人為操作）
是我們應(yīng)該避免的現(xiàn)象。

2. 主從復(fù)制現(xiàn)象
（1）最直觀：主庫做變更操作，從庫看數(shù)據(jù)狀態(tài)
（2）監(jiān)控命令：只能證明是否有延時
命令：mysql -S /data/3308/mysql.sock  -e "show slave status \G"|grep "Seconds_Behind_Master:"
顯示：Seconds_Behind_Master: 0
（3）計算二進(jìn)制日志差異變化。

3. 主從延時原因
（1）外在因素
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容