MySQL二进制日志（binlog）总结

目录

一、binlog日志的内容

二、binlog日志的作用

三、binlog日志的格式

四、binlog日志的开启

五、binlog日志的查看

六、binlog日志相关参数

七、总结

MySQL二制进日志&＃xff08;binlog&＃xff09;&＃xff0c;也叫做变更日志&＃xff08;update log&＃xff09;&＃xff0c;是 MySQL 中非常重要的日志。二进制日志&＃xff08;binlog&＃xff09;中记录了对MySQL数据库执行更改的所有操作&＃xff0c;并且记录了语句发生时间、执行时长、操作数据等其它额外信息&＃xff0c;但是它不记录select、show等那些不修改数据的SQL语句。二进制日志&＃xff08;binlog&＃xff09;主要用于数据库恢复和主从复制&＃xff0c;以及审计操作。如果 MySQL 数据库意外停止&＃xff0c;可以通过二进制日志文件来查看用户执行了哪些操作&＃xff0c;对数据库服务器文件做了哪些修改&＃xff0c;然后根据二进制日志文件中的记录来恢复数据库服务器。

下面我们分几个方面介绍一下MySQL binlog二进制日志文件的相关知识。

一、binlog日志的内容

二进制日志&＃xff08;binlog&＃xff09;中记录了对MySQL数据库执行更改的所有操作&＃xff0c;并且记录了语句发生时间、执行时长、操作数据等其它额外信息&＃xff0c;但是它不记录select、show等那些不修改数据的SQL语句。

二、binlog日志的作用

1、基于时间点的恢复&＃xff1a;在备份文件恢复的基础上&＃xff0c;通过binlog日志&＃xff0c;可以将数据库恢复到某一时间点&＃xff1b;

2、主从复制&＃xff1a;在主从复制模式下&＃xff0c;必须在主服务器上开启binlog日志&＃xff1b;

3、操作审计&＃xff1a;对所有更改数据的操作进行审计&＃xff1b;

三、binlog日志的格式

在MySQL中&＃xff0c;binlog二进制日志文件的格式主要有三种&＃xff1a;statement、row、mixed。下面我们分别看看三种格式有什么不同以及优缺点。

• statement模式

statement模式下的 binlog 记录的是数据库执行的原生SQL语句。采用该格式的二进制日志文件最小&＃xff0c;性能最高&＃xff0c;主从复制网络带宽小&＃xff0c;但很容易出现主从不一致的问题。

优点&＃xff1a;

statement模式下&＃xff0c;不需要记录每一行的变化&＃xff0c;所以bin-log日志量相对较小&＃xff0c;节约IO&＃xff0c;提高性能。因为它只需要记录所执行的语句的细节&＃xff0c;以及执行语句时的上下文信息。

缺点&＃xff1a;

由于它是记录执行语句&＃xff0c;为了让这些语句在slave端也能正确执行&＃xff0c;它还必须记录每条语句在执行的时候的一些相关信息&＃xff0c;也就是上下文信息&＃xff0c;来保证所有语句在slave端能够得到和在master端相同的执行结果。statement模式下&＃xff0c;在执行一些不确定的函数&＃xff08;如uuid(),now()&＃xff09;时会出现主从数据不一致问题。

例如&＃xff1a;使用了获取当前时间的函数&＃xff0c;如果记录的sql&＃xff0c;在恢复的时候重新执行&＃xff0c;当前时间肯定就变了&＃xff0c; 这就是statement模式的其中一个缺点&＃xff0c;使用下面的row模式可以解决这个问题。

• row模式

binlog 记录的是数据行的更改情况&＃xff0c;即数据行在更改前、更改后的变化情况&＃xff0c;所以row模式下的二进制日志文件最大&＃xff0c;对性能会有一定影响&＃xff0c;主从复制网络带宽高&＃xff0c;但在主从复制模式下可靠性最好。

优点&＃xff1a;

在row模式下&＃xff0c;bin_log中不需要记录执行的sql语句的上下文信息&＃xff0c;仅仅只需要记录哪一条记录被修改&＃xff0c;修改成什么样&＃xff0c;所以日志内容会非常清楚的记录每一行数据修改的细节。

缺点&＃xff1a;

row模式下&＃xff0c;所有的执行语句都会记录到日志中&＃xff0c;同时都会以每行记录修改的来记录&＃xff0c;这样生成的日志内容将会非常大&＃xff0c;占用空间。

新版本中的mysql中对row模式也做了优化&＃xff0c;并不是所有的修改都会以row来记录&＃xff0c;像遇到表结构变更的时候就会以statement模式来记录&＃xff0c;如果sql语句确实就是update或者delete等修改数据的语句&＃xff0c;那么还是会记录所有行的变更。 

