一文带你简单了解MySQL各种日志类型

TtrOpsStack 2022-05-19

622

Part1参考资料

MySQL8.0 官网日志地址：https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

Part2日志类型

这6类日志分别为：

慢查询日志： 记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
通用查询日志： 记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
错误日志： 记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
二进制日志： 记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
中继日志： 用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
数据定义语句日志： 记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于 MySQL数据目录中。

开启各种日志的弊端，注意：日志功能会降低MySQL数据库的性能，日志会占用大量的磁盘空间。

Part3慢查询日志(slow query log)

11. 作用剖析

慢查询日志官方文档： https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

作用： 记录所有sql执行时间超过系统变量long_query_time所规定时间的所有查询，方便我们对查询进行优化。

比如long_query_time=10（10是默认值），系统变量long_query_time的意义：如果查询花费的时间超过long_query_time变量规定的秒数，服务器会将计数器状态变量Slow_queries递增。如果启用了慢查询日志，则查询将记录到慢查询日志文件中。Slow_queries（计数器）的作用：查询耗时超过 long_query_time变量规定的秒数。无论是否启用慢查询日志，此计数器都会递增。long_query_time变量的默认值是10，最小值是0。

22. 查看慢查询日志开启状态

查看慢查询日志是否开启，以及慢查询日志文件的位置

mysql> show global variables like "%slow_query_log%";
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log      | ON                                 |
| slow_query_log_file | /data/mysql_data/zbx-db01-slow.log |
+---------------------+------------------------------------+
2 rows in set (0.21 sec)

复制

slow_query_log 系统变量的意义：是否开启慢查询日志。该值可以是 0（或 OFF）以禁用日志或 1（或 ON）以启用日志。日志输出的目的地由 log_output系统变量控制；如果该值为NONE，即使启用了日志，也不会写入日志条目。

通过查看，看到这时慢查询分析已经开启，同时文件保存在 data/mysql_data/zbx-db01-slow.log 文件中

33. 关闭慢查询日志

方式1：Mysql运行中可以通过set来设置slow_query_log变量，不用重启mysql服务就可以马上生效

set global slow_query_log=OFF;
或
set global slow_query_log=0;

复制

方式2：通过在配置文件my.cnf进行修改，需重启mysql服务才会生效

# vi /etc/my.cnf
slow_query_log = OFF
# 或
slow_query_log = 0

复制

44. 开启慢查询日志

方式1：Mysql运行中可以通过set来设置slow_query_log变量，不用重启mysql服务就可以马上生效，但如果一旦重启，设置就会丢失，如果永久设置，还是需要在my.cnf增加配置

set global slow_query_log=ON;
或
set global slow_query_log=1;

复制

方式2：通过在配置文件/etc/my.cnf进行修改，需重启mysql服务才会生效

# vi /etc/my.cnf
slow_query_log = ON
# 或
slow_query_log = 1

复制

55. 查看慢查询的时间阈值

mysql> show global variables like "%long_query_time%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> 

复制

这时看到的慢查询时间为4秒，也就是如果1条sql语句的执行时间超过4秒还没执行完，那么这条sql就会被认为是慢sql，会被记录到慢查询日志中，也意味着要优化这条sql了。

在实际工作中，我们经常会写出一些SQL语句，一条糟糕的SQL语句可能让你的整个程序都非常慢，超过10秒一般用户就会选择关闭你的应用或者网页，如何优化SQL语句将那些运行时间比较长的SQL语句找出呢？MySQL给我们提供了一个很好的功能，那就是慢查询！所谓的慢查询就是通过设置来记录超过一定时间的SQL语句。

66. 设置慢查询时间阈值

方式1：通过set命令设置变量long_query_time，不用重启mysql服务就可以马上生效，但如果一旦重启，设置就会丢失，如果永久设置，还是需要在my.cnf增加配置

mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)

复制

方式2：通过/etc/my.cnf配置文件，需重启mysql服务才会生效

long_query_time = 5

复制

上面将慢查询时间设置为5秒，也就是如果1条sql语句的执行时间超过5秒还没执行完，那么这条sql就会被认为是慢sql，会被记录到慢查询日志中，也意味着要优化这条sql了。

