Comment compresser ou restreindre le fichier de données MySQL ibdata1 ?

Je recommande de stocker les tables InnoDB dans des fichiers séparés, un fichier par table. Il est ainsi plus facile de les gérer et de voir quelles tables prennent le plus de place. Mettez les éléments suivants dans /etc/mysql/my.cnf (ou à l'endroit où il se trouve sur votre système) [mysqld] section :

innodb_file_per_table

Il est ensuite facile d'identifier les tables qui utilisent trop d'espace. Vous devez ensuite recréer les tables problématiques périodiquement (une fois par jour, une fois par mois, etc.) en procédant comme suit :

ALTER TABLE tablename ENGINE=InnoDB;

Cela prendra un certain temps pour les grandes tables. Il recréera les fichiers de table à partir de zéro, ce qui permettra de se débarrasser de la fragmentation et des trous qui augmentent la taille des fichiers. Lorsque ALTER TABLE est en cours d'exécution, il utilise le double de la capacité de disque nécessaire. Je ne suis pas sûr que vous puissiez faire cela à moins que vous ne fonctionniez en mode "fichier par table".

UPDATE

Je voulais ajouter que pour se débarrasser du "fichier unique pour toutes les tables" gonflé existant InnoDB ibdata1 vous devez procéder comme suit :

1. exporter toutes les tables InnoDB avec mysqldump
2. shutdown mysqld
3. ajoutez la ligne "innodb_file_per_table" dans votre my.cnf
4. rm ibdata* ib_logfile* dans votre répertoire de données mysql
5. démarrer mysqld
6. importer les données que vous avez sauvegardées à l'étape 1

Pour activer la compression sur des tables spécifiques, vous devez

• fixer innodb_file_per_table a ON
• fixer innodb_file_format a Barracuda
• redémarrer MySQL et recréer le tablespace.
• modifier la définition de la table en utilisant ALTER TABLE name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Barracuda Le format de ligne est pris en charge par MySQL 5.5 o MySQL 5.1 avec le plugin InnoDB .

Il y a une raison spécifique pour laquelle la compression de ibdata1 est très importante.

Que contient ibdata1 ? Quatre choses :

• Données du tableau
• Index des tableaux
• Métadonnées du tableau
• Données MVCC (Multiversioning Concurrency Control)

Vous allez devoir faire deux choses :

• Nettoyez ibdata1 en y séparant les données et les index (j'ai répondu à ce même type de question dans 29 octobre 2010 y 4 février 2011 ). C'est similaire à la réponse de @snap, mais puisque @snap a mentionné cette méthode en premier lieu dans cette question Il devrait recevoir des félicitations (acceptation de sa réponse) pour cela.
• Enlevez ce plafond de 32G de innodb_data_file_path. Il faut lire 'ibdata1:10M:autoextend' (qui est de toute façon la valeur par défaut).

CAVEAT

La raison pour laquelle innodb_data_file_path devrait être la valeur par défaut est liée à ce qui est stocké dans ibdata1. Même si vous supprimez toutes les données et les index comme spécifié par @snap, les métadonnées de la table et les données MVCC sont toujours écrites dans ibdata1. Les métadonnées de table peuvent toujours faire grossir ibdata1 s'il y a un volume très élevé de commandes DDL (CREATE TABLEs, DROP TABLEs, ALTER TABLEs, etc.). Les données MVCC peuvent encore faire grossir ibdata1 s'il y a un volume élevé de transactions qui créent des instantanés de grandes quantités de données par transaction. Puisque c'est le cas avec ibdata1, il vaut mieux qu'il soit le plus léger possible. Par conséquent, laissez innodb_data_file_path=ibdata1:10M:autoextend dans /etc/my.cnf

BTW : +1 de ma part pour @snap ! !!

Il s'agit d'un excellent guide publié ici pour une explication et une résolution approfondies :

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261