Inkrementelle MySQL-Sicherung – Point-in-Time-Sicherung und -Wiederherstellung von InnoDB- und MyIsam-Datenbanken

Das Durchführen inkrementeller Sicherungen ist eine wichtige Anforderung für große Produktionsdatenbanken. Ohne ein sicheres inkrementelles Backup können Sie sich nicht sicher sein, dass Sie über eine zuverlässige Produktionsdatenbank verfügen. Denn Sie müssen genügend Daten haben, um Ihre Datenbank im Notfall wiederherstellen zu können. Nach einiger Suche im Internet konnte ich kein Tool finden, das ein vollständiges inkrementelles Backup für MyISAM und InnodB in einer gemischten Umgebung durchführen kann, in der Anwendungen beide Datenbank-Engines gleichzeitig verwenden (vielleicht bin ich kein Experte für Google und das Internet). Also habe ich beschlossen, dieses zu schreiben, aber um keine Zeit zu verschwenden und von anderen Open-Source-Lösungen zu profitieren, habe ich es vorgezogen, diese Funktion zu -automysqlbackup- script hinzuzufügen, das das beste Skript für eine vollständige Sicherung ist, da es einfach und weit verbreitet ist.

Mechanismus

Wir verwenden die Post- und Pre-Funktion von automysqlbackup, um eine inkrementelle Sicherung durchzuführen. Vor dem Start einer vollständigen Sicherung führt mysql-backup-pre eine Abfrage aus, um die gesamte Datenbank während des Sicherungsvorgangs zu sperren, da wir das Binlog einfrieren müssen, um Änderungen während der Sicherung zu vermeiden. Der Name und die Position des Binlogs dürfen sich während der Sicherung nicht ändern. Die Position des Binärprotokolls ist im nachfolgenden inkrementellen Backup-Prozess sehr wichtig und wird als Ausgangspunkt für den Beginn des nächsten inkrementellen Backups verwendet. Nach Abschluss der vollständigen Sicherung entfernt mysql-backup-post die Datenbanksperre.

Sperrabfrage:FLUSH TABLES WITH READ LOCK; RUHE WÄHLEN (86400)

Find Lock Queries:mysql -u[username] -p[pass] -e "show processlist" | grep "SELECT SLEEP(86400)" | awk '{print $1}'

Anforderungen

• Root-Rechte zum Installieren des Pakets und Aktualisieren von mysql.conf
• mysql-community-client-Paket
• Installation von automysqlbackup und mysql-incremental

Installation

Installieren Sie das Paket mysql-community-client für Ihre Distribution.

Hinweis:Nach der MySQL-Installation müssen Sie den Befehl 'mysqlshow' haben.

Installieren Sie automysqlbackup:

download the package from https://sourceforge.net/projects/automysqlbackup/

tar -xzf [PathYouSavedTarFile] -C /tmp/

cd /tmp/

./install.sh

Während der Installation von automysqlbackup werden Sie nach dem Pfad von automysqlbackup.conf und seiner Binärdatei gefragt, Sie können die Standardeinstellungen unverändert lassen.

rm /etc/automysqlbackup/myserver.conf

Installieren Sie mysql-incremental:Laden Sie das Paket von https://sourceforge.net/projects/mysqlincrementalbackup/

herunter

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

Aktualisieren Sie die automysqlbackup.conf:

Finden Sie die folgenden Parameter, kommentieren Sie sie aus und ändern Sie sie:

        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"

Meine.cnf aktualisieren:

Bearbeiten Sie die MySQL-Konfigurationsdatei:

nano /etc/mysql/my.cnf

1- BinLog-Format

Aufgrund einiger Einschränkungen des STATEMENT-Formats empfehle ich, das ROW-basierte Format festzulegen. Weitere Informationen finden Sie im Abschnitt „Fehlerbehebung“ in diesem Howto. Sie können den Typ des binären Protokollformats überprüfen, indem Sie "select @@binlog_format;" ausführen. Anfrage. Um das Logbin-Format zu ändern, müssen Sie mysql.conf oder my.cnf binlog_format =ROW hinzufügen.

2- binlog_do_db

Sie müssen die Datenbanken angeben, für die Sie die zugehörigen Änderungen im Binärlog haben möchten. Bitte beachten Sie, wenn Sie keine Datenbank angeben, wird jede Änderung an einer Datenbank im Binärprotokoll protokolliert. Wenn Sie in diesem Fall das STATEMENT-Format gewählt haben, haben Sie möglicherweise Probleme bei der Wiederherstellung aus inkrementellen Backup- und Binlog-Dateien. Sie können dieser Option Datenbanken hinzufügen:

binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME2

3- expire_logs_days

Um binäre Protokolldateien länger zu haben, können Sie diesen Parameter auf einen höheren Wert erhöhen. Meine Empfehlung sind 60 Tage. Sie müssen es also zu „expire_logs_days =60“ hinzufügen oder ändern.

4- Log-Bin

Das Verzeichnis, in dem die Binärlogs gespeichert werden. In alten MySQL-Versionen kann mysql-inkremental möglicherweise nicht den richtigen Pfad finden. Wenn Sie also nach der Ausführung von mysql-incremental eine Fehlermeldung dazu erhalten, müssen Sie das mysql-incremental-Skript aktualisieren und den Pfad für das Binärprotokoll festlegen.

5- log_slave_updates

Wenn Sie eine mysql-inkrementelle Sicherung auf einem Slave-Server einrichten, müssen Sie diese Option aktivieren. Normalerweise protokolliert ein Slave keine Aktualisierungen in seinem eigenen Binärprotokoll, da sie von einem Master-Server empfangen wurden. Diese Option weist den Slave an, die von seinen SQL-Threads durchgeführten Aktualisierungen in seinem eigenen Binärlog zu protokollieren. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

