Stratégies essentielles de sauvegarde MySQL : choisir la bonne approche pour vos données

Votre stratégie de sauvegarde MySQL n'est valable que si vous pouvez restaurer à partir de celle-ci sous pression. Un fichier dump, un instantané du système de fichiers et une sauvegarde physique résolvent différents problèmes de récupération. Le bon choix dépend donc de la taille de vos données, de votre tolérance aux temps d'arrêt et de la quantité de données que vous pouvez vous permettre de perdre.

Pourquoi les sauvegardes MySQL nécessitent un plan de restauration

Les sauvegardes vous protègent de plus que d'une simple panne de disque. Elles vous aident également à récupérer après des bugs d'application, de mauvais déploiements, des suppressions accidentelles, des ransomwares et des pannes régionales.

Commencez par deux objectifs :

RTO : Combien de temps la base de données peut-elle être indisponible pendant la restauration ?
RPO : Quelle quantité de données récentes pouvez-vous perdre ?

Par exemple, un petit wiki interne peut tolérer un mysqldump nocturne et une restauration d'une heure. Un système de production de commandes peut nécessiter des sauvegardes physiques ainsi que des journaux binaires pour pouvoir récupérer à une seconde précise avant un DELETE malencontreux.

Sauvegardes logiques avec `mysqldump`

Les sauvegardes logiques exportent le schéma et les données sous forme de SQL. Elles sont portables et faciles à inspecter, mais peuvent être lentes à créer et encore plus lentes à restaurer sur de grandes bases de données.

Sauvegarder une base de données :

mysqldump -u votre_nom_utilisateur -p votre_nom_base_de_donnees > fichier_sauvegarde.sql

Sauvegarder toutes les bases de données :

mysqldump -u votre_nom_utilisateur -p --all-databases > sauvegarde_toutes_bases.sql

Sauvegarder des tables spécifiques :

mysqldump -u votre_nom_utilisateur -p votre_nom_base_de_donnees table1 table2 > sauvegarde_tables_specifiques.sql

Incluez les routines, événements et déclencheurs lorsque votre schéma en dépend. Les déclencheurs sont inclus par défaut dans l'utilisation typique de mysqldump, mais rendre l'intention explicite dans votre manuel de sauvegarde aide les réviseurs à le remarquer.

mysqldump -u votre_nom_utilisateur -p --routines --events --triggers votre_nom_base_de_donnees > base_avec_programmes.sql

Restaurer dans une base de données existante :

mysql -u votre_nom_utilisateur -p votre_nom_base_de_donnees < fichier_sauvegarde.sql

Pour les charges de travail lourdes sur InnoDB, ajoutez --single-transaction pour que le dump lise un instantané cohérent sans verrous longs sur les tables :

mysqldump -u votre_nom_utilisateur -p --single-transaction --routines --events votre_nom_base_de_donnees | gzip > fichier_sauvegarde.sql.gz

Évitez d'utiliser --single-transaction comme seule protection de cohérence si vous dépendez de tables non transactionnelles comme MyISAM. Ces tables nécessitent un plan de verrouillage ou d'instantané différent.

Sauvegardes physiques pour les grandes bases de données

Les sauvegardes physiques copient les fichiers de données MySQL au lieu de reconstruire la base de données en SQL. Elles sont généralement mieux adaptées aux grands ensembles de données car le temps de restauration est proche de la copie des fichiers et de l'application des journaux de récupération.

Les options courantes incluent :

Instantanés du système de fichiers ou de volumes cloud, coordonnés pour que les données MySQL soient cohérentes en cas de panne ou cohérentes au niveau application.
Percona XtraBackup, un outil open source largement utilisé pour les sauvegardes physiques à chaud des données InnoDB compatibles MySQL.
MySQL Enterprise Backup, l'outil de sauvegarde commercial d'Oracle pour les déploiements MySQL Enterprise.

Créer une sauvegarde complète avec XtraBackup :

xtrabackup --backup --target-dir=/chemin/vers/sauvegarde/complet --user=votre_nom_utilisateur --password=votre_mot_de_passe

Préparer la sauvegarde avant restauration :

xtrabackup --prepare --target-dir=/chemin/vers/sauvegarde/complet

Restaurer après avoir arrêté MySQL et déplacé l'ancien répertoire de données :

systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql.avant-restauration
mkdir /var/lib/mysql
xtrabackup --copy-back --target-dir=/chemin/vers/sauvegarde/complet --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Cet exemple suppose un nom de service et un répertoire de données de style Debian. Vérifiez la documentation de votre paquet, image conteneur ou base de données gérée avant d'exécuter les commandes de restauration.

Journaux binaires et récupération à un instant précis

Les sauvegardes vous indiquent d'où vous pouvez restaurer. Les journaux binaires vous aident à rejouer les modifications après cette sauvegarde, ce dont vous avez besoin pour la récupération à un instant précis.

Activez la journalisation binaire sur MySQL auto-géré avec le paramètre log_bin approprié pour votre version et votre packaging. Sauvegardez ensuite les journaux binaires séparément du serveur de base de données.

Lors de la restauration, vous procédez généralement comme suit :

Restaurez la dernière bonne sauvegarde complète ou incrémentielle.
Utilisez mysqlbinlog pour rejouer les journaux binaires jusqu'à l'heure ou la position cible.
Arrêtez-vous avant l'instruction erronée si vous récupérez d'une erreur d'opérateur.

Choisir la bonne stratégie de sauvegarde

Utilisez une règle de décision simple :

Petites bases de données : commencez par mysqldump automatisé, compression, stockage hors site et tests de restauration réguliers.
Bases de données moyennes : ajoutez des sauvegardes de journaux binaires pour pouvoir récupérer à un instant précis.
Grandes bases de données ou bases critiques : utilisez des sauvegardes physiques, des sauvegardes incrémentielles si prises en charge, des journaux binaires, une surveillance et un exercice de restauration documenté.

Ne choisissez pas uniquement en fonction de la vitesse de sauvegarde. La vitesse de restauration est plus importante lors d'un incident.

Pratiques de sauvegarde qui comptent vraiment

Automatisez la tâche de sauvegarde, mais testez la restauration manuellement jusqu'à ce que le processus devienne routinier. Stockez les sauvegardes hors site, chiffrez les sauvegardes sensibles, surveillez les échecs de tâches et conservez une rétention suffisamment longue pour détecter une corruption silencieuse ou des suppressions accidentelles découvertes des jours plus tard.

Documentez également l'ordre exact de restauration. Lors d'une panne réelle, votre futur vous ne devrait pas avoir à deviner quelle sauvegarde complète, sauvegarde incrémentielle et plage de journaux binaires vont ensemble.

À retenir

Choisissez la méthode de sauvegarde qui correspond à votre objectif de restauration. mysqldump convient pour les petites bases de données et les restaurations portables. Les sauvegardes physiques conviennent aux grands systèmes. Les journaux binaires comblent le fossé lorsque vous avez besoin d'une récupération à un instant précis. Quoi que vous choisissiez, une sauvegarde ne compte pas tant que vous ne l'avez pas restaurée avec succès dans un environnement de test.