MySQL binlog

MySQL binlog http://www.cnblogs.com/Richardzhu/p/3225254.html 1.前言 日志是把数据库的每一个变化都记载到一个专用的文件里，这种文件就叫做日志文件。Mysql默认只打开出错日志，因为过多的日志将会影响系统的处理性能。在5.0前支持文本格式和二进制格式

MySQL binlog

http://www.cnblogs.com/Richardzhu/p/3225254.html

1.前言

    日志是把数据库的每一个变化都记载到一个专用的文件里，这种文件就叫做日志文件。Mysql默认只打开出错日志，因为过多的日志将会影响系统的处理性能。在5.0前支持文本格式和二进制格式，5.0后只支持二进制格式，因为二进制日志在性能、信息处理方面有更多的优点。

2.基础知识

  2.1、二进制日志的启用

    二进制日志由配置文件的log-bin选项负责启用，Mysql服务器将在数据根目录创建两个新文件XXX-bin.001和XXX-bin.index，若配置选项没有给出文件名，Mysql将使用主机名称命名这两个文件，其中.index文件包含一份全体日志文件的清单。
    Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件，而不会记录SELECT和没有实际更新的UPDATE语句。

  2.2、日志文件的扩展

    当停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增，此外，如果日志文件超过max_binlog_size系统变量配置的上限时，也会生成新的日志文件。

  2.3、日志文件的查看

    Mysql提供了mysqlbinlog命令来查看日志文件，如mysqlbinlog xxx-bin.001 | more。在记录每条变更日志的时候，日志文件都会把当前时间给记录下来，以便进行数据库恢复。   

  2.4、日志文件的停用

    可以使用SET SQL_LOG_BIN=0命令停止使用日志文件，然后可以通过SET SQL_LOG_BIN=1命令来启用。

  2.5、使用日志进行数据库恢复

    如果遇到灾难事件，应该用最近一次制作的完整备份恢复数据库，然后使用备份之后的日志文件把数据库恢复到最接近现在的可用状态。使用日志进行恢复时需要依次进行，即最早生成的日志文件要最先恢复：
      mysqlbinlog xxx-bin.00001 | mysql -u root -p
      mysqlbinlog xxx-bin.00002 | mysql -u root -p

3.日志更换策略

  使用索引来循环文件，在以下条件将循环至下一个索引
  a.服务器重启
  b.服务器被更新
  c.日志达到了最大日志长度max_binlog_size
  d.日志被刷新mysql> flush logs;

4.日志格式

    从官网文档中看到，之前的MySQL一直都只有基于statement的复制模式，直到5.1.5版本的MySQL才开始支持row level的复制。从5.0开始，MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给MySQL Replication复制又带来了更大的新挑战。另外，看到官方文档说，从5.1.8版本开始，MySQL提供了除Statement Level和Row Level之外的第三种复制模式：Mixed，实际上就前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本中的Statement Level还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。
  --基于SQL语句的复制(statement-based replication,SBR)，
  --基于行的复制(row-based replication,RBR)，
  --混合模式复制(mixed-based replication,MBR)。
  静态设置binlog格式：

<span>vi my.cnf
  </span><span>log</span><span>-</span>bin <span>=</span> mysql<span>-</span><span>bin
  #binlog_format </span><span>=</span><span> "STATEMENT"
  #binlog_format </span><span>=</span><span> "ROW"
  binlog_format </span><span>=</span> "MIXED"

  动态修改binlog格式：

  mysql<span>></span> <span>SET</span> SESSION binlog_format <span>=</span> <span>'</span><span>STATEMENT</span><span>'</span><span>;
  mysql</span><span>></span> <span>SET</span> SESSION binlog_format <span>=</span> <span>'</span><span>ROW</span><span>'</span><span>;
  mysql</span><span>></span> <span>SET</span> SESSION binlog_format <span>=</span> <span>'</span><span>MIXED</span><span>'</span><span>; 
  mysql</span><span>></span> <span>SET</span> GLOBAL binlog_format <span>=</span> <span>'</span><span>STATEMENT</span><span>'</span><span>;
  mysql</span><span>></span> <span>SET</span> GLOBAL binlog_format <span>=</span> <span>'</span><span>ROW</span><span>'</span><span>;
  mysql</span><span>></span> <span>SET</span> GLOBAL binlog_format <span>=</span> <span>'</span><span>MIXED</span><span>'</span>;

5.binary log相关变量和参数

  5.1、命令行参数

  --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

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

  5.2、系统变量

  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信息都会丢失。

6.常见问题

  6.1、如何清除binlog

  --使用下面的两个命令
  PURGE {MASTER|BINARY} LOGS TO 'log_name'  //log_name不会被清除
  PURGE {MASTER|BINARY} LOGS BEFORE 'date'  //date不会被清除
 
  实例如下：