Automysqlbackup ausführen

Führen Sie automysqlbackup manuell aus, um mindestens eine vollständige Sicherung Ihrer angegebenen Datenbanken zu erhalten.

automysqlbackup

Überprüfen Sie nach erfolgreicher Ausführung des Befehls die Datei /[BackupDirInAutomysqlbackup]/status/backup_info auf die neu hinzugefügten Informationen zur täglichen Sicherung. Einzelheiten zu Fehlern finden Sie unter /var/log/Backup_Post_Pre_log . Die Sicherungsdatei wird im Verzeichnis /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/ .

gespeichert

mysql-incremental ausführen

Führen Sie mysql-incremental jetzt manuell aus, um mindestens eine stündliche Sicherung zu haben.

mysql-incremental

Im Fehlerfall werden die Details in der Datei „/var/log/Backup_Incremental_Log“ protokolliert. Die inkrementellen Sicherungsdateien werden im Verzeichnis /[BackupDirInAutomysqlbackup]/IncrementalBackup/ .

gespeichert

Root-Crontab bearbeiten

Sie können mysql-incremental für mehr als eine Stunde planen. Sie können die Gesamtzeit der vollständigen Sicherung von backup_status finden und dann basierend auf diesem Wert eine genaue Zeitplanzeit festlegen. Natürlich hat mysql-inkrementelles Backup einen Mechanismus, um jedes laufende vollständige Backup vor dem Start zu finden, so dass es keine Bedenken hinsichtlich eines Konflikts zwischen inkrementellem und vollständigem Backup gibt.

crontab -e

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

Datenbank wiederherstellen

Um bis zu einem bestimmten Zeitpunkt wiederherzustellen (Point-in-Time-Wiederherstellung), müssen Sie zuerst eine vollständige tägliche Sicherung wiederherstellen und dann nacheinander zugehörige inkrementelle Sicherungsdateien wiederherstellen. Um mehr zu verdeutlichen, hier sind die Schritte zum Wiederherstellen der testDB-Datenbank. Im Beispielszenario beabsichtigen wir, unsere Daten bis zum 01.05.2015 um 02:00 Uhr wiederherzustellen. Wir haben /backup als unser Hauptsicherungsverzeichnis und testDB als unsere Zieldatenbank festgelegt:

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

Wichtige Hinweise und Fehlerbehebung

MySQL unterstützt verschiedene Formate für das Binärlog. Einige Mysql-Versionen verwenden „Anweisungsbasiert“ als Binlog-Format, dass diese Art von Binlog einige Einschränkungen hat, die wir genau beachten müssen, wenn wir beabsichtigen, sie in einem inkrementellen Backup-Verfahren zu verwenden. Wenn mysql auf das anweisungsbasierte Format eingestellt ist, kann es basierend auf Datenbanken nicht korrekt filtern. Wenn Sie 'USE or \u' setzen, um die Datenbank zu ändern und dann eine andere Datenbank aktualisieren, die nicht in binlog-do-db enthalten ist, wird die Anweisung in der binlog-Datei protokolliert, dass dies kein wünschenswerter Zustand ist! und wird einige Probleme bei der Wiederherstellung basierend auf einer bestimmten Datenbank aufdecken und auch wenn Sie zu einer anderen Datenbank wechseln, die nicht in binlog-do-db enthalten ist, und eine Datenbank aktualisieren, die in binlog-do-db enthalten ist, wird die Anweisung nicht protokolliert binlog-Datei. Unser Zweck beim Hinzufügen von Datenbanken zu binlog-do-db ist es, basierend auf der Datenbank zu filtern, aber es funktioniert nicht wie erwartet. Wenn USE oder \u vor dem Ausführen von Abfragen nicht ausgeführt wird, kann mysqlbinlog keine 'Aktualisierungsabfragen' extrahieren, die sich auf eine Datenbank beziehen. Wir werden dieses Problem anhand der folgenden Szenarien näher erläutern:

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.

Umgehung des erwähnten Problems

1) Indem Benutzer für Datenbanken so definiert werden, dass jeder Benutzer nur Zugriff auf eine Datenbank zum Aktualisieren hat (Anwendungsbenutzer), und wenn eine Verbindung zur Datenbank hergestellt wird, muss der Name der Datenbank angegeben werden. Natürlich haben die meisten Anwendungen eine Konfigurationsdatei, in der die Anmeldeinformationen und der Name der Datenbank festgelegt sind, sodass Sie in diesem Fall keinen Querzugriff auf Datenbanken haben und keine Bedenken haben, "\USE oder \u ".

2) Wenn Sie das zeilenbasierte Binlog-Format verwenden, werden alle erwähnten Probleme behoben. Mit anderen Worten, das zeilenbasierte Format ist eine viel geeignetere Methode für Binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

Protokolldateien

Ich habe versucht, alles in einer Protokolldatei zu protokollieren, damit Sie genügend Informationen in den Protokollen finden können:

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

Die Datei "backup_info" enthält die detaillierten Informationen über die Sicherung und wann die Sicherung abgeschlossen ist (Zeiten sind im Unix-Zeitformat). Es enthält den Binlog-Namen und die Position des Zeitpunkts, an dem die Sicherung gestartet wurde, die Art der Sicherung, die Anzahl der Sicherungen seit der letzten vollständigen Sicherung und die Dauer der Sicherung.

Beispiel backup_info:

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

Hier sind Beschreibungen der verschiedenen Werte:

 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.

Fehlerbericht

Unter https://sourceforge.net/projects/mysqlincrementalbackup .

können Sie Fehler melden oder Ihre Vorschläge und Bewertungen abgeben