77. 查看慢查询数目

通过全局状态变量Slow_queries，可查询到当前系统中有多少条慢查询记录

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

复制

如上可以看到，当前没有慢查询的sql，1条都没有，说明应用程序里的sql的写的很牛逼了？或许是吧！

Part4通用查询日志(general query log)

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

81. 查看通用查询日志的开启和关闭状态

通过查看全局系统变量general_log查看是开启状态还是关闭状态，查看全局系统变量general_log_file来查看日志文件的路径

mysql> show global variables like "%general_log%";
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | OFF                           | # 处于关闭状态OFF
| general_log_file | /data/mysql_data/zbx-db01.log | # 日志文件路径是/data/mysql_data/zbx-db01.log
+------------------+-------------------------------+
2 rows in set (0.00 sec)

复制

92. 启用通用查询日志

方式1：永久性开启

修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务。格式如下：

[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名

复制

如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。

案例：在/etc/my.cnf中增加如下配置，开启通用查询日志，并指定通用查询日志的目录和文件名

[mysqld]
general_log=ON
general_log_file=/data/mysql_data/zbx-db01-general.log

复制

增加后，需重启mysql服务才会生效，如果是生产环境不方便重启，可找个合适的时间在重启即可

方式2：临时性开启

# 开启
SET GLOBAL general_log=ON; # 开启通用查询日志
SET GLOBAL general_log_file='/data/mysql_data/zbx-db01-general.log'; # 设置日志文件保存位置

复制

临时性开启可以马上生效，但重启Mysql服务后会丢失配置，因此建议同时也增加到配置文件my.cnf中，待下次有机会重启的时候就会从配置文件加载配置，不会丢失了。

103. 查看通用查询日志的文件

通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件。每台MySQL服务器的通用查询日志内容是不同的。

在Windows操作系统中，使用文本文件查看器；
在Linux系统中，可以使用vi工具或者gedit工具查看；
在Mac OSX系统中，可以使用文本文件查看器或者vi等工具查看。

从 SHOW VARIABLES LIKE 'general_log%'; 结果中可以看到通用查询日志的位置

查看如下，使用tail查看最尾部的10行，或tailf实时监听通用查询日志文件/data/mysql_data/zbx-db01-general.log

[root@zbx-db01 ~]# tail /data/mysql_data/zbx-db01-general.log  
2022-04-22T02:54:16.023719Z       276 Query     select a.alertid,a.mediatypeid,a.sendto,a.subject,a.message,a.status,a.retries,e.source,e.object,e.objectid,a.parameters,a.eventid,a.p_eventid from alerts a left join events e on a.eventid=e.eventid where alerttype=0 and a.status=3 order by a.alertid
2022-04-22T02:54:16.024976Z       276 Query     commit
2022-04-22T02:54:16.172102Z        20 Query     select t.taskid,t.type,t.clock,t.ttl,c.command_type,c.execute_on,c.port,c.authtype,c.username,c.password,c.publickey,c.privatekey,c.command,c.alertid,c.parent_taskid,c.hostid,cn.itemid,d.data,d.parent_taskid,d.type from task t left join task_remote_command c on t.taskid=c.taskid left join task_check_now cn on t.taskid=cn.taskid left join task_data d on t.taskid=d.taskid where t.status=1 and t.proxy_hostid=10352 and (t.ttl=0 or t.clock+t.ttl>1650596056) order by t.taskid
2022-04-22T02:54:16.516836Z        17 Query     begin
2022-04-22T02:54:16.517333Z        17 Query     insert into history (itemid,clock,ns,value) values (29175,1650596055,625394377,0.080000000000000002),(32655,1650596055,625829724,0),(32715,1650596055,628301839,0)
2022-04-22T02:54:16.517859Z        17 Query     commit
2022-04-22T02:54:16.518853Z        17 Query     begin
2022-04-22T02:54:16.519244Z        17 Query     commit
2022-04-22T02:54:16.519544Z        17 Query     begin
2022-04-22T02:54:16.519917Z        17 Query     commit
[root@zbx-db01 ~]# 
[root@zbx-db01 ~]# 

复制

在通用查询日志里面，我们可以清楚地看到，什么时候开启了新的客户端登陆数据库，登录之后做了什么 SQL 操作，针对的是哪个数据表等信息。

114. 关闭通用查询日志

方式1：永久性关闭

修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务。格式如下：

[mysqld]
general_log=OFF

复制

案例：在/etc/my.cnf中增加如下配置，关闭通用查询日志

[mysqld]
general_log=OFF

复制

提示：如果现有的配置文件中是general_log=ON，也可以把它注释掉，即等于关闭了，修改后保存，再重启mysql服务即可生效

[mysqld]
#general_log=ON  #通过“#”符合进行注释

复制

方式2：临时性关闭

SET GLOBAL general_log=OFF; # 关闭通用查询日志

复制

临时性关闭可以马上生效，但重启Mysql服务后会丢失配置，因此建议同时也增加到配置文件my.cnf中，待下次有机会重启的时候就会从配置文件加载配置，不会丢失了。

125. 删除通用查询日志和刷新通用查询日志

如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证MySQL服务器上的硬盘空间不会被撑爆。

手动删除通用查询日志文件：如果日志文件占用磁盘空间很大，可以手动删除，具体操作如下：

# 查看日志文件的路径和文件名，这里是/data/mysql_data/zbx-db01-general.log
mysql> SHOW VARIABLES LIKE 'general_log%';
+------------------+---------------------------------------+
| Variable_name    | Value                                 |
+------------------+---------------------------------------+
| general_log      | ON                                    |
| general_log_file | /data/mysql_data/zbx-db01-general.log |
+------------------+---------------------------------------+
2 rows in set (0.00 sec)


# 开始手动删除，需要注意，手动删除完成后，该日志文件不会再自动生成，哪怕是通用查询日志是ON的状态也不会，所以要重新生成查询日志文件
># rm -rf /data/mysql_data/zbx-db01-general.log
># ls -lh /data/mysql_data/zbx-db01-general.log
ls: cannot access /data/mysql_data/zbx-db01-general.log: No such file or directory

复制

重新生成查询日志文件

# 通过mysqladmin工具来生成查询日志文件
># /usr/local/mysql/bin/mysqladmin -uroot -p flush-logs           

# 生成后，发现已经生成啦！
># ls -lh /data/mysql_data/zbx-db01-general.log
-rw-r----- 1 mysql mysql 18K Apr 22 11:21 /data/mysql_data/zbx-db01-general.log

复制

Part5错误日志(error log)

错误日志文件包含了当MySQLd启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。在MySQL中，错误日志也是非常有用的，MySQL会将启动和停止数据库信息以及一些错误信息记录到错误日志文件中。

关于错误日志的官方文档：https://dev.mysql.com/doc/refman/8.0/en/error-log.html

131. 开启错误日志

在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。例如，MySQL所在的服务器主机名为MySQL-db，记录错误信息的文件名为MySQL-db.err。如果执行了FLUSH LOGS，错误日志会重新加载。
在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为mysqld.log（Linux系统）或hostname.err（mac系统），如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置：

[mysqld]
log-error = /data/mysql_data/mysql3306.err

复制

修改配置项后，需要重启MySQL服务以生效。

通过系统变量log_error来查看错误日志文件的路径和文件名，操作如下:

mysql> show global variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /data/mysql_data/mysql3306.err         |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)
mysql> 
mysql> select @@log_error;                                   
+--------------------------------+
| @@log_error                    |
+--------------------------------+
| /data/mysql_data/mysql3306.err |
+--------------------------------+
1 row in set (0.00 sec)

