MySQL Replication
主從復(fù)制介紹
1.主從復(fù)制基于binlog來實(shí)現(xiàn)的
2.主庫發(fā)生新的操作锯七，都會記錄binlog
3.從庫取得主庫的binlog進(jìn)行回放
4.主從復(fù)制的過程時異步
搭建主從復(fù)制
1.2個或以上的數(shù)據(jù)庫實(shí)例
2.主庫需要開啟二進(jìn)制日志
3.server_id要不同，區(qū)分不同的節(jié)點(diǎn)
4.主庫需要建立專用的復(fù)制用戶（replication slave）
5.人為告訴從庫一些復(fù)制信息（ip,port user pass,二進(jìn)制日志起點(diǎn)）
6.從庫應(yīng)該開啟專門的復(fù)制線程
準(zhǔn)備多實(shí)例
3307為主庫 3308位從庫 （搭建過程http://www.reibang.com/writer#/notebooks/40881420/notes/57340778
）
啟動3307
pkill mysqld
systemctl start mysqld 3307
初始化3308
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
systemctl start mysqld3308
mysql -S /data/3308/mysql.sock
檢查3307
mysql -uroot -p123456 -S /data/3307/mysql.sock -e 'select @@port'
檢查配置文件
主庫：二進(jìn)制日志是否開啟
3307
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
3308
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
主庫創(chuàng)建復(fù)制用戶
mysql -uroot -p123456 -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';"
主庫進(jìn)行全備
mysqldump -root -p123456 -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
從庫
mysql -uroot -p123456 -S /tmp/full.sql
set sql_log_bin=0;
source /tmp/full.sql
告訴從庫復(fù)制信息
查看起始點(diǎn)
more /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=444;
登陸數(shù)據(jù)庫
mysql -uroot -p123456 -S /data/3308/mysql.scok
CHANGE MASTER TO
MASTER_HOST='10.0.0.51', #主機(jī)IP或主機(jī)名
MASTER_USER='repl', #用戶
MASTER_PASSWORD='123456', #密碼
MASTER_PORT=3307, #端口
MASTER_LOG_FILE='mysql-bin.000003', #binlog日志
MASTER_LOG_POS=444, #起點(diǎn)
MASTER_CONNECT_RETRY=10;
從庫開啟復(fù)制線程(IO,SQL)
start slave;
檢查主從復(fù)制的狀態(tài)
show status slave \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主庫創(chuàng)建一個數(shù)據(jù)庫
create database hahaha;
從庫檢查
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| backup |
| binlog |
| gtid |
| hahaha |
| mysql |
| performance_schema |
| school |
| sky |
| sys |
| test |
| world |
| zabbix |
+--------------------+
如果復(fù)制中出現(xiàn)錯誤看看是否change master to里寫錯了
stop slave;
reset slave all;
再次change master to...
主從復(fù)制的原理
主從復(fù)制中涉及的文件
主庫：
binlog
從庫：
relaylog 中繼日志
master.info 主庫信息文件
relaylog.info relaylog應(yīng)用的信息
主從復(fù)制中涉及的線程
主庫：
binlog_dump Therad : DUMP_T
從庫：
SLAVE_IO_THREAD : IO_T
SLAVE_SQL_THREAD : SQL_T
1.從庫執(zhí)行change master to命令（主庫的連接信息+復(fù)制點(diǎn)）
2.從庫將以上信息，記錄到master.info文件中
3.從庫執(zhí)行start slave 命令峡捡，立即開啟IO_T和SQL_T
- 從庫的IO_T，讀取master.info文件中的信息（IP,PROT,USER,PASS,binlog
位置點(diǎn)）
5.從庫IO_T請求連接主庫，主庫專門提供一個DUMP_T，負(fù)責(zé)和IO_T交互
6.IO_T根據(jù)binlog的位置信息体捏，請求主庫新的binlog
7.主庫DUMP_T 將最新的binlog，通過網(wǎng)絡(luò)TP（傳送）給從庫的IO_T
8.IO_T接收到新的binlog日志糯崎，存儲到TCP/IP緩存中几缭，立即返回ACK給主庫,并更新master.info
9.IO_T將TCP/IP緩存中數(shù)據(jù)，轉(zhuǎn)存到磁盤relaylog中
10.SQL_T讀取relay.info中的信息沃呢，獲取上次已經(jīng)應(yīng)用過的relay.log的位置信息
11.SQL_T會按照上次的位置點(diǎn)往后回放最新的relaylog年栓，再次更新relay.info信息
12.從庫會自動purge應(yīng)用過relay進(jìn)行定期清理
補(bǔ)充說明：
一旦主從復(fù)制構(gòu)建成功，主庫當(dāng)中發(fā)生了新的變化薄霜，都會通過dump_T發(fā)送信號給IO_T某抓，增強(qiáng)了主從復(fù)制的實(shí)時性
主從復(fù)制監(jiān)控
show slave status\G
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
[主庫相關(guān)信息 master.info]
Master_Host: 10.0.0.51 地址
Master_User: repl 用戶
Master_Port: 3307 端口
Connect_Retry: 10 嘗試幾次
Master_Log_File: mysql-bin.000003 應(yīng)該與主庫相同
Read_Master_Log_Pos: 609
[從庫relaylog應(yīng)用信息relay.info]
Relay_Log_File: db01-relay-bin.000002 從庫SQL_T到那個點(diǎn)
Relay_Log_Pos: 485
Relay_Master_Log_File: mysql-bin.000003
[從庫線程運(yùn)行狀態(tài)用于排錯]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0 從下面復(fù)制過來的
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
[過濾復(fù)制有關(guān)的]
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
[從庫延遲的時間]
Seconds_Behind_Master: 0 查看主從復(fù)制延遲
[延遲從庫竿刁，人為定制]
SQL_Delay: 0
SQL_Remaining_Delay: NULL
[GTID復(fù)制有關(guān)的狀態(tài)信息]
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
主從復(fù)制故障
從庫：
IO線程故障
連接主庫(connecting): 網(wǎng)絡(luò)，連接信息錯誤或變更了搪缨，防火墻，連接數(shù)上限
排查思路：使用復(fù)制用戶手工登陸,是否用戶名 密碼 端口 是否正確
stop slave
reset slave all;
change master to...
start slave;
請求binlog
binlog 沒開
binlog 損壞鸵熟，不存在副编，reset master
主庫reset master處理辦法
從庫
stop slave;
reset slave all;
change master to 最新的
start slave;
SQL線程故障
relay-log損壞
回放relaylog，一下容易出現(xiàn)SQL故障的
insert delete update ----> t1 表不存在流强，
create table sky --->sky 庫不存在
約束沖突（主建痹届，唯一鍵，非空...）
合理解決辦法：
一切以主庫為準(zhǔn)進(jìn)行解決
如果出現(xiàn)問題打月，盡量進(jìn)行反操作
最直接穩(wěn)妥的辦法队腐，重新構(gòu)造主從
暴力的解決辦法
方法一：
stop slave;
set gloabl sql_slave_skip_counter =1;
將同步指針向下移動一個，如果多次不同步奏篙，可以重復(fù)操作
start slave;
方法二：
/etc/my.cnf
slvae-skip-errors=1032,1062,1007
常見錯誤代碼
1007:對象已經(jīng)存在
1032：無法執(zhí)行DML
1062:主建沖突柴淘，或約束沖突
但是，以上操作有時是由風(fēng)險的秘通，最安全的做法就是重新構(gòu)造主從
對于主建沖突問題
主從延遲
主庫方面
1为严，binlog寫入不及時
sync_binlog=1 及時寫入
2，默認(rèn)情況下dump_T 是串行傳輸binlog
在并發(fā)事務(wù)量大時肺稀，由于dump_t是串行工作的第股，導(dǎo)致傳輸日志較慢
解決辦法
必須GTID，使用group commit方式话原，支持dump_t并行
3.主庫及其繁忙
慢語句
鎖等待
從庫個數(shù)
網(wǎng)絡(luò)延時
從庫方面
1.傳統(tǒng)復(fù)制中
如果主庫并發(fā)事務(wù)量很大夕吻，或者出現(xiàn)大事務(wù)
由于從庫是單線程，導(dǎo)致繁仁，不管傳的日志有多少涉馅，只能一次執(zhí)行一個事務(wù)
5.6版本，有了GTID改备，可以實(shí)現(xiàn)多sql線程控漠，但是只能基于不同庫的事務(wù)進(jìn)行并發(fā)回放（database）
5.7版本，有了增強(qiáng)的GTID悬钳，增加了seq_no盐捷，增加了新型的并發(fā)SQL線程模式(logical_clock),MTS技術(shù)
2.主從硬件差異太大
3.主從的參數(shù)配置
4.從庫和主庫的索引不一致
5.主從版本不一樣
監(jiān)控主從延遲
mysql> show slave status\G
Seconds_Behind_Master: 0
主庫方面的原因監(jiān)控
show master status\G
File: mysql-bin.000003
Position: 609
跟從庫對比
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 609
從庫方面的原因監(jiān)控
cat relay-log.info
7
./db01-relay-bin.000003
320
mysql-bin.000003
609
show slave status\G
Relay_Log_File: db01-relay-bin.000004
Relay_Log_Pos: 320
Exec_Master_Log_Pos: 609
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 609
一致沒有延遲，如果有區(qū)別默勾，查了多少pos 看看誰導(dǎo)致的碉渡，查看 exec_masetr_log_pos 與read_master_log_pos查了多少個，查看日志
延時從庫
stop slave;
#秒單位
change master to master_delay=300;
start slave;
查看狀態(tài)
show slave status \G
SQL_Delay: 300 #300秒
SQL_Remaining_Delay: NULL #最近一個事務(wù)還差多少秒
在主庫創(chuàng)建一個庫
create database aa;
這個時候再去查看
show slave status\G
SQL_Delay: 300
SQL_Remaining_Delay: 294
假如主庫被刪除了 母剥，停掉sql線程
故障恢復(fù)思路
1.監(jiān)控到數(shù)據(jù)庫邏輯故障
2.停從庫SQL線程滞诺，記錄已經(jīng)回放的位置點(diǎn)（截取日志起點(diǎn)）
stop slave sql_thread;
show slave status \G
relay_log_file: db01-relay-bin.000003
relay_log_pos: 320
3.截取relayLog
起點(diǎn)：
show slave status \G
relay_log_file relay_log_pos
終點(diǎn)：drop之前的位置點(diǎn)
show relaylog events in ' '
進(jìn)行截取
4.模擬sql線程回放日志
從庫 source
5.恢復(fù)業(yè)務(wù)
情況一：就一個庫的話形导，從庫代替主庫工作
情況二：從庫導(dǎo)出故障庫，還原到主庫中
故障演練
主庫
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
drop databases delay;
從庫
停止從庫SQL線程
stop slave sql_thread;
獲取起點(diǎn)位置
show slave statu \G
Relay_Log_File: db01-relay-bin.000003
Relay_Log_Pos: 320
找到relay的截取終點(diǎn)
mysql> show relaylog events in 'db01-relay-bin.000003';
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| db01-relay-bin.000003 | 4 | Format_desc | 8 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000003 | 123 | Previous_gtids | 8 | 154 | |
| db01-relay-bin.000003 | 154 | Rotate | 7 | 0 | mysql-bin.000003;pos=765 |
| db01-relay-bin.000003 | 201 | Format_desc | 7 | 0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000003 | 320 | Anonymous_Gtid | 7 | 830 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000003 | 385 | Query | 7 | 943 | create database delay charset utf8mb4 |
| db01-relay-bin.000003 | 498 | Anonymous_Gtid | 7 | 1008 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000003 | 563 | Query | 7 | 1108 | use `delay`; create table t1 (id int) |
| db01-relay-bin.000003 | 663 | Anonymous_Gtid | 7 | 1173 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000003 | 728 | Query | 7 | 1246 | BEGIN |
| db01-relay-bin.000003 | 801 | Table_map | 7 | 1292 | table_id: 255 (delay.t1) |
| db01-relay-bin.000003 | 847 | Write_rows | 7 | 1342 | table_id: 255 flags: STMT_END_F |
| db01-relay-bin.000003 | 897 | Xid | 7 | 1373 | COMMIT /* xid=2891 */ |
| db01-relay-bin.000003 | 928 | Anonymous_Gtid | 7 | 1438 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| db01-relay-bin.000003 | 993 | Query | 7 | 1533 | drop database delay |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
|db01-relay-bin.000003 | 993 | Query | 7 | 1533 | drop database delay 這個就是結(jié)束位置
截取relaylog
cd /data/3308/data/
截取
mysqlbinlog --start-position=320 --stop-position=993 db01-relay-bin.000003 >/tmp/relay.sql
查看一下relay.sql有沒有drop命令 沒有就對了
恢復(fù)relay到從庫
set sql_log_bin=0;
source /tmp/relay.sql
show databases;
show tables;
+-----------------+
| Tables_in_delay |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
過濾復(fù)制
快速恢復(fù)測試環(huán)境
從庫
drop database delau;
stop slave;
reset slave all;
主庫
reset master;
從庫重新連接
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='repl',
-> master_password='123456',
-> master_port=3307,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154,
-> master_connect_retry=10;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
start slave;
參數(shù)
主庫（很少用）
binlog_do_db=word 白名單 只會同步word
binlog_ignore_db=word 黑名單 不會同步world
從庫（寫到配置文件里全是小寫）
[庫級別的]
replicate_do_db=白名單
replicate_ignore_db:=黑名單
[表級別的]
Replicate_Do_Table:
Replicate_Ignore_Table:
[模糊匹配表]
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
測試
從庫
replicate_do_db=repl
systemctl restart mysqld3308
主庫
create database aaaaaaaaaa;
從庫查看
show databases;
并沒有
主庫
create database repl;
從庫查看
show databases;
主從調(diào)優(yōu)
5.7從庫多線程MTS
必須開啟GTID
binlog必須是row模式
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=on
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
CPU核心數(shù)作為標(biāo)準(zhǔn)
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
