欢迎补充、指正(备注：部分内容参考自网文)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
部分、主从基础知识
一、MySQL主从复制线程的关联
1. Master 上面的 binlog dump 线程，该线程负责将 master 的 binlog event 传到slave；
2. Slave 上面的 IO 线程，该线程负责接收 Master 传过来的 binlog，并写入 relay log；
3. Slave 上面的 SQL 线程，该线程负责读取 relay log 并执行；
4. 如果是多线程复制，
SQL线程只做 coordinator，只负责把 relay log 中的 binlog读出来然后交给 worker 线程，
woker 线程负责具体 binlog event 的执行；
二、正确关闭slave步骤
1、查看慢sql
2、执行STOP SLAVE语句
3、使用SHOW STATUS检查slave_open_temp_tables变量的值
(1)如果值为0，使用mysqladmin shutdown命令关闭从服务器
(2)如果值不为0，用START SLAVE重启从服务器线程
(3)补充：slave_open_temp_tables
slave_open_temp_tables 的值显示，当前 slave 创建了多少临时表。
三、传统复制(基于postion)
1、master用户写入数据，生成event记到binary log中。
2、slave接收master上传来的binlog，然后按顺序应用，重现master上的用户操作。
3、在配置从节点时,要输入master的log_pos,要求它从哪个pos开始同步数据库里的数据,这也是传统复制技术,
4、缺点：如果dba在重新同步时指定的master_log_file + master_log_pos 参数不对，那么就会引起数据的不一致。
四、GTID的工作原理
1、GTID复制与普通复制模式的大不同就是不需要指定二进制文件名和位置减少手工干预和降低服务故障时间。
2、有了gtid了之后对于重新同步时slave从哪里开始同步master的binlog不在由dba来管了，省心了！
3、有了gtid了之后只要对比一下master和slave上的gtid数据量就可以知道master和slave是否一致了！
4、一个事务对应一个ID，GTID=UUID:transactionId。
5、 一个GTID在一个服务器上只会执行一次
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第二部分、从库relaylog损坏或被误删
一、现象
1、show slave status\G;
Last_Error: Relay log read failure: Could not parse relay log event entry.
The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log),
the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log),
a network problem, or a bug in the master's or slave's MySQL code.
If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
2、error_log如下：
160519 18:42:21 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.009095' position 1069989357 (位置)
二、紧急处理
经初步分析是relay log问题，可进行以下处理。
1.从库执行reset slave，清除掉binlog日志
(reset slave 将使slave 忘记主从复制关系的位置信息。它删除master.info和relay-log.info 以及所有的relay log 文件并重新启用一个新的relaylog文件。
使用reset slave之前必须使用stop slave 命令将复制进程停止)。
2.从出错前的位置CHANGE MASTER TO开始启动复制
> CHANGE MASTER TO MASTER_HOST='server1',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='p4ssword',
-> MASTER_LOG_FILE='mysql-bin.009095',
-> MASTER_LOG_POS=1069989357;
3、若是基于GTID的复制协议时使用MASTER_AUTO_POSITION = 1定位，更为简单
> change master to MASTER_HOST='10.10.10.106',
MASTER_PORT=3308,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
master_auto_position=1;
三、彻底优化
开启relay_log_recovery：
当从库宕机后造成relay-log损坏，导致部分relay log没处理，则自动放弃所有未执行的relay-log，
并且重新从master上获取日志，这样就保证了relay-log的完整性。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第三部分、从库relaylog堆积
一、默认情况下，relay logs会在SQL线程执行完毕后被自动删除
1、relay-log-purge=1
# 在SQL Thread每执行完一个events时判断，如果该relay-log 已经不再需要则自动删除。
2、relay-log-space-limit=10G
# 超过10G限制IO线程暂停同步, 待SQL线程清理完relay log后，IO线程再继续同步。
3、expire-logs-days=N
# 在实例启动时 和 flush logs 时判断该值,如果文件访问时间早于设定值，则purge file
二、案例：从库回放错误导致relay log堆积
1、故障
relay log积累了好多。
2、现象
> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: No # SQL进程停止
3、原因
主从数据不一致，从库存在某条数据，主库insert该记录时，在从库执行就报错了。
4、处理
(1)处理主从不一致问题
(2)start slave
(3)sql回放完成后，堆积问题消失
三、案例：未配置expire-logs-days出现relay-log堆积
1、原先是一个Slave:导致relay-log 和 relay-log.index的存在
2、现在没有了SQL-Thread，导致relay-log-purge 没有起作用。
3、每天mysqldump --flush-logs备份，导致每天会生成一个relay-log
4、没有配置expire-logs-days:导致flush logs时，也不会做relay-log清除
建议：当slave不再使用时，通过reset slave all彻底取消relaylog及change master信息。
四、MHA场景中，滞后从库依赖其他从库的relay log故禁用relay log自动删除
1、pureg_relay_logs脚本在不阻塞SQL线程的情况下自动清理relay log。
MHA场景下,对于不断产生的relay log直接将该脚本部署到crontab以实现按天或按小时期清理。
2、定制清理relay log cronjob
$ crontab -l
# purge relay logs at 5am
0 5 * * * purge_relay_logs --user=mha --password=mha --disable_relay_log_purge >> /tmp/purge_relay_logs.log 2>&1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第四部分、从库重放错误
一、sql_thread停止运行故障分析
1、现象Slave_SQL_Running: No
mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: No # slave sql进程出错
Last_Errno: 1062
Last_Error: …Duplicate…key ‘PRIMARY’, Error_code: 1062;… # 错误代码
Master_UUID: f2b6c829-9c87-11e4-84e8-deadeb54b599
Retrieved_Gtid_Set: x-x-x-x-deadeb54b599:1-283,x-x-x-x-deadeb54b599:1-33
Executed_Gtid_Set: x-x-x-x-deadeb54b599:1-283,x-x-x-x-deadeb54b599:1-31 #下一个GTID是32
2、Slave_SQL_Running: No 常见原因
种：删除失败。在master上删除一条记录，而slave上找不到。
Last_SQL_Error: Could not execute Delete_rows event on table zcs.t1;
Can't find record in 't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 666
第二种：主键重复。在slave已经有该记录，又在master上插入了同一条记录。
Last_SQL_Error: Could not execute Write_rows event on table zcs.t1;
Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000016, end_log_pos 666
[ERROR] Slave SQL: Error 'Duplicate entry '1007-443786-0' for key 'PRIMARY'' on query.
Default database: 'xxx'.
Query: 'insert into misdata (uid,mid,pid,state,mtime) values (443786,1007,0,-1,1262598003)', Error_code: 1062
第三种：更新丢失。在master上更新一条记录，而slave上找不到，丢失了数据。
Last_SQL_Error: Could not execute Update_rows event on table zcs.t1;
Can't find record in 't1',
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000026, end_log_pos 666
二、临时方案：忽略错误后，继续同步
1、基于position的复制，忽略N个event(1event=1sql)
> SET SQL_SLAVE_SKIP_COUNTER=N;
2、基于GTID的复制，指定GTID_NEXT
> SET @@SESSION.GTID_NEXT= 'xxxx:33';BEGIN; COMMIT;SET SESSION GTID_NEXT = AUTOMATIC;
3、基于GTID的复制，忽略GTID=32
> RESET MASTER; > SET @@GLOBAL.GTID_PURGED = “xxxx:1-32”;
4、无论是否启用GTID,忽略主键错误代码
# vi my.cnf：slave-skip-errors=1007,1008,1032,1062
# pt-slave-restart -S /tmp/mysql.sock --error-numbers=1032 --user= --password=''
5、无论是否启用GTID,忽略tab1的event
# pt-slave-restart -S /tmp/mysql.sock —error-text=”test.tab1” --user= --password='x'
三、数据效验
使用pt-table-checksum或mk-table-checksum工具进行校验，
可参考网友文章 https://www.cnblogs.com/kevingrace/p/6278200.html
四、同步数据
使用pt-table-sync或mk-table-sync或手工补数。
1、主从相同(主从同步范围内的库)
$ mk-table-sync --execute --print --databases db6\
h=1.1.1.1:3306,u=user1,p=pass1,P=3306 \ （主）
h=1.1.1.2:3310,u=user1,p=pass1,P=3316 （从）
2、主从同步(主从同步范围外的库)
注意：要添加 --no-check-slave 参数
$ mk-table-sync --execute --print --no-check-slave --databases db6 \
h=1.1.1.1:3306,u=user1,p=pass1,P=3306 \ （主）
h=1.1.1.2:3310,u=user1,p=pass1,P=3316 （从）
3、反向同步
注意：这时需要关闭主的binlog日志
$ mk-table-sync --execute --print --no-bin-log --databases db8 --tables tab11 \
h=1.1.1.2:3310,u=user1,p=pass1,P=3316 (从)
h=1.1.1.1:3306,u=user1,p=pass1,P=3306（主）
五、为保证从库安全性，建议配置为只读
# read_only = on
# super_read_only = on
# tx_read_only = on
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第五部分、从库重放延迟
有了gtid了之后只要对比一下master和slave上的gtid数据量就可以知道master和slave是否一致了！
一、基础知识
1、在SLAVE上执行SHOW SLAVE STATUS\G 的结果是：
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 668711237
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 654409041 # mysql-bin.000009已重放event数量
Seconds_Behind_Master: 3296 # IOthread和SQLthread间的时间差
2、这里涉及SHOW SLAVE STATUS\G 中几个重要的日志文件和位置
(1)Master_Log_File/Read_Master_Log_Pos记录了IO thread读到的master binlog的position
(2)Relay_Log_File/Relay_Log_Pos记录了SQL thread执行到relay log的position
(3)Relay_Master_Log_File/Exec_Master_Log_Pos是SQL thread执行到master binlog的position
二、判断slave延迟
案例1～查看IOthread和SQLthread间的时间差
Seconds_behind_master
Measures the time difference in seconds between the slave SQL thread and the slave I/O thread.
案例2~SLAVE延迟恰好在同一个master log内
在Relay_Master_Log_File 和 Master_Log_File一样的情况下，
通过对比Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，计算SQL线程比IO线程慢了多少个binlog事件；
案例2~SLAVE延迟不在同一个master log内
如果Relay_Master_Log_File 和 Master_Log_File不一样，需要在master/slave打印输出进行对比
1、在MASTER上执行show master logs 的结果是：
+------------------+--------------+
| Log_name | File_size |
+------------------+--------------+
| mysql-bin.000009 | 1073742063 | 主库及binlog9 event总数
| mysql-bin.000010 | 107374193 | 主库及binlog10 event总数
+------------------+--------------+
2、对比logSLAVE实际延迟的event差值
(1)000009总事件1073742063 和SLAVE已读取到的position654409041间的差
1073742063 - 654409041 = 419333022
(2)加上主库及000010总事件107374193
(3)共107374193 + 419333022 = 526707215 个binlog event
三、使用pt-heartbeat监控主从延迟
主从的地址均为10.10.101.11 主库端口3306 从库端口3307
1、在主库上建heartbeat表，按照一定的时间频率更新该表的字段（把时间更新进去）。
# pt-heartbeat -D test --create-table -h 10.10.101.11 -P 3306 -u root -p '*******' --update --daemonize
-D:表示受监控的数据库
2、在从库上连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。
(1)#执行一次就退出
# pt-heartbeat -D test -h 10.10.101.11 -P 3307 -uroot -p'*******' --check
0.00
(2)# 持续监控：
# pt-heartbeat -D test -h 10.10.101.11 -P 3307 -uroot -p'*******' --monitor
0.00s [ 0.00s, 0.00s, 0.00s ]
四、MySQL复制延迟优化的关键点
1、硬件问题导致的延迟（比如master是SSD，但是slave还是机械硬盘）
2、网络问题导致的延迟
3、DDL题导致的延迟
(1)方案1：通过pt-osc 或者 gh-ost 来让ddl拆分成一个个小事务
(2)方案2：在slave上先ddl，然后master-slave切换，然后再old master上进行ddl
4、大事务拆小事务（监控到不符合规范的trx自动kill）
5、SQL线程导致的延迟
并行复制三个比较关键的时间结点，
(1)5.6库间并发
(2)5.7组提交
(3)8.0写集合
6、调整安全参数
slave则不需要这么高的数据安全，可以将
(1)sync_binlog=1000 # 即1000个事务组提交一次
(2)innodb_flush_log_at_trx_commit=0commit不触发写盘。速度快，mysqld进程的崩溃会丢失上一秒钟的事务。
(3)关闭binlog
7、半同步： 半同步可以让延迟为0，但是半同步有自动切换为异步复制的可能
8、IO线程导致的延迟（较少）
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第六部分、主库导致的同步故障
一、binlog被禁用。
1、执行show master status，输出结果为空
2、vi my.cnf
log-bin=mysql-bin # 启用binlog
expire_logs_days = 7 # 只保留 7 天的日志文件
二、binlog_format
1、row,更耗空间带宽,但安全（推荐）
2、statement省空间，导致主从数据不一致。
3、mixed（混合模式）
三、主库binlog丢失
1、误删binglog
reset master; # 清空所有 binlog 文件
mysql-bin.index # 文件里描述的binlog文件都被删除了。
2、只有重新搭建从库
四、主库binlog堆积
1、正确删除binlog
mysql> show slave status\G # 你首先要来 slave 上查看从库读 binlog 到了哪里
Master_Log_File: mysql-bin.000009 # 可以看到是 mysql-bin.000009
mysql> purge master logs to 'mysql-bin.000009'; # 可以在 master 中执行这条指令 , 将 binlog 文件删除至 mysql-bin.000009 前
mysql> purge master logs before '2014-11-18 00:00:00'; # 将 binlog 文件删除至此日期前
2、佳实践
删除binglog之前，请先cp到一个大的nas空间并保留足够长的时间。
在可能的情况下，可使用mysqlbinlog从历史binlog找回数据。
五、主库binlog中缺少记录
1、模拟异常(主库操作未写入binlog)
mysql8.0中sql_log_bin 的全局作用域被改成会话级作用域，也就是修改本选项只影响本会话，不再影响全局：
master> set sql_log_bin=0; # 设为0后，在Master数据库上执行的语句都不记录binlog
master> create table t05 (name VARCHAR(40));
master> set sql_log_bin=1;
master> insert into t05 values('zcs');
(要慎用set global sql_log_bin=0，这会导致所有在Master上执行的语句都不记录binlog)。
2、从库日志：
slave> SHOW SLAVE STATUS \G;
Last_Errno: 1146
Last_Error:Error 'Table'testdb.test 'doesn't exist'on query' insert into t05 values('111') '.
3、解决方案
参考主库的建表语句，在从库上手动创建此表。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第七部分、从主正常切换与主从异常切换
一、master Binlog Dump 线程常见的几种状态。
1、Sending binlog event to slave # 线程读取到一个事件并正发送到slave上
2、Finished reading one binlog; switching to next binlog # 正切换到下一个
3、Has sent all binlog to slave; waiting for binlog to be updated # 等待在master日志中产生新的事件
4、Waiting to finalize termination # 当前线程停止了，这个时间很短
5、没有 Binlog Dump 线程 # 也就是说，没有slave连接上来
二、从库Slave_IO_State
1、Connecting to master # 该线程证尝试连接到master上。
2、Requesting binlog dump # 告诉master要请求的二进制文件以及开始位置。
3、Reconnecting after a failed binlog dump request # 正尝试重连到master。
4、Waiting for master to send event # 已连到master，正等待它发送binlog。
5、Queueing master event to the relay log # 已读到事件，正把它拷贝到中继日志。
6、Waiting for the slave SQL thread to free enough relay log space
# 总和超过relay_log_space_limit，I/O线程等待SQL线程处理后释放空间。
7、Reading event from the relay log # 从中继日志里读到一个事件以备执行。
8、Has read all relay log; waiting for the slave I/O thread to update it
# 已处理完中继日志中全部事件，正等待I/O线程写入更新的事件。
三、主从正常切换
1、在master准备
(1)关闭主库对应的中间件等程序
(2)show processlist;
//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated
(3)对主库进行锁表
flush tables with read lock
2、在slave准备
(1)确保重放完毕
或者show slave status \G;查看
Slave has read all relay log; waiting for the slave I/O thread to update it
或者SHOW PROCESSLIST;查看
Slave has read all relay log; waitingfor more updates
或者show slave status查看
Slave_SQL_Running_State: Slave has read allrelay log; waiting for more updates
(2)STOP SLAVE IO_THREAD
3、提升slave为master
(1)stop slave；
(2)Reset slave all;
然后show slave status\G 的信息返回为空
(3)RESET MASTER(慎用)删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始。
(另一个命令：purge binary log不会修改记录binlog的顺序的数值)
(4)在新的master上创建同步用户
grant replication slave on *.* repl@'IP of slave' identified by 'replpwd';
4、将原来master变为slave
(1)unlock tables # 释放锁
(2)# STOP SLAVE -> RESET MASTER -> # RESET SLAVE ALL; # 在新的slave上重置binlog等
(3)连接新master
change master to MASTER_HOST='newmasterip',
MASTER_PORT=3308,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
master_auto_position=1;
(4)show slave status \G
Master_Log_File: X
Read_Master_Log_Pos: X
Relay_Master_Log_File: X
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: X
6、将中间件的连接串指向新主库的IP或VIP
(1)在原主库把vip 去掉： /sbin/ifconfig eth1:1 down ;
(2)在新主库把vip 加上： /sbin/ifconfig eth1:1 x.x.x.VIP
(3)应用为改为连接新主库的IP或VIP
四、主从异常切换（主机故障，在salve执行：）
1、stop slave；
2、reset master；
3、show variables like 'read_only';->注释read-only=1并重启mysql服务
4、查看show master status \G;
5、将从库IP地址改为主库IP地址，测试应用连接是否正常。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第八部分、主从复制搭建思路
一、新建主从库
方式一：数据落地
1、在主服建复制帐号
2、配置主库的my.cnf
3、配置从库的my.cnf
4、从主库导出数据(数据落地)
5、从库导入数据(数据落地)
6、启动基于GTID的复制
7、建表测试同步
方式二：数据不落地
1、在主库建复制帐号
2、配置主库的my.cnf
3、配置从库的my.cnf
4、启动基于GTID的复制
5、建库、建表测试同步
二、重建从库（sql线程中断后，经常临时跳过继续同步，时间长了需要重建从库，以确保主从数据更加一致）
1、从主库导出数据(数据落地)
2、从库导入数据(数据落地)
3、启动基于GTID的复制
4、建表测试同步
三、举例: 搭建基于GTID的主从复制
1、在主库建立复制帐号
create user repl@'10.10.10.%' identified by 'repl';
grant replication slave on *.* to repl@'10.10.10.%' identified by 'repl';
2、配置主库的my.cnf
log_bin =on
server_id=1001
gtid-mode=on
enforce_gtid_consistency
# 强制事务一致性，保证事务的安全
3、配置从库的my.cnf
server_id=1002
log_bin=on
gtid-mode=on
enforce-gtid-consistency
保证从库安全性，建议配置为只读
read_only = on
# super_read_only = on
# tx_read_only = on
# master-info.log记录的是接收postion
master_info_repository=TABLE
# 把relay.info记录在slave_relay_log_info表可避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错。
relay_log_info_repository=TABLE
4、初始化从库数据。
(1)从主库导出数据
mysqldump --single-transaction --master-data=2 --all-databases --set-gtid-purged=off --routines --events -uroot -p >all.sql
# master-data等于默认值1时不注释，等于2时注释。
# 添加--set-gtid-purged=off参数，避免将gtid信息导出
# 从库不需要重新执行触发器(这里不需要--triggers)
(2)从库导入数据
mysql -uroot -p < all.sql
5、启动基于GTID的复制
(1)从库通过master_auto_position=1（自动定位）告诉主库已执行的事务的GTID值
change master to MASTER_HOST='10.10.10.106',
MASTER_PORT=3308,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
master_auto_position=1;
（主库会告诉从库哪些GTID事务没有被执行，同一个事务在指定的从库执行一次）
(2)start slave;
(3)show slave status \G;
6、测试同步：
(1)在主库创建一张表，插入记录。
(2)在从库查询验证是否正确，经验证配置正确。
动力小刚于2019年4月 个人邮箱：zcs0237#163.com