复制

执行结果中可以看到错误日志文件是mysql3306.err ，位于MySQL的数据目录下/data/mysql_data/。

142. 删除和刷新错误日志

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的硬盘空间不会被撑爆。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。

删除方式1：直接用rm命令删除错误日志文件

# 执行rm命令删除
[root@zbx-db01 ~]# rm -f /data/mysql_data/mysql3306.err 
[root@zbx-db01 ~]# ls -l /data/mysql_data/mysql3306.err       
ls: cannot access /data/mysql_data/mysql3306.err: No such file or directory

# 重建日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

# 重建后查看，已重建成功
[root@zbx-db01 ~]# ls -l /data/mysql_data/mysql3306.err 
-rw-r----- 1 mysql mysql 0 Apr 25 14:20 /data/mysql_data/mysql3306.err

复制

**删除方式2：通过/dev/null的机制清空错误日志文件的内容，使用install命令将null拷贝到错误日志文件mysql3306.err，这样就达到清空目的

特别说明一下，这个只是清空错误日志内容，不是删除，应该不用再执行flush logs命令重建日志文件。

install -omysql -gmysql -m0644 /dev/null /data/mysql_data/mysql3306.err

复制

特别注意：对于MySQL5.5.7以前的版本，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。但是从MySQL5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL启动并不会自动创建日志文件。flush logs在重新加载日志的时候，如果日志不存在，则会自动创建。所以在删除错误日志之后，如果需要重建日志文件需要在mysql中执行flush logs命令。

