oracle 二进制日志格式,二进制日志

二进制日志记录了对数据库执行更改的所有操作，但是不包括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的恢复，这些性能损失绝对是可以并且是应该被接受的。

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

这里的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 globalbinlog_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 setusername=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)