MySQL系列：（十二）MySQL主从复制

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=on，enforce-gtid-consistency=true，log-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;