二进制日志

yufengerdai 2013-07-17

展开全文

二进制日志记录了对数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，如果你还想记录SELECT和SHOW操作，那只能使用查询日志，而不是二进制日志了。此外，二进制还包括了执行数据库更改操作的时间和执行时间等信息。二进制日志主要有以下两种作用：

恢复（recovery）。某些数据的恢复需要二进制日志，如当一个数据库全备文件恢复后，我们可以通过二进制日志进行point-in-time的恢复。

复制（replication）。其原理与恢复类似，通过复制和执行二进制日志使得一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。

通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录（datadir），如：

mysql> show variables like 'datadir';  
 --------------- ----------------------------   
| Variable_name | Value     |  
 --------------- ----------------------------   
| datadir       | /usr/local/mysql/data/ |   
 --------------- ----------------------------   
1 row in set (0.00 sec)  
 
mysql> system ls -lh /usr/local/mysql/data/;  
total 2.1G  
-rw-rw----  1 mysql mysql 6.5M Sep 25 15:13 bin_log.000001  
-rw-rw----  1 mysql mysql   17 Sep 25 00:32 bin_log.index  
-rw-rw----  1 mysql mysql 300M Sep 25 15:13 ibdata1  
-rw-rw----  1 mysql mysql 256M Sep 25 15:13 ib_logfile0  
-rw-rw----  1 mysql mysql 256M Sep 25 15:13 ib_logfile1  
drwxr-xr-x  2 mysql mysql 4.0K May  7 10:08 mysql  
drwx------  2 mysql mysql 4.0K May  7 10:09 test

这里的bin_log.00001即为二进制日志文件，我们在配置文件中指定了名称，所以没有用默认的文件名。bin_log.index为二进制的索引文件，用来存储过往生产的二进制日志序号，通常情况下，不建议手工修改这个文件。

二进制日志文件在默认情况下并没有启动，需要你手动指定参数来启动。可能有人会质疑，开启这个选项是否会对数据库整体性能有所影响。不错，开启这个选项的确会影响性能，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使得性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以并且是应该被接受的。

以下配置文件的参数影响着二进制日志记录的信息和行为：

max_binlog_size

binlog_cache_size

sync_binlog

binlog-do-db

binlog-ingore-db

log-slave-update

binlog_format

参数max-binlog-size指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名 1，并记录到.index文件。从MySQL 5.0开始的默认值为

1 073 741 824，代表1GB（之前的版本max-binlog-size默认大小为1.1GB）

当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中，等该事务提交时（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32KB。此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。Binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。现在来看一个数据库的状态：

mysql> show variables like 'binlog_cache_size';  
 ------------------- -------   
| Variable_name   | Value |  
 ------------------- -------   
| binlog_cache_size | 32768 |   
 ------------------- -------   
1 row in set (0.00 sec)  
 
mysql> show global status like 'binlog_cache%';  
 ----------------------- --------------   
| Variable_name  | Value |  
 ----------------------- ---------------   
| binlog_cache_disk_use | 0     |   
| binlog_cache_use     | 33553 |   
 ----------------------- ---------------   
2 rows in set (0.00 sec)

使用缓冲次数33 553次，临时文件使用次数为0。看来，32KB的缓冲大小对于当前这个MySQL数据库完全够用，所以暂时没有必要增加binlog_cache_size的值。

默认情况下，二进制日志并不是在每次写的时候同步到磁盘（我们可以理解为缓冲写）。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中。这会给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。该默认值为0，如果使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为ON。不过该值为ON时，确实会对数据库的IO系统带来一定的影响。

但是，即使将sync_binlog设为1，还是会有一种情况会导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，因为COMMIT操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过将参数innodb_support_xa设为1来解决，虽然innodb_support_xa与XA事务有关，但它同时也确保了二进制日志和InnoDB存储引擎数据文件的同步。

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的日志。默认为空，表示需要将所有库的日志同步到二进制日志。

如果当前数据库是复制中的slave角色，则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中。如果需要写入，则需要设置log-slave-update。如果你需要搭建master=>slave=>slave架构的复制，则必须设置该参数。

