1. Accueil
  2. Développement
  3. SGBD et SQL
  4. MySQL

MySQL et la ligne de commande

Sauvegarde et restauration en ligne de commande

L’utilitaire "mysqldump

Montre quelques exemples de sauvegarde et restauration de bases de données à partir des utilitaires en lignes de commande et comment les automatiser à l’aide de scripts

Lorsque l’on travail sur des serveurs en entreprise, on ne dispose pas forcément d’interfaces graphiques pour administrer les bases de données, notamment pour exécuter des sauvegardes restaurations. C’est là que les utilitaires en lignes de commandes peuvent être utiles.

 Sauvegarde de la base

Présentation de mysqldump

L’utilitaire mysqldump permet d’exécuter des sauvegardes aussi bien de la structure d’une base de données que de son contenu.

Selon les options utilisées, il sera possible de générer un script ne contenant que les commandes INSERT permettant de recharger les données dans une base existante ou bien, générer un script contenant aussi les commandes CREATE TABLE permettant de recréer également la structure de la base de données. Il est également possible de sauvegarder plusieurs bases de données en une seule commande ou au contraire, une seule table d’une base de données.

Remarque : Par défaut, "mysqldump" envoi le script généré sur la console. Vous devrez donc rediriger la sortie vers un fichier pour exploiter le script par la suite.

Les options de "mysqldump"

La syntaxe générale est la suivante :

$>mysqldump -u <nom_utilisateur> -p[<mdp>] [OPTIONS] <nom_base1> [<nom_base2>,...]

On peut préciser les tables à sauvegarder :

$>mysqldump [OPTIONS] <nom_base> [tables]

Pour sauvegarder plusieurs bases de données :

$>mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

Pour sauvegarder l’ensemble des bases :

$>mysqldump [OPTIONS] --all-databases [OPTIONS]

Pour lister l’ensemble des options qui sont très nombreuses, le plus simple est de taper l’une des commandes suivantes pour les lister :

$>mysqldump --help | less

ou :

$>man mysqldump

Sauvegarde globale d’une base de données

La commande suivante permet de sauvegarder la structure et les données d’une base de données. L’option utilisée est —opt. Cette dernière remplace les options suivantes :

--add-drop-table --add-locks --create-options --disable-keys --extended-insert
--lock-tables --quick --set-charset

La commande suivante permet de sauvegarder la base de données nommée musiciensdb dans le fichier musiciensdb_bk.sql   :

$>mysqldump -u root -p --opt musiciensdb > musiciensdb_bk.sql

La base de données d’exemple n’ayant qu’une seule table, le fichier .sql   contient le script suivant :

