MySQL主从复制

一、概述

1.简介

实时的将一个节点的binlog,传输到另一个结点并快速恢复。

2.作用

  • 辅助备份
  • 架构演变

二、主从复制部署

1.主从复制准备环境

  • 至少2个及以上的MySQL实例,并定义主从角色(server_id)
  • 主库开启二进制日志
  • 主库需要开启专门的复制用户
  • 从库提前进行数据补偿
  • 告知从库其复制主库的相关信息(ip,port,user,password,复制的起点[change master to])
  • 从库启动专用线程

2.主从复制搭建过程

step1 准备多个数据库节点
多实例搭建流程
详见第三章第五节

[root@db01 ~]# systemctl start mysqld3308.service 
[root@db01 ~]# ps -ef |grep mysqld
mysql     37560      1  1 11:24 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
mysql     37594      1  1 11:24 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
root      37626  36582  0 11:25 pts/3    00:00:00 grep --color=auto mysqld

step2 主库创建专用复制用户

mysql -u用户 -p密码 -S socket文件 -e "grant replication slave on *.* to 用户@'白名单' identified by '密码'"
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "grant replication slave on *.*  to repl@'10.0.0.%' identified by '456';"
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "select user,host from mysql.user;"
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

step3 主库开启二进制日志

[root@db01 ~]# vim /data/mysql/3307/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
server_id=7
port=3307
log_error=/data/mysql/3307/data/mysql.log
log_bin=/data/mysql/3307/mysql-bin
binlog_format=row
[root@db01 ~]# systemctl restart  mysqld3307

step4 备份主库数据,恢复到从库

[root@db01 ~]# mysqldump -S /data/mysql/3307/mysql.sock -A -R -E --triggers --master-data=2 --single-transaction >/tmp/master_DB_bak.sql
[root@db01 ~]# head -22 /tmp/master_DB_bak.sql |tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;
[root@db01 ~]# mysql -S /data/mysql/3307/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock </tmp/master_DB_bak.sql 
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

step5 告知从库主库相关信息

change master to
CHANGE MASTER TO
MASTER_HOST='主库地址',
MASTER_USER='主从复制用户',
MASTER_PASSWORD='用户登录密码',
MASTER_PORT=端口号,
MASTER_LOG_FILE='所请求的二进制文件',
MASTER_LOG_POS=起始位置,
MASTER_CONNECT_RETRY=失败重试次数;
mysql> change master to
    ->     MASTER_HOST='10.0.0.151',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='456',
    ->     MASTER_PORT=3307,
    ->     MASTER_LOG_FILE='mysql-bin.000005',
    ->     MASTER_LOG_POS=154,
    ->     MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

step6 启动专用线程

start slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

step7 验证

show slave status
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock -e "show slave status\G" |grep 'Running'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

三、主从复制原理

1.相关文件

主库

  • binlog(二进制日志)

从库

  • relaylog(中继日志):保存主库同步到从库的二进制日志文件
[root@db01 ~]# mysqlbinlog /data/mysql/3308/data/db01-relay-bin.000001 |head -5
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190902 12:51:15 server id 8  end_log_pos 123 CRC32 0x36697afc  Start: binlog v 4, server v 5.7.26-log created 190902 12:51:15 at startup
  • Master.info:保存主库相关信息
[root@db01 ~]# head /data/mysql/3308/data/master.info
25
mysql-bin.000005
154
10.0.0.151
repl
456
3307
10
0
  • relaylog.info:保存relaylog的相关信息(记录relaylog的回放过的位置点)
[root@db01 ~]# cat /data/mysql/3308/data/relay-log.info 
7
./db01-relay-bin.000002
320
mysql-bin.000005
154
0
0
1

2.相关线程

主库

  • binlog dump thread(二进制日志投递线程)
show processlist #查看数据库现有进程
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  7 | system user |           | NULL | Connect |  568 | Waiting for master to send event                       | NULL             |
|  8 | system user |           | NULL | Connect |  568 | Slave has read all relay log; waiting for more updates | NULL             |
| 12 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

从库

  • I/O线程
  • SQL进程

3.工作原理

step1
从库执行change master to时,将主库的IP PORT USER PASSWORD BINLOG POSITION记录到master info文件中;
step2
从库执行start slaves时,会启动自身的slave_IO线程和slave_SQL线程
step3
slave_IO线程读取master info信息,获取主库信息并连接主库
step4
主库启动BINLOG DUMP线程,相应从库请求
step5
slave_IO线程根据master.info记录的BINLOG文件名与POSITION号,向主库DUMP线程请求最新日志
step6
DUMP线程检查主库的binlog日志,如果存在更新,将日志发送给从库的I/O线程
step7
slave_IO线程将收到的日志保存于了TCP/IP 缓存,并立即返回TCP ACK报文给主库;此时主库工作完成
step8
slave_IO线程将缓存中的数据,存储到relay-log日志文件,更新master.info文件BINLOG 文件名和POSITION;slave_IO线程工作完成
step9
slave_SQL线程读取relay-log.info文件,获取已执行的relay-log的位置,并以此作为起点,回放relay-log;
step10
slave_SQL线程完成日志回放之后,更新relay-log.info文件。
step11
relay-log会有自动清理的功能

