English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
As those who have worked on large systems know, the role of logs should not be underestimated. Often, during the later stages of a project, decisions for optimization and upgrade are made based on logs. Therefore, when studying MySQL, the log section cannot be skipped. The optimizations we discuss in our interviews are all derived from logs. A systematic study of MySQL logs helps us accurately locate problems and improve our work level. In addition, the following series of logs will focus on the operation and maintenance aspects of DBAs, systematically understanding various configurations of MySQL, knowing oneself and the enemy, and making MySQL an easy-to-use data warehouse.
I. Types of MySQL logs
Par défaut, tous les journaux MySQL sont stockés sous forme de fichiers dans le répertoire racine de la base de données :
[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 d'erreur (error) : informations relatives au démarrage, au fonctionnement ou à l'arrêt de l'instance de service MySQL.
2. Journal de consultation normal (general) : toutes les requêtes SQL ou commandes MySQL exécutées par l'instance de service MySQL.
3. Journal binaire (binary) : toutes les requêtes d'update de base de données, sans inclure les requêtes select et show.
4. Journal de consultation lente (slow) : les requêtes SQL whose exécution time exceeds the value of long_query_time, ou les requêtes SQL qui ne utilisent pas d'index.
Deuxième partie : Cache de journaux MySQL
Un système à haute vitesse, stable et fiable, le cache y joue un rôle crucial. Le traitement des journaux MySQL utilise également un mécanisme de cache. Les journaux MySQL sont initialement stockés dans la mémoire du serveur MySQL, et s'ils dépassent la capacité de stockage spécifiée, les journaux en mémoire sont écrits (ou actualisés) dans le stockage externe, et sont conservés de manière permanente sur le disque dur sous forme de table de base de données ou de fichier.
Troisième partie : Journal d'erreur de MySQL (error log)
Le journal d'erreur de MySQL enregistre principalement les détails de chaque démarrage et arrêt de l'instance de service MySQL, ainsi que les avertissements ou informations d'erreur générées pendant le fonctionnement de l'instance MySQL. Contrairement aux autres journaux, le journal d'erreur de MySQL doit être activé et ne peut pas être désactivé.
Par défaut, le nom du fichier du journal d'erreur est : hostname.err. Mais le journal d'erreur ne enregistre pas toutes les informations d'erreur, seulement les erreurs critiques (critical) qui se produisent pendant le fonctionnement de l'instance de service MySQL.
mysql> show variables like 'log_error'\G *************************** 1. ligne *************************** Nom_de_variable : log_error Valeur : /usr/local/webserver/extend_lib/mysql/data/roverliang.err 1 row in set (0.02 sec)
Quatrième partie : Journal de consultation normal de MySQL (general log)
Le journal de consultation normal de MySQL enregistre toutes les opérations de l'instance de service MySQL, telles que select, update, insert, delete, etc., peu importe si ces opérations sont exécutées avec succès ou non. Il enregistre également les informations sur la connexion et la déconnexion du client MySQL avec le serveur MySQL, peu importe si la connexion est réussie ou non. Il y a trois paramètres liés au journal de consultation normal de MySQL.
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | DÉSACTIVÉ | +---------------+-------+ 1 row in set (0.01 sec)
Vous pouvez activer le journal des requêtes ordinaires en utilisant set @@global.general_log = 1 Pour activer le journal des requêtes ordinaires.
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ACTIVÉ | +---------------+-------+
Mais ce mode de modification des variables MySQL ne sera efficace que pendant la période de fonctionnement de l'instance MySQL actuelle. Une fois MySQL redémarré, il reviendra à l'état par défaut. Pour une application permanente, il faut modifier le fichier my.cnf de MySQL. Ajoutez les configurations suivantes à la fin du fichier :
general_log = 1
general_log_file
Une fois activé, le service MySQL crée automatiquement un fichier de journal des requêtes ordinaires. Le paramètre general_log_file configure l'emplacement physique du fichier de journal des requêtes ordinaires. Voici comment :
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | Nom_de_variable | Valeur | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+
Attention : comme le journal des requêtes ordinaires enregistre presque toutes les opérations de MySQL, pour un serveur de base de données où l'accès aux données est fréquent, l'activation du journal des requêtes ordinaires de MySQL réduira considérablement les performances de la base de données. Par conséquent, il est recommandé de désactiver le journal des requêtes ordinaires. Seulement pendant des périodes spéciales, comme la nécessité de suivre certains journaux de requêtes spécifiques, le journal des requêtes ordinaires peut être ouvert temporairement.
log_output
Le paramètre log_output configure le contenu du journal des requêtes ordinaires et du journal des requêtes lentes pour être stocké dans une table de base de données. Vous pouvez utiliser set @@global.log_output='table' pour stocker le journal des requêtes ordinaires et le journal des requêtes lentes dans les tables general et slow_log du système de base de données MySQL. Il est important de noter que les moteurs de stockage de ces deux tables sont CSV, ce qui permet de consulter le contenu des nouveaux journaux des requêtes ordinaires en utilisant des requêtes SQL;
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+
Cinquième partie : Journal des requêtes lentes MySQL (slow log)
Les problèmes relatifs au journal des requêtes lentes MySQL sont très appréciés des recruteurs lors des entretiens. Avant, on ne pouvait que parler de l'architecture maître-esclave de MySQL et de l'optimisation de MySQL sous tous les angles, mais on n'avait jamais réellement cherché à comprendre comment activer le journal des requêtes lentes et les configurations associées.
L'utilisation du journal des requêtes lentes MySQL permet de suivre efficacement les requêtes whose temps d'exécution est trop long ou qui ne utilisent pas d'index. Cela inclut les requêtes SELECT, UPDATE, DELETE et INSERT, fournissant une aide à l'optimisation des requêtes. Une autre distinction par rapport au journal des requêtes habituelles est que le journal des requêtes lentes ne contient que les requêtes réussies. Parmi les paramètres liés au journal des requêtes lentes MySQL, on trouve5个。
1、slow_query_log
slow_query_log configure si le journal des requêtes lentes est activé ou non.
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+
2、slow_query_log_file
Une fois le journal des requêtes lentes activé, l'instance MySQL crée automatiquement un fichier de journal des requêtes lentes. Le fichier spécifié par slowquerylog_file contient le contenu du journal des requêtes lentes. La méthode de modification est la même que celle mentionnée précédemment. Éditez directement le fichier my.cnf.
3、long_query_time
long_query_time définit le seuil de temps pour les requêtes lentes. La valeur par défaut est10s。
4、log_quries_not_using_indexes
log_quries_not_using_indexes enregistre les requêtes de consultation qui ne utilisent pas d'index dans le journal des requêtes lentes, quel que soit la vitesse de la consultation.
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
Les formats de sortie pour les journaux de requêtes normales et lentes ont été configurés, avec deux valeurs : file et table;
VI. Vue des journaux de requêtes lentes MySQL
Le paramètre log_output peut configurer le format de sortie du journal des requêtes lentes. Par défaut, il est FILE, mais il peut être configuré en 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 | +----------------+---------------------+
Parmi eux : lock_time représente le temps que l'SQL est bloqué par le verrou. rows_send représente le nombre de lignes renvoyées après l'exécution de l'SQL. rows_examined représente le nombre d'enregistrements analysés pendant l'exécution de l'SQL.
L'utilisation de TABLE pour stocker les journaux de requêtes lentes n'est pas courante. Dans le cas d'une grande quantité de transactions, cela peut affecter le service principal du système. Nous pouvons utiliser le mode FILE pour stocker les journaux. Lors de l'installation de MySQL, l'outil mysqldumpslow.pl pour l'analyse des journaux de requêtes lentes est déjà installé par défaut dans le répertoire bin de MySQL. Sur Windows, l'utilisation de cet outil peut nécessiter des configurations, ce qui n'est pas couvert dans l'article. Apprendre les services système, passez plutôt à Linux. Les commandes et outils sous Linux peuvent utiliser la commande elle-même. + --L'option help peut être utilisée pour consulter la documentation d'aide.
-s signifie par quel moyen le tri est effectué
Options secondaires : c, t, l, r
c : nombre d'exécutions de SQL
t : temps d'exécution
l : temps d'attente de verrouillage
r : nombre de lignes retournées
at、al、ar sont les moyennes correspondantes de t l r. -t : représente le retour des N premiers enregistrements.
-g : grep abréviation. Correspondance de correspondance floue
Les méthodes courantes sont les suivantes :
//Retourne le nombre d'accès le plus élevé20 requêtes SQL ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Retourne le nombre de lignes retournées le plus élevé20 requêtes SQL ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Retourne les requêtes SQL contenant 'like' ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
Septième partie : Journal binaire (binary)
Le journal binaire est différent des autres types de journaux mentionnés précédemment, le journal binaire ne peut pas être directement visualisé à l'aide de cat ou d'un visualiseur de texte comme less. Il faut utiliser des outils spécialisés. Le journal binaire enregistre principalement les changements de base de données, et peut être utilisé pour la synchronisation entre maître et esclave. Le contenu主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。En une phrase plus simple et plus compréhensible : toutes les opérations impliquant des modifications de données doivent être enregistrées dans le journal binaire.
Démarrage du journal binaire Utilisez show variables like 'log_bin'\G pour vérifier si le journal binaire est activé.
mysql> show variables like 'log_bin'\G
*************************** 1. ligne ***************************
Nom_de_variable: log_bin
Valeur: OFF
1 row in set (0.00 sec)
mysql> set @@global.log_bin=1;
ERREUR 1238 (HY000) : La variable 'log_bin' est une variable en lecture seule
mysql>
On voit que log_bin est par défaut désactivé et qu'il s'agit d'une variable en lecture seule, il faut la configurer dans my.cnf puis redémarrer MySQL. service mysql restart Après le redémarrage de MySQL, on découvre dans le répertoire data qu'un fichier a été généré1.000001Le fichier. En réalité, à chaque redémarrage de MySQL, un tel fichier est généré dans le répertoire, le nom du fichier s'incrémentant à chaque fois. De plus, MySQL crée également un fichier d'index de journal binaire dans ce répertoire, que l'on peut consulter à l'aide de la commande show variables like 'log_bin_index'\G pour obtenir l'emplacement de l'index, puis utiliser la commande cat pour le voir. On会发现, il enregistre les chemins relatifs des fichiers binaires.
Pour consulter le journal binaire, vous pouvez utiliser les outils intégrés de MySQL. Ils se trouvent dans le répertoire bin de MySQL. Options courantes de la commande mysqlbinlog :
-s Afficher le contenu du journal de manière concise
-v Afficher le contenu du journal de manière détaillée
-d=nom_de_base_de_données Afficher uniquement le contenu du journal pour la base de données spécifiée
-o=n Ignorer les n premières lignes des commandes MySQL dans le journal
-r=fichier Écrire le contenu spécifié dans le fichier spécifié
--start-datetime
Afficher le contenu du journal pour une plage de temps spécifique
--stop-datetime
--start-position
Afficher le contenu du journal pour une intervalle de position spécifique
--stop-position
Obtenir le fichier de journal binaire utilisé actuellement
mysql> show master status; +----------+----------+--------------+------------------+-------------------+ | Fichier | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------+----------+--------------+------------------+-------------------+ | 1.000002 | 120 | | | | +----------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Récupération des données à l'aide du journal binaire
La syntaxe est simple :
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
Après mysqlbinlog, on peut suivre --start-datetime,--stop-datetime, start-position, stop-position et autres paramètres.
--start-datetime,--stop-datetime ces deux paramètres peuvent être utilisés pour la récupération de données basée sur un point temporel ;
start-position, stop-La position peut être utilisée pour une récupération de données plus détaillée ;
Paramètres associés au journal binaire 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 taille d'un fichier de journalisation binaire unique. Si elle dépasse cette valeur, un nouveau fichier est généré, avec le suffixe+1;
binlog_cache_size
binlogcachesize taille du cache mémoire pour la journalisation binaire
sync_binlog
sync_binlog écrit plusieurs fois dans le cache mémoire de la journalisation binaire, puis synchronisé et actualisé dans le stockage externe (disque dur).
log_slave_updates
logslvaeupdates 用于主从复制
Nettoyage des journaux binaires
En principe, il est nécessaire de sauvegarder les journaux à préparer pour le nettoyage de manière physique sur d'autres appareils de stockage, pour une conservation à long terme. Ensuite, il est recommandé d'utiliser les deux méthodes suivantes qui présentent un risque plus faible :
Première méthode :
purge master logs before '2017-02-16 00:00:00';
Deuxième méthode :
Définissez le paramètre expire_logs_days directement dans le fichier de configuration de MySQL my.cnf pour définir le nombre de jours d'expiration des fichiers binaires. Les fichiers binaires expirés seront supprimés automatiquement. Il est recommandé d'ouvrir un autre plan de tâche périodique pour sauvegarder les tâches binaires régulièrement, afin d'éviter que des erreurs ne soient découvertes quelques jours plus tard et que les journaux binaires soient supprimés automatiquement.
expire_logs_days=90
Huit, journal des transactions InnoDB
Les journaux des transactions InnoDB sont différents des journaux mentionnés précédemment, les journaux des transactions InnoDB sont gérés par le moteur de stockage InnoDB lui-même et leur contenu ne peut pas être lu par l'administrateur de base de données. En d'autres termes, tout système à haute performance doit utiliser des caches pour améliorer l'efficacité d'accès aux données. En d'autres termes, le cache et la file d'attente sont des chemins inévitables pour atteindre une haute performance. En ce qui concerne la base de données, c'est un problème difficile à résoudre. Pour garantir que les données soient lues et stockées plus efficacement, il est nécessaire d'utiliser des caches. Cependant, pour garantir l'intégrité des données, il est nécessaire de garantir que toutes les données doivent être stockées avec précision dans la base de données, même en cas d'incident, il doit être garanti que les données peuvent être restaurées. Nous savons que InnoDB est un moteur de stockage transactionnel sécurisé et que l'intégrité est une des caractéristiques importantes des ACID des transactions. Le moteur de stockage InnoDB réalise l'intégrité des données principalement par le journal des transactions InnoDB, qui inclut le journal de réexécution (redo) et le journal de rollback (undo).
Journal de réexécution (redo)
Le journal de réexécution enregistre principalement les transactions achevées intégralement, c'est-à-dire les journaux exécutés avec commit, par défaut, les valeurs du journal de réexécution sont enregistrées dans iblogfile0 et iblogfile1dans le journal de réexécution.
[root@roverliang data]# pwd /usr/local/webserver/mysql/data [root@roverliang data]# ls ib* ibdata1 ib_logfile0 ib_logfile1
Journal de rollback (undo)
Le journal de rollback enregistre principalement les transactions non terminées mais partiellement achevées et écrites sur le disque dur, par défaut, les informations du journal de rollback sont enregistrées dans les fichiers de l'espace de données, le fichier de l'espace de données partagé ibdata1or the exclusive table space is not seen in ibd.
From the above figure, we can know that the rollback log is recorded by default in the ibdta1of. The version of my mysql system is:5.6.24.
Checkpoint mechanism
After the MySQL server crashes, when the MySQL service is restarted, due to the existence of redo logs (redo logs) and undo logs, InnoDB rolls back all partially completed transactions that have been written to the hard disk through the undo log (undo log). Then, all transactions in the redo log (undo log) are re-executed to restore all data. However, due to the large amount of data, to shorten the recovery time, InnoDB introduces the Checkpoint mechanism.
Dirty page (dirty page)
When a transaction needs to modify a record, InnoDB first reads the data block where the data is located from the external storage to the hard disk, and after the transaction is committed, InnoDB modifies the record in the data page, at this time, the cached data page is different from the data block in the external storage, and the data page in the cache is called a dirty page (dirty page), and the dirty page is refreshed to the external storage and becomes a clean page (clean page).
Note: The default memory page is4K, or4K times. You can imagine the memory as a book that can be washed, every time MySQL reads data, it applies for several clean pages to the memory, and then writes on them. After the data is refreshed to the hard disk, these data pages are immediately erased and available for other programs to use.
Log sequence number (log sequence number)
Log sequence number (LSN) is the end point of each log in the log space, represented by byte offset, and used in Checkpoint and recovery.
The principle of Checkpoint mechanism Assuming that at a certain point in time, all the dirty pages (dirty pages) have been refreshed to the hard disk, all the redo logs (redo logs) before this point in time do not need to be redone. The system takes this point as the end position of the redo log as Checkpoint, and the redo logs before the Checkpoint do not need to be redone anymore, and can be safely deleted. In order to better utilize the space of the redo log (redo), InnoDB adopts a circular strategy to use the redo log space, so the redo log file of InnoDB is at least2Through the Checkpoint mechanism, the transactions that have been completed but not yet written to the external storage due to the database crash can be redone (undo) through the redo log (redo), which can ensure data consistency and also shorten the recovery time.
InnoDB redo log (redolog) parameters
innodb_log_buffer_size: Configure the size of the redo log buffer.
innodb_log_files_in_group : configure le nombre de fichiers de journal de redondance (redo) dans le groupe de fichiers de journal.
innodb_log_file_size : configure la taille des fichiers de journal de redondance, plus le fichier est grand, plus le temps de récupération est long.
innodb_mirrored_log_groups : configure le nombre de groupes de fichiers de journal redondants, ne peut être configuré que en1.
innodb_log_group_home_dir : configure le répertoire de stockage des fichiers de groupe de journal, par défaut dans le répertoire racine de la base de données.
Paramètres du journal de rollback (undo) InnoDB
innodb_undo_directory : configure le répertoire de stockage du journal de rollback.
innodb_undo_logs : configure la taille du segment de rollback du journal de rollback, la valeur par défaut est128k
innodb_undo_tablespace : configure le nombre de fichiers de journal de rollback composant le journal de rollback, la valeur par défaut est 0.
Attention spéciale : après l'installation de MySQL, vous devez configurer les paramètres du journal de rollback dans my.cnf. Si vous configurez les paramètres du journal de rollback après avoir créé la base de données, MySQL affichera un message d'erreur, et après avoir créé le journal de rollback, il ne sera plus possible de le modifier ou d'y ajouter.
Neuvième partie : Sauvegarde des fichiers de journal
Lors de la sauvegarde, vous pouvez utiliser flush logs pour fermer tous les fichiers de journal actuels, puis créer de nouveaux fichiers de journal. Après avoir fermé les fichiers de journal, vous pouvez effectuer une sauvegarde physique. De plus, flush logs peut ajouter des types de journal spécifiques :
flush error logs flush general logs flush binary logs flush slow logs
Déclaration : Le contenu de cet article est extrait du réseau, la propriété intellectuelle appartient aux auteurs respectifs, le contenu est contribué et téléchargé par les utilisateurs d'Internet, ce site ne détient pas de propriété intellectuelle, n'a pas été traité par l'éditeur humain et n'assume aucune responsabilité juridique. Si vous trouvez du contenu présumé de violation de droits d'auteur, vous êtes invité à envoyer un email à : notice#oldtoolbag.com (veuillez remplacer # par @ lors de l'envoi d'un email pour signaler une violation de droit d'auteur, et fournir des preuves pertinentes. Une fois vérifié, ce site supprimera immédiatement le contenu présumé illicite.