mysql<span>></span><span> show master logs;
</span><span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> Log_name             <span>|</span> File_size <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000001</span> <span>|</span>       <span>107</span> <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>1</span> row <span>in</span> <span>set</span> (<span>0.00</span><span> sec)

mysql</span><span>></span><span> flush logs;
Query OK, </span><span>0</span> rows affected (<span>0.11</span><span> sec)

mysql</span><span>></span><span> flush logs;
Query OK, </span><span>0</span> rows affected (<span>0.02</span><span> sec)

mysql</span><span>></span><span> flush logs;
Query OK, </span><span>0</span> rows affected (<span>0.01</span><span> sec)

mysql</span><span>></span><span> flush logs;
Query OK, </span><span>0</span> rows affected (<span>0.01</span><span> sec)

mysql</span><span>></span><span> show master logs;
</span><span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> Log_name             <span>|</span> File_size <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000001</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000002</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000003</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000004</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000005</span> <span>|</span>       <span>107</span> <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>5</span> rows <span>in</span> <span>set</span> (<span>0.00</span><span> sec)

mysql</span><span>></span> purge master logs <span>to</span> <span>'</span><span>mysql3306-bin.000002</span><span>'</span><span>;
Query OK, </span><span>0</span> rows affected (<span>0.01</span><span> sec)

mysql</span><span>></span><span> show master logs;
</span><span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> Log_name             <span>|</span> File_size <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000002</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000003</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000004</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000005</span> <span>|</span>       <span>107</span> <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>4</span> rows <span>in</span> <span>set</span> (<span>0.00</span> sec)

<span>[</span><span>root@node4 data</span><span>]</span><span># date
Tue Jul </span><span>30</span> <span>01</span>:<span>27</span>:<span>04</span> CST <span>2013</span><span>
mysql</span><span>></span><span> flush logs;
Query OK, </span><span>0</span> rows affected (<span>0.01</span><span> sec)

mysql</span><span>></span><span> show master logs;
</span><span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> Log_name             <span>|</span> File_size <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000002</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000003</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000004</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000005</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000006</span> <span>|</span>       <span>107</span> <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>5</span> rows <span>in</span> <span>set</span> (<span>0.00</span><span> sec)

mysql</span><span>></span> purge master logs before <span>'</span><span>2013-07-30 01:27:04</span><span>'</span><span>;
Query OK, </span><span>0</span> rows affected (<span>0.02</span><span> sec)

mysql</span><span>></span><span> show master logs;
</span><span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> Log_name             <span>|</span> File_size <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000005</span> <span>|</span>       <span>154</span> <span>|</span>
<span>|</span> mysql3306<span>-</span>bin.<span>000006</span> <span>|</span>       <span>107</span> <span>|</span>
<span>+</span><span>--</span><span>--------------------+-----------+</span>
<span>2</span> rows <span>in</span> <span>set</span> (<span>0.00</span> sec)

  --或使用命令：
  > RESET MASTER

  删除之前所有的binlog，并重新生成新的binlog，后缀从000001开始。

  注：如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是失败，并伴随一个错误。

  不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。

  当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。

  6.2、清除指定部分log

mysql>purge binary logs to 'log-bin.000012';

将log-bin.000012之前的binary logs清掉；

mysql>purge binary logs before '2011-05-28 12:05:38';

将指定时间之前的binary logs清掉；

  6.3、查看当前binary log的情况：

mysql>show master status;

  6.4、查看当前binary log的情况：

显示第一个二进制日志:

mysql>show binlog events;

从 'mysql-bin-log.000002' 第三行开始列出5行;

mysql>show binlog events in 'mysql-bin-log.000002' limit 3,5;

命令行下：

#mysqlbinlog /var/log/mysql/log-bin.000140;

或者

#mysqlbinlog --start-datetime='2011-07-01 00:00:00' --stop-datetime='2010-07-15 00:00:00' /var/log/mysql/log-bin.000020 > ./tmp.log

  6.4、在my.cnf/my.ini中设定binary logs回滚天数：

expire_logs_days = 7

  6.5、记录到二进制日志知的内容配置

   binlog-do-db=sales 只记录sales库 binlog-ignore-db=sales 除sales库不记录，其他都记录。

但是如果在操作数据库之前，不使用use $dbname 那么所有的SQL都不会记录 如果使用了use $dbname,那么判断规则取决于这里的$dbname，而不是SQL中操作的库

  6.6、二进制日志不准确的处理

   默认情况下，并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能二进制日志中最后的语句丢失。 要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使二进制日志在每N次二进制日志写入后与硬盘同步。 即使sync_binlog设置为1,出现崩溃时，也有可能表内容和二进制日志内容之间存在不一致性。

   如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务)， 如果sync_binlog =1并且硬盘/文件系统的确能根据需要进行同步(有些不需要)则不会发生，则输出错误消息 (“二进制日志比期望的要小”)。 在这种情况下，二进制日志不准确，复制应从主服务器的数据快照开始。 为了您的安全，请只打开来源可靠的网址。