-
MySQL Backup and Recovery Samstag, 30.09.2006 – 14:21 – yejr
MySQL-Sicherung und -Wiederherstellung
Autor/Übersetzer: Ye Jinrong (E-Mail: ), Quelle: http://imysql.cn . Bitte geben Sie beim Nachdruck den Autor/Übersetzer an. Verstöße werden nicht strafrechtlich verfolgt.
Datum: 01.10.2006
In diesem Artikel werden der Sicherungs- und Wiederherstellungsmechanismus von MySQL und die Verwaltung von Datentabellen erläutert, einschließlich der beiden Haupttabellentypen: MyISAM und Innodb. Die in diesem Artikel entwickelte MySQL-Version ist 5.0.22.
Zu den kostenlosen Backup-Tools, die derzeit von MySQL unterstützt werden, gehören: mysqldump, mysqlhotcopy. Sie können auch die SQL-Syntax für Backups verwenden: BACKUP TABLE oder SELECT INTO OUTFILE oder Binärprotokolle sichern (binlog) oder Datendateien und zugehörige Konfigurationsdateien direkt kopieren. MyISAM-Tabellen werden als Dateien gespeichert und sind daher relativ einfach zu sichern. Es können mehrere der oben genannten Methoden verwendet werden. Alle Tabellen in Innodb werden in derselben Datendatei ibdata1 gespeichert (es können auch mehrere Dateien oder unabhängige Tabellenbereichsdateien sein), was relativ schwierig zu sichern ist. Kostenlose Lösungen können darin bestehen, Datendateien zu kopieren und Binlog zu sichern .
1.mysqldump
1.1 Sicherung
mysqldump verwendet einen Sicherungsmechanismus auf SQL-Ebene. Es exportiert Datentabellen in SQL-Skriptdateien. Dies ist auch die am häufigsten verwendete Sicherungsmethode.
Lassen Sie uns nun über einige der Hauptparameter von mysqldump sprechen:
--kompatible=Name
Es teilt mysqldump mit, mit welcher Datenbank oder älteren Version des MySQL-Servers die exportierten Daten kompatibel sind. Werte können ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options usw. sein. Um mehrere Werte zu verwenden, trennen Sie sie durch Kommas. Natürlich kann keine vollständige Kompatibilität garantiert werden, aber es wird versucht, kompatibel zu sein.
--complete-insert, -c
Die exportierten Daten nutzen die komplette INSERT-Methode inklusive der Feldnamen, d.h. alle Werte werden in eine Zeile geschrieben. Dadurch kann die Einfügungseffizienz verbessert werden, es kann jedoch durch den Parameter max_allowed_packet beeinträchtigt werden und zu Einfügungsfehlern führen. Daher muss dieser Parameter mit Vorsicht verwendet werden, ich empfehle ihn zumindest nicht.
--default-character-set=Zeichensatz
Geben Sie an, welcher Zeichensatz beim Exportieren von Daten verwendet werden soll. Wenn die Datentabelle nicht den Standardzeichensatz latin1 verwendet, muss diese Option beim Exportieren angegeben werden, da sonst nach dem erneuten Importieren der Daten verstümmelte Zeichen auftreten.
--disable-keys
Weisen Sie mysqldump an, die Anweisungen /*!40000 ALTER TABLE table DISABLE KEYS */; und /*!40000 ALTER TABLE table ENABLE KEYS */; am Anfang und am Ende der INSERT-Anweisung hinzuzufügen weil es ist Der Index wird neu erstellt, nachdem alle Daten eingefügt wurden. Diese Option ist nur für MyISAM-Tabellen geeignet.
--extended-insert = true|false
Standardmäßig aktiviert mysqldump den Modus --complete-insert. Wenn Sie ihn also nicht verwenden möchten, verwenden Sie einfach diese Option und setzen Sie ihren Wert auf false.
--hex-blob
Exportieren Sie binäre Zeichenfolgenfelder im Hexadezimalformat. Diese Option muss verwendet werden, wenn Binärdaten vorliegen. Die betroffenen Feldtypen sind BINARY, VARBINARY und BLOB.
--lock-all-tables,-x
Senden Sie vor Beginn des Exports eine Anfrage zum Sperren aller Tabellen in allen Datenbanken, um die Datenkonsistenz sicherzustellen. Dies ist eine globale Lesesperre und wird mit den Optionen --single-transaction und --lock-tables automatisch deaktiviert.
--lock-tables
Es ähnelt --lock-all-tables, sperrt jedoch die aktuell exportierte Datentabelle, anstatt alle Tabellen in der Datenbank auf einmal zu sperren. Diese Option gilt nur für MyISAM-Tabellen. Wenn es sich um eine Innodb-Tabelle handelt, können Sie die Option --single-transaction verwenden.
--no-create-info, -t
Exportieren Sie die Daten nur, ohne eine CREATE TABLE-Anweisung hinzuzufügen.
--no-data,-d
Es werden keine Daten exportiert, sondern nur die Datenbanktabellenstruktur.
--opt
Dies ist nur eine schnelle Option, gleichbedeutend mit dem Hinzufügen von --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set- Zeichensatzoptionen. Mit dieser Option kann mysqldump Daten schnell exportieren und die exportierten Daten können schnell wieder importiert werden. Diese Option ist standardmäßig aktiviert, kann aber mit --skip-opt deaktiviert werden. Beachten Sie, dass, wenn Sie mysqldump ausführen, ohne die Option --quick oder --opt anzugeben, die gesamte Ergebnismenge im Speicher abgelegt wird. Beim Exportieren einer großen Datenbank können Probleme auftreten.
--schnell,-q
Diese Option ist beim Exportieren großer Tabellen nützlich. Sie zwingt mysqldump dazu, die von der Serverabfrage erhaltenen Datensätze direkt auszugeben, anstatt alle Datensätze abzurufen und im Speicher zwischenzuspeichern.
--routinen, -R
Exportieren Sie gespeicherte Prozeduren und benutzerdefinierte Funktionen.
--single-transaction
Diese Option sendet vor dem Exportieren von Daten eine BEGIN-SQL-Anweisung. BEGIN blockiert keine Anwendungen und stellt einen konsistenten Zustand der Datenbank während des Exports sicher. Es funktioniert nur mit Transaktionstabellen wie InnoDB und BDB.
Diese Option und die Option --lock-tables schließen sich gegenseitig aus, da LOCK TABLES dazu führt, dass alle ausstehenden Transaktionen implizit festgeschrieben werden.
Um große Tabellen zu exportieren, sollte die Option --quick in Kombination verwendet werden.
--triggers
Exportieren Sie auch Trigger. Diese Option ist standardmäßig aktiviert. Verwenden Sie --skip-triggers, um sie zu deaktivieren.
Einzelheiten zu anderen Parametern finden Sie im Handbuch. Normalerweise verwende ich das folgende SQL, um MyISAM-Tabellen zu sichern:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x Datenbankname > Datenbankname.sql
Verwenden Sie die folgende SQL, um Innodb-Tabellen zu sichern:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction Datenbankname > Datenbankname.sql
Wenn Sie außerdem ein Online-Backup implementieren möchten, können Sie auch den Parameter --master-data wie folgt verwenden:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--single-transaction --flush-logs Datenbankname > Datenbankname.sql
Es fordert nur zu Beginn die Sperrtabelle an, aktualisiert dann das Binlog und fügt dann die CHANGE MASTER-Anweisung zur exportierten Datei hinzu, um den Binlog-Speicherort der aktuellen Sicherung anzugeben. Wenn Sie diese Datei auf dem Slave wiederherstellen möchten, können Sie diese verwenden Auf diese Weise können Sie es tun.
1.2 Wiederherstellen Die mit mysqldump gesicherte Datei ist ein SQL-Skript, das direkt importiert werden kann. Es gibt zwei Möglichkeiten, die Daten zu importieren.
Verwenden Sie den MySQL-Client direkt, zum Beispiel:
/usr/local/mysql/bin/mysql -uyejr -pyejr Datenbankname < Datenbankname.sql
Die Verwendung der SOURCE-Syntax ist eigentlich keine Standard-SQL-Syntax, sondern eine vom MySQL-Client bereitgestellte Funktion, zum Beispiel:
QUELLE /tmp/db_name.sql;
Hier müssen Sie den absoluten Pfad der Datei angeben, und es muss sich um eine Datei handeln, für die der Benutzer, der mysqld ausführt (z. B. „niemand“), Leseberechtigung hat.
2. mysqlhotcopy
2.1 Sicherung
mysqlhotcopy ist ein PERL-Programm, das ursprünglich von Tim Bunce geschrieben wurde. Es verwendet LOCK TABLES, FLUSH TABLES und cp oder scp, um die Datenbank schnell zu sichern. Dies ist der schnellste Weg, eine Datenbank oder eine einzelne Tabelle zu sichern, kann jedoch nur auf dem Computer ausgeführt werden, auf dem sich die Datenbankdateien (einschließlich Datentabellendefinitionsdateien, Datendateien und Indexdateien) befinden. mysqlhotcopy kann nur zum Sichern von MyISAM verwendet werden und läuft nur auf Unix-ähnlichen und NetWare-Systemen.
mysqlhotcopy unterstützt das gleichzeitige Kopieren mehrerer Datenbanken und unterstützt auch reguläre Ausdrücke. Hier ein paar Beispiele:
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
Datenbankname /tmp (Ändern Sie das Datenbankverzeichnis Datenbankname
Nach /tmp kopieren
Runter)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
Datenbankname_1 ... Datenbankname_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
Datenbankname./regex/ /tmp
Weitere Informationen zur Verwendung finden Sie im Handbuch oder rufen Sie den folgenden Befehl auf, um die Hilfe von mysqlhotcopy anzuzeigen:
perldoc /usr/local/mysql/bin/mysqlhotcopy
Beachten Sie, dass Sie, wenn Sie mysqlhotcopy verwenden möchten, über die Berechtigungen SELECT und RELOAD (um FLUSH TABLES auszuführen) sowie über die Berechtigung zum Lesen des Verzeichnisses datadir/db_name verfügen müssen.
2.2 Wiederherstellen
Mysqlhotcopy sichert das gesamte Datenbankverzeichnis. Bei Verwendung kann es direkt in das von mysqld angegebene Datenverzeichnis kopiert werden (hier ist es /usr/local/mysql/data/). Gleichzeitig sollte auf Berechtigungsprobleme geachtet werden. wie im folgenden Beispiel:
root#cp -rf Datenbankname /usr/local/mysql/data/
root#chown -R someone:nobody /usr/local/mysql/data/ (Ändern Sie den Besitzer des db_name-Verzeichnisses in mysqld
laufender Benutzer)
3. SQL-Syntaxsicherung
3.1 Sicherung
Die Syntax von BACKUP TABLE ähnelt tatsächlich dem Funktionsprinzip von mysqlhotcopy. Sie sperren beide die Tabelle und kopieren dann die Datendatei. Es kann eine Online-Sicherung durchgeführt werden, der Effekt ist jedoch nicht ideal und wird daher nicht empfohlen. Es kopiert nur Tabellenstrukturdateien und Datendateien, aber nicht gleichzeitig Indexdateien, sodass die Wiederherstellung langsamer ist.
Beispiel:
BACK TABLE tbl_name TO '/tmp/db_name/';
Beachten Sie, dass Sie über die FILE-Berechtigung verfügen müssen, um diese SQL auszuführen, und dass das Verzeichnis /tmp/db_name/ für den mysqld-Benutzer beschreibbar sein muss. Die exportierte Datei kann die vorhandene Datei nicht überschreiben, um Sicherheitsprobleme zu vermeiden.
SELECT INTO OUTFILE exportiert die Daten in eine normale Textdatei. Sie können das Feldintervall anpassen, um die Verarbeitung dieser Daten zu erleichtern.
Beispiel:
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
Beachten Sie, dass Sie über die FILE-Berechtigung verfügen müssen, um diese SQL auszuführen, und dass die Datei /tmp/db_name/tbl_name.txt vom mysqld-Benutzer beschreibbar sein muss. Die exportierte Datei kann die vorhandene Datei nicht überschreiben, um Sicherheitsprobleme zu vermeiden.
3.2 Um mit der BACKUP TABLE-Methode gesicherte Dateien wiederherzustellen, können Sie die RESTORE TABLE-Anweisung ausführen, um die Datentabelle wiederherzustellen.
Beispiel:
TABELLE AUS '/tmp/db_name/' WIEDERHERSTELLEN;
Die Genehmigungsanforderungen ähneln den oben beschriebenen.
Für Dateien, die mit der Methode SELECT INTO OUTFILE gesichert wurden, können Sie die Anweisung LOAD DATA INFILE ausführen, um die Datentabelle wiederherzustellen.
Beispiel:
LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;
Die Genehmigungsanforderungen ähneln den oben beschriebenen. Vor dem Importieren von Daten muss die Datentabelle bereits vorhanden sein. Wenn Sie Bedenken hinsichtlich der Datenduplizierung haben, können Sie das Schlüsselwort REPLACE hinzufügen, um vorhandene Datensätze zu ersetzen, oder das Schlüsselwort IGNORE verwenden, um sie zu ignorieren.
4. Binärprotokoll (binlog) aktivieren
Die Verwendung von Binlog ist relativ flexibler, spart Sorgen und Aufwand und kann auch inkrementelle Sicherungen unterstützen.
Mysqld muss neu gestartet werden, wenn Binlog aktiviert ist. Schließen Sie zunächst mysqld, öffnen Sie my.cnf und fügen Sie die folgenden Zeilen hinzu:
Server-ID=1
log-bin = binlog
log-bin-index = binlog.index
Dann starten Sie mysqld. Binlog.000001 und binlog.index werden während des Vorgangs generiert. Die erstere Datei ist MySQL, die alle Aktualisierungsvorgänge für Daten aufzeichnet, und die letztere Datei ist der Index aller Binlogs, die nicht einfach gelöscht werden können. Weitere Informationen zu Binlog finden Sie im Handbuch.
Wenn Sie eine Sicherung durchführen müssen, können Sie zunächst die SQL-Anweisung ausführen, damit mysqld das Schreiben in das aktuelle Binlog beendet, und dann die Datei direkt sichern. Auf diese Weise kann der Zweck der inkrementellen Sicherung erreicht werden:
FLUSH LOGS; Wenn Sie den Slave-Server im Replikationssystem sichern, sollten Sie auch die Dateien „master.info“ und „relay-log.info“ sichern.
Die gesicherte Binlog-Datei kann mit dem von MySQL bereitgestellten Tool mysqlbinlog angezeigt werden, z. B.:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
Mit diesem Tool können Sie alle SQL-Anweisungen einer bestimmten Datenbank anzeigen und auch den Zeitbereich begrenzen, was sehr praktisch ist. Weitere Informationen finden Sie im Handbuch.
Beim Wiederherstellen können Sie Anweisungen wie die folgenden verwenden:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
Verwenden Sie die von mysqlbinlog ausgegebenen SQL-Anweisungen direkt als Eingabe, um sie auszuführen.
Wenn Sie einen inaktiven Computer haben, können Sie ihn genauso gut mit dieser Methode sichern. Da die Leistungsanforderungen von Slave-Maschinen relativ gering sind, ist die inkrementelle Sicherung kostengünstig und ein Teil des Datenabfragedrucks kann geteilt werden.
5. Direkte Sicherung von Datendateien Im Vergleich zu den vorherigen Methoden ist die Sicherung von Datendateien die direkteste, schnellste und bequemste. Der Nachteil besteht darin, dass eine inkrementelle Sicherung grundsätzlich unmöglich ist. Um die Datenkonsistenz sicherzustellen, muss vor dem Sichern der Datei die folgende SQL-Anweisung ausgeführt werden:
FLUSH TABLES WITH READ LOCK; das heißt, alle Daten im Speicher werden auf die Festplatte geleert und die Datentabelle gesperrt, um sicherzustellen, dass während des Kopiervorgangs keine neuen Daten geschrieben werden. Die mit dieser Methode gesicherten Daten lassen sich auch sehr einfach wiederherstellen. Kopieren Sie sie einfach zurück in das ursprüngliche Datenbankverzeichnis.
Beachten Sie, dass Sie für Tabellen vom Typ Innodb auch die Protokolldateien (ib_logfile*-Dateien) sichern müssen. Denn wenn die Innodb-Tabelle beschädigt ist, können Sie sich bei der Wiederherstellung auf diese Protokolldateien verlassen.
6. Backup-Strategie Für Systeme mit mittlerem Geschäftsvolumen kann die Backup-Strategie wie folgt festgelegt werden: vollständige Sicherung beim ersten Mal, inkrementelle Sicherung einmal täglich, vollständige Sicherung einmal wöchentlich usw. Bei wichtigen und ausgelasteten Systemen benötigen Sie möglicherweise einmal täglich ein vollständiges Backup, einmal pro Stunde oder sogar noch häufiger ein inkrementelles Backup. Um Online-Backups und inkrementelle Backups zu erreichen, ohne das Online-Geschäft zu beeinträchtigen, ist es am besten, den Master-Slave-Replikationsmechanismus (Replikation) zu verwenden, um Backups auf dem Slave-Computer zu erstellen.
7. Datenpflege und Notfallwiederherstellung Als DBA (das bin ich noch nicht, haha) besteht eine der wichtigsten Aufgaben darin, sicherzustellen, dass Datentabellen sicher, stabil und mit hoher Geschwindigkeit verwendet werden können. Daher müssen Ihre Datentabellen regelmäßig gepflegt werden. Die folgende SQL-Anweisung ist nützlich:
CHECK TABLE oder REPAIR TABLE, überprüfen oder pflegen Sie MyISAM-Tabellen
TABELLE OPTIMIEREN, MyISAM-Tabelle optimieren
ANALYZE TABLE, analysieren Sie die MyISAM-Tabelle. Natürlich können alle oben genannten Befehle über das Tool myisamchk ausgeführt werden und werden hier nicht im Detail beschrieben.
Innodb-Tabellen können defragmentiert und die Indizierungsgeschwindigkeit verbessert werden, indem die folgenden Anweisungen ausgeführt werden:
ALTER TABLE tbl_name ENGINE = Innodb;
Dies ist eigentlich eine NULL-Operation, die oberflächlich betrachtet nichts bewirkt, aber tatsächlich die Fragmente neu anordnet.
Häufig verwendete MyISAM-Tabellen können mit den oben genannten Methoden wiederhergestellt werden. Wenn der Index defekt ist, können Sie den Index mit dem Tool myisamchk neu erstellen. Bei Innodb-Tabellen ist dies nicht so einfach, da alle Tabellen in einem Tabellenbereich gespeichert werden. Innodb verfügt jedoch über einen Prüfmechanismus namens Fuzzy Checkpoint. Solange die Protokolldatei gespeichert ist, können Fehler anhand der Protokolldatei repariert werden. Sie können der Datei my.cnf die folgenden Parameter hinzufügen, damit mysqld die Protokolldatei beim Start automatisch überprüft:
innodb_force_recovery = 4
Informationen zu diesem Parameter finden Sie im Handbuch.
8. Fassen Sie die Datensicherung zusammen und legen Sie die geeignete Sicherungsstrategie fest. Dies ist ein kleiner Teil dessen, was ein DBA tut. Am Anfang ist alles schwierig.