What are the four major types of logs in MySQL?

Preface

The MySQL log records the daily operations and error information of the MySQL database. MySQL has different types of log files (each stores different types of logs). From the logs, you can query the running status of the MySQL database, user operations, error information, etc.

MySQL logs are divided into 4 major categories

1. Error log: record the problems that occur when starting, running or stopping the mysql service

2. Query log: records the established client connection and executed statements

3. Binary log: records all statements that change data, which can be used for data replication

4. Slow query log: records all queries that take longer than long_query_time to execute or queries that do not use indexes. By default, all logs are created with the MySQL data directory. By flushing the log, you can force MySQL to shut down and Reopen the log file, Flush logs to refresh the log, or execute mysqladmin flush-logs. If you are using the MySQL replication function, you can maintain more log files on the replication server. This kind of log is called a replacement log. Enabling the logging function will reduce the performance of the MySQL database.

Error log

In the mysql database, the error log function is enabled by default. By default, error logs are stored in the data directory of the mysql database. The error log file is usually named hostname.err. Among them, hostname represents the server host name. The error log information can be configured by yourself. The information recorded in the error log can be defined through log-error and log-warnings. Log-error defines whether to enable the error log function and the storage location of the error log. log- warnings defines whether warning information is also defined in the error log.

By default, the error log probably records the following aspects of information: information during server startup and shutdown (not necessarily error information, such as how mysql starts the InnoDB table space file, how to initialize its own storage engine, etc.), error messages during server operation, information generated when the event scheduler runs an event, information generated when starting the server process from the server Note 1: MySQL has many system variables that can be set, system variable settings Differences will lead to different system operating status. Therefore, mysql provides two sets of commands to view system settings and operating status respectively.

1. View system settings:

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] 
SHOW VARIABLES： shows the values of MySQL system variables.

2. Running status:

SHOW [GLOBAL | SESSION] STATUS [like_or_where] 
SHOW STATUS： provides server status information.

Modify system configuration

Method 1: Configuration file settings my.cnf such as ：binlog_cache_size = 1M

Method 2: set global binlog_cache_size = 1048576;

Note: Check the mysql version

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using  EditLine wrapper

or

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:      11
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.40 MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql.sock
Uptime:         87 days 2 hours 22 min 4 sec

Threads: 1  Questions: 61  Slow queries: 0  Opens: 114  Flush tables: 1  Open tables: 107  Queries per second avg: 0.000
--------------

or

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.40    |
+-----------+
1 row in set (0.00 sec)

Generally speaking, the definition of log level without session variables is only defined at the global level. The status of the error log is:

mysql> show global variables like '%log_error%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| binlog_error_action | ABORT_SERVER                    |
| log_error           | /usr/local/mysql/data/mysql.log |
| log_error_verbosity | 3                               |
+---------------------+---------------------------------+
3 rows in set (0.00 sec)

where log_error is defined as the error log file path log_error_verbosity:

##1errors only2errors and warnings3errors,warinigs,and notes(default)

更改错误日志位置可以使用log-error来设置形式如下

[root@localhost ~]# vim /etc/my.cnf
log-error = /usr/local/mysql/data/mysqld.err

查看mysql错误日志

[root@localhost ~]# tail /usr/local/mysql/data/mysqld.err

为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。

删除错误日志：

        在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –uroot –p flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。

        在mysql5.5.7之后：服务器将关闭此项功能。要创建新的日志文件，需要手动清除旧的错误日志并重命名它。操作方法如下：

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mv mysqld.err mysql.bak
[root@localhost data]# mysqladmin -uroot -p flush-logs
Enter password: 		#输入root密码

更多信息请查阅官方文档：

MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log

MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log

MySQL :: MySQL 5.7 Reference Manual :: 5.4.2 The Error Log

二进制日志

主要记录MySQL数据库的变化，二进制日志以一种有效的格式，并且是事务安全的方式包含更新日志中可用的信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据。二进制日志还包含关于每个更新数据库的语句的执行时间，它不包含没有修改任何数据的语句。使用二进制日志的主要目的是最大可能地恢复数据库。启动二进制日志，默认情况下二进制日志是关闭的 编辑配置文件My.ini 或my.cnf

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin=my-bin				//二进制日志[路径[指定日志文件的名字]]
Expire_logs_days = 10 									//清除日志的天数
Max_binlog_size = 100M 									//单个日志文件的大小限制，超出会新建一个默认为1GB
server_id=1												//mysql5.7版本以后需要添加serverid
[root@localhost ~]# service mysqld restart

