Vous lancez mysql -u root -p, vous tapez des requêtes SQL, ça fonctionne. Mais qu’est-ce qui tourne en dessous ? Quel processus gère vos connexions, où sont stockées les données, pourquoi la mémoire grimpe après quelques minutes, et quel est ce fameux buffer pool que tout le monde recommande d’augmenter ?
MySQL n’est pas une simple base de données — c’est un système complet avec son propre vocabulaire : instance, moteur de stockage, InnoDB, buffer pool, redo log, binary log, data dictionary. InnoDB gère vos données en mémoire et sur disque avec un mécanisme de journalisation analogue aux WAL de PostgreSQL. Le binary log enregistre chaque modification pour la réplication et la restauration. Ce guide démonte la mécanique interne pour que vous compreniez ce que vous administrez avant de toucher à la configuration.
Ce que vous allez apprendre
Section intitulée « Ce que vous allez apprendre »- Décrire l’architecture client/serveur de MySQL et comprendre le rôle de chaque couche (connecteur, parseur, optimiseur, moteur de stockage)
- Expliquer pourquoi InnoDB est le moteur par défaut et ce qui le rend transactionnel (ACID)
- Distinguer les trois structures critiques : buffer pool (cache mémoire), redo log (journal crash-safe) et binary log (réplication/PITR)
- Identifier les threads MySQL et comprendre leur rôle dans le traitement des connexions et la maintenance
- Différencier le track LTS (8.4) du track Innovation (9.x) pour choisir la bonne version
- Naviguer dans les fichiers d’une installation MySQL et savoir où trouver les données, la configuration et les logs
Dans quel contexte ?
Section intitulée « Dans quel contexte ? »Ce guide est le point de départ avant toute administration MySQL. Vous en avez besoin quand :
- Vous installez MySQL pour la première fois et voulez comprendre ce qui a été créé automatiquement (répertoires, fichiers, bases système)
- Vous devez diagnostiquer un problème de performance et on vous dit « augmente le buffer pool » — mais vous ne savez pas ce que c’est ni comment le dimensionner
- Vous préparez une stratégie de sauvegarde et devez comprendre la différence entre redo log et binary log avant de configurer quoi que ce soit
- Vous lisez la documentation officielle et les termes tablespace, doublewrite buffer, change buffer vous bloquent
- Vous devez choisir entre MySQL 8.4 LTS et MySQL 9.x Innovation pour un nouveau projet
Ce guide ne couvre pas…
Section intitulée « Ce guide ne couvre pas… »Ce guide explique l’architecture — il ne configure rien. Les actions pratiques sont dans les guides dédiés :
L’instance MySQL : un serveur, plusieurs bases
Section intitulée « L’instance MySQL : un serveur, plusieurs bases »Un processus unique : mysqld
Section intitulée « Un processus unique : mysqld »Contrairement à PostgreSQL qui fonctionne en multi-processus (un processus par connexion), MySQL utilise un modèle multi-thread : un seul processus — mysqld — gère tout. Ce processus écoute les connexions, exécute les requêtes et coordonne les opérations d’I/O. Chaque client connecté obtient un thread dédié à l’intérieur de ce processus.
Analogie : si PostgreSQL est un immeuble de bureaux où chaque employé travaille dans son propre bureau (processus), MySQL est un open space où tout le monde travaille dans la même pièce (processus) avec son propre poste de travail (thread). L’avantage : moins de coût à créer un thread qu’un processus. L’inconvénient : un problème mémoire dans un thread peut affecter tous les autres.
ps aux | grep mysqldmysql 4521 2.3 8.1 1842384 331276 ? Ssl 08:49 0:12 /usr/sbin/mysqldUn seul processus, un seul PID. Toute la gestion des connexions, du cache, des I/O et de la maintenance se fait à l’intérieur de ce processus via des threads.
Plusieurs bases dans une instance
Section intitulée « Plusieurs bases dans une instance »Une instance MySQL contient une ou plusieurs bases de données (appelées aussi schémas — les deux termes sont synonymes dans MySQL). Chaque base est un espace de noms contenant des tables, des vues, des procédures stockées et des triggers.
Sur une installation fraîche, quatre bases système existent :
SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+| Base | Rôle |
|---|---|
mysql | Base système qui contient les comptes utilisateurs, les privilèges, les événements planifiés et les métadonnées internes. C’est l’équivalent du catalogue système de PostgreSQL. |
information_schema | Vue en lecture seule sur les métadonnées de toutes les bases : tables, colonnes, index, contraintes. Utile pour l’introspection. |
performance_schema | Collecte des métriques en temps réel : requêtes, verrous, I/O, threads, mémoire. C’est le système d’instrumentation interne de MySQL. |
sys | Vues simplifiées qui combinent performance_schema et information_schema pour faciliter le diagnostic. |
Base de données et schéma : même chose dans MySQL
Section intitulée « Base de données et schéma : même chose dans MySQL »Dans MySQL, CREATE DATABASE et CREATE SCHEMA sont strictement synonymes. Un schéma = une base de données = un répertoire dans le datadir. C’est une différence fondamentale avec PostgreSQL, où une base contient plusieurs schémas.
-- Ces deux commandes sont identiquesCREATE DATABASE labdb;CREATE SCHEMA labdb;Dans le système de fichiers, chaque base correspond à un répertoire :
ls /var/lib/mysql/#innodb_redo binlog.000001 ca-key.pem ib_buffer_pool mysql.ibd public_key.pem undo_001#innodb_temp binlog.000002 ca.pem ibdata1 mysql.sock server-cert.pem undo_002auto.cnf binlog.index client-cert.pem ibtmp1 performance_schema server-key.pembinlog.000001 ca-key.pem client-key.pem mysql private_key.pem sysLes répertoires mysql/, performance_schema/, sys/ correspondent aux bases système. Quand vous créez une base labdb, un répertoire labdb/ apparaît avec les fichiers .ibd de chaque table.
Le modèle client/serveur
Section intitulée « Le modèle client/serveur »MySQL fonctionne en client/serveur. Le serveur (mysqld) écoute sur un socket Unix (connexion locale) ou un port TCP (connexion réseau, par défaut 3306). Le client (mysql, mysqldump, mysqlsh, ou toute application) se connecte au serveur, envoie des requêtes SQL et reçoit les résultats.
Les couches internes de mysqld
Section intitulée « Les couches internes de mysqld »Quand une requête SQL arrive, elle traverse plusieurs couches à l’intérieur de mysqld :
-
Connecteur — gère l’authentification (plugin
caching_sha2_passwordpar défaut depuis MySQL 8.0), maintient la session et le thread dédié au client -
Parseur SQL — analyse la syntaxe de la requête et construit un arbre syntaxique (AST). Si la syntaxe est invalide, l’erreur est renvoyée ici
-
Optimiseur — choisit le plan d’exécution le plus efficace : quel index utiliser, dans quel ordre joindre les tables, faut-il un tri ou un scan complet. C’est la partie qui détermine les performances
-
Exécuteur — exécute le plan choisi en appelant l’API du moteur de stockage. C’est ici que MySQL délègue à InnoDB (ou un autre moteur) la lecture et l’écriture des données
-
Moteur de stockage (InnoDB) — gère le stockage physique : cache mémoire (buffer pool), journalisation (redo log), écriture sur disque, verrouillage des lignes, transactions ACID
Cette architecture en couches explique pourquoi MySQL supporte plusieurs moteurs de stockage : la couche SQL est indépendante du moteur. Mais dans la pratique, InnoDB est le seul moteur à utiliser pour les données de production.
Connexion locale vs connexion réseau
Section intitulée « Connexion locale vs connexion réseau »Par défaut sur Debian, MySQL écoute sur deux canaux :
# Socket Unix (connexion locale)ls -la /var/run/mysqld/mysqld.socksrwxrwxrwx 1 mysql mysql 0 Apr 13 08:49 /var/run/mysqld/mysqld.sock# Port TCP (connexion réseau)ss -tlnp | grep 3306LISTEN 0 151 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=4521,fd=23))Le socket Unix est plus rapide que TCP pour les connexions locales (pas de couche réseau). C’est ce que mysql -u root -p utilise par défaut quand vous ne spécifiez pas de -h.
| Type | Commande | Usage |
|---|---|---|
| Socket Unix | mysql -u root -p | Connexion locale, administration |
| TCP localhost | mysql -h 127.0.0.1 -u root -p | Connexion locale via TCP |
| TCP réseau | mysql -h 192.168.122.70 -u labadmin -p | Connexion depuis un autre serveur |
Les moteurs de stockage : InnoDB au centre
Section intitulée « Les moteurs de stockage : InnoDB au centre »InnoDB : le moteur par défaut
Section intitulée « InnoDB : le moteur par défaut »InnoDB est le moteur de stockage par défaut de MySQL depuis la version 5.5 (2010). Toutes les tables devraient utiliser InnoDB, sauf cas très spécifique. C’est le seul moteur qui offre l’ensemble des garanties attendues d’une base de données moderne :
| Caractéristique | InnoDB | MyISAM |
|---|---|---|
| Transactions ACID | ✅ COMMIT, ROLLBACK | ❌ |
| Clés étrangères | ✅ FOREIGN KEY | ❌ |
| Verrouillage | ✅ Ligne (row-level) | ❌ Table (table-level) |
| Crash recovery | ✅ Via redo log | ❌ Réparation manuelle |
| MVCC | ✅ Lectures non bloquantes | ❌ |
| Fulltext | ✅ (depuis 5.6) | ✅ |
| Compression | ✅ PAGE_COMPRESSED | ✅ (format différent) |
ACID signifie : Atomicité (une transaction entière réussit ou échoue), Cohérence (les contraintes sont toujours respectées), Isolation (les transactions concurrentes ne se voient pas), Durabilité (une transaction commitée survit à un crash).
MVCC (Multi-Version Concurrency Control) signifie que les lectures ne bloquent pas les écritures et inversement. Chaque transaction voit un snapshot cohérent des données, même si d’autres transactions modifient les mêmes lignes au même moment. C’est ce qui permet à MySQL de gérer des milliers de connexions simultanées sans que les SELECT ne soient bloqués par les UPDATE.
Pour vérifier le moteur d’une table :
SHOW TABLE STATUS FROM labdb WHERE Name = 'servers'\G*************************** 1. row *************************** Name: servers Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 10 Avg_row_length: 1638 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 11 Create_time: 2026-04-13 09:15:23 Update_time: 2026-04-13 09:15:23 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULLLe champ Engine: InnoDB confirme le moteur. Row_format: Dynamic est le format par défaut depuis MySQL 5.7 — il gère efficacement les colonnes de taille variable (VARCHAR, TEXT, BLOB).
Autres moteurs (Memory, MyISAM, NDB)
Section intitulée « Autres moteurs (Memory, MyISAM, NDB) »MySQL supporte d’autres moteurs, mais ils servent des cas d’usage très spécifiques :
| Moteur | Usage | Limites |
|---|---|---|
| Memory (HEAP) | Tables temporaires en RAM, caches de session | Données perdues au redémarrage, pas de transactions |
| MyISAM | Legacy — ne plus utiliser pour de nouvelles tables | Pas de transactions, verrouillage table, crash = réparation manuelle |
| NDB (Cluster) | MySQL Cluster (NDB Cluster) pour la haute disponibilité distribuée | Architecture complexe, cas d’usage très spécifique (télécom, temps réel) |
| ARCHIVE | Tables en lecture seule compressées, logs historiques | Pas d’index, pas d’UPDATE, pas de DELETE |
| CSV | Import/export de fichiers CSV | Pas d’index, pas de transactions |
SHOW ENGINES;+--------------------+---------+------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+------------------------------------------------------------+--------------+------+------------+| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it vanishes) | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |+--------------------+---------+------------------------------------------------------------+--------------+------+------------+Architecture interne d’InnoDB
Section intitulée « Architecture interne d’InnoDB »InnoDB est le cœur de MySQL. Comprendre ses structures internes vous permet de diagnostiquer les problèmes de performance et de configurer correctement la mémoire. Voici les composants essentiels :
Le buffer pool
Section intitulée « Le buffer pool »Le buffer pool est la zone de mémoire principale d’InnoDB. C’est un cache de pages (16 Ko par page) qui stocke à la fois les données et les index des tables InnoDB. Quand MySQL lit une ligne, il charge la page contenant cette ligne dans le buffer pool. Les lectures suivantes de la même page sont servies depuis la mémoire — sans accès disque.
Analogie : le buffer pool est comme le plan de travail d’un cuisinier. Plus le plan de travail est grand, plus vous pouvez garder d’ingrédients (pages de données) à portée de main sans aller au frigo (disque). Un buffer pool trop petit force MySQL à aller constamment au disque, ce qui ralentit tout.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';+-------------------------+-----------+| Variable_name | Value |+-------------------------+-----------+| innodb_buffer_pool_size | 134217728 |+-------------------------+-----------+134 217 728 octets = 128 Mo — c’est la valeur par défaut. C’est adapté à un petit lab, mais pas à la production. Sur un serveur dédié à MySQL, la documentation Oracle indique qu’on peut aller jusqu’à 80 % de la RAM physique pour le buffer pool. En pratique, 50 à 80 % de la mémoire est un bon point de départ selon les autres services présents sur la machine. Sur un serveur avec 4 Go de RAM où MySQL est le seul service, innodb_buffer_pool_size = 2G à 3G est raisonnable.
Pour voir l’utilisation actuelle du buffer pool :
SHOW ENGINE INNODB STATUS\GLa section BUFFER POOL AND MEMORY indique :
----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 0Dictionary memory allocated 594498Buffer pool size 8192Free buffers 7024Database pages 1149Old database pages 0Modified db pages 0| Métrique | Signification |
|---|---|
| Buffer pool size | Nombre total de pages (8 192 × 16 Ko = 128 Mo) |
| Free buffers | Pages libres, inutilisées |
| Database pages | Pages contenant des données en cache |
| Modified db pages | Pages modifiées en mémoire mais pas encore écrites sur disque (dirty pages) |
Quand Free buffers atteint 0, InnoDB doit évincer des pages anciennes pour en charger de nouvelles — c’est là que les performances se dégradent. Le guide Configuration détaille le dimensionnement du buffer pool.
Le redo log (InnoDB log)
Section intitulée « Le redo log (InnoDB log) »Le redo log est le journal de crash recovery d’InnoDB. Chaque modification de données est d’abord écrite dans le redo log avant d’être appliquée aux fichiers de données. C’est le mécanisme Write-Ahead Logging — le même principe que les WAL de PostgreSQL.
Analogie : le redo log est un carnet de bord. Avant de ranger un livre dans la bibliothèque (fichier de données), vous notez dans le carnet « ranger le livre X à l’emplacement Y ». Si quelqu’un renverse la bibliothèque (crash serveur), vous pouvez tout reconstituer en relisant le carnet.
Depuis MySQL 8.0.30, les fichiers redo log se trouvent dans #innodb_redo/ :
ls /var/lib/mysql/#innodb_redo/#ib_redo10 #ib_redo11 #ib_redo12_tmp #ib_redo13_tmp #ib_redo14_tmp#ib_redo15_tmp #ib_redo16_tmp #ib_redo17_tmp #ib_redo18_tmp #ib_redo19_tmpLes fichiers sans _tmp sont actifs (en cours d’écriture). Les fichiers _tmp sont de réserve (recyclés quand les actifs sont pleins). Depuis MySQL 8.0.30, InnoDB stocke ses redo logs dans ce répertoire #innodb_redo/ et pilote leur capacité globale via innodb_redo_log_capacity. Les anciennes variables innodb_log_file_size et innodb_log_files_in_group sont désormais dépréciées.
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';+--------------------------+-----------+| Variable_name | Value |+--------------------------+-----------+| innodb_redo_log_capacity | 104857600 |+--------------------------+-----------+104 857 600 octets = 100 Mo par défaut — la capacité globale allouée aux redo logs. InnoDB gère automatiquement la taille et le nombre de fichiers à l’intérieur de cette enveloppe.
Le doublewrite buffer
Section intitulée « Le doublewrite buffer »Le doublewrite buffer est une protection contre les écritures partielles sur disque. Le problème : une page InnoDB fait 16 Ko, mais le système de fichiers peut écrire par blocs de 4 Ko. Si le serveur crashe au milieu de l’écriture d’une page, seule une partie est écrite — la page est corrompue.
Le mécanisme :
- Avant d’écrire une page modifiée sur disque, InnoDB l’écrit d’abord dans le doublewrite buffer (des fichiers dédiés sur disque)
- Puis il écrit la page à son emplacement final dans le tablespace
- Si un crash interrompt l’étape 2, InnoDB retrouve la copie intacte dans le doublewrite buffer au redémarrage et la réécrit
Depuis MySQL 8.0.20, le doublewrite buffer n’est plus stocké dans le tablespace système (ibdata1). InnoDB utilise des doublewrite files dédiés, contrôlés par les variables innodb_doublewrite_dir et innodb_doublewrite_files :
ls /var/lib/mysql/#ib_*dblwr*/var/lib/mysql/#ib_16384_0.dblwr /var/lib/mysql/#ib_16384_1.dblwrSHOW VARIABLES LIKE 'innodb_doublewrite%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| innodb_doublewrite | ON || innodb_doublewrite_batch_size | 0 || innodb_doublewrite_dir | || innodb_doublewrite_files | 2 || innodb_doublewrite_pages | 4 |+-------------------------------+-------+Ne désactivez jamais le doublewrite buffer sauf si votre système de fichiers garantit des écritures atomiques de 16 Ko (comme ZFS ou certains SAN avec écriture atomique). Sur ext4 ou XFS, le risque de corruption est réel sans cette protection.
Le tablespace système et les tablespaces fichier par table
Section intitulée « Le tablespace système et les tablespaces fichier par table »Un tablespace est un conteneur logique qui regroupe les fichiers de données InnoDB. MySQL utilise deux types de tablespaces :
Le tablespace système (ibdata1) contient principalement le change buffer et d’autres structures internes d’InnoDB. Depuis MySQL 8.0.20, le doublewrite buffer est stocké dans des fichiers dédiés (.dblwr), et les undo logs résident dans des undo tablespaces séparés (undo_001, undo_002) :
ls -lh /var/lib/mysql/ibdata1-rw-r----- 1 mysql mysql 12M Apr 13 08:49 /var/lib/mysql/ibdata1Les tablespaces fichier par table (file-per-table) : chaque table InnoDB est stockée dans son propre fichier .ibd. C’est le comportement par défaut depuis MySQL 5.6 :
ls /var/lib/mysql/labdb/servers.ibd deployments.ibd events.ibd access_log.ibd metrics.ibdChaque fichier .ibd contient les données et les index de la table correspondante. Ce modèle présente plusieurs avantages :
| Avantage | Explication |
|---|---|
| Gestion de l’espace | Un DROP TABLE libère immédiatement l’espace disque (supprime le .ibd) |
| Sauvegarde ciblée | Possibilité de copier/restaurer une seule table |
| Compression | Chaque table peut être compressée indépendamment |
| Monitoring | La taille de chaque table est visible dans le filesystem |
SHOW VARIABLES LIKE 'innodb_file_per_table';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+Le change buffer
Section intitulée « Le change buffer »Le change buffer est une optimisation pour les opérations d’écriture sur les index secondaires. Quand vous insérez une ligne, MySQL doit mettre à jour non seulement la table mais aussi tous ses index. Si la page d’index n’est pas déjà en mémoire (dans le buffer pool), InnoDB devrait la charger depuis le disque — une opération coûteuse.
Au lieu de ça, InnoDB met en cache la modification dans le change buffer. Quand la page d’index sera finalement lue (par un SELECT ou lors du prochain merge), la modification sera appliquée à ce moment-là. Cela réduit considérablement les I/O aléatoires sur les systèmes avec beaucoup d’écritures.
Le change buffer est une partie du tablespace système (ibdata1) et est aussi mis en cache dans le buffer pool.
Le binary log : réplication et PITR
Section intitulée « Le binary log : réplication et PITR »Binlog vs redo log
Section intitulée « Binlog vs redo log »MySQL utilise deux systèmes de journalisation distincts qui ont des rôles complètement différents :
| Redo log | Binary log (binlog) | |
|---|---|---|
| Géré par | InnoDB (moteur de stockage) | MySQL Server (couche SQL) |
| Contenu | Modifications physiques des pages InnoDB | Événements logiques (INSERT, UPDATE, DELETE, DDL) |
| Usage | Crash recovery (redémarrage après crash) | Réplication + PITR (restauration à un instant précis) |
| Fichiers | #innodb_redo/#ib_redo* | binlog.000001, binlog.000002, … |
| Rotation | Automatique, recyclage circulaire | Rotation par taille (max_binlog_size) ou flush |
| Rétention | Court terme (entre checkpoints) | Configurable (binlog_expire_logs_seconds, défaut : 30 jours) |
En résumé : le redo log est un journal interne à InnoDB pour qu’il puisse se relever après un crash. Le binary log est un journal global de toutes les modifications, utilisé pour envoyer les changements aux réplicas et pour restaurer les données à un instant précis.
Formats du binary log (ROW, STATEMENT, MIXED)
Section intitulée « Formats du binary log (ROW, STATEMENT, MIXED) »Le binary log peut enregistrer les modifications de données sous trois formats :
| Format | Ce qui est enregistré | Avantage | Inconvénient |
|---|---|---|---|
| ROW | L’image avant/après de chaque ligne modifiée | Déterministe, pas d’ambiguïté | Binlog plus volumineux pour les UPDATE massifs |
| STATEMENT | La requête SQL elle-même (UPDATE ... WHERE ...) | Binlog compact | Non déterministe (NOW(), UUID(), fonctions aléatoires) |
| MIXED | STATEMENT quand possible, ROW quand nécessaire | Compromis | Comportement parfois imprévisible |
SHOW VARIABLES LIKE 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+ROW est le format par défaut depuis MySQL 5.7.7 et c’est le format recommandé. Il garantit une réplication déterministe — le réplica applique exactement les mêmes modifications que la source, quelles que soient les fonctions utilisées dans la requête.
Pour voir le contenu du binary log :
mysqlbinlog --verbose /var/lib/mysql/binlog.000002 | head -40Le binary log contient des événements structurés : Query_event pour les DDL, Write_rows_event pour les INSERT, Update_rows_event pour les UPDATE, Delete_rows_event pour les DELETE.
Le data dictionary (depuis MySQL 8.0)
Section intitulée « Le data dictionary (depuis MySQL 8.0) »Avant MySQL 8.0, les métadonnées des tables (colonnes, index, types) étaient stockées dans des fichiers .frm — un fichier par table, dans un format propriétaire non transactionnel. Un crash pendant un DDL (ALTER TABLE, DROP TABLE) pouvait créer des incohérences entre les fichiers .frm et les données InnoDB.
Depuis MySQL 8.0, le data dictionary est stocké dans des tables InnoDB transactionnelles, dans le tablespace mysql.ibd. Plus de fichiers .frm. Les opérations DDL sont maintenant atomiques : un DROP TABLE sur plusieurs tables réussit ou échoue entièrement, sans état intermédiaire.
ls /var/lib/mysql/mysql.ibd-rw-r----- 1 mysql mysql 31457280 Apr 13 09:15 /var/lib/mysql/mysql.ibdCe fichier unique contient l’intégralité des métadonnées : tables, colonnes, index, contraintes, charset, collation, routines, vues — pour toutes les bases de l’instance.
Impact concret : vous ne verrez plus de fichiers .frm dans les répertoires de bases. Les vues de INFORMATION_SCHEMA sont alimentées par le data dictionary (plus rapides qu’avant). Et les opérations DDL sont plus sûres.
Les threads MySQL
Section intitulée « Les threads MySQL »Threads de connexion
Section intitulée « Threads de connexion »Quand un client se connecte à MySQL, le serveur crée un thread de connexion (foreground thread) dédié à cette session. Ce thread exécute les requêtes du client, gère les transactions et renvoie les résultats. À la déconnexion, le thread peut être recyclé via le thread cache :
SHOW VARIABLES LIKE 'thread_cache_size';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| thread_cache_size | 9 |+-------------------+-------+Au lieu de détruire le thread à chaque déconnexion et d’en créer un nouveau à chaque connexion, MySQL garde 9 threads en réserve (par défaut). Cela réduit le coût de création de threads pour les applications avec beaucoup de connexions courtes.
Pour voir les threads actuellement actifs :
SHOW PROCESSLIST;+----+---------+-----------+------+---------+------+----------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+---------+-----------+------+---------+------+----------+------------------+| 8 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |+----+---------+-----------+------+---------+------+----------+------------------+Chaque ligne représente un thread de connexion. Le nombre maximal est contrôlé par max_connections (défaut : 151). Au-delà, MySQL refuse les connexions avec l’erreur Too many connections.
Threads d’arrière-plan (purge, page cleaner, log writer)
Section intitulée « Threads d’arrière-plan (purge, page cleaner, log writer) »En plus des threads de connexion, InnoDB lance des threads d’arrière-plan qui assurent la maintenance continue :
| Thread | Rôle | Paramètre de contrôle |
|---|---|---|
| Master thread | Coordonne les opérations d’arrière-plan, déclenche les flushes | — |
| Page cleaner | Écrit les dirty pages du buffer pool vers les fichiers .ibd | innodb_page_cleaners (défaut : 4) |
| Purge thread | Supprime les anciennes versions de lignes (MVCC) devenues inutiles | innodb_purge_threads (défaut : 4) |
| Log writer | Écrit les entrées du redo log buffer vers les fichiers redo | — |
| Log flusher | Synchronise (fsync) les fichiers redo log sur disque | — |
| Checkpoint | Marque les positions dans le redo log jusqu’où les données sont écrites sur disque | — |
SELECT name, type, processlist_commandFROM performance_schema.threadsWHERE type = 'BACKGROUND'ORDER BY nameLIMIT 15;+----------------------------------------+------------+-------------------+| name | type | processlist_command |+----------------------------------------+------------+-------------------+| innodb/buf_dump_thread | BACKGROUND | NULL || innodb/clone_gtid_thread | BACKGROUND | NULL || innodb/dict_stats_thread | BACKGROUND | NULL || innodb/fts_optimize_thread | BACKGROUND | NULL || innodb/io_ibuf_thread | BACKGROUND | NULL || innodb/io_log_thread | BACKGROUND | NULL || innodb/io_read_thread | BACKGROUND | NULL || innodb/io_read_thread | BACKGROUND | NULL || innodb/io_write_thread | BACKGROUND | NULL || innodb/io_write_thread | BACKGROUND | NULL || innodb/log_checkpointer_thread | BACKGROUND | NULL || innodb/log_closer_thread | BACKGROUND | NULL || innodb/log_files_governor_thread | BACKGROUND | NULL || innodb/log_flusher_thread | BACKGROUND | NULL || innodb/log_writer_thread | BACKGROUND | NULL |+----------------------------------------+------------+-------------------+Contrairement à PostgreSQL où les workers sont des processus visibles avec ps aux, les threads InnoDB sont internes au processus mysqld. Vous ne les verrez pas dans ps — utilisez performance_schema.threads pour les inspecter.
MySQL 8.4 LTS vs Innovation (9.x) : quel track choisir
Section intitulée « MySQL 8.4 LTS vs Innovation (9.x) : quel track choisir »Depuis 2023, Oracle publie MySQL selon deux tracks parallèles :
| LTS (Long Term Support) | Innovation | |
|---|---|---|
| Version actuelle | 8.4 (avril 2024) | 9.0, 9.1, 9.2… |
| Positionnement | Stabilité fonctionnelle | Accès plus rapide aux nouveautés |
| Qualité | Production-grade | Production-grade |
| Support | Support long (5 ans Premier + 3 ans Extended) | Cycle plus court (jusqu’à la version suivante) |
| Stabilité API | Pas de suppression de fonctionnalités | Fonctions retirées ou renommées entre versions |
| Cas d’usage | Production stable, standardisation, certifications | Adoption rapide des nouveautés, équipes prêtes à suivre le rythme |
| Fréquence | Patches trimestriels (8.4.x) | Nouvelle version tous les ~3 mois |
SELECT VERSION();+-----------+| VERSION() |+-----------+| 8.4.5 |+-----------+Changements notables dans MySQL 8.4 LTS par rapport à 8.0 :
- Le binary log est activé par défaut (plus besoin de
--log-binexplicite) - Le plugin
mysql_native_passwordn’est plus activé par défaut — pour les nouveaux déploiements, utilisezcaching_sha2_password(le plugin reste disponible mais déprécié, et sera supprimé en MySQL 9.0) - Les variables
innodb_log_file_sizeetinnodb_log_files_in_groupsont remplacées parinnodb_redo_log_capacity CHANGE MASTER TOest remplacé parCHANGE REPLICATION SOURCE TO(ancienne syntaxe encore acceptée mais dépréciée)SHOW SLAVE STATUSest remplacé parSHOW REPLICA STATUS- Le terme slave est remplacé par replica partout dans la documentation et les commandes
Les fichiers clés d’une installation MySQL
Section intitulée « Les fichiers clés d’une installation MySQL »Voici l’arborescence d’une installation MySQL 8.4 sur Debian 12, avec le rôle de chaque fichier :
/etc/mysql/├── my.cnf → Fichier principal (inclut conf.d/ et mysql.conf.d/)├── conf.d/│ └── mysql.cnf → Options du client mysql└── mysql.conf.d/ └── mysqld.cnf → Configuration du serveur mysqld
/var/lib/mysql/ → datadir (toutes les données)├── #innodb_redo/ → Fichiers redo log├── #innodb_temp/ → Tablespaces temporaires├── auto.cnf → UUID du serveur (unique par instance)├── binlog.000001 → Binary log (réplication + PITR)├── binlog.index → Index des fichiers binlog├── ca.pem, server-cert.pem, … → Certificats TLS auto-générés├── ib_buffer_pool → Dump du buffer pool au shutdown (warm restart)├── ibdata1 → Tablespace système (doublewrite, change buffer)├── ibtmp1 → Tablespace temporaire├── mysql/ → Base système mysql├── mysql.ibd → Data dictionary (métadonnées transactionnelles)├── performance_schema/ → Base performance_schema├── sys/ → Base sys├── undo_001, undo_002 → Undo tablespaces (MVCC, rollback)└── labdb/ → Vos données (un répertoire par base) ├── servers.ibd ├── deployments.ibd └── …
/var/log/mysql/├── error.log → Log d'erreurs et de démarrage└── mysql-slow.log → Requêtes lentes (si activé)
/var/run/mysqld/└── mysqld.sock → Socket Unix pour connexions locales| Fichier/Répertoire | Rôle | Criticité |
|---|---|---|
/etc/mysql/mysql.conf.d/mysqld.cnf | Configuration serveur (buffer pool, connexions, log) | Élevée — vos réglages personnalisés |
/var/lib/mysql/ | Toutes les données, l’intégrité de la base dépend de ce répertoire | Critique — sauvegardez-le |
ibdata1 | Tablespace système partagé | Critique — ne jamais supprimer |
mysql.ibd | Data dictionary (toutes les métadonnées) | Critique — indispensable au démarrage |
binlog.* | Binary log pour réplication et PITR | Élevée — nécessaire pour la restauration |
#innodb_redo/ | Redo log pour crash recovery | Critique — géré automatiquement |
undo_001/002 | Undo tablespaces (rollback, MVCC) | Élevée — gérés automatiquement |
auto.cnf | UUID unique du serveur | Moyenne — ne jamais copier tel quel sur un réplica |
ib_buffer_pool | Contenus du buffer pool au dernier shutdown | Basse — optimisation de redémarrage |
Vocabulaire essentiel
Section intitulée « Vocabulaire essentiel »| Terme | Signification dans MySQL |
|---|---|
| Instance | Un processus mysqld en cours d’exécution, avec son datadir et sa configuration |
| Base de données / Schéma | Un espace de noms contenant des tables (les deux termes sont synonymes) |
| InnoDB | Moteur de stockage par défaut — transactionnel, crash-safe, MVCC |
| Buffer pool | Cache mémoire d’InnoDB pour les pages de données et d’index |
| Redo log | Journal de crash recovery — écrit avant toute modification (Write-Ahead Logging) |
| Binary log (binlog) | Journal de réplication et PITR — enregistre les événements DML/DDL |
| GTID | Global Transaction Identifier — identifiant unique de transaction pour la réplication |
| Data dictionary | Métadonnées stockées dans InnoDB (depuis MySQL 8.0), remplace les fichiers .frm |
| Tablespace | Conteneur logique de fichiers de données (ibdata1 = système, .ibd = par table) |
| Doublewrite buffer | Protection contre les écritures partielles lors du flush des pages |
| Change buffer | Cache des modifications d’index secondaires non encore en mémoire |
| LTS | Long Term Support — version supportée 8 ans (actuellement 8.4) |
| Thread de connexion | Thread dédié à un client connecté (un thread par session) |
| Performance Schema | Système d’instrumentation intégré exposant les métriques internes |
À retenir
Section intitulée « À retenir »- MySQL est mono-processus, multi-thread — un seul
mysqldgère tout, avec un thread par connexion client et des threads d’arrière-plan pour la maintenance. - InnoDB est le seul moteur à utiliser en production. Il offre les transactions ACID, le crash recovery via redo log, le MVCC et le verrouillage au niveau ligne.
- Le buffer pool est le paramètre de performance le plus important. La valeur par défaut (128 Mo) est bien trop faible — visez 50-70 % de la RAM disponible.
- Le redo log (InnoDB) garantit le crash recovery. Le binary log (MySQL Server) sert à la réplication et au PITR. Ce sont deux journaux distincts, coordonnés par un two-phase commit.
- Chaque table InnoDB est stockée dans son propre fichier
.ibd(tablespace fichier par table), ce qui simplifie la gestion de l’espace et les sauvegardes ciblées. - Le data dictionary d’InnoDB (depuis MySQL 8.0) remplace les anciens fichiers
.frmpar des métadonnées transactionnelles — les DDL sont maintenant atomiques. - MySQL 8.4 LTS est la version recommandée pour la production (support 8 ans). Les versions Innovation (9.x) ne reçoivent des patches que 3 mois.
- Les connexions locales passent par un socket Unix (
/var/run/mysqld/mysqld.sock), les connexions réseau par TCP sur le port 3306.