Structure des répertoires de données MySQL

Comprendre l'organisation des fichiers de données MySQL est essentiel pour la gestion, la sauvegarde et la résolution de problèmes. Cet article explore les principaux répertoires et la relation entre la structure logique de la base de données et le système de fichiers.

1. Emplacement des fichiers de données

Le répertoire de données principal de MySQL contient tous les fichiers relatifs aux bases de données, aux journaux et à la configuraton du serveur. Vous pouvez trouver ce chemin en exécutant la commande suivante :

SHOW VARIABLES LIKE 'datadir';

Sur la plupart des systèmes Linux, le chemin par défaut est /var/lib/mysql/.

1.1 Répertoires des commandes

Les exécutables de la ligne de commande de MySQL, tels que mysqladmin, mysqldump et mysql, se trouvent généralement dans /usr/bin/ ou /usr/sbin/.

1.2 Répertoires de configuraton

Les fichiers de configuration principaux, comme my.cnf, se trouvent souvent dans /etc/mysql/. D'autres ressources, y compris des scripts et des configurations supplémentaires, peuvent être dans /usr/share/mysql/.

2. Relation entre les bases de données et le système de fichiers

2.1 Bases de données système par défaut

Lors de l'installation, MySQL crée plusieurs bases de données système. Vous pouvez les lister avec :

SHOW DATABASES;

Les bases de données système courantes incluent :

• mysql : Contient les informations sur les utilisateurs, les privilèges, les procédures stockées, les événements et les configurations du serveur.
• information_schema : Fournit des métadonnées sur toutes les autres bases de données, tables, colonnes, index, etc. Elle contient des tables spéciales comme celles commençant par innodb_sys.
• performance_schema : Utilisée pour surveiller les performances du serveur MySQL, y compris l'exécution des requêtes, l'utilisation de la mémoire et les temps de latence.
• sys (MySQL 8+) : Offre une couche d'abstraction au-dessus d'information_schema et de performance_schema, facilitant la surveillance des performances.

2.2 Représentation des bases de données dans le système de fichiers

Chaque base de données utilisateur (non système) correspond généralement à un sous-répertoire dans le répertoire de données principal (datadir). Par exemple, une base de données nommée ma_base aura un répertoire /var/lib/mysql/ma_base/.

Le répertoire information_schema est une exception ; il n'a pas de répertoire physique correspondant directement à son contenu dans le datadir.

2.3 Représentation des tables dans le système de fichiers

La manière dont une table est représentée dans le système de fichiers dépend du moteur de stockage utilisé (InnoDB, MyISAM, etc.).

Moteur de stockage InnoDB

InnoDB gère le stockage des tables différemment selon la configuration.

• Structure de la table : Un fichier .frm (ou intégré dans le fichier .ibd pour MySQL 8+) est créé dans le répertoire de la base de données pour décrire la structure de la table (schéma, colonnes, types de données).
• Données et index :
  • Espace de table système (System Tablespace) : Par défaut, les données et les index peuvent être stockés dans un ou plusieurs fichiers nommés ibdata1, ibdata2, etc., situés à la racine du répertoire de données. Ces fichiers sont auto-extensibles.
  • Espace de table par table (File-per-table Tablespace) : À partir de MySQL 5.6.6, la configuration par défaut (innodb_file_per_table=1) crée un fichier distinct .ibd pour chaque table dans le répertoire de sa base de données. Ce fichier .ibd contient à la fois les données et les index de la table.

La configuration innodb_file_per_table contrôle si les tables utilisent l'espace de tablee système ou des fichiers .ibd individuels. La valeur 1 (activé) est la configuration par défaut et recommandée.

Dans MySQL 8+, les informations de structure de table sont intégrées dans le fichier .ibd, éliminant ainsi le besoin d'un fichier .frm séparé.

Moteur de stockage MyISAM

MyISAM stocke la structure de la table, les données et les index dans des fichiers séparés.

• Structure de la table : Un fichier .frm (ou un fichier .xxx.sdi dans les versions plus récentes) est créé dans le répertoire de la base de données.
• Données : Un fichier .MYD (MYData) est créé pour stocker les données de la table.
• Index : Un fichier .MYI (MYIndex) est créé pour stocker les index de la table.

Ces trois fichiers (.frm, .MYD, .MYI) se trouvent dans le sous-répertoire de la base de données correspondante.

2.4 Fichier db.opt

Dans certaines versions de MySQL (notamment avant MySQL 8.0), un fichier db.opt pouvait être trouvé dans le répertoire de chaque base de données. Ce fichier stockait les options de la base de données, telles que le jeu de caractères et les règles de tri par défaut.

3. Récapitulatif

Pour une base de données nommée a et une table nommée b :

• Avec InnoDB :
  • a/b.frm : Décrit la structure de la table (sauf MySQL 8+ où c'est dans b.ibd).
  • a/b.ibd : Contient les données et les index si innodb_file_per_table est activé.
  • ibdata1 (et fichiers suivants) : Contient les données et les index si innodb_file_per_table est désactivé.
  • a/db.opt : Options de la base de données (versions antérieures à MySQL 8.0).
• Avec MyISAM :
  • a/b.frm : Décrit la structure de la table (ou b.xxx.sdi dans les versions récentes).
  • a/b.MYD : Contient les données de la table.
  • a/b.MYI : Contient les index de la table.