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

J'utilise MySQL en localhost comme un "outil de requête" pour effectuer des statistiques en R, c'est-à-dire chaque fois que j'exécute un script R, je crée une nouvelle base de données (a), je crée une nouvelle table (B), j'importe les données en B, je soumets une requête pour obtenir ce dont j'ai besoin, et puis je laisse tomber B et laisse tomber A.

cela fonctionne très bien pour moi, mais je me rends compte que la taille du fichier ibdata augmente rapidement, je n'ai rien stocké dans MySQL, mais le fichier ibdata1 dépassait déjà 100 Mo.

je suis en utilisant plus ou moins le paramètre MySQL par défaut pour la configuration, y a-t-il un moyen pour que je puisse automatiquement rétrécir/purger le fichier ibdata1 après une période de temps fixe?

510
demandé sur Bill Karwin 2010-08-11 11:20:29
la source

8 ответов

que ibdata1 ne rétrécit pas est une caractéristique particulièrement gênante de MySQL. Le fichier ibdata1 ne peut être rétréci que si vous supprimez toutes les bases de données, supprimez les fichiers et rechargez un dump.

mais vous pouvez configurer MySQL de sorte que chaque table, y compris ses index, soit stockée dans un fichier séparé. De cette façon, ibdata1 ne se développera pas aussi grand. Selon le commentaire de Bill Karwin ceci est activé par défaut à partir de la version 5.6.6 de MySQL.

c'est il y a un moment que j'ai fait ça. Cependant, pour configurer votre serveur pour utiliser des fichiers séparés pour chaque table, vous devez changer my.cnf pour activer ceci:

[mysqld]
innodb_file_per_table=1

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

comme vous voulez récupérer l'espace de ibdata1 vous devez en fait supprimer le fichier:

  1. Faire un mysqldump de toutes les bases de données, des procédures, des déclencheurs etc à l'exception de la mysql et performance_schema bases de données
  2. Drop toutes les bases de données à l'exception de la au-dessus de 2 bases de données
  3. Stop mysql
  4. Supprimer ibdata1 et ib_log fichiers
  5. Démarrer mysql
  6. Restaurer à partir d'une dump

lorsque vous démarrez MySQL à l'étape 5, les fichiers ibdata1 et ib_log seront recréés.

maintenant tu es en état de partir. Lorsque vous créez une nouvelle base de données pour l'analyse, les tables seront situées dans des fichiers séparés ibd* , et non dans ibdata1 . Comme vous déposez habituellement la base de données peu après, les fichiers ibd* seront supprimés.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

vous avez probablement vu ceci:

http://bugs.mysql.com/bug.php?id=1341

en utilisant la commande ALTER TABLE <tablename> ENGINE=innodb ou OPTIMIZE TABLE <tablename> on peut extraire des données et des pages d'index de ibdata1 pour séparer des fichiers. Cependant, l'ibdata1 ne rétrécira pas à moins que vous ne fassiez les étapes ci-dessus.

en ce qui concerne le information_schema , il n'est pas nécessaire ni possible de le laisser tomber. En fait, c'est juste un tas des vues en lecture seule, pas des tableaux. Et il n'y a aucun fichier associé avec eux, pas même un répertoire de base de données. Le informations_schema utilise le moteur mémoire db et est lâché et régénéré lors de l'arrêt/redémarrage de mysqld. Voir https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

727
répondu John P 2017-05-23 15:10:32
la source

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

33
répondu titanoboa 2015-10-21 12:06:40
la source

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.

32
répondu Vinay Vemula 2018-07-05 19:52:05
la source

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 /

14
répondu Vik 2014-10-03 13:38:09
la source

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

6
répondu Cyno 2012-01-26 19:03:19
la source

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
4
répondu adjustable_wrench 2017-08-07 16:38:33
la source

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;
3
répondu Pierre-Alexis de Solminihac 2018-07-26 19:55:56
la source

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.

-1
répondu steveayre 2011-11-21 21:57:33
la source

Autres questions sur mysql database innodb