MySQL系列:(十二)MySQL主从复制

admin 2021年01月11日 2,406次浏览

1、主从复制介绍

  • 主从复制是基于binlog来实现的
  • 主库发生新的操作,都会记录binlog
  • 从库取得主库的binlog进行回放
  • 主从复制的过程是异步操作

2、主从复制的前提

  • 需要2个或以上的数据库实例
  • 主库需要开启二进制日志
  • server_id要不同,区分不同的节点
  • 主库需要建立专用的复制用户 (replication slave)
  • 从库应该通过备份主库,然后恢复自身数据
  • 创建从库的同步信息(ip、port、user、pass、二进制日志起点)
  • 从库应该开启专门的复制线程

3、搭建主从复制环境

  • 创建多实例

  • 检查配置文件

    • 主库:二进制日志是否开启
    • 两个节点: server_id(建议主库ID号小于从库ID号)
  • 主库创建复制用户

    # 创建用户
    mysql> create user rep@'192.168.137.%' identified by '123';
    
    # 给用户授权
    mysql> grant replication slave on *.* to rep@'192.168.137.%';
    
  • 备份主库数据并导入从库

    # 主库全量备份
    root@localhost data]# mysqldump -S /data/db_1/data/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
    
    # 从库导入数据
    [root@localhost data]# /data/db_1/data/mysql.sock 
    mysql> set sql_log_bin=0;
    mysql> source /tmp/full.sql
    
  • 创建从库的同步信息

    mysql> CHANGE MASTER TO
    	MASTER_HOST='192.168.137.10',
        MASTER_USER='rep',
        MASTER_PASSWORD='123',
        MASTER_PORT=3307,
        MASTER_LOG_FILE='bin_log.000002',
        MASTER_LOG_POS=156,
        MASTER_CONNECT_RETRY=10;
    
    # 重新配置'CHANGE MASTER TO',需要先停止slave,然后重置slave,然后再重新配置
    mysql> stop slave;
    mysql> reset slave;
    
  • 从库开启复制线程(IO,SQL)

    [root@localhost data]# mysql -S /data/db_2/mysql.sock 
    mysql> start slave;
    
  • 检查主从复制状态

    # 在从库中操作
    mysql> show slave status \G
    
    # 或者在主库中执行一些操作,如果从库中有相应变化,说明主从复制搭建正常
    

4、主从复制原理

4.1、主从复制涉及的文件

  • 主库

    binlog:二进制日志

  • 从库:
    relaylog:中继(回放)日志
    master.info :主库信息文件
    relaylog.info:relaylog应用的信息

4.2、主从复制涉及的线程

  • 主库
    Binlog_Dump Thread(DUMP线程)
  • 从库:
    SLAVE_IO_THREAD(I/O线程)
    SLAVE_SQL_THREAD(SQL线程)

4.3、主从复制工作(过程)原理

  • 从库执行change master to 命令(主库的连接信息+复制的起点)

  • 从库会将以上信息,记录到master.info文件

  • 从库执行 start slave 命令,立即开启IO线程和SQL线程

  • 从库 IO线程读取master.info文件中的信息,获取到IP、PORT、User、Password、binlog的位置信息

  • 从库IO线程请求连接主库,主库专门提供一个DUMP线程,负责和IO线程交互

  • IO线程根据binlog的位置信息(bin_log.000002 , 156),请求主库新的binlog

  • 主库通过DUMP线程将最新的binlog,通过网络传送给从库的IO线程

  • IO线程接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info

  • IO线程将TCP/IP缓存中的数据转储到磁盘relaylog中

  • SQL线程读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息

  • SQL线程会按照上次的位置点回放最新的relaylog,再次更新relay.info信息

  • 从库会自动purge应用过的relaylog进行定期清理

说明:

​ 一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump线程发送信号给IO线程,该功能增强了主从复制的实时性