• mixed模式

mixed实际上就是前两种模式的结合&＃xff0c;在mixed模式下&＃xff0c;MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式&＃xff0c;也是在statement和row之间选择一种。

MySQL 会在一些特定的情况下自动从 statement 格式切换到 row格式&＃xff0c;默认采用statement格式。在以下情况下MySQL将会采用row格式&＃xff1a;

• a、表的存储引擎为NDB&＃xff0c;这时对表的DML操作都会以row格式记录 &＃xff1b;
• b、使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数 &＃xff1b;
• c、使用了insert delay语句 &＃xff1b;
• d、使用了用户自定义函数UDF &＃xff1b;
• e、使用了临时表&＃xff1b;

在主从复制模式下强烈推荐使用row格式。

四、binlog日志的开启

在 MySQL 中&＃xff0c;可以通过在MySQL配置文件中添加 log-bin 选项来开启二进制日志&＃xff0c;格式如下&＃xff1a;

[mysqld]
log-bin&＃61;dir/[filename]

其中&＃xff0c;dir 参数指定二进制文件的存储路径&＃xff0c;filename 参数指定二进制文件的文件名&＃xff0c;其形式为 filename.number&＃xff0c;number 的形式为 000001、000002 等。

每次重启 MySQL 服务后&＃xff0c;都会生成一个新的二进制日志文件&＃xff0c;这些日志文件的文件名中 filename 部分不会改变&＃xff0c;number 会不断递增。

如果没有 dir 和 filename 参数&＃xff0c;二进制日志将默认存储在数据库的数据目录下&＃xff0c;默认的文件名为 hostname-bin.number&＃xff0c;其中 hostname 表示主机名。

注意&＃xff1a;配置文件修改保存后&＃xff0c;一定要重启MySQL。

五、binlog日志的查看

【a】查看是否启用了二进制日志

默认情况下&＃xff0c;二进制日志功能是关闭的&＃xff0c;系统变量log_bin的值为OFF表示没有开启二进制日志。ON表示开启了二进制日志。可以通过以下命令查看二进制日志是否开启&＃xff1a;

mysql> show variables like &＃39;log_bin&＃39;;
&＃43;---------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;---------------&＃43;-------&＃43;
| log_bin | ON |
&＃43;---------------&＃43;-------&＃43;
1 row in set (0.00 sec)

【b】查看所有二进制日志文件列表

可以使用如下命令查看 MySQL 中有哪些二进制日志文件&＃xff1a;

mysql> show binary logs;
&＃43;------------------&＃43;-----------&＃43;
| Log_name | File_size |
&＃43;------------------&＃43;-----------&＃43;
| mysql-bin.000001 | 987 |
| mysql-bin.000002 | 22289 |
| mysql-bin.000003 | 542645484 |
&＃43;------------------&＃43;-----------&＃43;
3 rows in set (0.00 sec)

【c】查看当前正在写入的二进制日志文件

mysql> show master status;
&＃43;------------------&＃43;-----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
&＃43;------------------&＃43;-----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;
| mysql-bin.000004 | 426484 | | | |
&＃43;------------------&＃43;-----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;
1 row in set (0.00 sec)

【d】查看二进制日志文件内容

二进制日志使用二进制格式存储&＃xff0c;不能直接打开查看。如果需要查看二进制日志&＃xff0c;必须使用 mysqlbinlog 命令。格式如下&＃xff1a;

mysqlbinlog filename.number

例如&＃xff1a;

mysqlbinlog mylog.000001
或者
mysqlbinlog usr/mysql/data/mylog.000001
//如果不指定路径&＃xff0c;mysqlbinlog 命令将在当前工作目录下查找 mylog.000001 文件

mysqlbinlog 命令只在当前文件夹下查找指定的二进制日志&＃xff0c;因此需要在二进制日志所在的目录下运行该命令&＃xff0c;否则将会找不到指定的二进制日志文件。

【e】删除二进制日志文件

二进制日志中记录着大量的信息&＃xff0c;如果很长时间不清理二进制日志&＃xff0c;将会浪费很多的磁盘空间。二进制日志的删除可以通过命令手工删除&＃xff0c;也可以设置自动清理。

1、删除所有二进制日志文件

reset master;

删除所有二进制日志后&＃xff0c;MySQL 将会重新创建新的二进制日志&＃xff0c;新二进制日志的编号从 000001 开始。

2、根据编号删除二进制日志文件

使用  purge binary logs to xxx 语句&＃xff0c;可以删除指定二进制日志的编号之前的日志。例如&＃xff1a;

//删除bin-log.000002之前的二进制日志
purge binary logs to &＃39;bin-log.000002&＃39;;

3、根据创建时间删除二进制日志文件

同上&＃xff0c;使用 purge binary logs before语句&＃xff0c;可以删除指定时间之前创建的二进制日志。例如&＃xff1a;

//删除 2021-06-01 00:00:00 之前创建的二进制日志
purge binary logs before &＃39;2021-06-01 00:00:00";

 另外&＃xff0c;我们也可以设置expire_logs_days参数&＃xff0c;设置自动清理&＃xff0c;其默认值为0&＃xff0c;表示不启用过期自动删除功能&＃xff0c;如果启用了自动清理功能&＃xff0c;表示超出此天数的二进制日志文件将被自动删除&＃xff0c;自动删除工作通常发生在MySQL启动时或FLUSH日志时。

mysql> show variables like &＃39;expire_logs_days&＃39;;
&＃43;------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;------------------&＃43;-------&＃43;
| expire_logs_days | 10 |
&＃43;------------------&＃43;-------&＃43;
1 row in set (0.00 sec)

六、binlog日志相关参数

【a】sync_binlog

sync_binlog用于控制cache的数据commit多少次才刷到磁盘上&＃xff0c;默认是0&＃xff0c;也就是让数据库自己决定同步的频率。如设置成1的话&＃xff0c;则每commit一次就会将cache的数据同步到磁盘上&＃xff0c;这样做最安全&＃xff0c;但是性能最差。

• sync_binlog&＃61;0&＃xff0c;当事务提交后&＃xff0c;Mysql仅仅是将binlog_cache中的数据写入binlog文件&＃xff0c;但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘&＃xff0c;而是让Filesystem自行决定什么时候来做同步。

MySQL中默认的设置是 sync_binlog&＃61;0&＃xff0c;即不作任何强制性的磁盘刷新指令&＃xff0c;这个设置性能是最好的&＃xff0c;但风险也是最大的。一旦系统崩溃&＃xff08;Crash&＃xff09;&＃xff0c;在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。

• sync_binlog&＃61;n&＃xff0c;在进行n次事务提交以后&＃xff0c;Mysql将执行一次fsync之类的磁盘同步指令&＃xff0c;同时文件系统将Binlog文件缓存刷新到磁盘。

可以适当的调整sync_binlog&＃xff0c; 在牺牲一定的一致性下&＃xff0c;获取更高的并发和性能。

mysql> show variables like &＃39;sync_binlog&＃39;;
&＃43;---------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;---------------&＃43;-------&＃43;
| sync_binlog | 0 |
&＃43;---------------&＃43;-------&＃43;
1 row in set (0.00 sec)

【b】binlog_format

指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式&＃xff0c;MySQL 5.7.7之后默认为ROW模式。

mysql> show variables like &＃39;binlog_format&＃39;;
&＃43;---------------&＃43;-----------&＃43;
| Variable_name | Value |
&＃43;---------------&＃43;-----------&＃43;
| binlog_format | STATEMENT |
&＃43;---------------&＃43;-----------&＃43;
1 row in set (0.00 sec)

【c】log_bin &＃61; /mysql/bin_log/mysql_binlog

开启 Binlog 并指定存放日志的位置&＃xff1b;默认使用的设置是“log-bin&＃61;mysql-bin”&＃xff0c;一般是放在data目录中。

【d】expire_logs_days &＃61; 10

expire_logs_day 定义了 MySQL 清除过期日志的时间、二进制日志自动删除的天数。默认值为 0&＃xff0c;表示“没有自动删除”。当 MySQL 启动或刷新二进制日志时可能删除。

【e】max_binlog_size &＃61; 1​00M

max_binlog_size 定义了单个文件的大小限制&＃xff0c;如果二进制日志写入的内容大小超出给定值&＃xff0c;日志就会发生滚动&＃xff08;关闭当前文件&＃xff0c;重新打开一个新的日志文件&＃xff09;。不能将该变量设置为大于 1GB 或小于 4096B&＃xff08;字节&＃xff09;&＃xff0c;其默认值是 1GB。

七、总结

本文将MySQL中其中一个比较重要的日志----binlog二进制日志进行了总结&＃xff0c;binlog主要记录着我们数据的修改操作&＃xff0c;我们从binlog的作用、数据格式、开启、删除、查看等方面对binlog做了一个比较全面的介绍。由于笔者水平有限&＃xff0c;如有不足或疏漏之处&＃xff0c;敬请指出&＃xff0c;希望对大家有所帮助。

参考资料&＃xff1a;

https://dev.mysql.com/doc/refman/5.6/en/binary-log.html

https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_log_bin_basename