day09-02-主从原理及故障

5.主从复制原理 *****

5.1 主从复制中涉及的文件

主库：binlog

从库：

relaylog 中继日志

master.info 主库信息文件

relaylog.info relalog应用的信息

5.2 主从复制中涉及的线程

主库：

binlog_dump thread：DUMP_T

从库：

SLAVE_IO_THREAD: IO_T

SLAVE_SQL_THRED:SQL_T

5.3 主从复制工作原理 ********

![image-20211029175110989](D:\data\Sync-data\simon\学习笔记\Mysql 笔记\MySql-DBA\MySQL\day09-02-主从原理及故障.assets\image-20211029175110989.png)

1.从库执行change master to 命令（主库的链接信息+复制的起点）

2.从库会将以上信息记录到master.info

3.从库执行start slave，从库立即开启IO_T,SQL_T

4.从库IO_T，读取master.info文件中的信息，获取到IP，PORT，user，pass，binlog的位置信息。

5.从库IO_T链接主库，主库专门提供有一个DUMP_T线程，负责和IO_T进行交互。

6.IO_T请求根据binlog位置点信息（mysql-bin.000004.445),请求主库新的binlog

7.主库通过DUMP_T将最新的binlog网络传输至从库的IO_T

8.IO_T接收到新的binlog日志，存储到TCP/IP缓存，立即返回ACK给主库

9.IO_T将TCP/IP缓存转储到磁盘relaylog，并更新master.info（binlog信息）

10.SQL_T读取relay.info的信息，获取到上次已经应用过的relaylog的位置信息。

11.SQL_T会按照上次的位置点回放最新的relaylog，再次更新relay.info信息

小细节：

12.从库会根据（参数relay_log_purge=ON）自动定期删除应用过的relay-log

  1. 主库DUMP线程实时监控主库的binlog变化，如果有新变化，发信号通知从库，从库就再一次进行请求

补充说明：
主库一旦构建成功，主库当中发生新的变化，都会通过DUMP_T发送信号给IO_T，增强主从复制的实时性

5.4 主从复制监控****

命令：

show slave status \G

[root@mysql-node01 data]# mysql -uroot -p -S /data/3309/mysql.sock -e "show slave status\G"
Enter password: 
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
\#主库有关的信息，来自master.info
Master_Host: 10.0.50.61
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
----
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1039
----

\#从库relaylog应用信息有关的（relay.info）
Relay_Log_File: mysql-node01-relay-bin.000002
Relay_Log_Pos: 914
Relay_Master_Log_File: mysql-bin.000004

\#  从库线程运行状态(排错)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
\# 从库同步线程状态不是"YES",还需要看这4个信息 + 从库的error-log
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 

\# 过滤复制有关的信息：
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table:

\# 从库延时主库的时间（秒） 
Seconds_Behind_Master: 0

\# 延时从库（防止误操作）
SQL_Delay: 0
SQL_Remaining_Delay: NULL

\# GTID复制有关的状态信息
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

[root@mysql-node01 data]#

5.5 主从复制故障*****

slave从库故障：

5.5.1 IO_Thread 线程故障：

1）链接主库 Connecting

网络，链接信息错误或变更了，防火墙，连接数上限

排查思路：

1.使用复制用户手工登陆

解决：

1.stop slave
2.reset slave all;
3.change master to ....
4.start slave

2）请求binlog

binlog 没开

binlog 损坏，不存在

reset master

（如果master做了reset master操作，会直接导致主从关系断裂，需要在slave节点按照master最新的binlog文件，重新执行

stop slave; 
CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
start slave;

重新构建主从关系）

3）存储binlog到relaylog

relaylog 不能写入，也会报IO故障，检查relaylog权限

5.5.2 SQL_Thread 线程故障

SQL_Thread 作用就是在slave节点重新执行IO_Thread请求到的binlog信息

realy-log损坏

回放relay-log（执行SQL语句）

研究一条SQL语句为什么会执行失败？

insert delete update ---> t1 表不存在

create table db_name ---> db 已存在

约束冲突（主键，唯一键，非空....）

合理处理方法：

把握一个原则，一切以主库为准进行解决。

如果出现问题，在slave节点，找出故障语句，并进行反操作。

最直接稳妥办法，重新构建主从

官方给出处理方法(以从库为核心的处理方案)：

方法一：（生产，看具体情况细节，前提是确认主从信息（库、表、表结构、字符集、数据，已完全一致）
stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：（生产不推荐）
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.

对于主键冲突问题：

Error 'Duplicate entry '20663' for key 'PRIMARY'' on query

先校验 主从 之间 重复主键数据行的信息，然后以master为准，把slave 节点上主键对应的数据行进行update，然后再跳过这个报错，再start slave

第三方对比表差异工具：

pt-table-checker 对比差异

pt-table-sync 同步

故障演示：

slave 新建db：

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)

mysql> 
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db_test            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

master节点也建立db：

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3308 |
+--------+
1 row in set (0.00 sec)

mysql> create database db_test charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db_test            |
| mysql              |
| performance_schema |
| sys                | 
+--------------------+
6 rows in set (0.00 sec)

mysql>
mysql> use db_test
Database changed
mysql> 
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql>

从库查看db_test.t1

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)

mysql> use db_test
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

从库检查 同步状态：

mysql> show slave status\G
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
......
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'db_test'; database exists' on query. Default database: 'db_test'. Query: 'create database db_test charset utf8mb4'
......

可以看到，slave节点SQL_Thread报错，提示不能创建db_test，查询db_test已存在。

在主从关系中，slave一切数据信息以master为准。

解决：

1）停止slave

2）删除已经创建的db_test

