Exemple de partage de code qui explique les détails du système de journalisation MySQL

Cet article résume et présente les informations détaillées du système de journalisation dans MySQL Il est très détaillé. Les amis dans le besoin peuvent s'y référer.

Tous ceux qui ont fait à grande échelle. Systems le sait. , le rôle des journaux ne doit pas être sous-estimé. Souvent, au milieu et aux étapes ultérieures d'un projet, les décisions d'optimisation et de mise à niveau sont prises sur la base des journaux. Ainsi, lorsque vous apprenez MySQL, vous ne pouvez bien sûr pas manquer la partie journalisation. Les optimisations évoquées dans nos entretiens sont toutes dérivées de logs. L'étude systématique des journaux MySQL peut nous aider à localiser avec précision les problèmes et à améliorer notre niveau de travail. De plus, la série de journaux suivante se concentrera sur le fonctionnement et la maintenance de DBA, comprendra systématiquement la configuration de tous les aspects de MySQL, se connaîtra ainsi que l'ennemi et fera de MySQL un entrepôt de données pratique pour vous.

1. type de journal de MySQL

Par défaut, tous les journaux MySQL sont stockés dans le répertoire racine de la base de données sous forme de fichiers :

[root@roverliang data]# pwd
/usr/local/webserver/extend_lib/mysql/data
[root@roverliang data]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test

Les types de journaux de MySQL sont les suivants :

1. Journal des erreurs (erreur), informations associées telles que le démarrage, l'exécution ou l'arrêt de l'instance de service MySQL.
2. Journal général des requêtes (général), toutes les instructions SQL ou commandes MySQL exécutées par l'instance de service MySQL.
3. Journal binaire (binaire), toutes les instructions de mise à jour exécutées sur la base de données, à l'exclusion des instructions select et show.
4. Journal des requêtes lentes (lent), instructions SQL dont le temps d'exécution dépasse la valeur du paramètre long_query_time ou instructions SQL qui n'utilisent pas d'index.

2. Cache de journaux MySQL

Un système rapide, stable et fiable, dans lequel le cache doit jouer un rôle essentiel. Le traitement des journaux MySQL utilise également un mécanisme de mise en cache. Les journaux MySQL sont initialement stockés dans la mémoire du serveur MySQL. Si la capacité de stockage spécifiée est dépassée, les journaux en mémoire sont écrits (ou vidés) dans la mémoire externe et stockés de manière permanente sur le disque dur sous forme de tables de base de données. ou des fichiers.

3. Journal des erreurs MySQL (journal des erreurs)

Le journal des erreurs MySQL enregistre principalement des informations détaillées sur chaque démarrage et arrêt de l'instance de service MySQL, ainsi que sur l'exécution processus de l'instance MySQL. Avertissements ou messages d'erreur générés. Contrairement aux autres journaux, le journal des erreurs de MySQL doit être activé et ne peut pas être désactivé.

Par défaut, le nom du fichier journal des erreurs est : hostname.err. Cependant, le journal des erreurs n'enregistre pas toutes les informations sur les erreurs. Seules les erreurs critiques (critiques) qui se produisent lors de l'exécution de l'instance de service MySQL seront enregistrées.

mysql> show variables like 'log_error'\G
*************************** 1. row ***************************
Variable_name: log_error
Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
1 row in set (0.02 sec)

4. Journal des requêtes générales MySQL (journal général)

Le journal des requêtes générales MySQL enregistre toutes les opérations de l'instance de service MySQL, telles que la sélection, la mise à jour, insertion, suppression et autres opérations, que l'opération soit exécutée avec succès ou non. Il existe également des informations sur la connexion et la déconnexion entre le client MySQL et le serveur MySQL, si la connexion a réussi ou échoué. Il existe trois paramètres liés au journal des requêtes générales MySQL.

[]()general_log

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | OFF  |
+---------------+-------+
1 row in set (0.01 sec)

Vous pouvez activer le journal des requêtes générales en définissant @@global.general_log = 1.

mysql> set @@global.general_log =1;
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | ON  |
+---------------+-------+

