binlog mysql 的主从复制的方式 mysql 5.7 主从复制

转载

AI领域布道师 2023-07-04 13:42:59

文章标签 数据库 SQL mysql 主从复制 文章分类 MySQL 数据库

1、主从复制简介

2、搭建主从复制流程

3、主从复制原理

3.1、主从复制过程涉及的文件

3.2、主从复制涉及的线程及原理

4、主从异常分析

4.1、IO线程异常

经典问题现象与解决

4.2、SQL线程异常

4.2.1、SQL功能

4.2.2、SQL线程问题及分析

一劳永逸的方法:

4.3、主从延时大

4.4、从库问题

5、主从复制高级进阶

5.1、延时从库

5.1.1、延时从库配置

5.1.2、延时从库应用（故障恢复思路）

主库逻辑损害恢复案例

6、从库过滤复制

1、主从复制简介

1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4. 主从复制核心功能:
辅助备份,处理物理损坏                   
扩展新型的架构:高可用,高性能,分布式架构等

2、搭建主从复制流程

1、两个数据库实例、server_id、server_uuid
2、主库开启二进制日志
3、主库建立专用复制用户（replication slave）
4、主库全备（用于恢复备库）
5、change、master to、（用于指定主库的ip、port、user、password、复制起点（file、position）在全备中可以找到）
6、start slave 、开启复制线程（主库：binlog_dump_T,从库：IO_T、SQL_T）

开始搭建操作：

1、主库开启二进制库和server_id
    cat /etc/my.cnf
    [mysqld]
    server_id=1
    log-bin=/var/log/mysql/mysql-bin
2、主库建立专业复制用户（进入主数据库） 
    mysql> grant replication slave on *.* to master@'192.168.0.%' identified by '123456';
3、主库全备
    mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >./full.sql
    找到全备结束的点
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=691237;
4、从库恢复（从库操作主库全备）
    mysql -uroot -p < full.sql
5、change master to （从库操作）
    mysql> help change master to; （查看帮助）
    CHANGE MASTER TO
      MASTER_HOST='source2.example.com',
      MASTER_USER='replication',
      MASTER_PASSWORD='password',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='source2-bin.001',
      MASTER_LOG_POS=4,
      MASTER_CONNECT_RETRY=10;

      修改帮助语句：
     CHANGE MASTER TO
      MASTER_HOST='192.168.0.2',
      MASTER_USER='master',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='mysql-bin.000025',
      MASTER_LOG_POS=691237,
      MASTER_CONNECT_RETRY=10; 
6、开启从库复制进程
    mysql> start slave;

主从复制的状态：

1、状态查看命令：
    show slave status \G
1.1、主库相关信息：
    Master_Host: 192.168.0.2         （主库IP）
    Master_User: master              （主库赋予的用户）
    Master_Port: 3306                （主库端口）  
    Connect_Retry: 10                （网络不好时尝试链接主库次数）
    Master_Log_File: mysql-bin.000025 （复制的到二进制文件） 
    Read_Master_Log_Pos: 691532        （复制到的节点）    
1.2、从库的中继日志的状态
    Relay_Log_File: localhost-relay-bin.000002  
    Relay_Log_Pos: 615
1.3、从库复制的线程状态：
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
     
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
1.4、过滤复制有关状态
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
1.5、主从延时相关状态（非人为）
    Seconds_Behind_Master: 0
1.6、延时从库相关状态（人为）
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
1.7、GTID复制相关状态：
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0

3、主从复制原理

3.1、主从复制过程涉及的文件

库主：
    二进制日志文件：（binlog）
从库：
    中继日志文件（relaylog）
    master.info                 ===》主库信息记录日志
    relay-log.info              ===> 记录中继应用情况信息

3.2、主从复制涉及的线程及原理

主:
DUMP THREAD
从:
IO  THREAD
SQL THREAD

binlog mysql 的主从复制的方式 mysql 5.7 主从复制_数据库

原理描述

1.change master to 时，ip pot user password binlog position写入到master.info进行记录
2. start slave 时，从库会启动IO线程和SQL线程
3.IO_T，读取master.info信息，获取主库信息连接主库
4. 主库会生成一个准备binlog DUMP线程，来响应从库
5. IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6. DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
7. IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10.SQL_T回放完成之后，会更新relay-log.info文件。
11. relay-log会有自动清理的功能。
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

4、主从异常分析

4.1、IO线程异常

可能性：
1.主库ip连错
2.主库port连错
3.链接主库的user，password错误

上面是初建时可能出现的问题

4.防火墙，网络问题 
5.主库或从库tcp链接上线
6.主库没启动

经典问题现象与解决

binlog mysql 的主从复制的方式 mysql 5.7 主从复制_数据库_02

1、主库二进制文件缺失或损坏

现象：（上图）
解决：主要在从库中操作（重新搭建主从复制）
    1) mysql>stop slave;  (停slave)
    2) mysql>reset slave all;   (重置从库信息)
    3）mysql>show slave status \G  (查看)
    4) mysql> CHANGE MASTER TO
                MASTER_HOST='192.168.0.2',
                MASTER_USER='master',
                MASTER_PASSWORD='123456',
                MASTER_PORT=3306,
                MASTER_LOG_FILE='mysql-bin.000025',  (主库新的二进制文件名)
                MASTER_LOG_POS=691237,       （新的二进制节点show master status;可在主库看）
                MASTER_CONNECT_RETRY=10;
    
     5）开启从库复制进程
        mysql> start slave;

