Déjà en 2005, je gueulais à qui voulait l'entendre que InnoDB à un bug sérieux sur la taille de son fichier principal. En 2014, ce bug ne semble toujours pas corrigé. Pourtant, il peut bloquer des systèmes entiers. L'explication est simple, le fichier qui stocke vos bases de données relationnelles, ne fait que grossir. Grossir et grossir encore sans jamais redonner l’espace disque alloué dont il ne sert plus. Si vous effacez une database (innodb), la place prise sur le disque par le fichier ibdata n'est pas redonnée. Pire cette place n'est même pas réutilisée par les autres database. D'origine ce fichier est dans le répertoire /var/lib/mysql. Dans la plupart des configurations Linux, ce répertoire est sur la partition principale. Dès lors, vous risquez l'asphyxie de la partition et le blocage de votre linux (surtout ne pas redémarrer).
Il y a tout de même des choses positives. Après test, lorsque je supprime toutes les tables d’une base (oui soyons fou), certes l’espace disque n’est pas redonné, mais lorsque je remonte le dump, l’ancienne place prise par les bases est reprise.
Bref, si un jour vous avez une erreur de ce type SQLSTATE[42S02]: Base table or view not found
Entrez vite un df -h dans un terminal
jbaptiste@Ubuntu-v2:~/www/drupal/master.longchamp.com/drupal/sites/master$ df
/dev/sda1 18415356 17456840 4 100%
/none 4 0 4 0% /sys/fs/cgroup
udev 2013816 12 2013804 1% /dev
tmpfs 404796 932 403864 1% /run
none 5120 0 5120 0% /run/lock
none 2023976 1764 2022212 1% /run/shm
none 102400 28 102372 1% /run/user
/dev/sdc1 15729120 7289524 7617556 49% /mnt/mandriva
/dev/sdb1 30830592 24991028 4250420 86% /space2
/dev/sda5 102272436 72630176 24424224 75% /home
/dev/sdc6 21263412 18904032 2342996 89% /mnt/jbaptiste
Si à l'issue de cette commande vous avez un 100% sur /dev/sda1 là c'est cuit. Il y a de fortes chances que votre partition principale (celle qui contient le noyau et plein de trucs rigolos) est pleine. Il va falloir agir
Cette méthode a été testée sur une Ubuntu 13.10
Se donner un peu d’espace pour travailler
Attention la procédure est à appliquer à la lettre, une erreur de copie et ça peut vite être le drame. Pas de quoi stresser non plus, on prend des précautions.
Pour faire un peu d'espace sur le disque, j'ai effacé les log avec
sudo rm /var/log/*.gz
Mais cela n’a pas été suffisant, j’ai donc éffacer ce qu’il y avait dans /tmp
Mais cela n’a pas été suffisant, alors j’ai effacé avec ubuntu-tweak que j’avais sur ma machine les vieux noyau du système. Dans le répertoire /opt il y a aussi des trucs à faire. Cela m’a permis de récupérer 200 Mo et de travailler sereinement.
Arrêter le mysql
sudo service mysql stop
Passer en root avec sudo su ou sudo -s (Attention lorsqu’on passe dans ce mode on fait vite des bêtises, du style faire un chmod 600 sur son home …)
Sinon ajoutez des sudo devant les commandes
Créer un répertoire mysql (sur une partition pas trop pleine (pour moi la /home)
mkdir /home/mysql
la donner au user Mysql
chown mysql:mysql /home/mysql
copier tout le contenu du répertoire /var/lib/mysql dans /home/mysql avec la commande suivante :
cp -pr /var/lib/mysql/* /home/mysql
ou
rsync -rp /var/lib/mysql/* /home/mysql
Le -p est très important car il préserve les droits et les permissions pour chaque fichier. Cela vous évitera de le faire à la main.
Mais tout n’est pas fini, loin de là. Maintenant, il faut créer un petit lien symbolique pour ne pas perturber la configuration d’Ubuntu.
On va d’abord bouger le répertoire d’origine, histoire de ne pas perdre les bases (on ne sait jamais)
mv /var/lib/mysql/ /var/lib/mysqls
Création du lien et modification du propriétaire
ln -s /home/mysql/ /var/lib/mysql chown -h mysql:mysql /var/lib/mysql
Mais ce n’est pas tout, si votre version d’Ubuntu utilise apparmor qui sert à contrôler les droits sur les applications, il va falloir modifier sa configuration.
Le fichier ce trouve ici (pour 13.10) /etc/apparmor.d/usr.sbin.mysqld
Remplacer les lignes (le # indique un commentaire)
# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
par
/home/mysql/ r,
/home/mysql/** rwk,
Modifier le fichier de configuration de mysql /etc/mysql/my.cnf (faire une copie avant)
cd /etc/mysql/ cp my.cnf my.cnf.jba
vi my.cnf
Ajouter la ligne suivante dans la section [mysqld] :
#datadir = /var/lib/mysql
datadir = /home/mysql
Recharger la configuration apparmor
service apparmor reload
Enfin redémarrer mysql
service mysql start
blague possible
start: Job failed to start
Repassez tous les étapes ci-dessus. Si vraiment cela ne fonctionne pas. Tout refaire en arrière.
Récupérer de l’espace
Maintenant que l'on a sécurisé notre Linux occupons-nous de cette saloperie d'InnoDB. Je dis saloperie, car je ne l'aime pas et je ne l'ai jamais aimé. Cela n'appartient qu'à moi et InnoDB doit bien avoir des qualités sinon il aurait été supprimé depuis longtemps. Il est temps de récupérer de l'espace. Le paradoxe est qu'il va falloir en perdre encore un peu pour pouvoir en récupérer
Voici la méthode que l’on trouve en général
- Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
- Drop all databases except the above 2 databases
- Stop mysql
- Delete ibdata1 and ib_log files
- Start mysql
- Restore from dump
En français ça donne quoi
Se connecteur au serveur mysql
Lister les tables utilisées par innodb
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';
1. utiliser le script suivant (inspiré ce celui-ci ici ) le placer dans un fichier mysqldump.sh (par exemple)
#!/bin/bash
MYUSERNAME=root
MYPASSWORD=
MYHOST=127.0.0.1
MYSQL=$(mysql -N -u root -pEnversx2 -h127.0.0.1 <<<"SELECT DISTINCT (CONCAT( table_schema, ' ' )) FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" )
mysqldump -v -u${MYUSERNAME} -p${MYPASSWORD} -h${MYHOST} --databases ${MYSQL} > DB-DUMP.sql
Ensuite taper bash mysqldump.sh
Ensuite, je vous conseille de faire
mysql -N -u root -pmotdepasse -h 127.0.0.1 <<< "SELECT DISTINCT(CONCAT(table_schema,' ')) FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';"
Pour voir quelles sont les tables que vous devez effacer (NE JAMAIS EFFACER LES DATABASES mysql et performance_schema databases, Sinon là c’est mort !!!
Dans mon cas et pour information le dump non compressé pèse 1,2Go alors que le fichiers InnoDB pèse 5 Go, en clair il y a 3.8 Go utilisé pour le “relationnelle” de base relationnelle et des index !!!
2) Effacer les tables avec DROP DATABASE database, où vous remplacer “database” la les noms listés avec la commande précédente (on pourrait faire un script, mais là je suis naze)
3) sudo service mysql stop
4) sudo rm /home/mysql/ibdata1
sudo rm /home/mysql/ib_log*
5) sudo service mysql start
6) mysql -uroot -pEnversx2 -h127.0.0.1 < DB-DUMP.sql
Lors de la première insertion, j'ai gagné 100 Mo, c'est-à-dire rien du tout. Il est donc conseillé de faire le ménage avant de faire le DUMP.
Je vous conseille d’ajouter aussi les lignes suivantes pour optimiser la place dans la future configuration. Cela aura pour effet de faire un fichier par table (ce qui n’est pas idéal, notamment avec Drupal 7)
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
Conclusion
On peut se demander si c'est un bug, ou un oubli volontaire. Il semble moins gênant qu'en 2005. Cela dit, son effet est toujours aussi dévastateur. Il ne faut donc pas perdre de vue qu'avec les bases de données relationnelles la place prise sur le disque est 3 fois plus importante que son dump (C'est une estimation à la grosse louche ;) ). Je n'aime toujours pas InnoDB, car je ne contrôle pas vraiment l'espace disque utilisé. Les outils donnés pour réduire la taille ne donnent pas entièrement satisfaction. Bref, il faut vraiment se poser la question ai-je besoin d'une base de données relationnelle avant d'utiliser InnoDB.
Comments powered by CComment