MySQL主从复制

1 典型主从架构

• 一主多从

​ 特点：读写分离，应用于读取压力大的场景，将要求实时性不高的请求分发到多个从库，降低主库的压力

​ 缺陷：存在主从延时问题

​ 如何解决：优化网络环境，MySQL5.5~5.6使用半同步复制，MySQL5.7使用增强半同步复制

• 多级复制

  解决了主库I/O和网络压力大的问题，多级复制可以减小主库的压力，主库只需要向另一个主库发送binlog日志

• 双主

  适用主从切换的场景，通过双主复制架构避免了重复搭建从库的麻烦

• ​ 主库Master1和Master互为主从，所有Web Client的写请求都访问主库Master1或Master2

  • 多源复制

    多源(Multi-Source)复制架构适用于复杂的业务需求，既可以支撑OLTP(联机事务处理),也可以满足OLAP(联机分析处理)

  2 主从复制原理

  mysql支持两种复制方式，一是基于行的复制，二是基于语句的复制通过主库记录的二进制日志并在从库进行异步复制，可能会产出延时

• 3 主从复制搭建

  3.1 异步复制

  • 逻辑上

    MySQL默认的复制操作是异步的，主库在客户端提交的事务会立即返回结果给客户端，不关心从库是否已经被接收并处理，若主库crash，从库也无法收到提交的事务，强行切换导致数据不完整

  • 技术上

    主库将事务写入binlog后通知dump线程发送到从库，主库继续处理其他事务，不能保证binlog完全送达所有从库

  • 环境搭建

    1）相关主库与从库的配置文件

  • [mysqld]          # master
    datadir=/data
    socket=/data/mysql.sock
    log-bin=master-bin
    sync-binlog=1
    server-id=100
    #----------------------------------
    [mysqld]       # slave
    datadir=/data
    socket=/data/mysql.sock
    relay-log=slave-bin
    server-id=111
    #------------------------------------

    2）开启master与slave的实例

  • #关闭mysql实例
    mysqladmin -uroot -h127.1  -P3306  -p'123456' shutdown;
    #开启mysql数据库实例
    mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
    #查看进程
    ps -ef |grep mysqld

    3）创建复制专用用户

  • mysql> create user 'repl'@'192.169.43.%' identified by '123456';
    #create user 'repl'@'%' identified by '123456';
    mysql> grant REPLICATION SLAVE on *.* to 'repl'@'192.169.43.%';
    mysql> flush privileges;

    4）导出master数据

  • #--master-data值为2会注释change master，值为1或者没有提供值时，这些语句是直接激活的。同时，--master-data会锁定所有表(如果同时使用了--single-transaction，则不是锁所有表
    mysqldump -uroot -p --all-databases --master-data=2 > master.sql

    5）从master.sql中获取到binlog的坐标

  • grep -i -m 1 'change master to' master.sql

    6）在从库执行导出的master.sql

  • mysql -uroot -p -h 127.1 -e 'source master.sql'

    7）从库连接master

  • mysql> change master to 
            master_host='192.168.43.42',
            master_port=3306,
            master_user='repl',
            master_password='Password',
            master_log_file='master-bin.000002',
            master_log_pos=771;

    8）启动IO线程和SQL线程

  • #一次性启动
    start slave;
    #分开执行
    start slave io_thread;
    start slave sql_thread;

    9）在从库查看同步信息

  • mysql> show slave status\G
    Master_Log_File：IO线程正在读取的master binlog
    Read_Master_Log_Pos：IO线程已经读取到master binlog的哪个位置
    Relay_Log_File：SQL线程正在读取和执行的relay log
    Relay_Log_Pos：SQL线程已经读取和执行到relay log的哪个位置
    Relay_Master_Log_File：SQL线程最近执行的操作对应的是哪个master binlog
    Exec_Master_Log_Pos：SQL线程最近执行的操作对应的是master binlog的哪个位置

    10）reset master与reset slave

    reset slave会删除master.info/relay-log.info和relay log，然后新生成一个relay log。但是change master to设置的连接参数还在内存中保留着，所以此时可以直接start slave，并根据内存中的change master to连接参数复制日志。

    reset slave all除了删除reset slave删除的东西，还删除内存中的change master to设置的连接信息

    reset master会删除master上所有的二进制日志，并新建一个日志。在正常运行的主从复制环境中，执行reset master很可能导致异常状况。所以建议使用purge来删除某个时间点之前的日志(应该保证只删除那些已经复制完成的日志)，生产环境慎用

    3.2 全同步复制

    也称之组复制MGR

    • 逻辑上

      主库执行完事务，会等待所有事务分发给从库并执行完才返回客户端，因此会带来性能影响

    • 技术上

      主库提交事务后，所有的从库必须收到并提交事务，主库线程收到返回才继续工作，缺点是主库完成事务是时间变长，性能降低

    • 额外的，全同步是主从同步的增强。

      因为主从同步虽可以实现一主多从，但它的局限在于只有在主数据库上写的时候从数据库才会做数据备份，而在从数据库做出改变时，主数据库不会记录相应的改变。

      然而，全同步出现了，它可以是只要在一个数据库做出改变，所以其它在同组的数据库也会改变，同组的数据库没有等级之分。可以理解为“同组数据库之间数据相等”

    • Master节点设置

      1）配置Master节点my.cnf配置文件

      [mysqld]
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      symbolic-links=0
      log-error=/var/log/mysqld.log
      pid-file=/var/run/mysqld/mysqld.pid
      log-bin=slave-bin
      binlog_checksum=NONE
      sync-binlog=1
      #此处需要作出区分
      server-id=101
      gtid_mode=on
      enforce-gtid-consistency=1
      # 启用SQL线程回放之后将二进制写入自身的binlog中，在组复制中，依赖于每个成员持久化的binlog来实现一些数据自动平衡>的特性
      log_slave_updates=ON
      # 启用ROW格式复制，增强数据一致性
      binlog_format=ROW
      # 启用双TABLE，使用InnoDB引擎表来保存IO和SQL线程的位置信息（复制元数据），以增强复制状态的安全性
      master_info_repository=TABLE
      relay_log_info_repository=TABLE
      plugin_load_add='group_replication.so'
      transaction_write_set_extraction=XXHASH64
      #组内统一，自行设定值
      loose-group_replication_group_name="eba794f9-cfb3-11ec-9b91-000c29058c90"
      loose-group_replication_start_on_boot=off
      #本机ip，此处各节点需单独配置
      loose-group_replication_local_address= "192.168.43.43:33061"
      #组内ip
      loose-group_replication_group_seeds="192.168.43.42:33061,192.168.43.43:33061"
      loose-group_replication_bootstrap_group=off
      #白名单
      loose-group_replication_ip_whitelist="127.0.0.1,192.168.43.0/24"
      loose-group_replication_enforce_update_everywhere_checks=ON
      loose-group_replication_single_primary_mode=OFF

      2）新建复制用户

    • #创建用户，需暂时关闭同步，避免同步问题
      SET SQL_LOG_BIN=0;
      CREATE USER repl@'%' IDENTIFIED BY '123456';
      GRANT REPLICATION SLAVE ON *.* TO repl@'%';
      FLUSH PRIVILEGES;
      SET SQL_LOG_BIN=1;

      #设置同步Master
      CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

      3）配置hosts

    • #vi /etc/hosts 按实际情况填写 ip 主机名
      192.168.43.43 master
      192.168.43.42 slave

      4）启动组

    • mysql> SET GLOBAL group_replication_bootstrap_group=ON;
      mysql> START GROUP_REPLICATION;
      mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

    • 注意：引导只能由单个服务器完成，即启动组的服务器并且只执行一次。这就是为什么group_replication_bootstrap_group选项的值没有存储在实例的选项文件中的原因。如果它保存在选项文件中，则在重新启动服务器时会自动引导第二个具有相同名称的组。这将导致两个不同的组具有相同的名称

      5）查看组内成员

    • mysql> select * from performance_schema.replication_group_members;

      +---------------------------+------------------+------+------+-------+
      | CHANNEL_NAME              | MEMBER_ID        | HOST | PORT | STATE |
      +---------------------------+------------------+------+------+-------+
      | group_replication_applier | 688532-0c296515c |master| 3306 | ONLINE|
      +---------------------------+------------------+------+------+-------+

      Slave节点设置

      1）配置Master节点my.cnf配置文件

    • [mysqld]
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      symbolic-links=0
      log-error=/var/log/mysqld.log
      pid-file=/var/run/mysqld/mysqld.pid
      log-bin=slave-bin
      binlog_checksum=NONE
      sync-binlog=1
      #此处需要作出区分
      server-id=100
      gtid_mode=on
      enforce-gtid-consistency=1
      # 启用SQL线程回放之后将二进制写入自身的binlog中，在组复制中，依赖于每个成员持久化的binlog来实现一些数据自动平衡>的特性
      log_slave_updates=ON
      # 启用ROW格式复制，增强数据一致性
      binlog_format=ROW
      # 启用双TABLE，使用InnoDB引擎表来保存IO和SQL线程的位置信息（复制元数据），以增强复制状态的安全性
      master_info_repository=TABLE
      relay_log_info_repository=TABLE
      plugin_load_add='group_replication.so'
      transaction_write_set_extraction=XXHASH64
      #组内统一，自行设定值
      loose-group_replication_group_name="eba794f9-cfb3-11ec-9b91-000c29058c90"
      loose-group_replication_start_on_boot=off
      #本机ip，此处各节点需单独配置
      loose-group_replication_local_address= "192.168.43.42:33061"
      #组内ip
      loose-group_replication_group_seeds="192.168.43.42:33061,192.168.43.43:33061"
      loose-group_replication_bootstrap_group=off
      #白名单
      loose-group_replication_ip_whitelist="127.0.0.1,192.168.43.0/24"
      loose-group_replication_enforce_update_everywhere_checks=ON
      loose-group_replication_single_primary_mode=OFF

      2）新建复制用户

    • #创建用户，需暂时关闭同步，避免同步问题
      SET SQL_LOG_BIN=0;
      CREATE USER repl@'%' IDENTIFIED BY '123456';
      GRANT REPLICATION SLAVE ON *.* TO repl@'%';
      FLUSH PRIVILEGES;
      SET SQL_LOG_BIN=1;

      #设置同步Master
      CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

      3）配置Slave服务器hosts

    • #vi /etc/hosts 按实际情况填写 ip 主机名
      192.168.43.43 master
      192.168.43.42 slave