mysql数据日志恢复

mysql数据日志恢复

今天莫名mysql被黑了
image.png
然而内心毫无波澜,因为我昨天才备份过一次(论备份的重要性),不过也在想这些操作应该都是有日志记录的,所以在想万一没有备份怎么办,查阅一些资料后整理了一些做法。

数据库日志记录信息

使用

mysql -uroot -p

登录mysql后

查询是否开启

show variables like '%log_bin%';

image.png

  • log_bin为ON即为开启
  • /var/lib/mysql/为log的存放位置

查看过期时间

show variables like 'expire_logs_days';

可以查询日志过期天数,值为0不过期,值为n则n天后被清理
image.png

查看日志

进入到存放路径会看到以下文件
image.png
其中binlog.000001等文件就是日志文件,此时为二进制文件,需要利用工具导出成文本文件

转换文件

利用mysql自带的mysqlbinlog

1. 全部转换

mysqlbinlog ./binlog.000003 > ~/log3.log
或
mysqlbinlog --no-defaults --base64-output='decode-rows' -d xxx -v ./binlog.000003 > ~/log3.log

这样可以全部导出

2. 截取部分

# 根据日期
# 因为一个mysql实例的所有binlog文件是在一个文件中的,所以我们先要去除其他不想关的数据库。利用-d参数来指明数据实例。然后在利用开始时间(--start-datetime)和结束时间(--stop-datetime)来进一步筛选
mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d xxx --start-datetime='2020-06-29 15:22:15' --stop-datetime='2020-06-29 15:23:15' binlog.000003 > temp.sql
或者
# 根据位置
mysqlbinlog --start-position=109674 --stop-position=112446 ./binlog.000003 > temp.sql

3. binlog数据组成

下面是binlog基本语句块

# at 109674
#200629 15:22:15 server id 1  end_log_pos 109760 CRC32 0x3920d0d8 	Query	thread_id=659	exec_time=0	error_code=0
SET TIMESTAMP=1593415335/*!*/;
BEGIN
...
...
COMMIT/*!*/;
  • at 109674
    指明的当前位置相对文件开始的偏移位置,这个在mysqlbinlog命令中可以作为--start-position或者--stop-position的参数
  • 第二行指明时间等信息
  • 第三行设置时间戳
  • 第四行BEGIN开始是具体的执行
  • COMMIT/*!*/;为当前这行记录的结束

恢复

运行导出的sql文件即可
如果当时没备份

# at 112334
#200629 16:55:37 server id 1  end_log_pos 112446 CRC32 0x99511249 	Query	thread_id=790	exec_time=1	error_code=0	Xid = 233557
SET TIMESTAMP=1593420937/*!*/;
DROP DATABASE `xxx`
/*!*/;

可以看到drop的记录,将drop之前的记录恢复即可,再一次论证备份和日志的重要性,另外记得及时改mysql密码。