Show variables 或show variables like 'log_%'; 语句来查询日志设置

mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/my-bin       |
| log_bin_index                   | /usr/local/mysql/data/my-bin.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)

查看二进制日志

MySQL的二进制日志包含了所有修改的信息，因此常被广泛应用。当MySQL创建二进制日志文件时，首先创建一个以’filename’为名称，以’.index’为后缀的文件；在创建一个以’filename’为名称，以’.000001’为后缀的文件。当MySQL服务重启一次，以’.000001’为后缀的文件会增加一个，并且后缀名加1递增。如果日志长度超过max_binlog_size的上限，也会创建一个新的日志。You can view the number and file names of current binary log files by using the command "Show binary logs;".。若需查看二进制日志的内容，应使用mysqlbinlog命令，而无法直接查看。

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| my-bin.000001 |       154 |
+---------------+-----------+
1 row in set (0.00 sec)

或者

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| my-bin.000001 |       154 |
+---------------+-----------+
1 row in set (0.00 sec)

查看二制日志的内容

退出mysql在命令行

[root@mysql ~]# mysqlbinlog myusql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'myusql-bin.000001' not found (Errcode: 2 - No such file or directory)
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的二进制文件可以配置自动删除，同时MySQL提供了手动删除二进制文件的方法：

RESET MASTER 删除所有的二进制日志文件；PURGE MASTER LOGS只删除部分二进制日志文件。 Resetmaster; 删除所有二进制日志 Purge master logs to ‘二进制名’ 删除单个二进制日志之前的

语法格式：

PURGE { BINARY | MASTER } LOGS {
    TO 'log_name'
  | BEFORE datetime_expr
}

例：

mysql> purge master logs to 'my-bin.000001';
Query OK, 0 rows affected (0.02 sec)
 
mysql> purge master logs before '20230101';			#删除指定日期之前的日志
Query OK, 0 rows affected, 1 warning (0.02 sec)

暂时停止二进制日志的功能

如果MySQL的配置文件已经启动了二进制日志，MySQL会一直记录二进制日志，修改配置文件，可以停止二进制日志，但是需要重启MySQL数据库。MySQL提供了暂时停止二进制日志的功能，通过SET SQL_LOG_BIN语句可以使MySQL暂时停止二进制

mysql> set sql_log_bin=1;			#0暂停 1恢复
Query OK, 0 rows affected (0.00 sec)	

事务日志(或称redo日志)

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。一旦事务日志持久化，后台会逐步将内存中被修改的数据写回磁盘。目前大多数的存储引擎都是这样实现的。 如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

查看事务日志的定义：

mysql> show global variables like '%log%';
#省略部分内容
| innodb_flush_log_at_timeout                | 1 
| innodb_flush_log_at_trx_commit             | 1 
| innodb_locks_unsafe_for_binlog             | OFF                  
| innodb_log_buffer_size                     | 16777216            
| innodb_log_checksums                       | ON                  
| innodb_log_compressed_pages                | ON                  
| innodb_log_file_size                       | 50331648          #日志文件大小  
| innodb_log_files_in_group                  | 2                 # DB中设置几组事务日志，默认是2   
| innodb_log_group_home_dir                  | ./				 #定义innodb事务日志组的位置,此位置设置默认为MySQL的datadir
#省略部分内容
每个事务日志都是大小为50兆的文件（不同版本的mysql有差异）： 在mysql中默认以ib_logfile0,ib_logfile1名称存在

innodb_flush_log_at_trx_commit # 在事务提交时innodb是否同步日志从缓冲区到文件中，当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新，性能会很差造成大量的磁盘I/O但这种方式最安全；如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘。设置为0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

注：刷写的概念

刷新和写入实际上是两个不同的操作，因此区分它们的概念非常重要。在大多数的操作系统中，把Innodb的log buffer（内存）写入日志（调用系统调用write），只是简单的把数据移到操作系统缓存中，操作系统缓存同样指的是内存。并没有实际的持久化数据。所以，通常设为0和2的时候，在崩溃或断电的时候会丢失最后一秒的数据，因为这个时候数据只是存在于操作系统缓存。在执行flush操作时可能会因阻塞而丢失超过1秒的数据，因此使用词语“通常”。

