création de site internet

flus RSS du blogsur Facebooksur Twitter

30
10

Export/import de tables MySQL

dans MySQL

Si vous avez déjà eu à migrer une base de données MySQL d'un serveur à un autre, vous devez savoir à quel point cette opération est fastidieuse. Pour peu que le volume de données soit important, vous vous heurtez à de nombreuses contraintes. Outre le temps à passer, des problématiques telles que le poids des fichiers, les erreurs MySQL, les interruptions impromptues, ... sont autant de difficultés qui rendent la tâche compliquée.

J'ai personnellement eu à expérimenter cette aventure, avec une difficulté supplémentaire, migrer les données d'un serveur MySQL, vers un serveur MySQL Cluster beaucoup plus exigeant et contraignant en terme de rigueur et d'exploitation. De cet arrachage de cheveux, je retiens les 2 méthodes suivantes qui m'ont permis de me sortir de bien des complications.

A noter que ces méthodes ont fait leurs preuves pour exporter une table MySQL et importer une table, mais elles peuvent également s'appliquer pour exporter / importer une base MySQL complète, ou simplement pour sauvegarder une table ou faire la sauvegarde d'une base MySQL.

MySQL Dump

Mysqldump est un utilitaire MySQL qui permet d'exporter les données d'une table ou d'une base MySQL vers un fichier texte. Les données, ici, ne sont pas sauvegardées sous un format brut, mais sous forme de requêtes MySQL qui permettront de les reconstruire ailleurs.

