Quels sont les quatre principaux types de journaux dans MySQL ?-tutoriel mysql-php.cn

Avant-propos

Le journal MySQL enregistre les opérations quotidiennes et les informations sur les erreurs de la base de données MySQL. MySQL possède différents types de fichiers journaux (chacun stocke différents types de journaux). À partir des journaux, vous pouvez interroger l'état d'exécution de la base de données MySQL, les opérations utilisateur, les informations sur les erreurs, etc.

Les journaux MySQL sont divisés en 4 grandes catégories

Journal des erreurs : enregistre le démarrage du service MySQL, les problèmes qui surviennent lors de l'exécution ou de l'arrêt du service MySQL
Journal des requêtes : enregistre la connexion client établie et les instructions exécutées
Journal binaire : enregistre toutes les instructions qui modifient les données, peut être utilisé pour la réplication des données
Journal des requêtes lentes : enregistre toutes les requêtes qui prennent plus de temps à s'exécuter ou les requêtes qui n'utilisent pas d'index. Par défaut, toutes. les journaux sont créés avec Dans le répertoire de données MySQL, vous pouvez forcer MySQL à fermer et rouvrir les fichiers journaux en vidant les journaux, en actualisant les journaux ou en exécutant mysqladmin flush-logs. Si vous utilisez la fonction de réplication MySQL, vous pouvez en conserver davantage. log sur le serveur de réplication. Ces journaux sont appelés journaux de remplacement. L'activation de la fonction de journalisation réduira les performances de la base de données MySQL.

Journal des erreurs

Dans la base de données mysql, la fonction de journal des erreurs est activée par défaut. Par défaut, les journaux d'erreurs sont stockés dans le répertoire de données de la base de données MySQL. Le fichier journal des erreurs est généralement nommé hostname.err. Parmi eux, le nom d'hôte représente le nom d'hôte du serveur. Les informations du journal des erreurs peuvent être configurées par vous-même. Les informations enregistrées dans le journal des erreurs peuvent être définies via le journal des erreurs et les avertissements du journal. Log-error définit s'il faut activer la fonction de journal des erreurs et l'emplacement de stockage du journal des erreurs. - warns définit si les informations d'avertissement sont également définies dans le journal des erreurs.

Par défaut, le journal des erreurs enregistre probablement les aspects d'informations suivants : des informations lors du démarrage et de l'arrêt du serveur (pas nécessairement des informations d'erreur, telles que la façon dont MySQL démarre le fichier d'espace table InnoDB, comment initialiser son propre moteur de stockage, etc.) , messages d'erreur pendant le fonctionnement du serveur, informations générées lorsque le planificateur d'événements exécute un événement, informations générées lors du démarrage du processus serveur à partir du serveur Remarque 1 : MySQL dispose de nombreuses variables système qui peuvent être définies. Différents paramètres de variables système entraîneront l'état de fonctionnement du système. est différent. Par conséquent, MySQL fournit deux ensembles de commandes pour afficher respectivement les paramètres du système et l'état de fonctionnement.

1. Vérifiez les paramètres du système :

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

Copier après la connexion

2. État d'exécution :

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

Copier après la connexion

Modifiez la configuration du système

Méthode 1 : définissez le fichier de configuration my.cnf tel que : binlog_cache_size = 1M

Méthode 2 : définissez global binlog_cache_size = 1048576;

Remarque : Vérifiez la version de mysql

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

Copier après la connexion

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:      &#39;&#39;
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
--------------

Copier après la connexion

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

Copier après la connexion

De manière générale, la définition du niveau de journalisation sans variables de session n'est définie qu'au niveau global. L'état du journal des erreurs :

mysql> show global variables like &#39;%log_error%&#39;;
+---------------------+---------------------------------+
| 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)

Copier après la connexion

où log_error est défini comme erreur Chemin du fichier journal log_error_verbosity :

valeur de verbosité	types de messages enregistrés
1	erreurs uniquement
2	erreurs et avertissements
3	erreurs, avertissements et notes (par défaut)

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

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

Copier après la connexion

查看mysql错误日志

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

Copier après la connexion

为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候就可以利用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密码

Copier après la connexion

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

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

Copier après la connexion

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

mysql> show variables like &#39;log_bin%&#39;;
+---------------------------------+------------------------------------+
| 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)

Copier après la connexion

查看二进制日志

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)

Copier après la connexion

或者

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

Copier après la connexion

查看二制日志的内容

退出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 &#39;myusql-bin.000001&#39; not found (Errcode: 2 - No such file or directory)
SET @@SESSION.GTID_NEXT= &#39;AUTOMATIC&#39; /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Copier après la connexion

删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL提供了手动删除二进制文件的方法：

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

语法格式：

PURGE { BINARY | MASTER } LOGS {
    TO &#39;log_name&#39;
  | BEFORE datetime_expr
}

Copier après la connexion

例：

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

Copier après la connexion

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

如果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)

Copier après la connexion

事务日志(或称redo日志)

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

查看事务日志的定义：

mysql> show global variables like &#39;%log%&#39;;
#省略部分内容
| 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名称存在

Copier après la connexion

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 &#39;%slow_query_log%&#39;;
+---------------------+--------------------------------------+
| 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 &#39;%long%&#39;;
+----------------------------------------------------------+-----------+
| 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)

Copier après la connexion

启动和设置慢查询日志：

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

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

mysql> show global variables like &#39;%slow%&#39;;
+---------------------------+--------------------------------------+
| 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)

Copier après la connexion

其中： 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服务 再次查询慢查询日志定义

Copier après la connexion

方法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 &#39;long%&#39;;			#查询定义时间
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000100 |
+-----------------+----------+
1 row in set (0.00 sec)

Copier après la connexion

查看慢查询日志

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

Copier après la connexion

用系统查看文件内容命令如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

Copier après la connexion

注： 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条里面含有左连接的查询语句。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!