Cependant, la modification de la variable de MySQL de cette manière ne prendra effet que pendant l'exécution de l'instance MySQL actuelle. Une fois MySQL redémarré, elle sera restaurée à sa valeur par défaut . état . La façon de le rendre permanent est de modifier le fichier my.cnf de mysql. Ajoutez après le fichier de configuration  :

general_log = 1 
general_log_file

Une fois le journal des requêtes générales activé, l'instance de service MySQL créera automatiquement un fichier journal des requêtes générales. Le paramètre general_log_file définit l'emplacement physique. du fichier journal des requêtes générales. Comme suit :

mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------------------+
| Variable_name  | Value                           |
+------------------+-----------------------------------------------------------+
| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+-----------------------------------------------------------+

Remarque : Étant donné que le journal des requêtes ordinaire enregistre presque toutes les opérations de MySQL, pour les serveurs de base de données avec un accès fréquent aux données, si vous activez le journal des requêtes ordinaire de MySQL, cela réduira considérablement le charge de la base de données, il est donc recommandé de désactiver le journal des requêtes normal. Uniquement pendant des périodes spéciales, si vous avez besoin de suivre certains journaux de requêtes spéciaux, vous pouvez ouvrir temporairement le journal de requêtes ordinaire.

log_output

Le paramètre log_output définit le contenu du journal des requêtes normales et du journal des requêtes lentes à stocker dans la table de base de données. Vous pouvez utiliser set @@global.log_output='table' pour stocker les journaux de requêtes normales et les journaux de requêtes lentes dans la table générale et la table slow_log dans la base de données du système MySQL. Il convient de noter que le moteur de stockage de ces deux tables est CSV. Après cela, vous pouvez utiliser des instructions SQL lors de l'affichage du nouveau contenu du journal des requêtes ordinaires

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output  | TABLE |
+---------------+-------+

5. Journal des requêtes lentes MySQL ; (journal lent )

Questions liées aux journaux de requêtes lents Au cours de l'entretien, l'intervieweur aime beaucoup parler de ce problème. Dans le passé, je ne pouvais parler que de l'architecture maître-esclave MySQL et optimiser MySQL sous tous ses aspects, mais je ne comprenais pas vraiment comment activer les requêtes lentes et les configurations associées.

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。与MySQL慢查询日志有关的参数有5个。

1、slow_query_log

slow_query_log 设置慢查询日志是否开启。

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF  |
+----------------+-------+

2、slow_query_log_file

慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。 修改方法和上文所示的一致。直接到my.cnf 文件中进行编辑。

3、long_query_time

long_query_time 设置了慢查询的时间阈值。默认阈值是10s。

4、log_quries_not_using_indexes

log_quries_not_using_indexes 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快。

mysql> set @@global.log_queries_not_using_indexes=1;

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+

5、log_output

设置了普通查询日志以及慢查询日志的输出形式，值有两个file、table;

六、MySQL慢查询日志查看

log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE;

mysql> desc mysql.slow_log;
+----------------+---------------------+
| Field     | Type        |
+----------------+---------------------+
| start_time   | timestamp      |
| user_host   | mediumtext     |
| query_time   | time        |
| lock_time   | time        |
| rows_sent   | int(11)       |
| rows_examined | int(11)       |
| db       | varchar(512)    |
| last_insert_id | int(11)       |
| insert_id   | int(11)       |
| server_id   | int(10) unsigned  |
| sql_text    | mediumtext     |
| thread_id   | bigint(21) unsigned |
+----------------+---------------------+

其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。

但是使用TABLE来存储慢查询日志并不常见，业务量较大的情况下，对于系统的主服务会有影响。我们可以使用FILE 的方式来进行日志存储。安装MySQL的时候在MySQL的bin目录下已经默认安装了mysqldumpslow.pl工具来进行慢查询的日志分析。 window下使用这个工具，可能需要折腾些配置，这不在本文的介绍范围内，学系统服务，还是移步linux吧。 linux下的命令以及工具都可以使用 命令本身 + --help 的选项来查看帮助文档。

-s 表示按照何种方式排序

子选项: c、t、l、r

c : SQL执行的次数
t : 执行时间
l : 锁等待时间
r : 返回数据条数
at、al、ar 是对应 t l r 的平均值。 -t :表示返回前 N 条记录。

