Importation et exportation
Les backups / sauvegardes de données sont issues d’exportations de données. Les backup permettent de prévenir tout « crash » sérieux deu systeme. Il est donc nécéssaire de faire des backups réguliers. Sauvegarder ses backups sur deux disques dont un stocké en dehors du local serveur. Il est aussi nécéssaire de vérifier le backup. On peut utiliser trois méthodes pour réaliser de l’importation/exportation de données. :
- Requêtes SQL
- Programme en ligne de commande (shell)
- Outils graphiques (MySQL Administrator)
Backups par MySQL Administrator
MySQL Administrator fourni une interface vers mysqldump afin d’effectuer des sauvegardes de la base de données. Attention : MySQL Admin ne pourra importer des backup qu’il aura réaliser lui même
Les options du backup :
No Create : aucun CREATE,
Compatibility Mode : rend le fichier compatible avec les anciennes versions de MySQL,
No Extended Insert : pas d’insertion multiligne
Ansi Quotes : guillement au lieu des apostrophes inversées
Add DROP Statements : vérifie si la table existe et la supprime si elle existe.
Fully qualified identifiers : Les identifiants deviennent qualifiés Base.Table
Disable keys : crée des index non unique a la fin des insertions
Optimized commit : gestion de transactions
Pour binlog pos, il faudra activer log-bin dans my.ini pour générer un log des requetes effectuées.
On peut activer log-bin ponctuellement pour le backup : 
Lors d’une restauration, les fichiers de log ne seront pas restaurés.
Backups par Requête SQL
Les commandes SQL permettent d’exporter et d’importer des données dans des fichiers. Et ceci qu’importe l’extension
Exportation
L’exportation en ligne de commande se fait via INTO OUTFILE
SELECT ... INTO OUTFILE nom_fichier FROM table;
Les données sauvegardées seront exclusivement des données de type CSV, c’est à dire : les données de la tables séparées par défaut par une TABLUATION entre chaques champs et un retour à la ligne entre chaque enregistrements.
Le fichier est écrit sur le serveur. Il ne doit pas exister sinon il y a une erreur. L’utilisateur doit disposer du privilege FILE pour executer ce type de commandes. Si une table contient des champs ayant des retours à la ligne, ou encore des tabulations, TAB = NULL et LF ne représentera plus la fin d’un enregistrement
Le chemin du fichier peut être absolue, relatif et ne peut comporter aucune direction et l’enregistrement se fera dans la base de données courrante.
Options de format
Il est possible de modifier le format de sortie, de determiner les séparateurs de champs et de lignes, de determiner les caracteres d’encadrement de valeur mais aussi les caracteres d’echappement.
- FIELDS : pour les champs
- TERMINATED BY ‘…’ : terminé par
- [OPTIONNALY] ENCLOSED BY ‘…’ : caractere permettant d’encadrer des valeurs de champs
- ESCAPED BY ‘…’ : caractere d’echappement
- LINES : pour les enegistrements
Exemples :
mysql> SELECT * INTO OUTFILE 'c:/city.txt' FROM city; Query OK, 4079 rows affected (0.03 sec)
1 Kabul AFG Kabol 1780000n 2 Qandahar AFG Qandahar 237500n 3 Herat AFG Herat 186800n 4 Mazar-e-Sharif AFG Balkh 127800n 5 Amsterdam NLD Noord-Holland 731200n 6 Rotterdam NLD Zuid-Holland 593321n 7 Haag NLD Zuid-Holland 440900n 8 Utrecht NLD Utrecht 234323n
mysql> SELECT * INTO OUTFILE 'c:/city2.txt' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'rn' FROM city; Query OK, 4079 rows affected (0.00 sec)
1;"Kabul";"AFG";"Kabol";1780000rn 2;"Qandahar";"AFG";"Qandahar";237500rn 3;"Herat";"AFG";"Herat";186800rn 4;"Mazar-e-Sharif";"AFG";"Balkh";127800rn 5;"Amsterdam";"NLD";"Noord-Holland";731200rn 6;"Rotterdam";"NLD";"Zuid-Holland";593321rn 7;"Haag";"NLD";"Zuid-Holland";440900rn 8;"Utrecht";"NLD";"Utrecht";234323rn
La commande SELECT INTO OUTFILE retournera le nombre de lignes affectées
Importation
Il existe différentes manieres de restaurer des données :
- SOURCE chemin
- LOAD DATA INFILE
LOAD DATA INFILE
LOAD DATE [LOCAL] INFILE fichier [IGNORE || REPLACE] INTO TABLE table option_format [IGNORE n LINES] champs_a_inserer [SET]
LOCAL : à mettre si et seulement si la table se trouve sur le client.
IGNORE et REPLACE : Le mode de traitement des clés dupliquées (cf. Insertions)
option_format : Cf Options de format
IGNORE n LINES : Ignore un certain nombre de lignes
SET : permet d’inserer de nouvelles valeurs à partir des valeurs déjà présentes. Il faut autant de valeurs que de colonnes dans le fichier. Si aucune colonne n’est spécifiée, il faut que les colonnes du fichier soit dans le meme ordre que dans la table.
mysql> CREATE TABLE t4 (i int NULL, c char(1)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t4 (i,c) VALUES (NULL,'a'),(1,'b'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t4; +------+------+ | i | c | +------+------+ | NULL | a | | 1 | b | +------+------+ 2 rows in set (0.00 sec) mysql> SELECT * INTO OUTFILE 'c:/test.sql' FROM t4; Query OK, 2 rows affected (0.01 sec) mysql> LOAD DATA INFILE 'c:/test.sql' INTO TABLE t4; Query OK, 2 rows affected (0.02 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> SELECT * FROM t4; +------+------+ | i | c | +------+------+ | NULL | a | | 1 | b | | NULL | a | | 1 | b | +------+------+ 4 rows in set (0.00 sec)
Les insertions avec les LOAD DATA INFILE sont plus performants et plus rapides.
L’utilisateur doit disposer des privileges FILE
En spécifiant LOCAL, MySQL inclue IGNORE automatiquement
Exemple 1 : IGNORE/REPLACE les clés en doublons
mysql> CREATE TABLE t5 (pk tinyint AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM t5;
+----+
| pk |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.00 sec)
mysql> SELECT * FROM t6
-> ;
+------+
| pk |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
+------+
6 rows in set (0.00 sec)
mysql> SELECT * INTO OUTFILE 'c:/t6.txt' FROM t6;
Query OK, 6 rows affected (0.02 sec)
mysql> LOAD DATA INFILE 'c:/t6.txt' REPLACE INTO TABLE t5;
Query OK, 9 rows affected (0.02 sec)
Records: 6 Deleted: 3 Skipped: 0 Warnings: 0
mysql> SELECT * FROM t5;
+----+
| pk |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+----+
13 rows in set (0.00 sec)
Exemple 2 : IGNORE n LINES
mysql> SELECT * FROM t5; +----+ | pk | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 7 rows in set (0.00 sec) Query OK, 3 rows affected (0.02 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> LOAD DATA INFILE 'c:/t6.txt' IGNORE 3 LINES INTO TABLE t5; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 LINES INTO TABLE t5' at line 1 mysql> LOAD DATA INFILE 'c:/t6.txt' INTO TABLE t5 IGNORE 3 LINES; Query OK, 3 rows affected (0.02 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t5; +----+ | pk | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 11 | | 12 | | 13 | +----+ 10 rows in set (0.00 sec)
Exemple 3 : Fichier source contenant plus de colonnes que la table
//t7.txt 3 Foo Bar 4 Bar Baz
mysql> CREATE TABLE t7(id tinyint, personne char(30)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t7(id,personne) VALUES(1,'toto dupond'),(2,'titi dupont'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t7; +------+-------------+ | id | personne | +------+-------------+ | 1 | toto dupond | | 2 | titi dupont | +------+-------------+ 2 rows in set (0.00 sec) mysql> LOAD DATA INFILE 'c:/t7.txt' INTO TABLE t7; ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns mysql> LOAD DATA INFILE 'c:/t7.txt' INTO TABLE t7 (id, @var1, @var2) SET personne = CONCAT(@var1,' ',@var2); Query OK, 2 rows affected (0.02 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> SELECT * FROM t7; +------+-------------+ | id | personne | +------+-------------+ | 1 | toto dupond | | 2 | titi dupont | | 3 | Foo Bar | | 4 | Bar Baz | +------+-------------+ 4 rows in set (0.00 sec)
Backups avec les outils en lignes de commande
Exportation
MySQL fournit un programme client nommé mysqldump qui se connecte au serveur pour exportation. Il est possible d’inclure des DROP, des CREATE, d’include uniquement des données ou encore uniquement la structure de table, d’exporter les données dans un fromat standard ou non. Mysqldump donne la possibilité de compresser le fichier. Il existe différentes manieres d’invoquer mysqldump :
- Section developpeur
- Section administrateur
Section développeur
On invoquera mysqldump uniquement en shell. Il existe trois maniere d’invoquer mysql dump :
- mysqldump option_connexion nomDB > fichier (chemin relatif ou absolue)
- mysqldump option_connexion nomDB t1 t2… > fichier (chemin relatif ou absolue)
- mysqldump option_connexion –all-databases > fichier (chemin relatif ou absolue)
- mysqldump option_connexion –databases db1 db2… > fichier (chemin relatif ou absolue)
- mysqldump option_connexion –tab = fichier (chemin relatif ou absolue) bdd[ t1,...]
Attention : si on spécifie les tables à sauvegarder, MySQL va créer 2 fichiers dans le repertoire de destination : 1 pour la creation de la table, le 2eme pour les données (SELECT INTO OUTFILE)
Forme courte : –all-databases = -A
Remarque : Ces syntaxe sont compatibles avec mysqlimport
shell>mysqldump -u root -pmysql50 --tab="c:/sauvegarde" world city country shell>mysqldump -u root -pmysql50 --fields-terminated-by=";" --lines-terminated-by="rn" --tab="c:/sauvegarde" world city country
Importer
Pour importer des données en lignes de commandes, MySQL fournit le programme client nommé mysqlimport. Il fonctionne de la meme maniere que LOAD DATA INFILE. C’est donc une interface vers la commande LOAD DATA INFILE
shell>mysqlimport options db fichier,fichier...
Les fichiers doivent obligatoirement porter le meme nom que la table. Il n’existe pas d’options SET
Options :
- –local
- –ignore | –replace
- –fields-terminated-by= »"
- –fields-optionally-enclosed-by= »"
- –columns= »col1,col2… »
Commentaires récents