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

Michaelwubo

已于 2022-08-08 16:24:47 修改

阅读量1k

点赞数

文章标签： mysql 数据库 java

于 2022-08-08 16:23:37 首次发布

本文链接：https://blog.csdn.net/Michaelwubo/article/details/126229721

版权

如果想通过 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)

Michaelwubo

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql 基于GTID方式的bin-log日志恢复数据

1.如果想通过 mysql 的 binlog 恢复数据，首先要开启 binlog。这里搭建一个测试的环境，了解一下 mysql binlog 是如何恢复数据库的。原理比较简单，binlog 会存储mysql中变化的数据，比如你创建了一个数据库，写入了一些数据，这些都会存储在 mysql 的 binlog 中。需要恢复的时候就找到，两个位置，一个起始位置，一个结束的位置。结束的位置，一半是数据被破坏或者删除前的位置。mysql 8 默认已经开启了 binlog。......
复制链接

扫一扫