MySQL et InnoDB : activer les fichiers au niveau table avec innodb_file_per_table

Fichiers de base d’InnoDB

Par défaut, votre configuration pour les tables utilisant le moteur InnoDB avec MySQL est la suivante :

  • un fichier ibdata1 constamment alimenté est créé
  • deux fichiers de log (ib_logfile0 et ib_logfile1) sont crées

Vous pouvez les voir en faisant : 

sebastien.ferrandez@sebastien$ ls -l /var/lib/mysql
total 28736
-rw-rw---- 1 mysql mysql 18874368 mai   21 10:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 mai   21 10:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 mai   20 15:58 ib_logfile1

Evidemment, il faudra au besoin modifier le chemin si vous avez customisé votre configuration MySQL. Le problème de ce fichier ibdata1 en mode append est qu’il a tendance à grossir très vite et à atteindre des tailles problématiques (de nature à remettre en cause le bon fonctionnement de MySQL) : sur ma machine locale, le mien faisait 3.4G ! Il m’a fallu dumper mes bases de données à des fins de sauvegarde, faire en sorte de ne plus avoir ce fichier monolithique mais plusieurs (par base de données et par table), redémarrer MySQL et rejouer mon script SQL pour remettre en place les données dont j’avais besoin rapidement.

mysql

Pour effectuer ce découpage propre par table, nous allons nous servir de la directive de configuration innodb_file_per_table. Comme je le disais plus haut, celle-ci n’est pas activée par défaut :

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+

Pour l’activer, il vous suffit d’en faire mention dans votre fichier my.cnf (situé par défaut sur mon installation dans /etc/mysql/my.cnf), dans la section réservée à mysqld (par exemple du côté du commentaire « Fine-tuning ») et évidemment il vous faudra redémarrer le démon mysqld pour prendre en compte ce changement.

Voici l’option de configuration à rajouter :

[mysqld]
#
# * Fine Tuning
#
innodb_file_per_table   = 1

Vous pouvez également écrire tout simplement « innodb_file_per_table », ceci fonctionnera. Redémarrez ensuite le service MySQL :

sudo service mysql restart

Connectez-vous en ligne de commande à votre MySQL et retapez la commande donnée ci-dessus, vous devriez avoir du nouveau :

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Bien ! Notre modification est donc bien prise en compte ! Si je crée une nouvelle base de données et une nouvelle table :

mysql> create database lolcats; use lolcats;
Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table cat(id tinyint unsigned primary key, name varchar(20), age tinyint unsigned);
Query OK, 0 rows affected (0.04 sec)

Je retrouve bien tout ça dans mon répertoire MySQL :

sebastien.ferrandez@sebastien$ sudo ls -l /var/lib/mysql/lolcats/
total 116
-rw-rw---- 1 mysql mysql  8614 mai   21 10:44 cat.frm
-rw-rw---- 1 mysql mysql 98304 mai   21 10:44 cat.ibd
-rw-rw---- 1 mysql mysql    65 mai   21 10:43 db.opt

C’est bien le signe que le découpage « un fichier par table » est désormais en place !

Bénéficier du découpage avec une base de données déjà implantée

Si vous avez déjà une base de données et que vous vous apercevez qu’ibdata1 a grossi et qu’il est temps de passer à un fichier par table, procédez comme suit :

Créez un répertoire s’apprêtant à recueillir votre sauvegarde :

sebastien.ferrandez@sebastien:$ mkdir -p $HOME/mysql/backups/avant_filepertable

Faites une sauvegarde de l’intégralité de vos données :

sebastien.ferrandez@sebastien:$ mysqldump -u root -p --all-databases > $HOME/mysql/backups/avant_filepertable/all_databases.sql

Arrêtez le démon MySQL :

sebastien.ferrandez@sebastien:~$ sudo service mysql stop
[ ok ] Stopping MySQL database server: mysqld.

Naturellement, rajoutez votre option dans my.cnf :

[mysqld]
#
# * Fine tuning
#
innodb_file_per_table

Redémarrez MySQL :

sebastien.ferrandez@sebastien:~$ sudo service mysql start
[ ok ] Starting MySQL database server: mysqld ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

A l’issue de ce redémarrage, pensez à valider la bonne prise en compte de cette option à l’aide du SHOW VARIABLES montré un peu plus haut dans ce billet.
Supprimez vos fichiers (à vous de voir si vous souhaitez conserver les logs) :

