Astuces: Restaurer un MySql Dump Volumineux

Rien de plus énervant que de devoir attendre qu’une restauration d’un fichier dump se termine. Et plus le fichier est volumineux, plus c’est long.

Si vous n’avez pas utilisé un outil comme MyDumper, voici des petite astuces qui vous feront gagner du temps:

Configurer MySql

La base de l’optimisation est de modifier max_allowed_packet dans le fichier my.cnf au niveau de mysqldump. Cette variable correspond à la taille totale d’une chaîne de requête.
N’hésitez pas à mettre une taille importante (512M)

[mysqldump]
quick
quote-names
max_allowed_packet      = 512M

Désactiver les clés

Une optimisation très nette lorsqu’on utilise le moteur MyISAM. Permet d’attendre que toutes les données soient dans la table avant de recalculer les indexes. Il suffit de rajouter après la création de la table:

ALTER TABLE `table` DISABLE KEYS;

Une fois la restauration terminée, ne pas oublier de les réactiver:

ALTER TABLE `table` ENABLE KEYS;

Découper votre fichier dump.mysql

Découper votre fichier dump.sql en plusieurs. Pourquoi? Cela facilitera la lecture à Mysql. Il vaut mieux lui donner un petit fichier à traiter qu’un gros: le temps de lecture d’un tel fichier est trop long.
Voici la méthode:

Split d’un fichier dump.mysql

Allez dans le répertoire de votre fichier dump

cd /path/to/backup/

Créer un répertoire splits

mkdir splits

Faites un split:

split -l200 dump.sql splits/sql_

sql_ : préfixe des splits (sql_aa, sql_ab etc…)
200: correspond au nombre de lignes par fichier. Attention, nous sommes limité à 677 noms de fichier. Il faut donc calculer un nombre suffisant pour pouvoir avoir toutes les lignes de notre fichier dump, sous peine d’avoir le message d’erreur suivant:

split: output file suffixes exhausted

Restaurer les fichiers dump_split.sql

Cela se fait simplement en une ligne de commande:

cd splits && cat sql_* | mysql -u root -p database_name

Mysql va prendre dans l’ordre alphabétique tous les fichiers (pas d’inquiétude à avoir sur les premières lignes du fichier mysql contenant les informations des tables, il va bien en tenir compte).

Avec ces méthodes, vous allez gagné plusieurs heures sur un fichier avec des millions d’enregistrements.

Bon courage!