MySQL主从复制
1，介绍
依赖二进制日志的，实时备份的一个多节点架构
2，主从复制的前提
至少两个实例
不同的server_id
主库需要开启二进制日志
主库需要授权一个专用的复制用户
主库数据备份
开启专用复制线程
3，搭建主从复制
3.1准备多实例
参考：https://www.cnblogs.com/dengmeng/articles/12733446.html
3.2检查server_id
mysql -S /data/3307/mysql.sock -e "select @@server_id" mysql -S /data/3308/mysql.sock -e "select @@server_id" mysql -S /data/3309/mysql.sock -e "select @@server_id"
3.3检查主库（3307）的二进制情况
mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"
3.4连接主库，创建复制用户
mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';
3.5进行主库数据备份
mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
3.6登录从库，恢复数据（3308）
登录：
mysql -S /data/3308/mysql.sock
关闭二进制日志记录：
set sql_log_bin=0;
恢复数据：
source /tmp/full.sql;
3.7在从库填写主库信息
获取模板信息：help change master to;
CHANGE MASTER TO
MASTER_HOST='主库的IP地址',
MASTER_USER='主库的复制用户',
MASTER_PASSWORD='复制用户的密码',
MASTER_PORT=主库的端口号,
MASTER_LOG_FILE='从库起始备份的二进制文件，就是全备里面的二进制文件信息',
MASTER_LOG_POS=备份里面的位置号和二进制文件在一起,
MASTER_CONNECT_RETRY=10;断开连接之后重连次数
列如：
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=444,
MASTER_CONNECT_RETRY=10;
3.8启动专用的复制线程
start slave;
3.9如果change master to 信息输入错误，怎么解决？
1.将专用复制线程停掉 stop slave; 2.清除所有信息 reset slave all;
3.重新输入CHANGE MASTER TO信息
4.启动专用复制线程
start slave;
3.9.1查看一下主从复制的状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 444
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: f409df1d-1203-11eb-9bd6-000c29347872
Master_Info_File: /data/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
3.9.2 show slave status 字段详细介绍
1.主库的信息(master.info)
Master_Host: 10.0.0.51 主库的IP Master_User: repl 复制用户名 Master_Port: 3307 主库的端口号 Connect_Retry: 10 断链之后重试次数 Master_Log_File: mysql-bin.000001 已经获取到的binlog文件名 Read_Master_Log_Pos: 444 已经获取到的binlog位置号
2.从库的relaylog信息（relay-log.info）
Relay_Log_File: db01-relay-bin.000002从库已经运行过的relaylog文件名
Relay_Log_Pos: 320 从库已经运行过的relaylog位置点
3.从库复制线程工作状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.过滤复制相关的状态
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
5.从库延时主库的时间（单位/S）
Seconds_Behind_Master: 0
6.从库线程报错详细信息
Last_IO_Errno: 0 IO报错的号码
Last_IO_Error: IO报错的具体信息
Last_SQL_Errno: 0 SQL报错的号码
Last_SQL_Error: SQL线程报错的具体原因
7.延时从库
SQL_Delay: 0 延时从库设定的时间
SQL_Remaining_Delay: NULL 延时操作的剩余时间
8.GTID复制信息
Retrieved_Gtid_Set: 接收到的GTID的个数
Executed_Gtid_Set: 执行了GTID的个数
4，主从复制工作过程（原理）
4.1名词认识
4.1.1 文件
主库： binlog 从库： relay-log：中继日志 master.info：主库信息文件 relay-log.info：中继日志应用信息
4.1.2 线程
主库： binglog_dump_therad：二进制日志投递的线程 mysql -S /data/3307/mysql.sock -e "show processlist" 查看二进制投递线程的方法
从库：
IO_Thread：从库的IO线程，请求和接收
binlog SQL_Thread：从库的SQL线程，回放日志4.2主从复制工作过程说明：
1.从库执行CHANGE MASTER TO的语句,会立即将主库的信息记录到master.info中 2.从库执行start slave，会立即启动IO_thread和SQL_thread两个线程 3.IO_T读取Master.info文件，获取到主库的信息 4.IO_T连接主库，主库会立即分配一个dump_T,进行交互 5.IO_T根据master.info binlog信息，向dunp_T请求最新的binlog 6.主库dump_T经过查询，如果发现有新的binlog，截取并返回给从库的IO_T 7.从库的IO_T会收到binlog，存储在TCP/IP的缓存中（内存），在网络底层返回ACK 8.从库的IO_T会更新master.info信息，重置binlog位置点信息 9.从库的IO_T会将binlog，写入到relay-log中 10.从库SQL_T读取relay-log.info 文件，获取上次的位置点 11.SQL_T按照位置点往下执行relay-log日志 12.SQL_T执行完成后，重新更新relay-log.info 13.在mysql引擎层面有一个pager线程，将应用过的relay-log做一下清理
细节：主库发生了日志修改，更新二进制日志完成后，会发送一个“信号”给dump_T,dump_T会通知IO_T线程
5，主从复制监控及故障处理
5.1主从监控
5.1.1 主库：
show processlist;
5.1.2 从库：
show slave status\G
5.2 主从复制故障分析及处理
从库线程复制状态： Slave_IO_Running: Yes Slave_SQL_Running: Yes 从库线程报错详细信息： Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:
5.3 IO线程故障
（1）链接主库连接不上：Slave_IO_Running:connecting
原 因 ：网络不通 ，防火墙问题，IP，prot，user，passwd不对，skip_name_resolve，连接数上限
处理思路：msyql -u -p -h -P 手动链接测试一下
如何处理：
1.将专用复制线程停掉 stop slave; 2.清除所有信息 reset slave all; 3.重新输入CHANGE MASTER TO信息 4.启动专用复制线程 start slave;
（2）IO线程NO的状态分析：Slave_IO_Running:NO
原因一：日志名不对 解决方法：对比备份文件的master_log_file 原因二：日志损坏，日志不连续 原因三：server_ID 重复 （3）写relaylog （4）更新master.info
5.4 SQL线程故障
SQL线程主要做三件事： 读relay-log.info 读relay-log，并执行SQL 更新relay-log.info
以上文件损坏，最好是重新构建主从
如果SQL语句执行不成功，版本差异导致，主从参数不一致，改变不存在的对象，DML语句不符合表的定义及约束时
解决方法：
方法一:跳过当前的错误点
stop slave;
set global sql_slave_skip_counter=1;
方法二：跳过错误代码
/etc/my.cnf
slave-skip-errors=1032,1062,1007
方法三：推荐使用
设置从库为只读，防止写入
使用我们的中间件做成读写分离的架构
5.5主从延时原因分析
Seconds_Behind_Master: 0:从库延时主库的时间（单位/S）
5.5.1主库方面：
日志写入不及时
解决方法：双一标准之一
5.6版本以后默认是开启的，=1会将binlog立即刷写到磁盘
mysql> select @@sync_binlog; +---------------+ | @@sync_binlog | +---------------+ | 1 | +---------------+
主库并发业务较高
分布式架构
从库太多
级联主从
对于classic replication：
主库是有能力并发运行事务的，但是在dump_T在传输日志的时候，是以事件为单元传输日志的，所以导致事务的传输工作是串行方式的，这时在主库的TPS很高时，会产生比较大主从延时
怎么处理：从5.6开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的，此外除了GTID支持，还需要双一保证
5.5.2从库方面：
classic replication：
SQL线程只有一个，所以说只能串执行relay的事务
怎么解决：加线程，在5.6中出现了database级别的多线程SQL，只能针对不同库下的事务进行并发处理，在5.7版本加入MTS，才真正实现了事务级别的并发SQL
6.延时从库
作用：延时从库是为了防止数据逻辑损坏
物理损坏：磁盘出现故障，误删除了frm文件
逻辑损坏：drop库或表
对于传统的主从复制，比较擅长处理物理损坏
6.1设计理念
对SQL线程进行延时设置，一般设置的时间是3-6小时
6.2如何设置
在从库去设置延时时间
stop slave; CHANGE MASTER TO MASTER_DELAY=300; start slave;
6.3 如何使用延时从库，当主库发生故障如何使用从库代替主库，解决问题思路流程；
1.停止SQL线程，停止主库业务
stop slave sql_thread;
2.模拟SQL手工恢复relaylog到drop之前的位置点 3.截取relaylog日志，找到起点（relay-log.info）和终点（drop操作之前的位置点）
mysql> show slave status\G 这条命令使用找到relaylog的起点
mysql> show relaylog events in 'db01-relay-bin.000002' drop前位置点
4.获取截取的日志，验证数据可用性
7.过滤复制
主库（从主库方面控制的话，将以下两个参数写入到my.cnf,用的不多，简单了解）
show master status; （查看当前的一个状态）
binlog_do_db :白名单（添加白名单的库，记录binlog)
binlog_ignore_db：黑名单(添加黑名单的库，排除不记录binlog）
从库（在SQL线程回访日志时，进行控制过滤）
show slave status \G（查看从库当前黑白名单状态）
replicate_do_db: （白名单，只会复制那些库）
replicate_ignore_db:（黑名单，排除那些库不复制）
replicate_do_table: （白名单，只会复制那些表）
replicate_ignore_table:（黑名单，排除那些表不复制）
replicate_wild_do_table: (白名单，模糊匹配表）
replicate_wild_ignore_table:（黑名单，模糊匹配表）
如何配置：将对应的参数配置在my.cnf,重启一下数据就可以了
8.半同步复制
和传统复制的区别：以一个插件形式提供的功能
主库方面多了一个线程：ACK_receive
从库方面多了一个线程：ACK _send
主库的ACK_receive线程只有接收到从库发来的ACK确认，主库事务才能commit成功
从库的ack_send只有等relay_log记录到磁盘上才会发送ACK给主库
主库只会等待10s，如果ACK还没收到，就会自动替换为异步复制
浙公网安备 33010602011771号