附:主库一旦生成新的日志,会主动向BINLOG DUMP线程发送“信号”,通知从库slave_IO线程请求日志

主从复制在数据库启动之时自动重构,不需手动重启;

4. 从库状态信息

从库当前I/O线程请求状态
Slave_IO_State: Waiting for master to send event
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G"|grep 'IO_State'
               Slave_IO_State: Waiting for master to send event
主库相关信息(Master.info)
Master_Host: 主库IP地址
Master_User: 主库主从复制用户
Master_Port: 主库端口
Connect_Retry: 时间 #重试时间(单位:s)
Master_Log_File: 当前请求的主库二进制日志
Read_Master_Log_Pos: 主从复制起点位置
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '(^[ ]+Master_(Host|User|Port|Log))|(Connect_Retry)|(Read_Master_Log_Pos)'
                  Master_Host: 10.0.0.151
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
从库中继日志信息(relay.info)
Relay_Log_File: 从库中继日志
Relay_Log_Pos: SQL线程当前回放位置
Relay_Master_Log_File: 中继日志对应的二进制日志
Exec_Master_Log_Pos: 中继日志已执行位置
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '(^[ ]+Relay_)|Exec_Master'|head -4
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
          Exec_Master_Log_Pos: 154
从库的线程状态以及异常信息提示
Slave_IO_Running: Yes #正常状态为Yes;
Slave_SQL_Running: Yes #正常状态为Yes;
Last_IO_Errno: I/O线程错误号
Last_IO_Error: I/O线程错误提示信息
Last_SQL_Errno: SQL线程错误号
Last_SQL_Error: I/O线程错提示信息>
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             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:
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '^[ ]+Replicate_'|head -6
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
主从延时判定
Seconds_Behind_Master: 0
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep 'Seconds_Behind'
        Seconds_Behind_Master: 0  
延时从库相关信息
SQL_Delay: 0
SQL_Remaining_Delay: NULL
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '^[ ]+SQL'
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
GTID复制信息
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E 'Gtid|Auto'
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0

[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.151
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 7
                  Master_UUID: 6ca5e638-bfcc-11e9-a36e-000c2938d306
             Master_Info_File: /data/mysql/3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

三、主从复制维护

1.故障监控(手动)

mysql -u用户名 -p密码 -S socket文件 -e "show slave status \G" |grep Running: #监控从库I/O和SQL线程状态信息
mysql -u用户名 -p密码 -S socket文件 -e "show slave status \G" |grep Last |head -6 |tail -4
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G"|grep 'Running:'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G"|grep 'Last'| head -6| tail -4
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@db01 ~]#  mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

2.故障原因

I/O线程故障
解决思路:重建主从关系;

I/O线程主要工作:

连接主库
请求日志
接收日志
写入日志
  • 连接主库相关故障
    连接信息错误
    网络故障
    主库宕机
    防火墙阻挡
    主库连接数超过上限(set global max_connections)
排除方法:手工连接主库
  • 请求日志相关故障
    主库二进制日志完整性错误
reset slave all #重置master.info信息(需先停止从库I/O和SQL线程后)
show master status #查看主库状态
#模拟故障
[root@db01 ~]#  mysql -S /data/mysql/3307/mysql.sock  -e "show master status\G;"
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
[root@db01 ~]#  mysql -S /data/mysql/3307/mysql.sock  -e "reset master"
[root@db01 ~]#  mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000005' at 154, the last event read from '/data/mysql/3307/mysql-bin.000005' at 123, the last byte read from '/data/mysql/3307/mysql-bin.000005' at 154.'
               Last_SQL_Errno: 0
               Last_SQL_Error:
#模拟恢复
mysql> stop slave;
Query OK, 0 rows affected (0.41 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.28 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.151',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='456',
    -> MASTER_PORT=3307,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=154,
    -> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.14 sec)
[root@db01 ~]#  mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  • 接收日志相关故障
    I/O线程故障

  • 写入日志相关故障
    relay-log损坏

SQL线程故障

SQL线程主要工作:回放relay-log
  • 回放relay-log相关故障
    relay-log损坏
    回放SQL语句失败(创建的对象已存在,修改或删除的对象不存在,约束条件冲突)
