Mysql binlog详解

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

开启binlog日志

  • 编辑mysql配置文件my.cnf
1
2
# 设置binlog日志名字前缀,也可指定二进制日志生成的路径,如:log-bin=/usr/local/mysql/log/mysql-bin
log-bin=mysql-bin
  • 查看binlog日志是否开启
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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。

mysql

每个客户端线程都有自己独立的 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写入二进制日志文件里。

1
2
3
4
5
6
7
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;

1
2
3
4
5
6
7
8
9
10
11
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;

1
2
3
4
5
6
7
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;

1
2
3
4
5
6
7
8
9
10
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 :查询总条数(不指定就是所有行)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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。

例如:

1
2
mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql;
mysql -uroot -p < /path/bak.sql;
有用就打赏一下作者吧!