知识扩展：

/dev/null是啥？

/dev/null 是一个特殊的设备文件，它丢弃一切写入其中的数据 可以将它 视为一个黑洞, 它等效于只写文件, 写入其中的所有内容都会消失, 尝试从中读取或输出不会有任何结果，同样，/dev/null 在命令行和脚本中都非常有用。

复制

install命令用途

install命令的作用是安装或升级软件或备份数据，它的使用权限是所有用户。install命令和cp命令类似，都可以将文件/目录拷贝到指定的地点。但是，install允许你控制目标文件的属性。install通常用于程序的makefile，使用它来将程序拷贝到目标（安装）目录。

常用参数：
-o，--owner=所有者：自行设定所有者 (只适用于超级用户)。
-m，--mode=模式：自行设定权限模式 (像chmod)，而不是rwxr-xr-x。
-g，--group=组：自行设定所属组，而不是进程目前的所属组。

复制

Part6二进制日志(binlog)

提示：Oracle的归档日志等同于Mysql的binlog日志，所以Binlog日志其实也可以叫归档日志

二进制日志官方文档：https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

binlog的主要作用：

binlog可以说是MySQL中比较重要的日志了，在日常开发及运维过程中，经常会遇到。
binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。
binlog 就是binary log，二进制日志文件，这个文件记录了MySQL所有的DDL和DML(除了数据查询语句)语句，也就是会记录增删改的操，以事件形式记录，还包含语句所执行的消耗的时间，注意，查询的操作是不会被记录的二进制日志的。

binlog主要应用场景：

用于数据恢复
用于数据复制

特别声明：上图来源，摘自互联网，仅作参考。如有侵权请告知笔者，将立即删除，谢谢！

binlog日志包括两类文件

二进制日志索引文件（文件名后缀为.index），用于记录所有的二进制文件，如我这里的文件名后缀是mysql-bin：

[root@zbx-db01 mysql_data]# cat mysql-bin.index 
./mysql-bin.000054
./mysql-bin.000055
./mysql-bin.000056
./mysql-bin.000057
./mysql-bin.000058
./mysql-bin.000059
./mysql-bin.000060
./mysql-bin.000061
./mysql-bin.000062
./mysql-bin.000063
./mysql-bin.000064
./mysql-bin.000065

复制

文件名后缀是可以在my.cnf自定义配置的，并不是只能叫mysql-bin，也可以叫别的名字，比如mysql-abc-bin都可以，建议尽量取一个有意义的名字即可

二进制日志文件（文件名后缀为.00000*）：记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

[root@zbx-db01 mysql_data]# ls -l mysql-bin.*   
-rw-r----- 1 mysql mysql  3403136 Apr 22 10:09 mysql-bin.000054 # 二进制日志文件
-rw-r----- 1 mysql mysql  4405820 Apr 22 11:21 mysql-bin.000055
-rw-r----- 1 mysql mysql 23387993 Apr 24 20:08 mysql-bin.000056
-rw-r----- 1 mysql mysql  7747401 Apr 24 22:04 mysql-bin.000057
-rw-r----- 1 mysql mysql 20376333 Apr 25 13:58 mysql-bin.000058
-rw-r----- 1 mysql mysql  1421676 Apr 25 14:20 mysql-bin.000059
-rw-r----- 1 mysql mysql 11762442 Apr 25 17:33 mysql-bin.000060
-rw-r----- 1 mysql mysql   106139 Apr 26 17:38 mysql-bin.000061
-rw-r----- 1 mysql mysql 29829686 Apr 27 20:32 mysql-bin.000062
-rw-r----- 1 mysql mysql      275 Apr 27 20:32 mysql-bin.000063
-rw-r----- 1 mysql mysql 44495957 Apr 28 21:39 mysql-bin.000064
-rw-r----- 1 mysql mysql 17895778 Apr 29 14:01 mysql-bin.000065
-rw-r----- 1 mysql mysql      228 Apr 29 08:42 mysql-bin.index # 这是二进制日志索引文件，除了这个，其他都是二进制日志文件
[root@zbx-db01 mysql_data]#

