MySQL-lesson08-主從復(fù)制基礎(chǔ)
0.企業(yè)高可用性標(biāo)準(zhǔn) ***
0.1 全年無故障率(非計劃內(nèi)故障停機(jī))
99.9% ----> 0.001*365*24*60=525.6 min
99.99% ----> 0.0001*365*24*60=52.56 min
99.999% ----> 0.0001*365*24*60=5.256 min
0.2 高可用架構(gòu)方案
負(fù)載均衡:有一定的高可用性
LVS Nginx
主備系統(tǒng):有高可用性,但是需要切換,是單活的架構(gòu)
KA , MHA, MMM
真正高可用(多活系統(tǒng)):
NDB Cluster Oracle RAC Sysbase cluster , InnoDB Cluster（MGR）,PXC , MGC
1. 主從復(fù)制簡介 **
1.1. 基于二進(jìn)制日志復(fù)制的
1.2. 主庫的修改操作會記錄二進(jìn)制日志
1.3. 從庫會請求新的二進(jìn)制日志并回放,最終達(dá)到主從數(shù)據(jù)同步
1.4. 主從復(fù)制核心功能:
輔助備份,處理物理損壞
擴(kuò)展新型的架構(gòu):高可用,高性能,分布式架構(gòu)等
2. 主從復(fù)制前提(搭建主從的過程) ***
## 2.1 兩臺以上mysql實(shí)例 ,server_id,server_uuid不同
## 2.2 主庫開啟二進(jìn)制日志
## 2.3 專用的復(fù)制用戶
## 2.4 保證主從開啟之前的某個時間點(diǎn),從庫數(shù)據(jù)是和主庫一致(補(bǔ)課)
## 2.5 告知從庫,復(fù)制user,passwd,IP port,以及復(fù)制起點(diǎn)(change master to)
## 2.6 線程(三個):Dump thread IO thread SQL thread 開啟(start slave)
3. 主從復(fù)制搭建(Classic replication) ***
3.1 清理主庫數(shù)據(jù)
rm -rf /data/3307/data/*
3.2 重新初始化3307
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data
3.3 修改my.cnf ,開啟二進(jìn)制日志功能
[root@db01 3307]# vim /data/3307/my.cnf
log_bin=/data/3307/data/mysql-bin
3.4 啟動所有節(jié)點(diǎn)
[root@db01 3307]# systemctl start mysqld3307
[root@db01 3307]# systemctl start mysqld3308
[root@db01 3307]# systemctl start mysqld3309
[root@db01 3307]# ps -ef |grep mysqld
mysql 3684 1 4 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
mysql 3719 1 7 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
mysql 3754 1 8 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
[root@db01 3307]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
[root@db01 3307]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
[root@db01 3307]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
3.5 主庫中創(chuàng)建復(fù)制用戶
[root@db01 3307]# mysql -S /data/3307/mysql.sock
db01 [(none)]>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
db01 [(none)]>select user,host from mysql.user;
3.6 備份主庫并恢復(fù)到從庫
[root@db01 3307]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=653;
[root@db01 3307]# mysql -S /data/3308/mysql.sock
db01 [(none)]>source /backup/full.sql
3.7 告知從庫關(guān)鍵復(fù)制信息
ip port user password binlog position
[root@db01 3307]# mysql -S /data/3308/mysql.sock
db01 [mysql]>help 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=653,
MASTER_CONNECT_RETRY=10;
3.8 開啟主從專用線程
start slave ;
3.9 檢查復(fù)制狀態(tài)
db01 [mysql]>show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4. 主從復(fù)制的原理 (Classic Replication)*****
4.1 主從中設(shè)置到的文件和線程
4.1.1 線程
主:
DUMP THREAD
從:
IO THREAD
SQL THREAD
4.1.2 文件
主:
mysql-bin.000001
從:
db01-relay.000001 ===>中繼日志
master.info ===》主庫信息記錄日志
relay-log.info ===> 記錄中繼應(yīng)用情況信息
4.2 主從復(fù)制原理
image
image
主從復(fù)制原理描述：
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給主庫 ，主庫工作完成
8.IO_T將緩存中的數(shù)據(jù)，存儲到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T讀取relay-log.info文件，獲取到上次執(zhí)行到的relay-log的位置，作為起點(diǎn)，回放relay-log
10.SQL_T回放完成之后，會更新relay-log.info文件。
11. relay-log會有自動清理的功能。
細(xì)節(jié)：
1.主庫一旦有新的日志生成，會發(fā)送“信號”給binlog dump ，IO線程再請求
5. 主從故障監(jiān)控\分析\處理 *****
5.1 線程相關(guān)監(jiān)控
主庫:
show full processlist;
每個從庫都會有一行dump相關(guān)的信息
HOSTS:
db01:47176
State:
Master has sent all binlog to slave; waiting for more updates
如果現(xiàn)實(shí)非以上信息,說明主從之間的關(guān)系出現(xiàn)了問題
從庫:
db01 [(none)]>show slave status \G
*************************** 1\. row ***************************
主庫相關(guān)信息監(jiān)控
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 444
從庫中繼日志的應(yīng)用狀態(tài)
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 485
從庫復(fù)制線程有關(guān)的狀態(tài)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
過濾復(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:
主從延時相關(guān)狀態(tài)(非人為)
Seconds_Behind_Master: 0
延時從庫有關(guān)的狀態(tài)(人為)
SQL_Delay: 0
SQL_Remaining_Delay: NULL
GTID 復(fù)制有關(guān)的狀態(tài)
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
5.2 主從復(fù)制故障分析
5.2.1 IO
5.2.1.1 連接主庫
(1) 用戶 密碼 IP port
Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10 retries: 7
[root@db01 ~]# mysql -urepl -p123333 -h 10.0.0.51 -P 3307
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
原因:
密碼錯誤
用戶錯誤
skip_name_resolve
地址錯誤
端口
image
image
處理方法
stop slave
reset slave all
change master to
start slave
主庫連接數(shù)上線,或者是主庫太繁忙
show slave staus \G
Last_IO_Errno: 1040
Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10 retries: 7
處理思路:
拿復(fù)制用戶,手工連接一下
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
處理方法:
db01 [(none)]>set global max_connections=300;
(3) 防火墻,網(wǎng)絡(luò)不通
5.2.1.2 請求二進(jìn)制日志
主庫缺失日志
從庫方面,二進(jìn)制日志位置點(diǎn)不對
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/data/mysql-bin.000002' at 154, the last byte read from '/data/3307/data/mysql-bin.000002' at 154.'
image
注意: 在主從復(fù)制環(huán)境中,嚴(yán)令禁止主庫中reset master; 可以選擇expire 進(jìn)行定期清理主庫二進(jìn)制日志
解決方案:
重新構(gòu)建主從
5.2.2 SQL 線程故障
SQL線程功能：
(1)讀寫relay-log.info
(2)relay-log損壞,斷節(jié),找不到
(3)接收到的SQL無法執(zhí)行
導(dǎo)致SQL線程故障原因分析：
1\. 版本差異，參數(shù)設(shè)定不同，比如：數(shù)據(jù)類型的差異，SQL_MODE影響
2.要創(chuàng)建的數(shù)據(jù)庫對象,已經(jīng)存在
3.要刪除或修改的對象不存在
4.DML語句不符合表定義及約束時.
歸根揭底的原因都是由于從庫發(fā)生了寫入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'
處理方法(以從庫為核心的處理方案)：
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
#將同步指針向下移動一個，如果多次不同步，可以重復(fù)操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常見錯誤代碼:
1007:對象已存在
1032:無法執(zhí)行DML
1062:主鍵沖突,或約束沖突
但是，以上操作有時是有風(fēng)險的，最安全的做法就是重新構(gòu)建主從。把握一個原則,一切以主庫為主.
一勞永逸的方法:
(1) 可以設(shè)置從庫只讀.
db01 [(none)]>show variables like '%read_only%';
注意：
只會影響到普通用戶，對管理員用戶無效。
(2)加中間件
讀寫分離。
6. 主從延時監(jiān)控及原因 *****
主庫做了修改操作,從庫比較長時間才能追上.
6.1 外在因素
網(wǎng)絡(luò)
主從硬件差異較大
版本差異
參數(shù)因素
6.2 主庫
(1) 二進(jìn)制日志寫入不及時
[rep]>select @@sync_binlog;
(2) CR的主從復(fù)制中,binlog_dump線程,事件為單元,串行傳送二進(jìn)制日志(5.6 5.5)
1\. 主庫并發(fā)事務(wù)量大,主庫可以并行,傳送時是串行
2\. 主庫發(fā)生了大事務(wù),由于是串行傳送,會產(chǎn)生阻塞后續(xù)的事務(wù).
解決方案:
1\. 5.6 開始,開啟GTID,實(shí)現(xiàn)了GC(group commit)機(jī)制,可以并行傳輸日志給從庫IO
2\. 5.7 開始,不開啟GTID,會自動維護(hù)匿名的GTID,也能實(shí)現(xiàn)GC,我們建議還是認(rèn)為開啟GTID
3\. 大事務(wù)拆成多個小事務(wù),可以有效的減少主從延時.
6.3 從庫
SQL線程導(dǎo)致的主從延時
在CR復(fù)制情況下: 從庫默認(rèn)情況下只有一個SQL,只能串行回放事務(wù)SQL
1\. 主庫如果并發(fā)事務(wù)量較大,從庫只能串行回放
2\. 主庫發(fā)生了大事務(wù),會阻塞后續(xù)的所有的事務(wù)的運(yùn)行
解決方案:
1\. 5.6 版本開啟GTID之后,加入了SQL多線程的特性,但是只能針對不同庫(database)下的事務(wù)進(jìn)行并發(fā)回放.
2\. 5.7 版本開始GTID之后,在SQL方面,提供了基于邏輯時鐘(logical_clock),binlog加入了seq_no機(jī)制,
真正實(shí)現(xiàn)了基于事務(wù)級別的并發(fā)回放,這種技術(shù)我們把它稱之為MTS(enhanced multi-threaded slave).
3\. 大事務(wù)拆成多個小事務(wù),可以有效的減少主從延時.
[https://dev.mysql.com/worklog/task/?id=6314]
7. 小結(jié)
1\. 主從復(fù)制原理
2\. 主從復(fù)制故障
3\. 主從延時：group commit MTS