SQL层主要工作:语法检查,SQL_mode检查,语义检查,语句解析,语句优化,语句执行

回放SQL失败的主要原因为主从数据不一致;

主从数据不一致原因

从库人为写入数据(解决方法:设置只读从库或使用中间件)
主从本身原因造成数据不一致
[root@db01 ~]#  mysql -S /data/mysql/3308/mysql.sock  -e "show variables like '%read_only%'"
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
设置只读从库
read_only
super_read_only

主从数据辅助工具

  1. pt-table-checksum 主从数据校验工具
  2. pt-table-sync 主从数据修复工具

四、主从延时

1.状态信息

Seconds_Behind_Master

2.延时因素

主库原因

  • 二进制日志书写不及时
解决方法:sync_binlog 默认值为1;事务提交立即刷写日志。
  • DUMP线程串行传输问题
解决方法:GTID

从库原因

  • SQL线程串行问题
解决方法:GTID和逻辑时钟(Logical_clock)

大事务原因

解决方法:定位日志点,查看主库二进制日志;

五、延时从库

1.概述

定义:延时从库是我们人为定制的一种的从库角色,与主从延时无关。
作用:数据库逻辑损坏时,可快速恢复数据。

2.配置

step 0 当前数据库状态

[root@db01 ~]#  mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
[root@db01 ~]# mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G;"|grep -E '^[ ]+SQL'
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

step 1 停止主从关系

stop slave;
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

step 2 设置延时时间(单位:秒)

change master to MASTER_DELAY = n
实际生产环境中一般延时3~6个小时;
mysql> change master to MASTER_DELAY = 1800;
Query OK, 0 rows affected (0.00 sec)

step 3 开启主从关系

start slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

step 4 验证延时从库

mysql -u用户名 -p密码 -S socket文件 -e "show slave status"|grep -E '^[ ]+SQL_'
[root@db01 ~]# mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G;"|grep -E '^[ ]+SQL'
                    SQL_Delay: 1800
          SQL_Remaining_Delay: NULL
[root@db01 ~]#  mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

3.模拟演练

恢复思路
1.停止从库SQL线程(stop slave sql_thread;)
2.上线维护页面
3.截取从库relaylog日志(起点:SQL线程停止时relay-log位置点;终点:误操作点)/将日志中误操作剔除;
4.恢复日志到从库

step 0 数据模拟

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

mysql> create database delay_db charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use delay_db;
Database changed

mysql> create table t1(id int primary key auto_increment, k1 char(5) not null default 'NA' )engine=innodb,charset=utf8mb4;
Query OK, 0 rows affected (0.37 sec)

mysql>  insert into t1(k1) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 for update;
+----+----+
| id | k1 |
+----+----+
|  1 | aa |
|  2 | bb |
|  3 | cc |
+----+----+
3 rows in set (0.00 sec)

mysql> drop database delay_db;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

step 1 停止从库SQL线程

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G;"|grep -E '^[ ]+SQL'
                    SQL_Delay: 1800
          SQL_Remaining_Delay: NULL
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G"| grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

step 2 截取relay-log日志

show relaylog events in '中继日志'
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock  -e "show slave status\G"| grep -E 'Relay_Log'|head -2
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 1051
[root@db01 /data/mysql/3309/data]# mysql -S /data/mysql/3309/mysql.sock  -e "show relaylog events in 'db01-relay-bin.000002';"
+-----------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| Log_name              | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                               |
+-----------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| db01-relay-bin.000002 |    4 | Format_desc    |         9 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                                                                                              |
| db01-relay-bin.000002 |  123 | Previous_gtids |         9 |         154 |                                                                                                                                    |
| db01-relay-bin.000002 |  154 | Rotate         |         7 |           0 | mysql-bin.000002;pos=154                                                                                                           |
| db01-relay-bin.000002 |  201 | Format_desc    |         7 |           0 | Server ver: 5.7.26-log, Binlog ver: 4                                                                                              |
| db01-relay-bin.000002 |  320 | Anonymous_Gtid |         7 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                               |
| db01-relay-bin.000002 |  385 | Query          |         7 |         341 | create database delay_db charset=utf8mb4                                                                                           |
| db01-relay-bin.000002 |  507 | Anonymous_Gtid |         7 |         406 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                               |
| db01-relay-bin.000002 |  572 | Query          |         7 |         602 | use `delay_db`; create table t1(id int primary key auto_increment, k1 char(5) not null default 'NA' )engine=innodb,charset=utf8mb4 |
| db01-relay-bin.000002 |  768 | Anonymous_Gtid |         7 |         667 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                               |
| db01-relay-bin.000002 |  833 | Query          |         7 |         743 | BEGIN                                                                                                                              |
| db01-relay-bin.000002 |  909 | Table_map      |         7 |         795 | table_id: 222 (delay_db.t1)                                                                                                        |
| db01-relay-bin.000002 |  961 | Write_rows     |         7 |         854 | table_id: 222 flags: STMT_END_F                                                                                                    |
| db01-relay-bin.000002 | 1020 | Xid            |         7 |         885 | COMMIT /* xid=2092 */                                                                                                              |
| db01-relay-bin.000002 | 1051 | Anonymous_Gtid |         7 |         950 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                               |
| db01-relay-bin.000002 | 1116 | Query          |         7 |        1054 | drop database delay_db                                                                                                             |
+-----------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------+
[root@db01 /data/mysql/3309/data]# mysqlbinlog --start-position=1051 --stop-position=1116 db01-relay-bin.000002 >/tmp/recover.sql;
[root@db01 /data/mysql/3309/data]# ls /tmp/recover.sql -l
-rw-r--r-- 1 root root 1139 Sep  6 13:14 /tmp/recover.sql

