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.
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)