Voir le script

  1. /*!40101 SET NAMES utf8 */;
  2. /*!40103 set @old_time_zone=@@time_zone */;
  3. /*!40103 set time_zone='+00:00' */;
  4. /*!40014 set @old_unique_checks=@@unique_checks, unique_checks=0 */;
  5. /*!40014 set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0 */;
  6. /*!40101 set @old_sql_mode=@@sql_mode, sql_mode='no_auto_value_on_zero' */;
  7. /*!40111 set @old_sql_notes=@@sql_notes, sql_notes=0 */;
  8.  
  9. --
  10. -- Table structure for table `T_COMPOSITEUR_CMP`
  11. --
  12.  
  13. DROP TABLE IF EXISTS `t_compositeur_cmp`;
  14. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  15. /*!40101 SET character_set_client = utf8 */;
  16. CREATE TABLE `t_compositeur_cmp` (
  17.   `CMP_ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  18.   `CMP_DECE` SMALLINT(6) DEFAULT NULL,
  19.   `CMP_NAISSANCE` SMALLINT(6) DEFAULT NULL,
  20.   `CMP_NOM` VARCHAR(50) DEFAULT NULL,
  21.   `CMP_PRENOM` VARCHAR(50) DEFAULT NULL,
  22.   PRIMARY KEY (`cmp_id`)
  23. ) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
  24. /*!40101 SET character_set_client = @saved_cs_client */;
  25.  
  26. --
  27. -- Dumping data for table `T_COMPOSITEUR_CMP`
  28. --
  29.  
  30. LOCK TABLES `t_compositeur_cmp` WRITE;
  31. /*!40000 alter table `t_compositeur_cmp` disable keys */;
  32. INSERT INTO `T_COMPOSITEUR_CMP` VALUES (1,1750,1685,'Bach','Johann Sebastian'),(2,1827,1770,'Beethoven','Ludwig van'),(3,1935,1885,'Berg','Alban'),(4,1976,1913,'Britten','Benjamin'),(5,1896,1824,'Bruckner','Anton'),(6,1713,1653,'Corelli','Arcangelo'),(7,1894,1841,'Chabrier','Emmanuel'),(8,1849,1810,'Chopin','Frédéric'),(9,1990,1900,'Copland','Aaron'),(10,1661,1626,'Couperin','Louis'),(11,1904,1841,'Dvorak','Anton'),(12,1937,1898,'Gershwin','George'),(13,1918,1862,'Debussy','Claude'),(14,1759,1685,'Handel','George Frideric'),(15,1911,1860,'Mahler','Gustav'),(16,1953,1891,'Prokofiev','Serge'),(17,1943,1873,'Rachmaninov','Serge'),(18,1764,1683,'Rameau','Jean-Philippe'),(19,1847,1809,'Mendelssohn','Felix'),(20,1791,1756,'Mozart','Wolgang Amadeus'),(21,1963,1899,'Poulenc','Francis'),(22,1937,1875,'Ravel','Maurice'),(23,1910,1873,'Reger','Max'),(24,1868,1792,'Rossini','Gioachino'),(25,1828,1797,'Schubert','Franz'),(26,1856,1810,'Schumann','Robert'),(27,1915,1872,'Scriabine','Alexandre'),(28,0,0,'Haydn',''),(29,1893,1840,'Tchaîkovski','Piotr Ilytch'),(30,1901,1813,'Verdi','Giuseppe'),(31,1741,1678,'Vivaldi','Antonio'),(32,1782,1735,'Bach','Johann Christian'),(33,1788,1714,'Bach','Carl Philipp Emanuel'),(34,1784,1710,'Bach','Wilhelm Friedemann'),(64,NULL,NULL,'Fauré','Gabriel'),(65,NULL,NULL,'Bowie','David'),(66,NULL,NULL,'Champfort','Alain'),(67,NULL,NULL,'Chevalier','Maurice');
  33. /*!40000 alter table `t_compositeur_cmp` enable keys */;
  34. UNLOCK TABLES;
  35. /*!40103 set time_zone=@old_time_zone */;
  36.  
  37. /*!40101 set sql_mode=@old_sql_mode */;
  38. /*!40014 set foreign_key_checks=@old_foreign_key_checks */;
  39. /*!40014 set unique_checks=@old_unique_checks */;
  40. /*!40101 set character_set_client=@old_character_set_client */;
  41. /*!40101 set character_set_results=@old_character_set_results */;
  42. /*!40101 set collation_connection=@old_collation_connection */;
  43. /*!40111 set sql_notes=@old_sql_notes */;
  44.  
  45. -- Dump completed on 2017-10-18 10:28:30

Télécharger

On peut vérifier que le script contient bien les commandes de création de table et les commandes d’insertion d’enregistrement. Il suffira d’effectuer ce script en précisant simplement le nom de la base de données dans laquelle seront créées les tables et insérées les données.

 Restauration de la base de données

Cette procédure pourra être exécutée avec la commande mysql suivante :

$>mysql -u root -p musiciensdb < musiciensdb_bk.sql

La même commande pourra être utilisée avec un nom de base de données différente pour disposer d’une copie de la base de données. La base de données spécifiée devra avoir été créée auparavant puisque, avec les options utilisées dans cet exemple, le script généré ne contient pas de commande CREATE DATABASE.

 Ajouter la création de la base de données dans le script

Dans le cas ou nous voudrions ajouter la commande CREATE DATABASE au script, il suffit d’ajouter l’option -B à la commande de backup.

Exemple :

$>mysqldump -u root -p --opt -B musiciensdb > musiciensdb_bk.sql

Les lignes suivantes seront ajoutées dans le script :

  1. ...
  2. --
  3. -- Current Database: `musiciensdb`
  4. --
  5. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `musiciensdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
  6. USE `musiciensdb`;
  7. ...

Télécharger

Vous pourrez avec le script précédent aller sur un autre serveur et l’exécuter avec la commande suivante, pour ajouter une nouvelle base de données, créer les tables et importer les données ;

$>mysql -u root -p < musiciensdb_bk.sql

Remarque : Vous devez être administrateur MySQL pour pouvoir créer la base de données.

 Créer un script de sauvegarde

Lorsque l’on est amené à exécuter régulièrement des sauvegardes, il peut être utile de réaliser un petit script pour simplifier la ligne de commande.

Le programme très basique suivant, permet de créer un script de sauvegarde dont le nom dépend de la date et de l’heure courante.

Il suffit de l’appeler en passant en paramètre l’utilisateur et mot de passe de la base.

Après plusieurs exécutions de ce script, on obtient les fichiers suivants :

Il est possible d’enrichir ce script pour l’adapter à une sauvegarde journalière par exemple et d’utiliser un gestionnaire de tâche comme cron pour automatiser la procédure.

 Conclusion

Cet article a permit de voir les commandes qui permettent de sauvegarder et restaurer une base de données MySQL à partir des 2 utilitaires mysqldump et mysql.

Nous avons également vu comment automatiser la sauvegarde à l’aide d’un petit script shell.

Article n° 101

Crée par: chris

Créé le: 28 novembre 2017

Modifié le: 28 novembre 2017

Nombre de visites: 898

Popularité: 13 %

Popularité absolue: 1

MySQL

Mots clés de cet article

SPIP

2003-2024 LePpf
Plan du site | | RSS 2.0 | Sur YouTube

Visiteurs connectés : 3

Nombre moyen de visites quotidiennes sur le site: 214