总结

设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2,如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

慢查询日志：slow query log

        顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow query。对于慢查询日志，它所采用的是一种简单的文本格式，而这个文本格式则可以被各种文本编辑器所查看。其中 记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。慢查询日志的功能在于记录执行时间超过特定时间限制的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。通常建议启用该选项，因为它对服务器性能的影响微不足道，但可以记录在MySQL服务器上执行了长时间的查询语句。可以帮助我们定位性能问题的。MySQL 还提供了专门用来分析满查询日志的工具程序mysqldumpslow，用来帮助数据库管理人员解决可能存在的性能问题。

查看慢查询日志的定义：

mysql> show global variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)
 
mysql> show global variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

启动和设置慢查询日志：

方法1：通过配置文件my.cnf开启慢查询日志：

注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like "%slow%" 和show variables like "%long%"查看出来。

mysql> show global variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | OFF                                  |
| slow_query_log_file       | /usr/local/mysql/data/mysql-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)

        其中： slow_query_log： off关闭状态 on开启状态 slow_query_log_file 慢查询日志存放地点 long_query_time选项来设置一个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为10秒），这个查询语句将被记录到慢查询日志中, 设置为0的话表示记录所有的查询。 slow_launch_time 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加 注：如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为hostname-slow.log 修改my.cnf文件：

[mysqld]
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log
long_query_time=1
slow_launch_time=1
#重启mysqld服务 再次查询慢查询日志定义

方法2：通过登录mysql服务器直接定义，方式如下：

mysql> set global slow_query_log=1;		#开启慢查询日志
Query OK, 0 rows affected (0.00 sec)
 
mysql> set session long_query_time=0.0001;		#更改时间（当前session中，退出则重置）
 
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global long_query_time=0.0001;		#更改时间（全局中，重启服务则重置）
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'long%';			#查询定义时间
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000100 |
+-----------------+----------+
1 row in set (0.00 sec)

查看慢查询日志

[root@mysql ~]# cat /usr/local/mysql/data/mysql-slow.log

        用系统查看文件内容命令如cat直接查看慢日志文件第一行表示记录日志时的时间。其格式是 YYYY-MM-DD HH:MM:SS。我们可以看出上面的查询记录于 2016 年8 月 29 日下午 15:47：24 - 注意：这个是服务器时间. MySql 用户、服务器以及主机名第三行表示总的查询时间、锁定时间、"发送"或者返回的行数 Query_time: 0.000304 表示用了0.000304秒 Lock_time: 0.000128 表示锁了0.000128秒 Rows_sent: 4 表示返回4行 Rows_examined: 4 表示一共查了4行 SET timestamp=UNIXTIME; 这是查询实际发生的时间 何将其变成一个有用的时间，将 Unix 时间转成一个可读的时间，可以使用 date –d@日志中的时间戳以看到查询进行的同时记录了该日志 ，但是对于一台超负载的服务器常常并非如此。所以请记住：实际查询执行的时间是通过SET timestamp = value来设置的。

        慢查询分析mysqldumpslow 们可以通过打开log文件查看得知哪些SQL执行效率低下。从日志中，可以发现查询时间超过long_query_time时间的query为慢查询，而小于long_query_time时间的没有出现在此日志中。 如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。在日志文件中，mysqldumpslow进行了分类统计并显示了摘要结果。要在存放日志的目录下运行

[root@mysql ~]# mysqldumpslow /usr/local/mysql/data/mysql-slow.log

        注： mysqldumpslow -s c -t 10 /database/mysql/slow-query.log 这会输出记录次数最多的10条SQL语句，其中： -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序； -t, 是top n的意思，即为返回前面多少条的数据； -g, 后边可以写一个正则匹配模式，大小写不敏感的；

        例如： /path/mysqldumpslow -s r -t 10/database/mysql/slow-log 得到返回记录集最多的10个查询。/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log 得到按照时间排序的前10条里面含有左连接的查询语句。

verbosity value	message types logged

The above is the detailed content of What are the four major types of logs in MySQL?. For more information, please follow other related articles on the PHP Chinese website!