Mysql学习之--Mysql二进制日志管理

简介：
     MySQL的二进制日志可以说或是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是失误安全型的.
      MySQL的二进制日志的作用是显而易见的，可以方便的备份这些日志以便做数据恢复，也可以作为主从复制的同步文件，然而二进制日志的大小可能会根据不同的需求而存在麻烦，所以让日志回滚是必须的，当然MySQL已经为我们提供了二进制回滚的功能，那就是max_binlog_size参数。

       默认MySQL的二进制日志达到1G后就会自动回滚，如果我们想要更小的二进制日志，可以使用max_binlog_size参数来设置。测试使用max_binlog_size=200M，具体应用就是在配置文件里添加这个参数max_binlog_size=200M，官方文档是这样解释的：

        mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到 max_binlog_size，还会自动创建新的二进制日志。如果你正使用大的事务，二进制日志还会超过 max_binlog_size：事务全写入一个二进制日志中，绝对不要写入不同的二进制日志中。也就是说，在达到max_binlog_size的时候，如果正在处理一个大的事务，那么二进制日志会在处理完这个事务后才会回滚，所以该二进制日志可能会大于所设定的max_binlog_size。

         在主从复制的应用中，可能我们不希望二进制日志过大，因为日志过大可能会影响日志的执行效率，适当调整max_binlog_size的值还是非常有意义的，当然还要记得定期清理长时间不用的日志哦，要不然你的硬盘空间很快就会被用完的。

开启mysql二进制日志：

编辑my.cnf，添加

[root@ogg mysql]# cat /etc/my.cnf
# binary logging is required for replication
log-bin = /var/lib/mysql/mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
binlog_format=row

     开启日志后需要myssqladmin flush logs才能生效。需要注意的是log-bin指定扩展名是无效的，当mysql创建二进制日志文件时，首先创建一个以“mysql_log_bin”为名称，以“.index”为后缀的文件；再创建一个以“mysql_log_bin”为名称，以 “.000001”为后缀的文件。当mysql服务重新启动一次以“.000001”为后缀的文件会增加一个，并且后缀名加1递增；如果日志长度超过了 max_binlog_size的上限（默认是1G）也会创建一个新的日志文件；使用flush logs（mysql命令符）或者执行mysqladmin –u –p flush-logs（windows命令提示符）也会创建一个新的日志文件。

案 例分析：

事务做DML操作：
mysql> delete from test2 where id=60;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into test2 values (60,‘jack‘);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> update test2 set name=‘hellen‘ where id=50;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看日志：
[root@ogg mysql]# mysqlbinlog mysql-bin.000008 -d test

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140928 13:44:37 server id 1  end_log_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 140928 13:44:37 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
RaAnVA8BAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABFoCdUEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAADM7vj8=
‘/*!*/;
# at 114
#140928 13:45:40 server id 1  end_log_pos 182   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1411883140/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 182
# at 229
#140928 13:45:40 server id 1  end_log_pos 229   Table_map: `test`.`test2` mapped to number 66
#140928 13:45:40 server id 1  end_log_pos 268   Delete_rows: table id 66 flags: STMT_END_F
BINLOG ‘
hKAnVBMBAAAALwAAAOUAAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
hKAnVBkBAAAAJwAAAAwBAAAAAEIAAAAAAAEAAv/8PAAAAARqYWNr
‘/*!*/;
# at 268
#140928 13:45:40 server id 1  end_log_pos 295   Xid = 11
COMMIT/*!*/;
# at 295
#140928 13:46:39 server id 1  end_log_pos 363   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1411883199/*!*/;
BEGIN
/*!*/;
# at 363
# at 410
#140928 13:46:39 server id 1  end_log_pos 410   Table_map: `test`.`test2` mapped to number 66
#140928 13:46:39 server id 1  end_log_pos 449   Write_rows: table id 66 flags: STMT_END_F
BINLOG ‘
v6AnVBMBAAAALwAAAJoBAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
v6AnVBcBAAAAJwAAAMEBAAAAAEIAAAAAAAEAAv/8PAAAAARqYWNr
‘/*!*/;
# at 449
#140928 13:46:39 server id 1  end_log_pos 476   Xid = 14
COMMIT/*!*/;
# at 476
#140928 13:49:22 server id 1  end_log_pos 544   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1411883362/*!*/;
BEGIN
/*!*/;
# at 544
# at 591
#140928 13:49:22 server id 1  end_log_pos 591   Table_map: `test`.`test2` mapped to number 66
#140928 13:49:22 server id 1  end_log_pos 643   Update_rows: table id 66 flags: STMT_END_F
BINLOG ‘
YqEnVBMBAAAALwAAAE8CAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
YqEnVBgBAAAANAAAAIMCAAAAAEIAAAAAAAEAAv///DIAAAAEam9obvwyAAAABmhlbGxlbg==
‘/*!*/;
# at 643
#140928 13:49:22 server id 1  end_log_pos 670   Xid = 16
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