4.2、SQL线程异常

4.2.1、SQL功能

(1)读写relay-log.info 
(2)relay-log损坏,断节,找不到
(3)接收到的SQL无法执行

4.2.2、SQL线程问题及分析

1. 版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
2.要创建的数据库对象,已经存在
3.要删除或修改的对象不存在  
4.DML语句不符合表定义及约束时.  
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'


解决：最好重建主从（其他方法有风险）

一劳永逸的方法:

(1) 可以设置从库只读.
    mysql>show variables like '%read_only%';
注意：
只会影响到普通用户，对管理员用户无效。
(2)加中间件
读写分离。

4.3、主从延时大

外在因素：
    网络 
    主从硬件差异较大
    版本差异
    参数因素

主库问题：
    1）二进制文件写入不及时 （ select @@sync_binlog;）
    2）CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)
        主库并发事务量大,主库可以并行,传送时是串行
        主库发生了大事务,由于是串行传送,会产生阻塞后续的事务
    
解决方案:
    1）5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
    2）5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
    3）大事务拆成多个小事务,可以有效的减少主从延时.

4.4、从库问题

SQL线程导致的主从延时
在CR复制情况下: 从库默认情况下只有一个SQL,只能串行回放事务SQL
1. 主库如果并发事务量较大,从库只能串行回放
2. 主库发生了大事务,会阻塞后续的所有的事务的运行

解决方案:
1. 5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
2. 5.7 版本开始GTID之后,在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
3. 大事务拆成多个小事务,可以有效的减少主从延时

5、主从复制高级进阶

5.1、延时从库

5.1.1、延时从库配置

SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间（从库配置）

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;  （秒的）
mysql>start slave;
查看：
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

5.1.2、延时从库应用（故障恢复思路）

1主1从,从库延时5分钟,主库误删除1个库
1. 5分钟之内 侦测到误删除操作
2. 停从库SQL线程
3. 截取relaylog
起点 :停止SQL线程时,relay最后应用位置
终点:误删除之前的position(GTID)
4. 恢复截取的日志到从库
5. 从库身份解除,替代主库工作

主库逻辑损害恢复案例

1.主库数据操作
    mysql>create database relay charset utf8;
    mysql>use relay
    mysql>create table t1 (id int);
    mysql>insert into t1 values(1);
    mysql>drop database relay;           (误操作)
2. 停止从库SQL线程
    mysql>stop slave sql_thread;
3. 找relaylog的截取起点和终点
起点:
    Relay_Log_File: db01-relay-bin.000002
    Relay_Log_Pos: 482
终点:
    show relaylog events in 'db01-relay-bin.000002'
    | db01-relay-bin.000002 | 1046 | Xid            |         7 |        2489 | COMMIT /* xid=144 */                  |
    | db01-relay-bin.000002 | 1077 | Anonymous_Gtid |         7 |        2554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    mysqlbinlog --start-position=482 --stop-position=1077  /data/3308/data/db01-relay-bin.000002>/tmp/relay.sql
4.从库恢复relaylog
    mysql>source /tmp/relay.sql
5.可以从库变主库、可以备份库写到主库中（都需要重新搭建主从节点）

6、从库过滤复制

主库也可以配置过滤但不建议；

从库查看和配置：

查看：
show slave status\G
Replicate_Do_DB: 
Replicate_Ignore_DB: 

配置在my.cnf
白名单配置：
    riplicate_do_db=world
    riplicate_do_db=sk
黑名单配置：
    replicate_ignore_db=mydql
    replicate_ignore_db=sk

重启从数据库

7、高级架构演变（需要第三方软件）

高可用架构：

单活：keepalived+2主从、MHA
多活：NDB（收费），InnoDB Cluster，PXC，MGC

高性能架构：

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：mysql json函数 mysql json_contains

下一篇：axios 请求2次 axios请求过程

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

binlog mysql 的主从复制 的方式 mysql 5.7 主从复制

binlog mysql 的主从复制 的方式 mysql 5.7 主从复制

1、主从复制简介

2、搭建主从复制流程

3、主从复制原理

3.1、主从复制过程涉及的文件

3.2、主从复制涉及的线程及原理

4、主从异常分析

4.1、IO线程异常

经典问题现象与解决

4.2、SQL线程异常

4.2.1、SQL功能

4.2.2、SQL线程问题及分析

一劳永逸的方法:

4.3、主从延时大

4.4、从库问题

5、主从复制高级进阶

5.1、延时从库

5.1.1、延时从库配置

5.1.2、 延时从库应用（故障恢复思路）

主库逻辑损害恢复案例

6、从库过滤复制

7、高级架构演变（需要第三方软件）

51CTO博客

binlog mysql 的主从复制的方式 mysql 5.7 主从复制

binlog mysql 的主从复制的方式 mysql 5.7 主从复制

5.1.2、延时从库应用（故障恢复思路）