MySQL主从复制原理与搭建实践

2024年 2月 20日 64.7k 0

导读

本文将从 MySQL 主从复制的应用目的和场景出发,探讨其实际意义及必要性。之后,介绍 MySQL 主从复制的实现原理及其各个复制模式。最后,通过 Docker 容器化的方式搭建一主一从的 MySQL 主从复制架构。

应用目的及场景

MySQL 主从复制有以下应用目的及场景:

  • 提高系统的可用性:当主库服务不可用时,可切换到从库服务,保证可用性,从库服务依然可以提供数据读取和部分数据写入。
  • 实现数据备份和灾难恢复:在数据库服务正常运行过程中,持续地保持主从库数据同步,实现数据冗余备份;当主库服务产生错误操作、发生数据污染或遭到灾难事件后,可通过从库服务恢复数据,减少数据损失、降低服务中断风险。
  • 分担主服务器负载:将读取数据的请求分发到从库服务器,以减轻主服务器的负载压力,保证系统整体性能,提高读取吞吐量。

总的来讲,MySQL主从复制适用于需要读写分离、数据冗余备份和灾难恢复的情况;然而主从复制并不能提供强一致性保证,因为复制延迟和异步复制的特性可能导致主从之间的数据差异。

主从复制原理

以 MySQL 一主一从架构为例,分别有两个节点,主库服务所在节点为 Master 节点,从库服务所在的节点为 Slave 节点。在此之下,主库负责写入数据,从库负责读取数据。

  • 主库服务(Master):当在主库服务执行数据变更操作时(增、删、改),将操作命令记录到binlog二进制文件中。
  • 从库服务(Slave):从库服务向主库服务发起请求来复制变更的数据,主库服务通过一个binglog dump线程将binlog文件数据发送到从库服务;从库服务通过一个 I/O 线程将接收到的数据写入relaylog二进制文件,最后从库服务将relaylog的数据重放(Replay)完成数据同步。
  • 主从复制模式

    MySQL 主从复制有以下模式:

    • 全同步复制
    • 异步复制
    • 半同步复制
    • 增强半同步复制

    全同步复制

    当主库服务完成一次事务时,要求所有从库服务也完成此次事务再响应客户端。这样保证了数据强一致性但也降低了性能。

    异步复制

    异步复制是 MySQL 的默认策略。
    当主库服务提交一次事务时,通知binlog dump线程将binglog数据发送到从库服务,之后立即响应客户端。也就是说,主库服务不再关心从库服务是否完成执行事务。这会因此造成短暂的数据不一致,当主库已变更了数据,但读取从库数据时依然是旧数据。
    极端情况下,在主库提交完事务,未来得及发送binglog数据时就宕机,此时切换从库为主库服务,就会造成数据丢失。

    半同步复制

    半同步复制就是异步复制与同步复制的折中选择。
    当主库服务提交一次事务后,需要等待从库服务已写入到relaylog,此时才响应客户端。
    同样地,半同步复制也存在一些缺陷:

  • 半同步复制相对异步复制会稍微降低性能。
  • 主库等待从库的响应可以设置超时,如果超时,半同步复制就变为异步复制。
  • MySQL 5.7.2 之前存在幻读现象,在之后版本采用增强半同步复制解决。
  • 增强半同步复制

    增强半同步复制模式是 MySQL 5.7.2 版本后对半同步复制做的改进,主要解决幻读现象。
    主库配置参数rpl_semi_sync_master_wait_point=AFTER_SYNC后,主库服务存储引擎在提交事务前,需要等待从库服务已写入到relaylog之后才提交事务,此时才响应客户端。

    配置主从复制

    部署主库

    使用 Docker 快速部署一个 MySQL 主库--name=mysql-master

    docker run -d 
      -p 3306:3306 
      -e MYSQL_ROOT_PASSWORD=123456 
      --name=mysql-master 
      mysql/mysql-server:8.0
    

    配置主库

    修改配置文件/etc/my.cnf[mysqld]下的配置项。
    binlog_format的三种格式:

    • STATEMENT,记录SQL语句,从库服务会执行与主库相同的语句来复制数据更改。
    • ROW,记录实际行级别的更改,可以更准确地复制数据更改。
    • MIXED,根据执行的 SQL 来区分对待记录的日志形式,也就是在 STATEMENT 和 ROW 之间选择一种。
    [mysqld]
    # 开启二进制日志,并指定文件前缀
    log_bin=mysql_binlog
    # 设置日志格式
    binlog_format=ROW
    # 日志过期时间
    expire_logs_days=7
    # 单个日志文件最大容量,超过后创建新的日志文件
    max_binlog_size=100M
    # 设置不要复制的数据库
    # binlog_ignore_db=mysql
    # binlog_ignore_db=information_schema
    # 设置需要复制的数据库
    binlog_do_db=lingren_boot
    # 主服务器唯一ID
    server_id=1
    

    重启主库 MySQL:

    docker restart mysql-master
    

    查看主库状态

    重启 MySQL,之后可查看主库的二进制文件及数据位置;
    FilePosition构成file:position用于从库的复制起始位置。

    mysql> show master status;
    +---------------------+----------+--------------+------------------+-------------------+
    | File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------------+----------+--------------+------------------+-------------------+
    | mysql_binlog.000001 |      156 | lingren_boot |                  |                   |
    +---------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    

    创建复制权限账号

    创建一个用于数据复制的权限账号,以便之后从库在连接主库服务时使用。

    create user 'slave'@'%' identified by '123456';
    ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    grant replication slave on *.* to 'slave'@'%';
    
    flush privileges;
    

    部署从库

    使用 Docker 部署一个 MySQL 从库--name=mysql-slave

    docker run -d 
      -p 3307:3306 
      -e MYSQL_ROOT_PASSWORD=123456 
      --name=mysql-slave 
      mysql/mysql-server:8.0
    

    修改配置文件/etc/my.cnf[mysqld]下的配置项。

    [mysqld]
    # 开启bin-log日志(为了主从切换时使用,不开启bin-log的从机只能当备库使用)
    # log-bin=mysql-bin-log
    # 设置需要复制的数据库
    # binlog_do_db=lingren_boot
    # 指定bin-log日志的格式为混合模式
    # binlog_format=row
    # 设置单个binlog日志文件的最大容量
    # max_binlog_size=100M
    
    #启用中继日志
    relay_log=mysql_relaylog
    # 开启存储过程、函数、触发器等内容的同步功能
    log_bin_trust_function_creators=true
    # 同步执行跳过一些错误码(防止同步写入时出现错误导致复制中断)
    slave_skip_errors=1062
    #从服务器唯一ID
    server_id=2
    

    重启从库 MySQL:

    docker restart mysql-slave
    

    配置从库

    查看主库服务 IP,以便之后从库连接主库服务时使用:

    docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master
    
    172.17.0.2
    

    在从库中执行以下 SQL,连接到主库:

    • master_host:MySQL主库服务的主机名或IP地址,这里指定为mysql-master容器的IP
    • master_port:MySQL主库服务的端口号,这里指定为mysql-master容器的端口
    • master_user:用于同步数据的用户名
    • master_password:用于同步数据的用户密码
    • master_log_file:指定从哪个日志文件开始复制数据,即上文中提到的File字段的值
    • master_log_pos:从哪个位置开始读,即上文中提到的Position字段的值
    • master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒
    change master to
    master_host='172.17.0.2',
    master_port=3306,
    master_user='slave',
    master_password='123456',
    master_log_file='mysql_binlog.000001',
    master_log_pos=156,
    master_connect_retry=60;
    

    启动从库模式,执行以下 SQL:

    start slave;
    

    查看从库状态

    查看从库状态,执行以下 SQL:

    show slave status G;
    

    Slave_IO_RunningSlave_SQL_Running均为Yes即完成配置。

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    

    关闭主从复制

    关闭从库模式:

    stop slave;
    

    关闭从库模式及重置主库链接配置:

    stop slave;
    reset master;
    

    手动同步数据库

    如果主从复制配置是在生产中途进行的(也就是已存在数据库、表以及数据),那么需要先将主数据库中的数据手动同步到从库中,否则会造成错误。

    GTID复制

    在前面配置从库时,我们需要先知道从库的File:Position来确定同步的起始位置,然而这种会存在一定问题;当主节点不可用时,会使从节点成为主节点,如果有多个从节点,那么此时File:Position就会成为一个不定值,因为各个从节点的同步进度可能是不一样的,这时就需要人为介入,手动修改。
    MySQL5.6开始引入了一种GTID复制的技术,专门用于处理从库寻点的问题。
    首先停止从库模式:

    stop slave;
    

    停止主从库服务:

    docker stop mysql-master;
    docker stop mysql-slave;
    

    修改主从库两个节点/etc/my.cnf文件,增加如下内容:

    # 开启GTID复制
    gtid_mode=on
    # 跳过一些可能导致执行出错的SQL语句
    enforce-gtid-consistency=true
    

    重启主从库服务:

    docker start mysql-master;
    docker start mysql-slave;
    

    在从库中执行以下 SQL,连接到主库:

    • master_auto_postion:自动寻找同步点
    change master to
    master_host='172.17.0.2',
    master_port=3306,
    master_user='slave',
    master_password='123456',
    master_auto_position=1,
    master_connect_retry=60;
    

    开启从库模式:

    start slave;
    

    参考

    [1] MySQL主从复制原理和使用
    [2] MySQL | 利用 Docker 快速搭建主从复制

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论