简介
由于程序出错 导致MySQL数据库三天内的数据混乱 现在需要删除三天内错误的数据 因为大意 where语句条件忘记添加引号 导致条件不生效 删除了表中所有的数据 现在需要通过binlog日志将此表的数据恢复
环境
生产环境 MySQL-5.5.15 已经开启二进制日志
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
步骤
大体的数据恢复思路是：
- 先查询MySQL已经读写到的二进制日志 然后刷新二进制到新的日志文件
- 读取二进制日志 过滤出关于要恢复表的信息
- 将所有对要恢复表的操作信息保存为sql文件
- 然后将sql文件导入MySQL 完成数据恢复
查询已经写到的二进制位置
通过 show master status; 查看当前写入到的二进制日志文件
[root@localhost /]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 236859
Server version: 5.5.14-log MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000037 | 40435151 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
刷新二进制日志文件
执行 flush logs; 刷新二进制日志 可以看到 已经打来了一个新的文件开始写入了
二进制日志文件的路径一般在数据库目录下
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000038 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
使用mysqlbinlog工具查看二进制日志
mysqlbinlog程序就是MySQL 二进制日志文件的查看工具了 先从从二进制中找到最后执行删除指令的语句
最后记录删除操作的二进制日志文件为mysql-bin.000037 要恢复的表存在与dbinfo库中
[root@localhost data]# mysqlbinlog -d dbinfo mysql-bin.000037 | \
grep 'delete' -B1 |grep zf_total_increment -B1
SET TIMESTAMP=1460950871/*!*/;
delete from zf_total_increment where insert_time > 2016-04-16
这里看到了执行删除语句的时间戳是 1460950871 现在需要将它转换为日期时间
mysql> select FROM_UNIXTIME(1460950871);
+---------------------------+
| FROM_UNIXTIME(1460950871) |
+---------------------------+
| 2016-04-18 11:41:11 |
+---------------------------+
1 row in set (0.00 sec)
恢复的时候当然不能把这一句也恢复了 否则刚恢复完毕又被删除了
继续找到建表的日期 因为表并没有被删除 根据情况 可以先truncate下表 这样就不用重新创建了
[root@localhost data]# mysqlbinlog -d dbinfo mysql-bin.000009|grep zf_total_increment -B1|head
SET TIMESTAMP=1389259355/*!*/;
DROP TABLE IF EXISTS `zf_total_increment` /* generated by server */
--
SET TIMESTAMP=1389259355/*!*/;
CREATE TABLE `zf_total_increment` (
--
-- ----------------------------
-- Records of zf_total_increment
-- ----------------------------
INSERT INTO `zf_total_increment` VALUES (......)
mysql> select FROM_UNIXTIME(1389259355);
+---------------------------+
| FROM_UNIXTIME(1389259355) |
+---------------------------+
| 2014-01-09 17:22:35 |
+---------------------------+
1 row in set (0.00 sec)
可以看到 在同一时刻进行了删除已存在的表 创建表 插入第一条语句 那么就从这里开始恢复
zf_total_increment这个表的数据存在与mysql-bin.000009到mysql-bin.000037中 需要挨个恢复
由于我并非要恢复所有的数据 原本就是要删除这几天的数据 所以只将数据恢复到2016-04-16之前
如果是完全恢复的话 千万不要将delete语句的时间也恢复了 可以恢复到这个时间前一秒钟都行
最好可以恢复到这条语句的前一个位置
[root@localhost data]# mkdir recovery
[root@localhost data]# mysqlbinlog -d dbinfo \
--start-datetime='2014-01-09 17:22:35' \
--stop-datetime='2016-04-16 00:00:00' mysql-bin.000009 | \
grep zf_total_increment >> recovery/zf_total_increment.sql
这样 mysql-bin.000009中所有关于zf_total_increment的语句都被追加到zf_total_increment.sql中了
接下来继续将mysql-bin.000010 到 mysql-bin.0000037中的也继续恢复
如果恢复的文件太多 可以使用脚本 等所有的日志都跑完了 最终的目标表的数据就都被保存为sql文件
[root@localhost recovery]# head zf_total_increment.sql
DROP TABLE IF EXISTS `zf_total_increment` /* generated by server */
CREATE TABLE `zf_total_increment` (
-- Records of zf_total_increment
INSERT INTO `zf_total_increment` VALUES (......)
查看这个文件 发现删除和创建语句也在其中 由于创建语句并没有写在一行 所以并不完整
由于过滤的时候只过滤一行 导致写在下一行的 /*!*/; 丢失 下面用sed工具修正
[root@localhost recovery]# sed -i '1,3d' zf_total_increment.sql
[root@localhost recovery]# sed -i 's/$/;/g' zf_total_increment.sql
最后就可以将这个sql文件导入MySQL执行了
[root@localhost recovery]# mysql -uroot -p123456 dbinfo < zf_total_increment.sql
最后查看数据库 这个表的数据是否已经完全恢复了呢 这个sql文件也可以作为备份 供以后使用
附录
以下是mysqlbinlog支持的常用选项
--database=db_name, -d db_name 只列出该数据库的条目 只用本地日志
–-host=host_name, -h host_name 获取指定主机上的MySQL服务器的二进制日志
–-user=user_name, -u user_name 远程主机需要用于连接的用户名
–-password[=password], -p[password] 远程主机需要输入密码 注意-p和密码间不能有空格
–-port=port_num, -P port_num 连接远程主机使用的端口号
–-read-from-remote-server, -R 如果未给出该选项 任何远程连接的选项都被忽略
--result-file=name, -r name 输出到给定的文件
--start-datetime=datetime 从二进制中的日志某个日期开始读取往后的日志
--stop-datetime=datetime 读取二进制中的日志某个日期为止
--start-postition=N 从二进制中的某个位置开始读取 (# at 1 中的1就是N)
--stop-postition=N 读取到二进制中的某个位置为止
–-disable-logs-bin,-D 如果直接将日志传给MySQL执行 此选项禁用二进制日志
如果数据库配置的有主从同步的话 不可以禁用二进制日志方式恢复
否则可能因为没有二进制日志的产生 slave库并没有恢复数据
mysqlbinlog打印出来的数据是可以直接导入mysql的
如果想把当时INSERT语句的执行日期也记录 就需要把SET TIMESTAMP也保留下
也可以通过以下方式直接将数据恢复到MySQL
mysqlbinlog mysql-bin.000009 \
--start-position=7492 --stop-position=647724 \
|grep zf_total_increment -B1 -A1 |mysql -uroot -p123456 dbinfo
这个例子通过事务在日志中的位置来恢复的 grep的 -B1 参数同时保留了SET TIMESTAMP语句
然后直接通过管道符提交给MySQL MySQL选择要恢复到的库
每一个二进制日志文件的position都是从新开始 如果在多个文件内恢复 可能就不如datetime方便了
但是可以精确到每一条语句上 datetime的话 可能1秒内就有好多条语句