数据库第十四章：主从复制
全年无故障率(非计划内故障停机)
99.9% 0.001 365 24 _60 525.6Min
99.99% 0.0001_365 24 60 52.56Min
99.999% 0.00001 365 24*60 5.256Min
- 高可用架构方案
（1）负载均衡: 具备一定的高可用特性
LVS、Nginx、haproxy
（2）主备系统: 具有单线路上可扩展性
Keepalived、MHA、MMM
（3）真正高可用（多活系统）:
NDB Cluster、Oracle RAC、Sybase cluster,
InnoDB Cluster（MGR）、PXC（percona）、MGC（ mariadb）
MySQL Replication(主从复制)
1.职责
（1）构建主从复制架构
（2）对主从复制的工作原理有深入理解
（3）掌握并能熟练执行主从复制系统的故障排查与解决
（4）识别并处理系统出现延迟或同步不及时的情况
（5）针对过滤复制和延时同步架构进行详细配置
（6）随着技术发展与应用需求的变化，在读写分离、高可用性和分布式架构等方面进行持续优化
介绍主从复制的方式
主从复制的前提（实现主从复制功能）包括以下几点：
-
需要有两个及以上的数据库实例
-
主库必须启用二进制日志
-
每个主节点需分配独特的server_id以区分彼此
-
必须为副本管理创建专用的副本管理用户（replication slave）
-
备份主库数据后通过恢复过程实现历史数据的同步
-
必须向副本库指定具体的连接信息（IP地址、端口号、用户名、密码以及二进制日志起始位置）
-
副本库需启动特定的同步复制品线程以保持一致性
基于两台服务器的配置实现主从复制生产流程
mkdir -p /data/binlog
chown -R mysql.mysql /data
systemctl restart mysqld
Slave的IP地址设置为192.168.8.20
Vim被配置用于编辑/etc/my.cnf文件
User字段指定数据库用户的名称为mysql
基础目录路径设在/usr/local.mysql
数据目录位置定在/usr/local.mysql.data
数据库连接到socket文件夹中的/tmp.mysql.sock
服务器ID设定为数值2
数据二进制文件存储于/data/binlog_slave-bin目录下
数据端口设置为3306
数据库连接到相同的socket文件夹中的/tmp.mysql.sock
提示符切换到slaves模式，并附上>符号
运行save和exit命令以保存更改并退出程序
mkdir -p /data/binlog
chown -R mysql.mysql /data
systemctl restart mysqld
4.3 主库创建复制用户
grant replication slave on . to repl@‘192.168.8.%’ identified by ‘123’;
操作：生成历史数据副本
从:
mysql> set sql_log_bin=0;
mysql> source /root/full.sql
mysql> set sql_log_bin=1;
4.5 告知从库详细信息。首先需要做的是获取主数据库的日志文件名称及其位置编号。可以通过执行以下SQL命令来实现：show master status;
通过执行从库命令连接到主库，并配置必要的参数如下所示：
4.6 从库开启复制线程(IO,SQL)
start slave;
4.7 在从库检查主从复制状态
show slave status \G
显示信息：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.8 测试主从同步
主库:
创建数据库ms；
切换到数据库ms；
创建表t1包含字段id（整型）和name（长度不超过20的字符型）；
向表t1中插入以下几行数据：(1,'z3'),(2,'l4'),(3,'w5')；
从库:
show databases;
use ms;
select * from t1;
- 主从复制原理 *****
5.1 主从复制涉及的相关文件
主库为Binlog_Dump Thread；从库包括SLAVE_IO_THREAD和IO_T；此外还有SQL数据库复制线程包括SLAVE_SQL_THREAD及SQL_T
5.3 主从复制工作原理
- 从库发出执行change master to命令（包含主库连接信息及复制起始点）
- 从库将上述信息记录至master.info文件中
- 从库随后立即启动IO_T和SQL_T服务
- 从库中的IO_T接收 master.info 文件中的数据，并获取IP地址 PORT 用户名 Password以及 binlog文件的位置信息
- IO_T随后请求主库建立一个DUMP_T通道以便与之交互
- IO_T根据binlog的位置信息（例如mysql-bin.000004位置）请求主库获取最新的binlog文件
- 主库里通过DUMP_T通道将最新的binlog数据传输至IO_T所在的网络端口TP上
- IO_T接收到新的binlog日志后将其存储至TCP/IP缓存中并立即返回ACK确认给主库同时更新master.info文件
9(IOContinuation) IO-T将TCP/IP缓存中的数据转储至本地磁盘上的relaylog文件中
10(SQL Continuation) SQL-T读取relay.log文件中的数据并获取上次已应用过的relaylog位置信息
11(SQL Continuation) SQL-T按照上次记录的位置回放最新的relaylog内容并更新 relay.info 文件
12(补充说明)一旦主从复制系统建立完成后 在主库里发生的任何变化都会通过dump-T向IO-T发送通知 并增强了系统的实时响应能力。
5.4 主从复制监控
命令:
show slave status \G
核心数据库相关信息：主机IP地址为192.168.8.10；用户名为repl；运行端口号设置为3306；连接重试策略设定为最多十次尝试。
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 608
该库与相关应用的信息通过 relay.info 连接。 Log File: mysql-relay-version号 Master File: mysql-version号
系统库线程运行情况（排查）
过滤复制有关的信息：
- 是否复制数据库Do？
- 是否忽略数据库Db？
- 是否复制表Do？
- 是否忽略表Db？
- 是否复制野表Do？
- 是否忽略野表Db？
从库延时主库的时间(秒):
Seconds_Behind_Master: 0
延时从库（延时误操作）:
SQL_Delay: 0
SQL_Remaining_Delay: NULL
该系统通过复制GTID来获取相关信息
Retrieval_Gtid_Set:
Execution_Gtid_Set:
当前自动位置设置为0
5.5 主从复制故障 *****
5.5.1 IO 线程故障
(1) 连接主库: connecting
网络,连接信息错误或变更了,防火墙,连接数上限
排查思路:
- 使用复制用户手工登录
测试是否用户名、密码、IP出错。
解决:
从库
- 暂停同步操作以避免潜在的数据不一致问题。
- 清除所有从 master 到 target 的关联信息；
- 切换到 master 状态并重新连接至 master 服务。
- 恢复或启动从 master 到 target 的同步连接。
(2) 请求Binlog
binlog 没开
binlog 损坏,不存在
解决：
主库开启binlog
最终解决方案：
通过主库重置 master 处理：
从库
停止从库上的从机节点；
重置所有目标节点；
更改主库设置为：
MASTER_HOST=192.168.8.10,
MASTER_USER=repl,
MASTER_PASSWORD=123,
MASTER_PORT=3306,
MASTER_LOG_FILE=master-bin.000001,
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
启动到主库的到机节点；
(3) 存储binlog到relaylog
5.5.2 SQL线程故障
relay-log发生故障
尝试回放 relaylog数据
调查为何某条SQL语句无法执行？
插入、删除和更新操作针对表t1无效
创建表t1时发现该表已存在
约束冲突问题可能涉及主键、唯一键或非空值限制
合理的处理方案：始终遵循这一原则，并且全部按照主库标准执行。在出现异常情况时，请尽量采取反向操作。选择最直接稳定的方案来解决问题。
暴力的解决方法
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:该对象已存在
1032:无法对数据进行修改操作,可能该对象不存在
1062:主键冲突,或伴随约束冲突
然而，在某些情况下这类操作可能会带来风险 最佳做法是建立主副关系 为了确保稳定运行 我们必须坚持一条原则 即始终将主库放在首位
为了预防SQL线程故障的发生，
(1) 应采用单线程读取模式
single_thread_mode
super_single_thread_mode
(2) 可配置代理型SQL缓存中间件
proxy_sql_cache_interpolator
mycat_cache_interpolator
MaxScale_cache_interpolator
5.6 主从延时监控及原因 *****
5.6.1 主库方面原因
(1) binlog写入不及时
sync_binlog=1
(2) 在高并发事务场景下或处理大事务时,由于dump_t采用了串行模式,导致日志传输速度较慢。
如何解决这一问题？必须引入GTID,并采用Group commit的方式.该方法能够支持DUMP_T的同时实现并行.
(3) 主库运行异常频繁出现慢查询现象锁等待现象严重影响系统性能.
建议排查主库负载情况,统计分析每个并发操作的数量,及时调整数据库配置参数以优化系统性能.
5.6.2 从库方面原因
（1）传统复制（Classic）中存在一些问题
如果主库并发事务量很大或者出现大规模并发操作，在传统的复制模式下（Classic），由于从库是一个单SQL线程模式，在日志数据量有限的情况下只能一次性处理一个事务操作
5.6版引入了GTID机制后，在保持原有功能的基础上实现了多SQL线程模式（database），但这种模式仅适用于不同数据库环境下的并发回放操作
在5.7版中进一步优化了GTID机制增加了seq_no字段引入了新型逻辑时钟模式(logical_clock)以及MTS技术以支持多SQL线程并发执行
（2）主从硬件组件存在明显差异
（3）主从两端的参数设置有所不同
（4）从库与主库的索引设计不完全一致
（5）不同数据库版本之间存在功能上的差异
5.6.3 主从延时的监控
show slave status\G
Seconds_Behind_Master: 0
主库方面原因的监控
主库:
mysql> show master status ;
File: mysql-bin.000001
Position: 1373
从库
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1373
从库方面原因监控:
记录了多少次:
MasterLogFile: mysql-bin.1
RecordMasterLogFilePosition: 691,688
执行了多少次:
RelayLogFile: db-DB-DB-DB-4
RelayLogFilePosition: 69,万位,千位,百位,十位,个位均为...（此处可能有误）
ExecuteMasterLogFilePosition: 691,千位为...
ExecuteMainLogSpaceUsage: 69,万位,...（此处可能有误）