Dies ist eine alte Version des Dokuments!
Inhaltsverzeichnis
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, 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
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.
- /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}
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:
- 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.
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