017、MySQL主从复制(Replication)架构特殊从库之GTID复制部署实战
|
Mysql
|
139

2341 字
|
3.3 小时
本文最后更新于 428 天前，其中的信息可能已经过时，如有错误请发送邮件到 wuxianglongblog@163.com

MySQL 主从复制 (Replication) 架构特殊从库之 GTID 复制部署实战

一.GTID 复制概述

1.GTID 概述

    GTID的英文全称为"Global Transaction ID"，即一个全局唯一的编号。GTID主要保证主从复制架构中一致性的问题。生产环境中强烈建议开启GTID，最好是在初始化数据库时就直接开启GTID哟~
 
    对于DDL语句来讲，一条语句对应一个GTID，但对于一个DML语句而言，一个已提交的事物对应一个GTID。
 
    GTID由MySQL 5.6版本新加的特性，在MySQL 5.7版本中有所加强，在MySQL 8.0版本中已经开始主推GTID。值得注意的是，在mysql 5.7中的GTID，即使不开启也会有自动生成。
 
    标准的GTID格式:"GTID = server_uuid:transaction_id"，例如: "ecaf563f-5345-11eb-a106-000c29820c67:5"。
        mysql> SELECT @@server_uuid;
        +--------------------------------------+
        | @@server_uuid                        |
        +--------------------------------------+
        | ecaf563f-5345-11eb-a106-000c29820c67 |
        +--------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
 
    如下所示，表示未开启GTID时，我们查看到"Event_type=Anonymous_Gtid"，而"Info=SET @@SESSION.GTID_NEXT= 'ANONYMOUS'"，这是由于咱们未开启GTID，由MySQL自动生成的GTID信息。
        mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001';  
        +----------------------------------+-----+----------------+-----------+-------------+------------------------------------------------+
        | Log_name                         | Pos | Event_type     | Server_id | End_log_pos | Info                                           |
        +----------------------------------+-----+----------------+-----------+-------------+------------------------------------------------+
        | oldboyedu-mysqld-binary.000001 |   4 | Format_desc    |         7 |         123 | Server ver: 5.7.31-log, Binlog ver: 4          |
        | oldboyedu-mysqld-binary.000001 | 123 | Previous_gtids |         7 |         154 |                                                |
        | oldboyedu-mysqld-binary.000001 | 154 | Anonymous_Gtid |         7 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'           |
        | oldboyedu-mysqld-binary.000001 | 219 | Query          |         7 |         343 | CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4 |
        +----------------------------------+-----+----------------+-----------+-------------+------------------------------------------------+
        4 rows in set (0.00 sec)
 
        mysql> 
 
    如下所示，表示已开启GTID时，我们查看到"Event_type=Gtid"，而"Info=SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:2'"
        mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000004';
        +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
        | Log_name                         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
        +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |    4 | Format_desc    |         7 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                             |
        | oldboyedu-mysqld-binary.000004 |  123 | Previous_gtids |         7 |         154 |                                                                   |
        | oldboyedu-mysqld-binary.000004 |  154 | Gtid           |         7 |         219 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:1' |
        | oldboyedu-mysqld-binary.000004 |  219 | Query          |         7 |         357 | CREATE DATABASE IF NOT EXISTS hive DEFAULT CHARACTER SET utf8mb4  |
        | oldboyedu-mysqld-binary.000004 |  357 | Gtid           |         7 |         422 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:2' |
        | oldboyedu-mysqld-binary.000004 |  422 | Query          |         7 |         557 | CREATE DATABASE IF NOT EXISTS cdh DEFAULT CHARACTER SET utf8mb4   |
        | oldboyedu-mysqld-binary.000004 |  557 | Gtid           |         7 |         622 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:3' |
        | oldboyedu-mysqld-binary.000004 |  622 | Query          |         7 |         736 | CREATE TABLE IF NOT EXISTS hive.user(id int)                      |
        | oldboyedu-mysqld-binary.000004 |  736 | Gtid           |         7 |         801 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:4' |
        | oldboyedu-mysqld-binary.000004 |  801 | Query          |         7 |         869 | BEGIN                                                             |
        | oldboyedu-mysqld-binary.000004 |  869 | Table_map      |         7 |         916 | table_id: 112 (hive.user)                                         |
        | oldboyedu-mysqld-binary.000004 |  916 | Write_rows     |         7 |         966 | table_id: 112 flags: STMT_END_F                                   |
        | oldboyedu-mysqld-binary.000004 |  966 | Xid            |         7 |         997 | COMMIT /* xid=16 */                                               |
        | oldboyedu-mysqld-binary.000004 |  997 | Gtid           |         7 |        1062 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:5' |
        | oldboyedu-mysqld-binary.000004 | 1062 | Query          |         7 |        1174 | CREATE TABLE IF NOT EXISTS cdh.user(id int)                       |
        +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
        15 rows in set (0.00 sec)
 
        mysql>

2.GTID 复制的作用

    GTID复制的作用:
        主要保证主从复制中的高级特性。
 
    GTID在MySQL 5.6版本中引入的新特性，但默认并没有开启。
    GTID在MySQL 5.7版本中即使不开启，也有匿名的GTID记录。
    GTID的优势:
        (1)为主库的dump线程传输可以提供并行的解决方案;
        (2)为从库的SQL线程可以提供并发"回放";
        (3)配置主从时方便，无需手动定位主库二进制日志文件名称及位置信息，而是交由MySQL自动去定位;
 
    温馨提示:
        MySQL 5.7.17+的版本以后几乎都是GTID模式了。

3.GTID 主从复制和传统复制的差别

    我们知道配置基于GTID主从复制相对简单，只需设置"MASTER_AUTO_POSITION=1"参数，而无需定位主库的文件名称和位置点。而是交由MySQL去自动识别复制的起始点。
 
    基于GTID的主从复制，MySQL自动判断复制起始点的流程如下所示:
        (1)在主库和从库都是刚刚初始化的数据库而言，从库的IO线程会读取中继日志的最后一个GTID事件，我们此时可以认为GTID的事件为"0"，表示从来没有复制过，那么从库的I/O线程就会去主库的第一个GTID事件开始复制;
        (2)但在生产环境中，很有可能主库已经运行很长时间了，因此需要对主库进行备份，而后在从库进行恢复，这个时候从库就会去查"GTID_PURGED"变量记录的信息，会根据"GTID_PURGED"记录的GTID事件继续往后复制;
 
    举个例子:
        假设从主库中的备份文件中有这样"SET @@GLOBAL.GTID_PURGED='ecaf563f-5345-11eb-a106-000c29820c67:1-100';"一行记录，那么我们基于GTID下次请求的事务就是从第101个GTID开始复制，因为当前数据库已经存在了1-100啦。

