8.8 mysqldump , sauvegarde des structures de tables et les données

Utilitaire qui permet d'exporter une base ou un groupe de bases vers un fichier texte, pour la sauvegarde ou le transfert entre deux serveurs (pas nécessairement entre serveurs MySQL). L'export contiendra les requêtes SQL nécessaires pour créer la table et la remplir.

Il y a plusieurs méthodes pour invoquer mysqldump :

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases DB1 [DB2 DB3...]
shell> mysqldump [options] --all-databases

Vous pouvez obtenir une liste des options valides pour votre version de mysqldump avec la commande mysqldump --help .

Notez que si vous utilisez une nouvelles copie du programme mysqldump , et que vous allez faire un export qui sera lu dans une vieille version de MySQL, vous ne devriez pas utiliser les options --opt et -e .

mysql> CREATE TABLE t (f DOUBLE);
mysql> INSERT INTO t VALUES(1e+111111111111111111111);
mysql> INSERT INTO t VALUES(-1e111111111111111111111);
mysql> SELECT f FROM t;
+------+
| f    |
+------+
|  inf |
| -inf |
+------+

--
-- Dumping data for table `t`
--
 
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);

mysqldump supporte les options suivantes :

--help , -?

Affiche le message d'aide et quitte.

--add-drop-table

Ajoute une commande drop table avant chaque requête de création de table.

--add-locks

Ajoute une commande LOCK TABLES avant l'export de table et une commande UNLOCK TABLE après(Pour accélérer les insertions dans MySQL). Vitesse des requêtes INSERT .

--all-databases , -A

Exporte toutes les tables. C'est l'équivalent de l'option --databases avec toutes les bases de données sélectionnées.

--allow-keywords

Permet la création de colonnes ayant des noms de mots réservés. Cela fonctionne en préfixant chaque nom de colonne avec le nom de la table.

--comments[={0|1}]

Si cette option prend 0 , elle supprime les informations additionnelles (comme les versions de programme, les versions d'hôte) dans les exports. L'option --skip-comments fait la même chose. Par défaut, la valeur de cette option est 1 , pour conserver ces informations. Nouveau en MySQL 4.0.17.

--compatible=name

Produit un résultat qui est compatible avec les autres bases de données, ou avec d'anciennes versions de MySQL. Les valeurs possibles de name sont mysql323 , mysql40 , postgresql , oracle , mssql , db2 , sapdb , no_key_options , no_table_options , ou no_field_options . Pour utiliser plusieurs valeurs, séparez les par des virgules. Ces valeurs ont la même signification que les options correspondantes de configuration du mode SQL. Le mode SQL du serveur .

Cette option requiert la version 4.1.0 ou plus récente. Avec les anciens serveurs, cela ne fait rien.

--complete-insert , -c

Utilise des commandes INSERT complètes, avec les noms de colonnes.

-C , --compress

Compresse toutes les informations entre le client et le serveur, les deux supporte la compression.

--create-options

Inclut toutes les options spécifiques MySQL de création de table dans les commandes CREATE TABLE . Avant MySQL 4.1.2, utilisez --all .

--databases , -B

Pour exporter plusieurs bases de données. Notez la différence d'utilisation. Dans ce cas, aucune table n'est spécifié. Tous les arguments de noms sont considérés comme des noms de base. Une ligne USE db_name; sera ajoutée dans l'export avant chaque base de données.

--debug[=debug_options] , -# [debug_options]

Active l'historique de débogage. La chaîne de format est généralement 'd:t:o,file_name' .

--default-character-set=charset

Configure le jeu de caractères par défaut pour l'export. S'il n'est pas spécifié, mysqldump 10.3 (MySQL-4.1.2) ou plus récent va utiliser utf8 . Les versions plus anciennes utiliseront latin1 .

--delayed

Les insertions se font avec la commande INSERT DELAYED .

--delete-master-logs

Sur un maître de réplication, efface le log binaire une fois que l'opération d'export est faite. Cette option active automatiquement --first-slave . Elle a été ajoutée en MySQL 3.23.57 (pour MySQL 3.23) et MySQL 4.0.13 (pour MySQL 4.0).

--disable-keys , -K

Pour chaque table, entoure les commandes d' INSERT avec les commandes /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; et /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; . Cela accélère les chargements du fichier d'export pour MySQL 4.0 car les index ne sont créés qu'après l'insertion. Cette option n'est effective que pour les tables MyISAM .

--extended-insert , -e

Utilise la nouvelle syntaxe multi-ligne INSERT . (Cela donne des insertions plus courtes et plus efficaces).

--fields-terminated-by=...

--fields-enclosed-by=...

--fields-optionally-enclosed-by=...

--fields-escaped-by=...

--lines-terminated-by=...

Ces options sont utilisées avec l'option -T et ont la même signification que les clauses correspondantes de la commande LOAD DATA INFILE . Syntaxe de LOAD DATA INFILE .

--first-slave , -x

Verrouille toutes les tables de toutes les bases de données.

--flush-logs , -F

Ecrit tout le fichier de log du serveur avant de commencer l'export. Notez que si vous utilisez cette option avec --all-databases (ou l'option -A ), les logs seront vidés pour chaque base de données exportée .

-f , --force,

Continue même si une erreur SQL survient durant l'export.

--host=host_name , -h host_name

Exporte les données depuis le serveur MySQL vers l'hôte indiqué. L'hôte par défaut est localhost .

