详解mysql的二进制日志恢复数据库

今天是蛋疼的一天，一大早本想把客户服务器上的数据库下载下来，更新公司的数据库；可怕的是反过来了，客户的数据库有上万条数据，全没了，完全没备份过啊，想死的心都有了。还好从同事口中得知有从二进制日志那里恢复的方法。百度谷歌了好一阵子，还好这个数据库版本默认开启了二进制日志记录，可算恢复了。看见网上的都是零零乱乱的，该有的就到了别的贴，有些就草草地讲了用什么命令。所以觉的该好好总结一番。

本文涉及：

1、查看是否开启了二进制日志

2、开启二进制日志

3、讲解二进制日志文件

4、恢复数据库

5、flush刷新二进制日志文件及删除二进制日志文件

引言

二进制日志记录了由开启到停止所有数据库的所有增删改操作（这里的数据库指的是mysql里面的所有数据库），所以只能恢复出现过的sql语句的数据；适用于数据灾难的、证据库总体和高精度的恢复；此方法不适合日常的数据恢复，所以平常要做好数据备份！

1、查看是否开启了二进制日志

mysql>show variables like '%log_bin%'

log_bin=OFF是还没开启

2、开启二进制日志

window需要修改my.in文件，linux需要修改my.conf

在[mysqld]下面加上一行log-bin=D:\\mysql\\bin-log\\mysql-binlog

指定的是存放路径和存放的文件名（mysql-binlog）

修改后重启mysql

会发现d:/mysql/bin-log/下多了mysql-binlog.index、mysql-binlog.000001这两个文件，mysql-binlog.index为文件的索引文件，mysql-binlog.000001为二进制日志文件

3、讲解二进制日志文件

直接用二进制编辑器打开mysql-binlog.000001，如下所示

红框为他的实体内容，但是我们还是不怎么清晰里面的内容

使用D:\mysql\bin-log>mysqlbinlog mysql-binlog.000001>1.txt命令将其转换成文本内容

/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4 位置（可用于位置恢复）#140418 21:01:50 server id 1  end_log_pos 107 Start: binlog v 4, server v 5.5.19-log created 140418 21:01:50 at startupROLLBACK/*!*/; 时间（可用于时间恢复），其他信息BINLOG 'PiJRUw8BAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA+IlFTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA=='/*!*/;# at 107#140418 21:04:48 server id 1  end_log_pos 204 Querythread_id=1exec_time=0error_code=0SET TIMESTAMP=1397826288/*!*/;SET @@session.pseudo_thread_id=1/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1344274432/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database test_binlog/*!*/;# at 204#140418 21:14:51 server id 1  end_log_pos 383 Querythread_id=2exec_time=0error_code=0use test_binlog/*!*/;SET TIMESTAMP=1397826891/*!*/;CREATE TABLE `test` (id int AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255)) ENGINE=innoDB DEFAULT CHARSET=utf8/*期间执行过的mysql语句*/;# at 383#140418 21:16:02 server id 1  end_log_pos 458 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1397826962/*!*/;BEGIN/*!*/;# at 458#140418 21:16:02 server id 1  end_log_pos 486 IntvarSET INSERT_ID=1/*!*/;# at 486#140418 21:16:02 server id 1  end_log_pos 617 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1397826962/*!*/;INSERT INTO `test` (`name`) VALUES ('a'), ('b'), ('c'), ('d')/*!*/;# at 617#140418 21:16:02 server id 1  end_log_pos 644 Xid = 7COMMIT/*!*/;# at 644#140418 21:17:47 server id 1  end_log_pos 757 Querythread_id=3exec_time=1error_code=0SET TIMESTAMP=1397827067/*!*/;DROP TABLE `test` /* generated by server *//*!*/;# at 757#140418 21:18:47 server id 1  end_log_pos 854 Querythread_id=3exec_time=0error_code=0SET TIMESTAMP=1397827127/*!*/;DROP DATABASE `test_binlog`/*!*/;# at 4602#140418 23:27:35 server id 1  end_log_pos 4621 StopDELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

上面txt文件其实可以直接导入数据库恢复上面文件涉及的数据，使用命令：D:\mysql\bin-log>mysql<1.txt -uroot -proot

4、恢复数据库

mysqlbinlog命令用于将二进制日志文件恢复数据库

主要的参数有

--start-date（指定开始日期，时间格式为2014-04-19 20:37:00，恢复到这个时间之后的数据，包括此时间）

--stop-date（指定结束时间，时间格式为2014-04-19 20:37:00，恢复到这个时间段之前的，不包括此指定时间）

例子，上面参数是可以单独一个使用

D:\mysql\bin-log>mysqlbinlog --start-date="2014-04-18 21:14:51" --stop-date="2014-04-18 21:17:47" mysql-binlog.000001 | mysql -uroot -proot

--start-position（指定开始位置，恢复到此位置之后的数据，包括此位置）

--stop-position（指定结束位置，恢复此位置之前的数据，不包括此位置）

D:\mysql\bin-log>mysqlbinlog --start-position=951 --stop-position=1391 mysql-bin
log.000001 | mysql -uroot -proot

5、flush刷新二进制日志文件及删除二进制日志文件

刷新二进制日志

mysql> flusth logs;

删除二进制文件

1：设置my.cnf的expire-logs-days参数2：PURGE BINARY LOGS BEFORE datetime;3：PURGE BINARY LOGS TO 'filename';

4：mysql -uroot -proot -e"reset master;"//全部二进制日志清空

相关阅读：

mysql二进制日志怎么干活：http://blog.csdn.net/z1988316/article/details/7838727