sebastien.ferrandez@sebastien:$ sudo rm -fr /var/lib/mysql/*

Installez de nouvelles tables système. Attention, les messages que j’ai mis en gras nécessitent votre attention !

sebastien.ferrandez@sebastien:$ sudo /usr/bin/mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h sebastien password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

Je vous conseille de passer par :

sudo /usr/bin/mysql_secure_installation

Voici les options que j’ai personnellement choisi :

Change the root password? [Y/n] n
 ... skipping.

Remove anonymous users? [Y/n] n
 ... skipping.
Disallow root login remotely? [Y/n]
 ... Success!

Remove test database and access to it? [Y/n]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n]

Ré-injectez vos données sauvegardées dans votre base qui maintenant sait découper par table :

sebastien.ferrandez@sebastien:~$ mysql -u root -p < $HOME/mysql/backups/avant_filepertable/all_databases.sql

Et validez que les tables que vous avez recrée se trouvent bien dans les répertoires correspondant à vos bases de données :

sebastien.ferrandez@sebastien:~$ sudo ls -l /var/lib/mysql/lolcats
total 116
-rw-rw---- 1 mysql mysql    65 mai   22 09:31 db.opt
-rw-rw---- 1 mysql mysql  8556 mai   22 10:02 cat.frm
-rw-rw---- 1 mysql mysql 98304 mai   22 10:02 cat.ibd

La table cat que j’ai crée se retrouve bien dans le répertoire correspondant à la base de données lolcats dont elle fait partie, l’opération est un succès !

Kit de premiers secours

Problèmes pouvant survenir au shutdown du démon MySQL

Si jamais vous n’arrivez pas à stopper MySQL, il s’agit sans doute d’un problème lié à l’utilisateur ‘debian-sys-maint’ du à la suppression des tables.
Voici comment le résoudre :

Récupérez d’abord le mot de passe courant de cet utilisateur (c’est une installation locale, je vous donne le mot de passe sans crainte) :

sebastien.ferrandez@sebastien:$ sudo grep password /etc/mysql/debian.cnf
password = JZe6ZMa9bMK4aqfm

Ensuite mettez-lui les bons privilèges dans votre ligne de commande MySQL :

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'JZe6ZMa9bMK4aqfm' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Changer le mot de passe root

Pour mettre p@$sW0rd! comme mot de passe à l’utilisateur root

mysqladmin -u root password p@$sW0rd!

Pour aller plus loin…

http://dev.mysql.com/doc/refman/5.0/fr/innodb-configuration.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html (anglais)

5 réflexions sur « MySQL et InnoDB : activer les fichiers au niveau table avec innodb_file_per_table »

  1. gorus

    Bonjour,

    Un grand merci pour ce tuto très clair. Ca m’a bien aidé car mon fichier ibdata faisait plusieurs Go et empêchait les serveurs de fonctionner.
    Je rencontre par contre un bug:
    Quand je redémarre mysql, il semble ignorer la directive innodb_file_per_table et me recréé un fichier ibdata (et ca devient le bordel au niveau des tables)
    Est-ce normal ? comment éviter ça ?

    Merci

    Répondre
    1. admin Auteur de l’article

      Merci gorus de votre commentaire. La présence d’ibdata1 après un passage en « 1 table per file » et redémarrage n’est pas une anomalie en soit. Normalement, elle ne contient plus que des métadonnées et non plus des indexes ou des données de vos tables. Jetez un œil à sa taille et vérifiez qu’il ne grossisse pas anormalement avec le temps !

      Répondre
  2. gorus

    Ok, c’est normal, c’est déjà rassurant.

    Mais ce n’est pas tant la présence d’ibdata1 qui me dérange.
    Après redémarrage de mysql, certaines tables (en l’occurence Amavis) ne sont plus vues dans phpmyadmin, malgré la présence des fichiers .frm et .ibd dans le dossier /var/lib/mysql/amavis.
    Par contre, je peux encore les voir en passant par le serveur (mysql -p (..etc..) show tables), mais je ne peux pas faire de select. => Table ‘amavis.maddr’ doesn’t exist
    Et surtout le service ne fonctionne plus.
    Avez-vous une idée ?

    Répondre
  3. gorus

    Le problème est réglé, en redémarrant mysql avant l’import de la base.
    Merci encore pour le tuto ;o)

    Répondre

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.