Table des matières de l'article :
Dans le monde des bases de données, les sauvegardes sont un sujet crucial. Nombre d'administrateurs se croient à l'abri grâce à une sauvegarde quotidienne, un instantané de volume ou une copie nocturne du répertoire de données. En réalité, lorsqu'il s'agit de systèmes de production, le problème ne se limite pas à « avoir une sauvegarde », mais consiste à pouvoir revenir à un état précis au moment opportun, en minimisant les pertes de données et en limitant les interruptions de service. C'est là qu'intervient la restauration à un point précis dans le temps (PITR).
PITR permet de restaurer une base de données MariaDB non seulement à l'état de la dernière sauvegarde disponible, mais aussi à un point précis dans le temps après cette sauvegarde . Ceci est crucial en cas d'erreur humaine, de requête destructive, de mise à jour défectueuse d'une application, de suppression accidentelle ou de compromission d'une application modifiant les données de manière non intentionnelle. Dans tous ces cas, la simple restauration de la sauvegarde peut s'avérer insuffisante : si la sauvegarde date de 02 h 00 et que la panne survient à 14 h 36, revenir à 02 h 00 signifierait perdre plus de douze heures de modifications légitimes.
Avec une restauration à un point précis dans le temps (PITR) correctement conçue, vous pouvez restaurer la sauvegarde de 2 h du matin et réappliquer toutes les modifications ultérieures jusqu'à 14 h 35 min 59 s, soit une seconde avant la requête malveillante. Le principe est simple, mais nécessite une configuration préalable : il est impossible d'improviser une restauration à un point précis dans le temps après un incident si les journaux nécessaires n'ont pas été générés et conservés.
Qu'est-ce que PITR dans MariaDB ?
MariaDB n'effectue pas de restauration à la source « magique » à partir de zéro. Son mécanisme repose sur la combinaison de deux éléments : une sauvegarde cohérente et des journaux binaires. La sauvegarde représente un instantané de la base de données à un moment précis. Les journaux binaires, quant à eux, enregistrent les modifications survenues après cet instantané : insertions, mises à jour, suppressions, modifications et créations de tables, et plus généralement, toutes les opérations qui modifient l'état des données.
Le processus est donc le suivant : effectuer une sauvegarde valide, la restaurer dans un répertoire de données propre, identifier l’emplacement du fichier binlog correspondant à l’heure de la sauvegarde, puis réappliquer les binlogs jusqu’au point souhaité. Ce point d’arrêt peut être défini par date et heure, par position dans le journal binaire ou, dans les architectures plus avancées, par GTID. Dans la plupart des contextes opérationnels, notamment sur les serveurs uniques ou les environnements gérés peu complexes, la restauration par nom de fichier binlog, emplacement et date/heure d’arrêt est largement suffisante.
D'un point de vue pratique, l'outil de sauvegarde physique le plus adapté à MariaDB est mariadb-backup, précédemment connu sous le nom mariabackupIl s'agit d'un outil conçu pour effectuer des sauvegardes physiques en ligne, réduisant ainsi l'impact sur le service et produisant une copie du répertoire de données qui peut ensuite être préparée et restaurée. Pour la lecture et la relecture des journaux binaires, en revanche, mariadb-binlog, ou dans certains environnements la commande compatible mysqlbinlog.
Prérequis : Activer et gérer les journaux binaires
La première condition pour la restauration à l'état initial (PITR) est l'activation des journaux binaires. Sans ces journaux, MariaDB ne peut être restaurée qu'à l'état où elle se trouvait au moment de la sauvegarde, et non à un point intermédiaire ultérieur. C'est l'erreur la plus fréquente : on met en place une politique de sauvegarde, parfois même correcte, mais on oublie que la PITR requiert des journaux de modifications persistants.
Sur un serveur MariaDB installé sous Linux, la configuration peut être saisie dans le fichier de configuration principal du serveur. Dans les environnements AlmaLinux avec des paquets MariaDB, par exemple, il est courant de travailler ainsi. /etc/my.cnf.d/server.cnfVoici un exemple minimal :
[mysqld] server_id=1 log_bin=/var/lib/mysql/mariadb-bin binlog_format=ROW sync_binlog=1 expire_logs_days=14
Le paramètre server_id Cela est nécessaire pour activer la journalisation binaire et il est toujours recommandé de la définir explicitement. log_bin définit le chemin et le préfixe des fichiers binlog. binlog_format=ROW Il est généralement préférable à des fins de récupération, car il enregistre les modifications au niveau de la ligne et réduit les ambiguïtés typiques de la journalisation basée sur les instructions. sync_binlog=1 Améliore la durabilité des bûches, au prix possible d'une baisse de performance. expire_logs_daysEnfin, elle détermine combien de jours conserver les fichiers binlog avant leur expiration automatique.
Sur les versions plus récentes, il peut être préférable d'utiliser une durée de rétention exprimée en secondes :
[mysqld] server_id=1 log_bin=/var/lib/mysql/mariadb-bin binlog_format=ROW sync_binlog=1 binlog_expire_logs_seconds=1209600
La valeur 1209600 Cela correspond à 14 jours. La durée de conservation choisie doit être cohérente avec la fréquence de sauvegarde et l'objectif de point de restauration (RPO) souhaité. Si vous effectuez une sauvegarde quotidienne complète mais supprimez les fichiers binlog après quelques heures, la fenêtre utile pour la restauration à un point précis dans le temps devient inutilement courte. En production, il est également conseillé de copier les fichiers binlog hors du serveur principal, car les stocker uniquement sur le même disque que la base de données vous expose au risque de les perdre en même temps que le répertoire de données.
Après modification de la configuration, le service doit être redémarré :
systemctl restart mariadb
Vous pouvez vérifier que les journaux binaires sont activés avec :
SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW BINARY LOGS;
Se log_bin résultats ON e SHOW BINARY LOGS Afficher au moins un fichier binlog ; la base technique du PITR est présente.
Création d'une sauvegarde physique avec mariadb-backup
Une stratégie PITR sérieuse commence par des sauvegardes physiques régulières. Une sauvegarde SQL peut être utile dans de nombreux cas, mais sur les bases de données volumineuses, elle a tendance à être lente, tant à l'exportation qu'à l'importation. Une sauvegarde physique avec mariadb-backup, au lieu de cela, elle copie les fichiers de base de données eux-mêmes et permet des restaurations généralement plus rapides, notamment sur les grandes instances.
Tout d'abord, il est conseillé de créer un utilisateur dédié à la sauvegarde, en évitant d'utiliser l'utilisateur administrateur principal :
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'PasswordMoltoForteQui'; GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;
Une sauvegarde complète peut être effectuée dans un répertoire dédié :
mkdir -p /backup/mariadb/full-$(date +%F) mariadb-backup --backup \ --target-dir=/backup/mariadb/full-$(date +%F) \ --user=backup \ --password='PasswordMoltoForteQui'
La sauvegarde nouvellement créée ne doit pas être considérée comme immédiatement prête à être restaurée. Comme pour les sauvegardes physiques InnoDB, une phase de préparation est nécessaire afin de garantir la cohérence de la sauvegarde en appliquant les informations nécessaires issues des journaux internes et en rendant les fichiers restaurables.
mariadb-backup --prepare \ --target-dir=/backup/mariadb/full-2026-06-10
Une fois la préparation terminée, vous trouverez dans le répertoire de sauvegarde un fichier extrêmement important : xtrabackup_binlog_infoCe fichier contient le nom du journal binaire et l'emplacement exact correspondant à la sauvegarde.
cat /backup/mariadb/full-2026-06-10/xtrabackup_binlog_info
Un résultat typique pourrait être :
mariadb-bin.000123 456789
Cela signifie qu'après la restauration de cette sauvegarde, la réexécution des journaux binaires devra démarrer à partir du fichier. mariadb-bin.000123 et de la position 456789Ces informations doivent être conservées avec la sauvegarde, car elles représentent le lien entre l'instantané du répertoire de données et le flux de modifications ultérieur.
Restaurez la sauvegarde dans un répertoire de données propre
La restauration ne doit pas être effectuée à l'aveugle sur le serveur de production. En cas d'incident réel, la procédure la plus prudente consiste à restaurer d'abord les données sur une machine distincte ou dans un environnement isolé, à les valider, puis à décider du retour en production. Cette approche évite l'écrasement définitif de données potentiellement utiles pour l'analyse, l'extraction partielle ou d'autres tentatives de restauration.
Sur un serveur de restauration, après l'arrêt de MariaDB, vous pouvez supprimer le répertoire de données existant et en préparer un vide :
systemctl stop mariadb mv /var/lib/mysql /var/lib/mysql.pre-pitr.$(date +%F-%H%M%S) mkdir /var/lib/mysql chown mysql:mysql /var/lib/mysql
À ce stade, copiez la sauvegarde préparée dans le répertoire de données :
mariadb-backup --copy-back \ --target-dir=/backup/mariadb/full-2026-06-10 chown -R mysql:mysql /var/lib/mysql systemctl start mariadb
Après le redémarrage, MariaDB sera dans l'état exact de la sauvegarde complète. Nous n'avons pas encore atteint le point précis dans le temps ; nous avons simplement reconstruit la configuration de base. L'étape suivante consiste à réappliquer les journaux binaires.
Appliquer les journaux binaires jusqu'à l'heure souhaitée
Supposons que nous souhaitions récupérer la base de données jusqu'au 10 juin 2026 à 14h35min59s. Supposons également que l'erreur se soit produite à 14h36min12s, peut-être à cause d'une… DROP TABLE ou un DELETE sans clause WHERENotre objectif est de réappliquer tous les événements valides survenus après la sauvegarde, en évitant toute opération destructive.
Si le fichier xtrabackup_binlog_info indiqué:
mariadb-bin.000123 456789
et les fichiers binlog originaux ont été préservés dans /var/lib/mysql.pre-pitr.2026-06-10-150000/, nous pouvons générer un fichier SQL de récupération comme ceci :
mariadb-binlog \ --start-position=456789 \ --stop-datetime="2026-06-10 14:35:59" \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000123 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000124 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000125 \ > /root/pitr-recovery.sql
Le fichier résultant peut ensuite être appliqué à la base de données restaurée :
mariadb < /root/pitr-recovery.sql
Cette opération rejoue les événements contenus dans les fichiers journaux binaires jusqu'à la date d'arrêt spécifiée. La base de données finale ne sera pas celle de la sauvegarde, mais celle résultant de la sauvegarde augmentée de toutes les modifications ultérieures jusqu'à la date sélectionnée.
Dans les situations sensibles, il est souvent préférable de ne pas se fier uniquement à l'heure. Deux transactions peuvent être très proches l'une de l'autre, ou l'horloge du serveur peut introduire une ambiguïté. C'est pourquoi, dans la mesure du possible, il est préférable d'examiner le journal binaire et de localiser précisément l'événement à éviter.
mariadb-binlog \ --base64-output=DECODE-ROWS \ -vv \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000124 \ | less
Ce mode vous permet de lire les événements ligne par ligne sous une forme plus compréhensible, de rechercher l'opération malveillante et de noter la position immédiatement précédente. Vous pouvez ensuite utiliser --stop-position au lieu de --stop-datetime:
mariadb-binlog \ --start-position=456789 \ --stop-position=987654 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000123 \ /var/lib/mysql.pre-pitr.2026-06-10-150000/mariadb-bin.000124 \ > /root/pitr-recovery.sql mariadb < /root/pitr-recovery.sql
La position dans le journal binaire est généralement plus précise que l'heure, car elle identifie un point exact dans le flux des événements.
Exemple technique : récupération avant suppression accidentelle
Imaginons une base de données d'application appelée ecommerceÀ 02h00, une sauvegarde complète est effectuée avec mariadb-backupÀ 14 h 36, un opérateur exécute par erreur :
DELETE FROM ordini;
ou, dans un scénario encore plus grave :
DROP TABLE ordini;
Une sauvegarde complète vous permettrait de revenir à 2 h du matin, mais vous perdriez toutes les commandes enregistrées ce matin-là. Avec la restauration à la demande (PITR), la procédure est la suivante : isolez le serveur ou préparez une machine de restauration, restaurez la sauvegarde de 2 h du matin, puis récupérez les données à partir de la sauvegarde de 2 h du matin. xtrabackup_binlog_info Déterminez la position de départ, lisez les journaux binaires jusqu'à quelques secondes avant l'erreur et appliquez le résultat.
cat /backup/mariadb/full-2026-06-10/xtrabackup_binlog_info
Sortie :
mariadb-bin.000120 884422
Génération de replay :
mariadb-binlog \ --start-position=884422 \ --stop-datetime="2026-06-10 14:35:59" \ /safe-binlogs/mariadb-bin.000120 \ /safe-binlogs/mariadb-bin.000121 \ /safe-binlogs/mariadb-bin.000122 \ > /root/ecommerce-pitr.sql
Applicazione:
mariadb ecommerce < /root/ecommerce-pitr.sql
Après l'importation, il est nécessaire de vérifier la cohérence de l'application : nombre de commandes, tables associées, contraintes, données de paiement, journaux d'application et état des files d'attente. L'outil PITR reconstruit la base de données au niveau des événements MariaDB, mais ne remplace pas la validation fonctionnelle de l'application.
Attention opérationnelle et meilleures pratiques
La première erreur à éviter est de stocker les sauvegardes et les journaux binaires sur le même système de fichiers sans réplication externe. En cas de panne de disque, d'attaque par rançongiciel ou de suppression accidentelle côté système, vous risquez de perdre à la fois l'instantané et le journal des modifications. Une politique adéquate doit inclure des sauvegardes locales pour une restauration rapide, des copies distantes pour la reprise après sinistre et un stockage cohérent des journaux binaires.
Le deuxième point concerne les tests périodiques. Une stratégie de sauvegarde non testée n'est qu'un vœu pieux . Il est indispensable de tester régulièrement la restauration dans un environnement distinct, de mesurer les temps de restauration en temps réel, de vérifier la présence des journaux binaires et de s'assurer que la procédure atteint bien le point temporel souhaité. Cela permet d'estimer les RTO et RPO de manière réaliste, et non théorique.
Le troisième aspect concerne la sécurité. Les sauvegardes MariaDB contiennent des données sensibles, des mots de passe d'applications, des informations personnelles et des données transactionnelles. Elles doivent être protégées par des autorisations strictes, le chiffrement lorsque nécessaire, un accès limité et des procédures de rotation. Le plan de reprise après sinistre (PITR) est une mesure de continuité d'activité, mais il ne doit pas devenir une nouvelle source de risques.
Enfin, il est important de rappeler que les journaux binaires consomment des ressources. Ils occupent de l'espace disque, génèrent des E/S et doivent être surveillés. Dans les environnements à forte activité d'écriture, la taille des journaux binaires peut augmenter considérablement. Il est donc conseillé de configurer des alertes d'espace disque insuffisant, d'automatiser la copie des journaux à distance et de définir des durées de conservation compatibles avec le cycle de sauvegarde.
conclusion
La restauration à un point précis dans le temps est une procédure essentielle pour les gestionnaires de bases de données de production dans MariaDB. Il ne suffit pas de pouvoir restaurer une sauvegarde : il est impératif de pouvoir restaurer les données exactes, au moment opportun, afin d'éviter toute perte de données excessive et tout retour à un état déjà compromis.
La combinaison de mariadb-backup, logarithme binaire et mariadb-binlog Elle vous permet de mettre en place une stratégie de récupération robuste et relativement simple à automatiser. La sauvegarde physique offre une base solide et fiable. xtrabackup_binlog_info indique le point de départ et les journaux binaires permettent de réappliquer les modifications jusqu'à la date souhaitée.
La véritable différence ne réside cependant pas dans la commande utilisée lors de l'urgence, mais dans la préparation en amont : journaux binaires actifs, conservation appropriée des données, copies distantes, sauvegardes planifiées, procédures documentées et tests périodiques. Le plan de reprise après incident (PITR) n'est efficace que s'il a été conçu avant l'incident. Après coup, au mieux, il est possible de déterminer si la stratégie de sauvegarde était réellement adéquate.
Pour des détails d'implémentation plus précis, il est également utile de consulter la documentation officielle de MariaDB sur la récupération à un point précis dans le temps avec mariadb-backup , le guide sur la sauvegarde et la restauration avec mariadb-backup et la référence mariadb-binlog.