--lock-tables , -l

Verrouille toutes les tables avant de commencer l'export. Les tables sont verrouillées avec READ LOCAL pour permettre des insertions concurrentes sur les tables MyISAM .Notez que lorsque vous exportes des tables de bases différentes, l'option --lock-tables va verrouiller chaque base séparément. Cette option ne vous garantira pas que vos tables seront logiquement cohérente entre les bases. Des tables de différentes bases pourraient être exportées dans des états très différents.

--master-data

Cette option est similaire à --first-slave , mais produit aussi une commande CHANGE MASTER TO qui fait que le serveur esclave va commencer à la bonne position dans le log du maître, si vous utilisez cette exportation pour configurer initialement l'esclave.

--no-create-db , -n

CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; ne sera pas ajouté dans l'export. Sinon, la ligne ci-dessus sera ajoutée, si l'une des options --databases ou --all-databases ont été activée.

--no-create-info , -t

N'écrit pas les informations de création de table (la requête CREATE TABLE ).

--no-data , -d

N'écrit aucune ligne d'informations sur la table. C'est très pratique si vous voulez simplement exporter la structure de la table.

--opt

Identique à --quick --add-drop-table --add-locks --extended-insert --lock-tables . Vous obtiendrez l'export le plus rapide à importer dans un serveur MySQL.

--password[=password] , -p[password]

Le mot de passe à utiliser lors de la connexion au serveur. Notez que si vous utilisez l'option courte -p , vous ne devez pas laisser d'espace entre l'option et le mot de passe. Si vous spécifiez en omettant la partie '=your_pass' , mysqldump vous demandera le mot de passe en ligne de commande.

--port=port_num , -P port_num

Le port TCP/IP à utiliser avec l'hôte.

--protocol={TCP | SOCKET | PIPE | MEMORY}

Pour spécifier le protocole de connexion à utiliser. Nouveau en MySQL 4.1.

--quick , -q

Ne garde pas en buffer les requêtes, mais écrit immédiatement dans la sortie. Utilise mysql_use_result() pour cela.

--quote-names , -Q

Protège les noms des tables et colonnes avec le caractère '`' .

--result-file=file , -r file

Ecrit directement dans le fichier indiqué. Cette option doit être utilisé sur MSDOS, car cela évite que la nouvelle ligne '\n' soient converties en '\n\r' (nouvelle ligne et retour chariot).

--single-transaction

Cette option ajoute la commande SQL BEGIN avant d'exporter les données vers le serveur. C'est généralement pratique pour les tables InnoDB et le niveau d'isolation de transaction READ_COMMITTED , car ce mode va exporter l'état de la base au moment de la commande BEGIN sans bloquer les autres applications.

Lorsque vous utilisez cette option, pensez bien que seules les tables transactionnelles seront exportées dans un état cohérent, c'est à dire que les tables MyISAM ou HEAP qui seront exportées avec cette option, pourront changer d'état.

L'option --single-transaction a été ajoutée en version 4.0.2. Cette option est mutuellement exclusive avec l'option --lock-tables car LOCK TABLES va valider une transaction interne précédente.

--socket=path , -S path

Le fichier de socket à utiliser pour les connexions locale (à localhost ), qui est l'hôte par défaut.

--skip-comments

Identique à que --comments = 0 .

--tab=path , -T path

Crée un fichier table_name.sql , qui contient les commandes SQL CREATE , et un fichier table_name.txt , qui contient les données, pour chaque table. Le format du fichier .txt est celui qui est spécifié par les options --fields-xxx et --lines--xxx . Note : cette option ne fonctionne qui si mysqldump est exécuté sur la même machine que le démon mysqld , et que le nom d'utilisateur et le groupe de mysqld (normalement l'utilisateur mysql , et le groupe mysql ) doivent avoir des permission pour créer et écrire un fichier dans le dossier que vous spécifiez.

--tables

Remplace l'option --databases ou -B . Tous les arguments suivant les options sont considérés comme des noms de tables.

--user=user_name , -u user_name

Le nom d'utilisateur MySQL lors de la connexion à un serveur distant.

--verbose , -v

Mode détaillé. Affiche plus d'informations sur les faits et gestes du programme.

--version , -V

Affiche la version du programme et quitte.

--where='where-condition' , -w 'where-condition'

Exporte uniquement les lignes sélectionnées. Notez que les guillemets sont obligatoires.

Exemples :

"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

-X , --xml

Exporte la base au format XML.

max_allowed_packet

La taille maximale du buffer pour les communications client / serveur. La valeur de cette variable peut être au maximum de 16 Mo avant MySQL 4.0, et jusqu'à 1 Go depuis MySQL 4.0. Lors de la création de commandes d'insertions multiples (avec l'option --extended-insert ou --opt ), mysqldump va créer des lignes ayant une taille maximale de max_allowed_packet octets. Si vous augmentez la valeur de cette variable, assurez vous que max_allowed_packet est assez grande dans le serveur.

net_buffer_length

La taille initiale du buffer de communication.

mysqldump --opt database > backup-file.sql

mysql database < backup-file.sql

mysql -e "source /patch-to-backup/backup-file.sql" database

mysqldump --opt database | mysql ---host=remote-host -C database

mysqldump --databases database1 [database2 ...] > my_databases.sql

mysqldump --all-databases > all_databases.sql

Pour plus d'informations sur les sauvegardes, voyez Sauvegardes de base de données .