MySQL利用binlog日志恢复记录

2019 年 10 月 21 日

3471 次浏览

4007字数

记录一次真实的数据恢复 o(T~T)o
事情是这样的,在一个月黑风高的夜晚,由于程序的漏洞给一位超级大神注入,修改了一些比较重要的表,并且有些还给老夫删除了,实属过分,但是还是佩服这位大神的厉害 o(T~T)o
废话不多说,下面就开始吧! Let's Go~

首先必须要提醒,想要利用binglog恢复数据,那么MySQL就必须设置开启二进制日志my.cnf的参数配置

[mysqld]
server-id = 1 #服务器ID
log_bin = mysql-bin #开启二进制日志,设置日志路径
max_binlog_size = 1000M #binlog每个日志文件大小
binlog_format = row #binlog的格式
expire_logs_days = 7 #设置binlog清理时间
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size = 512m #最大binlog缓存大小
binlog_rows_query_log_events = 1 #将原始的数据操作sql记录写入事件中

重启MySQL然后查看确认是否开启

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

误删除修改操作

这种时候，一定不要慌张！！！要从容冷静,蛋定~

1.备份一下最后一个binlog日志文件
2.接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件,做这个的操作主要是便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件
3.仔细查看最后一个binlog日志,并记录下关键的pos点(又名偏移量),到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步);
4.恢复数据
方法一:binlog实现闪回(推荐此种方法)
登录服务器，并查看

show binlog events in 'mysql-bin.000003';

binlog_evvents

通过分析,得出造成数据库破坏的pos点区间是介于到哪里之间(这是按照日志区间的pos节点算的),然后只要恢复到区间之前即可,从上图可以看出user表是在pos1001被删除

接下来,我们可以借助美团DBA团队的一个python工具-binlog2sql

安装相关依赖

yum install -y pip git
pip install --upgrade pip
git clone https://github.com/danfengcao/binlog2sql.git
pip install -r binlog2sql/requirements.txt

查询pos区间

python binlog2sql/binlog2sql/binlog2sql.py  -hlocalhost -P3306 -uroot -p'123456' -dtest -tinfo --start-file='mysql-bin.000011'

pos区间

从上面得出的结果中我们可以知道delete语句是在pos点2180 到 3789 之间,我们接下来就可以直接导出 sql文件了

python binlog2sql/binlog2sql/binlog2sql.py -hlocalhost -P3306 -uroot -p'123456' -dtest -tinfo --start-file='mysql-bin.000011' --start-position=2180 --stop-position=3789 -B > /root/rollback.sql

我们可以直接查看这个文件

这里要注意这是数据是否是你想要的,不然就前功尽弃了

接下来我们就可以直接导入到数据库中
导入数据

方法二:使用mysqlbinlog工具转换导入
同样的需要找到偏移量,然后再进行装换,--start-position指定需要开始转换的位置,反之--stop-position则代表结束位置,当然可以不一定使用偏移量,也可以是时间,但前提是必须对应的上

mysqlbinlog -d test --start-position=1450 --stop-position=2180 mysql-bin.000011 --skip-gtids=true > /root/backcall.sql

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS  -v  -d test --start-position=1450 --stop-position=2180 mysql-bin.000011 --skip-gtids=true > /root/backcall.sql

--skip-gtids=true使用这个选项是因为如果你是在另一台测试机上面导入会提示你gtid对应不上,加了这个选项后表示忽略