binlog_format参数十分重要，这影响了记录二进制日志的格式。在MySQL 5.1版本之前，没有这个参数。所有二进制文件的格式都是基于SQL语句（statement）级别的，因此基于这个格式的二进制日志文件的复制（Replication）和Oracle 逻辑Standby有点相似。同时，对于复制是有一定要求的如rand、uuid等函数，或者有使用触发器等可能会导致主从服务器上表的数据不一致（not sync），这可能使得复制变得没有意义。另一个影响是，你会发现InnoDB存储引擎的默认事务隔离级别是REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果使用READ COMMITTED的事务隔离级别（大多数数据库，如Oracle、Microsoft SQL Server数据库的默认隔离级别）会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。

MySQL 5.1开始引入了binlog_format参数，该参数可设的值有STATEMENT、ROW和MIXED。

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

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。基于ROW格式的复制类似于Oracle的物理Standby（当然，还是有些区别）。同时，对于上述提及的Statement格式下复制的问题给予了解决。MySQL 5.1版本开始，如果设置了binlog_format为ROW，你可以将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）使用了用户定义函数（UDF）。

5）使用了临时表（temporary table）。

此外，binlog_format参数还有对于存储引擎的限制，如表3-1所示。

表3-1 存储引擎二进制日志格式支持情况

binlog_format是动态参数，因此可以在数据库运行环境下进行更改，例如，我们可以将当前会话的binlog_format设为ROW，如：

mysql> set @@session.binlog_format='ROW';  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> select @@session.binlog_format;  
 -------------------------------   
| @@session.binlog_format |  
 -------------------------------   
| ROW                 |   
 -------------------------------   
1 row in set (0.00 sec)

当然，也可以将全局的binlog_format设置为你想要的格式。不过通常情况下，这个操作可能会带来问题，运行时，请确保更改后不会对你的复制带来影响。如：

mysql> set global binlog_format='ROW';  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> select @@global.binlog_format;  
 ------------------------------   
| @@global.binlog_format |  
 ------------------------------   
| ROW             |   
 ------------------------------   
1 row in set (0.00 sec)

通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库的恢复和复制带来更好的可靠性。但是不能忽略一点的是，这会带来二进制文件大小的增加，有些语句下的ROW格式可能需要更大的容量。比如我们有两张一样的表，大小都为100W，执行UPDATE操作，观察二进制日志大小的变化：

mysql> select @@session.binlog_format\G;  
*************************** 1. row ***************************  
@@session.binlog_format: STATEMENT  
1 row in set (0.00 sec)  
 
mysql> show master status\G;  
*************************** 1. row ***************************  
            File: test.000003  
        Position: 106  
    Binlog_Do_DB:   
Binlog_Ignore_DB:   
1 row in set (0.00 sec)  
 
mysql> update t1 set username=upper(username);   
Query OK, 89279 rows affected (1.83 sec)  
Rows matched: 100000  Changed: 89279  Warnings: 0  
 
mysql> show master status\G;  
*************************** 1. row ***************************  
            File: test.000003  
        Position: 306  
    Binlog_Do_DB:   
Binlog_Ignore_DB:   
1 row in set (0.00 sec)

可以看到，在binlog_format格式为STATEMENT下，执行UPDATE语句二进制日志大小只增加了200字节（306-106）。如果我们使用ROW格式，同样来操作t2表，可以看到：

mysql> set session binlog_format='ROW';  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> show master status\G;  
*************************** 1. row ***************************  
            File: test.000003  
        Position: 306  
    Binlog_Do_DB:   
Binlog_Ignore_DB:   
1 row in set (0.00 sec)  
 
mysql> update t2 set username=upper(username);   
Query OK, 89279 rows affected (2.42 sec)  
Rows matched: 100000  Changed: 89279  Warnings: 0  
 
mysql> show master status\G;  
*************************** 1. row ***************************  
            File: test.000003  
        Position: 13782400  
    Binlog_Do_DB:   
Binlog_Ignore_DB:   
1 row in set (0.00 sec)

这时你会惊讶地发现，同样的操作在ROW格式下竟然需要13 782 094字节，二进制日志文件差不多增加了13MB，要知道t2表的大小也不超过17MB。而且执行时间也有所增加（这里我设置了sync_binlog=1）。这就是因为，这时MySQL数据库不再将逻辑的SQL操作记录到二进制日志，而是记录对于每行的更改记录信息。

上面的这个例子告诉我们，将参数binlog_format设置为ROW，对于磁盘空间要求有了一定的增加。而由于复制是采用传输二进制日志方式实现的，因此复制的网络开销也有了增加。