Wie man MySQL-Datenbanken mit 5 verschiedenen Methoden sichert?

Da immer mehr Daten in der Datenbank gespeichert werden, wird die Datensicherung immer wichtiger. Die Sicherung von Datenbanken ist eine sehr wichtige Aufgabe, um sicherzustellen, dass Unternehmen oder private Personen aufgrund von Datenverlusten keine großen Verluste erleiden. MySQL ist ein weit verbreitetes Open-Source-Relationales-Datenbanksystem, das in Unternehmen häufig eingesetzt wird. Dieser Text wird erläutern, wie man eine MySQL-Datenbank sichert.

Arten der MySQL-Datenbank-Backup

Aufgrund der Größe der Datenbank gibt es vier Arten von Backups, die in verschiedenen Situationen verwendet werden:

Vollständige Sicherung

Dies ist eine von den meisten Menschen verwendete Methode. Sie kann die gesamte Datenbank einschließlich aller Datenbankobjekte wie Benutzertabellen, Systemtabellen, Indizes, Sichten und gespeicherte Prozeduren sichern. Allerdings benötigt sie mehr Zeit und Platz, daher wird generell empfohlen, einmal wöchentlich ein vollständiges Backup durchzuführen.

Transaktionsprotokoll-Sicherung

Das Transaktionsprotokoll ist eine separate Datei, die Änderungen an der Datenbank protokolliert. Sicherungen müssen nur die seit der letzten Sicherung vorgenommenen Änderungen kopieren, daher dauert dies sehr wenig Zeit. Für die Stabilität der Datenbank werden stündliche oder sogar häufigere Sicherungen des Transaktionsprotokolls empfohlen.

Differenzial-Backup

Es ist eine weitere Methode, um nur einen Teil der Datenbank zu sichern. Sie verwendet nicht das Transaktionsprotokoll, sondern ein neues Abbild der gesamten Datenbank. Es ist kleiner als die ursprüngliche vollständige Sicherung, da es nur die seit der letzten vollständigen Sicherung veränderten Datenbanken enthält. Es hat den Vorteil, dass Speicherung und Wiederherstellung schnell sind. Es wird empfohlen, täglich eine differentielle Sicherung durchzuführen.

Dateisicherung

Eine Datenbank kann aus vielen Dateien auf der Festplatte bestehen. Wenn die Datenbank sehr groß ist und nicht über Nacht gesichert werden kann, können Dateisicherungen verwendet werden, um jeden Abend einen Teil der Datenbank zu sichern. Da Datenbanken in der Regel nicht so groß sind, dass mehrere Dateispeicher verwendet werden müssen, ist diese Art der Sicherung nicht sehr üblich.

Nach dem Status der Datenbank kann diese in drei Arten unterteilt werden:

1. Kaltbackup

Kalte Backups sind Backups, die auf Daten durchgeführt werden, während die Datenbank offline und für Benutzer nicht zugänglich ist. Da die Backups während der Offline-Zeit der Datenbank durchgeführt werden, werden kalte Backups auch oft als Offline-Backups bezeichnet. Kalte Backups beanspruchen oft weniger Ressourcen und da keine neuen Daten hinzugefügt werden können (die Datenbank ist offline), kann der Backup-Prozess alle Daten auf einmal sichern. Kalte Backups haben jedoch die Einschränkung, dass die Datenbank während des Backup-Vorgangs nicht zugänglich sein kann.

2. Heiße Sicherungskopie

Hot-Backups sind Sicherungen, die während der Online-Zeit und der Verfügbarkeit der Datenbank für Benutzer durchgeführt werden. Hot-Backups sind oft eine sehr praktische Lösung, da sie keine Downtime erfordern und die Störungen des Betriebssystems minimal sind, aber es gibt auch bestimmte Nachteile: Wenn die während der Sicherung gesicherten Daten auf irgendeine Weise verändert werden, könnte die Sicherung nicht mit der endgültigen Version der Daten in der Datenbank übereinstimmen.

3. Logische Sicherung

Logische Sicherung verwendet Software, um Daten aus einer Datenbank zu extrahieren und die Ergebnisse in eine Datei zu schreiben.

Wie man MySQL-Datenbanken sichert

Es gibt logische und physische Sicherungen für MySQL je nach Wiederherbeimethode. Eine logische Sicherung beinhaltet das Sichern von SQL-Anweisungen und deren Ausführung während der Wiederherstellung, um die Datenbankdaten zu reproduzieren. Eine physische Sicherung kopiert die Datenbankdateien im gleichen Format, wie sie auf der Festplatte gespeichert sind.

Diese beiden Arten von Backups haben jeweils ihre Vor- und Nachteile. Im Allgemeinen ist die Wiederherstellungsgeschwindigkeit der physischen Sicherung schneller, aber sie beansprucht mehr Speicherplatz, während die Geschwindigkeit der logischen Sicherung langsamer ist, jedoch weniger Speicherplatz benötigt.

1. Verwenden von mysqldump

Mysqldump ist ein Backup-Tool, das mit MySQL geliefert wird, im Verzeichnis: /usr/local/mysql/bin/mysqldump, das eine heiße Sicherung auf Basis von InnoDB unterstützt. Da es sich jedoch um eine logische Sicherung handelt, ist die Geschwindigkeit nicht sehr hoch und es eignet sich für Szenarien, in denen die zu sichernden Daten relativ klein sind. Eine vollständige Mysqldump-Sicherung + Binärlog ermöglicht eine Wiederherstellung zu einem bestimmten Zeitpunkt.

Für die Tabellen des Speicherengines MyISAM kann nur ein Hot Backup verwendet werden. Zu diesem Zeitpunkt sollten Datenbeschreibungen verhindert werden, daher füge zunächst einen Lesesperre hinzu. Zu diesem Zeitpunkt kannst du auch manuell in die Datenbank gehen und eine Lesesperre hinzufügen, aber das ist umständlich. Du kannst direkt eine Sperrungsoption im mysqldump-Tool verwenden, die --lock-all-tables ist, wie zum Beispiel mysqldump --databases test --lock-all- tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql.

Wenn Sie eine einzelne Tabelle sichern möchten, fügen Sie den Tabellennamen nach dem Bibliotheksnamen test hinzu.

Für InnoDB-Speichermodultabellen kann ein Hot-Backup ohne die Sperren der Datenbank durchgeführt werden. Fügen Sie eine Option hinzu, um das Hot-Backup durchzuführen: --single-transaction, zum Beispiel: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql.

P.S. Vergiss nicht das Binärprotokoll beim Wiederherstellen zu schließen:

mysql> set sql_log_bin=0;

Da dies auf einer logischen Backup-Methode basiert, wird die Ausführung von SQL Daten einfügen und diese im Binärlog vermerken. Da dies eine Wiederherstellung ist, kann das Einfügen des Binärlogs deaktiviert werden, um die Wiederherstellungszeit zu verkürzen.

Klicken Sie und erfahren Sie mehr über MySQL-Datenbank-Backup und -Wiederherstellung mit und ohne Mysqldump.

2. Backup auf Basis eines LVM-Snapshots

Was physisches Backup betrifft, gibt es ein physisches Backup basierend auf dem Dateisystem (LVM-Snapshot), und Sie können auch Befehle wie tar verwenden, um das gesamte Datenbankverzeichnis zu verpacken und zu sichern, aber diese Methoden können nur für kaltes Backup verwendet werden. Verschiedene Speicher-Engines sichern unterschiedliche Inhalte. MyISAM sichert automatisch auf Tabellenebene, während innoDB nur die gesamte Datenbank sichern kann, wenn keine unabhängige Tabellenräume aktiviert sind.

Das Folgende ist eine Einführung in die Verwendung der Schnappschussfunktion von LVM für Backups.

1) Aus Sicherheitsgründen legen Sie zuerst einen Leseschlussel auf die Datenbank

mysql>FLUSH TABLES WITH READ LOCK

2) Das binäre Protokoll für die Zeitpunktwiederherstellung aktualisieren

mysql>FLUSH LOGS

3) Dann erstellen Sie das Schnappschussvolumen

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) Geben Sie die Datenbank ein, um das Lese-Schloss zu freigeben

UNLOCK TABLES

5) Das Snapshot-Volumen für die Sicherung mounten

mount –r /dev/myvg/data-snap /mnt/snap

6) Verpacken und sichern Sie die Dateien unter /mnt/snap

Für das Wiederherstellen:

1) mysqld beenden

2) dann sichern Sie das Binärprotokoll

3) die ursprüngliche Sicherungskopie wiederherstellen

4) dann zur Zeit des Fehlers durch das Binärprotokoll zurückkehren (vergiss nicht das Binärprotokoll vorübergehend zu schließen wenn du den Zeitpunkt über das Binärprotokoll wiederherstellst)

Zwischen 2010 und 2012 wurde die MySQL-Datenbank in der Cloud-Umgebung von Amazon bereitgestellt. Sie boten LVM-Snapshots an, die sehr praktisch und schnell sind. Mit LVM-Snapshots ist auch die Wiederherstellung unter Amazons 10G-Netzwerk extrem schnell.

3. Tar-Sicherung

Bereiten Sie die erste Datenbanksicherung vor, sperren Sie temporär alle Tabellen und öffnen Sie Fenster 1

mysql> flush tables with read lock;

Abfrage erfolgreich, 0 Zeilen betroffen (0.00 Sek.)

mysql> show master status;

+++++
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  
| mysql-bin.000003 | 194554 | | |  
1 row in set (0.00 sec)

