Sauvegarde MySQL · 10 min read · Dec 10, 2025

Sauvegarde Incrémentale MySQL - Sauvegarde et Récupération Point Dans le Temps des Bases de Données InnoDB et MyIsam

Faire des sauvegardes incrémentales est une exigence importante pour les grandes bases de données de production. Sans une sauvegarde incrémentale sécurisée, vous ne pouvez pas vous dire que vous avez une base de données de production fiable. Car vous devez avoir suffisamment de données pour récupérer votre base de données en cas d’urgence. Après quelques recherches sur Internet, je n’ai pas trouvé d’outil capable de faire une sauvegarde incrémentale complète pour MyISAM et InnodB dans un environnement mixte où les applications utilisent simultanément les deux moteurs de base de données (peut-être que je ne suis pas un chercheur expert sur Google et Internet). J’ai donc décidé d’écrire celui-ci, mais pour éviter de perdre du temps et bénéficier d’autres solutions open-source, j’ai préféré ajouter cette fonctionnalité au script -automysqlbackup- qui est le meilleur script pour une sauvegarde complète en simplicité et en utilisation répandue.

Mécanisme

Nous utilisons la fonctionnalité Post- et Pre d’automysqlbackup pour effectuer une sauvegarde incrémentale. Avant de commencer une sauvegarde complète, mysql-backup-pre exécute une requête pour verrouiller toute la base de données pendant le processus de sauvegarde car nous devons geler le binlog pour éviter tout changement pendant que la sauvegarde est en cours. Le nom et la position du binlog ne doivent pas changer pendant la sauvegarde. La position du journal binaire est très cruciale dans le processus de sauvegarde incrémentale ultérieur et sera utilisée comme point de départ pour commencer la prochaine sauvegarde incrémentale. Après avoir terminé la sauvegarde complète, mysql-backup-post supprime le verrou de la base de données.

Requête de verrouillage : FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)

Trouver les requêtes de verrouillage : mysql -u[username] -p[pass] -e “show processlist” | grep “SELECT SLEEP(86400)” | awk ‘{print $1}’

Exigences

  • privilèges root pour installer le package et mettre à jour mysql.conf
  • package mysql-community-client
  • installation automysqlbackup et mysql-incremental

Installation

Installez le package mysql-community-client pour votre distribution.

Remarque : après l’installation de MySQL, vous devez avoir la commande ‘mysqlshow’.

Installez automysqlbackup :

téléchargez le package depuis https://sourceforge.net/projects/automysqlbackup/
tar -xzf [PathYouSavedTarFile] -C /tmp/
cd /tmp/
./install.sh

Lors de l’installation d’automysqlbackup, on vous demandera le chemin de automysqlbackup.conf et son binaire, vous pouvez laisser les valeurs par défaut sans aucun changement.

rm /etc/automysqlbackup/myserver.conf

Installez le mysql-incremental : Téléchargez le package depuis https://sourceforge.net/projects/mysqlincrementalbackup/

cd /tmp  
wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz  
tar xfz mysql-incremental.tar.gz
cp mysql-incremental /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-incremental
cp mysql-backup-post /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-backup-post
cp mysql-backup-pre /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-backup-pre

Mettez à jour le automysqlbackup.conf :

Trouvez les paramètres ci-dessous, décommentez et modifiez-les :

        CONFIG_mysql_dump_username='Nom d'utilisateur Mysql. Il doit avoir des privilèges pour obtenir le verrou'
    CONFIG_mysql_dump_password='Mot de passe'
    CONFIG_backup_dir='Le répertoire de sauvegarde où vous souhaitez stocker la sauvegarde complète et incrémentale'
    CONFIG_db_names=('databaseName1' 'databaseName2' )
    CONFIG_db_month_names=('databaseName1' 'databaseName2' )
    CONFIG_mysql_dump_master_data=2
    CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre"
    CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"

Mettez à jour my.cnf :

Éditez le fichier de configuration MySQL :

nano /etc/mysql/my.cnf

1- Format BinLog

En raison de certaines limitations sur le format STATEMENT, ma recommandation est de définir le format basé sur ROW. Pour plus d’informations, veuillez consulter la section ‘dépannage’ dans ce guide. Vous pouvez vérifier le type de format de journal binaire en exécutant la requête “ select @@binlog_format; “. Pour modifier le format logbin, vous devez ajouter binlog_format = ROW à mysql.conf ou my.cnf.

2- binlog_do_db

Vous devez spécifier les bases de données pour lesquelles vous souhaitez avoir les modifications associées dans le journal binaire. Veuillez noter que si vous ne spécifiez aucune base de données, tout changement sur n’importe quelle base de données sera enregistré dans le journal binaire. Dans ce cas, si vous choisissez le format STATEMENT, vous pourriez avoir des problèmes lors de la restauration à partir de la sauvegarde incrémentale et des fichiers binlog. Vous pouvez ajouter des bases de données à cette option :

binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME2

3- expire_logs_days

Pour conserver les fichiers de journal binaire plus longtemps, vous pouvez augmenter ce paramètre à une valeur plus élevée. Ma recommandation est de 60 jours. Vous devez donc l’ajouter ou le modifier en “ expire_logs_days = 60 “.

4- log-bin

Le répertoire où les journaux binaires seront stockés. Dans les anciennes versions de MySQL, mysql-incremental pourrait ne pas être en mesure de trouver le bon chemin. Donc, si vous obtenez une erreur à ce sujet après avoir exécuté mysql-incremental, vous devez mettre à jour le script mysql-incremental et définir le chemin du journal binaire.

5- log_slave_updates

Si vous configurez une sauvegarde mysql-incremental sur un serveur esclave, vous devez activer cette option. Normalement, un esclave ne journalise pas les mises à jour dans son propre journal binaire comme elles ont été reçues d’un serveur maître. Cette option indique à l’esclave de journaliser les mises à jour effectuées par ses threads SQL dans son propre journal binaire. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

Exécutez automysqlbackup

Exécutez automysqlbackup manuellement pour avoir au moins une sauvegarde complète de vos bases de données spécifiées.

automysqlbackup

Après avoir exécuté la commande avec succès, vérifiez le fichier /[BackupDirInAutomysqlbackup]/status/backup_info pour les nouvelles informations ajoutées concernant la sauvegarde quotidienne. Pour les détails des erreurs, vérifiez /var/log/Backup_Post_Pre_log. Le fichier de sauvegarde sera stocké dans le répertoire /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/.

Exécutez mysql-incremental

Exécutez mysql-incremental manuellement maintenant pour avoir au moins une sauvegarde horaire.

mysql-incremental

En cas d’erreur, les détails sont enregistrés dans le fichier “ /var/log/Backup_Incremental_Log “. Les fichiers de sauvegarde incrémentale seront stockés dans le répertoire /[BackupDirInAutomysqlbackup]/IncrementalBackup/.

Éditez le crontab root

Vous pouvez planifier mysql-incremental pour plus d’une heure. Vous pouvez trouver le temps total de la sauvegarde complète à partir de backup_status et ensuite, en fonction de cette valeur, vous définir un horaire précis. Bien sûr, la sauvegarde incrémentale mysql a un mécanisme pour trouver toute sauvegarde complète en cours avant de commencer, donc il n’y a pas de souci de conflit entre la sauvegarde incrémentale et la sauvegarde complète.

crontab -e
5 00 * * * root /usr/local/bin/automysqlbackup
25 *  * * * root  /etc/automysqlbackup/mysql-incremental

Restaurer la base de données

Pour restaurer jusqu’à un moment spécifique (récupération point dans le temps), vous devez d’abord restaurer une sauvegarde complète quotidienne, puis restaurer séquentiellement les fichiers de sauvegarde incrémentale associés. Pour clarifier davantage, voici les étapes pour récupérer la base de données testDB. Dans un scénario d’exemple, nous avons l’intention de récupérer nos données jusqu’au 2015-5-01 à 2h du matin. Nous avons défini /backup comme notre répertoire principal de sauvegarde et testDB comme notre base de données cible :

1- mysql -u root -p DatabaseName < /backup/daily/testDB/daily_DatabaseName_2015-05-16_00h05m_Saturday.sql.gz
2- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.1
3- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.2
4- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_02h25m.3

Notes importantes et Dépannage

MySQL prend en charge différents formats pour le journal binaire. Certaines versions de Mysql utilisent ‘basé sur les déclarations’ comme format binlog, ce type de binlog a certaines limitations auxquelles nous devons prêter une attention particulière lorsque nous avons l’intention de l’utiliser dans la procédure de sauvegarde incrémentale. Lorsque mysql est configuré en format basé sur les déclarations, il n’est pas capable de filtrer correctement en fonction des bases de données. Si vous définissez ‘USE ou \u’ pour changer de base de données et que vous mettez ensuite à jour une autre base de données qui n’est pas incluse dans binlog-do-db, la déclaration sera enregistrée dans le fichier binlog, ce qui n’est pas un état souhaitable ! et exposera certains problèmes lors de la restauration en fonction d’une base de données spécifique et aussi si vous changez pour une autre base de données qui n’est pas incluse dans binlog-do-db, et mettez à jour une base de données qui est incluse dans binlog-do-db, la déclaration ne sera pas enregistrée dans le fichier binlog. Notre objectif en ajoutant des bases de données à binlog-do-db est de filtrer en fonction de la base de données, mais cela ne fonctionne pas comme prévu. Si USE ou \u n’est pas exécuté avant l’exécution des requêtes, mysqlbinlog ne peut pas extraire les ‘requêtes de mise à jour’ liées à une base de données. Nous expliquerons davantage ce problème avec les scénarios ci-dessous :