4. 启用 GTID 模式

    有关GTID启用参数说明:
        gtid-mode:
            将其值设置为"on"则表示开启GTID功能。默认值为"OFF"，表示未开启。
 
        enforce-gtid-consistency:
            启用强制GITD的一致性。即在启动MySQL实例之前，会校验GTID一致性的问题。默认值为"OFF"，表示未开启。
 
    如下所示，表示启用MySQL数据库实例的实操:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 2
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql> SELECT @@gtid_mode;
        +-------------+
        | @@gtid_mode |
        +-------------+
        | OFF         |
        +-------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> SELECT @@enforce_gtid_consistency;
        +----------------------------+
        | @@enforce_gtid_consistency |
        +----------------------------+
        | OFF                        |
        +----------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> QUIT
        Bye
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql23307/my.cnf 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# tail -2 /oldboyedu/softwares/mysql23307/my.cnf 
        gtid-mode=on
        enforce-gtid-consistency=true
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# systemctl restart mysqld23307
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 2
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql> 
        mysql> SELECT @@gtid_mode;
        +-------------+
        | @@gtid_mode |
        +-------------+
        | ON          |
        +-------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> SELECT @@enforce_gtid_consistency;
        +----------------------------+
        | @@enforce_gtid_consistency |
        +----------------------------+
        | ON                         |
        +----------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +----------------------------------+----------+--------------+------------------+-------------------+
        | oldboyedu-mysqld-binary.000004 |      154 |              |                  |                   |
        +----------------------------------+----------+--------------+------------------+-------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> CREATE DATABASE IF NOT EXISTS hive DEFAULT CHARACTER SET utf8mb4;  # 注意哈，我们下面分别执行了DDL,DML语句，注意观察"Executed_Gtid_Set"字段信息
        Query OK, 1 row affected (0.00 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+----------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
        +----------------------------------+----------+--------------+------------------+----------------------------------------+
        | oldboyedu-mysqld-binary.000004 |      357 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1 |
        +----------------------------------+----------+--------------+------------------+----------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> CREATE DATABASE IF NOT EXISTS cdh DEFAULT CHARACTER SET utf8mb4;
        Query OK, 1 row affected (0.00 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |      557 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-2 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.01 sec)
 
        mysql> 
        mysql> CREATE TABLE IF NOT EXISTS hive.user(id int);
        Query OK, 0 rows affected (0.05 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |      736 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-3 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> INSERT INTO hive.user VALUES(100),(200),(300);
        Query OK, 3 rows affected (0.00 sec)
        Records: 3  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |      997 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-4 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> CREATE TABLE IF NOT EXISTS cdh.user(id int);
        Query OK, 0 rows affected (0.01 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |     1174 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-5 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000004';  # 还记得"SET @@SESSION.GTID_NEXT= 'ANONYMOUS'"这样的日志信息吗？这是由于咱们未开启GTID，由MySQL自动生成的GTID信息，开启后就会输出如下的信息。
        +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
        | Log_name                         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
        +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |    4 | Format_desc    |         7 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                             |
        | oldboyedu-mysqld-binary.000004 |  123 | Previous_gtids |         7 |         154 |                                                                   |
        | oldboyedu-mysqld-binary.000004 |  154 | Gtid           |         7 |         219 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:1' |
        | oldboyedu-mysqld-binary.000004 |  219 | Query          |         7 |         357 | CREATE DATABASE IF NOT EXISTS hive DEFAULT CHARACTER SET utf8mb4  |
        | oldboyedu-mysqld-binary.000004 |  357 | Gtid           |         7 |         422 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:2' |
        | oldboyedu-mysqld-binary.000004 |  422 | Query          |         7 |         557 | CREATE DATABASE IF NOT EXISTS cdh DEFAULT CHARACTER SET utf8mb4   |
        | oldboyedu-mysqld-binary.000004 |  557 | Gtid           |         7 |         622 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:3' |
        | oldboyedu-mysqld-binary.000004 |  622 | Query          |         7 |         736 | CREATE TABLE IF NOT EXISTS hive.user(id int)                      |
        | oldboyedu-mysqld-binary.000004 |  736 | Gtid           |         7 |         801 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:4' |
        | oldboyedu-mysqld-binary.000004 |  801 | Query          |         7 |         869 | BEGIN                                                             |
        | oldboyedu-mysqld-binary.000004 |  869 | Table_map      |         7 |         916 | table_id: 112 (hive.user)                                         |
        | oldboyedu-mysqld-binary.000004 |  916 | Write_rows     |         7 |         966 | table_id: 112 flags: STMT_END_F                                   |
        | oldboyedu-mysqld-binary.000004 |  966 | Xid            |         7 |         997 | COMMIT /* xid=16 */                                               |
        | oldboyedu-mysqld-binary.000004 |  997 | Gtid           |         7 |        1062 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:5' |
        | oldboyedu-mysqld-binary.000004 | 1062 | Query          |         7 |        1174 | CREATE TABLE IF NOT EXISTS cdh.user(id int)                       |
        +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
        15 rows in set (0.00 sec)
 
        mysql>

二。部署 GTID 实战

1. 准备三台虚拟机

    三台虚拟机安装MySQL 5.7的发行版本，关闭防火墙即可。
 
    温馨提示:
        其实使用多实例配置GTID也可以模拟实现效果，但为了后面的MHA做准备，建议大家还是可以直接使用虚拟机来做实验。
 
    实验环境:
        mysql201.oldboyedu.com:
            172.200.1.201(主库)
        mysql202.oldboyedu.com:
            172.200.1.202(从库)
        mysql203.oldboyedu.com:
            172.200.1.203(从库)

2. 生成配置文件

    mysql201.oldboyedu.com主库生成配置文件:
        [root@mysql201.oldboyedu.com ~]# cat > /etc/my.cnf <<EOF
        > [mysql]
        > prompt=mysql201 [\d]>
        > socket=/tmp/mysql.sock
        > 
        > [mysqld]
        > server_id=201
        > bind-address=172.200.1.201
        > port=3306
        > basedir=/oldboyedu/softwares/mysql/mysql
        > datadir=/oldboyedu/data/mysql
        > log_bin=/oldboyedu/logs/mysql/binlog/mysql-bin
        > socket=/tmp/mysql.sock
        > secure-file-priv=/tmp
        > autocommit=0
        > binlog_format=row
        > # 开启GTID功能
        > gtid-mode=on
        > enforce-gtid-consistency=true
        > log-slave-updates=1
        > EOF
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# cat /etc/my.cnf
        [mysql]
        prompt=mysql201 [\d]>
        socket=/tmp/mysql.sock
 
        [mysqld]
        server_id=201
        bind-address=172.200.1.201
        port=3306
        basedir=/oldboyedu/softwares/mysql/mysql
        datadir=/oldboyedu/data/mysql
        log_bin=/oldboyedu/logs/mysql/binlog/mysql-bin
        socket=/tmp/mysql.sock
        secure-file-priv=/tmp
        autocommit=0
        binlog_format=row
        # 开启GTID功能
        gtid-mode=on
        enforce-gtid-consistency=true
        log-slave-updates=1
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      80                                                              [::]:3306                                                                        [::]:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# install -o mysql -g mysql -d /oldboyedu/logs/mysql/binlog/
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# systemctl restart mysqld
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      80                                                     172.200.1.201:3306                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql201.oldboyedu.com ~]# 
 
    mysql202.oldboyedu.com从库生成配置文件:
        [root@mysql202.oldboyedu.com ~]# cat > /etc/my.cnf <<EOF
        > [mysql]
        > prompt=mysql202 [\d]>
        > socket=/tmp/mysql.sock
        > 
        > [mysqld]
        > server_id=202
        > bind-address=172.200.1.202
        > port=3306
        > basedir=/oldboyedu/softwares/mysql/mysql
        > datadir=/oldboyedu/data/mysql
        > log_bin=/oldboyedu/logs/mysql/binlog/mysql-bin
        > socket=/tmp/mysql.sock
        > secure-file-priv=/tmp
        > autocommit=0
        > binlog_format=row
        > # 开启GTID功能
        > gtid-mode=on
        > enforce-gtid-consistency=true
        > log-slave-updates=1
        > EOF
        [root@mysql202.oldboyedu.com ~]# 
        [root@mysql202.oldboyedu.com ~]# cat /etc/my.cnf
        [mysql]
        prompt=mysql202 [\d]>
        socket=/tmp/mysql.sock
 
        [mysqld]
        server_id=202
        bind-address=172.200.1.202
        port=3306
        basedir=/oldboyedu/softwares/mysql/mysql
        datadir=/oldboyedu/data/mysql
        log_bin=/oldboyedu/logs/mysql/binlog/mysql-bin
        socket=/tmp/mysql.sock
        secure-file-priv=/tmp
        autocommit=0
        binlog_format=row
        # 开启GTID功能
        gtid-mode=on
        enforce-gtid-consistency=true
        log-slave-updates=1
        [root@mysql202.oldboyedu.com ~]# 
        [root@mysql202.oldboyedu.com ~]# install -o mysql -g mysql -d /oldboyedu/logs/mysql/binlog/
        [root@mysql202.oldboyedu.com ~]# 
        [root@mysql202.oldboyedu.com ~]# systemctl restart mysqld
        [root@mysql202.oldboyedu.com ~]# 
        [root@mysql202.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      80                                                     172.200.1.202:3306                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql202.oldboyedu.com ~]# 
 
    mysql203.oldboyedu.com从库生成配置文件:
        [root@mysql203.oldboyedu.com ~]# cat > /etc/my.cnf <<EOF
        > [mysql]
        > prompt=mysql203 [\d]>
        > socket=/tmp/mysql.sock
        > 
        > [mysqld]
        > server_id=203
        > bind-address=172.200.1.203
        > port=3306
        > basedir=/oldboyedu/softwares/mysql/mysql
        > datadir=/oldboyedu/data/mysql
        > log_bin=/oldboyedu/logs/mysql/binlog/mysql-bin
        > socket=/tmp/mysql.sock
        > secure-file-priv=/tmp
        > autocommit=0
        > binlog_format=row
        > # 开启GTID功能
        > gtid-mode=on
        > enforce-gtid-consistency=true
        > log-slave-updates=1
        > EOF
        [root@mysql203.oldboyedu.com ~]# 
        [root@mysql203.oldboyedu.com ~]# cat /etc/my.cnf
        [mysql]
        prompt=mysql203 [\d]>
        socket=/tmp/mysql.sock
 
        [mysqld]
        server_id=203
        bind-address=172.200.1.203
        port=3306
        basedir=/oldboyedu/softwares/mysql/mysql
        datadir=/oldboyedu/data/mysql
        log_bin=/oldboyedu/logs/mysql/binlog/mysql-bin
        socket=/tmp/mysql.sock
        secure-file-priv=/tmp
        autocommit=0
        binlog_format=row
        # 开启GTID功能
        gtid-mode=on
        enforce-gtid-consistency=true
        log-slave-updates=1
        [root@mysql203.oldboyedu.com ~]# 
        [root@mysql203.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      80                                                     172.200.1.203:3306                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql203.oldboyedu.com ~]# 
 
    有关GTID启用参数说明:
        gtid-mode:
            将其值设置为"on"则表示开启GTID功能。默认值为"OFF"，表示未开启。
 
        enforce-gtid-consistency:
            启用强制GITD的一致性。即在启动MySQL实例之前，会校验GTID一致性的问题。默认值为"OFF"，表示未开启。
 
        log-slave-updates:
            强制从库记录二进制日志的一致性。换句话说，就是要求从库的GTID和主库的GTID要保持一致。
 
    温馨提示:
        在上面配置时"server_id","bind-address"并不相同哟，log_bin指定的上级目录必须存在且运行MySQL实例的用户必须有权限访问!

3. 初始化数据库 (三个节点都要做相同操作，但我笔记中仅以 "mysql201.oldboyedu.com" 做演示)

    (1)清理环境，避免以前的一些数据干扰(比如已经存在的文件，用户权限等)，我们准备用一个全新的数据库来实验(这样做有个好处就是可以省略使用mysqlbinlog工具进行备份恢复，因为3个数据库初始数据相同)。
        [root@mysql201.oldboyedu.com ~]# systemctl stop mysqld
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# rm -rf /oldboyedu/data/mysql/*
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# rm -rf /oldboyedu/logs/mysql/binlog/*
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# ll /oldboyedu/data/mysql/
        总用量 0
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql/binlog/
        总用量 0
        [root@mysql201.oldboyedu.com ~]# 
 
    (2)初始化数据库
        [root@mysql201.oldboyedu.com ~]# mysqld --initialize-insecure --user=mysql --basedir=/oldboyedu/softwares/mysql/mysql --datadir=/oldboyedu/data/mysql
        2021-02-17T01:44:38.696093Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
        2021-02-17T01:44:38.899253Z 0 [Warning] InnoDB: New log files created, LSN=45790
        2021-02-17T01:44:38.931787Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
        2021-02-17T01:44:39.277253Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b2a02b77-70c1-11e
        b-a390-000c29e297a4.2021-02-17T01:44:39.279812Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
        2021-02-17T01:44:39.968279Z 0 [Warning] CA certificate ca.pem is self signed.
        2021-02-17T01:44:40.163229Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# ll /oldboyedu/data/mysql/
        总用量 110660
        -rw-r----- 1 mysql mysql       56 2月  17 09:44 auto.cnf
        -rw------- 1 mysql mysql     1676 2月  17 09:44 ca-key.pem
        -rw-r--r-- 1 mysql mysql     1112 2月  17 09:44 ca.pem
        -rw-r--r-- 1 mysql mysql     1112 2月  17 09:44 client-cert.pem
        -rw------- 1 mysql mysql     1680 2月  17 09:44 client-key.pem
        -rw-r----- 1 mysql mysql      431 2月  17 09:44 ib_buffer_pool
        -rw-r----- 1 mysql mysql 12582912 2月  17 09:44 ibdata1
        -rw-r----- 1 mysql mysql 50331648 2月  17 09:44 ib_logfile0
        -rw-r----- 1 mysql mysql 50331648 2月  17 09:44 ib_logfile1
        drwxr-x--- 2 mysql mysql     4096 2月  17 09:44 mysql
        drwxr-x--- 2 mysql mysql     8192 2月  17 09:44 performance_schema
        -rw------- 1 mysql mysql     1680 2月  17 09:44 private_key.pem
        -rw-r--r-- 1 mysql mysql      452 2月  17 09:44 public_key.pem
        -rw-r--r-- 1 mysql mysql     1112 2月  17 09:44 server-cert.pem
        -rw------- 1 mysql mysql     1680 2月  17 09:44 server-key.pem
        drwxr-x--- 2 mysql mysql     8192 2月  17 09:44 sys
        [root@mysql201.oldboyedu.com ~]# 
 
    (3)启动数据库

4. 准备 MySQL 的启动脚本 (如果基于以前的环境部署，该步骤可省略，因为之前部署主从环境时已经拷贝过脚本，并做了修改！)

    关于启动脚本的操作，我这里不做赘述，因为我是基于早期部署的MySQL环境使用的，详情可参考第一章节中"MySQL二进制安装"笔记。
 
        [root@mysql201.oldboyedu.com ~]# ll /oldboyedu/softwares/mysql/mysql/support-files/
        总用量 24
        -rw-r--r-- 1 mysql mysql   773 6月   2 2020 magic
        -rwxr-xr-x 1 mysql mysql  1061 6月   2 2020 mysqld_multi.server
        -rwxr-xr-x 1 mysql mysql   894 6月   2 2020 mysql-log-rotate
        -rwxr-xr-x 1 mysql mysql 10576 6月   2 2020 mysql.server
        [root@mysql201.oldboyedu.com ~]#
        [root@mysql201.oldboyedu.com ~]# cp /oldboyedu/softwares/mysql/mysql/support-files/mysql.server /etc/init.d/mysqld
        [root@mysql201.oldboyedu.com ~]#
        [root@mysql201.oldboyedu.com ~]# chkconfig --add mysqld  # 设置开机自启动脚本
        [root@mysql201.oldboyedu.com ~]#
 
    在MySQL的脚本文件中指定MySQL数据的自定义安装路径:
        [root@mysql201.oldboyedu.com ~]# cp /etc/init.d/mysqld /etc/init.d/mysqld-`date +%F`
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# grep "^datadir=" /etc/init.d/mysqld
        datadir=
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# sed -r -i "s#^(datadir=)#\1/oldboyedu/data/mysql#" /etc/init.d/mysqld
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# grep "^datadir=" /etc/init.d/mysqld
        datadir=/oldboyedu/data/mysql
        [root@mysql201.oldboyedu.com ~]# 
 
    在MySQL的脚本文件中指定MySQL程序的自定义安装路径:
        [root@mysql201.oldboyedu.com ~]# grep "^basedir=" /etc/init.d/mysqld
        basedir=
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# sed -r -i "s#^(basedir=)#\1/oldboyedu/softwares/mysql/mysql#" /etc/init.d/mysqld
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# grep "^basedir=" /etc/init.d/mysqld
        basedir=/oldboyedu/softwares/mysql/mysql
        [root@mysql201.oldboyedu.com ~]# 
 
    重新加载启动脚本:
        [root@mysql201.oldboyedu.com ~]# systemctl daemon-reload  # 由于上面修改了启动脚本，因此我们要执行该命令重新加载一下！
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# systemctl restart mysqld
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql202.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      80                                                     172.200.1.202:3306                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql202.oldboyedu.com ~]#

5. 启动数据库

        [root@mysql201.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql201.oldboyedu.com ~]#
        [root@mysql201.oldboyedu.com ~]# systemctl start mysqld
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      80                                                     172.200.1.201:3306                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql201.oldboyedu.com ~]#  
        [root@mysql201.oldboyedu.com ~]# systemctl enable mysqld
        mysqld.service is not a native service, redirecting to /sbin/chkconfig.
        Executing /sbin/chkconfig mysqld on
        [root@mysql201.oldboyedu.com ~]#

