La réalisation de sauvegardes incrémentielles 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 disposez d'une base de données de production fiable. Parce que vous devez disposer de 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 trouvé aucun outil capable de faire une sauvegarde incrémentielle complète pour MyISAM et InnodB dans un environnement mixte où les applications utilisent les deux moteurs de base de données simultanément (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 toute simplicité et utilisation généralisée.
Mécanisme
Nous utilisons les fonctionnalités Post- et Pre d'automysqlbackup pour effectuer une sauvegarde incrémentielle. Avant de démarrer une sauvegarde complète, mysql-backup-pre exécute une requête pour verrouiller l'ensemble de la base de données pendant le processus de sauvegarde, car nous devons geler le binlog pour éviter tout changement pendant l'exécution de la sauvegarde. Le nom et la position du binlog peuvent ne pas changer pendant la sauvegarde. La position du journal binaire est très cruciale dans le processus de sauvegarde incrémentielle ultérieur et sera utilisée comme point de départ pour commencer la prochaine sauvegarde incrémentielle. Une fois la sauvegarde complète terminée, mysql-backup-post supprime le verrou de la base de données.
Requête de verrouillage :FLUSH TABLES WITH READ LOCK ; SÉLECTIONNER SOMMEIL(86400)
Rechercher les requêtes de verrouillage :mysql -u[nom d'utilisateur] -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
- paquet 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 :
download the package from https://sourceforge.net/projects/automysqlbackup/
tar -xzf [PathYouSavedTarFile] -C /tmp/
cd /tmp/
./install.sh
Lors de l'installation d'automysqlbackup, il vous sera demandé le chemin d'automysqlbackup.conf et son binaire, vous pouvez laisser les valeurs par défaut sans aucune modification.
rm /etc/automysqlbackup/myserver.conf
Installez 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 fichier automysqlbackup.conf :
Recherchez les paramètres ci-dessous, décommentez-les et modifiez-les :
CONFIG_mysql_dump_username='Mysql user name. It must has privileges to get Lock'
CONFIG_mysql_dump_password='Password'
CONFIG_backup_dir='The backup directory you want to store full and incremental backup'
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"
Mettre à jour mon.cnf :
Modifiez le fichier de configuration MySQL :
nano /etc/mysql/my.cnf
1- Format BinLog
En raison de certaines limitations du format STATEMENT, ma recommandation est de définir le format basé sur ROW. Pour plus d'informations, veuillez consulter la section "Dépannage" de ce guide. Vous pouvez vérifier le type de format de journal binaire en exécutant "select @@binlog_format;" requête. Pour modifier le format de 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 avez l'intention d'avoir les modifications associées dans le journal binaire. Veuillez noter que si vous ne spécifiez aucune base de données, toute modification apportée à une base de données sera consignée dans le journal binaire. Dans ce cas, si vous avez choisi le format STATEMENT, vous rencontrez peut-être des problèmes lors de la restauration à partir de fichiers de sauvegarde incrémentiels et de 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 avoir des fichiers journaux binaires 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
Répertoire dans lequel les journaux binaires seront stockés. Dans les anciennes versions de MySQL, mysql-incremenetal peut ne pas être en mesure de trouver le bon chemin. Donc, si vous obtenez une erreur à ce sujet après l'exécution de 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 mysql-incremental backup sur un serveur esclave, vous devez activer cette option. Normalement, un esclave n'enregistre pas les mises à jour dans son propre journal binaire car elles ont été reçues d'un serveur maître. Cette option indique à l'esclave de consigner 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écuter 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 informations nouvellement ajoutées sur la sauvegarde quotidienne. Pour plus de détails sur l'erreur, consultez /var/log/Backup_Post_Pre_log . Le fichier de sauvegarde sera stocké dans le répertoire /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/ .
Exécuter mysql-incremental
Exécutez mysql-incremental manuellement maintenant pour avoir au moins une sauvegarde par heure.
mysql-incremental
En cas d'erreur, les détails sont consignés dans le fichier "/var/log/Backup_Incremental_Log" . Les fichiers de sauvegarde incrémentielle seront stockés dans le répertoire /[BackupDirInAutomysqlbackup]/IncrementalBackup/ .
Modifier la crontab racine
Vous pouvez programmer mysql-incremental pendant plus d'une heure. Vous pouvez trouver la durée totale de la sauvegarde complète à partir de backup_status, puis, en fonction de cette valeur, vous définissez une heure de planification précise. Bien sûr, la sauvegarde incrémentielle mysql dispose d'un mécanisme pour trouver toute sauvegarde complète en cours d'exécution avant le démarrage, il n'y a donc pas de problème de conflit entre la sauvegarde incrémentielle 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
Afin de restaurer jusqu'à un moment précis (récupération à un moment donné), vous devez d'abord restaurer une sauvegarde quotidienne complète, puis restaurer les fichiers de sauvegarde incrémentielle séquentiellement associés. Pour clarifier davantage, voici les étapes pour récupérer la base de données testDB. Dans un exemple de scénario, nous avons l'intention de récupérer nos données jusqu'au 01/05/2015 à 2 heures du matin. nous avons défini /backup comme répertoire de sauvegarde principal et testDB comme 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
Remarques importantes et dépannage
MySQL prend en charge différents formats pour le journal binaire. Certaines versions de Mysql utilisent le format "basé sur les déclarations" comme format binlog, ce type de binlog présente certaines limitations auxquelles nous devons prêter une attention particulière lorsque nous avons l'intention de l'utiliser dans une procédure de sauvegarde incrémentielle. Lorsque mysql est défini sur le format de base d'instructions, il ne peut pas filtrer correctement en fonction des bases de données. Si vous définissez 'USE ou \u' pour changer de base de données, puis mettez à jour une autre base de données qui n'est pas incluse dans binlog-do-db, l'instruction sera consignée dans le fichier binlog indiquant qu'il n'est pas souhaitable ! et exposera un problème lors de la restauration basée sur une base de données spécifique et également si vous passez à 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, l'instruction ne sera pas enregistrée dans fichier binlog. notre objectif d'ajouter 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 plus en détail ce problème avec les scénarios ci-dessous :
databases:
- binlog
- person (table)
- binlog2
- person (table)
binlog-do-db=binlog2 (it is supposed only change of this database are logged to binlog file)
--------Scenario 1---------
\u binlog2
insert into person (data) values ('17') ---> loged in binlog *desired state*
insert into binlog.person (data) values ('25'); ---> logged in binlog (target database is 'binlog' ) *undesired state*
--------Scenario 2---------
\u binlog
insert into person (data) values ('17') ---> is not logged in binlog *desired state*
insert into binlog2.person (data) values ('25'); ---> is not logged in binlog (target database is 'binlog2' ) *undesired state* because the binlog2 database
is begin changed, so we want to have this change,but it will not logged in logbin file
--------Scenario 3---------
if you just connect to database without any USE or \u statement, all of updates on any databases will be logged, but mysqlbinlog can not able to filter
based on specific database, so that is not desirable state for our purpose in incremental backup. Using USE or \u before executing update queries, is very
important. Because mysqlbinlog finds update queries based on USE statement in binlog file. Contourner le problème mentionné
1) En définissant les utilisateurs sur les 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 de l'application) et lors de la connexion à 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 y 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 à utiliser "\USE ou \u ".
2) Si vous utilisez le format binlog basé sur les lignes, tous les problèmes mentionnés auront disparu. en d'autres termes, le format basé sur les lignes est une méthode beaucoup plus appropriée pour binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html
Fichiers journaux
J'ai essayé de tout consigner 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 les informations détaillées sur la sauvegarde et la fin de la sauvegarde (les heures sont au format Unix Time). Il contient le nom du binlog et la position de l'heure à laquelle 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 d'informations_de_sauvegarde :
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 :
1th) 1431043501 : indicates the time when the backup has been finished. You can run date --date @1431043501 command on the server the backup has been done to view it in human readable format. 2th) Mysql-bin.000026 : indicates the binary log name that backup up to this file has been done. 3th) 120 : indicates the position of binlog that backup up to this position in binary log has been done. 4th) Daily/Hourly: indicates type of backup. Daily does mean the full backup by automysqlbackup script and Hourly is done by mysql-incremental script. 5th) 2015-05-08: The date that backup has been done. This date will be used in creating directory for incremental backup and also as a base for restore hourly backups. In restoring procedure, first a full backup is restored and then sequentially other incremental backup are restored. 6th) 0 : indicates number of backups from previous full backup. 0 does mean the backup is full and others mean hourly. This number is very important in restoring procedure. 7th) 24: The backup duration in second.
Rapport de bogue
Vous pouvez signaler des bogues ou donner vos suggestions et avis sur https://sourceforge.net/projects/mysqlincrementalbackup .