MySQL-lesson08-主從復(fù)制基礎(chǔ)
0.企業(yè)高可用性標(biāo)準(zhǔn) ***
0.1 全年無故障率(非計(jì)劃內(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ù)制簡(jiǎn)介 **
1.1. 基于二進(jìn)制日志復(fù)制的
1.2. 主庫(kù)的修改操作會(huì)記錄二進(jìn)制日志
1.3. 從庫(kù)會(huì)請(qǐng)求新的二進(jìn)制日志并回放,最終達(dá)到主從數(shù)據(jù)同步
1.4. 主從復(fù)制核心功能:
輔助備份,處理物理?yè)p壞
擴(kuò)展新型的架構(gòu):高可用,高性能,分布式架構(gòu)等
2. 主從復(fù)制前提(搭建主從的過程) ***
## 2.1 兩臺(tái)以上mysql實(shí)例 ,server_id,server_uuid不同
## 2.2 主庫(kù)開啟二進(jìn)制日志
## 2.3 專用的復(fù)制用戶
## 2.4 保證主從開啟之前的某個(gè)時(shí)間點(diǎn),從庫(kù)數(shù)據(jù)是和主庫(kù)一致(補(bǔ)課)
## 2.5 告知從庫(kù),復(fù)制user,passwd,IP port,以及復(fù)制起點(diǎn)(change master to)
## 2.6 線程(三個(gè)):Dump thread IO thread SQL thread 開啟(start slave)
3. 主從復(fù)制搭建(Classic replication) ***
3.1 清理主庫(kù)數(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 啟動(dòng)所有節(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 主庫(kù)中創(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 備份主庫(kù)并恢復(fù)到從庫(kù)
[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 告知從庫(kù)關(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 ===》主庫(kù)信息記錄日志
relay-log.info ===> 記錄中繼應(yīng)用情況信息
4.2 主從復(fù)制原理
image
image
主從復(fù)制原理描述：
1.change master to 時(shí)，ip pot user password binlog position寫入到master.info進(jìn)行記錄
2. start slave 時(shí)浸颓，從庫(kù)會(huì)啟動(dòng)IO線程和SQL線程
3.IO_T萄唇，讀取master.info信息，獲取主庫(kù)信息連接主庫(kù)
4. 主庫(kù)會(huì)生成一個(gè)準(zhǔn)備binlog DUMP線程，來響應(yīng)從庫(kù)
5. IO_T根據(jù)master.info記錄的binlog文件名和position號(hào)顿肺，請(qǐng)求主庫(kù)DUMP最新日志
6. DUMP線程檢查主庫(kù)的binlog日志钟鸵，如果有新的，TP(傳送)給從從庫(kù)的IO_T
7. IO_T將收到的日志存儲(chǔ)到了TCP/IP 緩存撇眯，立即返回ACK給主庫(kù) 报嵌，主庫(kù)工作完成
8.IO_T將緩存中的數(shù)據(jù)虱咧，存儲(chǔ)到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T讀取relay-log.info文件锚国，獲取到上次執(zhí)行到的relay-log的位置腕巡，作為起點(diǎn)，回放relay-log
10.SQL_T回放完成之后血筑，會(huì)更新relay-log.info文件绘沉。
11. relay-log會(huì)有自動(dòng)清理的功能。
細(xì)節(jié)：
1.主庫(kù)一旦有新的日志生成豺总，會(huì)發(fā)送“信號(hào)”給binlog dump 车伞，IO線程再請(qǐng)求
5. 主從故障監(jiān)控\分析\處理 *****
5.1 線程相關(guān)監(jiān)控
主庫(kù):
show full processlist;
每個(gè)從庫(kù)都會(huì)有一行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)了問題
從庫(kù):
db01 [(none)]>show slave status \G
*************************** 1\. row ***************************
主庫(kù)相關(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
從庫(kù)中繼日志的應(yīng)用狀態(tài)
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 485
從庫(kù)復(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:
主從延時(shí)相關(guān)狀態(tài)(非人為)
Seconds_Behind_Master: 0
延時(shí)從庫(kù)有關(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 連接主庫(kù)
(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)
原因:
密碼錯(cuò)誤
用戶錯(cuò)誤
skip_name_resolve
地址錯(cuò)誤
端口
image
image
處理方法
stop slave
reset slave all
change master to
start slave
主庫(kù)連接數(shù)上線,或者是主庫(kù)太繁忙
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 請(qǐng)求二進(jìn)制日志
主庫(kù)缺失日志
從庫(kù)方面,二進(jìn)制日志位置點(diǎn)不對(duì)
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)令禁止主庫(kù)中reset master; 可以選擇expire 進(jìn)行定期清理主庫(kù)二進(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.要?jiǎng)?chuàng)建的數(shù)據(jù)庫(kù)對(duì)象,已經(jīng)存在
3.要?jiǎng)h除或修改的對(duì)象不存在
4.DML語(yǔ)句不符合表定義及約束時(shí).
歸根揭底的原因都是由于從庫(kù)發(fā)生了寫入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'
處理方法(以從庫(kù)為核心的處理方案)：
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
#將同步指針向下移動(dòng)一個(gè)，如果多次不同步沸枯，可以重復(fù)操作日矫。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常見錯(cuò)誤代碼:
1007:對(duì)象已存在
1032:無法執(zhí)行DML
1062:主鍵沖突,或約束沖突
但是，以上操作有時(shí)是有風(fēng)險(xiǎn)的绑榴，最安全的做法就是重新構(gòu)建主從哪轿。把握一個(gè)原則,一切以主庫(kù)為主.
一勞永逸的方法:
(1) 可以設(shè)置從庫(kù)只讀.
db01 [(none)]>show variables like '%read_only%';
注意：
只會(huì)影響到普通用戶，對(duì)管理員用戶無效翔怎。
(2)加中間件
讀寫分離窃诉。
6. 主從延時(shí)監(jiān)控及原因 *****
主庫(kù)做了修改操作,從庫(kù)比較長(zhǎng)時(shí)間才能追上.
6.1 外在因素
網(wǎng)絡(luò)
主從硬件差異較大
版本差異
參數(shù)因素
6.2 主庫(kù)
(1) 二進(jìn)制日志寫入不及時(shí)
[rep]>select @@sync_binlog;
(2) CR的主從復(fù)制中,binlog_dump線程,事件為單元,串行傳送二進(jìn)制日志(5.6 5.5)
1\. 主庫(kù)并發(fā)事務(wù)量大,主庫(kù)可以并行,傳送時(shí)是串行
2\. 主庫(kù)發(fā)生了大事務(wù),由于是串行傳送,會(huì)產(chǎn)生阻塞后續(xù)的事務(wù).
解決方案:
1\. 5.6 開始,開啟GTID,實(shí)現(xiàn)了GC(group commit)機(jī)制,可以并行傳輸日志給從庫(kù)IO
2\. 5.7 開始,不開啟GTID,會(huì)自動(dòng)維護(hù)匿名的GTID,也能實(shí)現(xiàn)GC,我們建議還是認(rèn)為開啟GTID
3\. 大事務(wù)拆成多個(gè)小事務(wù),可以有效的減少主從延時(shí).
6.3 從庫(kù)
SQL線程導(dǎo)致的主從延時(shí)
在CR復(fù)制情況下: 從庫(kù)默認(rèn)情況下只有一個(gè)SQL,只能串行回放事務(wù)SQL
1\. 主庫(kù)如果并發(fā)事務(wù)量較大,從庫(kù)只能串行回放
2\. 主庫(kù)發(fā)生了大事務(wù),會(huì)阻塞后續(xù)的所有的事務(wù)的運(yùn)行
解決方案:
1\. 5.6 版本開啟GTID之后,加入了SQL多線程的特性,但是只能針對(duì)不同庫(kù)(database)下的事務(wù)進(jìn)行并發(fā)回放.
2\. 5.7 版本開始GTID之后,在SQL方面,提供了基于邏輯時(shí)鐘(logical_clock),binlog加入了seq_no機(jī)制,
真正實(shí)現(xiàn)了基于事務(wù)級(jí)別的并發(fā)回放,這種技術(shù)我們把它稱之為MTS(enhanced multi-threaded slave).
3\. 大事務(wù)拆成多個(gè)小事務(wù),可以有效的減少主從延時(shí).
[https://dev.mysql.com/worklog/task/?id=6314]
7. 小結(jié)
1\. 主從復(fù)制原理
2\. 主從復(fù)制故障
3\. 主從延時(shí)：group commit MTS
