前言
偶尔的机会敌人说他部门的数据库误删了,想复原回来,他百度了一些材料,也跟着试了。但发现会报一些错,于是他就找我帮忙看一下。对于我来说,因为公司的数据库都是 DBA 在管控,平时都没机会操作,基本上都停留在实践上。
但为了维持我在他心中的光芒形象,不能让他看出我是个半吊子,我就装逼让他把谬误的信息发给我一下,我好百度一下。于是就有明天这么一篇水文呈现。
敌人的数据库是基于 docker 搭建的,敌人一度狐疑是因为 docker 起因导致他复原不胜利。
数据恢复之 binlog 前置常识
注: 本文的例子为 demo 示例,mysql 基于 docker 搭建。版本为 mysql 8.0
1、先确认 mysql 是否曾经开启 binlog
SHOW VARIABLES LIKE '%log_bin%';
注: mysql8.0 默认就曾经开启 binlog,且 binlog-format 为 row 格局。如果是 mysql8.0 以下,需通过 /etc/my.cnf 进行配置开启。配置内容如下
## 配置 binlog 日志门路以及日志文件名前缀、生成的日志格局为:binlog.000001
log-bin=/var/lib/mysql/binlog
## 留神配置 server-id,保障唯一性
server-id=1
##binlog 格局,有 3 种 statement,row,mixed
binlog-format=row
2、查看 binlog 日志列表
SHOW MASTER LOGS;
3、查看以后 binlog 以及以后地位
SHOW MASTER STATUS;
4、查看 binlog 内容
SHOW BINLOG EVENTS IN 'binlog.000008';
5、指定位点查问,比方从 pos 为 746 开始查问
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746;
6、指定位点分页查问
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746 LIMIT 0,5
7、刷新产生新 binlog
FLUSH LOGS;
数据恢复案例
有这么一张用户表
假如咱们误删了 lisi 这条记录
当初咱们想把 lisi 复原回来。
1、查看 binlog
SHOW MASTER LOGS;
2、查看 binlog 事件
SHOW BINLOG EVENTS IN 'binlog.000010';
3、通过指定地位复原数据
/usr/bin/mysqlbinlog --start-position=65945--stop-position=66150 --database=demo_test /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v demo_test
注:
/usr/bin/mysqlbinlog 为 binlog 命令
--start-position=65945 为复原的开始地位
--stop-position=66150 为复原的完结地位
--database=demo_test 指定数据库为 demo_test
/var/lib/mysql/binlog.000010 为 binlog 日志
| /usr/bin/mysql -uroot -p123456 -v demo_test
通过管道连贯数据库,并通过 - v 显示详细信息
此时执行上来就会可能会呈现
ERROR 1062 (23000) at line 19: Duplicate entry '1' for key 't_user.PRIMARY'
这个报错也是敌人呈现的问题。这种看字面的意思就是呈现主键抵触了,此时的方法有 2 种
a、 办法一:将数据库产生抵触的记录删除,而后再执行复原语句。此时查看数据库
就会发现数据曾经复原回来了
b、 办法二:就是将 binlog 导出成 sql 语句,将 insert 语句改成 replace into。
具体操作如下:
1、将 binlog 内容输出到一个 sql 文件
/usr/bin/mysqlbinlog --start-position=65945 --stop-position=66150 --database=demo_test --base64-output=decode-rows -v /var/lib/mysql/binlog.000010 --result-file=/var/lib/mysql/binlog000010.sql
2、将生成的 sql 文件拷贝到宿主机
docker cp e0b7fb702f75:/var/lib/mysql/binlog000010.sql /
3、导出来的文件内容如下
注: 如果不加 –base64-output=decode-rows,则输入来的内容为 base64 加密内容
4、将 insert into 改成 replace into
5、将改好的 sql 语句从新导入到 docker 外面
docker cp /binlog000010.sql e0b7fb702f75:/var/lib/mysql
6、执行 sql 语句
mysql -uroot -p123456 -f </var/lib/mysql/binlog000010.sql
4、通过工夫复原数据
先通过如下命令,查看工夫点
/usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/binlog.000010 > /var/lib/mysql/bin_log000010.sql
注: 因日志比拟多,就先导入到文件再查看
执行如下语句进行复原
/usr/bin/mysqlbinlog --no-defaults --start-datetime="2022-04-25 16:10:00" --stop-datetime="2022-04-25 16:11:00" --database=demo_test /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v demo_test
注:
--start-datetime="2022-04-25 16:10:00" 为复原的开始工夫
-stop-datetime="2022-04-25 16:11:00" 为复原的完结工夫
执行语句,察看数据库,能够发现数据同样复原回来
总结
尽管以前就看过基于 mysqlbinlog 复原数据的八股文,但始终没有去尝试。刚好借敌人这次机会,尝试了一把。本文算是一次记录吧。真的应了一句话纸上得来终觉浅, 绝知此事要躬行。