2022年9月4日星期日

MySQL二进制日志(binlog)总结

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

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

一、binlog日志的内容

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

二、binlog日志的作用

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

2、主从复制:在主从复制模式下,必须在主服务器上开启binlog日志;

3、操作审计:对所有更改数据的操作进行审计;

三、binlog日志的格式

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

statement模式

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

优点:

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

缺点:

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

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

row模式

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

优点:

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

缺点:

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

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

mixed模式

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

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

a、表的存储引擎为NDB,这时对表的DML操作都会以row格式记录 ;

b、使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数 ;

c、使用了insert delay语句 ;

d、使用了用户自定义函数UDF ;

e、使用了临时表;

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

四、binlog日志的开启

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

[mysqld]

log-bin=dir/[filename]

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

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

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

注意:配置文件修改保存后,一定要重启MySQL。

五、binlog日志的查看

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

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

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.00 sec)

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

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

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       987 |

| mysql-bin.000002 |     22289 |

| mysql-bin.000003 | 542645484 |

+------------------+-----------+

3 rows in set (0.00 sec)

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

mysql> show master status;

+------------------+-----------+--------------+------------------+-------------------+

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+-----------+--------------+------------------+-------------------+

| mysql-bin.000004 |    426484 |              |                  |                   |

+------------------+-----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

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

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

mysqlbinlog filename.number

例如:

mysqlbinlog mylog.000001

或者

mysqlbinlog usr/mysql/data/mylog.000001

//如果不指定路径,mysqlbinlog 命令将在当前工作目录下查找 mylog.000001 文件

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

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

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

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

reset master;

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

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

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

//删除bin-log.000002之前的二进制日志

purge binary logs to 'bin-log.000002';

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

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

//删除 2021-06-01 00:00:00 之前创建的二进制日志

purge binary logs before '2021-06-01 00:00:00";

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

mysql> show variables like 'expire_logs_days';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| expire_logs_days | 10    |

+------------------+-------+

1 row in set (0.00 sec)

六、binlog日志相关参数

【a】sync_binlog

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

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

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

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

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

mysql> show variables like 'sync_binlog';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sync_binlog   | 0     |

+---------------+-------+

1 row in set (0.00 sec)

【b】binlog_format

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

mysql> show variables like 'binlog_format';

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

【c】log_bin = /mysql/bin_log/mysql_binlog

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

【d】expire_logs_days = 10

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

【e】max_binlog_size = 1​00M

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

七、总结

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

参考资料:

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

————————————————

版权声明:本文为CSDN博主「每天都要进步一点点」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/Weixiaohuai/article/details/117596317

留下您的评论