mysql二进制日志的使用

mysql二进制日志也就是一些mysql命令操作的记录

删除二进制日志信息：

二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是，删除之后可能导致数据库崩溃时无法进行恢复，所以若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删(可以做即时点还原)。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下：

1、使用RESET MASTER语句可以删除所有的二进制日志重新记录

mysql> reset master;

mysql> show binary logs;

解析：首先不建议在生产环境下使用此操作；删除所有的二进制日志后，Mysql将会重新创建新的二进制日志。新二进制日志的编号从000001开始。

2、根据文件或时间点来删除二进制日志：

语法形式：

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

其中TO'log_name'表示把这个文件之前的其他文件都删除掉，也可使用BEFORE datetime_expr指定把哪个时间之前的二进制文件删除了。

mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';  #把mysql-bin.000007文件之前的其他文件都删除掉（000001--000006）

mysql> PURGEBINARY LOGS BEFORE '2013-10-19 10:26:36'; #使用时间来删除二进制日志

★注意：my.cnf配置开启二进制日志功能默认记录所有数据库，如果指定数据库做二进制日志记录则添加

log-bin = mysql-bin

binlog-do-db = happy

binlog-do-db = iwker_global

##二进制开启状态：

mysql> show global variables like "%log_bin%";

##使用show master status命令可以查看当前启用二进制日志记录的数据库

mysql> show master status;

+------------------+----------+-----------------------------------------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB                                  | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+-----------------------------------------------+------------------+-------------------+

| mysql-bin.000002 |      120 | iwker_global,iwker_inside,iwker_ucenter |                  |                   |

+------------------+----------+-----------------------------------------------+------------------+-------------------+

##查看MySQL Server上的二进制日志

mysql> show binary logs;

★★★使用mysqlbinlog工具进行恢复

步骤：

1、分析定位关键的位置或者时间点

2、导入全库备份的文件

3、注释掉误操作的命令或者避开误操作的时间点进行导入

//二进制日志转换成可读的SQL文件

mysqlbinlog mysql-bin.000001 > mysql.sql    

//或者只导出某个指定数据库的二进制日志

mysqlbinlog -d happy mysql-bin.000001 > happy_bin.sql

a.查询二进制日志文件含有drop操作的记录

mysqlbinlog binlog.0000003 |less    //输出包括在binlog.000003中包含的所有语句，以及其它信息例如每个语句花费的时间、客户发出的线程ID、发出线程时的时间戳等等。

mysqlbinlog mysql-bin.000002 |grep -i -C 2 "drop table" -c

b.根据时间来还原 --start-datetime,--stop-datetime

mysqlbinlog --start-datetime="2010-09-29 18:00:00" --stop-datetime="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

mysqlbinlog --stop-datetime='2016-07-21 14:40:10' /var/log/mysql-bin.000001 | mysql -uroot -p

c.根据数据库名来进行还原

mysqlbinlog -d iwker_inside  /var/lib/mysql/mysql-bin.000002

d.基于位置恢复

mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002 |mysql -u root -p