6. 主库创建专用于复制的用户，从库进行验证

    主库创建用于复制的用户:
        [root@mysql201.oldboyedu.com ~]# mysql
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 4
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql201 [(none)]>
        mysql201 [(none)]>SELECT user,host FROM mysql.user;
        +---------------+-------------+
        | user          | host        |
        +---------------+-------------+
        | mysql.session | localhost   |
        | mysql.sys     | localhost   |
        | root          | localhost   |
        +---------------+-------------+
        3 rows in set (0.00 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>CREATE USER copy@'172.200.1.%' IDENTIFIED BY 'oldboyedu';  # 创建用户
        Query OK, 0 rows affected (0.00 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>SELECT user,host FROM mysql.user;
        +---------------+-------------+
        | user          | host        |
        +---------------+-------------+
        | copy          | 172.200.1.% |
        | mysql.session | localhost   |
        | mysql.sys     | localhost   |
        | root          | localhost   |
        +---------------+-------------+
        4 rows in set (0.00 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>SHOW GRANTS FOR copy@'172.200.1.%';
        +--------------------------------------------+
        | Grants for copy@172.200.1.%                |
        +--------------------------------------------+
        | GRANT USAGE ON *.* TO 'copy'@'172.200.1.%' |
        +--------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>GRANT REPLICATION SLAVE ON *.* TO copy@'172.200.1.%';  # 为创建的用户授权复制权限("REPLICATION SLAVE")
        Query OK, 0 rows affected (0.01 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>SHOW GRANTS FOR copy@'172.200.1.%';
        +--------------------------------------------------------+
        | Grants for copy@172.200.1.%                            |
        +--------------------------------------------------------+
        | GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.200.1.%' |
        +--------------------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>QUIT
        Bye
        [root@mysql201.oldboyedu.com ~]# 
 
    从库验证用户是否可以正常登录:(一定要验证一下，如果从库登录不了，说明您配置的用户权限有问题，请及时排查，否则在从库使用该用户复制时会报错哟~)
        [root@mysql202.oldboyedu.com ~]# mysql -h 172.200.1.201 -u copy -poldboyedu
        mysql: [Warning] Using a password on the command line interface can be insecure.
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 6
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql202 [(none)]>
        mysql202 [(none)]>SHOW DATABASES;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        +--------------------+
        1 row in set (0.00 sec)
 
        mysql202 [(none)]>
        mysql202 [(none)]>QUIT
        Bye
        [root@mysql202.oldboyedu.com ~]#

7. 从库开启主从

    (1)主库使用mysqlbinlog工具进行备份，而后在两个从库进行恢复
        该步骤直接省略，原因很简单，因为我们三个数据库都重新格式化的数据库，因此3个数据库目前都没有新数据生成。但生产环境中一定不要忽略该步骤哟~
 
    (2)mysql202.oldboyedu.com从库开启主从复制
        [root@mysql202.oldboyedu.com ~]# mysql
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 3
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql202 [(none)]>
        mysql202 [(none)]>CHANGE MASTER TO
            ->   MASTER_HOST='172.200.1.201',
            ->   MASTER_USER='copy',
            ->   MASTER_PASSWORD='oldboyedu',
            ->   MASTER_PORT=3306, # 该参数可以省略，因为默认就是3306端口，生产环境中如果修改了3306端口的话，就得显式使用该参数了哟~
            ->   MASTER_AUTO_POSITION=1;  # 让从库自动判断二进制位置的起点，无需咱们人为手动指定主库的二进制文件名称及位置啦~
        Query OK, 0 rows affected, 2 warnings (0.30 sec)
 
        mysql202 [(none)]>
        mysql202 [(none)]>START SLAVE;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql202 [(none)]>
        mysql202 [(none)]>SHOW SLAVE STATUS\G
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event
                          Master_Host: 172.200.1.201
                          Master_User: copy
                          Master_Port: 3306
                        Connect_Retry: 60
                      Master_Log_File: mysql-bin.000003
                  Read_Master_Log_Pos: 615
                       Relay_Log_File: mysql202-relay-bin.000002
                        Relay_Log_Pos: 828
                Relay_Master_Log_File: mysql-bin.000003
                     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: 615
                      Relay_Log_Space: 1038
                      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: 201
                          Master_UUID: b2a02b77-70c1-11eb-a390-000c29e297a4
                     Master_Info_File: /oldboyedu/data/mysql/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: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
                    Executed_Gtid_Set: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
                        Auto_Position: 1
                 Replicate_Rewrite_DB: 
                         Channel_Name: 
                   Master_TLS_Version: 
        1 row in set (0.00 sec)
 
        mysql202 [(none)]>
 
    (3)mysql203.oldboyedu.com从库开启主从复制
        [root@mysql203.oldboyedu.com ~]# mysql -uroot -poldboyedu
        mysql: [Warning] Using a password on the command line interface can be insecure.
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 2
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql203 [(none)]>USE mysql
        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
        mysql203 [mysql]>
        mysql203 [mysql]>CHANGE MASTER TO
            ->    MASTER_HOST='172.200.1.201',
            ->    MASTER_USER='copy',
            ->    MASTER_PASSWORD='oldboyedu',
            ->    MASTER_PORT=3306,
            ->    MASTER_AUTO_POSITION=1;
        Query OK, 0 rows affected, 2 warnings (0.02 sec)
 
        mysql203 [mysql]>
        mysql203 [mysql]>START SLAVE;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql203 [mysql]>
        mysql203 [mysql]>SHOW SLAVE STATUS\G
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event
                          Master_Host: 172.200.1.201
                          Master_User: copy
                          Master_Port: 3306
                        Connect_Retry: 60
                      Master_Log_File: mysql-bin.000003
                  Read_Master_Log_Pos: 615
                       Relay_Log_File: mysql203-relay-bin.000002
                        Relay_Log_Pos: 828
                Relay_Master_Log_File: mysql-bin.000003
                     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: 615
                      Relay_Log_Space: 1038
                      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: 201
                          Master_UUID: b2a02b77-70c1-11eb-a390-000c29e297a4
                     Master_Info_File: /oldboyedu/data/mysql/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: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
                    Executed_Gtid_Set: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
                        Auto_Position: 1
                 Replicate_Rewrite_DB: 
                         Channel_Name: 
                   Master_TLS_Version: 
        1 row in set (0.00 sec)
 
        mysql203 [mysql]>  
 
    温馨提示:
        (1)基于GTID的主从复制，我们就不用太过于关心主库的二进制日志，从库的中继日志的文件名称和位置点了，而是应该关心"Retrieved_Gtid_Set"和"Executed_Gtid_Set"这两个参数;
        (2)有关"Retrieved_Gtid_Set"和"Executed_Gtid_Set"这两个参数说明如下:
            Retrieved_Gtid_Set: 
                表示从库已经接收的GTID编号。
            Executed_Gtid_Set: 
                表示从库已经执行的GITD编号。

8. 基于 GTID 的主从复制更容易查看从库是否有延迟

    mysql201.oldboyedu.com主库查看GTID执行的位置点:
        [root@mysql201.oldboyedu.com ~]# mysql
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 12
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
        mysql201 [(none)]>
        mysql201 [(none)]>SHOW MASTER STATUS;
        +------------------+----------+--------------+------------------+------------------------------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +------------------+----------+--------------+------------------+------------------------------------------+
        | mysql-bin.000003 |      615 |              |                  | b2a02b77-70c1-11eb-a390-000c29e297a4:1-2 |
        +------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql201 [(none)]>
        mysql201 [(none)]>QUIT
        Bye
        [root@mysql201.oldboyedu.com ~]# 
 
    mysql202.oldboyedu.com从库查看执行的位置点:
        [root@mysql202.oldboyedu.com ~]# mysql -e "SHOW SLAVE STATUS\G" | grep Gtid
                   Retrieved_Gtid_Set: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
                    Executed_Gtid_Set: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
        [root@mysql202.oldboyedu.com ~]# 
 
    mysql203.oldboyedu.com从库查看执行的位置点:
        [root@mysql203.oldboyedu.com ~]# mysql -uroot -poldboyedu -e "SHOW SLAVE STATUS\G" | grep Gtid
        mysql: [Warning] Using a password on the command line interface can be insecure.
                   Retrieved_Gtid_Set: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
                    Executed_Gtid_Set: b2a02b77-70c1-11eb-a390-000c29e297a4:1-2
        [root@mysql203.oldboyedu.com ~]# 
 
    综上所述，我们很容易发现基于GTID的主从复制更容易判断从库是否有延迟。直接对比主从的"Executed_Gtid_Set"是否一致即可！

9.mysqldump 备份工具的 "--set-gtid-purged" 参数

    我们先看下面命令的输出结果:
        [root@mysql201.oldboyedu.com ~]# mysqldump  -A > /oldboyedu/backup/full.sql
        Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
        store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# grep -i "@@GLOBAL.GTID_PURGED=" /oldboyedu/backup/full.sql 
        SET @@GLOBAL.GTID_PURGED='b2a02b77-70c1-11eb-a390-000c29e297a4:1-2';
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# mysqldump  -A --set-gtid-purged=OFF > /oldboyedu/backup/full.sql
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# grep -i "@@GLOBAL.GTID_PURGED=" /oldboyedu/backup/full.sql 
        [root@mysql201.oldboyedu.com ~]# 
        [root@mysql201.oldboyedu.com ~]# 
 
    综上所述，我们不难发现，如果手动添加了"--set-gtid-purged=OFF"参数后，在备份文件中就没有"@@GLOBAL.GTID_PURGED="这行命令啦。而这行命令对于GTID命令赋值很有用，因此建议大家备份时不要添加"--set-gtid-purged=OFF"参数哟~
 
    如果不设置"--set-gtid-purged=OFF"参数，则默认的参数是"ON"，也就是会设置GTID信息在备份文件中，这有助于基于GTID复制的IO线程准确获取最新的GTID事件信息。

三。基于 GTID 模式恢复数据实战案例

1. 模拟数据

    经过下面四个步骤的操作都很简单，其目的是生成多个二进制文件，而且每个二进制文件都有有效的DDL和DML语句。
    第一次执行DDL及DML语句，并滚动二进制日志:
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000004 |     1174 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-5 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> CREATE DATABASE IF NOT EXISTS hdp DEFAULT CHARACTER SET utf8mb4;
        Query OK, 1 row affected (0.28 sec)
 
        mysql> 
        mysql> CREATE TABLE IF NOT EXISTS hdp.t1(id int);
        Query OK, 0 rows affected (0.01 sec)
 
        mysql> 
        mysql> BEGIN;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> INSERT INTO hdp.t1 VALUES(1),(3),(5),(7);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> INSERT INTO hdp.t1 VALUES(2),(4),(6),(8);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> COMMIT;
        Query OK, 0 rows affected (0.05 sec)
 
        mysql> 
        mysql> FLUSH LOGS;
        Query OK, 0 rows affected (0.01 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000005 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-8 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
    第二次执行DDL及DML语句，并滚动二进制日志:
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        | oldboyedu-mysqld-binary.000005 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-8 |
        +----------------------------------+----------+--------------+------------------+------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql>  
        mysql> CREATE TABLE IF NOT EXISTS hdp.t2(id int);
        Query OK, 0 rows affected (0.29 sec)
 
        mysql> 
        mysql> BEGIN;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> INSERT INTO hdp.t2 VALUES(10),(30),(50),(70);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> INSERT INTO hdp.t2 VALUES(20),(40),(60),(80);
        Query OK, 4 rows affected (0.01 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> COMMIT;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000005 |      731 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-10 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> FLUSH LOGS;
        Query OK, 0 rows affected (0.29 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000006 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-10 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
    第三次执行DDL及DML语句，并滚动二进制日志:
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000006 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-10 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql>  
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000006 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-10 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> CREATE TABLE IF NOT EXISTS hdp.t3(id int);
        Query OK, 0 rows affected (0.01 sec)
 
        mysql> 
        mysql> BEGIN;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> INSERT INTO hdp.t3 VALUES(100),(300),(500),(700);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> INSERT INTO hdp.t3 VALUES(200),(400),(600),(800);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> COMMIT;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000006 |      731 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-12 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> FLUSH LOGS;
        Query OK, 0 rows affected (0.01 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000007 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-12 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
    第四次执行DDL及DML语句，并不滚动二进制日志，而是删除"hdp"数据库:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 5
        Server version: 5.7.31-log MySQL Community Server (GPL)
 
        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.
 
        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000007 |      194 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-12 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> CREATE TABLE IF NOT EXISTS hdp.t5(id int);
        Query OK, 0 rows affected (0.01 sec)
 
        mysql> 
        mysql> BEGIN;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> INSERT INTO hdp.t5 VALUES(111),(222),(333),(444);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> INSERT INTO hdp.t5 VALUES(10),(20),(30),(40);
        Query OK, 4 rows affected (0.00 sec)
        Records: 4  Duplicates: 0  Warnings: 0
 
        mysql> 
        mysql> COMMIT;
        Query OK, 0 rows affected (0.00 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000007 |      731 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-14 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql> 
        mysql> DROP DATABASE hdp;
        Query OK, 4 rows affected (0.30 sec)
 
        mysql> 
        mysql> SHOW MASTER STATUS;
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        | oldboyedu-mysqld-binary.000007 |      885 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-15 |
        +----------------------------------+----------+--------------+------------------+-------------------------------------------+
        1 row in set (0.00 sec)
 
        mysql>

2. 基于 GTID 截取事务日志用于数据恢复

    在第四步骤我们删除了"hdp"数据库，那么来我们的操作就是得恢复"hdp"数据库中的数据，我们分为以下几个步骤来操作:
        (1)确定起点的位置，我们得找到创建数据库的语句在二进制日志中的位置
            mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000004';  # 不难发现创建hdp数据库的位置为Pos=1239，其对应SESSION.GTID_NEXT的GTID的事务编号为"6"
            +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
            | Log_name                         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
            +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
            | oldboyedu-mysqld-binary.000004 |    4 | Format_desc    |         7 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                             |
            | oldboyedu-mysqld-binary.000004 |  123 | Previous_gtids |         7 |         154 |                                                                   |
            | oldboyedu-mysqld-binary.000004 |  154 | Gtid           |         7 |         219 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:1' |
            | oldboyedu-mysqld-binary.000004 |  219 | Query          |         7 |         357 | CREATE DATABASE IF NOT EXISTS hive DEFAULT CHARACTER SET utf8mb4  |
            | oldboyedu-mysqld-binary.000004 |  357 | Gtid           |         7 |         422 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:2' |
            | oldboyedu-mysqld-binary.000004 |  422 | Query          |         7 |         557 | CREATE DATABASE IF NOT EXISTS cdh DEFAULT CHARACTER SET utf8mb4   |
            | oldboyedu-mysqld-binary.000004 |  557 | Gtid           |         7 |         622 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:3' |
            | oldboyedu-mysqld-binary.000004 |  622 | Query          |         7 |         736 | CREATE TABLE IF NOT EXISTS hive.user(id int)                      |
            | oldboyedu-mysqld-binary.000004 |  736 | Gtid           |         7 |         801 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:4' |
            | oldboyedu-mysqld-binary.000004 |  801 | Query          |         7 |         869 | BEGIN                                                             |
            | oldboyedu-mysqld-binary.000004 |  869 | Table_map      |         7 |         916 | table_id: 112 (hive.user)                                         |
            | oldboyedu-mysqld-binary.000004 |  916 | Write_rows     |         7 |         966 | table_id: 112 flags: STMT_END_F                                   |
            | oldboyedu-mysqld-binary.000004 |  966 | Xid            |         7 |         997 | COMMIT /* xid=16 */                                               |
            | oldboyedu-mysqld-binary.000004 |  997 | Gtid           |         7 |        1062 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:5' |
            | oldboyedu-mysqld-binary.000004 | 1062 | Query          |         7 |        1174 | CREATE TABLE IF NOT EXISTS cdh.user(id int)                       |
            | oldboyedu-mysqld-binary.000004 | 1174 | Gtid           |         7 |        1239 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:6' |
            | oldboyedu-mysqld-binary.000004 | 1239 | Query          |         7 |        1374 | CREATE DATABASE IF NOT EXISTS hdp DEFAULT CHARACTER SET utf8mb4   |
            | oldboyedu-mysqld-binary.000004 | 1374 | Gtid           |         7 |        1439 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:7' |
            | oldboyedu-mysqld-binary.000004 | 1439 | Query          |         7 |        1549 | CREATE TABLE IF NOT EXISTS hdp.t1(id int)                         |
            | oldboyedu-mysqld-binary.000004 | 1549 | Gtid           |         7 |        1614 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:8' |
            | oldboyedu-mysqld-binary.000004 | 1614 | Query          |         7 |        1682 | BEGIN                                                             |
            | oldboyedu-mysqld-binary.000004 | 1682 | Table_map      |         7 |        1726 | table_id: 113 (hdp.t1)                                            |
            | oldboyedu-mysqld-binary.000004 | 1726 | Write_rows     |         7 |        1781 | table_id: 113 flags: STMT_END_F                                   |
            | oldboyedu-mysqld-binary.000004 | 1781 | Table_map      |         7 |        1825 | table_id: 113 (hdp.t1)                                            |
            | oldboyedu-mysqld-binary.000004 | 1825 | Write_rows     |         7 |        1880 | table_id: 113 flags: STMT_END_F                                   |
            | oldboyedu-mysqld-binary.000004 | 1880 | Xid            |         7 |        1911 | COMMIT /* xid=36 */                                               |
            | oldboyedu-mysqld-binary.000004 | 1911 | Rotate         |         7 |        1974 | oldboyedu-mysqld-binary.000005;pos=4                            |
            +----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
            27 rows in set (0.00 sec)
 
            mysql> 
 
        (2)确定结束的位置，我们得找到删除数据库的语句在二进制日志中的位置
            mysql> SHOW MASTER STATUS;
            +----------------------------------+----------+--------------+------------------+-------------------------------------------+
            | File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
            +----------------------------------+----------+--------------+------------------+-------------------------------------------+
            | oldboyedu-mysqld-binary.000007 |      885 |              |                  | ecaf563f-5345-11eb-a106-000c29820c67:1-15 |
            +----------------------------------+----------+--------------+------------------+-------------------------------------------+
            1 row in set (0.00 sec)
 
            mysql> 
            mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000007';  # 我们知道删除数据库的起始位置为Pos=796，我们还需要过滤掉记录该操作的GTID日志位置(即Pos=731)，因此得到的终点位置为Pos=731，对应的事务id为14。
            +----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
            | Log_name                         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
            +----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
            | oldboyedu-mysqld-binary.000007 |   4 | Format_desc    |         7 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                              |
            | oldboyedu-mysqld-binary.000007 | 123 | Previous_gtids |         7 |         194 | ecaf563f-5345-11eb-a106-000c29820c67:1-12                          |
            | oldboyedu-mysqld-binary.000007 | 194 | Gtid           |         7 |         259 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:13' |
            | oldboyedu-mysqld-binary.000007 | 259 | Query          |         7 |         369 | CREATE TABLE IF NOT EXISTS hdp.t5(id int)                          |
            | oldboyedu-mysqld-binary.000007 | 369 | Gtid           |         7 |         434 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:14' |
            | oldboyedu-mysqld-binary.000007 | 434 | Query          |         7 |         502 | BEGIN                                                              |
            | oldboyedu-mysqld-binary.000007 | 502 | Table_map      |         7 |         546 | table_id: 116 (hdp.t5)                                             |
            | oldboyedu-mysqld-binary.000007 | 546 | Write_rows     |         7 |         601 | table_id: 116 flags: STMT_END_F                                    |
            | oldboyedu-mysqld-binary.000007 | 601 | Table_map      |         7 |         645 | table_id: 116 (hdp.t5)                                             |
            | oldboyedu-mysqld-binary.000007 | 645 | Write_rows     |         7 |         700 | table_id: 116 flags: STMT_END_F                                    |
            | oldboyedu-mysqld-binary.000007 | 700 | Xid            |         7 |         731 | COMMIT /* xid=71 */                                                |
            | oldboyedu-mysqld-binary.000007 | 731 | Gtid           |         7 |         796 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:15' |
            | oldboyedu-mysqld-binary.000007 | 796 | Query          |         7 |         885 | DROP DATABASE hdp                                                  |
            +----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
            13 rows in set (0.00 sec)
 
            mysql> 
 
        (3)综上所述，起始位置是GTID是6，结束位置的GTID是14。但是这些GTID范围是从'oldboyedu-mysqld-binary.000004'到'oldboyedu-mysqld-binary.000007'，跨越了4个二进制日志文件，你也可以模拟更多的跨越文件。
            [root@docker201.oldboyedu.com ~]# cd /oldboyedu/logs/mysql23307/binlog/
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# ll
            总用量 32
            -rw-r----- 1 mysql mysql  177 2月   4 17:54 oldboyedu-mysqld-binary.000001
            -rw-r----- 1 mysql mysql  177 2月   4 17:59 oldboyedu-mysqld-binary.000002
            -rw-r----- 1 mysql mysql  177 2月   4 18:00 oldboyedu-mysqld-binary.000003
            -rw-r----- 1 mysql mysql 1974 2月   4 20:46 oldboyedu-mysqld-binary.000004
            -rw-r----- 1 mysql mysql  794 2月   4 20:49 oldboyedu-mysqld-binary.000005
            -rw-r----- 1 mysql mysql  794 2月   4 20:51 oldboyedu-mysqld-binary.000006
            -rw-r----- 1 mysql mysql  885 2月   4 20:58 oldboyedu-mysqld-binary.000007
            -rw-r----- 1 mysql mysql  483 2月   4 20:51 oldboyedu-mysqld-binary.index
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# mysqlbinlog --include-gtids='ecaf563f-5345-11eb-a106-000c29820c67:6-14' oldboyedu-mysqld-binary.000004 oldboyedu-mysqld-binary.000005 oldboyedu-mysqld-binary.000006 oldboyedu-mysqld-binary.000007 > /tmp/recover_hdp.log
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# ll /tmp/recover_hdp.log 
            -rw-r--r-- 1 root root 10563 2月   4 21:21 /tmp/recover_hdp.log
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# file /tmp/recover_hdp.log 
            /tmp/recover_hdp.log: ASCII text
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com ~]# cat /tmp/recover_hdp.log 
            /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
            /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
            DELIMITER /*!*/;
            # at 4
            #210204 18:00:33 server id 7  end_log_pos 123 CRC32 0x76297bdc  Start: binlog v 4, server v 5.7.31-log created 210204 18:00:33 at startup
            ROLLBACK/*!*/;
            BINLOG '
            wcUbYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAADBxRtgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            Adx7KXY=
            '/*!*/;
            # at 123
            #210204 18:00:33 server id 7  end_log_pos 154 CRC32 0x92f8d825  Previous-GTIDs
            # [empty]
            # at 154
            # at 219
            # at 357
            # at 422
            # at 557
            # at 622
            # at 736
            # at 801
            # at 869
            # at 916
            # at 966
            # at 997
            # at 1062
            # at 1174
            #210204 20:44:51 server id 7  end_log_pos 1239 CRC32 0x5e5c0e3e     GTID    last_committed=5    sequence_number=6   rbr_only=no
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:6'/*!*/;
            # at 1239
            #210204 20:44:51 server id 7  end_log_pos 1374 CRC32 0xf985d172     Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442691/*!*/;
            SET @@session.pseudo_thread_id=3/*!*/;
            SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
            SET @@session.sql_mode=1436549152/*!*/;
            SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
            /*!\C utf8 *//*!*/;
            SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
            SET @@session.lc_time_names=0/*!*/;
            SET @@session.collation_database=DEFAULT/*!*/;
            CREATE DATABASE IF NOT EXISTS hdp DEFAULT CHARACTER SET utf8mb4
            /*!*/;
            # at 1374
            #210204 20:45:05 server id 7  end_log_pos 1439 CRC32 0x673a125b     GTID    last_committed=6    sequence_number=7   rbr_only=no
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:7'/*!*/;
            # at 1439
            #210204 20:45:05 server id 7  end_log_pos 1549 CRC32 0x9063a6b8     Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442705/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t1(id int)
            /*!*/;
            # at 1549
            #210204 20:46:10 server id 7  end_log_pos 1614 CRC32 0xaa06fd48     GTID    last_committed=7    sequence_number=8   rbr_only=yes
            /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:8'/*!*/;
            # at 1614
            #210204 20:45:55 server id 7  end_log_pos 1682 CRC32 0xfd9e0f76     Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442755/*!*/;
            BEGIN
            /*!*/;
            # at 1682
            #210204 20:45:55 server id 7  end_log_pos 1726 CRC32 0xe51d20e7     Table_map: `hdp`.`t1` mapped to number 113
            # at 1726
            #210204 20:45:55 server id 7  end_log_pos 1781 CRC32 0x0f990eb8     Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            g+wbYBMHAAAALAAAAL4GAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAAecgHeU=
            g+wbYB4HAAAANwAAAPUGAAAAAHEAAAAAAAEAAgAB//4BAAAA/gMAAAD+BQAAAP4HAAAAuA6ZDw==
            '/*!*/;
            # at 1781
            #210204 20:46:04 server id 7  end_log_pos 1825 CRC32 0x525b9fea     Table_map: `hdp`.`t1` mapped to number 113
            # at 1825
            #210204 20:46:04 server id 7  end_log_pos 1880 CRC32 0xa8f8d8b1     Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            jOwbYBMHAAAALAAAACEHAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAAeqfW1I=
            jOwbYB4HAAAANwAAAFgHAAAAAHEAAAAAAAEAAgAB//4CAAAA/gQAAAD+BgAAAP4IAAAAsdj4qA==
            '/*!*/;
            # at 1880
            #210204 20:46:10 server id 7  end_log_pos 1911 CRC32 0xcb360eb0     Xid = 36
            COMMIT/*!*/;
            # at 1911
            #210204 20:46:14 server id 7  end_log_pos 1974 CRC32 0x0d0c33da     Rotate to oldboyedu-mysqld-binary.000005  pos: 4
            SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
            # at 4
            #210204 20:46:14 server id 7  end_log_pos 123 CRC32 0x3519dc45  Start: binlog v 4, server v 5.7.31-log created 210204 20:46:14
            BINLOG '
            luwbYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            AUXcGTU=
            '/*!*/;
            # at 123
            #210204 20:46:14 server id 7  end_log_pos 194 CRC32 0x795f5386  Previous-GTIDs
            # ecaf563f-5345-11eb-a106-000c29820c67:1-8
            # at 194
            #210204 20:49:03 server id 7  end_log_pos 259 CRC32 0xea454371  GTID    last_committed=0    sequence_number=1   rbr_only=no
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:9'/*!*/;
            # at 259
            #210204 20:49:03 server id 7  end_log_pos 369 CRC32 0x684da844  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442943/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t2(id int)
            /*!*/;
            # at 369
            #210204 20:49:41 server id 7  end_log_pos 434 CRC32 0xb7145ad5  GTID    last_committed=1    sequence_number=2   rbr_only=yes
            /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:10'/*!*/;
            # at 434
            #210204 20:49:26 server id 7  end_log_pos 502 CRC32 0x1911e8ed  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442966/*!*/;
            BEGIN
            /*!*/;
            # at 502
            #210204 20:49:26 server id 7  end_log_pos 546 CRC32 0x3babdcc4  Table_map: `hdp`.`t2` mapped to number 114
            # at 546
            #210204 20:49:26 server id 7  end_log_pos 601 CRC32 0x76055b6a  Write_rows: table id 114 flags: STMT_END_F
 
            BINLOG '
            Vu0bYBMHAAAALAAAACICAAAAAHIAAAAAAAEAA2hkcAACdDIAAQMAAcTcqzs=
            Vu0bYB4HAAAANwAAAFkCAAAAAHIAAAAAAAEAAgAB//4KAAAA/h4AAAD+MgAAAP5GAAAAalsFdg==
            '/*!*/;
            # at 601
            #210204 20:49:38 server id 7  end_log_pos 645 CRC32 0x20f22d52  Table_map: `hdp`.`t1` mapped to number 113
            # at 645
            #210204 20:49:38 server id 7  end_log_pos 700 CRC32 0x0630cdb3  Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            Yu0bYBMHAAAALAAAAIUCAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAAVIt8iA=
            Yu0bYB4HAAAANwAAALwCAAAAAHEAAAAAAAEAAgAB//4UAAAA/igAAAD+PAAAAP5QAAAAs80wBg==
            '/*!*/;
            # at 700
            #210204 20:49:41 server id 7  end_log_pos 731 CRC32 0x801b4f88  Xid = 43
            COMMIT/*!*/;
            # at 731
            #210204 20:49:55 server id 7  end_log_pos 794 CRC32 0xeb18bd7b  Rotate to oldboyedu-mysqld-binary.000006  pos: 4
            SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
            # at 4
            #210204 20:49:55 server id 7  end_log_pos 123 CRC32 0xf0651c2a  Start: binlog v 4, server v 5.7.31-log created 210204 20:49:55
            BINLOG '
            c+0bYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            ASocZfA=
            '/*!*/;
            # at 123
            #210204 20:49:55 server id 7  end_log_pos 194 CRC32 0x4e23ad9c  Previous-GTIDs
            # ecaf563f-5345-11eb-a106-000c29820c67:1-10
            # at 194
            #210204 20:50:37 server id 7  end_log_pos 259 CRC32 0xb038289c  GTID    last_committed=0    sequence_number=1   rbr_only=no
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:11'/*!*/;
            # at 259
            #210204 20:50:37 server id 7  end_log_pos 369 CRC32 0xd1bd6f93  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612443037/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t3(id int)
            /*!*/;
            # at 369
            #210204 20:51:08 server id 7  end_log_pos 434 CRC32 0x699bb1b6  GTID    last_committed=1    sequence_number=2   rbr_only=yes
            /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:12'/*!*/;
            # at 434
            #210204 20:50:53 server id 7  end_log_pos 502 CRC32 0xb4049482  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612443053/*!*/;
            BEGIN
            /*!*/;
            # at 502
            #210204 20:50:53 server id 7  end_log_pos 546 CRC32 0x02beea7e  Table_map: `hdp`.`t2` mapped to number 114
            # at 546
            #210204 20:50:53 server id 7  end_log_pos 601 CRC32 0x57ee4dbd  Write_rows: table id 114 flags: STMT_END_F
 
            BINLOG '
            re0bYBMHAAAALAAAACICAAAAAHIAAAAAAAEAA2hkcAACdDIAAQMAAX7qvgI=
            re0bYB4HAAAANwAAAFkCAAAAAHIAAAAAAAEAAgAB//5kAAAA/iwBAAD+9AEAAP68AgAAvU3uVw==
            '/*!*/;
            # at 601
            #210204 20:51:04 server id 7  end_log_pos 645 CRC32 0x841c8812  Table_map: `hdp`.`t1` mapped to number 113
            # at 645
            #210204 20:51:04 server id 7  end_log_pos 700 CRC32 0x8bd11c26  Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            uO0bYBMHAAAALAAAAIUCAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAARKIHIQ=
            uO0bYB4HAAAANwAAALwCAAAAAHEAAAAAAAEAAgAB//7IAAAA/pABAAD+WAIAAP4gAwAAJhzRiw==
            '/*!*/;
            # at 700
            #210204 20:51:08 server id 7  end_log_pos 731 CRC32 0xeb6ff1ef  Xid = 52
            COMMIT/*!*/;
            # at 731
            #210204 20:51:23 server id 7  end_log_pos 794 CRC32 0xced96af1  Rotate to oldboyedu-mysqld-binary.000007  pos: 4
            SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
            # at 4
            #210204 20:51:23 server id 7  end_log_pos 123 CRC32 0x5fd2e5ae  Start: binlog v 4, server v 5.7.31-log created 210204 20:51:23
            # Warning: this binlog is either in use or was not closed properly.
            BINLOG '
            y+0bYA8HAAAAdwAAAHsAAAABAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            Aa7l0l8=
            '/*!*/;
            # at 123
            #210204 20:51:23 server id 7  end_log_pos 194 CRC32 0xe03d79e4  Previous-GTIDs
            # ecaf563f-5345-11eb-a106-000c29820c67:1-12
            # at 194
            #210204 20:56:15 server id 7  end_log_pos 259 CRC32 0x05ec33da  GTID    last_committed=0    sequence_number=1   rbr_only=no
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:13'/*!*/;
            # at 259
            #210204 20:56:15 server id 7  end_log_pos 369 CRC32 0x11961a84  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612443375/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t5(id int)
            /*!*/;
            # at 369
            #210204 20:57:44 server id 7  end_log_pos 434 CRC32 0xb22e8220  GTID    last_committed=1    sequence_number=2   rbr_only=yes
            /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
            SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:14'/*!*/;
            # at 434
            #210204 20:57:16 server id 7  end_log_pos 502 CRC32 0xcd9824eb  Query   thread_id=5 exec_time=0 error_code=0
            SET TIMESTAMP=1612443436/*!*/;
            BEGIN
            /*!*/;
            # at 502
            #210204 20:57:16 server id 7  end_log_pos 546 CRC32 0x112c5ce1  Table_map: `hdp`.`t5` mapped to number 116
            # at 546
            #210204 20:57:16 server id 7  end_log_pos 601 CRC32 0xb1be8156  Write_rows: table id 116 flags: STMT_END_F
 
            BINLOG '
            LO8bYBMHAAAALAAAACICAAAAAHQAAAAAAAEAA2hkcAACdDUAAQMAAeFcLBE=
            LO8bYB4HAAAANwAAAFkCAAAAAHQAAAAAAAEAAgAB//5vAAAA/t4AAAD+TQEAAP68AQAAVoG+sQ==
            '/*!*/;
            # at 601
            #210204 20:57:34 server id 7  end_log_pos 645 CRC32 0x90393d97  Table_map: `hdp`.`t5` mapped to number 116
            # at 645
            #210204 20:57:34 server id 7  end_log_pos 700 CRC32 0x6a9c7ac7  Write_rows: table id 116 flags: STMT_END_F
 
            BINLOG '
            Pu8bYBMHAAAALAAAAIUCAAAAAHQAAAAAAAEAA2hkcAACdDUAAQMAAZc9OZA=
            Pu8bYB4HAAAANwAAALwCAAAAAHQAAAAAAAEAAgAB//4KAAAA/hQAAAD+HgAAAP4oAAAAx3qcag==
            '/*!*/;
            # at 700
            #210204 20:57:44 server id 7  end_log_pos 731 CRC32 0x63b34137  Xid = 71
            COMMIT/*!*/;
            SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
            DELIMITER ;
            # End of log file
            /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
            /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
            [root@docker201.oldboyedu.com ~]#

3. 恢复数据失败 - 由于 GTID 幂等性原因导致

[root@docker201.oldboyedu.com ~]# ll /tmp/recover_hdp.log 
-rw-r--r-- 1 root root 10563 2月   4 21:21 /tmp/recover_hdp.log
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.31-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdh                |
| cmdb               |
| hive               |
| mysql              |
| performance_schema |
| school             |
| sys                |
| world              |
| oldboyedu        |
| zabbix             |
+--------------------+
11 rows in set (0.00 sec)
 
mysql> 
mysql> SOURCE /tmp/recover_hdp.log
Query OK, 0 rows affected (0.00 sec)
 
...
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdh                |
| cmdb               |
| hive               |
| mysql              |
| performance_schema |
| school             |
| sys                |
| world              |
| oldboyedu        |
| zabbix             |
+--------------------+
11 rows in set (0.00 sec)
 
mysql> 
mysql> SELECT * FROM hdp.t5;
ERROR 1146 (42S02): Table 'hdp.t5' doesn't exist
mysql>

4.GTID 的幂等性解决方案

    幂等性:
        就是用户对于同一个操作发起的一次或者多次请求的结果是一致的，不会因为多次点击而产生副作用。
 
    开启GTID后，MySQL恢复binlog时，重复GTID事务不会再执行了。也就是说，我们基于GTID对二进制日志进行截取后，想要直接使用截取后的日志进行恢复是不可行的，这是因为数据库日志中已经有GTID的存在，因此并不会执行截取的GTID事件。
 
    综上所述，想要解决GTID幂等性的问题，可以在使用mysqlbinlog工具截取日志时添加"--skip-gtids"选项，就会跳过GTID信息，具体操作如下所示:
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# mysqlbinlog --skip-gtids --include-gtids='ecaf563f-5345-11eb-a106-000c29820c67:6-14' oldboyedu-mysqld-binary.000004 oldboyedu-mysqld-binary.000005 oldboyedu-mysqld-binary.000006 oldboyedu-mysqld-binary.000007 > /tmp/recover_hdp2.log
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# 
            [root@docker201.oldboyedu.com /oldboyedu/logs/mysql23307/binlog]# cd
            [root@docker201.oldboyedu.com ~]# 
            [root@docker201.oldboyedu.com ~]# ll -h /tmp/recover_hdp*
            -rw-r--r-- 1 root root 7.9K 2月   4 21:42 /tmp/recover_hdp2.log
            -rw-r--r-- 1 root root  11K 2月   4 21:42 /tmp/recover_hdp.log
            [root@docker201.oldboyedu.com ~]# 
            [root@docker201.oldboyedu.com ~]# cat /tmp/recover_hdp2.log 
            /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
            /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
            DELIMITER /*!*/;
            # at 4
            #210204 18:00:33 server id 7  end_log_pos 123 CRC32 0x76297bdc  Start: binlog v 4, server v 5.7.31-log created 210204 18:00:33 at startup
            ROLLBACK/*!*/;
            BINLOG '
            wcUbYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAADBxRtgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            Adx7KXY=
            '/*!*/;
            /*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
            # at 123
            # at 154
            # at 219
            # at 357
            # at 422
            # at 557
            # at 622
            # at 736
            # at 801
            # at 869
            # at 916
            # at 966
            # at 997
            # at 1062
            # at 1174
            # at 1239
            #210204 20:44:51 server id 7  end_log_pos 1374 CRC32 0xf985d172     Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442691/*!*/;
            SET @@session.pseudo_thread_id=3/*!*/;
            SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
            SET @@session.sql_mode=1436549152/*!*/;
            SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
            /*!\C utf8 *//*!*/;
            SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
            SET @@session.lc_time_names=0/*!*/;
            SET @@session.collation_database=DEFAULT/*!*/;
            CREATE DATABASE IF NOT EXISTS hdp DEFAULT CHARACTER SET utf8mb4
            /*!*/;
            # at 1374
            # at 1439
            #210204 20:45:05 server id 7  end_log_pos 1549 CRC32 0x9063a6b8     Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442705/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t1(id int)
            /*!*/;
            # at 1549
            # at 1614
            #210204 20:45:55 server id 7  end_log_pos 1682 CRC32 0xfd9e0f76     Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442755/*!*/;
            BEGIN
            /*!*/;
            # at 1682
            #210204 20:45:55 server id 7  end_log_pos 1726 CRC32 0xe51d20e7     Table_map: `hdp`.`t1` mapped to number 113
            # at 1726
            #210204 20:45:55 server id 7  end_log_pos 1781 CRC32 0x0f990eb8     Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            g+wbYBMHAAAALAAAAL4GAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAAecgHeU=
            g+wbYB4HAAAANwAAAPUGAAAAAHEAAAAAAAEAAgAB//4BAAAA/gMAAAD+BQAAAP4HAAAAuA6ZDw==
            '/*!*/;
            # at 1781
            #210204 20:46:04 server id 7  end_log_pos 1825 CRC32 0x525b9fea     Table_map: `hdp`.`t1` mapped to number 113
            # at 1825
            #210204 20:46:04 server id 7  end_log_pos 1880 CRC32 0xa8f8d8b1     Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            jOwbYBMHAAAALAAAACEHAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAAeqfW1I=
            jOwbYB4HAAAANwAAAFgHAAAAAHEAAAAAAAEAAgAB//4CAAAA/gQAAAD+BgAAAP4IAAAAsdj4qA==
            '/*!*/;
            # at 1880
            #210204 20:46:10 server id 7  end_log_pos 1911 CRC32 0xcb360eb0     Xid = 36
            COMMIT/*!*/;
            # at 1911
            #210204 20:46:14 server id 7  end_log_pos 1974 CRC32 0x0d0c33da     Rotate to oldboyedu-mysqld-binary.000005  pos: 4
            # at 4
            #210204 20:46:14 server id 7  end_log_pos 123 CRC32 0x3519dc45  Start: binlog v 4, server v 5.7.31-log created 210204 20:46:14
            BINLOG '
            luwbYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            AUXcGTU=
            '/*!*/;
            /*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
            # at 123
            # at 194
            # at 259
            #210204 20:49:03 server id 7  end_log_pos 369 CRC32 0x684da844  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442943/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t2(id int)
            /*!*/;
            # at 369
            # at 434
            #210204 20:49:26 server id 7  end_log_pos 502 CRC32 0x1911e8ed  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612442966/*!*/;
            BEGIN
            /*!*/;
            # at 502
            #210204 20:49:26 server id 7  end_log_pos 546 CRC32 0x3babdcc4  Table_map: `hdp`.`t2` mapped to number 114
            # at 546
            #210204 20:49:26 server id 7  end_log_pos 601 CRC32 0x76055b6a  Write_rows: table id 114 flags: STMT_END_F
 
            BINLOG '
            Vu0bYBMHAAAALAAAACICAAAAAHIAAAAAAAEAA2hkcAACdDIAAQMAAcTcqzs=
            Vu0bYB4HAAAANwAAAFkCAAAAAHIAAAAAAAEAAgAB//4KAAAA/h4AAAD+MgAAAP5GAAAAalsFdg==
            '/*!*/;
            # at 601
            #210204 20:49:38 server id 7  end_log_pos 645 CRC32 0x20f22d52  Table_map: `hdp`.`t1` mapped to number 113
            # at 645
            #210204 20:49:38 server id 7  end_log_pos 700 CRC32 0x0630cdb3  Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            Yu0bYBMHAAAALAAAAIUCAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAAVIt8iA=
            Yu0bYB4HAAAANwAAALwCAAAAAHEAAAAAAAEAAgAB//4UAAAA/igAAAD+PAAAAP5QAAAAs80wBg==
            '/*!*/;
            # at 700
            #210204 20:49:41 server id 7  end_log_pos 731 CRC32 0x801b4f88  Xid = 43
            COMMIT/*!*/;
            # at 731
            #210204 20:49:55 server id 7  end_log_pos 794 CRC32 0xeb18bd7b  Rotate to oldboyedu-mysqld-binary.000006  pos: 4
            # at 4
            #210204 20:49:55 server id 7  end_log_pos 123 CRC32 0xf0651c2a  Start: binlog v 4, server v 5.7.31-log created 210204 20:49:55
            BINLOG '
            c+0bYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            ASocZfA=
            '/*!*/;
            /*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
            # at 123
            # at 194
            # at 259
            #210204 20:50:37 server id 7  end_log_pos 369 CRC32 0xd1bd6f93  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612443037/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t3(id int)
            /*!*/;
            # at 369
            # at 434
            #210204 20:50:53 server id 7  end_log_pos 502 CRC32 0xb4049482  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612443053/*!*/;
            BEGIN
            /*!*/;
            # at 502
            #210204 20:50:53 server id 7  end_log_pos 546 CRC32 0x02beea7e  Table_map: `hdp`.`t2` mapped to number 114
            # at 546
            #210204 20:50:53 server id 7  end_log_pos 601 CRC32 0x57ee4dbd  Write_rows: table id 114 flags: STMT_END_F
 
            BINLOG '
            re0bYBMHAAAALAAAACICAAAAAHIAAAAAAAEAA2hkcAACdDIAAQMAAX7qvgI=
            re0bYB4HAAAANwAAAFkCAAAAAHIAAAAAAAEAAgAB//5kAAAA/iwBAAD+9AEAAP68AgAAvU3uVw==
            '/*!*/;
            # at 601
            #210204 20:51:04 server id 7  end_log_pos 645 CRC32 0x841c8812  Table_map: `hdp`.`t1` mapped to number 113
            # at 645
            #210204 20:51:04 server id 7  end_log_pos 700 CRC32 0x8bd11c26  Write_rows: table id 113 flags: STMT_END_F
 
            BINLOG '
            uO0bYBMHAAAALAAAAIUCAAAAAHEAAAAAAAEAA2hkcAACdDEAAQMAARKIHIQ=
            uO0bYB4HAAAANwAAALwCAAAAAHEAAAAAAAEAAgAB//7IAAAA/pABAAD+WAIAAP4gAwAAJhzRiw==
            '/*!*/;
            # at 700
            #210204 20:51:08 server id 7  end_log_pos 731 CRC32 0xeb6ff1ef  Xid = 52
            COMMIT/*!*/;
            # at 731
            #210204 20:51:23 server id 7  end_log_pos 794 CRC32 0xced96af1  Rotate to oldboyedu-mysqld-binary.000007  pos: 4
            # at 4
            #210204 20:51:23 server id 7  end_log_pos 123 CRC32 0x5fd2e5ae  Start: binlog v 4, server v 5.7.31-log created 210204 20:51:23
            # Warning: this binlog is either in use or was not closed properly.
            BINLOG '
            y+0bYA8HAAAAdwAAAHsAAAABAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
            Aa7l0l8=
            '/*!*/;
            /*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
            # at 123
            # at 194
            # at 259
            #210204 20:56:15 server id 7  end_log_pos 369 CRC32 0x11961a84  Query   thread_id=3 exec_time=0 error_code=0
            SET TIMESTAMP=1612443375/*!*/;
            CREATE TABLE IF NOT EXISTS hdp.t5(id int)
            /*!*/;
            # at 369
            # at 434
            #210204 20:57:16 server id 7  end_log_pos 502 CRC32 0xcd9824eb  Query   thread_id=5 exec_time=0 error_code=0
            SET TIMESTAMP=1612443436/*!*/;
            BEGIN
            /*!*/;
            # at 502
            #210204 20:57:16 server id 7  end_log_pos 546 CRC32 0x112c5ce1  Table_map: `hdp`.`t5` mapped to number 116
            # at 546
            #210204 20:57:16 server id 7  end_log_pos 601 CRC32 0xb1be8156  Write_rows: table id 116 flags: STMT_END_F
 
            BINLOG '
            LO8bYBMHAAAALAAAACICAAAAAHQAAAAAAAEAA2hkcAACdDUAAQMAAeFcLBE=
            LO8bYB4HAAAANwAAAFkCAAAAAHQAAAAAAAEAAgAB//5vAAAA/t4AAAD+TQEAAP68AQAAVoG+sQ==
            '/*!*/;
            # at 601
            #210204 20:57:34 server id 7  end_log_pos 645 CRC32 0x90393d97  Table_map: `hdp`.`t5` mapped to number 116
            # at 645
            #210204 20:57:34 server id 7  end_log_pos 700 CRC32 0x6a9c7ac7  Write_rows: table id 116 flags: STMT_END_F
 
            BINLOG '
            Pu8bYBMHAAAALAAAAIUCAAAAAHQAAAAAAAEAA2hkcAACdDUAAQMAAZc9OZA=
            Pu8bYB4HAAAANwAAALwCAAAAAHQAAAAAAAEAAgAB//4KAAAA/hQAAAD+HgAAAP4oAAAAx3qcag==
            '/*!*/;
            # at 700
            #210204 20:57:44 server id 7  end_log_pos 731 CRC32 0x63b34137  Xid = 71
            COMMIT/*!*/;
            DELIMITER ;
            # End of log file
            /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
            /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
            [root@docker201.oldboyedu.com ~]#

5. 再次尝试恢复数据成功

[root@docker201.oldboyedu.com ~]# ll -h /tmp/recover_hdp*
-rw-r--r-- 1 root root 7.9K 2月   4 21:42 /tmp/recover_hdp2.log
-rw-r--r-- 1 root root  11K 2月   4 21:42 /tmp/recover_hdp.log
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdh                |
| cmdb               |
| hive               |
| mysql              |
| performance_schema |
| school             |
| sys                |
| world              |
| oldboyedu        |
| zabbix             |
+--------------------+
11 rows in set (0.00 sec)
 
mysql> 
mysql> SET sql_log_bin=0;  # 临时关闭二进制日志功能，因为下面会基于咱们自己截取的二进制日志进行恢复！
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
mysql> SOURCE /tmp/recover_hdp2.log;
Query OK, 0 rows affected (0.00 sec)
 
...
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
mysql> SET sql_log_bin=1;  # 恢复数据后，千万别忘记开启二进制日志功能哈，否则下面执行的DDL或者DML语句将始终不会记录在二进制日志中。
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdh                |
| cmdb               |
| hdp                |
| hive               |
| mysql              |
| performance_schema |
| school             |
| sys                |
| world              |
| oldboyedu        |
| zabbix             |
+--------------------+
12 rows in set (0.00 sec)
 
mysql> 
mysql> SELECT * FROM hdp.t5;
+------+
| id   |
+------+
|  111 |
|  222 |
|  333 |
|  444 |
|   10 |
|   20 |
|   30 |
|   40 |
+------+
8 rows in set (0.00 sec)
 
mysql> 
mysql>

6.mysqlbinlog 工具有关 GTID 常用的参数说明:

mysqlbinlog --include-gtids=17d31e58-1b75-11ec-8f7c-000c29416eca:1695-1699 --exclude-gtids=17d31e58-1b75-11ec-8f7c-000c29416eca:1698-1699 --skip-gtids  oldboyedu_mysql_logbin.000012  > /tmp/oldboyedu-linux2021.sql
 
相关参数说明:
    --skip-gtids:
        跳过GTID信息的截取。
 
    --include-gtids:
        只截取关心的GTID范围。
 
    --exclude-gtids:
        排除指定GTID事件。

可能会遇到的错误

1.The replication receiver thread cannot start in AUTO_POSITION mode: the master has GTID_MODE = OFF_PERMISSIVE instead of ON.

报错原因:
    主库未开启GTID
 
解决方案:
    cat > /oldboyedu/etc/mysql57/3307/my.cnf <<EOF
    [mysqld]
    ...
 
    # 将其值设置为"on"则表示开启GTID功能。默认值为"OFF"，表示未开启。
    gtid-mode=ON
    # 启用强制GITD的一致性。即在启动MySQL实例之前，会校验GTID一致性的问题。默认值为"OFF"，表示未开启。
    enforce-gtid-consistency=ON
    # 强制从库记录二进制日志的一致性。换句话说，就是要求从库的GTID和主库的GTID要保持一致。
    log-slave-updates=1
    EOF

1632295628137

2.ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.

报错原因:
    从库未开启GTID
 
解决方案:
cat > /oldboyedu/etc/mysql57/3307/my.cnf <<EOF
[mysqld]
...
gtid-mode=ON
enforce-gtid-consistency=ON
EOF

1632295962795

3.[ERROR] GTID_MODE = ON requires ENFORCE_GTID_CONSISTENCY = ON.

报错原因:
    gtid-mode和enforce-gtid-consistency这两个参数必须同时开启!
 
解决方案:
    cat > /oldboyedu/etc/mysql57/3307/my.cnf <<EOF
    [mysqld]
    ...
    gtid-mode=ON
    enforce-gtid-consistency=ON
    EOF

1632296096952

谨此笔记，记录过往。凭君阅览，如能收益，莫大奢望。
MySQL运维篇
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
003、JDBC连接数据库详细教程指南

							
							

							
017、MySQL 事务详解

							
							

							
006、MySQL的全面优化

							
							

							
027、yum安装mysql

							
							

							
004、MySQL连接IDEA详细教程