4.4、主从复制监控

  • 监控命令

    在从库中执行

    # 查看从库状态
    mysql> show slave status \G
    
    # 查看从库回放日志
    mysql> show relaylog events in LOG_FILE;
    
  • 输出信息解析

    # 主库有关的信息(master.info)
    Master_Host: 192.168.137.10
    Master_User: rep
    Master_Port: 3307
    Connect_Retry: 10
    Master_Log_File: bin_log.000003
    Read_Master_Log_Pos: 976
    
    # 从库relay应用信息有关的(relay.info)
    Relay_Log_File: localhost-relay-bin.000004
    Relay_Log_Pos: 1187
    Relay_Master_Log_File: bin_log.000003
    
    # 从库线程运行状态(排错)
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    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
    

4.5、主从复制故障

4.5.1、IO线程故障

  • 连接主库:connecting

    • 故障原因

      网络故障、连接信息错误或变更、防火墙、连接数达到上限

    • 排查思路:

      • 使用复制用户手工登录,查看具体错误信息
    • 解决方法

      mysql> stop slave; 
      mysql> reset slave all;
      
      # 修改“change master to”参数
      mysql> change master to 
      mysql> start slave;
      
  • 请求:Binlog

    • 故障原因

      • binlog 没开、损坏、不存在

      • 主库执行了reset master

    • 解决方法

      在从库中执行以下操作

      # 停止slave
      mysql> stop slave ;
      # 重置slave
      mysql> reset slave all;
      # 重做CHANGE MASTER TO
      mysql> CHANGE MASTER TO
      	MASTER_HOST='192.168.137.10',
          MASTER_USER='rep',
          MASTER_PASSWORD='123',
          MASTER_PORT=3307,
          MASTER_LOG_FILE='bin_log.000007',
          MASTER_LOG_POS=1633,
          MASTER_CONNECT_RETRY=10;
      # 启动slave
      mysql> start slave;
      

4.5.2、SQL线程故障

SQL线程主要是完成relaylog回放,所以处理SQL线程故障时,主要是做SQL语句执行失败原因的分析

  • 故障原因

    可能是操作失误,导致从库的数据比主库的数据新,造成数据冲突或异常

  • 解决方法

    • 一切以主库为准进行解决
    • 如果出现故障,尽量将从库进行反操作
    • 最直接、稳妥的办法,重新构建主从复制
  • 主键冲突的解决办法

    查找到主\从库之间主键冲突数据,然后将从库中的数据update一下,然后跳过这一条错误

    mysql> stop slave; 
    mysql> set global sql_slave_skip_counter = [events个数];
    mysql> start slave;
    

    可以使用pt-table-check校验两个表的差异,然后使用pt-table-sync进行同步,然后跳过错误,进行主从复制

  • 常见错误代码

    • 1007:对象已存在
    • 1032:无法执行DML
    • 1062:主键冲突,或约束冲突
  • 避免SQL线程故障

    • 从库设置只读

      在配置文件中添加以下参数

      read_only = ON
      super_read_only = ON
      
    • 使用读写分离中间件

4.6、主从延时原因及监控

4.6.1、主库方面原因与解决方法

  • binlog写入不及时

    在配置文件中加入sync_binlog=1参数

  • 默认情况下dump线程是串行方式传输binlog
    在并发事务量大或者大事务时,由于dump线程是串型工作的,导致传送日志较慢,因此必须开启GTID复制,使用Group commit方式,可以支持DUMP线程并行传输

  • 主库极其繁忙
    慢语句、锁等待、从库数量多、网络延时等

4.6.2、从库方面原因

  • 传统复制(Classic)中
    如果主库并发事务量很大,或者出现大事务,由于从库是单SQL线程,导致不管传的日志有多少,只能一次执行一个事务
  • 主从硬件差异太大
  • 主从的参数配置
  • 从库和主库的索引不一致
  • 版本有差异

4.6.3、主从延时监控

  • 主库方面原因的监控

    对比主库和从库的bin_log文件、position号是否相同

    • 主库

      mysql> show master status;
      	File: mysql-bin.000001
      	Position: 1373
      
    • 从库

      mysql> show master status;
      	Master_Log_File: mysql-bin.000001
      	Read_Master_Log_Pos: 1373
      
  • 从库方面原因监控

    • 查看从主库获取到多少事务

    • 查看从库执行了多少事务

      # 对比获取到的日志数量和回放的日志数量,以确定影响执行效率的事务;如果两个号码一致,说明没有延时
      mysql> show master status;
      	Read_Master_Log_Pos: 976
      	Exec_Master_Log_Pos: 976
      

