通过GTID进行增量恢复
1、GTID 介绍
1)什么是GTID
GTID(Global Transaction ID)全局事务标识符。给予每一个已提交事务全局唯一的编号。目的在于能够实现主从自动定位和切换,不用像之前那样指定文件和位置。GTID使用冒号分隔的一对坐标表示。从MySQL5.6版本后开启支持。
2)GTID的格式与存储
①单个GTID
GTID与主库上提交的每个事务相关联,例如:
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23
前一部分是主库的server_uuid,后面一部分是主库上按提交事务的顺序确定的序列号,提交的事务序号从1开始。
GTID = server_uuid :transaction_id
②GTID集
GTID集是包括一个或多个单个GTID或GTID范围的集合,例如:
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321
上面的示例表示源自server_uuid为8eed0f5b-6f9b-11e9-94a9-005056a57a4e服务器的“第1”到“第321”个事务。
源自同一服务器的多个单GTID或GTID范围可以同时包含在由冒号分隔的单个表达式(不连续)中,例如:
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-3:11:47-49
3)开启GTID
#查看GTID开启前的设定
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
//修改配置文件
[root@localhost ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
#查看GTID开启后的一些相关设定
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
#通过“gtid_next=AUTOMATIC”可知GTID自动递增
4)mysql.gtid_executed表
#mysql.gtid_executed表结构
mysql> desc mysql.gtid_executed;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| source_uuid | char(36) | NO | PRI | NULL | |
| interval_start | bigint(20) | NO | PRI | NULL | |
| interval_end | bigint(20) | NO | | NULL | |
+----------------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
5)执行DDL和DML语句观察GTID变化(素材)
DDL一个语句产生一个gtid
mysql> create database db3;
Query OK, 1 row affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 | 310 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> use db3;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 471 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 632 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t3 (id int);
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 793 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
DML一个事务产生一个gtid
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 1128 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 1379 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
6)基于GTID进行查看binlog
mysql> show binlog events in 'mysql-bin.000004';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 133 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 133 | 154 | |
| mysql-bin.000004 | 154 | Gtid | 133 | 219 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:1' |
| mysql-bin.000004 | 219 | Query | 133 | 310 | create database db3 |
| mysql-bin.000004 | 310 | Gtid | 133 | 375 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:2' |
| mysql-bin.000004 | 375 | Query | 133 | 471 | use `db3`; create table t1 (id int) |
| mysql-bin.000004 | 471 | Gtid | 133 | 536 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:3' |
| mysql-bin.000004 | 536 | Query | 133 | 632 | use `db3`; create table t2 (id int) |
| mysql-bin.000004 | 632 | Gtid | 133 | 697 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:4' |
| mysql-bin.000004 | 697 | Query | 133 | 793 | use `db3`; create table t3 (id int) |
| mysql-bin.000004 | 793 | Gtid | 133 | 858 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:5' |
| mysql-bin.000004 | 858 | Query | 133 | 929 | BEGIN |
| mysql-bin.000004 | 929 | Table_map | 133 | 973 | table_id: 108 (db3.t1) |
| mysql-bin.000004 | 973 | Write_rows | 133 | 1013 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000004 | 1013 | Table_map | 133 | 1057 | table_id: 108 (db3.t1) |
| mysql-bin.000004 | 1057 | Write_rows | 133 | 1097 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000004 | 1097 | Xid | 133 | 1128 | COMMIT /* xid=20 */ |
| mysql-bin.000004 | 1128 | Gtid | 133 | 1193 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:6' |
| mysql-bin.000004 | 1193 | Query | 133 | 1264 | BEGIN |
| mysql-bin.000004 | 1264 | Table_map | 133 | 1308 | table_id: 109 (db3.t2) |
| mysql-bin.000004 | 1308 | Write_rows | 133 | 1348 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000004 | 1348 | Xid | 133 | 1379 | COMMIT /* xid=25 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
22 rows in set (0.00 sec)
7)GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复的GTID事务不会再执行,意思是假如多个GTID中,存在记录相同的语句,则不会执行,这就有可能导致恢复数据时恢复失败(原因:语句缺失)。
这就需要在进行binlog备份时,在语句中添加**–skip-gtids**参数,表示跳过GTID幂等性。
2、恢复示例
1)正常恢复
#此时将db3库删除
mysql> drop database db3;
Query OK, 3 rows affected (0.01 sec)
mysql> show binlog events in 'mysql-bin.000004'; #查看GTID变化
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 133 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 133 | 154 | |
| mysql-bin.000004 | 154 | Gtid | 133 | 219 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:1' |
| mysql-bin.000004 | 219 | Query | 133 | 310 | create database db3 |
| mysql-bin.000004 | 310 | Gtid | 133 | 375 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:2' |
| mysql-bin.000004 | 375 | Query | 133 | 471 | use `db3`; create table t1 (id int) |
| mysql-bin.000004 | 471 | Gtid | 133 | 536 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:3' |
| mysql-bin.000004 | 536 | Query | 133 | 632 | use `db3`; create table t2 (id int) |
| mysql-bin.000004 | 632 | Gtid | 133 | 697 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:4' |
| mysql-bin.000004 | 697 | Query | 133 | 793 | use `db3`; create table t3 (id int) |
| mysql-bin.000004 | 793 | Gtid | 133 | 858 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:5' |
| mysql-bin.000004 | 858 | Query | 133 | 929 | BEGIN |
| mysql-bin.000004 | 929 | Table_map | 133 | 973 | table_id: 108 (db3.t1) |
| mysql-bin.000004 | 973 | Write_rows | 133 | 1013 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000004 | 1013 | Table_map | 133 | 1057 | table_id: 108 (db3.t1) |
| mysql-bin.000004 | 1057 | Write_rows | 133 | 1097 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000004 | 1097 | Xid | 133 | 1128 | COMMIT /* xid=20 */ |
| mysql-bin.000004 | 1128 | Gtid | 133 | 1193 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:6' |
| mysql-bin.000004 | 1193 | Query | 133 | 1264 | BEGIN |
| mysql-bin.000004 | 1264 | Table_map | 133 | 1308 | table_id: 109 (db3.t2) |
| mysql-bin.000004 | 1308 | Write_rows | 133 | 1348 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000004 | 1348 | Xid | 133 | 1379 | COMMIT /* xid=25 */ |
| mysql-bin.000004 | 1379 | Gtid | 133 | 1444 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:7' |
| mysql-bin.000004 | 1444 | Query | 133 | 1533 | drop database db3 |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
24 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 1533 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#可以看到最新的删除语句是c4d1a502-b360-11ec-afa4-000c2967ad99:7
//按照GTID进行binlog备份
//通过上方查询GTID可知,“序号7为删除库语句”,因此需要跳过删除语句则需要选取GTID小于7的二进制日志
//最后选取了“6”
mysqlbinlog --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:1-6' /var/lib/mysql/mysql-bin.000004 > /backup/gtid_test1.sql
[root@localhost ~]# ll /backup/gtid_test1.sql //查看创建备份结果
-rw-r--r-- 1 root root 5050 May 1 22:57 gtid_test1.sql
#进行恢复
mysql> source /backup/gtid_test1.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1049 (42000): Unknown database 'db3'
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#通过观察,没有恢复成功
恢复失败原因
观察GTID可知,序号“2”-“4”均是存在“use db3”语句,恢复失败的原因就在这,可以看到下面图片中,在恢复的时候,出现了不知道db3库的错误,这原因在于GTID的幂等性(上面有提及和解释)。
在binlog备份语句中添加–skip-gtids语句
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:1-6' /var/lib/mysql/mysql-bin.000004 > /backup/gtid_test1.sql
//备份文件名相同默认将原本的覆盖
[root@localhost ~]# ll /backup/gtid_test1.sql //查看备份结果
-rw-r--r-- 1 root root 3670 May 1 23:07 /backup/gtid_test1.sql
再次恢复
mysql> source /backup/gtid_test1.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#可以看到这次恢复成功了
2)跨binlog文档恢复
①素材
第一次操作
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 | 1379 | | | 6181523d-bc2e-11ea-a78b-000c29221146:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create database gtid;
Query OK, 1 row affected (0.01 sec)
mysql> use gtid;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000007 | 194 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
第二次操作
mysql> create table t2(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000008 | 194 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-12 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
第三次操作
mysql> create table t3(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> drop database gtid; //误操作:误删gtid库
Query OK, 3 rows affected (0.01 sec)
②恢复
mysql> show binary logs; //查看当前所有binlog日志
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 1556 |
| mysql-bin.000005 | 217 |
| mysql-bin.000006 | 815 |
| mysql-bin.000007 | 656 |
| mysql-bin.000008 | 609 |
+------------------+-----------+
8 rows in set (0.00 sec)
#根据刷新日志次数判断最初创建gtid库的语句在“mysql-bin.000006”中
mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 133 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000006 | 123 | Previous_gtids | 133 | 194 | c4d1a502-b360-11ec-afa4-000c2967ad99:1-7 |
| mysql-bin.000006 | 194 | Gtid | 133 | 259 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:8' |
| mysql-bin.000006 | 259 | Query | 133 | 353 | create database gtid |
| mysql-bin.000006 | 353 | Gtid | 133 | 418 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:9' |
| mysql-bin.000006 | 418 | Query | 133 | 515 | use `gtid`; create table t1(id int) |
| mysql-bin.000006 | 515 | Gtid | 133 | 580 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:10' |
| mysql-bin.000006 | 580 | Query | 133 | 652 | BEGIN |
| mysql-bin.000006 | 652 | Table_map | 133 | 697 | table_id: 108 (gtid.t1) |
| mysql-bin.000006 | 697 | Write_rows | 133 | 737 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000006 | 737 | Xid | 133 | 768 | COMMIT /* xid=13 */ |
| mysql-bin.000006 | 768 | Rotate | 133 | 815 | mysql-bin.000007;pos=4 |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
12 rows in set (0.00 sec)
#通过查看可以发现“create database gtid”创建gtid库的语句在“c4d1a502-b360-11ec-afa4-000c2967ad99:8”中
#由于进行误删操作后没有进行刷新日志操作,因此删除gtid库的语句肯定位于最后一个binlog中,因此查看“mysql-bin.000008”
mysql> show binlog events in 'mysql-bin.000008';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000008 | 4 | Format_desc | 133 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids | 133 | 194 | c4d1a502-b360-11ec-afa4-000c2967ad99:1-12 |
| mysql-bin.000008 | 194 | Gtid | 133 | 259 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:13' |
| mysql-bin.000008 | 259 | Query | 133 | 356 | use `gtid`; create table t3(id int) |
| mysql-bin.000008 | 356 | Gtid | 133 | 421 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:14' |
| mysql-bin.000008 | 421 | Query | 133 | 493 | BEGIN |
| mysql-bin.000008 | 493 | Table_map | 133 | 538 | table_id: 110 (gtid.t3) |
| mysql-bin.000008 | 538 | Write_rows | 133 | 578 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000008 | 578 | Xid | 133 | 609 | COMMIT /* xid=25 */ |
| mysql-bin.000008 | 609 | Gtid | 133 | 674 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:15' |
| mysql-bin.000008 | 674 | Query | 133 | 766 | drop database gtid |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
11 rows in set (0.00 sec)
#通过查看可以发现“drop database gtid”删除gtid库的语句在“c4d1a502-b360-11ec-afa4-000c2967ad99:15”中,由于需要忽略删除gtid库操作,因此选取上一个GTID“14”作为结尾
//根据上述两次查看操作的思路,可以推算出GTID范围为“8-14”,binlog文件范围为“6-8”
//进行binlog备份操作
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:8-14' /var/lib/mysql/mysql-bin.000006 /var/lib/mysql/mysql-bin.000007 /var/lib/mysql/mysql-bin.000008 > /backup/gtid_test2.sql
[root@localhost ~]# ll /backup/gtid_test2.sql //查看备份结果
-rw-r--r-- 1 root root 4900 May 2 01:28 /backup/gtid_test2.sql
#恢复数据
mysql> source /backup/gtid_test2.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#可以看出恢复没有报错,大概已经成功
#查看恢复结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 |
| gtid |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use gtid;
Database changed
mysql> show tables;
+----------------+
| Tables_in_gtid |
+----------------+
| t1 |
| t2 |
| t3 |
+----------------+
3 rows in set (0.01 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
#可以看出全部数据完好无损全部恢复成功
此时再次查看binlog“mysql-bin.000008”
mysql> show binlog events in 'mysql-bin.000008';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000008 | 4 | Format_desc | 133 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids | 133 | 194 | c4d1a502-b360-11ec-afa4-000c2967ad99:1-12 |
| mysql-bin.000008 | 194 | Gtid | 133 | 259 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:13' |
| mysql-bin.000008 | 259 | Query | 133 | 356 | use `gtid`; create table t3(id int) |
| mysql-bin.000008 | 356 | Gtid | 133 | 421 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:14' |
| mysql-bin.000008 | 421 | Query | 133 | 493 | BEGIN |
| mysql-bin.000008 | 493 | Table_map | 133 | 538 | table_id: 110 (gtid.t3) |
| mysql-bin.000008 | 538 | Write_rows | 133 | 578 | table_id: 110 flags: STMT_END_F |
| mysql-bin.000008 | 578 | Xid | 133 | 609 | COMMIT /* xid=25 */ |
| mysql-bin.000008 | 609 | Gtid | 133 | 674 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:15' |
| mysql-bin.000008 | 674 | Query | 133 | 766 | drop database gtid |
| mysql-bin.000008 | 766 | Gtid | 133 | 831 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:16' |
| mysql-bin.000008 | 831 | Query | 133 | 925 | create database gtid |
| mysql-bin.000008 | 925 | Gtid | 133 | 990 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:17' |
| mysql-bin.000008 | 990 | Query | 133 | 1087 | use `gtid`; create table t1(id int) |
| mysql-bin.000008 | 1087 | Gtid | 133 | 1152 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:18' |
| mysql-bin.000008 | 1152 | Query | 133 | 1224 | BEGIN |
| mysql-bin.000008 | 1224 | Table_map | 133 | 1269 | table_id: 112 (gtid.t1) |
| mysql-bin.000008 | 1269 | Write_rows | 133 | 1309 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000008 | 1309 | Xid | 133 | 1340 | COMMIT /* xid=61 */ |
| mysql-bin.000008 | 1340 | Gtid | 133 | 1405 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:19' |
| mysql-bin.000008 | 1405 | Query | 133 | 1502 | use `gtid`; create table t2(id int) |
| mysql-bin.000008 | 1502 | Gtid | 133 | 1567 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:20' |
| mysql-bin.000008 | 1567 | Query | 133 | 1639 | BEGIN |
| mysql-bin.000008 | 1639 | Table_map | 133 | 1684 | table_id: 113 (gtid.t2) |
| mysql-bin.000008 | 1684 | Write_rows | 133 | 1724 | table_id: 113 flags: STMT_END_F |
| mysql-bin.000008 | 1724 | Xid | 133 | 1755 | COMMIT /* xid=70 */ |
| mysql-bin.000008 | 1755 | Gtid | 133 | 1820 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:21' |
| mysql-bin.000008 | 1820 | Query | 133 | 1917 | use `gtid`; create table t3(id int) |
| mysql-bin.000008 | 1917 | Gtid | 133 | 1982 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:22' |
| mysql-bin.000008 | 1982 | Query | 133 | 2054 | BEGIN |
| mysql-bin.000008 | 2054 | Table_map | 133 | 2099 | table_id: 114 (gtid.t3) |
| mysql-bin.000008 | 2099 | Write_rows | 133 | 2139 | table_id: 114 flags: STMT_END_F |
| mysql-bin.000008 | 2139 | Xid | 133 | 2170 | COMMIT /* xid=79 */ |
| mysql-bin.000008 | 2170 | Stop | 133 | 2193 | |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
35 rows in set (0.00 sec)
#通过观察发现,GTID序号从恢复前的“15”,一直递增到现在的“22”,原因在于GTID将刚刚的恢复语句作为普通的DDL和DML语句一起计算
让GTID忽略恢复语句
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /backup/gtid_test2.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#在恢复前通过“set sql_log_bin=0”将binlog关闭,恢复之后再通过“set sql_log_bin=1”将binlog打开,这样就可以让GTID忽略恢复语句
3)忽略GTID恢复(跳过中间某个GTID)
重置日志(日志太多太复杂了)【生产环境不要乱搞】
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#可以看到binlog已恢复为1
①素材
由于GTID对插入数据语句和删除数据语句进行了加密,为了后续恢复时更好的区分GTID,后面每进行一次DDL或DML语句,就查看一次GTID序号变化
mysql> create database it DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 | 334 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
#创建it库,GTID为“1”
mysql> use it; //切换it库
Database changed
mysql> CREATE TABLE `Student` ( `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名', `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄', `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 834 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#创建Student表,GTID为“2”
mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from Student;
+-----+--------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+--------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
+-----+--------+------+------+-----------------+
4 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1205 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#插入四条语句,GTID为“3”
mysql> INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from Student;
+-----+----------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+----------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
| 5 | xumubin | 男 | 29 | 中文专业 |
| 6 | wangzhao | 男 | 21 | 导弹专业 |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 516 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#再次插入两条数据,GTID为“4”
模拟误删除操作,删除“Sno>4”的数据(记住此次操作的GTID)【极其重要】
mysql> delete from Student where Sno > 4;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from Student;
+-----+--------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+--------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
+-----+--------+------+------+-----------------+
4 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 838 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#删除“Sno>4”的两条数据,GTID为“5”
mysql> INSERT INTO Student values(0007,'JohnnyG','男',22,'计算机专业'),(0008,'Fang','女',21,'传媒专业');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from Student;
+-----+---------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+---------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
| 7 | JohnnyG | 男 | 22 | 计算机专业 |
| 8 | Fang | 女 | 21 | 传媒专业 |
+-----+---------+------+------+-----------------+
6 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1159 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#删除“Sno>4”的两条数据后,再次插入序号为“7”和“8”的两条数据,GTID为“6”
模拟误删除操作,将it库删除
mysql> drop database it;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1310 | | | c4d1a502-b360-11ec-afa4-000c2967ad99:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#此时模拟误删除操作(删除it库),GTID为“7”
②恢复
//进行binlog备份
//思路一:已知中间删除两条数据的语句的GTID为“5”,因此使用冒号忽略“5”
//思路二:由于最后删除it库的GTID为“7”,因此往前取“6”,忽略“7”
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:1-4:6' /var/lib/mysql/mysql-bin.000001 -r /backup/it_test1.sql
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /backup/it_test1.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#在恢复前通过“set sql_log_bin=0”将binlog关闭,恢复之后再通过“set sql_log_bin=1”将binlog打开,这样就可以让GTID忽略恢复语句
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 |
| gtid |
| it |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.01 sec)
mysql> use it;
Database changed
mysql> show tables;
+--------------+
| Tables_in_it |
+--------------+
| Student |
+--------------+
1 row in set (0.00 sec)
mysql> select * from Student;
+-----+----------+------+------+-----------------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----+----------+------+------+-----------------+
| 1 | 陆亚 | 男 | 24 | 计算机网络 |
| 2 | tom | 男 | 26 | 英语 |
| 3 | 张阳 | 男 | 21 | 物流管理 |
| 4 | alex | 女 | 22 | 电子商务 |
| 5 | xumubin | 男 | 29 | 中文专业 |
| 6 | wangzhao | 男 | 21 | 导弹专业 |
| 7 | JohnnyG | 男 | 22 | 计算机专业 |
| 8 | Fang | 女 | 21 | 传媒专业 |
+-----+----------+------+------+-----------------+
8 rows in set (0.00 sec)
#可以看到最后结果是所有数据都恢复成功(包括中间误删的两条数据)