MySQL主从复制

# 高可用介绍

企业高可用标准是根据全年无故障率(非计划内故障停机)来看的。
- 99.9% - 0.00136524*60=525.6min
- 99.99% - 0.000136524*60=52.56min
- 99.999% - 0.0000136524*60=5.256min
一般来说为了提高服务的高可用性，我们会使用一些高可用架构方案。
- 负载均衡：有一定的高可用性，如 LVS、Nginx。
- 主备系统：有高可用性、但是需要切换、是单活的架构，如：KeepAlive (KA)、MHA、MMM。
- 多活系统：就是每一个节点都可以提供服务。

# 主从复制介绍

主从复制用于将主库的数据，复制到从库中，使得我们在主库崩溃时可以快速的进行一个主从切换，来提高数据库的高可用性。
主从复制的过程是异步的，它是基于二进制日志完成，主库在发生操作提交事务时，都会记录binlog，然后从库会取得主库的binlog信息进行回放(source)。

# 主从复制的搭建过程

首先主库需要开启binlog二进制日志，另外如果主库开启了GTID配置，则从库也要启用GTID。
然后主从配置的server_id要不同，用于区分不同的节点。
主库需要建立专用的复制用户，并给予replication slave权限(只能分配给所有库)。
- 例如：grant replication slave on *.* to slave@'10.0.0.%' identified by 'qwe123123';
从库需要先通过主库备份恢复，同步大部分的主库数据。因为binlog可能由于定时清理会不完整，并且binlog同步大量数据时效率较慢。

然后从库需要配置一些主库信息，通过help change master to可以查看语句说明。

CHANGE MASTER TO
  # 主库主机 (IP/主机名等)
  MASTER_HOST='192.168.10.100',
  # 主库中的供从库使用的用户名
  MASTER_USER='slave',
  # 主库中的供从库使用的用户密码
  MASTER_PASSWORD='qwe123123',
  # 主库端口号
  MASTER_PORT=3306,

  # (日志文件和起点，根据刚刚备份文件中的位置信息来决定)
  # 复制用的起点binlog日志文件
  MASTER_LOG_FILE='mysql-bin.000004',
  # 复制用的binlog日志起点位置号
  MASTER_LOG_POS=197,

  # 网络断开后 尝试重连次数
  MASTER_CONNECT_RETRY=10,

  # 从服务器获取公钥, 8.0版本开始默认使用caching_sha2_password作为密码认证方式, 需要获取公钥才能正常连接
  GET_MASTER_PUBLIC_KEY=1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

然后从库开启自动复制用的复制线程即可：start slave;

检查主从复制状态：show slave status\G;

# 主要看这两个参数都是Yes就说明主从复制正常启动了，如果不是Yes就需要检查下主库和从库的日志。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1
2
3

# 主从复制相关语句

# 查看"从库配置的主库信息"命令
- help change master to
# 清空"从库配置的主库信息"命令
- reset slave all;
# 从库启动复制
- start slave;
# 从库关闭复制
- stop slave;
# 查看从库状态
- show slave status\G

# 主从复制原理

# 主从复制涉及的文件

# 主库
- binlog - 日志文件
# 从库
- relaylog - 中继日志
- master.info - 主库信息文件
- relay-log.info - relaylog应用的信息

# 主从复制涉及的线程

# 主库
- Binlog_Dump Thread : DUMP_T
# 从库
- SLAVE_IO_THREAD : IO_T
- SLAVE_SQL_THREAD : SQL_T

# 主从复制原理 (流程)

从库通过执行change mater to命令，告诉从库主库的连接信息和复制的起点，然后从库会将该信息记录到master.info文件中。
从库执行start slave命令后，会立即开启IO_T和SQL_T线程。
从库IO_T线程读取master.info文件中的信息，获得主库的ip、port、pass、binlog位置等信息。
从库IO_T通过TCP/IP请求连接主库。
主库专门提供一个DUMP_T线程，负责与从库IO_T建立连接，进行交互。
从库IO_T根据起点binlog位置信息，请求主库的新binlog。
主库检查有没有新的binlog信息，如果有则返回给从库，没有则等待新的binlog。
主库一旦有新的binlog日志变化，就会立即通过DUMP_T线程发送信号给IO_T，然后IO_T就会立即请求主库的新binlog。
主库通过DUMP_T线程将新的binlog通过连接给从库IO_T。
IO_T收到后，立即更新master.info中的binlog起点位置信息，更新为接收到的binlog日志最新的位置号。
IO_T将缓冲中的数据，写入到磁盘relaylog文件中。
SQL_T线程读取relay-log.info中的信息，获取上次已经执行过的relaylog的位置信息，另外如果是第一次则是从头开始执行。
SQL_T会按照上次执行位置点信息，只执行新的relaylog日志(可以减少线程压力)，然后再次更新relay-log.info信息。
从库会自动清理已经执行过的relaylog日志。

# 主从复制状态监控

