Tip MySQL: "innodb_file_per_table" ou comment utiliser des fichiers de table séparés pour éviter le manque d’espace disque!!

Bon,
je suis pas très fier de ce que j’ai fait mais je vous relate mes erreurs et comment je suis arrivé à les résoudre pour vous éviter de faire pareil…

Situation

J’installe Mysql sur mon nouveau serveur dédié, monstre de calcul (8 cpu « machin » bridge) et d’espace disque ( 1To). Je configure ma base de données au format  Innodb ( le format qui permet les contraintes externes, etc.); je configure les capacités de mémoire, l’espace disque temporaire;
bref, je crois avoir tout passé en revue…

Puis j’alimente ma base avec des données provenant de réseaux sociaux – autant dire que la volumétrie est importante.

Out of space

Au bout de quelques semaines, je m’aperçoit que le fichier ibdata qui est dans le répertoire de données grossit mais ne rétrécit jamais , y compris lorsque je supprime des tables de plusieurs centaines de GigaOctets.
Finalement j’atteins la taille limite de mon disque et là je suis mal surtout que je dois produire des rapports d’analyse pour avant hier.

L’option de la mort qui tue

C’est là  que je m’aperçoit que par défaut à  l’installation,  MySQL et configuré pour gérer toutes les tables et leurs index dans un seul et même fichier –  le fameux « ibdata » et que ce fichier  est voué a grossir –  jamais à  réduire !
Il aurait fallu utiliser l’option « innodb_file_per_table » dans le fichier de configuration (my.cnf) pour que chaque table utilise son propre fichier de stockage des données et d’index.
Ok, je l’ai pas fait; mais alors comment faire pour rattraper ma bourde ?

Play it like a barbarian

Oui, je dirais qu’il faut y aller « à  la barbare » c’est à  dire faire une réinstallation des bases!
Première chose, faire une sauvegarde complète des bases : 

mysqldump -R -q -p --all-databases > 20120402_mysql_all.sql

Ensuite, arrêter MySQL et supprimer tous les fichiers du répertoire des bases de données( moi, je fais un move sur un support de sauvegarde):

service mysql stop
mv  /home/mysql/* /home/backup/mysql_old_dir

Redémarrer Mysql :

service mysql start

Enfin, restaurer les tables :

mysql -u root  < 20120402_mysql_all.sql

Je vous cache pas qu’il faut aimer avoir des sueurs froides dans le dos pendant quelques longues minutes.

N’hésitez pas à  commenter ce post si vous avez été dans le même cas ! 

2 réflexions au sujet de « Tip MySQL: "innodb_file_per_table" ou comment utiliser des fichiers de table séparés pour éviter le manque d’espace disque!! »

  1. Salut, j’aurais une petite question:
    Sais-tu comment sur une base MySQL InnoDB, je peut faire en sorte qu’à la création des table et des insertions il n’y est pas 1 fichier.frm par table mais un seul fichier pour toute les stables confondues?

    • Bonjour Thomas,
      merci pour ton commentaire.
      Il suffit de désactiver l’option innodb_file_per_table.
      Pour cela tu dois aller dans ton fichier my.cnf et modifier dans la section mysqld
      [mysqld]
      innodb_file_per_table=0

      Tu n’as plus qu’à redémarrer.
      Ensuite, si tu as des tables déjà existantes , tu dois les déplacer dans le nouvel espace; pour cela , tu dois altérer la table.
      exemple :
      ALTER TABLE table_name ENGINE=InnoDB;
      voici le
      lien vers la documentation officielle

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.