详解mysql的二进制日志恢复数据库_二进制日志没开怎么恢复数据呢-CSDN博客

本文链接：https://blog.csdn.net/ze773108248/article/details/24050417

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

本文涉及：

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 startup
ROLLBACK/*!*/; 时间（可用于时间恢复），其他信息
BINLOG '
PiJRUw8BAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA+IlFTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#140418 21:04:48 server id 1  end_log_pos 204 	Query	thread_id=1	exec_time=0	error_code=0
SET 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 	Query	thread_id=2	exec_time=0	error_code=0
use 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 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1397826962/*!*/;
BEGIN
/*!*/;
# at 458
#140418 21:16:02 server id 1  end_log_pos 486 	Intvar
SET INSERT_ID=1/*!*/;
# at 486
#140418 21:16:02 server id 1  end_log_pos 617 	Query	thread_id=2	exec_time=0	error_code=0
SET 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 = 7
COMMIT/*!*/;
# at 644
#140418 21:17:47 server id 1  end_log_pos 757 	Query	thread_id=3	exec_time=1	error_code=0
SET TIMESTAMP=1397827067/*!*/;
DROP TABLE `test` /* generated by server */
/*!*/;
# at 757
#140418 21:18:47 server id 1  end_log_pos 854 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1397827127/*!*/;
DROP DATABASE `test_binlog`
/*!*/;
# at 4602
#140418 23:27:35 server id 1  end_log_pos 4621 	Stop
DELIMITER ;
# End of log file
ROLLBACK /* 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;"//全部二进制日志清空