复制

151. 查看binlog的状态

在MySQL8中，默认情况下，二进制文件是开启的，查看方法如下：

# 通过查看全局系统变量log_bin来查看，当前是ON（开启），二进制日志文件的路径是/data/mysql_data/，二进制日志文件的基本名称是mysql-bin（也可以说是前缀名）
mysql> show global variables like "%log_bin%";    
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               | # 默认开启
| log_bin_basename                | /data/mysql_data/mysql-bin       | # 二进制日志文件基本名称
| log_bin_index                   | /data/mysql_data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
+---------------------------------+----------------------------------+
5 rows in set (0.00 sec)
mysql> 

# 查看当前master的二进制日志文件对应的是哪个，这里是mysql-bin.000061
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000061
         Position: 252
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
92099aae-4731-11ec-a3da-00505629525b:1-1003676


# 查看二进制日志文件，可以看到很多不同编号的二进制日志文件，二进制日志文件很重要，不能随便手动删除
[root@zbx-db01 ~]# ls -l /data/mysql_data/mysql-bin.*
-rw-r----- 1 mysql mysql  3937281 Apr 21 23:01 /data/mysql_data/mysql-bin.000052
-rw-r----- 1 mysql mysql   724563 Apr 21 23:12 /data/mysql_data/mysql-bin.000053
-rw-r----- 1 mysql mysql  3403136 Apr 22 10:09 /data/mysql_data/mysql-bin.000054
-rw-r----- 1 mysql mysql  4405820 Apr 22 11:21 /data/mysql_data/mysql-bin.000055
-rw-r----- 1 mysql mysql 23387993 Apr 24 20:08 /data/mysql_data/mysql-bin.000056
-rw-r----- 1 mysql mysql  7747401 Apr 24 22:04 /data/mysql_data/mysql-bin.000057
-rw-r----- 1 mysql mysql 20376333 Apr 25 13:58 /data/mysql_data/mysql-bin.000058
-rw-r----- 1 mysql mysql  1421676 Apr 25 14:20 /data/mysql_data/mysql-bin.000059
-rw-r----- 1 mysql mysql 11762442 Apr 25 17:33 /data/mysql_data/mysql-bin.000060
-rw-r----- 1 mysql mysql      252 Apr 26 08:50 /data/mysql_data/mysql-bin.000061 # 这是master当前所处的二进制日志文件
-rw-r----- 1 mysql mysql      190 Apr 26 08:50 /data/mysql_data/mysql-bin.index
[root@zbx-db01 ~]# 

复制

一定要切记：二进制日志文件很重要，不能随便手动删除，只能通过相关参数，利用Mysql自身的机制删除，比如可以关闭BinLOG，或者指定保留多少天

关于二进制日志文件保留多长时间的参数问题：

mysql8之前的版本是这个参数expire_logs_days，单位是天，比如 expire_logs_days=7
mysq8之后，已经是建议使用binlog_expire_logs_seconds参数了，单位是秒，比如 binlog_expire_logs_seconds=604800

162. 开启binlog

说明：mysql8的binlog默认是开启的，也就是说，不需要在my.cnf中显示指定开启，那么它都是开启的。方式1：永久性

修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数，启用二进制日志和设置相关参数如下：

log-bin=zbx-db01-mysql-bin # 二进制日志文件基础名
binlog_format = row # 二进制日志格式
binlog_expire_logs_seconds=604800 # 二进制日志文件保留时间（单位秒）
max_binlog_size=100M # binlog日志每达到设定大小后会使用新的binlog日志，如mysql-bin.000001达到500M后就创建并使用mysql-bin.000002文件作为日志记录。（默认是1GB）

