Crmgo Blog

mysqldump: Backups erstellen

← Blog

renisans

mysqldump: Backups erstellen

Erstellung von Daten- und Tabellendumps Diese Utility ermöglicht es, einen Dump (einen „Momentaufnahme“) des Inhalts einer Datenbank oder einer Gruppe von Datenbanken zu erstellen, um eine Sicherungskopie anzufertigen oder Daten an einen anderen SQL-Datenbankserver (nicht unbedingt einen MySQL-Server) zu übertragen. Der Dump enthält eine Reihe von SQL-Befehlen…

mysqldump: Backups erstellen

Erstellen von Daten- und Tabellendumps

Mit diesem Dienstprogramm können Sie einen Dump (einen „Momentaufnahme“) des Inhalts einer Datenbank oder einer Gruppe von Datenbanken erstellen, um eine Sicherungskopie anzufertigen oder Daten an einen anderen SQL-Datenbankserver (nicht unbedingt einen MySQL-Server) zu übertragen. Der Dump enthält eine Reihe von SQL-Befehlen zum Erstellen und/oder Befüllen von Tabellen.

Wenn die Sicherungskopie auf dem Server erstellt wird, sollten Sie anstelle des beschriebenen Dienstprogramms mysqlhotcopy verwenden. Siehe Abschnitt See Abschnitt 4.8.6, „mysqlhotcopy, Kopieren von MySQL-Datenbanken und -Tabellen“.

shell> mysqldump [OPTIONS] database [tables]
или  mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
или  mysqldump [OPTIONS] --all-databases [OPTIONS]

Wenn keine Tabellennamen angegeben werden oder die Parameter --databases oder --all-databases verwendet werden, wird ein Dump der gesamten Datenbank (bzw. aller Datenbanken) erstellt.

Eine Liste der von Ihrer spezifischen Version des Dienstprogramms mysqldump unterstützten Optionen erhalten Sie durch Ausführen des Befehls mysqldump --help.

Beachten Sie, dass das Dienstprogramm mysqldump, das ohne die Optionen --quick oder --opt verwendet wird, das gesamte Ergebnis der Abfrage vor dem Erstellen des Dumps in den Speicher lädt. Dies kann Probleme beim Erstellen eines Dumps einer großen Datenbank verursachen.

Beachten Sie, dass Sie die Parameter --opt oder -e nicht verwenden sollten, wenn Sie beabsichtigen, den Dump mit einer neuen Kopie des Programms mysqldump zu erstellen und ihn anschließend auf einem sehr alten MySQL-Server wiederherzustellen.

