MySQL-Datensicherung mit mysqldump

1. Verschiedene Verwendungsanweisungen

A. Die einfachste Verwendung:

mysqldump -uroot -pPassword [database name] 
> [dump file]

Der obige Befehl sichert die angegebene Datenbank in einer Dump-Datei (Dump-Datei), wie zum Beispiel:

mysqldump -uroot -p123 test > test.dump

Die generierte test.dump-Datei enthält Tabellenerstellungsanweisungen (zum Generieren von Datenbankstrukturen) und Einfügeanweisungen zum Einfügen von Daten.

B. --opt

Wenn Sie den Parameter --opt hinzufügen, sieht die generierte Dump-Datei etwas anders aus:

Anweisung Enthält eine Tabelle löschen, wenn Tabellenname

vorhanden ist. Enthält eine Anweisung zum Sperren der Tabelle vor dem Einfügen, zum Sperren der Tabelle Tabellenname und zum Entsperren nach dem Einfügen. Tabellen

C. Hostübergreifende Sicherung

Verwenden Sie den folgenden Befehl, um die Quelldatenbank auf Host1 in die Zieldatenbank von Host2 zu kopieren, sofern die Zieldatenbank auf dem Host2-Host erstellt wurde:

mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

-C gibt an, dass die Datenübertragung zwischen Hosts Datenkomprimierung verwendet

D. Nur die Tabellenstruktur sichern

mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.dump

wird nur die Tabellenstruktur sichern. --databases gibt die Datenbanken auf dem Host an, die gesichert werden sollen. Wenn Sie alle Datenbanken auf einem MySQL-Host sichern möchten, können Sie die Option --all-databases wie folgt verwenden:

mysqldump --all-databases
> test.dump

E. Stellen Sie die Datenbank aus der Sicherungsdatei wieder her

mysql [database name] < [backup file name]

2. Kombination Der cron-Befehl von Linux implementiert eine geplante Sicherung

Wenn Sie beispielsweise jeden Tag um 1:30 Uhr alle Datenbanken auf einem bestimmten Host sichern und die Dump-Datei in das GZ-Format komprimieren müssen, können Sie der Konfigurationsdatei /etc/crontab die folgenden Codezeilen hinzufügen:

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

Vorherige 5 Die Parameter stehen für Minuten, Stunden, Tage, Monate und Jahre, und das Sternchen steht für beliebige. date '+%m-%d-%Y' ruft das aktuelle Datum im Format MM-TT-JJJJ ab.

3. Ein vollständiges Shell-Skript zum Sichern einer MySQL-Datenbank.

#vi /backup/backup.sh

#!bin/bash
cd /backup
echo "You are in backup dir"
mv backup* /oldbackup
echo "Old dbs are moved to oldbackup folder"
File = backup-$Now.sql
mysqldump -u user -p password database-name > $File
echo "Your database backup successfully completed"

Die obige Skriptdatei wird als backup.sh gespeichert und zwei Verzeichnisse wurden erstellt im System/olcbackup und /backup. Bei jeder Ausführung von „backup.sh“ werden alle Dateien, deren Namen im Verzeichnis „/backup“ mit „backup“ beginnen, in das Verzeichnis „/oldbackup“ verschoben.

Entwickeln Sie einen Ausführungsplan für das obige Skript wie folgt:

#crontab -e30 1 * * * /backup.sh

4. Vollständiges Backup von mysqldump + inkrementelles Backup des mysqlbinlog-Binärprotokolls

Beim Wiederherstellen von Daten aus der Sicherungsdatei mysqldump gehen die aktualisierten Daten ab dem Sicherungspunkt verloren. Daher ist es auch erforderlich, die inkrementelle Sicherung des Binärprotokolls mysqlbinlog zu kombinieren. Stellen Sie sicher, dass my.ini oder my.cnf die folgende Konfiguration enthält, um die binäre Protokollierung oder mysqld zu aktivieren ---log-bin:

[mysqld]
log-bin=mysql-bin

Der Befehl mysqldump muss die Option --flush-logs mitbringen, um eine neue Binärprotokolldatei zu generieren:

mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

Die inkrementelle Binärdatei, die in generiert wurde Auf diese Weise Wenn die Protokolldatei beispielsweise mysql-bin.000003 lautet, erfolgt die Datenwiederherstellung wie folgt:

shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql 
shell> mysqlbinlog mysql-bin.000003 | mysql -uroot -pPwd

Darüber hinaus kann mysqlbinlog auch --start-date, - angeben. Die Parameter -stop-date, --start-position und --stop-position werden verwendet, um Daten zu einem bestimmten Zeitpunkt genau wiederherzustellen oder einen bestimmten Problemzeitraum in der Mitte zu überspringen, um Daten direkt aus der MySQL-Dokumentation wiederherzustellen folgt:

5.9.3.1. 指定恢复时间
对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：
mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \
     | mysql -u root -pmypwd
该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 \
     | mysql -u root -pmypwd \
在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

5.9.3.2. 指定恢复位置
也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：
mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" \
      /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
    | mysql -u root -pmypwd 
 
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
    | mysql -u root -pmypwd \ 
上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。