MySQL中的gtid数据恢复

1. 参数设置

vim /etc/my.cnf
# 开启binlog功能
log_bin=/data/binlog/mysql-bin
#开启gtid
gtid-mode=on
enforce-gtid-consistency=true
# 取消自动提交
autocommit=0

2. 模拟错误操作

create database db charset utf8; 
use db;
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=3;
commit;
delete from t1 where id=4;
commit;
insert into t1 values(7),(8),(9);
commit;
drop database db;

3. 故障恢复

1）查看当前使用的binlog文件

3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000005 |     1785 |              |                  | 43d8f582-e6cb-11ec-abd7-0242ac110003:1-12 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

2）查看事件

3306 [(none)]>3306 [(none)]>show binlog events in 'mysql-bin.000005';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000005 |    4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                              |
| mysql-bin.000005 |  123 | Previous_gtids |         6 |         194 | 43d8f582-e6cb-11ec-abd7-0242ac110003:1-5                           |
| mysql-bin.000005 |  194 | Gtid           |         6 |         259 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:6'  |
| mysql-bin.000005 |  259 | Query          |         6 |         360 | create database db charset utf8                                    |
| mysql-bin.000005 |  360 | Gtid           |         6 |         425 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:7'  |
| mysql-bin.000005 |  425 | Query          |         6 |         519 | use `db`; create table t1 (id int)                                 |
| mysql-bin.000005 |  519 | Gtid           |         6 |         584 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:8'  |
| mysql-bin.000005 |  584 | Query          |         6 |         654 | BEGIN                                                              |
| mysql-bin.000005 |  654 | Table_map      |         6 |         697 | table_id: 108 (db.t1)                                              |
| mysql-bin.000005 |  697 | Write_rows     |         6 |         747 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000005 |  747 | Table_map      |         6 |         790 | table_id: 108 (db.t1)                                              |
| mysql-bin.000005 |  790 | Write_rows     |         6 |         840 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000005 |  840 | Xid            |         6 |         871 | COMMIT /* xid=13 */                                                |
| mysql-bin.000005 |  871 | Gtid           |         6 |         936 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:9'  |
| mysql-bin.000005 |  936 | Query          |         6 |        1006 | BEGIN                                                              |
| mysql-bin.000005 | 1006 | Table_map      |         6 |        1049 | table_id: 108 (db.t1)                                              |
| mysql-bin.000005 | 1049 | Update_rows    |         6 |        1095 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000005 | 1095 | Xid            |         6 |        1126 | COMMIT /* xid=16 */                                                |
| mysql-bin.000005 | 1126 | Gtid           |         6 |        1191 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:10' |
| mysql-bin.000005 | 1191 | Query          |         6 |        1261 | BEGIN                                                              |
| mysql-bin.000005 | 1261 | Table_map      |         6 |        1304 | table_id: 108 (db.t1)                                              |
| mysql-bin.000005 | 1304 | Delete_rows    |         6 |        1344 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000005 | 1344 | Xid            |         6 |        1375 | COMMIT /* xid=18 */                                                |
| mysql-bin.000005 | 1375 | Gtid           |         6 |        1440 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:11' |
| mysql-bin.000005 | 1440 | Query          |         6 |        1510 | BEGIN                                                              |
| mysql-bin.000005 | 1510 | Table_map      |         6 |        1553 | table_id: 108 (db.t1)                                              |
| mysql-bin.000005 | 1553 | Write_rows     |         6 |        1603 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000005 | 1603 | Xid            |         6 |        1634 | COMMIT /* xid=20 */                                                |
| mysql-bin.000005 | 1634 | Gtid           |         6 |        1699 | SET @@SESSION.GTID_NEXT= '43d8f582-e6cb-11ec-abd7-0242ac110003:12' |
| mysql-bin.000005 | 1699 | Query          |         6 |        1785 | drop database db                                                   |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
30 rows in set (0.00 sec)

3）具体分析:

GTID-6创建数据库db
GTID-7建表
GTID-8插入数据”1-6“
GTID-9将”3“改成"30"
GTID-10删除数据”4“
GTID-11插入数据"7-9"
GTID-12删库

其中删除”4“和删库都是误操作，因此我们需要恢复GTID6-9和GTID-11的数据即可。

4）导出GTID的SQL文件

[root@bff8617b4afc /]# mysqlbinlog --skip-gtids --include-gtids='43d8f582-e6cb-11ec-abd7-0242ac110003:6-11' --exclude-gtids='43d8f582-e6cb-11ec-abd7-0242ac110003:10' /data/binlog/mysql-bin.000005 >/tmp/gtid.sql

由于GTID幂等性的检查，6-11的事务已经做过了，在导出SQL文件时，忽略原有的GTID信息，恢复时生成最新的GTID信息，所以此时用到–skip-gtids参数。

5）恢复原有数据

3306 [(none)]>source /tmp/gtid.sql

6）确认恢复成功

3306 [db]>select * from db.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|   30 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

4.总结

1）确认参数设置。
二进制日志开启；
gtid启用；
取消自动提交。

2）确认当前binlog文件。

3）具体分析应恢复的对应gtid的SQL文件。