【MySQL】删库跑路?了解下bin-log!

907 阅读2分钟

操作生产数据库时每一个操作都需要反复审核。任意的小错误,都会导致线上“大灾难”!
“从删库到跑路”,可以说是IT业内老梗了。

但是真的不小心误删了,真的就无法挽救了吗?

其实也没这么夸张,真实的生产数据库往往“热备”和“冷备”同时进行。万一出现删库的情况,还是可以通过备份文件进行恢复。这里提到的备份文件可以是数据库某一时刻的快照,也可以是bin-log文件

bin-log 归档

什么是 bin-log

bin-log是一种由MySQL提供的记录数据库操作(CUD操作)的二进制日志文件。
它有以下两个特点:

  1. Binlog为逻辑日志,记录的是一条语句的原始逻辑
  2. Binlog不限大小,追加写入,不会覆盖以前的日志

如何使用bin-log

修改mysql配置文件/etc/my.cnf

log‐bin= /data/mysql/bin/mysql-bin
# mysql 版本高于5.7 需要额外配置,唯一id
server‐id= 1212
# binlog格式,有3种statement,row,mixed
binlog‐format=ROW
# 什么时候进行刷盘,配置1就是每次操作都进行刷盘写入,0 则是将刷盘交给系统决什么时候进行
sync‐binlog=1

检查开启状态

show variables like '%log_bin%';

如果得到如下(log_bin | ON)表示正常开启,如下:

+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /data/mysql/bin/mysql-bin       |
| log_bin_index                   | /data/mysql/bin/mysql-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

模拟删库

通过 reset master;将数据库历史的binlog文件还原,然后新增库 test 新增表 test;

新增一条记录

查看binlog日志文件,可以发现已经记录了日志。

然后删除库,也会进行记录。

数据恢复

通过mysql提供的 mysqlbinlog 命令查看日志文件,找到需要恢复的位子。

/usr/local/mysql/bin/mysqlbinlog --no-defaults /data/mysql/bin/mysql-bin.000001

可以看到一些库以及表的信息。

选择需要恢复的内容,并记录行号。

通过以下命令,进行恢复(恢复之前需要新建一个和原来一样的库)

## 行号
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="1491" --stop-position="2424" /data/mysql/bin/mysql-bin.000001 |./mysql -u root -p test 
## 通过时间
/usr/local/mysql/bin/mysqlbinlog --no-defaults /data/mysql/bin/mysql-bin.000001 --stop-date= "2020-11-24 00:00:00"  --start-date= "2020-11-24 11:55:00"| ./mysql -u root -p test
## 全部恢复
/usr/local/mysql/bin/mysqlbinlog --no-defaults /data/mysql/bin/mysql-bin.000001 |./mysql -u root -p test


效果如下: