MySQL Inkrementelles Backup - Punkt-in-Zeit Backup und Wiederherstellung von InnoDB und MyIsam Datenbanken

Das Durchführen von inkrementellen Backups ist eine wichtige Anforderung für große Produktionsdatenbanken. Ohne ein sicheres inkrementelles Backup können Sie sich nicht sicher sein, dass Sie eine zuverlässige Produktionsdatenbank haben. Denn Sie müssen genügend Daten haben, um Ihre Datenbank in Notfällen wiederherzustellen. 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 bei der Google-Suche und im Internet). Also beschloss ich, dieses zu schreiben, aber um Zeit zu sparen und von anderen Open-Source-Lösungen zu profitieren, zog ich es vor, diese Funktion zum -automysqlbackup- Skript hinzuzufügen, das das beste Skript für vollständige Backups in Einfachheit und weit verbreiteter Nutzung ist.

Mechanismus

Wir verwenden die Post- und Pre-Funktion von automysqlbackup, um ein inkrementelles Backup durchzuführen. Bevor ein vollständiges Backup gestartet wird, führt mysql-backup-pre eine Abfrage aus, um die gesamte Datenbank während des Backup-Prozesses zu sperren, da wir das Binlog einfrieren müssen, um Änderungen während des laufenden Backups zu vermeiden. Der Name und die Position des Binlogs dürfen sich während des Backups nicht ändern. Die Position des Binärlogs ist im nachfolgenden inkrementellen Backup-Prozess von entscheidender Bedeutung und wird als Ausgangspunkt verwendet, um das nächste inkrementelle Backup zu beginnen. Nach Abschluss des vollständigen Backups entfernt mysql-backup-post die Datenbanksperre.

Sperr-Abfrage: FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)

Finde Sperr-Abfragen: mysql -u[benutzername] -p[pass] -e “show processlist” | grep “SELECT SLEEP(86400)” | awk ‘{print $1}’

Anforderungen

Root-Rechte zur Installation des Pakets und zur Aktualisierung von mysql.conf
mysql-community-client Paket
Installation von automysqlbackup und mysql-incremental

Installation

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

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

Installieren Sie automysqlbackup:

Laden Sie das Paket von https://sourceforge.net/projects/automysqlbackup/ herunter.

tar -xzf [PfadZuIhremTarFile] -C /tmp/

cd /tmp/

./install.sh

Während der Installation von automysqlbackup werden Sie nach dem Pfad von automysqlbackup.conf und dessen Binärdatei gefragt, Sie können die Standardwerte ohne Änderungen lassen.

rm /etc/automysqlbackup/myserver.conf

Installieren Sie das 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-Benutzername. Er muss Berechtigungen haben, um die Sperre zu erhalten'
    CONFIG_mysql_dump_password='Passwort'
    CONFIG_backup_dir='Das Backup-Verzeichnis, in dem Sie vollständige und inkrementelle Backups speichern möchten'
    CONFIG_db_names=('DatenbankName1' 'DatenbankName2' )
    CONFIG_db_month_names=('DatenbankName1' 'DatenbankName2' )
    CONFIG_mysql_dump_master_data=2
    CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre"
    CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"

Aktualisieren Sie my.cnf:

Bearbeiten Sie die MySQL-Konfigurationsdatei:

nano /etc/mysql/my.cnf

1- BinLog-Format

Aufgrund einiger Einschränkungen im STATEMENT-Format empfehle ich, das ROW-basierte Format einzustellen. Für weitere Informationen siehe den Abschnitt ‘Fehlerbehebung’ in diesem Howto. Sie können den Typ des Binärlogformats überprüfen, indem Sie die Abfrage “ select @@binlog_format; “ ausführen. Um das logbin-Format zu ändern, müssen Sie binlog_format = ROW zu mysql.conf oder my.cnf hinzufügen.

2- binlog_do_db

Sie müssen die Datenbanken angeben, bei denen Sie die entsprechenden Änderungen im Binärlog haben möchten. Bitte beachten Sie, dass, wenn Sie keine Datenbank angeben, jede Änderung an jeder Datenbank im Binärlog protokolliert wird. In diesem Fall, wenn Sie das STATEMENT-Format gewählt haben, könnten Sie Probleme beim Wiederherstellen aus inkrementellen Backups und Binlog-Dateien haben. Sie können Datenbanken zu dieser Option hinzufügen:

binlog_do_db = DATENBANKNAME1
binlog_do_db = DATENBANKNAME2

3- expire_logs_days

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

4- log-bin

