MySQL主從複制
1.介紹
通過二進制日志方式，達到2台以上MySQL執行個體資料“同步”（異步同步）。
2.主從複制前提 （搭建）
2.1兩台以上資料庫執行個體，版本一緻
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309
2.2 區分不同角色
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
@@server_id: 7
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
@@server_id: 8
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
@@server_id: 9
2.3 主庫開啟二進制日志（3307）
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@log_bin"
@@log_bin: 1
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@log_bin_basename"
@@log_bin_basename: /data/3307/mysql-bin
2.4 主庫建立專用複制使用者
注意：8.0版本建立使用者授權指令
1. create user [email protected]'10.0.0.%' identified with mysql_native_password by '123';
2. grant replication slave on *.* to [email protected]'10.0.0.%';
2.5 備份主庫資料，恢複從庫
[root@db01 data]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 >/tmp/full.sql
[root@db01 data]# mysql -S /data/3308/mysql.sock </tmp/full.sql
[root@db01 data]# mysql -S /data/3309/mysql.sock </tmp/full.sql
2.6 開啟從庫複制功能（連接配接資訊，複制起點）
[root@db01 ~]# grep "\-- CHANGE MASTER TO" /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=674;
#告知兩個從庫關鍵複制資訊
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=674,
MASTER_CONNECT_RETRY=10;
#開啟主從專用線程
mysql> start slave;
2.7 檢查主從狀态
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show slave status \G"|grep "Running"
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running"
3.主從複制原理
3.1 涉及到的檔案
主庫：binlog（二進制日志）
從庫：relay-log（中繼日志）：存儲請求過來的binlog
master.info：儲存主庫資訊（IP,PORT, USER, PASSWORD, 複制binlog的位置點）
relay-log.info：記錄的是從庫回放relaylog的位置點資訊。
3.2 涉及到的線程
主庫：dump： 日志投遞線程
從庫：IO ： 連接配接主庫，請求日志
SQL： 回放日志
3.3 主從複制原理（文字說明）
1. 從庫執行change master to 語句：IP、PORT、USER、PASSWD、binlog起點，資訊記錄到master.info
2. 從庫執行start slave 。開啟IO、SQL複制線程
3. 從庫IO開始工作，讀取master.info： IP、 PORT 、USER、PASSWD，連接配接主庫。
4. 主庫連接配接層接收到請求，驗證通過後，生成 DUMP線程和IO線程互動。
5. 從庫IO 通過master.info ： binlog起點，找主庫DUMP請求新的binlog
6. 主庫DUMP監控着binlog變化，接收到從庫IO請求，截取最新的Binlog，傳輸給IO
7. 從庫IO接收到binlog，臨時存儲再TCP/IP緩存。主庫工作到此為止。
8. 從庫IO将接收到的日志存儲到relay-log中，并更新master.info。IO線程工作結束
9. 從庫SQL線程讀取relay.info中，擷取到上次回放到的relay-log的位置點
10.從庫SQL回放新的relaylog，再次更新relay.info。SQL線程工作結束。
彩蛋：
11. relay_log_purge線程，對relay-log有自動清理的功能。
12. 主庫dump線程實時監控binlog的變化，自動通知給從庫IO。
4.主從複制監控
4.1主庫監控
mysql> show processlist;
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 9 | | 3309 | 7 | d1492ae9-6728-11ea-ad4b-000c29248f69 |
| 8 | | 3308 | 7 | ced6749c-6728-11ea-ab51-000c29248f69 |
+-----------+------+------+-----------+--------------------------------------+
4.2 從庫監控
mysql> show slave status \G
主庫資訊彙總：master.info
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 674
從庫relaylog回放到的位置點：relay-log.info
Relay_Log_File: db01-relay-bin.000004
Relay_Log_Pos: 320
從庫的線程狀态：log_error
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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
SQL_Remaining_Delay: NULL
GTID複制狀态：
Retrieved_Gtid_Set:
Executed_Gtid_Set:
5. 主從複制故障原因分析
5.0 監控方法
show slave status \G
從庫的線程狀态：log_error
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
5.1 IO
5.1.1 連接配接主庫：connecting
可能原因：
連接配接資訊有誤。
網絡故障。
防火牆。
最大連接配接數上線。
排查方法：
處理方法：
mysql -S /data/3308/mysql.sock -e "stop slave;reset slave all;"
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=674,
MASTER_CONNECT_RETRY=10;
start slave;
5.1.2 請求日志: NO
主庫日志損壞。
日志起點寫錯。
server_id重複
排查方法：
mysql> show slave status \G
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 674
Last_IO_Error: xxxx
5.2 SQL 線程故障： 回放中繼日志
5.2.1 中繼日志損壞
環境準備：
1. 從庫停SQL線程
stop slave sql_thread ;
2. 主庫發生新的操作
create database test1;
3. 從庫删除relaylog
rm -rf /data/3308/data/db01-relay-bin.00000*
4. 啟動SQL線程
start slave sql_thread ;
修複：
1）cat /data/3308/data/relay-log.info ----> binlog 位置點
7
./db01-relay-bin.000002
320
mysql-bin.000001
154
0
0
1
2）重構
stop slave;
reset slave all;
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=154,
MASTER_CONNECT_RETRY=10;
start slave;
5.2.2 日志回放失敗（執行不了SQL）
1）修改的對象不存在
2）建立的對象已存在
3）限制沖突
4）主從配置不同
5）SQL_MODE不相容
6）主從版本差異
方法0：從庫逆反操作
mysql> drop 已有的
mysql> stop slave;
mysql> start slave;
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
#将同步指針向下移動一個，如果多次不同步，可以重複操作。
start slave;
方法二
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常見錯誤代碼:
1007:對象已存在
1032:無法執行DML
1062:主鍵沖突,或限制沖突
但是，以上操作有時是有風險的，最安全的做法就是重新建構主從。把握一個原則,一切以主庫為主.
方法三： PT工具
pt-table-checksum
pt-table-sync
方法四：從庫隻讀
mysql> select @@read_only;
mysql> select @@super_read_only;
6. 主從複制延時
主庫做了操作，從庫很久才回放
6.1 主庫方面
6.1.1 提供binlog
binlog日志檔案落地（寫入磁盤）不及時，sync_binlog=1（控制實時将日志寫入磁盤）
6.1.2傳輸binlog
傳統claassic 模式（無GTID），dump線程傳輸日志是串行的。主庫可以并行多個事務。
産生問題：大事務、并發事務量大。都會導緻較高延時。
5.6 版本加入了GTID功能，在傳輸時就可以并行傳輸日志了。
5.7 版本即使沒開GTID，會自動生成Anonymous_Gtid。
6.2 從庫方面
6.2.1 relay 落地
無其他影響因素，唯一因素就是從庫所在的磁盤性能
6.2.2 SQL回訪
單一SQL線程（預設一個），隻能串行回放relaylog。主庫可以并發事務，并行傳輸日志，回放時是串行的。
産生問題：如果大事務，并發事務量大。都會導緻較高回放延時。
5.6 版本GTID模式下，可以開啟多個SQL線程。但是，5.6多SQL回放時，隻能針對不同database并行回放。
5.7 版本GTID模式下，可以開啟多個SQL線程，真正實作了并性回放（MTS）
6.3 外部因素
網絡慢
主從配置相差大 （例如主庫固态盤，從庫機械盤）
7.過濾複制
7.1 介紹
部分資料複制
7.2 配置方法
主庫：
binlog_do_db=world
binlog_ignore_db
從庫：
庫級别
replicate_do_db=world
replicate_do_db=test
replicate_ignore_db=
表級别：
replicate_do_table=world.city
replicate_ignore_table=
模糊：
replicate_wild_do_table=world.t*
replicate_wild_ignore_table=
7.3 模拟（隻同步oldguo和olddoy庫）
[root@db01 3309]# cat /data/3309/my.cnf
[mysqld]
basedir=/data/app/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
replicate_do_db=oldguo
replicate_do_db=oldboy
systemctl restart mysqld3309
7.4 線上修改
stop slave sql_thread;
change replication filter replicate_do_db=(oldguo,oldboy)
start slave sql_thread;
取消：
stop slave sql_thread;
change replication filter replicate_do_db=()
start slave sql_thread;
8. 延時從庫
8.1 介紹
人為設定的一種特殊從庫，主庫變更，在延時時間過後，從庫才執行。
8.2 什麼是資料損壞
邏輯損壞：DROP delete truncate update
實體損壞：磁盤，檔案
8.3 配置
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
8.4 怎麼用
8.4.1 思路
1）場景假設：
主庫 drop database oldguo; 9:00
2）監控故障 9:10
發現業務olgduo業務不能正常運作
3）挂維護頁
4）停止主從
判斷業務是否還有流量，停止主從
5）修複資料
使用relay-log修複資料
起點：relay- log.info ---> sQL線程執行到的位置點
終點：drop之前
6）業務恢複
從庫替代主庫工作
7）主從修複（後話）
8.4.2模拟故障，使用延時從恢複資料
1.主庫模拟基礎資料
create database delaydb charset utf8mb4;
use delaydb;
create table t1 (idint);
insert into t1 values(1),(2),(3);
commit;
create table t2 (id int);
insert into t2 values(1),(2),(3);
commit;
create table t3 (id int);
insert into t3 values(1),(2),(3);
commit;
drop database delaydb;
2.從庫
1）線程
2）截取relaylog
起點：
mysql> show slave status\G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476
終點：
| db01-relay-bin.000002 | 2039 | Query | 7 | 3284 | drop database delaydb
mysql> show relaylog events in 'db01-relay-bin.000002';
[root@db01 data]# cd /data/3309/data/
[root@db01 data]# mysqlbinlog --start-position=476 --stop-position=2039 db01-relay-bin.000002 >/tmp/relay.sql
3）恢複relaylog 到從庫
mysql> stop slave;
mysql> reset slave all;
mysql> reset master;
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> set sql_log_bin=1;
8.5 主從資料一緻: 半同步複制、無損複制、MGR
5.5 版本加入，半同步複制。
1）不能100%保證，主從一緻性。
2）性能拉低很多
5.6 gtid 串行傳輸日志，串行SQL，可以緩解。
5.7 增強半同步複制，無損複制。
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
5.7.17 加入MGR ，8.0以後MGR 。
https://www.jianshu.com/p/8c66e0f65324
9.GTID複制
9.1 優勢
1）每個事務都有唯一邏輯編号，并具備幂等性
2）截取binlog時更加靈活、友善(include-gtids --exclude-gtids)
3）主從複制，提高性能：dump傳輸日志并行，SQL線程并行回放。
4）主從複制搭建、監控延時、資料一-緻性保證。
9.2 搭建
9.2.1 準備3台獨立虛拟機節點
9.2.2 清理環境（3個節點）
pkill mysqld
rm -rf /data/3306/*
mv /etc/my.cnf /tmp
9.2.3 建立需要的目錄
mkdir -p /data/3306/data /data/3306/binlog
chown -R mysql.mysql /data
9.2.4 準備配置檔案
db01
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\\d]>
socket=/tmp/mysql.sock
EOF
db02
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
server_id=7
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\\d]>
socket=/tmp/mysql.sock
EOF
db03
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
server_id=8
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_data_file_path=ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\\d]>
socket=/tmp/mysql.sock
EOF
9.2.5 初始化資料
mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data
9.2.6 啟動資料庫
/etc/init.d/mysqld start
9.2.7 建構主從
1）db01建立使用者
db01 [(none)]>grant replication slave on *.* to [email protected]'10.0.0.%' identified by '123';
2）db02、db03建構主從
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
9.3 gtid 建構時的不同點
MASTER_AUTO_POSITION=1;
參數功能：第一次建構主從時，自動檢查最後一個relay的gtid資訊，檢查沒有SET @@GLOBAL.GTID_PURGED='1c35b73a-7321-11ea-8974-000c29248f69:1-10'參數
如果都沒有資訊。就從主庫的第一個GTID事件開始全新複制binlog日志。
注意： 備份主庫資料 ，恢複至從庫的方式建構GTID主從，不要 --set-gtid-purged=OFF
9.4 檢視監控資訊
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2
解決—注入空事物的方法：
stop slave;
set gtid_next='71bfa52e-4aae-11e9-ab8c-000c293b577e:3';
begin;commit;
set gtid_next='AUTOMATIC';
10.主從複制架演變
10.1基礎架構
1主1從
1主多從
多級主從
雙主結構
10.2 進階架構
10.2.1 高可用架構
1）故障轉移
2）資料一緻保證
3）代表産品
MHA（faceback、RDS MySQL(TMHA)、Oracle官方Operator(K8s+MHA)）
PXC(Percona)
MGC（mariaDB）
MySQL Cluster
Mycat
InnoDB Cluster(8.0.17 clone plugin) 未來 2-3年
10.2.2高性能架構
1）讀寫分離：
Atlas
ProxySQL
Maxscale
Mycat
2）分布式架構：
Mycat
DBLE
sharding-jdbc
ProxySQL
10.2.3 NewSQL
PinCAP TiDB
Aliyun PolarDB
Google Spanner