databases: 
 - binlog
     - person (table) 
  - binlog2
     - person (table)

 binlog-do-db=binlog2 (il est supposé que seuls les changements de cette base de données sont enregistrés dans le fichier binlog)
--------Scénario 1---------
\u binlog2
insert into person (data) values ('17') ---> enregistré dans le binlog  *état souhaité*
insert into binlog.person (data) values ('25'); ---> enregistré dans le binlog (la base de données cible est 'binlog' ) *état non souhaité*
--------Scénario 2---------
\u binlog
insert into person (data) values ('17') ---> n'est pas enregistré dans le binlog  *état souhaité*
insert into binlog2.person (data) values ('25'); ---> n'est pas enregistré dans le binlog (la base de données cible est 'binlog2' ) *état non souhaité* car la base de données binlog2
est en train d'être modifiée, donc nous voulons avoir ce changement, mais il ne sera pas enregistré dans le fichier logbin
--------Scénario 3---------
s'il vous suffit de vous connecter à la base de données sans aucune déclaration USE ou \u, toutes les mises à jour sur n'importe quelle base de données seront enregistrées, mais mysqlbinlog ne peut pas filtrer
en fonction d'une base de données spécifique, donc ce n'est pas un état souhaitable pour notre objectif dans la sauvegarde incrémentale. Utiliser USE ou \u avant d'exécuter des requêtes de mise à jour est très
important. Parce que mysqlbinlog trouve les requêtes de mise à jour en fonction de la déclaration USE dans le fichier binlog.

Solution de contournement pour le problème mentionné

  1. En définissant des utilisateurs sur des bases de données de manière à ce que chaque utilisateur n’ait accès qu’à une seule base de données à mettre à jour (utilisateur d’application) et lorsque vous vous connectez à la base de données, le nom de la base de données doit être spécifié. Bien sûr, la plupart des applications ont un fichier de configuration dans lequel les informations d’identification et le nom de la base de données sont définis, donc dans ce cas, vous n’aurez pas d’accès croisé sur les bases de données et il n’y aura pas de souci d’utilisation de “\USE ou \u”.

  2. Si vous utilisez le format binlog basé sur les lignes, tous les problèmes mentionnés disparaîtront. En d’autres termes, le format basé sur les lignes est une méthode beaucoup plus appropriée pour le binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

Fichiers de journal

J’ai essayé de tout enregistrer dans un fichier journal afin que vous puissiez trouver suffisamment d’informations dans les journaux :

/var/log/Backup_Post_Pre_log
/var/log/Backup_Incremental_Log
/[SpecifiedBackupDirInAutomysqlbackup.conf]/status/backup_info

Le fichier “ backup_info “ contient des informations détaillées sur la sauvegarde et quand la sauvegarde est terminée (Les heures sont au format Unix Time). Il contient le nom du binlog et la position du point temporel où la sauvegarde a commencé, le type de sauvegarde, le nombre de sauvegardes depuis la dernière sauvegarde complète et la durée de la sauvegarde.

Exemple de backup_info :

1431043501,mysql-bin.000026,120,Daily,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1

Voici la description des différentes valeurs :

 1er) 1431043501 : indique le moment où la sauvegarde a été terminée. Vous pouvez exécuter la commande date --date @1431043501 sur le serveur où la sauvegarde a été effectuée pour la voir au format lisible par l'homme.
 2ème) Mysql-bin.000026 : indique le nom du journal binaire que la sauvegarde jusqu'à ce fichier a été effectuée.
 3ème) 120 : indique la position du binlog que la sauvegarde jusqu'à cette position dans le journal binaire a été effectuée.
 4ème) Quotidien/Horaire : indique le type de sauvegarde. Quotidien signifie la sauvegarde complète par le script automysqlbackup et Horaire est effectué par le script mysql-incremental.
 5ème) 2015-05-08 : La date à laquelle la sauvegarde a été effectuée. Cette date sera utilisée pour créer le répertoire pour la sauvegarde incrémentale et également comme base pour restaurer les sauvegardes horaires. Dans la procédure de restauration, d'abord une sauvegarde complète est restaurée puis séquentiellement d'autres sauvegardes incrémentales sont restaurées.
 6ème) 0 : indique le nombre de sauvegardes depuis la dernière sauvegarde complète. 0 signifie que la sauvegarde est complète et les autres signifient horaire. Ce nombre est très important dans la procédure de restauration.
 7ème) 24 : La durée de la sauvegarde en secondes.

Rapport de bogue

Vous pouvez signaler des bogues ou donner vos suggestions et avis à https://sourceforge.net/projects/mysqlincrementalbackup.

Share: X/Twitter LinkedIn
#Sauvegarde MySQL

Recevez de nouveaux articles dans votre boîte de réception.

Aucun spam. Désabonnez-vous à tout moment.