1.
如果想通过 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 ; ; 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' ; DELIMITER ; # End of log file ; ;
这个时候，我们添加一些数据
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.index3 现在就需要解决一个实际问题，怎么恢复这个数据库
首先为了防止干扰，执行 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; ; 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 ; 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)
