Sauvegarde logique et restauration MySQL (mysqldump, MySQL Shell)

Sauvegarde logique et restauration avec mysqldump ou MySQL Shell (util.dumpInstance / dumpSchemas / dumpTables + util.loadDump) : une table, un schéma ou toute l'instance ; structure seule ou données seules ; migration vers un autre serveur ; extraction d'une seule table depuis un dump complet ; sauvegarde planifiée avec systemd/cron, sans mot de passe grâce au login-path, vérifiée, avec durée de rétention. Chaque parcours se termine par une comparaison du nombre de lignes. Ce guide sert aux migrations, à la récupération d'un objet isolé et au passage d'une version à l'autre ; pour la sauvegarde quotidienne d'une grosse base, les restaurations rapides et le retour à un instant précis (PITR), utilisez le guide Percona XtraBackup.

Topologie: 1 serveur (+ un serveur cible pour la migration et la restauration d'instance)

Scénarios

• Export / import de table(s) — Sauvegarder une ou plusieurs tables, les copier vers un autre schéma/serveur ou les restaurer. La source reste disponible ; aucun verrou n'est pris sur les tables InnoDB.
• Sauvegarder / restaurer un schéma (base de données) — Sauvegarder une base de données (schéma) entière ou la charger dans un autre schéma/serveur. La source reste disponible ; sur la cible, les tables de même nom sont supprimées puis recréées.
• Instance complète (utilisateurs + tous les schémas) — La sauvegarde est prise sur ce serveur et la restauration se fait sur un serveur vide : utilisateurs, privilèges et tous les schémas. Pour reconstruire un serveur ou changer de version/plateforme.
• Structure seule (DDL) — Définitions des tables, vues, triggers, procédures et events ; pas de données. Pour comparer des schémas, monter un environnement vide ou mettre la structure sous gestion de versions.
• Données seules — Seules les lignes sont transférées, la structure existant déjà sur la cible (par ex. pour rafraîchir des données de test). Les triggers de la cible peuvent se déclencher pendant le chargement ; le guide le vérifie.
• Migration vers un autre serveur — Vous travaillez sur le serveur cible et vous vous connectez à la source par le réseau avec un login-path. Au moment de la bascule, l'application cesse d'écrire ; l'interruption dure le temps du dump + du chargement. Pour changer de serveur, de système d'exploitation ou de version.
• Extraire une seule table d'un dump complet — Vous disposez d'un dump complet et n'avez besoin que d'une table (lignes supprimées, table supprimée par DROP). Elle est extraite dans un schéma séparé sans toucher au schéma d'origine, ou la table supprimée est remise en place.
• Sauvegarde planifiée (systemd timer / cron) — Sauvegarde logique automatique chaque jour à heure fixe : utilisateur de sauvegarde dédié, login-path, script, systemd timer ou cron, durée de rétention et notification d'erreur. La source reste disponible.

Avant de commencer

• MySQL … tourne ; l'utilisateur root du système d'exploitation peut se connecter sans mot de passe avec la commande mysql (auth_socket ou ~/.mylogin.cnf). Si l'installation a été faite avec le guide DBAtlas Installation MySQL sur un seul serveur, les chemins sont identiques.
• La taille des schémas est connue et le répertoire de sauvegarde … dispose d'assez d'espace libre (une sauvegarde logique compressée représente en général 10 à 30 % du volume de données ; les index n'entrent dans le dump que sous forme de définitions).
• Toutes les tables sont en InnoDB. --single-transaction (et le dump cohérent de MySQL Shell) n'est sans verrou et cohérent que pour les tables InnoDB ; s'il existe une table MyISAM, les écritures sur cette table pendant le dump créent une incohérence.
• Le serveur sur lequel MySQL Shell sera installé accède au dépôt MySQL (repo.mysql.com), ou un miroir interne est configuré.
• Une version de MySQL identique ou plus récente est installée et tourne sur le serveur cible … (avec le guide d'installation DBAtlas) et ne contient aucun schéma utilisateur. Le chargement dans une version plus ancienne, par exemple de 8.4 vers 8.0 pour un retour arrière, n'est pas pris en charge.
• Le serveur cible peut joindre le port … de la source.
• Le serveur peut envoyer des e-mails : le MTA local (postfix) relaie vers le relais SMTP de l'entreprise.
• Une fenêtre de maintenance est prévue pour une restauration ou une migration volumineuse ; si la cible doit être écrasée, elle a été sauvegardée.
• Ce serveur est une replica saine : threads IO et SQL à Yes, faible retard. La position binlog du dump (--source-data=2) désigne le binlog propre à cette replica ; pour l'utiliser en PITR, log_replica_updates doit être activé (valeur par défaut en 8.x).
• Ce serveur est membre d'un InnoDB Cluster et est actuellement SECONDARY / ONLINE.

Phases du guide

• Préparation — Créer le répertoire de sauvegarde · Contrôler les moteurs des tables et les transactions longues · Installer MySQL Shell
• Restauration (import) — Créer le schéma cible · Créer la structure des tables sur la cible · Contrôler les triggers de la cible · Charger (mysql) · Charger (mysql, par-dessus le même schéma) · Charger (util.loadDump) …
• Sauvegarde d'instance — Exporter les utilisateurs et les privilèges · Sauvegarder tous les schémas (mysqldump) · Sauvegarder l'instance (util.dumpInstance) · Copier la sauvegarde sur le serveur de restauration · Charger la sauvegarde (mysql) · Charger les utilisateurs …
• Compte de migration sur le serveur source — Créer l'utilisateur de migration · Ouvrir le port vers la cible dans le firewall · Arrêter l'application (bascule)
• Transfert sur le serveur cible — Enregistrer la connexion à la source en login-path · Migrer les utilisateurs · Transférer les schémas (mysqldump | mysql) · Charger les utilisateurs sur la cible · Exporter la source (util.dumpInstance / dumpSchemas) · Charger dans la cible (util.loadDump) …
• Extraire une seule table d'un dump — Vérifier que la table figure dans le dump · Extraire la table du dump · Créer le schéma cible · Charger la table · Restaurer la table dans le schéma d'origine · Comparer le nombre de lignes …
• Utilisateur de sauvegarde et login-path — Créer l'utilisateur système de sauvegarde · Créer le compte MySQL de sauvegarde · Enregistrer le login-path (mysql_config_editor)
• Script de sauvegarde — Installer l'outil de notification · Écrire le script de sauvegarde · Rendre le script exécutable · Prendre la première sauvegarde à la main
• Planification (systemd timer) — Écrire le service systemd · Écrire le systemd timer · Activer le timer et déclencher le service à la main · Vérifier que la tâche planifiée a tourné (le lendemain)
• Planification (cron) — Préparer le service cron · Écrire la tâche cron · Régler les droits du fichier cron et tester dans un environnement cron · Vérifier que la tâche planifiée a tourné (le lendemain)
• Vérification et supervision — Restaurer la dernière sauvegarde dans un schéma de test · Surveiller l'âge de la dernière sauvegarde
• Étape suivante : sauvegarde physique — Couvrir les limites de la sauvegarde logique