4.7、从库同步延时

4.7.1、作用与配置

  • 作用

    设置从库同步延时,主要是为了防止操作失误或逻辑故障导致数据异常

  • 执行原理

    主要是修改SQL线程执行延时,数据已经正常写入relaylog中,只是SQL线程在指定延时时间后再执行

  • 配置

    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
    

4.7.2、使用从库同步延时,处理逻辑故障

  • 从库同步延时的恢复思路

    • 监控到数据库逻辑故障

    • 停止从库SQL线程,记录已经回放的位置点(既:截取日志起点)

      mysql> stop slave sql_thread;
      mysql> show slave status \G
      	Relay_Log_File: localhost-relay-bin.000003
          Relay_Log_Pos: 322
      
    • 截取relaylog

      # 起点: 	
      mysql>show slave status \G
      	Relay_Log_File: localhost-relay-bin.000003
          Relay_Log_Pos: 322
      
      # 终点: drop之前的位置点
      mysql> show relaylog events in 'localhost-relay-bin.000003';
      
      # 进行截取
      
    • 模拟SQL线程回放日志
      从库执行source,导入数据

    • 恢复业务

      • 只有一个库
        从库替代主库工作

      • 有多个库

        从库导出故障库,还原到主库中

4.7.3、故障演练

  • 主库操作

    mysql> create database test_db charset utf8mb4;
    mysql> use test_db;
    mysql> create table t1 (id int);
    mysql> insert into t1 values(1),(2),(3);
    mysql> commit;
    mysql> drop database test_db;
    
  • 从库操作

    • 停止从库SQL线程,获取relay的位置点(截取日志的起点)

      mysql> stop slave sql_thread;
      mysql> show slave status \G
      	Relay_Log_File: localhost-relay-bin.000014
          Relay_Log_Pos: 1016
      
    • 找到relay的截取终点

      drop事务的开始点就是上一个事务的结束点,因此我们的日志只需要截取到drop事务开始号即可

      mysql> show relaylog events in 'localhost-relay-bin.000014';
      
      	| localhost-relay-bin.000014 | 1093 | Query          |         1 |       15290 | drop database test_db /* xid=1641 */                   |
      
    • 截取relay

      [root@localhost ~]# cd /data/db_2/data/
      [root@localhost data]# mysqlbinlog --start-position=1016 --stop-position=1093 localhost-relay-bin.000014 >/tmp/relay.sql
      
    • 恢复relay到从库

      [root@localhost data]# mysql -uroot -p -S /data/db_2/data/mysql.sock 
      mysql> set sql_log_bin=0;
      mysql> source /tmp/relay.sql
      
    • 恢复主库

      # 在从库中备份主库损坏的数据库
      [root@localhost data]# mysqldump -S /data/db_2/data/mysql.sock -B test_db --master-data=2 --single-transaction -R -E --triggers >/tmp/test_db.sql
      
      # 在主库中恢复损坏数据库
      [root@localhost data]# mysql -S /data/db_1/data/mysql.sock 
      mysql> set sql_log_bin=0;
      mysql> source /tmp/test_db.sql
      mysql> set sql_log_bin=1;
      
      # 恢复数据后,查看误操作事务数量
      mysql> show binlog events in 'bin_log.000007';
      
      # 根据实际误操作事务数量,在“从库”中跳过对应事务数量
      mysql> stop slave;
      mysql> set global sql_slave_skip_counter = 1;
      mysql> start slave;
      
      # 查看所有数据同步完毕后,将“从库”中跳过事务数量为0
      mysql> stop slave;
      mysql> set global sql_slave_skip_counter = 0;
      mysql> start slave;
      
      # 数据库恢复完毕
      

