本文最后更新于 66 天前，其中的信息可能已经过时，如有错误请发送邮件到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
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
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