MySQL使用bin_log恢复删除的数据

1、确认是否开启binlog

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/mysql/binlog       |
| log_bin_index                   | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                      |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+

#开启binlog
vim /etc/my.conf
#开启log_bin日志，方便有问题时恢复 
server-id=1
log-bin=mysql-bin
#设置bin_log日志只保存15天 超过这个天数就自动清理
expire_logs_days=15

2、创建测试数据

#库
mysql> create database test_del;
Query OK, 1 row affected (0.00 sec)
#表
mysql> create table test (uid int ,uname varchar(32));
Query OK, 0 rows affected (0.02 sec)
#数据
mysql> insert into test  values (1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test  values (2,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+----------+
| uid  | uname    |
+------+----------+
|    1 | zhangsan |
|    2 | zhangsan |
+------+----------+

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_del           |
+--------------------+
6 rows in set (0.00 sec)

#删除模拟
mysql> drop database test_del;
Query OK, 1 row affected (0.01 sec)

3、进行日志截取

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1237 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#首先为了防止干扰，执行 flush logs ，产生一个新binlog 文件。
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.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4、恢复数据

#查看binlog存储位置
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

##首先要找到数据在哪里被删除了。
cd /var/lib/mysql/
mysqlbinlog mysql-bin.000001 | grep -n  "drop database"
88:drop database test_del

#接下来找位置
#数据需要恢复的起始位置
#数据需要恢复的结束位置

#将binlog变成可查看文件
mysqlbinlog --set-charset=utf8 mysql-bin.000001 > tmp.sql

#这边是创建test_del的起始位置 123 一个at为一个步长
'/*!*/;
# at 123
#220706 10:47:07 server id 1  end_log_pos 154 CRC32 0xa4641180  Previous-GTIDs
# [empty]
# at 154
#220706 10:48:05 server id 1  end_log_pos 219 CRC32 0x88b9783a  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#220706 10:48:05 server id 1  end_log_pos 325 CRC32 0x7628b831  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1657075685/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
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_del


#drop删除前的位置 1068 要恢复到的位置
# at 1037
#220706 10:51:31 server id 1  end_log_pos 1068 CRC32 0x40af3269         Xid = 23
COMMIT/*!*/;

# at 1068
#220706 10:52:16 server id 1  end_log_pos 1133 CRC32 0xecfb89df         Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 1133
#220706 10:52:16 server id 1  end_log_pos 1237 CRC32 0x2db0560e         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1657075936/*!*/;
drop database test_del
/*!*/;

# at 1237
#220706 10:52:41 server id 1  end_log_pos 1284 CRC32 0x3e3418b7         Rotate to mysql-bin.000002  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

#执行恢复
mysqlbinlog -v mysql-bin.000001 --start-position=123 --stop-position=1068 | mysql -uroot -ppwd@123

5、验证恢复

[root@aly mysql]# mysql -uroot -ppwd@123

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_del           |
+--------------------+

select * from test;
+------+----------+
| uid  | uname    |
+------+----------+
|    1 | zhangsan |
|    2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)s