4.8、过滤复制应用

  • 主库参数

    在主库配置文件中设置日志记录黑/白名单参数,选择性记录日志

    # 日志记录白名单
    binlog_do_db=[DATABASE]
    
    # 日志记录黑名单
    binlog_ignore_db=[DATABASE] 
    
  • 从库参数

    可以在从库配置文件中设置日志记录黑/白名单参数,选择性记录日志;也可以使用“CHANGE MASTER TO”设置

    # 库的黑/白名单
    replicate_do_db=[DATABASE] 
    replicate_ignore_db=[DATABASE]
    
    # 表的黑/白名单
    replicate_do_table=[TABLE]
    replicate_ignore_table=[TABLE]
    
    # 表的黑/白名单(模糊匹配)
    replicate_wild_Do_table=[TABLE]
    replicate_wild_Ignore_table=[TABLE]
    

4.9、GTID复制应用

4.9.1、核心参数

# 启用gtid模式,否则就是普通的复制架构
gtid-mode=on

# 强制GTID的一致性
enforce-gtid-consistency=true

# slave更新是否记入日志
log-slave-updates=1

4.9.2、主/从库参数配置

  • 主库配置

    主库配置与普通主从配置相同

  • 从库配置

    mysql> change master to 
    	master_host='192.168.137.10',
    	master_user='rep',
    	master_password='123' ,
    	# GTID号从1开始自动生成
    	MASTER_AUTO_POSITION=1;
    

4.9.3、GTID 复制和普通复制的区别

  • 在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便做失效转移(Failover)

  • 需添加三个额外功能参数:gtid-mode=onenforce-gtid-consistency=truelog-slave-updates=1

  • change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;

  • 在复制过程中,从库不再依赖master.info文件,而是直接读取最后一个relaylog的 GTID号

  • mysqldump备份时,默认会将备份中包含的事务操作,以以下方式 告诉从库,然后从库直接从下一个GTID开始请求binlog

    ### SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';
    

4.9.4、GTID主从复制搭建示例

# 1、数据库配置
## 主库_master_db
[root@localhost ~]# cat > /etc/my.cnf <<EOF
    [mysqld]
    basedir=/application/mysql/
    datadir=/data/mysql/data
    socket=/tmp/mysql.sock
    port=3306
    server_id=1
    secure-file-priv=/tmp
    autocommit=0
    log_bin=/data/binlog/mysql-bin
    binlog_format=row
    gtid-mode=on
    enforce-gtid-consistency=true
    log-slave-updates=1
    [mysql]
    prompt=master_db [\\d]>
    EOF

## 从库_1_slave_db01
[root@localhost ~]# cat > /etc/my.cnf <<EOF
    [mysqld]
    basedir=/application/mysql
    datadir=/data/mysql/data
    socket=/tmp/mysql.sock
    port=3306
    server_id=2
    secure-file-priv=/tmp
    autocommit=0
    log_bin=/data/binlog/mysql-bin
    binlog_format=row
    gtid-mode=on
    enforce-gtid-consistency=true
    log-slave-updates=1
    [mysql]
    prompt=slave_db01 [\\d]>
    EOF

## 从库_2_slave_db02
[root@localhost ~]# cat > /etc/my.cnf <<EOF
    [mysqld]
    basedir=/application/mysql
    datadir=/data/mysql/data
    socket=/tmp/mysql.sock
    server_id=3
    port=3306
    secure-file-priv=/tmp
    autocommit=0
    log_bin=/data/binlog/mysql-bin
    binlog_format=row
    gtid-mode=on
    enforce-gtid-consistency=true
    log-slave-updates=1
    [mysql]
    prompt=slave_db02 [\\d]>
    EOF

# 初始化数据
[root@localhost ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql  --datadir=/data/mysql/data 

# 启动数据库
[root@localhost ~]# systemctl start mysqld

# 2、构建主从
## master:192.168.137.10
## slave:192.168.137.20,192.168.137.30

## 主库
### 创建用户
mysql> create user rep@'192.168.137.%' identified by '123';
### 给用户授权
mysql> grant replication slave on *.* to rep@'192.168.137.%';

## 从库
mysql> change master to 
	master_host='192.168.137.10',
	master_user='rep',
	master_password='123',
	MASTER_AUTO_POSITION=1;
mysql> start slave;