Question Comment réduire / purger le fichier ibdata1 dans MySQL


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

Cela fonctionne 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.

J'utilise plus ou moins de paramètres MySQL par défaut pour l'installation, y at-il un moyen pour que je puisse automatiquement rétrécir / purger le fichier ibdata1 après une période de temps déterminée?


500
2017-08-11 07:20


origine


Réponses:


Cette ibdata1 ne rétrécit pas est une caractéristique particulièrement gênante de MySQL. le ibdata1 fichier ne peut pas réellement être réduit à moins que vous supprimiez toutes les bases de données, supprimez les fichiers et rechargez un vidage.

Mais vous pouvez configurer MySQL pour que chaque table, y compris ses index, soit stockée dans un fichier distinct. De cette façon ibdata1 ne grandira 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'était il y a un moment que je l'ai fait. Toutefois, pour configurer votre serveur afin qu'il utilise des fichiers distincts pour chaque table, vous devez modifier my.cnf afin de permettre cela:

[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. Fait une mysqldump de toutes les bases de données, procédures, déclencheurs, etc. sauf le mysql et performance_schema des bases
  2. Supprimer toutes les bases de données sauf les 2 bases de données ci-dessus
  3. Arrêtez mysql
  4. Effacer ibdata1 et ib_log des dossiers
  5. Démarrer mysql
  6. Restaurer à partir de la décharge

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

Maintenant vous êtes prêt à partir. Lorsque vous créez une nouvelle base de données pour analyse, les tables seront situées dans des ibd* fichiers, pas dans ibdata1. Comme vous déposez généralement la base de données peu après, le ibd* les fichiers seront supprimés.

http://dev.mysql.com/doc/refman/5.1/fr/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 indexer des pages depuis ibdata1 vers des fichiers séparés. Cependant, ibdata1 ne rétrécira que si vous suivez les étapes ci-dessus.

En ce qui concerne la information_schema, ce n'est pas nécessaire ni possible de laisser tomber. Il ne s'agit en fait que d'un ensemble de vues en lecture seule, pas de tables. Et il n'y a pas de fichiers associés à eux, pas même un répertoire de base de données. le informations_schema utilise la mémoire db-engine et est abandonné 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.


718
2017-08-11 09:07



Lorsque vous supprimez des tables innodb, MySQL ne libère pas l'espace dans le fichier ibdata, c'est pourquoi il ne cesse de croître. Ces fichiers ne rétrécissent presque jamais.

Comment réduire un fichier ibdata existant:

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

Vous pouvez écrire ceci et planifier l'exécution du script après une période de temps déterminée, mais pour l'installation décrite ci-dessus, il semble que plusieurs tablespaces soient une solution plus simple.

Si vous utilisez l'option de configuration innodb_file_per_table, vous créez plusieurs espaces de table. Autrement dit, MySQL crée des fichiers séparés pour chaque table au lieu d'un fichier partagé. Ces fichiers séparés sont 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 le besoin de réduire / purger les fichiers ibdata dans votre cas.

Plus d'informations sur plusieurs espaces de table:

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


32
2017-08-11 08:31



Ajouter à La réponse de John P,

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 supprime tout autre ib_logfile pouvant être nommé 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.


29
2017-10-31 07:11



Si vous utilisez le moteur de stockage InnoDB pour (certaines de) vos tables MySQL, vous avez probablement déjà rencontré un problème avec sa configuration par défaut. Comme vous avez pu le remarquer 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 gros. Par défaut, ce fichier a une taille initiale de 10 Mo et s'étend automatiquement. Malheureusement, les fichiers de données InnoDB ne peuvent pas être rétrécis. C'est la raison pour laquelle DELETE, TRUNCATE, DROP, etc. ne récupère pas l'espace utilisé par le fichier.

Je pense que vous pouvez trouver une bonne explication et une solution là-bas:

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


13
2018-04-24 06:32



Si votre objectif est de surveiller l'espace libre de MySQL et que vous ne pouvez pas arrêter MySQL de réduire votre fichier ibdata, passez-le à travers les commandes d'état de la table. Exemple:

MySQL> 5.1.24:

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

MySQL <5.1.24:

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

Comparez ensuite cette valeur à votre fichier ibdata:

du -b ibdata1

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


5
2018-01-26 15:03



Dans une nouvelle version de mysql-serveur recettes ci-dessus va écraser "mysql" base de données. Dans l'ancienne version cela fonctionne. Dans new certaines tables basculent vers le type de table INNODB, et ainsi vous les endommagerez. Le plus simple est de vider toutes vos bases de données, désinstaller mysql-server, ajouter à resté my.cnf:

[mysqld]
innodb_file_per_table=1


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

1
2017-08-07 13:38



Rapidement scripté la procédure de la 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 courir comme root.

Exclut mysql, information_schema, performance_schema (et binlog annuaire).

Suppose que vous avez des droits d'administrateur dans /root/.my.cnf et que votre base de données vit par défaut /var/lib/mysql annuaire.

Vous pouvez également purger les journaux binaires après l'exécution de ce script pour récupérer plus d'espace disque avec:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

0
2017-07-26 16:55