查看日志：

[root@ogg mysql]# pwd
/var/lib/mysql
[root@ogg mysql]# ls -l
total 28820
-rw-rw---- 1 mysql mysql       56 Sep 17 14:03 auto.cnf
-rw-rw---- 1 mysql mysql 18874368 Sep 28 15:23 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Sep 28 15:23 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Sep 17 13:51 ib_logfile1
-rw-rw---- 1 mysql mysql       32 Sep 26 17:35 master-log-bin.index
drwx------ 2 mysql root      4096 Sep 17 13:51 mysql
-rw-rw---- 1 mysql mysql      133 Sep 28 13:14 mysql-bin.000001
-rw-rw---- 1 mysql mysql      574 Sep 28 13:22 mysql-bin.000002
-rw-rw---- 1 mysql mysql      417 Sep 28 13:27 mysql-bin.000003
-rw-rw---- 1 mysql mysql      315 Sep 28 13:29 mysql-bin.000004
-rw-rw---- 1 mysql mysql      314 Sep 28 13:31 mysql-bin.000005
-rw-rw---- 1 mysql mysql      133 Sep 28 13:33 mysql-bin.000006
-rw-rw---- 1 mysql mysql      314 Sep 28 13:44 mysql-bin.000007
-rw-rw---- 1 mysql mysql     9539 Sep 28 15:23 mysql-bin.000008
-rw-rw---- 1 mysql mysql      256 Sep 28 13:44 mysql-bin.index
-rw-rw---- 1 mysql root     77132 Sep 28 15:13 ogg.err
-rw-rw---- 1 mysql mysql        5 Sep 28 13:44 ogg.pid
drwx------ 2 mysql mysql     4096 Sep 17 13:51 performance_schema
drwx------ 2 mysql root      4096 Sep 26 15:33 test

由于日志是以二进制方式存储的，不能直接读取，需要使用mysql自带的mysqlbinlog工具来进行查看

mysqlbinlog mysql-bin.000002 -d test

mysqlbinlog有一些选项可以使用,简单说明常用选项:

-d,--database=name :指定数据库名称,只列出指定数据库的操作.
-D, --disable-log-bin :执行恢复的时候,禁止二进制日志.可以防止同一台MySQL加上-t时进入死循环
-o,--offset=n :忽略掉日志前n行命令
-r,--result-file=name :将输出日志到指定文件
-R, --read-from-remote-server :从一个MySQL服务器上读取二进制
-s,--short-form :显示简单格式,省略一些信息
-S, --socket=name  :socket文件连接path.
-t, --to-last-log  :和-R一起使用,在二进制日志结束的时候并不会停止,而是在MySQL服务器最后生成的binlog结束,如果输出和输入都在一台MySQL上可能会导致死循环.
--set-charset=char-name :在输出文本格式的时候,在第一行加上set names char-name.
--start-datetime=# --stop-datetime=# :指定输出起始日期的日志.
--start-position=# --stop-position=# :指定起始日志的位置.

清理：
删除全部二进制日志：

reset master

删除部分日志：

PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
PURGE MASTER LOGS TO ‘mysql-bin.******‘命令，是将‘******‘编号之前的所有日志进行删除
PURGE MASTER LOGS BEFORE ‘yyyy-mm-dd hh:mm:ss‘命令，是将在‘yyyy-mm-dd hh:mm:ss‘时间之前的所有日志进行删除

设置日志过期时间：
修改my.cnf

expire_log_day=5

这里设置保存5天的日志，超过5天的日志会被自动删除

恢复：
完全恢复：

mysqlbinlog mysql-bin.00001|mysql -uroot -p

基于时间点的恢复：
如果误删了一张表，使用完全恢复是没有用的，因为日志里同样也保留着删除的sql语句，所以我们需要恢复到误操作前的状态，然后跳过误操作的语句。
假如我在20:00误删了一张表，可以使用以下语句恢复：

mysqlbinlog --stop-date=‘2012-06-05 19:59:59‘ /var/log/mysql-bin.000001 | mysql -uroot -p

跳过误删除的时间点，再执行：

mysqlbinlog --start-date=‘2012-06-05 20:01:00‘ /var/log/mysql-bin.000001 | mysql -uroot -p

基于位置点的恢复：
基于位置点的恢复可以得到更为精确的数据。

如上图，drop table test这条语句的起始位置是889107，终止位置是889189，那么我们可以使用于以下语句进行恢复：

mysqlbinlog --stop-position=‘889107‘ /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
mysqlbinlog --start-position=‘889189‘ /var/lib/mysql/mysql-bin.000001|mysql -uroot -p

有时有可能因为系统版本的问题，以上方法行不通，可以将二进制导出到一个sql文件中，再直接根据sql语句进行恢复

mysqlbinlog  mysqlbinlog.000001 >log.sql

完全恢复案例：

mysql> select * from test2;
+------+-------+
| id   | name  |
+------+-------+
|   10 | tom   |
|   20 | jerry |

恢复以前的数据：
[root@ogg mysql]# mysqlbinlog  /var/lib/mysql/mysql-bin.000003|mysql -uroot -poracle

恢复后查询： 
mysql> select * from test2;
+------+-------+
| id   | name  |
+------+-------+
|   10 | tom   |
|   20 | jerry |
|   60 | jack  |
|   60 | jack  |
|   60 | jack  |
|   60 | jack  |
|   60 | jack  |
|   50 | john  |
|   40 | ellen |
|   30 | rose  |
+------+-------+
10 rows in set (0.00 sec)

Binlog_format参数也特别重要。从mysql5.1版本开始引入这个参数，该参数可以设置的值有STATEMENT、ROW、MIXED；

（1）STATEMENT格式和之前的mysql版本一样，二进制日志文件记录的是日志的逻辑失SQL语句。

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况，此时可以将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。

（3）MIXED格式下，mysql默认采用的STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况包括：

1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录

2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数

3）使用了INSERT DELAY语句

4）使用了用户定义函数

5）使用了临时表

binary log相关变量和参数

命令行参数

  --log-bin [=file_name]

  设置此参数表示启用binlog功能，并制定路径名称。

  --log-bin-index[=file]

  设置此参数是指定二进制索引文件的路径与名称。

  --max_binlog_size

 Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，

为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。

  --binlog-do-db=db_name

  此参数表示只记录指定数据库的二进制日志

  --binlog-ignore-db=db_name

  此参数表示不记录指定的数据库的二进制日志

系统变量

  log_bin

  binlog_cache_size

  此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

  max_binlog_cache_size

  此参数表示binlog使用的内存最大的尺寸

  binlog_cache_use

  使用二进制日志缓存的事务数量

  binlog_cache_disk_use

  使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。

  binlog_do_db

  binlog_ignore_db

  sync_binlog

  这个参数直接影响mysql的性能和完整性。

  sync_binlog=0：

  当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘，同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

  sync_binlog=0，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，通知文件系统将Binlog文件缓存刷新到磁盘。

  Mysql中默认的设置是sync_binlog=0，即不做任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统Crash，在文件系统缓存中的所有Binlog信息都会丢失。

本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1559144

Mysql学习之--Mysql二进制日志管理