mysql 基于GTID方式的bin-log日志恢复数据

如果想通过 mysql 的 binlog 恢复数据，首先要开启 binlog 。这里搭建一个测试的环境，了解一下 mysql binlog 是如何恢复数据库的。原理比较简单，binlog 会存储mysql中变化的数据，比如你创建了一个数据库，写入了一些数据，这些都会存储在 mysql 的 binlog 中。

需要恢复的时候就找到，两个位置，一个起始位置，一个结束的位置。结束的位置，一半是数据被破坏或者删除前的位置。mysql 8 默认已经开启了 binlog


mysql>  show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/master-bin       |
| log_bin_index                   | /var/lib/mysql/master-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
6 rows in set (0.01 sec)

2 . 执行重置（reset master)后，可以看到之前的 binlog 文件已经被删除了，产生一个新的 binlog 文件。

可以查看一下这个文件的内容


mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       962 |
| master-bin.000002 |       242 |
| master-bin.000003 |       242 |
| master-bin.000004 |       242 |
| master-bin.000005 |       401 |
| master-bin.000006 |       194 |
+-------------------+-----------+
6 rows in set (0.00 sec)


mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       962 |
| master-bin.000002 |       242 |
| master-bin.000003 |       242 |
| master-bin.000004 |       242 |
| master-bin.000005 |       401 |
| master-bin.000006 |       194 |
+-------------------+-----------+
6 rows in set (0.00 sec)
 
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

可以查看一下这个文件的内容


[root@localhost mysql]# ls -al
 
-rw-r-----   1 mysql mysql      154 8月   8 16:07 master-bin.000001
-rw-r-----   1 mysql mysql       20 8月   8 16:07 master-bin.index


[root@localhost mysql]# mysqlbinlog master-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220808 16:07:41 server id 1  end_log_pos 123 CRC32 0xadec6205 	Start: binlog v 4, server v 5.7.35-log created 220808 16:07:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TcTwYg8BAAAAdwAAAHsAAAABAAQANS43LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABNxPBiEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AQVi7K0=
'/*!*/;
# at 123
#220808 16:07:41 server id 1  end_log_pos 154 CRC32 0x5c15c4eb 	Previous-GTIDs
# [empty]
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*/;

这个时候，我们添加一些数据


mysql> create database test;
Query OK, 1 row affected (0.00 sec)
 
mysql> use test;
Database changed
mysql> create table jettech01(id int,name char);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into jettech01 value(3,'c');
Query OK, 1 row affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from jettech01;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

这个时候，不小心删除了，数据库 test


mysql> drop database test;
Query OK, 1 row affected (0.01 sec)

binlog 大小没变还是没删除之前的数据都在里面


[root@localhost mysql]# ls -al
 
-rw-r-----   1 mysql mysql      913 8月   8 16:10 master-bin.000001
-rw-r-----   1 mysql mysql       20 8月   8 16:09 master-bin.index

3 现在就需要解决一个实际问题，怎么恢复这个数据库

首先为了防止干扰，执行 flush logs ，产生一个新binlog 文件


mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |      1070 |
+-------------------+-----------+
1 row in set (0.00 sec)
 
mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000001 |     1070 | test,test1   | mysql,performance_schema,information_schema,sys |                   |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (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 |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000002 |      154 | test,test1   | mysql,performance_schema,information_schema,sys |                   |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

系统文件：


[root@localhost mysql]# cat master-bin.index 
./master-bin.000001
./master-bin.000002
[root@localhost mysql]# ls -al master-bin.*
-rw-r----- 1 mysql mysql 1118 8月   8 16:12 master-bin.000001
-rw-r----- 1 mysql mysql  154 8月   8 16:12 master-bin.000002
-rw-r----- 1 mysql mysql   40 8月   8 16:12 master-bin.index

4.恢复数据，首先要找到数据在哪里被删除了。


[root@localhost mysql]# mysqlbinlog master-bin.000001 | grep -n  "drop database"
76:drop database test

可以看到在 76 行的地方有个删除语句。终可以找到两个地方

数据需要恢复的起始位置
数据需要恢复的结束位置

这里起始的位置就找创建数据库的位置，结束的位置就找删除数据库的位置。


# at 219
#220808 16:09:45 server id 1  end_log_pos 313 CRC32 0x781d4308 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1659946185/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
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 test
/*!*/;
 36 # at 313
 37 #220808 16:09:54 server id 1  end_log_pos 378 CRC32 0xed8705a1  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
 38 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 39 # at 378
 40 #220808 16:09:54 server id 1  end_log_pos 492 CRC32 0x372bd559  Query   thread_id=6     exec_time=0     error_code=0
 41 use `test`/*!*/;
 42 SET TIMESTAMP=1659946194/*!*/;
 43 create table jettech01(id int,name char)
 44 /*!*/;
 45 # at 492
 46 #220808 16:09:59 server id 1  end_log_pos 557 CRC32 0xf2b51311  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
 47 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 48 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 49 # at 557
 50 #220808 16:09:59 server id 1  end_log_pos 629 CRC32 0x5755f64f  Query   thread_id=6     exec_time=0     error_code=0
 51 SET TIMESTAMP=1659946199/*!*/;
 52 BEGIN
 53 /*!*/;
 54 # at 629
 55 #220808 16:09:59 server id 1  end_log_pos 684 CRC32 0xc59d8d91  Table_map: `test`.`jettech01` mapped to number 113
 56 # at 684
 57 #220808 16:09:59 server id 1  end_log_pos 726 CRC32 0x9b8b6300  Write_rows: table id 113 flags: STMT_END_F
 58 
 59 BINLOG '
 60 18TwYhMBAAAANwAAAKwCAAAAAHEAAAAAAAEABHRlc3QACWpldHRlY2gwMQACA/4C/gEDkY2dxQ==
 61 18TwYh4BAAAAKgAAANYCAAAAAHEAAAAAAAEAAgAC//wDAAAAAWMAY4ub
 62 '/*!*/;
 63 # at 726
 64 #220808 16:09:59 server id 1  end_log_pos 757 CRC32 0x86c37763  Xid = 399
 65 COMMIT/*!*/;
 66 # at 757
 67 #220808 16:10:10 server id 1  end_log_pos 822 CRC32 0xda41b64a  Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no
 68 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 69 # at 822
 70 #220808 16:10:10 server id 1  end_log_pos 913 CRC32 0xc428bae4  Query   thread_id=6     exec_time=0     error_code=0
 71 SET TIMESTAMP=1659946210/*!*/;
 72 SET @@session.time_zone='SYSTEM'/*!*/;
 73 flush privileges
 74 /*!*/;
 75 # at 913
 76 #220808 16:11:05 server id 1  end_log_pos 978 CRC32 0x4ee77a13  Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=no
 77 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 78 # at 978
 79 #220808 16:11:05 server id 1  end_log_pos 1070 CRC32 0x0ec87860         Query   thread_id=6     exec_time=0     error_code=0
 80 SET TIMESTAMP=1659946265/*!*/;
 81 drop database test
 82 /*!*/;
 83 # at 1070

start_position:219

end_position:978

恢复数据报错：


[root@localhost mysql]# mysqlbinlog -vv  master-bin.000001 --start-position=219 --stop-position=978 | mysql -uroot -p
Enter password: 
ERROR 1782 (HY000) at line 23: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

解决方案

MySQL :: MySQL 5.7 Reference Manual :: 16.1.4.3 Disabling GTID Transactions Online


mysql> show global variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'gtid_mode';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| gtid_mode     | OFF_PERMISSIVE |
+---------------+----------------+
1 row in set (0.00 sec)

注：更改 GTID_MODE 状态顺序为 ON<->ON_PERMISSIVE<->OFF_PERMISSIVE<->OFF ，需要按照顺序依次改变。

再次执行就不会报错了：


[root@localhost mysql]# mysqlbinlog -vv  master-bin.000001 --start-position=219 --stop-position=978 | mysql -uroot -p
Enter password: 
[root@localhost mysql]

检查数据


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> select * from test.jettech01;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

在恢复回去GTID_MODE


mysql> set @@GLOBAL.GTID_MODE = ON;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
mysql> set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.01 sec)

相关阅读:
YOLOV8改进：TripletAttention | 即插即用的轻量级注意力机制
 MES系统物料管理的五大功能，建议收藏
 语言模型和词向量的发展史及其原理
 十分钟“手撕”七大排序
 Javaweb项目中文件的上传与下载
 window11安装JDK1.8【jdk-8u121】
集合类ArrayList的扩容机制详解
 如何在Linux系统安装Nginx
java专题练习(抢红包)
第2-3-5章删除附件的接口开发-文件存储服务系统-nginx/fastDFS/minio/阿里云oss/七牛云oss
原文地址：https://blog.csdn.net/Michaelwubo/article/details/126229721