Das Dienstprogramm mysqldump unterstützt die folgenden Optionen:

  • --add-locksДобавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
  • --add-drop-tableДобавить команду DROP TABLE перед каждой командой CREATE TABLE.
  • -A, --all-databasesПроизвести дамп всех баз данных. Аналогично опции --databases с указанием всех баз данных.
  • -a, --allВключить все опции создания объектов, специфичные для MySQL.
  • --allow-keywordsРазрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.
  • -c, --complete-insertИспользовать полные команды INSERT (с именами столбцов).
  • -C, --compressИспользовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие.
  • -B, --databasesВыполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных.
  • --delayedИспользовать команду INSERT DELAYED при вставке строк.
  • -e, --extended-insertИспользовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
  • -#, --debug[=option_string]Отслеживать прохождение программы (для отладки).
  • --helpВывести справочную информацию и выйти из программы.
  • --fields-terminated-by=... , --fields-enclosed-by=... , --fields-optionally-enclosed-by=... , --fields-escaped-by=... , --lines-terminated-by=...Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE. См. раздел See Раздел 6.4.9, «Синтаксис оператора LOAD DATA INFILE».
  • -F, --flush-logsЗаписать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.
  • -f, --force,Продолжать даже при получении ошибки SQL при выполнении дампа таблицы.
  • -h, --host=..Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию - localhost.
  • -l, --lock-tables.Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL, чтобы разрешить параллельные записи для MyISAM-таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция --lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях.
  • -K, --disable-keysДобавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных.
  • -n, --no-create-dbВ выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций --databases или --all-databases.
  • -t, --no-create-infoНе записывать информацию о создании таблицы (команда CREATE TABLE).
  • -d, --no-dataНе записывать информацию из строк таблицы. Это очень полезно для получения дампа структуры таблицы!
  • --optТо же, что и --quick --add-drop-table --add-locks --extended-insert --lock-tables. Должно дать наиболее быстрый дамп для чтения на MySQL-сервере.
  • -pyour_pass, --password[=your_pass]Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль.
  • -P port_num, --port=port_numНомер порта TCP/IP, используемого для подключения к хосту.
  • --protocol=(TCP | SOCKET | PIPE | MEMORY)Для указания протокола соединения, который надлежит использовать. Новшество в MySQL 4.1.0.
  • -q, --quickВыводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result().
  • -Q, --quote-namesВзять в кавычки имена таблиц и столбцов без символов '`'.
  • -r, --result-file=...Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки '\n' в последовательность '\n\r' (новая строка + возврат каретки).
  • --single-transactionДанная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB-таблицамии и уровнем изоляции транзакций READ_COMMITTED, так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM- или HEAP-таблицы при использовании данной опции могут все же изменить свое состояние.Опция --single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции --lock-tables, так как команда LOCK TABLES уже принимает открытую транзакцию.
  • -S /path/to/socket, --socket=/path/to/socketФайл сокета для подсоединения к localhost (значение хоста по умолчанию).
  • --tablesПерекрывает параметр --databases (-B).
  • -T, --tab=path-to-some-directoryДля каждой заданной таблицы создает файл a table_name.sql, содержащий SQL CREATE команды для создания таблицы, и файл table_name.txt с данными таблицы. Файл .txt имеет формат в соответствии с параметрами --fields-xxx и --lines--xxx. Примечание: Этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld, причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу.
  • -u user_name, --user=user_nameИмя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix.
  • -O var=option, --set-variable var=optionУстановить значения переменных. Доступные для использования переменные перечислены ниже. В MySQL 4.0 просто используйте --var=option.
  • -v, --verboseРасширенный режим вывода. Вывод более детальной информации о работе программы.
  • -V, --versionВывести информацию о версии и выйти из программы.
  • -w, --where='where-condition'Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. 
    "--where=user='jimf'" "-wuserid>1" "-wuserid<1"

 

  • -X, --xmlStellt einen Datenbankdump im XML-Format dar.
  • -x, --first-slaveSperrt alle Tabellen in allen Datenbanken.
  • -O net_buffer_length=#, where # < 16MBei der Erstellung von mehrzeiligen Eingabeoperationen (wie auch bei Verwendung der Parameter --extended-insert oder --opt) wird das Dienstprogramm mysqldump Zeilen mit einer Länge bis zu dem in net_buffer_length angegebenen Wert erzeugen. Bei Erhöhung des Werts dieser Variable muss zudem sichergestellt werden, dass für die Variable max_allowed_packet auf dem MySQL-Server ein Wert größer als der Wert von net_buffer_length festgelegt ist.

    • Am häufigsten wird das Dienstprogramm mysqldump zur Erstellung einer Sicherungskopie aller Datenbanken verwendet. Siehe Abschnitt 4.4.1, „Datenbank-Sicherungen“.

    mysqldump --opt database > backup-file.sql

    Umgekehrt kann diese Datei auch auf dem MySQL-Server mittels des folgenden Befehls gelesen werden:

    mysql database < backup-file.sql

    oder

    mysql -e "source /patch-to-backup/backup-file.sql" database

     

    Dieses Dienstprogramm wird häufig auch zum Übertragen von Daten von einer Datenbank auf einen anderen MySQL-Server verwendet:

    mysqldump --opt database | mysql --host=remote-host -C database

    Es ist durchaus möglich, Dumps mehrerer Datenbanken mit einem einzigen Befehl zu erstellen:

    mysqldump --databases database1 [database2 ...] > my_databases.sql

    Wenn ein Dump aller Datenbanken erforderlich ist, kann Folgendes verwendet werden:

    mysqldump --all-databases > all_databases.sql

    Betrachten wir ein einfaches Beispiel: Wir erstellen einen Dump der Datenbank „database“ durch Umleiten des Streams in die Datei „database.sql“:

    mysqldump -uroot -h82.82.82.82 -p database > database.sql

    wobei:

    • -u oder --user=... — Benutzername
    • -h oder --host=... — entfernter Host (für den lokalen Host kann dieser Parameter weggelassen werden)
    • -p oder --password — nach Passwort fragen
    • database — Name der Datenbank
    • database.sql — Dump-Zieldatei

    Um einen Dump mehrerer Datenbanken zu erstellen, muss der Parameter --databases (oder kurz -B) verwendet werden, Beispiel:

    mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

    Und um einen Dump aller Datenbanken zu erstellen, muss der Parameter --all-databases (oder kurz -A) verwendet werden, Beispiel:

    mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

    Wiederherstellung des Dumps

    Leiten Sie den Stream in die entgegengesetzte Richtung um und stellen Sie die Datenbank wieder her:

    mysql -uroot -h82.82.82.82 -p database < database.sql

    Oder über die MySQL-Konsole:

    mysql> use database; mysql> source database.sql

    Wenn wir es beispielsweise mit einem gz-Archiv zu tun haben, dann:

    zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database

    Beispiel für die Verwendung einiger Parameter

    Beispielsweise benötigen wir Daten aus der „Produktionsversion der Datenbank“ für die „Entwicklerversion“, also eine „Sandbox“. Wir beschränken uns auf maximal 100 Datensätze:

    mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql

    Oder wir benötigen nur die Struktur, ohne Daten:

    mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql

    Die Beispiele wurden inspiriert durch den Beitrag von Alexander Makarov – http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

    Wir erstellen einen Dump nur von Triggern, Prozeduren und Events:

    mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p database | gzip > ~/database.sql.gz

    Referenzkarte zu den Parametern

    Im Folgenden werden einige Parameter aufgeführt, die bei der Arbeit mit dem Dienstprogramm mysqldump nützlich sein können.

    --add-drop-database
    Fügt vor jedem CREATE DATABASE-Befehl einen DROP DATABASE-Befehl ein.
    --add-drop-table
    Fügt vor jedem CREATE TABLE-Befehl einen DROP TABLE-Befehl ein.
    --add-locks
    Fügt vor der Ausführung und nach dem Sichern jeder Tabelle LOCK TABLES bzw. UNLOCK TABLE ein (zur Beschleunigung des MySQL-Zugriffs).
    --all-databases, -A
    Sichert alle Tabellen aller Datenbanken, die vom aktuellen Server verwaltet werden.
    --allow-keywords
    Ermöglicht die Erstellung von Spaltennamen, die mit Schlüsselwörtern übereinstimmen. Konflikte werden vermieden, indem der Tabellenname als Präfix an jeden Spaltennamen angehängt wird.
    --comments, -i
    Diese Option fügt dem Dump zusätzliche Informationen hinzu, wie z. B. die Version von mysqldump, die MySQL-Version und den Hostnamen, auf dem der MySQL-Server läuft.
    --compact
    Diese Option weist mysqldump an, einen Dump im möglichst kompakten Format zu erstellen. Diese Option ist das Gegenteil von --comments.
    --compatible=name
    Die Option generiert eine Ausgabe, die mit anderen DBMS oder älteren MySQL-Versionen kompatibel ist. Statt des Schlüsselworts name können folgende Werte verwendet werden: „ansi", „mysql323", „mysql40", „postgresql", „oracle", „mssql", „db2", „maxdb", „no_key_options", „no_table_options", „no_field_options". Mehrere Werte können durch Kommas getrennt angegeben werden.
    --complete-insert, -c
    Verwendet die vollständige Form des INSERT-Befehls (mit Spaltennamen).
    --create-options
    Fügt zusätzliche Informationen in die CREATE TABLE-Befehle ein. Dies kann den Tabellentyp, den Startwert für AUTO_INCREMENT und andere Optionen umfassen.
    --databases, -B
    Mit dieser Option können mehrere Datenbanknamen angegeben werden, für die ein Dump erstellt werden soll.
    --delayed
    Verwendet den Befehl INSERT DELAYED beim Einfügen von Zeilen.
    --delete-master-logs
    Auf dem Master-Replikationsserver werden die Binärprotokolle (logbin) automatisch gelöscht, nachdem der Dump erfolgreich mit mysqldump erstellt wurde. Diese Option aktiviert automatisch die Option „--master-data".
    --disable-keys, -K
    Um jeden INSERT-Befehl für jede Tabelle werden die Ausdrücke /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; und /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; in die Dump-Ausgabe eingefügt. Dies beschleunigt das Laden der Daten auf den Server für Tabellen vom Typ MyISAM, da die Indizes nach dem Einfügen aller Daten erstellt werden.
    --extended-insert, -e
    Verwendet den INSERT-Befehl mit der neuen mehrzeiligen Syntax (erhöht die Kompaktheit und Leistungsfähigkeit der Einfügebefehle).
    --flush-logs, -F
    Schreibt die Daten des Systemprotokolls aus dem Puffer des MySQL-Servers auf die Festplatte, bevor der Dump-Vorgang beginnt.
    --force, -f
    Fährt fort, auch wenn während des Dump-Vorgangs ein Fehler auftritt.
    --hex-blob
    Mit dieser Option können binäre Daten in Feldern vom Typ BINARY, VARBINARY, BLOB und BIT im Hexadezimalformat dargestellt werden. So wird die Sequenz „abc" durch 0×616263 ersetzt.
    --ignore-table=db_name.tbl_name
    Ermöglicht es, die Tabelle tbl_name der Datenbank db_name beim Erstellen des Dumps zu ignorieren. Wenn mehrere Tabellen aus dem Dump ausgeschlossen werden sollen, müssen mehrere „--ignore-table"-Optionen angegeben werden, wobei jede Option eine Tabelle angibt.
    --insert-ignore
    Fügt das Schlüsselwort IGNORE in den INSERT-Befehl ein.
    --lock-all-tables, -x
    Die Angabe dieser Option führt dazu, dass alle Tabellen in allen Datenbanken während der Erstellung eines vollständigen Dumps aller Datenbanken gesperrt werden.
    --lock-tables, -l
    Die Angabe dieser Option führt dazu, dass die Tabellen der Datenbank, für die der Dump erstellt wird, gesperrt werden.
    --no-autocommit
    Fasst alle INSERT-Befehle, die sich auf eine Tabelle beziehen, in eine einzige Transaktion zusammen, was die Ladeleistung der Daten erhöht.
    --no-create-db, -n
    Unterdrückt die Erstellung von CREATE DATABASE-Befehlen im Dump, die automatisch hinzugefügt werden, wenn die Optionen --databases und --all-databases verwendet werden.
    --no-data, -d
    Unterdrückt die Erstellung von INSERT-Befehlen im Dump. Dies kann nützlich sein, wenn nur die Struktur der Datenbank ohne die eigentlichen Daten gesichert werden soll.
    --opt
    Diese Option dient zur Optimierung der Sicherungsgeschwindigkeit und ist eine Abkürzung, die folgende Optionen umfasst: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Ab MySQL 4.1 ist --opt standardmäßig aktiviert, d. h., alle oben genannten Optionen sind standardmäßig eingeschaltet, auch wenn sie nicht explizit angegeben werden. Um dieses Verhalten zu deaktivieren, muss die Option --skip-opt verwendet werden.
    --order-by-primary
    Die Angabe dieser Option führt dazu, dass jede Tabelle nach dem Primärschlüssel oder dem ersten eindeutigen Index sortiert wird.
    --port, -P
    Die TCP-Port-Nummer, die für die Verbindung mit dem Host verwendet wird.
    --protocol={TCP|SOCKET|PIPE|MEMORY}
    Mit dieser Option kann das Verbindungsprotokoll zum Server festgelegt werden.
    --quick, -q
    Ermöglicht den Beginn der Dump-Erstellung, ohne auf das vollständige Laden der Daten vom Server zu warten, und spart so Speicher.
    --quote-names, -Q
    Setzt Datenbank-, Tabellen- und Spaltennamen in Backticks `. Ab MySQL 4.1 ist diese Option standardmäßig aktiviert.
    --replace
    Fügt das Schlüsselwort REPLACE in den INSERT-Befehl ein. Diese Option wurde erstmals in MySQL 5.1.3 eingeführt.
    --result-file=/path/to/file, -r /path/to/file
    Leitet den Dump in die Datei file um. Diese Option ist besonders in Windows praktisch, wenn die Ausgabe ohne Verwendung der Befehlszeile in eine Datei umgeleitet werden kann, indem die Sequenzen > und >> verwendet werden.
    --routines, -R
    Diese Option sichert gespeicherte Prozeduren und Funktionen. Verfügbar ab MySQL 5.1.2.
    --single-transaction
    Erstellt den Dump als eine einzelne Transaktion.
    --skip-comments
    Mit dieser Option kann die Ausgabe zusätzlicher Informationen im Dump unterdrückt werden.
    --socket=/path/to/socket, -S /path/to/socket
    Die Socket-Datei für die Verbindung mit localhost.
    --tab=/path/, -T /path/
    Bei Verwendung dieser Option werden im Verzeichnis path für jede Tabelle zwei separate Dateien erstellt: tbl_name.sql, das den CREATE TABLE-Befehl enthält, und tbl_name.txt, das die tabulatorgetrennten Tabellendaten enthält. Das Datenformat kann explizit mit den Optionen --fields-xxx und --lines-xxx überschrieben werden.
    --tables
    Überschreibt die Wirkung der Option --databases (-B). Alle Argumente, die auf diese Option folgen, werden als Tabellennamen interpretiert.
    --triggers
    Erstellt einen Dump von Triggern. Diese Option ist standardmäßig aktiviert. Zur Deaktivierung ist die Option --skip-triggers zu verwenden.
    --events, -E
    Erstellt einen Dump von Ereignissen. Siehe MySQL Event Scheduler oder der integrierte Ereignis-Manager in MySQL.
    --tz-utc
    Bei Verwendung dieser Option wird dem Dump ein Befehl der Form SET TIME_ZONE='+00:00' hinzugefügt, der den Austausch von Dumps in verschiedenen Zeitzonen ermöglicht.
    --verbose, -v
    Erweiterter Ausgabemodus. Ausgabe detaillierterer Informationen über den Programmablauf.
    --version, -V
    Gibt die Programmversion aus.
    --where='where-condition', -w 'where-condition'
    Sichert nur ausgewählte Datensätze. Beachten Sie, dass Anführungszeichen obligatorisch sind.
    --xml, -X
    Stellt den Datenbankdump als XML dar.
    --first-slave, -x
    Sperrt alle Tabellen in allen Datenbanken.
    --debug=..., -#
    Verfolgt den Programmablauf (zur Fehlersuche).
    --help
    Gibt Hilf Informationen aus und beendet das Programm.
    
  
    Hinweis: Dieser Beitrag kann KI-unterstützt erstellt und anschließend redaktionell geprüft worden sein. Verantwortlich: Dmytro Lomaka, Kaltenkirchen.