Formation PUB200 : MySQL, 2018 Copie de sécurité d'une base de données MySQL

Effectuer une copie de sécurité d'une base de données chez un hébergeur Web


Lorsqu'un site Web est en ligne avec sa base de données, il y a de fortes chances que les données de cette base de données soient modifiées directement en ligne. C'est le cas notamment avec Apical : les fiches sont éditées directement à partir du site Web.

La base de données utillisée localement pour le développement contient donc des données moins à jour que celle en ligne.

C'est pourquoi il est impératif d'effectuer régulièrement une copie de sécurité de la base de données en ligne. Imaginez-vous tout le travail perdu si la base de données d'Apical devenait corrompue et que je n'avais pas de sauvegarde de ses données ? Je n'ose même pas y penser.

▼Publicité

Privilèges requis

L'usager MySQL que vous utilisez pour effectuer la sauvegarde doit détenir des privilèges particuliers. Au minimum, il doit détenir le privilège SELECT sur chacune des tables à sauvegarder.

Selon la documentation officielle de MySQL1 :

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Pour donner ces privilèges à l'usager MySQL :

MySQL

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON mabd.* TO 'monusagermysql'@'localhost';

D'autres privilèges sont requis si vous désirez sauvegarder les procédures et fonctions stockées de votre base de données2 :

This option requires the global SELECT privilege.

Plus précisément, l'usager MySQL aura besoin du privilège SELECT sur la table mysql.proc. Il s'agit donc d'une table qui ne fait pas partie de votre base de données.

MySQL

GRANT SELECT ON mysql.proc TO 'monusagermysql'@'localhost';

Généralement, les hébergeurs ne vous permettent de modifier les privilèges que sur vos propres bases de données. Il est donc possible que les procédures et fonctions stockées ne puissent pas être exportées.

Si votre base de données contient des procédures et fonctions stockées, il est de votre responsabilité d'effectuer les vérifications du script SQL obtenu pour vous assurer que vos procédures et fonctions stockées soient présentes.

Au besoin, communiquez avec votre hébergeur afin qu'il vous offre une solution satisfaisante.

Script SQL qui ne contient que les éléments de programmation

J'ai déjà eu à contacter un hébergeur pour répondre à ce besoin et la solution a été que l'hébergeur réalise l'exportation pour moi.

Une fois la base de données recréée dans mon environnement de développement local, j'ai généré un script SQL qui ne contient que les procédures et fonctions stockées.

Terminal

mysqldump -u root -p --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers mabd > /chemin/mabd-procedures-aaaa-mm-jj.sql

Contrairement aux données, les éléments de programmation sont généralement stables donc ce script demeure à jour très longtemps.

Par la suite, quand je devais recréer la base de données, je partais d'un script SQL frais qui ne contient pas les procédures et fonctions stockées puis je roulais mon script de procédures, ce qui me donnait une belle base de données toute neuve!

Sauvegarde à partir de phpMyAdmin

Si votre hébergeur vous offre un accès avec phpMyAdmin, vous pouvez exporter la base de données à l'aide de cet outil.

Sauvegarde à partir d'une fenêtre Terminal

Vous pouvez également exporter la base de données en ligne de commande, grâce à la fenêtre Terminal offerte dans le cPanel.

Icône Terminal dans le cPanel

Dans cette fenêtre, entrez la commande mysqldump suivante pour créer un script SQL de la base de données.

À la place de monusagermysql, entrez le nom d'un usager MySQL qui détient les privilèges requis.

Terminal

mysqldump -u monusagermysql -p --routines --comments --triggers mabd > /chemin/mabd-aaaa-mm-jj.sql

Sauvegarde par programmation

Il est possible de coder directement dans votre site Web une fonctionnalité qui lancera la commande mysqldump.

Si vous empruntez cette voie, prenez bien soin de vous assurer que l'option ne soit disponible que pour les administrateurs de votre site Web.

Afin d'augmenter encore plus la sécurité, faites en sorte que le script SQL généré soit stocké sur le serveur en dehors de la racine du site Web. Ainsi, le script ne pourra être récupéré que par les personnes qui ont accès à votre panneau de contrôle chez votre hébergeur. 

Pour que l'opération de sauvegarde soit possible, assurez-vous de respecter ces consignes :

  • Il est préférable de ne pas utiliser les options -u et -p dans un programme afin de ne pas avoir à écrire le mot de passe en clair. D'ailleurs, ceci gènère un avertissement : « [Warning] Using a password on the command line interface can be insecure. ». Plutôt, entrez le code d'usager et le mot de passe dans le fichier .mylogin.cnf à l'aide de la commande mysql_config_editor puis utilisez l'option --login-path dans la commande mysqldump.
  • Le site Web doit pouvoir écrire dans le dossier dans lequel le script SQL sera généré. Un exemple de modification des droits est donné ici.
  • Pour faciliter votre travail de débogage, gardez une trace du code d'état de la commande et de ce qu'il donne en sortie. La technique est illustrée en couleur dans l'extrait de code. Le « 2>&1 » signifie qu'il faut rediriger la sortie standard des erreurs vers la première variable.
PHP

$nomFichier = "chemin/mabd-" . date("Y-m-d") . ".sql";
$commande = "mysqldump --login-path=local --routines --comments --triggers mabd -r \"$nomFichier\"";
exec("$commande 2>&1", $sortie, $etat);

if($etat > 0) {
    echo "Un problème a empêché la réussite de l'opération.";
    log_debug( $sortie );
}
else {
    echo "Opération réussie !";
}

Extension WordPress

Si vous travaillez avec un site WordPress, il existe de nombreuses extensions qui permettent d'effectuer une copie de sécurité de la base de données.

Source

1. « mysqldump — A Database Backup Program  ». MySQL. https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

2. « mysqldump — A Database Backup Program, --routines ». MySQL. https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

Dernière révision le 14 juin 2020
Merci de partager !

Site fièrement hébergé chez A2 Hosting.

Soumettre