18 MySQL主从复制延时优化及监控故障处理

# 1.优化主从延时方法

```sql
核心:主从延时核心-从库IO不够或者环境有问题
- 增大从库IO
- 节约从库IO
- 保证主从网络和环境稳定

1.增大从库buffer_ppol,缓存更多数据减少转换的IO压力
2.增大innodb_log_file_size和innodb_log_files_in_group，减少buffer pool的刷盘IO 提高写入性能
3.修改参数 flush为O_DIRECT,提高写入性能
4.关掉从库Binlog(非备份节点)
5.innodb_flush_log_at_trx_commit设置为0或2
6.sync_binlog设置为0或很大数
7.优化表(删除冗余索引)
8.从库预热数据
9.升级从库硬件
10.relay_log_info_repository和master_info_repository为TABLE减少直接IO的磁盘压力
11.使用5.7版本的多线程复制
```

# 2.主从复制监控及故障处理

## 2.1.监控

- 主库

```sql
show processlist;
#查看dump线程
mysql> show processlist;
#查看已经连接从节点
mysql> show slave hosts;
```

- 从库

```sql
mysql> show slave status\G
1.主库信息
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.21.188.37
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60

2.从库relay-log回放信息
                Relay_Log_File: db-tdptest-3-p4yr-ptest-relay-bin.000004
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000002
          Exec_Master_Log_Pos: 770

3.线程监控信息(是否有过滤复制和错误)
             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
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

4.落后主库的描述(重要参数)
        Seconds_Behind_Master: 0

5.点位参数
              Master_Log_File: mysql-bin.000002
		          Read_Master_Log_Pos: 770
	            Retrieved_Gtid_Set: 
	            Executed_Gtid_Set: 196c4617-3fa0-11ec-9cce-fa202013137b:1-1181,
c07ba4fb-5562-11ec-8640-fa2020131854:1,
ddd2aab7-24ae-11ec-8406-fa202013137b:1-72978,
e7e868e0-3faf-11ec-b6e6-fa202013137b:1-3
                Auto_Position: 1

```

- 故障类别

```sql
Slave_IO_Running: Yes # IO线程工作状态： YES、NO、Connecting
Slave_SQL_Running: Yes # SQL线程工作状态：YES、NO
Last_IO_Errno: 0 # IO故障代码：
Last_IO_Error: # IO线程报错详细信息
Last_SQL_Errno: 0 # SQL故障代码：
Last_SQL_Error: # IO线程报错详细信息
```

## 2.2.故障

- 主库与DUMP线程

```sql
1.连接主库失败
- 排查用户名,密码（最常见）
- 防火墙（最常见）
- 连接数（少见）
- 主库实例状态等（是否宕机了少见）
- server_id一致(常见)

2.binlog日志损坏或者被删除
- 备份恢复从节点,重构主从
```

- IO线程

```sql
- 网络原因居多
```

- SQL线程

```sql
1.realy-log 损坏。
- 重构从库
- 找出位置点修复(不推荐)

2.无法先执行SQL
- 主从配置:版本 参数 SQL_MODE
- 从库被修改过(数据 账号 库 表的增多或者减少)
	- 从库一定是read only
- 双主架构(删除用户 a-->b b无法在删除报错)
- 字符集问题
- 从库中断过久,binlog丢失
- 从库启动存储过程

3.事务跳过(少量且业务可以容忍的情况下,推荐用工具构建主从)
- GTID
- POS
```