Comment rétrécir/purger le fichier ibdata1 dans MySQL

lorsque vous supprimez les tables innodb, MySQL ne libère pas l'espace à l'intérieur du fichier ibdata, c'est pourquoi il continue de croître. Ces dossiers rétrécissent rarement.

comment rétrécir un fichier ibdata existant:

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

vous pouvez configurer ceci et programmer le script pour qu'il tourne après une période de temps fixe, mais pour la configuration décrite ci-dessus il semble que plusieurs tablespaces sont une solution plus facile.

si vous utilisez l'option de configuration innodb_file_per_table , vous créez plusieurs tablespaces. C'est-à-dire que MySQL crée des fichiers séparés pour chaque table au lieu d'un fichier partagé. Ces fichiers stockés dans le répertoire de la base de données, et ils sont supprimés lorsque vous supprimez cette base de données. Cela devrait supprimer la nécessité de rétrécir/purger les fichiers ibdata dans votre cas.

plus d'information sur multiple tablespaces:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Ajouter à John P la réponse de ,

pour un système linux, les étapes 1-6 peuvent être accomplies avec ces commandes:

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. DROP DATABASE [database_name];
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1

sudo rm /var/lib/mysql/ib_logfile (et supprimer toutes les autres ib_logfile qui peuvent être nommées ib_logfile0 , ib_logfile1 , etc...)
5. sudo /etc/init.d/mysqld start
6. create database [database_name];
7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

attention: ces instructions vous feront perdre d'autres bases de données si vous avez d'autres bases de données sur cette instance mysql. Assurez-vous que les étapes 1, 2 et 6, 7 sont modifiées pour couvrir toutes les bases de données que vous souhaitez conserver.

si vous utilisez le moteur de stockage InnoDB pour (certaines) vos tables MySQL, vous avez probablement déjà rencontré un problème avec sa configuration par défaut. Comme vous l'avez peut – être remarqué dans le répertoire de données de votre MySQL (dans Debian/Ubuntu - /var/lib/mysql) se trouve un fichier appelé "ibdata1". Il contient presque toutes les données InnoDB (ce n'est pas un journal des transactions) de L'instance MySQL et pourrait devenir assez grand. Par défaut, ce fichier a une taille initiale de 10Mb et s'étend automatiquement. Malheureusement, les fichiers de données InnoDB ne peuvent pas être rétrécis. C'est pourquoi les suppressions, les troncs, les gouttes, etc. ne sera pas récupérer l'espace utilisé par le fichier.

je pense que vous pouvez y trouver une bonne explication et une solution:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1 /

si votre but est de surveiller L'espace libre de MySQL et que vous ne pouvez pas arrêter MySQL pour rétrécir votre fichier ibdata, alors obtenez-le via les commandes d'état de la table. Exemple:

MySQL > 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print }'

MySQL < 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print }'

comparez ensuite cette valeur à votre fichier ibdata:

du -b ibdata1

Source: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

dans une nouvelle version de mysql-recettes de Serveur ci-dessus va écraser" mysql " base de données. Dans l'ancienne version, il fonctionne. Dans Nouveau certaines tables passe à la table de type INNODB, et en faisant cela vous les endommagerez. La manière la plus simple est de vider toutes vos bases de données, désinstaller mysql-server, ajouter au reste ma.cnf:

[mysqld]
innodb_file_per_table=1


erase all in /var/lib/mysql
install mysql-server
restore users and databases

a écrit rapidement la procédure de réponse acceptée dans bash:

#!/usr/bin/env bash
DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')"
mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do
    mysql -e "drop database \`$DB\`"
done && \
    /etc/init.d/mysql stop && \
    find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql && \
    rm -f alldatabases.sql

Enregistrer sous purge_binlogs.sh et exécuter en tant que root .

exclut mysql , information_schema , performance_schema (et binlog ).

suppose que vous avez des justificatifs d'administrateur dans /root/.my.cnf et que votre base de données vit dans le répertoire /var/lib/mysql par défaut.

vous pouvez également purger les journaux binaires après exécuter ce script pour regagner plus d'espace disque avec:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

comme déjà noté, vous ne pouvez pas rétrécir ibdata1 (pour ce faire, vous devez jeter et reconstruire), mais il n'y a souvent pas besoin.

à l'aide de l'option d'extension automatique (probablement le paramètre de taille le plus courant), ibdata1 permet de préallouer le stockage et de le faire croître chaque fois qu'il est presque plein. Cela rend les Écritures plus rapides car l'espace est déjà alloué.

quand vous supprimez des données il ne rétrécit pas mais l'espace à l'intérieur du fichier est marqué comme non utilisé. Maintenant, lorsque vous insérez de nouvelles données, il va la réutilisation de l'espace vide dans le fichier avant de cultiver le fichier.

donc il ne va continuer à croître que si vous avez besoin de ces données. Sauf si vous avez besoin d'espace pour une autre application, il n'y a probablement aucune raison de le rétrécir.