Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » mysql-dump
Zuletzt angesehen: • zfs • archlinux-installation • iptables • systemd • mysql-dump • security_key • ffmpeg
mysql-dump

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
mysql-dump [2018-03-19 11:19:30] – [MySQL-Dump] manfredmysql-dump [2024-06-26 16:45:59] (aktuell) – [Fehler] manfred
Zeile 1: Zeile 1:
 +====== MySQL-Dump ======
 +
 +  * [[http://dev.mysql.com/doc/refman/5.1/de/backup.html]]
 +    * [[http://dev.mysql.com/doc/refman/5.1/de/backup-strategy-example.html]]
 +    * [[http://dev.mysql.com/doc/refman/5.1/de/mysqldump.html]]
 +    * [[http://dev.mysql.com/doc/refman/5.1/en/view-restrictions.html]]
 +
 +  * [[http://stefankonarski.de/content/mysqldump-restore-von-innodb-tabellen]]
 +
 +MyISAM-Dump ziehen, um in eine INNODB wieder einzuspielen:
 +  # mysqldump --order-by-primary -Ac > all_databases.sql
 +
 +INNODB-Dump mit "%%--single-transaction%%" ziehen:
 +Es dürfen diese Aufrufe während der Dump geschrieben wird,
 +nicht ausgeführt werden:
 +''ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE''
 +  # mysqldump --opt --single-transaction -Ac > all_databases.sql
 +
 +Müssen die Aufrufe ''ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE'' während der Dump geschrieben wird,
 +nutzbar sein, dann muss man [[Percona xtrabackup]] einsetzen.
 +
 +Will man nur die Daten dumpen, ohne die Datenbanken und Tabellen vorher neu anzulegen, dann geht es so:
 +  # mysqldump --skip-add-drop-table -Ktnc
 +
 +komplette Optionen, **ohne** Tabelle löschen:
 +  # mysqldump --set-gtid-purged=OFF --opt --triggers --routines --skip-add-drop-table --order-by-primary --single-transaction --skip-extended-insert -QEc [Datenbankname] [Tabellenname] | gzip -1 > Datenbankname_Tabellenname.sql.gz
 +
 +komplette Optionen, **mit** Tabelle löschen:
 +  # mysqldump --set-gtid-purged=OFF --opt --triggers --routines --add-drop-table --order-by-primary --single-transaction --skip-extended-insert -QEc [Datenbankname] [Tabellenname] | gzip -1 > Datenbankname_Tabellenname.sql.gz
 +
 +
 +==== MySQLDump ====
 +
 +
 +=== Fehler ===
 +
 +wenn dieser Fehler auftritt:
 +  > mysqldump: Got error: 1105: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING when using LOCK TABLES
 +
 +dann liegt das daran:
 +  > echo "SHOW VARIABLES WHERE Variable_name LIKE ('pxc_strict_mode');" | mysql -t
 +  +-----------------+-----------+
 +  | Variable_name   | Value     |
 +  +-----------------+-----------+
 +  | pxc_strict_mode | ENFORCING |
 +  +-----------------+-----------+
 +
 +das ist die falsche vorgehensweise:
 +  > echo "SET GLOBAL pxc_strict_mode='DISABLED';" | mysql
 +
 +das wäre dann zu tun (eine dieser beiden zusätzlichen Optionen verwenden):
 +  # mysqldump ... --single-transaction
 +  # mysqldump ... --no-lock-tables
 +
 +mal ausprobieren:
 +  SET unique_checks=0;
 +  SET foreign_key_checks = 0;
 +
 +
 +===== MySQL-DB kopieren =====
 +
 +Das kopieren über den MySQL-Proxy funktioniert nicht,\\
 +hier werden die CREATE TABLES - Anweisungen nicht mitgedumpt.
 +
 +In diesem Beispiel soll eine Datenbank "neuedb" angelegt werden,
 +die die gleiche Tabellenstruktur wie die "altedb" besitzt.
 +
 +<file bash /root/bin/mysqldump.sh>
 +#!/bin/sh
 +
 +VERSION="v2021102000"
 +
 +# /root/bin/mysqldump.sh -p 3306 -d "testdb1 testdb2 testdb3" | gzip -9 > /tmp/testdbs.sql.gz
 +# /root/bin/mysqldump.sh -p 3306 -l -d "testdb1 testdb2 testdb3" | gzip -9 > /tmp/testdbs.sql.gz
 +# /root/bin/mysqldump.sh -p 3306 -l -t löschen -d "testdb1 testdb2 testdb3" | gzip -9 > /tmp/testdbs.sql.gz
 +# /root/bin/mysqldump.sh -p 3306 -t löschen -d "testdb1 testdb2 testdb3" | gzip -9 > /tmp/testdbs.sql.gz
 +# /root/bin/mysqldump.sh -h 192.168.1.100 -p 3306 -d "testdb1 testdb2 testdb3" | gzip -9 > /tmp/testdbs.sql.gz
 +
 +while [ "${#}" -ne "0" ]; do
 +        case "${1}" in
 +                -h)
 +                        HOST=${2}
 +                        shift
 +                        ;;
 +                -p)
 +                        PORT=${2}
 +                        shift
 +                        ;;
 +                -t)
 +                        TABELLEN=${2}
 +                        shift
 +                        ;;
 +                -d)
 +                        DATENBANKEN=${2}
 +                        shift
 +                        ;;
 +                -l)
 +                        LEER=${2}
 +                        shift
 +                        ;;
 +                -h)
 +                        echo "
 +                        HILFE:
 +                        ${0} [Option]
 +                          DB-Host: -h [Hostname]
 +                          DB-Port: -p [Port]
 +                          Tabellen vorher löschen: -t löschen
 +                          Datenbanken: -d [Datenbanken]
 +                          ohne Daten: -l
 +
 +                        ${0} -p 3306 -d \"testdb1 testdb2 testdb3\"
 +                        ${0} -p 3306 -t löschen -d \"testdb1 testdb2 testdb3\"
 +                        ${0} -p 3306 -l -d \"testdb1 testdb2 testdb3\"
 +                        ${0} -h 192.168.1.100 -p 3306 -d \"testdb1 testdb2 testdb3\"
 +                        "
 +                        exit 1
 +                        ;;
 +                *)
 +                        if [ "$(echo "${1}"|egrep '^-')" ] ; then
 +                                echo "Der Parameter '${1}' wird nicht unterstützt!"
 +                        fi
 +                        shift
 +                        ;;
 +        esac
 +done
 +
 +if [ "x${HOST}" == "x" ] ; then
 +        #HOST="localhost"
 +        HOST="127.0.0.1"
 +fi
 +
 +if [ "x${PORT}" == "x" ] ; then
 +        echo "${0} -h"
 +        echo "${0} -p 3306 ..."
 +        exit 11
 +fi
 +
 +if [ "x${LEER}" == "x" ] ; then
 +        LEER="-d"
 +fi
 +
 +if [ "${TABELLEN}" == "löschen" ] ; then
 +        DROP_TABLE="--add-drop-table"           # Tabellen löschen
 +else
 +        DROP_TABLE="--skip-add-drop-table"      # Tabellen nicht löschen
 +fi
 +
 +DBS="$(for D_B in ${DATENBANKEN}
 +do
 +        echo -n "-B ${D_B} "
 +done)"
 +
 +mysqldump -h ${HOST} -P ${PORT} --set-gtid-purged=OFF --opt --triggers --routines ${DROP_TABLE} --order-by-primary --single-transaction --skip-extended-insert -QEc ${LEER} ${DBS}
 +</file>
 +
 +
 +==== Tabellenstruktur einer Datenbank kopieren ====
 +
 +  > mysqldump -d -Y altedb > /tmp/altedb.sql
 +  > echo 'CREATE DATABASE IF NOT EXISTS `neuedb`;' | mysql
 +  > cat /tmp/altedb.sql | mysql neuedb
 +
 +
 +==== komplette Datenbank kopieren ====
 +
 +  > mysqldump -c -Y altedb > /tmp/altedb.sql
 +  > echo 'CREATE DATABASE IF NOT EXISTS `neuedb`;' | mysql
 +  > cat /tmp/altedb.sql | mysql neuedb
 +
 +
 +===== Backup =====
 +
 +==== mysqldump ====
 +
 +Mit mysqldump kann man die kompatibelste Form von einem Backup erstellen.
 +Allerdings trifft das nur dann zu, wenn jede DB einzeln gesichert wird, denn mit der Option "''-A''" werden auch die System-Datenbanken (''information_schema'' und ''performance_schema'') mit gesichert, die zwischen den einzelnen MySQL-Versionen nicht austauschbar sind. Auch unterscheidet sich die ''User''-Tabelle aus der Datenbank ''mysql'' zwischen einigen Versionen.
 +
 +//Weiterhin ist anzumerken, dass das sichern mit ''mysqldump'' zwar recht kompatibel ist aber es ist nicht die schnellst Möglichkeit.//
 +
 +alle Datenbanken zusammen auf einmal in eine einzige Datei sichern:
 +  > mysqldump --opt --triggers --routines --set-gtid-purged=ON -QEcA | gzip -1 > mysqldump_alle_Datenbanken.sql.gz
 +  > mysqldump --opt --triggers --routines --single-transaction --set-gtid-purged=ON -QEcA | gzip -1 > mysqldump_alle_Datenbanken.sql.gz
 +
 +nur eine einzige Datenbank (''Datenbankname'') sichern:
 +  > mysqldump -B Datenbankname --opt --triggers --routines --set-gtid-purged=OFF -QEc | gzip -1 > mysqldump_Datenbankname.sql.gz
 +  > mysqldump --opt --triggers --routines --set-gtid-purged=OFF -QEc Datenbankname | gzip -1 > mysqldump_Datenbankname.sql.gz
 +
 +nur eine Tabelle (''Tabellenname'') aus der Datenbank (''Datenbankname'') sichern:
 +  > mysqldump --opt --triggers --routines --set-gtid-purged=OFF -QEc Datenbankname Tabellenname | gzip -1 > mysqldump_Datenbankname_Tabellenname.sql.gz
 +
 +Hierbei ist auch die Reihenfolge der übergebenen Parameter zu beachten!
 +Zum Beispiel muss "''--opt''" als erstes übergeben werden, weil es eine Sammlung von Parametern ist, von denen man ggf. nachfolgend den einen oder anderen überschreiben möchte.
 +Der Parameter "''-c''" sollte am Ende kommen, da er einen Parameter aus der Sammlung von "''--opt''" überschreibt.
 +
 +
 +==== Sicherung ([database].sql) erstellen ====
 +
 +nur die DB-Struktur sichern:
 +  # mysqldump -d -cA > mysqldb-struktur.sql
 +
 +Dump von allen Datenbanken erstellen:
 +  # mysqldump -uroot -pgeheim -cA > [database].sql
 +
 +Dump von einer bestimmten Datenbank erstellen:
 +  # mysqldump -uroot -pgeheim -cB [database] > [database].sql
 +  # mysqldump -uroot -pgeheim -c [database] > [database].sql
 +
 +Dump von einer bestimmten Tabelle erstellen:
 +  # mysqldump -uroot -pgeheim -c [Datenbank] [Tabelle] > [Datenbank]_[Tabelle].sql
 +
 +Datenbank löschen:
 +  # mysqladmin DROP [database]
 +
 +
 +==== Sicherung ([database].sql) einlesen ====
 +
 +User anlegen, Datenbank anlegen + dump einlesen:
 +  # mysql -u[user] -p[passwort] -e "INSERT INTO user (host,user) VALUES ('localhost','[user]');" mysql
 +  # mysql -u[user] -p[passwort] -e "UPDATE user SET password=password('[passwort]') WHERE User='[user]';" mysql
 +  # mysql -u[user] -p[passwort] reload
 +  # mysql -u[user] -p[passwort] -e "CREATE DATABASE [database];" mysql
 +  # cat [database].sql | mysql -u[user] -p[passwort] [database]
 +
 +ODER
 +  # mysql -u[user] -p[passwort] -e "CREATE DATABASE [database];" mysql
 +  # mysql -u[user] -p[passwort] [database] < [database].sql
 +
 +
 +==== Backup auf Basis von LVM Snapshots ====
 +
 +Möglicherweise wird die Implementierung eines mysql Backup auf Basis von LVM Snapshots, doch nicht allzu schwer.
 +
 +  * [[http://blog.dbadojo.com/2007/09/mysql-backups-using-lvm-snapshots.html]]
 +  * [[http://nitek.bloggt.es/2008/02/23/mysql-backups-mittels-lvm-snapshots/]]
 +
 +Allerdings wurde das ganze hier auf einem Single System umgesetzt und nicht auf einem Master Slave System. Man müsste das ganze dann noch um das Stoppen des Sync vom Master erweitern. Im zweiten Link hat jemand das ganze über ein kleines PHP Script realisiert, angeblich ebenfalls auf einem Singlesystem ohne merkbare Aussetzer.
 +
 +Und hier ein Link zu einer Master Slave Konstellation:
 +
 +  * [[http://wiki.rootforum.de/backup/04_mysql_pitr]]
 +
 +Das Script sieht ebenfalls recht überschaubar aus und sollte mit relativ wenig Aufwand an unsere Umgebung anpassbar sein.
 +
 +
 +
 +===== Daten direkt zwischen Datenbanken kopieren =====
 +
 +
 +==== DB-Layout sichern ====
 +
 +Layout der Tabelle "SystemEvents" aus der DB "Syslog" sichern:
 +  # mysqldump -d Syslog SystemEvents > leere_tabelle.sql
 +
 +Tabellenname ändern
 +  # sed -i 's/SystemEvents/temp/g' leere_tabelle.sql
 +
 +leere temp-Tabelle anlegen
 +  # cat leere_tabelle.sql | mysql -t Syslog
 +
 +alles aus "SystemEvents" nach "temp" kopieren:
 +  echo "
 +  INSERT INTO
 +  SystemEvents
 +  SELECT
 +  *
 +  FROM
 +  temp
 +  ORDER BY ReceivedAt
 +  " | mysql -t Syslog
 +
 +oder nur bestimmte Spalten aus "SystemEvents" nach "temp" kopieren:
 +  echo "
 +  INSERT INTO
 +  SystemEvents
 +  (ReceivedAt,FromHost,Message,SysLogTag)
 +  SELECT
 +  ReceivedAt,FromHost,Message,SysLogTag
 +  FROM
 +  temp
 +  ORDER BY ReceivedAt
 +  " | mysql -t Syslog
 +
 +Jetzt kann man "temp" dumpen und in die Ziel-DB einspielen und dort dann in die entgültige Tabelle kopieren.
 +
 +
 +===== INNODB-Backup =====
 +
 +  * [[http://stefankonarski.de/content/mysqldump-restore-von-innodb-tabellen]]
 +  * [[http://dev.mysql.com/doc/refman/5.1/de/innodb-backup.html]]
 +
 +
 +==== InnoDB Hot Backup ====
 +
 +  * [[http://www.innodb.com/order.html]]
 +
 +InnoDB Hot Backup ist ein Online-Backup-Tool mit dem Sie eine InnoDB-Datenbank bei laufendem Betrieb sichern können. InnoDB Hot Backup verlangt nicht, dass Sie die Datenbank herunterfahren, setzt keine Sperren und stört nicht die normale Datenbankverarbeitung. InnoDB Hot Backup ist ein kostenpflichtiges (kommerzielles) Add-on.
 +
 +
 +==== Binär-Backup ====
 +
 +Wenn Sie in der Lage sind, Ihren MySQL-Server herunterzufahren, können Sie auch ein Binär-Backup aller Dateien erstellen, die InnoDB zur Verwaltung seiner Tabellen benötigt. Gehen Sie folgendermaßen vor:
 +  - Fahren Sie den MySQL-Server herunter und achten Sie darauf, dass dabei keine Fehler auftreten.
 +  - Kopieren Sie alle Datendateien (ibdata-Dateien und ''.ibd''-Dateien) an einen sicheren Ort.
 +  - Kopieren Sie alle ''ib_logfile''-Dateien an einen sicheren Ort.
 +  - Kopieren Sie Ihre ''my.cnf''-Konfigurationsdatei(en) an einen sicheren Ort.
 +  - Kopieren Sie alle ''.frm''-Dateien für Ihre InnoDB-Tabellen an einen sicheren Ort.
 +
 +
 +==== SQL-Backup ====
 +
 +
 +=== mysqldump: Restore von InnoDB Tabellen ===
 +
 +Der Restore von InnoDB-Tabellen ist ein wenig komplizierter, als der von MyISAM-Tabellen.
 +
 +Es gibt zwei Möglichkeiten:
 +
 +
 +== Editieren des mysqldump Backup-File ==
 +
 +Am Anfang des SQL-Skripts folgende Zeilen einfügen:
 +  SET FOREIGN_KEY_CHECKS=0;
 +
 +und am Ende des SQL-Skripts folgendes:
 +  SET FOREIGN_KEY_CHECKS = 1;
 +  COMMIT;
 +
 +Dann lässt sich diese Datei wie ein MyISAM Backup zurückschreiben.
 +
 +Um die ganze Sache zu beschleunigen, kann man entweder am Anfang des SQL-Skripts ''SET AUTOCOMMIT = 0;'' und am Ende ''SET AUTOCOMMIT = 1;'' setzen
 +oder man __schreibt den Dump__ mit der Option __''--opt''__.
 +
 +
 +== Restore über die Kommandozeile ==
 +
 +  # mysql -u$USER -p
 +  use $DATENBANKNAME;
 +  SET FOREIGN_KEY_CHECKS = 0;
 +  source $MYSQLDUMP_FILE_Name;
 +  SET FOREIGN_KEY_CHECKS = 1;
 +  COMMIT;
 +
 +
 +==== DB-Schäden ====
 +
 +In manchen Fällen, in denen Daten beschädigt wurden, reicht es aus, die beschädigten Tabellen zu dumpen, zu löschen und neu zu erzeugen. Mit der SQL-Anweisung ''CHECK TABLE'' finden Sie die meisten Schäden heraus, allerdings nicht jede nur denkbare Art von Datenkorruption. Der ''innodb_tablespace_monitor'' prüft die Integrität des Dateiraum-Managements in den Tablespace-Dateien. 
 +
 +----
 +  * [[http://dev.mysql.com/doc/refman/5.1/de/forcing-recovery.html]]
 +
 +Wenn Schäden an Datenbankseiten vorhanden sind, sollten Sie Ihre Tabellen mit ''SELECT INTO OUTFILE'' dumpen. Normalerweise sind die meisten auf diese Weise geretteten Daten intakt. Trotzdem kann der Schaden dazu führen, dass ''SELECT * FROM tbl_name''-Anweisungen oder ''InnoDB''-Hintergrundoperationen abstürzen oder sich durchsetzen oder gar die ''Roll-forward-Recovery'' von InnoDB abstürzen lassen. Sie können einen Neustart von ''InnoDB'' erzwingen und gleichzeitig die Hintergrundoperationen anhalten, sodass ein Tabellen-Dump möglich ist. Zum Beispiel könnten Sie dem Abschnitt ''[mysqld]'' Ihrer Optionsdatei vor dem Server-Neustart folgende Zeile hinzufügen:
 +  [mysqld]
 +  innodb_force_recovery = 4
 +
 +Die Datenbank darf auf keine andere Weise mit einem von ''null'' verschiedenen ''innodb_force_recovery''-Wert benutzt werden. Zur Sicherheit hindert ''InnoDB'' die Benutzer an ''INSERT-'', ''UPDATE-'' und ''DELETE''-Operationen, wenn ''innodb_force_recovery'' größer als ''0'' ist.
 +
 +
 +===== MysqlPump =====
 +
 +MysqlPump ist eine Alternative zu MysqlDump.
 +MysqlPump ermöglich das sichern der User in logisch definierter Form (mit ''CREATE USER'' und ''GRANT'').
 +
 +Datenbanken/Tabellen sichern (gleicher Aufruf wie bei ''mysqldump''):
 +  > mysqlpump -A
 +  > mysqlpump -B db_name
 +  > mysqlpump db_name tabname
 +
 +User sichern:
 +  > mysqlpump --exclude-databases=% --users
 +