Comment récupérer l’espace disque après suppression de bases de données InnoDB

Yop,

MySQL Logo

MySQL Logo

 

Je viens d’être confronté à un problème de taille (mouahahahaha), en supprimant des données MySQL (en InnoDB plus précisément) sur un serveur de df m’annonçait toujours le même résultat : ~1.2Gio de libre, alors que je lui ai quand même retiré plusieurs dizaines de Gio de données.

 

Après plusieurs minutes de recherches (plus ou moins fructueuse) j’ai pu rassemblé assez d’info pour envoyer du bois dans la chaudière.

En fait, ce qu’il se passe quand on créé des tables en InnoDB, c’est que le moteur garde la place « au cas où ». Sauf que bon, moi cette place j’en ai besoin :/

Voici le fichier en question:

# ls -lh /var/lib/mysql/ibdata1

-rw-rw----  1 mysql  mysql  36G  18 mai  17:01 ibdata1

Pour ceux à qui ça ne cause pas, ibdata1 est le fichier de stockage des données
InnoDB (36Gio ? oui j’ai un peu joué :D ) c’est donc lui mon ennemi ! Bon bah je pars à la chasse hein. Comme dit Mario :

Here we go !

Donc maintenant 2 moyens :

  • Soit je dump toutes les bases InnoDB, je les drop, j’éteins MySQL, je rm sa face à ibdata1, je relance MySQL et je réimporte tout (durée de l’opération : 1h)
  • Soit je passe toutes mes bases en MyISAM, je coupe MySQL, je rm sa mouille, je relance MySQL et je repasse mes bases en InnoDB (durée de l’opération : 5 minutes)

Ceux qui me connaissent savent que je prends la deuxième option parce que j’aime pas rester à regarder un écran. Donc on va se faire une opération rapide :D

Pour ceux que ça intéresse, une requête pour vérifier qu’on a bien des tables en InnoDB (même avec l’output en format SQL) :

SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

Ca donnera une sortie en « ma_database.ma_table », si vous n’avez rien qui s’affiche, c’est que vous avez juste beaucoup de données stockée en MyISAM, donc ce tuto ne vous servira à rien.

 

Maintenant, je vais vous mâcher le travail de création des requêtes ALTER pour tout passer en MyISAM:

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE=MyISAM;') FROM TABLES WHERE ENGINE = 'InnoDB';

Notez bien le résultat de cette requête ! C’est la flopée de ALTER TABLE qu’il faudra envoyer pour tout passer en MyISAM et tout remettre en InnoDB (après avoir fait un s/MyISAM/InnoDB/g sur le fichier).

Maintenant faut exécuter le bousin. Chacun son école, débrouillez vous, je vais pas vous apprendre à vous servir de MySQL quand même, si vous vous posez cette question c’est que vous êtes assez grand pour vous en sortir. Allez petit coup de main : > help; :D

 

Ensuite, tout s’est bien passé, on arrête MySQL et on supprime le fichier ibdata1 (enfin faites pas les cons hein, faites un backup d’abord). Maintenant on rallume MySQL en croisant les doigts.

 

On check que tout se passe bien, et on repasse toutes nos tables en InnoDB. Et grâce à tonton bartoua, vous êtes un winner \o/

Alors résultat ?

-rw-rw---- 1 mysql mysql  10M  18 mai  17:01 /var/lib/mysql/ibdata1

Tadaaaaaaaaaaam \o/

 

Bon maintenant on paye une binouse à tonton bartoua :D Aller s’teuplé :(

Big Up !

Une réflexion au sujet de « Comment récupérer l’espace disque après suppression de bases de données InnoDB »

  1. Et pour ne plus avoir ce genre problème à cause de l’auto extend innodb, tu peux utiliser l’option innodb_file_per_table, histoire d’avoir un tablespace par table. Ensuite tu n’as plus qu’à faire de optimize régulièrement ;)

    Dim

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Image CAPTCHA

*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>