Dieses Fenster kann nicht geschlossen werden und muss bis zum Abschluss des Tar-Pakets offen bleiben.

--Re-open a shell window, go to the data file directory tar package
open window 2
[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla
tar -zcvf mysqla1.tar.gz mysqla Compressed
Switch to window 1 and execute the unlock command

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)
mysql>

Kopiere das Tar-Paket auf einen anderen MySQL-Bibliotheksserver, überschreibe das Datenverzeichnis und starte dann den MySQL-Datenbankdienst neu.

4. xTraBackup von Percona

Es unterstützt physische Hot-Backups von InnoDB, vollständige und inkrementelle Backups, und die Geschwindigkeit ist sehr schnell. Es unterstützt die Datenmigration zwischen verschiedenen Datenbanken aufgrund der InnoDB-Speicherung und unterstützt die Sicherung und Wiederherstellung von Slaves im Replikationsmodus. Damit xTraBackup mehr Funktionserweiterungen unterstützt, können Sie einen unabhängigen Tabellenbereich einrichten und die innoDB_file_per_table-Funktion aktivieren. Dann können Sie einzelne Tabellen sichern.

xTraBackup-Prinzip:

xTraBackup basiert auf der Absturzwiederherstellungsfunktion von InnoDB. Es kopiert die Daten-Datei von InnoDB. Da die Tabelle nicht gesperrt ist, sind die kopierten Daten inkonsistent. Bei der Wiederherstellung wird die Absturzwiederherstellung verwendet, um die Daten konsistent zu machen.

innoDB führt ein Redo-Log, auch bekannt als Transaktionslog, welches alle Änderungen an innoDB-Daten enthält. Wenn innoDB startet, wird es zuerst die Datendatei und das Transaktionslog überprüfen und die folgenden Schritte durchführen:

Während xTraBackup ein Backup erstellt, kopiert es die InnoDB-Datenseiten seite für Seite, ohne die Tabelle zu sperren. Gleichzeitig hat xTraBackup einen weiteren Thread, um das Transaktionsprotokoll zu überwachen. Sobald sich das Protokoll ändert, wird es die geänderten Protokollseiten kopieren. Da die Größe der Transaktionsprotokolldatei begrenzt ist, beginnt es von vorne zu schreiben, wenn sie voll ist, sodass neue Daten alte Daten überschreiben können.

Während des Vorbereitungsprozesses verwendet xTraBackup das kopierte Transaktionsprotokoll, um eine Absturzwiederherstellung auf der gesicherten InnoDB-Datendatei durchzuführen.

5. MySQL-Datenbanken mit Vinchin Backup & Recovery sichern

Vinchin Backup & Recovery bietet leistungsstarke Funktionen, um Ihre Datenbanken sowohl in virtuellen Maschinen als auch in physischen Servern zu schützen. Durch die gute Zusammenarbeit mit VM-Level-Sicherungen wird den Nutzern von virtuellen Umgebungen für ihre wichtigen Geschäftsdaten und Informationssysteme eine doppelte Sicherheit geboten. Vinchin Backup & Recovery unterstützt den Schutz von Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro und MariaDB, die auf physischen und virtuellen Maschinen installiert sind, mit leistungsstarken Datenbanksicherungs- und -wiederherstellungsfunktionen. Es bietet auch vollständige Sicherung, differenzielle Sicherung, inkrementelle Sicherung und Transaktionsprotokoll-Sicherung, sodass Sie Ihren eigenen Sicherungsplan nach Bedarf erstellen können.

Vinchin Backup & Recovery unterstützt eine effiziente heiße Sicherung von MySQL, SQL Server und Oracle ohne die normale Datenbankoperation zu beeinträchtigen.

Die Operation von Vinchin Backup & Recovery ist sehr einfach, es sind nur ein paar einfache Schritte.

1. Wählen Sie das Sicherungsobjekt aus.

2. Wählen Sie das Sicherungsziel aus.

3. Konfigurieren Sie Sicherungsszenarien.

4. Prüfen und die Aufgabe einreichen.

Sie können mit einer 60-tägigen voll funktionsfähigen Testversion dieses leistungsstarken Systems beginnen. Klicken Sie einfach auf die Schaltfläche, um das Installationspaket zu erhalten. Sie können hier klicken, um mehr über das Backup von MySQL mit Vinchin Backup & Recovery zu erfahren.

Zusammenfassung

Die MySQL-Datenbank wird in vielen Branchen eingesetzt. Um ihre Sicherheit zu gewährleisten, teilen wir Ihnen in diesem Artikel fünf verschiedene Methoden zum Sichern von MySQL-Daten mit. Sie können eine geeignetere Methode nach Ihren tatsächlichen Bedingungen auswählen. Was warten Sie noch? Machen Sie schnell eine Sicherung Ihrer MySQL-Datenbank!