step 3 恢复日志

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/recover.sql;
......
Query OK, 0 rows affected (0.00 sec)
mysql> use delay_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+----+----+
| id | k1 |
+----+----+
|  1 | aa |
|  2 | bb |
|  3 | cc |
+----+----+
3 rows in set (0.00 sec)
[root@db01 /data/mysql/3309/data]# mysqldump -S /data/mysql/3309/mysql.sock -B delay_db -E -R --triggers --master-data=2 --single-transaction >/tmp/3309.sql
[root@db01 /data/mysql/3309/data]# ls /tmp/3309.sql -l
-rw-r--r-- 1 root root 2293 Sep  6 13:23 /tmp/3309.sql
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/3309.sql;
......
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| delay_db           |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use delay_db;
Database changed
mysql> select * from t1;
+----+----+
| id | k1 |
+----+----+
|  1 | aa |
|  2 | bb |
|  3 | cc |
+----+----+
3 rows in set (0.00 sec)

六、过滤复制

通常白名单和黑名单仅使用一个;

1、主库过滤(实际环境中基本不使用)

  • Binlog_Do_DB 记录日志白名单
  • Binlog_Ignore_DB 记录日志黑名单
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1054 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2、从库过滤

从库会接收主库的全部日志,但只会回放白名单内的相关库和表的日志;
基于库的过滤
replicate_do_db=库1
replicate_ignore_db=库2
基于表的精确过滤
replicate_do_table=库.表1
replicate_ignore_table=库.表2
基于表的模糊过滤
replicate_wild_do_table=库.表1
replicate_wild_ignore_table=库.表2
[root@db01 /data/mysql/3309/data]#  mysql -S /data/mysql/3308/mysql.sock  -e "show slave status\G;"|grep -E '^[ ]+Replicate_'|head -6
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
验证
[root@db01 /data/mysql/3309/data]# vim /data/mysql/3309/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql.sock
server_id=9
port=3309
log_error=/data/mysql/3309/data/mysql.log
log_bin=/data/mysql/3309/mysql-bin
replicate_do_db=aspen
[root@db01 /data/mysql/3309/data]# systemctl restart mysqld3309.service 
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.01 sec)

mysql> create database aspen;
Query OK, 1 row affected (0.00 sec)

mysql> create database stu;
Query OK, 1 row affected (0.00 sec)

mysql> create database aspen_han;
Query OK, 1 row affected (0.00 sec)
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aspen              |
| delay_db           |
| mysql              |
| performance_schema |
| relay_db           |
| sys                |
| test               |
+--------------------+
8 rows in set (0.00 sec)
mysql> show relaylog events in 'db01-relay-bin.000006';
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name              | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| db01-relay-bin.000006 |   4 | Format_desc    |         9 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000006 | 123 | Previous_gtids |         9 |         154 |                                       |
| db01-relay-bin.000006 | 154 | Rotate         |         7 |           0 | mysql-bin.000001;pos=1106             |
| db01-relay-bin.000006 | 201 | Format_desc    |         7 |           0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000006 | 320 | Anonymous_Gtid |         7 |        1171 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000006 | 385 | Query          |         7 |        1268 | create database aspen                 |
| db01-relay-bin.000006 | 482 | Anonymous_Gtid |         7 |        1333 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000006 | 547 | Query          |         7 |        1424 | create database stu                   |
| db01-relay-bin.000006 | 638 | Anonymous_Gtid |         7 |        1489 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000006 | 703 | Query          |         7 |        1598 | create database aspen_han             |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)

附:思维导图