-g： grep 缩写。包含模糊匹配

常用法如下：

//返回访问次数最多的20条SQL语句
./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回return记录数最多的20条SQL语句
./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回含有like的SQL语句
./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

七、二进制日志（binary）

二进制日志和前面提到的几种日志不同，二进制不能直接通过cat或者less 文本查看器查看。需要借助专业的工具。二进制日志主要记录数据库的变化情况，因此可以用作主从库的同步。内容主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。用一句更简洁易懂的话概括就是：所有涉及数据变动的操作，都要记录进二进制日志中。

启动二进制日志 使用 show variables like 'log_bin'\G 来查看二进制日志是否开启。

mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
    Value: OFF
1 row in set (0.00 sec)

mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>

看到log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，然后重启MySQL。 service mysql restart 重启MySQL后，在data目录会发现生成了一个1.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like 'log_bin_index'\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。

查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：

-s 以精简的方式显示日志内容
-v 以详细的方式显示日志内容
-d=数据库名 只显示指定数据库的日志内容
-o=n 忽略日志中前n行MySQL命令
-r=file 将指定内容写入指定文件

--start-datetime
显示指定时间范围内的日志内容
--stop-datetime

--start-position
显示指定位置间隔内的日志内容
--stop-position

获取当前使用的二进制日志文件

mysql> show master status;
+----------+----------+--------------+------------------+-------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 |   120 |       |         |          |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用二进制日志恢复数据

语法很简单：

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog 之后可以跟 --start-datetime 、--stop-datetime 、start-position 、stop-position 等参数。

--start-datetime 、--stop-datetime 这两个参数可以基于时间点进行数据恢复；

start-position 、stop-position 可以进行操作点更加细化的进行数据恢复；

MySQL二进制日志相关参数

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name              | Value        |
+-----------------------------------------+----------------------+
| binlog_cache_size            | 32768        |
| binlog_checksum             | CRC32        |
| binlog_direct_non_transactional_updates | OFF         |
| binlog_error_action           | IGNORE_ERROR     |
| binlog_format              | STATEMENT      |
| binlog_gtid_simple_recovery       | OFF         |
| binlog_max_flush_queue_time       | 0          |
| binlog_order_commits          | ON          |
| binlog_row_image            | FULL         |
| binlog_rows_query_log_events      | OFF         |
| binlog_stmt_cache_size         | 32768        |
| binlogging_impossible_mode       | IGNORE_ERROR     |
| innodb_api_enable_binlog        | OFF         |
| innodb_locks_unsafe_for_binlog     | OFF         |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size             | 1073741824      |
| max_binlog_stmt_cache_size       | 18446744073709547520 |
| simplified_binlog_gtid_recovery     | OFF         |
| sync_binlog               | 0          |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名+1；

binlog_cache_size

binlogcachesize 内存中存放二进制日志的缓存大小

sync_binlog

sync_binlog 缓存中写入几次二进制日志，开始同步刷新到外存（硬盘）中。

log_slave_updates

logslvaeupdates 用于主从复制

二进制日志的清理

原则上要先将准备清理的日志通过物理备份的方式备份到其他存储设备上，永久留存。 然后推荐使用下面两种风险较小的清理方式：

第一种：

purge master logs before '2017-02-16 00:00:00';

第二种：

直接在MySQL的配置文件my.cnf 中设置expire_logs_days参数来设置二进制文件的过期天数，过期的二进制文件将会被自动删除。建议在删除前另启一个周期计划任务，定期去备份二进制任务。免得一些数据若干天才发现出现了差错，而二进制日志被自动删除了。

expire_logs_days=90

八 、InnoDB 事务日志

MySQL会最大程度的利用缓存，从而提高数据的访问效率。那么换一句话来说，任何高性能的系统都必须利用到缓存，从各个层面来讲，缓存都发挥了巨大的作用。再上升到一个高度提炼一下：缓存和队列是实现高性能的必走之路。那么对于数据库来说这个却是个很棘手的问题，要保证数据更高效率的读取和存储，所以要利用到缓存。但是要保证数据的一致性，则必须保证所有的数据都必须准确无误的存储到数据库中，及时发生意外，也要保证数据可恢复。我们知道InnoDB是一个事务安全的存储引擎，而一致性是事务ACID中的一个重要特性。InnoDB存储引擎主要是通过InnoDB事务日志实现数据一致性的，InnoDB事务日志包括重做（redo）日志，以及回滚（undo）日志。