La syntaxe pour exporter une table est la suivante (il est bien sûr possible d'exporter toute une base, cf. explications sur le site officiel) :

$> mysqldump [... options ...] nom_de_la_base nom_de_la_table

Mysqldump propose de nombreuses options qui permettent de paramétrer l'export et configurer les requêtes MySQL de récupération des données. Voici celles qui me semblent intéressantes, les autres se trouvent sur le site officiel :

-h (ou --host) :
précise l'hôte du serveur MySQL (par défaut : localhost).

-h localhost

-u (ou --user) :
indique l'utilisateur qui va se connecter au serveur MySQL pour effectuer le dump.

-u nom_d_utilisateur

-p (mais préférez --password) :
indique le mot de passe de l'utilisateur ci-dessus.

--password='mot_de_passe_de_l_utilisateur'

NB : utilisez --password et entourez le mot de passe avec des ', sinon vous risquez d'avoir des problèmes avec la commande qui refuse de s'exécuter (problème de caractères protégés)

-r (ou --result-file) :
indique le chemin du fichier dans lequel seront exportées les données.

-r/var/www/le/chemin/absolu/de/mon/fichier/dump.sql

NB : il n'y a pas d'espace entre -r et le chemin du fichier.
NB : le chemin du fichier doit être absolu.
NB : si le fichier n'existe pas il est créé. Vérifiez bien les droits du fichier et du dossier.
NB : l'extension du fichier n'a en soit pas d'importance. Un fichier .sql permet simplement d'indiquer que le fichier contient des requêtes MySQL.

--where (ou -w)
spécifie une condition qui sera appelée dans le WHERE de la requête MySQL d'export des données.

--where="id > 100000"

NB : entourez la condition avec des "

--no-create-info (ou -t)
précise de ne pas intégrer les requêtes de création de tables (CREATE TABLE) dans le fichier dump (dans le cas où la table existerait déjà).

--no-create-info

--skip-extended-insert
précise d'utiliser la syntaxe ligne par ligne pour les INSERT (INSERT INTO ... VALUES (...); INSERT INTO ... VALUES (...); ...) plutôt que la syntaxe multiligne (INSERT INTO ... VALUES (...),(...);). J'ai eu besoin de cette commande pour ma migration vers le serveur MySQL Cluster qui avait du mal à encaisser les INSERT mutlilignes.

--skip-extended-insert

--order-by-primary
précise que les données doivent être ordonnées de manière croissante sur la clé primaire. L'intérêt de cette option et que en cas d'interruption du dump, on peut facilement déterminer quelle est la dernière donnée exportée et ne recommencer le dump qu'à partir de cette donnée (à l'aide de l'option --where), sans avoir à reprendre celles traitées avant.

--order-by-primary

Voici donc un exemple complet de commande mysqldump :

$> mysqldump --no-create-info --skip-extended-insert --order-by-primary --where="id > 100000" -h localhost -u nom_d_utilisateur --password='mot_de_passe_de_l_utilisateur' -r/var/www/le/chemin/absolu/de/mon/fichier/dump.sql nom_de_la_base nom_de_la_table

Ensuite l'intégration dans la nouvelle base de données MySQL se fait simplement. Le fichier généré étant constitué de requêtes MySQL, il suffit simplement de l'injecter dans la base de données à l'aide du pipe < :

$> mysql -h hote_du_serveur_mysql_de_destination -u nom_d_utilisateur --password='mot_de_passe_de_l_utilisateur' nom_de_la_base_de_destination </var/www/le/chemin/absolu/de/mon/fichier/dump.sql

Les avantages de la commande mysqldump :

  • commande native à MySQL, donc fiable et parfaitement intégrée
  • de nombreuses options permettant de faciliter et d'adapter les exports en fonction des besoins
  • manipule des requêtes MySQL, donc pas de risques sur l'intégrité des données à l'import

En revanche mysqldump présente quelques contraintes :

  • plus il y a de données, plus les fichiers générés sont lourds
  • le processus d'export/import est plus long car entièrement basé sur MySQL et donc sur ses temps de traitements et de contrôle.

LOAD DATA LOCAL INFILE

LOAD DATA LOCAL INFILE (variante de LOAD DATA INFILE) est une commande MySQL qui permet d'importer à grande vitesse des données contenus dans un fichier texte.

Au préalable, il faut donc exporter les données de la base MySQL d'origine. Nous allons voir ici une alternative à la commande mysqldump, qui consiste à utiliser le pipe > pour exporter les résultats d'une requête MySQL dans un fichier :

$> mysql -h localhost -u nom_d_utilisateur --password='mot_de_passe_de_l_utilisateur' -e "SELECT * FROM nom_de_la_base.nom_de_la_table WHERE id > 100000" > /var/www/le/chemin/absolu/de/mon/fichier/dump.txt

Il faut bien veiller à attribuer les bons droits au fichier et au dossier d'export. En l'état, le format n'étant pas spécifié, les données seront exportées ligne par ligne, les colonnes étant séparées par des tabulations.

Ensuite il suffit d'appeler la commande LOAD DATA LOCAL INFILE en passant en paramètre le fichier ainsi créé et la table de destination :

$> mysql -h localhost -u admin --password='mot_de_passe_de_l_utilisateur' -D nom_de_la_base_de_destination -e "LOAD DATA LOCAL INFILE '/var/www/le/chemin/absolu/de/mon/fichier/dump.txt' INTO TABLE nom_de_la_table_de_destination"

Là encore, le format n'étant pas spécifié, le traitement prend par défaut la tabulation comme séparateur. Aussi bien pour l'export que pour l'import, on peut spécifier les séparateurs, caractères d'échappements, retours à la ligne, à l'aide des clauses FIELDS TERMINATED BY, ESCAPED BY, LINES TERMINATED BY.

A l'aide de ces commandes on peut ainsi facilement automatiser un export / import (pour une sauvegarde MySQL par exemple) en créant en fichier batch.

#1. on créé le fichier d'export et on lui applique les bons droits
> /var/www/le/chemin/absolu/de/mon/fichier/dump.txt
chmod 666 /var/www/le/chemin/absolu/de/mon/fichier/dump.txt

#2. on exporte les données dans le fichier
mysql -h localhost -u nom_d_utilisateur --password='mot_de_passe_de_l_utilisateur' -e "SELECT * FROM nom_de_la_base.nom_de_la_table WHERE id > 100000" > /var/www/le/chemin/absolu/de/mon/fichier/dump.txt

#3. on importe les données sur le serveur de destination
mysql -h localhost -u admin --password='mot_de_passe_de_l_utilisateur' -D nom_de_la_base_de_destination -e "LOAD DATA LOCAL INFILE '/var/www/le/chemin/absolu/de/mon/fichier/dump.txt' INTO TABLE nom_de_la_table_de_destination"

#4. on supprime le fichier d'export qui ne sert plus
rm /var/www/le/chemin/absolu/de/mon/fichier/dump.txt

Les avantages de la commande LOAD DATA LOCAL INFILE sont :

  • A l'inverse de mysqldump, la commande d'export présentée ci-dessus permet de spécifier entièrement la requête MySQL d'export. Ce qui est extrêment pratique si on veut l'optimiser ou si on a des contraintes que mysqldump ne peut pas gérer (ex : la clause USE INDEX)
  • les insertions se font à grande vitesse, les temps de traitement en sont d'autant plus raccourcis.
  • accepte tout type de fichier en entrée (txt, csv, ...), donc peut s'appliquer à l'import d'un fichier plat, indépendant de l'export d'une autre base de données.

En revanche, subsistent quelques contraintes :

  • non présente sur les anciennes versions de MySQL
  • le plus souvent, non activée par l'administrateur à l'installation de MySQL. Il faut donc voir avec ce dernier pour l'activer.
  • étant basée sur des données et des fichiers dont le format n'est pas contraint, l'intégrité des données peut être altérée à l'import.

Partager :