复制

注意：修改了配置文件，需要重新启动MySQL服务哦，否则不生效呢

方式2：临时性

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql8中只有会话级别的设置，没有了global级别的设置。

# 可以看到，sql_log_bin变量不是全局的，查看全局，他是空的
mysql> show global variables like "%sql_log_bin%";  
Empty set (0.00 sec)

mysql> 

# sql_log_bin变量属于会话级别，可以看到该变量的值了，目前是ON
mysql> show session variables like "%sql_log_bin%";      
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

# 如果是OFF，那么可以执行下面的命令进行开启
mysql> set session sql_log_bin=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> 

复制

173. 关闭binlog

要禁用二进制日志记录，可以在启动MySQL时指定--skip-log-bin 或者 --disable-log-bin 选项

方式1：在启动时增加关闭选项

# 启动mysql时增加--disable-log-bin选项（经测试，可行）
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/usr/local/mysql8/my.cnf --disable-log-bin --daemonize
# 或者启动mysql时增加--skip-log-bin选项（经测试，可行）
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/usr/local/mysql8/my.cnf --skip-log-bin --daemonize

# 查看log_bin全局变量的值，发现从ON变成了OFF
mysql> show global variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   | # 发现已经为OFF，关闭成功
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.47 sec)

复制

经测试两个参数选其一即可，都可行，注意，如果是在my.cnf配置文件中增加参数时，不能带两个杠“--”，也就是直接配置“disable-log-bin” 或 “skip-log-bin” 就可以了。

方式2：在my.cnf配置文件中增加skip-log-bin参数或 disable-log-bin参数

[mysqld]
user=mysql
basedir=/usr/local/mysql8
datadir=/home/mysqlData
log-error=/usr/local/mysql8/error.log
pid-file=/usr/local/mysql8/mysql.pid
port=3306
socket=/usr/local/mysql8/mysql.sock
plugin-dir=/usr/local/mysql8/lib/plugin
disable-log-bin

复制

经测试两个参数选其一即可，都可行，注意，如果是在启动mysql是添加选项，是需要带两个杠“--”，也就是“--disable-log-bin” 或 “--skip-log-bin”

184. 查看binlog日志文件

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。

MySQL服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

1. 查看二进制日志文件列表和大小

查看当前的二进制日志文件列表及大小。指令如下：

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000052 |   3937281 | No        |
| mysql-bin.000053 |    724563 | No        |
| mysql-bin.000054 |   3403136 | No        |
| mysql-bin.000055 |   4405820 | No        |
| mysql-bin.000056 |  23387993 | No        |
| mysql-bin.000057 |   7747401 | No        |
| mysql-bin.000058 |  20376333 | No        |
| mysql-bin.000059 |   1421676 | No        |
| mysql-bin.000060 |  11762442 | No        |
| mysql-bin.000061 |       252 | No        |
+------------------+-----------+-----------+
10 rows in set (0.00 sec)

复制

2. mysqlbinlog读取binlog日志

参考资料：https://blog.csdn.net/line_on_database/article/details/115487650 二进制日志在MySQL数据库中有着至关重要的作用，而读取二进制日志的工具就显得同样重要了，官方的mysqlbinlog就是读取二进制日志的工具。

案例：通过mysqlbinlog工具，下面的命令将行事件以伪SQL的形式表现出来

[root@zbx-db01 mysql_data]# mysqlbinlog -v mysql-bin.000061 
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220426  8:50:44 server id 5  end_log_pos 125 CRC32 0xb9e47a1f  Start: binlog v 4, server v 8.0.27 created 220426  8:50:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
5EFnYg8FAAAAeQAAAH0AAAABAAQAOC4wLjI3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADkQWdiEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBH3rkuQ==
'/*!*/;
# at 125
#220426  8:50:46 server id 5  end_log_pos 252 CRC32 0x5f2fbd1d  Previous-GTIDs
# 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
# 92099aae-4731-11ec-a3da-00505629525b:1-1003676
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@zbx-db01 mysql_data]# 