Das Verzeichnis, in dem die Binärlogs gespeichert werden. In alten MySQL-Versionen konnte mysql-incremental möglicherweise den richtigen Pfad nicht finden. Wenn Sie also nach der Ausführung von mysql-incremental einen Fehler erhalten, müssen Sie das mysql-incremental-Skript aktualisieren und den Pfad des Binärlogs festlegen.

5- log_slave_updates

Wenn Sie ein mysql-incremental Backup auf einem Slave-Server einrichten, müssen Sie diese Option aktivieren. Normalerweise protokolliert ein Slave keine Updates in seinem eigenen Binärlog, da sie von einem Master-Server empfangen wurden. Diese Option weist den Slave an, die von seinen SQL-Threads durchgeführten Updates 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

Führen Sie automysqlbackup aus

Führen Sie automysqlbackup manuell aus, um mindestens ein vollständiges Backup Ihrer angegebenen Datenbanken zu haben.

automysqlbackup

Nach erfolgreicher Ausführung des Befehls überprüfen Sie die Datei /[BackupDirInAutomysqlbackup]/status/backup_info auf die neu hinzugefügten Informationen über das tägliche Backup. Für Fehlerdetails überprüfen Sie /var/log/Backup_Post_Pre_log. Die Backup-Datei wird im Verzeichnis /[BackupDirInAutomysqlbackup]/daily/[DatenbankName]/ gespeichert.

Führen Sie mysql-incremental aus

Führen Sie jetzt mysql-incremental manuell aus, um mindestens ein stündliches Backup zu haben.

mysql-incremental

Im Falle eines Fehlers werden die Details in der Datei “/var/log/Backup_Incremental_Log” protokolliert. Die inkrementellen Backup-Dateien werden im Verzeichnis /[BackupDirInAutomysqlbackup]/IncrementalBackup/ gespeichert.

Bearbeiten Sie die Root-Crontab

Sie können mysql-incremental für mehr als eine Stunde planen. Sie können die Gesamtzeit des vollständigen Backups aus backup_status entnehmen und basierend auf diesem Wert eine genaue Zeitplanung festlegen. Natürlich hat das mysql-incremental Backup einen Mechanismus, um ein laufendes vollständiges Backup vor dem Start zu finden, sodass es keine Bedenken hinsichtlich von Konflikten zwischen inkrementellen und vollständigen Backups gibt.

crontab -e

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

Datenbank wiederherstellen

Um bis zu einem bestimmten Zeitpunkt (Punkt-in-Zeit-Wiederherstellung) wiederherzustellen, müssen Sie zuerst ein vollständiges tägliches Backup wiederherstellen und dann die entsprechenden inkrementellen Backup-Dateien nacheinander wiederherstellen. Um es klarer zu machen, hier sind die Schritte zur Wiederherstellung der testDB-Datenbank. In einem Beispielszenario beabsichtigen wir, unsere Daten bis zum 2015-5-01 um 2 Uhr morgens wiederherzustellen. Wir haben /backup als unser Haupt-Backup-Verzeichnis und testDB als unsere Zieldatenbank festgelegt:

1- mysql -u root -p DatenbankName < /backup/daily/testDB/daily_DatenbankName_2015-05-16_00h05m_Samstag.sql.gz
2- mysql -u root -p DatenbankNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.1
3- mysql -u root -p DatenbankNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.2
4- mysql -u root -p DatenbankNAme < /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 ‘statement-based’ als Binlog-Format, das einige Einschränkungen hat, auf die wir bei der Verwendung im inkrementellen Backup-Verfahren achten müssen. Wenn MySQL im statement-basierten Format eingestellt ist, kann es nicht korrekt nach Datenbanken filtern. Wenn Sie ‘USE oder \u’ verwenden, um die Datenbank zu wechseln, und dann eine andere Datenbank aktualisieren, die nicht in binlog-do-db enthalten ist, wird die Anweisung im Binlog-Datei protokolliert, was nicht wünschenswert ist! und wird einige Probleme beim Wiederherstellen 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 im Binlog-Datei protokolliert. Unser Ziel, Datenbanken zu binlog-do-db hinzuzufügen, besteht darin, basierend auf der Datenbank zu filtern, aber es funktioniert nicht wie erwartet. Wenn USE oder \u nicht vor der Ausführung von Abfragen ausgeführt wird, kann mysqlbinlog keine ‘Update-Abfragen’ extrahieren, die sich auf eine Datenbank beziehen. Wir werden dieses Problem mit den folgenden Szenarien näher erläutern:

