Mysql 二进制日志管理

MySQL二进制日志是以事务安全的方式包含更新日志中可用的所有信息。它包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。

打开mysql二进制日志

编辑mysql配置文件：/etc/my.cnf（windows下是my.ini），将“bin-log”一行前面的注释去掉，默认是：#log-bin=mysql-bin，（没有的话，自己手动添加一行），等号后面的mysql-bin是生成日志的路径和文件名，这里表示在mysql的data目录下生成，你也可以自己改路径，如log-bin=/data/mysql/log/mysql-bin。

还可以设置以下选项：

binlog-do-db= db_name  #将db_name更新记录到二进制日志中

binlog-ignore-db=db_name #不将db_name更新保存到二进制日志中

max_binlog_size= 104857600 #每个日志文件最大100M

保存，重启mysql。

重启后看到有生成类似mysql-bin.000001、mysql-bin.000002的文件就说明已经成功打开了二进制日志。

管理二进制日志文件

1、查看当前已有的二进制日志文件，执行sql“SHOW BINLOG EVENTS \G”或“SHOW MASTER LOGS”：

mysql> SHOW BINLOG EVENTS \G;

mysql> SHOW MASTER LOGS；

2、清除二进制日志文件：
mysql> PURGE { MASTER|BINARY } LOGS TO 'log_name';
mysql> PURGE { MASTER|BINARY } LOGS BEFORE 'datetime';
例如： 
mysql> PURGE MASTER LOGS TO 'mysql-bin.000001'; 
mysql> PURGE MASTER LOGS BEFORE '2010-06-20 13:00:00';

另一种清除二进制日志方法：
mysql > flush logs;
mysql > reset master;

从二进制日志文件恢复数据

mysqlbinlog：用于处理二进制日志文件的实用工具；可以用它来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。

使用方法：mysqlbinlog [options] log-files。

mysqlbinlog有很多选项，具体可以使用mysqlbinlog –help查看:

如：bin>mysqlbinlog --help

（如果没有将mysqlbinlog设置成系统命令，linux下可以用find / -name "mysqlbinlog"搜索出mysqlbinlog的位置，windows下在mysql安装目录的bin文件夹下）

如果执行mysqlbinlog时出现类似unknown variable 'default-character-set=utf8'错误提示，是因为在my.cnf（或my.ini）中的client选项组中添加了default-character-set=utf8，可以有两种办法解决这个bug：

第一种，执行时加上--no-defaults选项；如：mysqlbinlog --no-defaults --help。

第二种，在使用mysqlbinlog工具时把my.cnf（或my.ini）中client选项组中的default-character-set=utf8注释掉。注意要记得在不在使用mysqlbinlog工具时取消这个注释。

部分常用选项说明：

--start-datetime=datetime　#从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取, 格式应符合DATETIME或TIMESTAMP数据类型。datetime值是相对于运行mysqlbinlog的机器上的本地时区时间。

--stop-datetime=datetime　#从二进制日志中第1个日期时间等于或晚于datetime参量的事件停止读取，描述同start-datetime。

--start-position=N #从二进制日志中第1个位置等于N参量时的事件开始读。

·       --stop-position=N　 #从二进制日志中第1个位置等于N参量时的事件停止读。

示例：

１、 从mysql-bin.000001中恢复从2010-01-01 01:00:00开始之后的数据：

bin> mysqlbinlog --start-datetime="2010-01-01 01:00:00" mysql-bin.000001 | mysql -uroot -proot

２、 从mysql-bin.000001中恢复从2010-01-01 01:00:00到2010-01-02 01:00:00之间的数据：

bin> mysqlbinlog --start-datetime="2010-01-01 01:00:00" --stop-datetime="2010-01-02 01:00:00" mysql-bin.000001 | mysql -uroot -proot

３、 从多个日志文件中恢复数据：

bin> mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -uroot -proot

或

bin> ls -t -r -1 mysql-bin.* | xargs mysqlbinlog | mysql -uroot -proot

４、 其他方式恢复：

bin> mysqlbinlog mysql-bin.000001 > /temp/binlog.sql #符号>表示创建新的文件，如果原来已经存在，则会覆盖掉原文件。

bin> mysqlbinlog --stop-datetime="2010-01-02 01:00:00" mysql-bin.000002 >> /temp/binlog.sql #符号>>表示追加到内容后面。

bin> mysql -uroot -proot -e “source /temp/binlog.sql”　#这步必须执行

本文转自 houzaicunsky 51CTO博客，原文链接:http://blog.51cto.com/hzcsky/481430