查看主从复制的命令是：show slave status \G;
# 主库相关信息
- Master_Host: 10.0.0.71 - 主库主机地址
- Master_User: slave - 主库用户名
- Master_Port: 3306 - 主库端口
- Connect_Retry: 10 - 连接重试次数
- Master_Log_File: mysql-bin.000010 - 获得到的主库最新binlog日志文件。
- Read_Master_Log_Pos: 2816 - 获得到的主库最新binlog日志位置号。
# 从库relay应用相关信息(relay-log.info)
- Relay_Log_File: node01-relay-bin.000006 - 当前relay日志文件
- Relay_Log_Pos: 1700 - 当前relay日志位置
- Relay_Master_Log_File: mysql-bin.000010 - 当前回放的主库binlog文件
# 从库复制线程运行状态
- Slave_IO_Running: Yes - 从库IO线程状态
- Slave_SQL_Running: Yes - 从库SQL线程状态
# 主从复制故障排错相关
- Last_IO_Errno: 0 - IO线程最后错误状态码
- Last_IO_Error: - IO线程最后错误信息
- Last_SQL_Errno: 0 - IO线程最后错误状态码
- Last_SQL_Error: - IO线程最后错误信息
# 过滤复制有关的信息
- 可以过滤复制某些数据库，只复制某些数据库。
- 以Replicate_开头的基本都是过滤相关的。
# 从库复制延时时间(s)
- Seconds_Behind_Master: 0 - 可用于判断从库延时
# 从库延时复制
- SQL_Delay: 0 - 0表示实时复制，设置延时复制可以防止从库立马执行导致主库数据损坏的操作。
- SQL_Remaining_Delay: NULL
# GTID复制相关信息
- Retrieved_Gtid_Set: 235ccc97-bc74-11eb-bd48-000c29cc18cd:7-14
- Executed_Gtid_Set: 235ccc97-bc74-11eb-bd48-000c29cc18cd:1-14
- Auto_Position: 0

# 主从复制相关故障

# IO线程故障

连接主库问题(connecting error)
- 可能是网络不通、防火墙阻挡、连接数上限导致的。
- 还有就是连接主库信息错误也导致连接不上，我们可以使用复制用户，手动登录主库试试，如果有问题则可以执行reset slave all后重新通过change master to配置。
请求binlog问题
- 主库没启动binlog日志、主库binlog日志文件不存在、主库binlog日志断节不完整。
存储binlog到relaylog问题
- relaylog文件权限不足，修改好relaylog文件权限后，重新启动主从复制即可。

# SQL线程故障

对relaylog进行回放失败
- 也就是SQL语句执行失败，可能是主库与从库复制前数据不完全一致导致的执行失败。
- 例如：从库进行了操作，比如建表，然后主库又建了一个一样的表，这样主库的建表命令同步过来后，从库执行就会报错，因为已经有这张表了，然后SQL线程就会故障。
- 解决方法是一切以主库为准进行解决，如果出现问题，尽量进行反操作，将从库做过的事，反操作一下，例如将从库上建的库进行删除，然后重启从库的主从复制即可。还有最直接稳妥的方法就是重新构建主从。
- 另外尽量不要操作从库，从库只用于复制和读数据即可，不要用于写数据。
- 设置从库只读相关参数：read_only、super_read_only

# 主从延时监控及原因

主从延时实际就是主库做的事从库迟迟不执行。
# 主库方面可能的原因
- 主库极其繁忙，例如查询数据比较大、慢语句、锁等待、从库数量过多、网络延时等。
- 主库提交的事务，binlog写入不及时，迟迟没有写入到磁盘，可能是没有使用双一标准。
- 主库数据并发量太大，默认情况下DUMP_T是串行传输binlog日志的，所以在mysql并发事务量大时，会导致传送日志较慢，造成主从延时。同时在大事务传输时，串行的方式会阻塞后面的小事务。
  - 解决方法是开启GTID、双一标准，然后使用Group Commit方式，实现DUMP_T并行，组提交的方式，可以并行批量发送事务。
# 从库方面可能的原因
- 主从硬件差异太大、主从的参数配置不同。
- 主从的索引不一致，有些企业中主库只做修改，从库只做查询但是其实尽量主从一致比较好。
- 版本有差异，一般很少见，可能在迁移时会使用。
# 主从延时的监控
- # 监控命令：show slave status \G;
  - 通过主从延时时间Seconds_Behind_Master : 0判断有没有延迟，这个延迟时间不够准确，我们要找到谁导致的延时。
- # 判断是否是主库导致的延时
  - 我们可以通过Master_Log_File : mysql-bin.000001和Read_Master_Log_Pos : 56来判断，看接收到的binlog，是否与主库的最新binlog差距很大。如果有该问题，可以开启双一、开启GTID。
- # 判断是否是从库导致的延时
  - 查看接收了多少
```
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 194
```
    1
    2
  - 查看执行了多少
```
Exec_Master_Log_Pos: 194
Relay_Log_Space: 820
```
    1
    2
  - 如果Exec_Master_Log_Pos比Read_Master_Log_Pos小，则说明从库发生延时卡了。
  - 然后查看对应卡住的语句，通过导致从库卡住的位置号，查看主库binlog文件中对应的语句，然后分析该语句慢的原因。

#数据库 #MySQL #存储引擎 #主从复制

← MySQL数据备份 ELK介绍和ES部署→

MySQL主从复制

# 高可用介绍

# 主从复制介绍

# 主从复制的搭建过程

# 主从复制相关语句

# 查看"从库配置的主库信息"命令

# 清空"从库配置的主库信息"命令

# 从库启动复制

# 从库关闭复制

# 查看从库状态

# 主从复制原理

# 主从复制涉及的文件

# 主库

# 从库

# 主从复制涉及的线程

# 主库

# 从库

# 主从复制原理 (流程)

# 主从复制状态监控

# 主库相关信息

# 从库relay应用相关信息(relay-log.info)

# 从库复制线程运行状态

# 主从复制故障排错相关

# 过滤复制有关的信息

# 从库复制延时时间(s)

# 从库延时复制

# GTID复制相关信息

# 主从复制相关故障

# IO线程故障

# SQL线程故障

# 主从延时监控及原因

# 主库方面可能的原因

# 从库方面可能的原因

# 主从延时的监控

# 监控命令：show slave status \G;

# 判断是否是主库导致的延时

# 判断是否是从库导致的延时

# 监控命令：`show slave status \G;`