datenbanken: 
 - binlog
     - person (Tabelle) 
  - binlog2
     - person (Tabelle)

 binlog-do-db=binlog2 (es wird angenommen, dass nur Änderungen dieser Datenbank im Binlog-Datei protokolliert werden)
--------Szenario 1---------
\u binlog2
insert into person (data) values ('17') ---> im Binlog protokolliert  *gewünschter Zustand*
insert into binlog.person (data) values ('25'); ---> im Binlog protokolliert (Zieldatenbank ist 'binlog') *unerwünschter Zustand*
--------Szenario 2---------
\u binlog
insert into person (data) values ('17') ---> wird nicht im Binlog protokolliert  *gewünschter Zustand*
insert into binlog2.person (data) values ('25'); ---> wird nicht im Binlog protokolliert (Zieldatenbank ist 'binlog2') *unerwünschter Zustand*, da die binlog2-Datenbank geändert wird, also wollen wir diese Änderung haben, aber sie wird nicht im logbin-Datei protokolliert
--------Szenario 3---------
Wenn Sie sich einfach mit der Datenbank verbinden, ohne eine USE oder \u-Anweisung, werden alle Updates an allen Datenbanken protokolliert, aber mysqlbinlog kann nicht basierend auf einer bestimmten Datenbank filtern, sodass dies kein wünschenswerter Zustand für unser Ziel im inkrementellen Backup ist. Die Verwendung von USE oder \u vor der Ausführung von Update-Abfragen ist sehr wichtig. Denn mysqlbinlog findet Update-Abfragen basierend auf der USE-Anweisung im Binlog-Datei.

Umgehung für das genannte Problem

Durch die Definition von Benutzern auf Datenbanken, sodass jeder Benutzer nur Zugriff auf eine Datenbank hat, um sie zu aktualisieren (Anwendungsbenutzer), und beim Verbinden mit der Datenbank 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 in diesem Fall kein Kreuzzugriff auf Datenbanken besteht und es keine Bedenken hinsichtlich der Verwendung von “\USE oder \u” gibt.
Wenn Sie das zeilenbasierte Binlog-Format verwenden, sind alle genannten Probleme verschwunden. Mit anderen Worten, das zeilenbasierte Format ist eine viel geeignetere Methode für das 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
/[SpezifiziertesBackupDirInAutomysqlbackup.conf]/status/backup_info

Die Datei “backup_info” enthält detaillierte Informationen über das Backup und wann das Backup abgeschlossen wurde (Zeiten sind im Unix-Zeitformat). Sie enthält den Binlog-Namen und die Position des Zeitpunkts, an dem das Backup gestartet wurde, den Typ des Backups, die Anzahl der Backups seit dem letzten vollständigen Backup und die Dauer des Backups.

Beispiel backup_info:

1431043501,mysql-bin.000026,120,Täglich,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Stündlich,2015-05-08,1,1

Hier sind die Beschreibungen der verschiedenen Werte:

 1.) 1431043501 : zeigt die Zeit an, zu der das Backup abgeschlossen wurde. Sie können den Befehl date --date @1431043501 auf dem Server ausführen, auf dem das Backup durchgeführt wurde, um es in einem menschenlesbaren Format anzuzeigen.
 2.) Mysql-bin.000026 : zeigt den Binärlog-Namen an, bis zu dem das Backup durchgeführt wurde.
 3.) 120 : zeigt die Position des Binlogs an, bis zu der das Backup im Binärlog durchgeführt wurde.
 4.) Täglich/Stündlich: zeigt den Typ des Backups an. Täglich bedeutet das vollständige Backup durch das automysqlbackup-Skript und Stündlich wird durch das mysql-incremental-Skript durchgeführt.
 5.) 2015-05-08: Das Datum, an dem das Backup durchgeführt wurde. Dieses Datum wird verwendet, um das Verzeichnis für das inkrementelle Backup zu erstellen und auch als Basis für die Wiederherstellung stündlicher Backups. Im Wiederherstellungsverfahren wird zuerst ein vollständiges Backup wiederhergestellt und dann nacheinander andere inkrementelle Backups wiederhergestellt.
 6.) 0 : zeigt die Anzahl der Backups seit dem letzten vollständigen Backup an. 0 bedeutet, dass das Backup vollständig ist, und andere bedeuten stündlich. Diese Zahl ist im Wiederherstellungsverfahren sehr wichtig.
 7.) 24: Die Backup-Dauer in Sekunden.

Fehlerbericht

Sie können Fehler melden oder Ihre Vorschläge und Bewertungen unter https://sourceforge.net/projects/mysqlincrementalbackup einreichen.