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