InnoDB事务日志与前面提高的日志不同，InnoDB事务日志由InnoDB存储引擎自行维护，而且内容不能被数据库管理员读取。

重做日志（redo）

重做日志主要是记录已经全部完成的事务,即执行了commit的日志，在默认情况下重做日志的值记录在iblogfile0 以及iblogfile1重做日志中。

[root@roverliang data]# pwd
/usr/local/webserver/mysql/data
[root@roverliang data]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

回滚日志（undo）

回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间未见ibd中。

由上图我们可以知道，回滚日志默认是记录在ibdta1中的。我的mysql系统版本为：5.6.24.

Checkpoint机制

MySQL服务器崩溃后，重新启动MySQL服务时，由于重做日志（redo）与回滚日志（undo）日志的存在，InnoDB通过回滚日志（undo）日志将所有已部分完成并写入硬盘的未完成事务进行回滚操作（rollback）。然后将重做日志（undo）日志中的事务全部重新执行一遍即可恢复所有的数据。但是数据量过大，为了缩短恢复的时间InnoDB引入了Checkpoint 机制。

脏页（dirty page）

当事务需要修改某条记录是，InnoDB首先将该数据所在的数据块从外存中读取到硬盘中，事务提交后，InnoDB修改数据页中的记录，这时缓存的数据页已经和外存中的数据块已经不一样了，此时缓存中的数据页称为脏页（dirty page）,脏页刷新到外存中，变为干净页（clean page）。

备注：一个内存页默认为4K，或者4K的倍数。你可以把内存想象成一本可以擦洗的书，每次MySQL读数据的时候，向内存申请几张干净的书页，然后书写上去。当数据刷新到硬盘之后，这些数据页马上被擦除，供其他程序使用。

日志序列号（log sequence number）

日志序列号（LSN）是日志空间中每条日志的结束点，用字节偏移量表示，在Checkpoin和恢复时使用。

Checkpoint 机制原理 假设在某个时间点，所有的脏页（dirty page）都被刷新到了硬盘上，这个时间点之前的所有重做日志（redo）就不需要重做了.系统就就下这个时间点重做日志的结尾位置作为Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的删除掉。为了更好的利用重做日志（redo）的空间,InnoDb采用轮循的策略使用重做日志空间，因此InnoDB的重做日志文件至少为2个。通过Checkpoint 机制，通过重做日志（redo）将数据库崩溃时已经完成但还没有来得及将缓存中已经修改但还未完全写入外存的事务进行重做（undo）操作，即可保证数据的一致性，也可以缩短恢复时间。

InnoDB重做日志(redo)的参数

innodb_log_buffer_size: 设置了重做日志缓存的大小。
innodb_log_files_in_group : 设置了日志文件组中重做日志（redo）日志的数量。
innodb_log_file_size: 设置了重做日志文件的大小，文件越大，恢复起来耗时越长。
innodb_mirrored_log_groups: 重做日志镜像文件组数量，只能设置为1.
innodb_log_group_home_dir： 设置日志文件组存放的目录，默认在数据库根目录下。

InnoDB回滚日志（undo）的参数

innodb_undo_directory: 设置回滚日志存放的目录。
innodb_undo_logs :设置回滚日志的回滚段大小，默认为128k
innodb_undo_tablespace:设置了回滚日志由多少个回滚日志文件组成，默认为0.
Warning 特别注意：安装MySQL后需要在my.cnf中设置回滚日志的参数，如果创建数据库以后再设置回滚日志的参数，MySQL就会报错，并且回滚日志建好后，就不能再次修改或者增加。

九 、日志文件备份

备份的时候可以使用flush logs，关闭当前的所有日志文件，然后产生新的日志文件。关闭日志文件后，可以采用物理方式备份。 另外flush logs可以添加具体的日志类型：

flush error logs
flush general logs
flush binary logs
flush slow logs

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!