原理圖解
image.png
1. 主從復(fù)制介紹(Master-Slave Replication)
兩臺(tái)以上的數(shù)據(jù)庫(kù)實(shí)例,通過(guò)二進(jìn)制日志實(shí)現(xiàn)數(shù)據(jù)復(fù)制關(guān)系.
2. 主從復(fù)制作用
輔助數(shù)據(jù)備份.比較擅長(zhǎng)處理數(shù)據(jù)庫(kù)的物理?yè)p壞.
架構(gòu)演變: 高可用,讀寫(xiě)分離,分布式...
3. 主從復(fù)制前提 (Classic Replication搭建過(guò)程)
(1) 兩臺(tái)以上數(shù)據(jù)庫(kù)實(shí)例,需要不同的server_id ,server_uuid保持不同
(2) 主庫(kù)需要開(kāi)啟二進(jìn)制日志(binlog),專用復(fù)制用戶
(3) 進(jìn)行主庫(kù)數(shù)據(jù),恢復(fù)到從庫(kù).
(4) 從庫(kù):change master to , 通知從庫(kù),主庫(kù):user,password (專用的),ip,port,復(fù)制的起點(diǎn).
(5) 從庫(kù):start slave; 開(kāi)啟專用的復(fù)制線程
4. 主從復(fù)制搭建
4.1 節(jié)點(diǎn)準(zhǔn)備及各項(xiàng)檢查
server_id/server_uuid不能相同
systemctl start mysqld3307
systemctl start mysqld3308
mysql -S /tmp/mysql3307.sock -e "select @@server_id"
mysql -S /tmp/mysql3308.sock -e "select @@server_id"
mysql -S /tmp/mysql3307.sock -e "select @@log_bin"
mysql -S /tmp/mysql3307.sock -e "select @@server_uuid"
mysql -S /tmp/mysql3308.sock -e "select @@server_uuid"
4.2 binlog日志檢查及用戶準(zhǔn)備
mkdir -p /data/mysql/binlog_3307
chown -R mysql.mysql /data
vim /data/mysql/my3307.cnf
添加以下行:
log_bin=/data/mysql/binlog_3307/mysql-bin
systemctl restart mysqld3307
創(chuàng)建用戶
grant replication slave on *.* to repl@'10.0.0.%' identified by '123'
4.3 備份主庫(kù)數(shù)據(jù),恢復(fù)到從庫(kù),并記錄位置點(diǎn).
備份數(shù)據(jù)
mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
記錄位置點(diǎn)
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;
mysql -S /tmp/mysql3308.sock < /tmp/full.sql
4.4 從庫(kù)執(zhí)行 change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
4.5 開(kāi)啟復(fù)制線程(IO,SQL)
start slave;
mysql -S /tmp/mysql3308.sock -e "show slave status\G"|grep Running:
4.6 簡(jiǎn)單診斷問(wèn)題思路:
1.線程
IO
SQL2.看具體報(bào)錯(cuò)
last_xx_error3.處理
stop slave
change master to
start slave
4.7 主從復(fù)制原理
文件:
主庫(kù): binlog
從庫(kù): relaylog,master.info,relay-log.info
線程:
主庫(kù): binlog dump thread(show processlist;)
從庫(kù): IO , SQL
4.8 主從復(fù)制的監(jiān)控方式
show slave status \G
- 1.主庫(kù)連接信息
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
- 2.從庫(kù)中繼日志回放位置點(diǎn)
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Exec_Master_Log_Pos: 444
Seconds_Behind_Master: 0
說(shuō)明: 主要是為了定位主從延時(shí)問(wèn)題.
- 3.從庫(kù)復(fù)制線程狀態(tài)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
說(shuō)明: 必須是yes , 但是yes不代表完全沒(méi)問(wèn)題.
(4) 過(guò)濾復(fù)制相關(guān)狀態(tài)
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
(5) 延時(shí)從庫(kù)
SQL_Delay: 0
SQL_Remaining_Delay: NULL
說(shuō)明: 作用是用來(lái)處理邏輯故障.
(6) GTID復(fù)制有關(guān):
Retrieved_Gtid_Set:
Executed_Gtid_Set:
4.9 主從復(fù)制故障分析及處理
1.線程為什么不工作
IO :
1.連接主庫(kù)
網(wǎng)絡(luò): ip , port , 防火墻 ,user,password , 網(wǎng)絡(luò)不通.
解決方案: 保證以上信息正確可用.
stop slave
change master to
start slave- 請(qǐng)求日志
日志位置點(diǎn)指定錯(cuò)誤(搭建過(guò)程問(wèn)題)
binlog損壞,誤刪等.
例如: reset master;
保險(xiǎn)的處理方法:重新備份恢復(fù),重新構(gòu)建主從
- 請(qǐng)求日志
- 存儲(chǔ)日志
relaylog 損壞,丟失,不連續(xù).
- 存儲(chǔ)日志
SQL:
- 回放relaylog ----> SQL ----> SQL線程執(zhí)行SQL語(yǔ)句為什么失敗?
創(chuàng)建對(duì)象失敗,刪除修改的對(duì)象不存在
從庫(kù)被寫(xiě)入了
- 回放relaylog ----> SQL ----> SQL線程執(zhí)行SQL語(yǔ)句為什么失敗?
解決方案:
- 方法一：
stop slave;
set global sql_slave_skip_counter = 1;
將同步指針向下移動(dòng)一個(gè)拴清，如果多次不同步会通，可以重復(fù)操作。
start slave; - 方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常見(jiàn)錯(cuò)誤代碼:
1007:對(duì)象已存在
1032:無(wú)法執(zhí)行DML
1062:主鍵沖突,或約束沖突 - 終極大招:
從庫(kù)只讀
read_only=on
super_read_only=on
或者,配合中間件.
2.版本不一致,SQL_mode不一致,配置不一致.
解決方案: 盡量統(tǒng)一. 將高版本的配置降低為低版本配置.
3.DML語(yǔ)句執(zhí)行失敗
異步復(fù)制會(huì)導(dǎo)致的問(wèn)題,比如:表不存在.
人工校驗(yàn)主從的數(shù)據(jù)不一致原因,把有問(wèn)題的操作給補(bǔ)上.
4.約束沖突,例如: 主鍵自增列沖突
雙主模式下,會(huì)導(dǎo)致主鍵沖突.
主庫(kù)宕機(jī),8.0以前自增列是沒(méi)有持久化的.
5.主從復(fù)制進(jìn)階
1. 延時(shí)從庫(kù)企業(yè)級(jí)應(yīng)用
1.1 場(chǎng)景介紹
邏輯損壞:
總數(shù)據(jù)量200G,誤刪除1個(gè)2G的庫(kù),有備份和日志.
1.2 配置
SQL線程延時(shí):
數(shù)據(jù)已經(jīng)寫(xiě)入relaylog中了,SQL線程"慢點(diǎn)"運(yùn)行
一般企業(yè)建議3-6小時(shí),具體看公司運(yùn)維人員對(duì)于故障的反應(yīng)時(shí)間
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
1.3 故障模擬
- 主庫(kù):
create database delaydb charset utf8mb4;
use delaydb;
create table t1 (id int);
begin;
insert into t1 values(1);
commit;
begin;
insert into t1 values(2);
commit;
begin;
insert into t1 values(3);
commit;
begin;
insert into t1 values(4);
commit;
begin;
insert into t1 values(5);
commit;
drop database delaydb;
- 恢復(fù)思路(延時(shí)從庫(kù)):
- 監(jiān)控到故障,第一時(shí)間停掉從庫(kù)SQL線程
- 手工模擬SQL線程回放relaylog,停止在drop之前
起點(diǎn):讀取relay.info信息
終點(diǎn):drop之前 - 恢復(fù)業(yè)務(wù)
演練:
從庫(kù) :
- 停止SQL線程
mysql> stop slave sql_thread;
- 截取relaylog日志
起點(diǎn):
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 653
終點(diǎn):
db01-relay-bin.000002 | 2366 | Query | 7 | 4772 | drop database delaydb
2. 過(guò)濾復(fù)制的企業(yè)級(jí)應(yīng)用
2.1 配置普通復(fù)制環(huán)境
1.啟動(dòng)一臺(tái)數(shù)據(jù)庫(kù)
systemctl start mysqld3309
2.備份主庫(kù)數(shù)據(jù)
mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
3.查看信息
grep "\-\- CHANGE MASTER TO" /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=4772;
4.導(dǎo)入數(shù)據(jù)庫(kù)數(shù)據(jù)
mysql -S /tmp/mysql3309.sock < /tmp/full.sql
5.配置從庫(kù)
mysql -S /tmp/mysql3309.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_PORT=3307,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=4772,MASTER_CONNECT_RETRY=10;start slave;"
mysql -S /tmp/mysql3309.sock -e "show slave status\G"|grep Running:
2.2 開(kāi)啟過(guò)濾復(fù)制功能
庫(kù)級(jí)別:
Replicate_Do_DB: world
Replicate_Ignore_DB:
表級(jí)別:
Replicate_Do_Table: world.t1
Replicate_Ignore_Table:
模糊過(guò)濾:
Replicate_Wild_Do_Table: world.t*
Replicate_Wild_Ignore_Table:
2.3 庫(kù)級(jí)別白名單演示
[root@db01 ~]# vim /data/mysql/my3309.cnf
replicate_do_db=oldguo
replicate_do_db=oldboy
[root@db01 ~]# systemctl restart mysqld3309
3. 半同步復(fù)制介紹
ACK
timeout
4. 基于GTID的復(fù)制
GTID復(fù)制配置過(guò)程：
- 1.清理環(huán)境
pkill mysqld
\rm -rf /data/*
mkdir -p /data/mysql/data
mkdir -p /data/binlog
chown -R mysql.mysql /data/*
- 2.準(zhǔn)備配置文件
主庫(kù)db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql57/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
從庫(kù)
slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql57
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql57
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
初始化數(shù)據(jù)
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data
- 3.啟動(dòng)數(shù)據(jù)庫(kù)
/etc/init.d/mysqld start
- 構(gòu)建主從：
master:51
slave:52,53
- 構(gòu)建主從：
master
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
slave
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
