二进制日志 Binary Log

1 什么是 binary log
二进制日志包含 “events”。这些“events” 描述了数据库的变更，例如创建数据表的操作，或者表数据的变革。如果使用的不是基于行的日志，二进制日志同时也包含那些 “有可能”改变数据表的“events”，例如 没有匹配任何 的 DELETE 语句。它也包含每条语句更新数据所花费的时间。

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data

2 binary log 的用途
A 复制。 master server 的 bin-log 记载了 master server 的 数据变更记录，这个变更记录随后被发送给 slave servers, slave servers 执行这些变更，使得自身数据与 master server 数据一致

B 数据恢复。 当一个备份被恢复后，在 备份恢复后的 “events”需要重新执行

bin-log 不会记录 SELECT 和 SHOW 之类 对数据没有修改的的语句
bin-log 使得服务器的性能有轻微下降，但是相对于通过它 进行 复制和恢复带来的好处，开启bin-log是完全值得的

The binary log has two important purposes:
A： For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 18.2, “Replication Implementation”.

B ： Certain data recovery operations require use of the binary log.
After a backup has been restored, the events in the binary log that
were recorded after the backup was made are re-executed. These events
bring databases up to date from the point of the backup. See Section
8.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

3 开启 binary log

step1 查看是否开启

mysql> show variables like  '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | OFF                  |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

step2 开启
修改 mysql 配置文件并重启服务器

log_bin = mysql_bin

或者附加 –log-bin[=base_name] 选项重启服务器

mysqld --log-bin=mysql_bin restart

step3

mysql> show variables like  '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

解释：
如果 base_name 缺省，默认值 是 pid-file 的value 后面加上 “-bin”,例如iZ23aazo3fzZ-bin （pid-file 默认value 是 机器名）。bin-log 默认目录是 data-directory,可以在 base_name 前面加上绝对路径指定其他目录
如果指定后缀名，例如 –log-bin=base_name.extension，后缀名将被忽略。

mysqld 在 base_name 后面 附件一个数字后缀。服务器新建一个log file,数字后缀就会增加，形成一系列有序的log files。
服务器在以下三种情况下会创建新的log file：
a 服务器重启
b flush log
c 当前 bin-log 文件大小超出 max_binlog_size
由于事务只能记录于同一个文件，当使用一个大的事务时，bin-log 可能 超出 max_binlog_size 所规定的大小
mysqld 会创建一个 bin-log 索引文件，它包含所有被使用的bin-log 名称。默认的，它的文件名是base_name加上 “.index”后缀。
可以通过 –log-bin-index[=file_name] 选项修改索引文件名称。
不要手工编辑索引文件，这会使得 mysqld 无法识别

4 查看bin-log

step1

mysql> show binlog events in  'mysql_bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql_bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.40-log, Binlog ver: 4
1 row in set (0.00 sec)

step2

create table test.test_bin( 
    `id` int(10) primary key auto_increment,
    `name` varchar(255)
); 

step3

mysql> show binlog events in  'mysql_bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql_bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.40-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql_bin.000001
        Pos: 107
 Event_type: Query
  Server_id: 1
End_log_pos: 266
       Info: use `test`; create table test.test_bin(
        `id` int(10) primary key auto_increment,
        `name` varchar(255)
)
2 rows in set (0.00 sec)

step4

insert into test.test_bin(`name`) values ('orange'); 

step5

 mysql> show binlog events in  'mysql_bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql_bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.40-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql_bin.000001
        Pos: 107
 Event_type: Query
  Server_id: 1
End_log_pos: 266
       Info: use `test`; create table test.test_bin(
        `id` int(10) primary key auto_increment,
        `name` varchar(255)
)
*************************** 3. row ***************************
   Log_name: mysql_bin.000001
        Pos: 266
 Event_type: Query
  Server_id: 1
End_log_pos: 334
       Info: BEGIN
*************************** 4. row ***************************
   Log_name: mysql_bin.000001
        Pos: 334
 Event_type: Intvar
  Server_id: 1
End_log_pos: 362
       Info: INSERT_ID=1
*************************** 5. row ***************************
   Log_name: mysql_bin.000001
        Pos: 362
 Event_type: Query
  Server_id: 1
End_log_pos: 474
       Info: use `test`; insert into test.test_bin(name) values ('orange')
*************************** 6. row ***************************
   Log_name: mysql_bin.000001
        Pos: 474
 Event_type: Query
  Server_id: 1
End_log_pos: 543
       Info: COMMIT
6 rows in set (0.00 sec)

解释：
Log_name: 此条log存在那个文件中
Pos: log在bin-log中的开始位置
Event_type: log的类型信息
Server_id: 可以查看配置中的server_id,表示log是那个服务器产生
End_log_pos：log在bin-log中的结束位置
Info: log的一些备注信息，可以直观的看出进行了什么操作

5 相关参数

mysql> show variables like  '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

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

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

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

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

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

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

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

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

max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

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

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

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

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

6 bin_log 操作

a1 删除bin-log
方法一：设置日志保留时长expire_logs_days自动删除
step1 查看当前日志保存天数 (默认是0，也就是logs不过期，可通过设置全局的参数，使他临时生效)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

step2 设置了只保留7天BINLOG, 下次重启mysql这个参数默认会失败，所以需在my.cnf中设置

set global expire_logs_days=7;  

方法二：手动删除BINLOG (purge binary logs)

reset {MASTER | SLAVE}
PURGE {MASTER | BINARY} LOGS TO ‘log_name’ 
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’ 

例如

mysql> reset master;   //删除master的binlog
mysql> reset slave;    //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002';   //删除指定日志文件的日志索引中binlog日志文件

方法三 ：用操作系统命令直接删除

a2 查看最后一个bin日志文件是那个

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

a3 启用新的日志文件,一般备份完数据库后执行

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000003 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

a4 如果用户有super权限，可以启用或禁用当前会话的binlog记录

mysql> set sql_log_bin=1/0;

a5 查看master的binlog日志

mysql> show master logs;

或者

mysql> show binary logs;

a6 显示当前注册的slave的列表。不以–report-host=slave_name选项为开头的slave不会显示在本列表中

mysql> show slave hosts; 

7 bin-log 三种格式

Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW

a Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制：

• LOAD_FILE()

• UUID()

• USER()

• FOUND_ROWS()

• SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

同时在INSERT …SELECT 会产生比 RBR 更多的行级锁

b Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

c Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

Mysql默认是使用Statement日志格式，推荐使用MIXED.
由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。

The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.

In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. It is important therefore that tables always use a primary key to ensure rows can be efficiently identified. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.

A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED. 

Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-based logging format is the default.

The logging format can also be set or limited by the storage engine being used. This helps to eliminate issues when replicating certain statements between a master and slave which are using different storage engines.

With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.

You can avoid these issues by using MySQL’s row-based replication instead.

【参考】http://dev.mysql.com/doc/refman/5.7/en/binary-log.html