MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DML、DDL、DCL，不记录SELECT, 以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

开启binlog日志

• 编辑mysql配置文件my.cnf

# 设置binlog日志名字前缀，也可指定二进制日志生成的路径，如：log-bin=/usr/local/mysql/log/mysql-bin
log-bin=mysql-bin

• 查看binlog日志是否开启

mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------------+
| Variable_name                          | Value                                |
+----------------------------------------+--------------------------------------+
| log_bin                                | ON                                   |
| log_bin_basename                       | /usr/local/mysql/var/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/var/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                  |
| log_bin_use_v1_row_events              | OFF                                  |
| log_builtin_as_identified_by_password  | OFF                                  |
| log_error                              | /usr/local/mysql/var/error.log       |
| log_error_verbosity                    | 3                                    |
| log_output                             | FILE                                 |
| log_queries_not_using_indexes          | OFF                                  |
| log_slave_updates                      | OFF                                  |
| log_slow_admin_statements              | OFF                                  |
| log_slow_slave_statements              | OFF                                  |
| log_statements_unsafe_for_binlog       | ON                                   |
| log_syslog                             | OFF                                  |
| log_syslog_facility                    | daemon                               |
| log_syslog_include_pid                 | ON                                   |
| log_syslog_tag                         |                                      |
| log_throttle_queries_not_using_indexes | 0                                    |
| log_timestamps                         | UTC                                  |
| log_warnings                           | 2                                    |
+----------------------------------------+--------------------------------------+
21 rows in set (0.00 sec)

binlog日志包括两类文件：

• 二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。
• 二进制日志文件(文件名后缀为.00000)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

binlog写入流程

事务执行过程中，binlog 首先会被写到 binlog cache 中；事务提交的时候，再讲binlog cache 写到 binlog 文件中。一个事务的 binlog 是原子的，无论多大都需要保证完整性。

系统为每个客户端线程分配一个 binlog cache，其大小由 binlog_cache_size 控制。如果binlog cache 超过阀值，就会临时持久化到磁盘。当事务提交的时候，再将 binlog cache 中完整的事务持久化到磁盘中，并清空 binlog cache。

每个客户端线程都有自己独立的 binlog cache，但是会共享一份 binlog files。

上面的 write 是指把binlog cache 写到文件系统的 page cache，并没有写入到磁盘中，因此速度较快。

fsync 是实际的写盘操作，占用磁盘的 IOPS。

write 和 fsync 的写入时机，是由sync_binlog 控制的：

• sync_binlog=0：每次事务提交都只 write，不 fsync；
• sync_binlog=1：每次事务提交都会fsync；
• sync_binlog=N（N>1）：每次提交事务都会 write，累计N 个后再执行 fsync。

在出现 IO 瓶颈的情况下，可以考虑将 sync_binlog 设置成一个大的值。比较常见的是将 N设置为 100~1000。但是存在的风险是，当主机异常重启时会丢失 N 个最近提交的事务 binlog。

binlog相关配置

log_bin

• log-bin [=file_name]：设置此参数表示启用binlog功能，并指定路径名称，生产中都要开启binlog
• sql_log_bin：会话级别的binlog开关控制，默认是开启的，可以在当前会话级别动态修改临时关闭binlog（主从延迟优化），set session sql_log_bin=0；

binlog_cache_size

设置binlog cache(默认32K)，每个线程单独分配内存空间，所有未提交的二进制日志文件会被记录到用户工作空间的binlog cache中，等该事务提交时直接将缓冲区中的binlog写入二进制日志文件里。

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

判断binlog_cache_size是否设置过小的依据，如果Binlog_cache_disk_use>0(次数)，说明事务未提交，binlog在用户工作空间存放不下，需要借用tmp目录。

sync_binlog

同步binlog的方式

• 0：默认，提交同步到文件系统缓存
• 1：commit，通过fsync方式，直接写入disk的binlog文件中(最安全)，与redo的双一模式。
• >1：sync_binlog=N，如果N>1，在意外发生的时候，就表示会有N-1个dml没有被写入binlog中，有可能就会发生主从数据不一致的情况。

max_binlog_size

binlog文件大小，默认1G

如果是row模式，需要增加binlog文件的大小，因为行模式产生的日志量相对较大。如果超过了该值，就会产生新的日志文件，后缀名+1，并且记录到.index文件里面。

binlog_format

设置binlog记录的模式：行(row)模式、语句(statement)模式、mixed模式。动态参数，可以会话级别修改

--binlog-do-db、--binlog-ingore-db

表示需要写入或者忽略写入哪些库的日志，默认为空，表示可以将所有库的日志写入到二进制文件里面

expire_logs_days

binlog过期清理时间，默认是0：不自动清除。假设expire_logs_days=5，表示系统保留5天binlog，第六天到来时会删除第一天的binlog。

binlog操作命令

• 查看所有binlog日志列表

show master logs;

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       177 |
| mysql-bin.000012 |      1267 |
| mysql-bin.000013 |       241 |
| mysql-bin.000014 |       751 |
| mysql-bin.000015 |       461 |
+------------------+-----------+
5 rows in set (0.00 sec)

• 查看master状态

即最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值。

show master status;

mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |                        |
+------------------+----------+--------------+--------------------------
| mysql-bin.000015 |      461 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

• 刷新log日志

flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;

flush logs;

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；

• 重置（清空）所有binlog日志

reset master;

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | 
+------------------+----------+--------------+--------------------------+
| mysql-bin.000001 |      154 |              | mysql,information_schema |                   
+------------------+----------+--------------+--------------------------+
1 row in set (0.01 sec)

binlog内容查看命令

• mysqlbinlog

binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看。

mysqlbinlog mysql-bin.000001

• show binlog

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

• IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）
• FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
• LIMIT【offset】：偏移量(不指定就是0)
• row_count :查询总条数（不指定就是所有行）

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: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'aeddb5b6-9e22-11e9-81af-080027a2d584:1'
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 291
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
        Pos: 291
 Event_type: Table_map
  Server_id: 1
End_log_pos: 341
       Info: table_id: 111 (rbac.user)
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
        Pos: 341
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 390
       Info: table_id: 111 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: mysql-bin.000001
        Pos: 390
 Event_type: Xid
  Server_id: 1
End_log_pos: 421
       Info: COMMIT /* xid=54 */
7 rows in set (0.00 sec)

ERROR: 
No query specified

利用mysqlbinlog恢复数据

通过 mysqlbinlog 命令从 binlog 日志中导出可执行的 SQL 文件，并将数据导入到mysql。

例如：

mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql;
mysql -uroot -p < /path/bak.sql;