3）重新开启主从

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> drop database db_test;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show slave status \G
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......
mysql>

为了很大程度避免SQL线程故障

1）从库只读

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| read_only             | OFF   |
| super_read_only       | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

mysql>

2）使用读写分离中间件

atlas

mycat

ProxySQL

MaxScale

5.6 主从延时监控及原因*****

5.6.1 主库方面原因

1）binlog写入不及时

sync_binlog=1 #有可能这个参数影响的，1为最优。

2）默认情况下，DUMP_T是串行传输binlog

DUMP_T 是按照事件的方式，传输二进制日志到slave节点IO_T线程

在并发事务量大时，或者大事务，由于dump_t是串行工作的，导致传送binlog较慢。

如何解决？

使用并行传输，前提：

必须使用GTID，使用group commit方式，可以支持dump_t并行

mysql> show variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+
2 rows in set (0.01 sec)

mysql>

必须开启双“一”参数

3）主库极其繁忙

满语句，锁等待，从库个数比较多，网络延时

5.6.2 从库方面原因

1）传统复制（Classic）中，

如果主库并发事务量很大，或者出现大事务

由于是单SQL线程，导致不管传的日志有多少，只能一次执行一个事务。

5.6 版本，有了GTID，可以实现多SQL线程，但只能基于不同库的事务进行并发回放。

# 从库的并发工作线程参数

mysql> show variables like '%worker%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql>

5.7 版本中，有了增强GTID，在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave)

1） 主从硬件差异太大

2） 主从参数配置不一样

3） 从库和主库索引不一样

4） 主从版本有差异

主从延时的监控

Seconds_Behind_Master: 0

主库方面原因的监控：

主库：

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 1391
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql>

从库：

mysql> show slave status\G
*************************** 1. row ***************************
......
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1391
......
mysql>

对比主从读取主库binlog进度，看是否在同一position进行判断。

从库方面原因：

查看从库拿了多少日志：

Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1391

执行了多少日志：

Relay_Log_File: mysql-node01-relay-bin.000003
Relay_Log_Pos: 320

binlog 和 relay-log 对应关系查看：

# 最准确，最直观：
[root@mysql-node01 data]# cat relay-log.info 
7
./mysql-node01-relay-bin.000003
320
mysql-bin.000004
1391
0
0
1
## mysql-node01-relay-bin.000003 ： 320 
## 对应 mysql-bin.000004 ：1391

[root@mysql-node01 data]#


#第二种方法：

mysql> show slave status\G
......
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1391
......
          Exec_Master_Log_Pos: 1391
              Relay_Log_Space: 1646
......
mysql> 

# Read_Master_Log_Pos 表示slave从master读取binlog日志的位置。
# Exec_Master_Log_Pos 表示slave执行binlog日志的位置。
# Read_Master_Log_Pos 与 Exec_Master_Log_Pos 相等，则证明从库无延迟。
# Exec_Master_Log_Pos 小于 Read_Master_Log_Pos 较多，说明slave的SQL执行线程繁忙，有阻塞，压力大。
# Exec_Master_Log_Pos 卡在哪个位置，就通过查看relaylog内容，定位对应的事件以及语句。

# 可以查看relay-log具体内容
mysql> show relaylog events in 'mysql-node01-relay-bin.000003';
+-------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name                      | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+-------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-node01-relay-bin.000003 |   4 | Format_desc    |      3309 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-node01-relay-bin.000003 | 123 | Previous_gtids |      3309 |         154 |                                       |
| mysql-node01-relay-bin.000003 | 154 | Rotate         |      3308 |           0 | mysql-bin.000004;pos=1391             |
| mysql-node01-relay-bin.000003 | 201 | Format_desc    |      3308 |           0 | Server ver: 5.7.26-log, Binlog ver: 4 |
+-------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)

mysql>

MySQL延迟追平

stop slave;

set global innodb_flush_log_at_trx_commit=2;
set global sync_binlog=0;
set global slave_parallel_type=LOGICAL_CLOCK;
set global binlog_transaction_dependency_tracking=COMMIT_ORDER;
set global slave_parallel_workers=8;
set global slave_preserve_commit_order=0;
start slave;

# 待追平以后
stop slave;

set global innodb_flush_log_at_trx_commit=1;
set global sync_binlog=1;
start slave;

如果库经常有延迟，写入较大

主库和从库都要做以下设置

stop slave;

set global binlog_transaction_dependency_tracking=COMMIT_ORDER;
set global binlog_group_commit_sync_delay=0;
set global binlog_group_comit_sync_no_delay_count=0;
set global slave_parallel_type=LOGICAL_CLOCK;
set global transaction_write_set_extraction=XXHASH64;
set global binlog_transaction_dependency_tracking=WRITESET;
set global slave_parallel_workers=8;
set global slave_preserve_commit_order=0;

start slave;


# 配置文件添加
binlog_group_commit_sync_delay=0
binlog_group_commit_sync_no_delay_count=0
slave_parallel_type=logical_clock
transaction_write_set_extraction=XXHASH64
binlog_transaction_dependency_tracking=WRITESET
slave_parallel_workers=8	-----注意节点CPU核数
SLAVE_PRESERVE_COMMIT_ORDER=0


#
posted @   oldSimon  阅读(2)  评论(0)  编辑  收藏  举报
相关博文：
·  day10-01-延时从库及过滤复制
·  day10-02-GTID复制-半同步
·  主从复制，延时从库，半同步，过滤复制，GTID复制
·  mysql5.7主从复制
·  原理+案例，关于主从延迟，一篇文章给你讲明白！
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~
点击右上角即可分享
微信分享提示
SQL AI 助手