mysqlbinlog恢复数据库内容

强调：备份！备份！！备份！！！

记录一次惨痛的经历，将测试数据库执行到了正式服务器上，导致正式环境的数据丢失。

不要以为是你本地的数据库，所以备份与否无所谓，万一错了呢，所以备份吧，总不会有错的。废话不多说，进入正文。

1、首先要保证mysql数据库开启了binlog：

 语句：show variables like 'log_%';

从查询结果中可以看到，binlog日志功能未开启。没开启怎么办，数据库有ibdata1，至于使用这个怎么恢复，请大家自行百度。

（1）开启数据库的binlog功能：

修改mysql数据库的配置文件，windows是my.ini文件，打开该文件，

在[mysqld] 区块设置/添加

log-bin=mysqlinit

binlog-format=row(可有可无，我就没设置)

确认是打开状态(值 mysqlinit 是日志的基本名或前缀名)；

设置完成之后需要重启mysql服务才能生效。

2、查看所有binlog日志列表

命令：show master logs;

3、查看binlog文件的内容

命令：show binlog events in 'mysqlinit.000006';

需要注意的有pos和end_log_pos,info表示执行的语句。找到你之前的备份的文件的时间点，可以根据执行的语句来查询或者是现有数据库最后执行的id等关键字来查找到对应的info，记录下相应的end_log_pos。

4、使用mysql的工具mysqlbinlog读取binlog日志

进入mysql安装目录下的bin文件夹，执行：

命令：mysqlbinlog D:/mysqlinit.000005>test.sql

如果本地有mysql，可以将服务器上的日志文件拿到本地执行该语句。如果执行失败，可以执行下面的语句：

mysqlbinlog --no-defaults D:/mysqlinit.000005>test.sql

生成的test.sql文件位于mysql安装目录/bin目录下，也可以：

mysqlbinlog --no-defaults D:/mysqlinit.000005>D:/test.sql

生成的文件在D盘根目录下。

注意：我在执行该语句时，结尾加了个分号;,结果执行不成功，去掉分号即可。

5、恢复数据库

打开生成的sql文件，查找到之前记录下来的点end_log_pos

生成的文件内容：

从begin到下一个begin为一个执行语句，

查找到相应的位置之后，从该语句之前的begin语句已经不需要了，需要删除该begin到第一个begin之间的内容，还需要把执行错误语句开始之后的语句删除。剩下的文件就是需要执行的文件。

如果有多个文件，比如我现在查看的是binloginit.000005的内容，那么binloginit.000006也需要执行上述操作。

需要注意的是，该文件中包含的是所有mysql数据库的操作，需要从test.sql文件中把其他数据库的操作剔除(不执行这一步应该也可以，就是执行这个文件的时候会报错，但是对于要恢复的数据库没有影响)。因此如果要执行这个文件之前一定要做好备份，包括其他的数据库。

当然也可以这样：

(1)、在本地创建数据库test，将备份文件恢复到test库

(2)、将test.sql文件中的use `c_qlc`/*!*/;替换成use `test`/*!*/;

(3)、执行刚才生成并且操作之后的test.sql文件，这样就将所有的数据恢复到本地test数据库中了

(4)、将本地的数据拿到正式环境中即可。

恢复流程：

总结：数据库恢复，就是从你最后备份的时间点，到最后出现问题的时间点之间所有的语句执行一次。

 

补充内容:

1、常用binlog日志操作命令

(1).查看所有binlog日志列表

mysql> show master logs;

(2).查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;

(3).刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

(4).重置(清空)所有binlog日志

mysql> reset master;

2、MySQL binlog的三种工作模式

      （1）Row level

　　日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

　　优点：能清楚的记录每一行数据修改的细节

　　缺点：数据量太大

　　（2）Statement level（默认）

　　每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

　　优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能

　　缺点：容易出现主从复制不一致

　　（3）Mixed（混合模式）

　　结合了Row level和Statement level的优点

3、MySQL企业binlog模式的选择

1. 互联网公司使用MySQL的功能较少（不用存储过程、触发器、函数），选择默认的Statement level
2. 用到MySQL的特殊功能（存储过程、触发器、函数）则选择Mixed模式
3. 用到MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化一直则选择Row模式

4、设置MySQL binlog模式

查看MySQLbinlog模式：mysql>show global variables like "binlog%";

系统默认为STATEMENT模式。

MySQL中设置binlog模式

(1) 命令方式 mysql>set global binlog_format='ROW';　

(2)配置文件：

[mysqld]

binlog_format='ROW'          #放在mysqld模块下面