记一次mysql突发删库，利用binlog还原数据库和对应数据

今天朋友反馈，之前服务器上跑着的一个项目，使用不了了，因为没什么人在用，今天才收到反
馈，结果排查一番后突然发现，我擦，数据库怎么没了(原因至今不清楚，可能是内部人员误删，也
有可能是被攻击。。。)，然后慌得一批开始了数据还原的过程，经过半个多小时的努力终于还原成功数据，在此记录一下。

首先想到的是通过前段时间手动备份的sql去还原数据，但是这样虽然能还原大部分数据，相应的剩余数据无法全部还原，这样子也会有问题.

这时候想到了之前了解过的mysql的binlog，如果操作得当，可以一比一还原mysql的全部数据，于是去my.cnf查看是否有开启binlog(在做这些操作前建议关闭相对应的tomcat服务，防止数据新添加，导致还原后更新不一致)

还好安装mysql的时候配置里有写上,看来还有救(这里我用的是宝塔软件商店安装的mysql,配置里会开启)
如果没有开启，在当前配置文件里面加上

#开启binlog
log-bin=mysql-bin
#如果是mysql5.7以上的版本需要加上serverid，不然会启动报错，随便加一个即可
server-id = 1

binlog_format是指定binlog的模式，一共有三种 statement,row,mixed不指定会默认为statement；
expire_logs_days是binlog文件的清除时间，不填默认是0

也可以通过show variables like ‘%log_bin%’;来查看binlog是否开启

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

log_bin为on即是已经开启

然后可以执行show binary logs;查看已经生成的binlog文件

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       437 |
| mysql-bin.000003 |       685 |
| mysql-bin.000004 |   9101013 |
+------------------+-----------+
4 rows in set (0.00 sec)

这里可以看到最大的我列表里4条，最大的一条是000004，如果有xftp连接的话，可以直接去目录查看文件更新时间

如果这里不清楚具体时间，可以通过时间段筛选把数据筛选出来分析，因为我知道数据删除操作是在9.7号之后的，所以问题定位在000004号binlog之中；

我们可以通过命令将这个binlog文件导出为sql，然后手动还原，也可以直接命令还原(我这里通过导出sql还原的时候，运行sql文件有问题，无法成功执行sql。最后选择的通过命令还原，但是依然可以通过导出sql来分析具体定位到时间或者数据操作的语句)

#首先我将该文件导出为sql分析一下问题，这会在data目录下生成一个名为plancat1.sql的sql文件
/www/server/mysql/bin/mysqlbinlog /www/server/data/mysql-bin.000004 >plancat1.sql

这段代码网上有很多，但是mysqlbinlog前面路径都不完整，如果没有配环境变量，直接运行的话会报错，所以全部填绝对路径去运行，网上都是复制来复制去你抄我我抄你，坑了我好半天。

这是导出的sql文件，可以复制到本地查看


打开就能看到每一条sql的记录了，一直到停服之前，但是即使是这样，文件太大的情况下观看起来也会不方便，所以建议用另一种方式，show binlog events in ‘binlog文件名’;查看每条排列好的sql，我这里用的是navicat工具去查看，会方便很多

然后在打开的界面输入show binlog events in 'mysql-bin.000004';

这样子就比较清晰了，接下来就分析一下我需要恢复的数据，首先我要恢复的数据是删库之前的plancat库的所有表数据，就是删库语句之前，建库语句之后这些ddl操作，那么我们搜索一下DROP DATABASE，和CREATE DATABASE


我们这里定位到建库的起始位置是242409pos，删库的截至位置4784105pos，ok，清楚了positiion后，那么我们就可以通过命令去恢复了

[root@VM-0-2-centos backup]# /www/server/mysql/bin/mysqlbinlog --start-position=242409 --stop-position=4784105 --database=plancat /www/server/data/mysql-bin.000004 | mysql -uroot -p

筛选条件
–database:指定对应的数据库
–start-postion:pos开始位置
–end-position:pos结束位置
–start-datetime:开始时间
–end-datetime:结束时间

/www/server/mysql/bin/mysqlbinlog --start-position=242409 --stop-position=4784105 --database=plancat /www/server/data/mysql-bin.000004 | mysql -uroot -p

这一行命令的意思是筛选出起始位置242409，到截止位置4784105，并且数据库为plancat(- -database，因为在这之中，肯定会有其他的数据库操作，如果不加这个筛选，会将其他库的数据也做了操作，导致数据错误)的操作。

敲完命令后，接着输入mysql密码，稍微等待一会，就会发现，被人删除数据库已经还原了，包含对应的数据，这次也算是第一次自己手动还原数据了

这是一条根据时间筛选，排查出执行sql的具体时间，最后定位到10月18日下午2点多，然后就是要开始找人背锅了。。

/www/server/mysql/bin/mysqlbinlog --start-datetime='2021-10-23 14:00:00' --stop-datetime='2021-10-23 23:00:00' --database=plancat /www/server/data/mysql-bin.000004 >plancat1.sql