Webseiten-Werkzeuge


mysql-dump

MySQL-Dump

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, mit 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, ohne 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

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.

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.

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:

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

InnoDB Hot Backup

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:

  1. Fahren Sie den MySQL-Server herunter und achten Sie darauf, dass dabei keine Fehler auftreten.
  2. Kopieren Sie alle Datendateien (ibdata-Dateien und .ibd-Dateien) an einen sicheren Ort.
  3. Kopieren Sie alle ib_logfile-Dateien an einen sicheren Ort.
  4. Kopieren Sie Ihre my.cnf-Konfigurationsdatei(en) an einen sicheren Ort.
  5. 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.


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
mysql-dump.txt · Zuletzt geändert: 2021/05/12 11:48 von manfred