二进制日志 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_</