复制

案例：前面的命令同时显示binlog格式的语句，使用如下命令不显示它

[root@zbx-db01 mysql_data]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000061 
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220426  8:50:44 server id 5  end_log_pos 125 CRC32 0xb9e47a1f  Start: binlog v 4, server v 8.0.27 created 220426  8:50:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 125
#220426  8:50:46 server id 5  end_log_pos 252 CRC32 0x5f2fbd1d  Previous-GTIDs
# 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
# 92099aae-4731-11ec-a3da-00505629525b:1-1003676
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

复制

案例：查看二进制日志文件mysql-bin.000061最后100行

[root@zbx-db01 mysql_data]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000061 |tail -100
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220426  8:50:44 server id 5  end_log_pos 125 CRC32 0xb9e47a1f  Start: binlog v 4, server v 8.0.27 created 220426  8:50:44 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 125
#220426  8:50:46 server id 5  end_log_pos 252 CRC32 0x5f2fbd1d  Previous-GTIDs
# 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
# 92099aae-4731-11ec-a3da-00505629525b:1-1003676
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@zbx-db01 mysql_data]# 

复制

案例：根据position查找

# 查看Position位置，当前是252
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000061
         Position: 252
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
92099aae-4731-11ec-a3da-00505629525b:1-1003676
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

# 根据position查找
[root@zbx-db01 mysql_data]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000061 |grep -A 20 '252'
#220426  8:50:46 server id 5  end_log_pos 252 CRC32 0x5f2fbd1d  Previous-GTIDs
# 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
# 92099aae-4731-11ec-a3da-00505629525b:1-1003676
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

复制

关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简单分享几个常用的语句，更多操作可以参考官方文档。

3. show binlog events查询binlog日志

通过mysqlbinlog工具读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：show binlog events

关于show binlog events语句的语法格式如下：

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-bin.000061的信息

# 首先查看目前都有哪些binlog日志文件
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000052 |   3937281 | No        |
| mysql-bin.000053 |    724563 | No        |
| mysql-bin.000054 |   3403136 | No        |
| mysql-bin.000055 |   4405820 | No        |
| mysql-bin.000056 |  23387993 | No        |
| mysql-bin.000057 |   7747401 | No        |
| mysql-bin.000058 |  20376333 | No        |
| mysql-bin.000059 |   1421676 | No        |
| mysql-bin.000060 |  11762442 | No        |
| mysql-bin.000061 |       252 | No        |
+------------------+-----------+-----------+
10 rows in set (0.00 sec)

mysql> 


# 开始通过show binlog events语句查询指定的binlog日志文件信息，例如，这里查询mysql-bin.000061
mysql> show binlog events in 'mysql-bin.000061'\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000061
        Pos: 4
 Event_type: Format_desc
  Server_id: 5
End_log_pos: 125
       Info: Server ver: 8.0.27, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000061
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 5
End_log_pos: 252
       Info: 9208096f-4731-11ec-a23e-005056210589:1-23:31-46,
92099aae-4731-11ec-a3da-00505629525b:1-1003676
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> 

复制

195. 关于binlog的格式

1. binlog格式概要

binlog的格式有三种：

ROW（这是默认的格式），行模式
Statement，语句模式
Mixed，自动模式

通过全局变量binlog_format来查看当前的binlog格式，默认的就是ROW格式

mysql> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

复制

2. binlog格式的特点

1. Row Level格式（行模式）

日志中会记录每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。
5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题。

缺点：row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。

2. Statement Level格式（语句模式）

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上锁执行的语句的细节，以及执行语句的上下文的信息。

缺点：由于只记录语句，所以，在statement level下已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现，所以生产上还是要慎重考虑是否使用这个格式

3. Mixed格式（自动化模式）

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

4. 行模式和语句模式的区别

语句模式（Statement）：100万条记录只需1条delete * from test；就可以删除100万条记录。
行模式（row）：100万条记录，记录100万条删除命令。

好了，因笔者时间有限，关于今天的分享就先到这里，就此搁笔。来日方长，后续会分享更多知识、经验给大家，希望多多关注、点赞、转发!

文章转载自TtrOpsStack，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。