Note: MySQL Dump erstellen und importieren

Um grosse MySQL Dump-Files (BKP-Dump > 100MB) wieder in eine Datenbank zu importieren verwendet man am besten eine SHELL auf dem Ziel-Datenbank Server.

Dump / Backup erstellen:

–complete-insert #Use complete INSERT statements that include column names
–comments #Add comments to dump file
–compress #Compress all information sent between client and server
–dump-date #Include dump date as „Dump completed on“ comment if –comments is given
–events #Dump events from dumped databases
–single-transaction #Issue a BEGIN SQL statement before dumping data from server
–quick #Retrieve rows for a table from the server a row at a time
–routines #Dump stored routines (procedures and functions) from dumped databases
–triggers #Dump triggers for each dumped table
–set-gtid-purged=OFF

server:~# mysqldump --user=[USERNAME] --password --host=[HOSTNAME] \
--set-gtid-purged=OFF \
--complete-insert \
--comments \
--compress \
--dump-date \
--events \
--single-transaction \
--quick \
--routines \
--triggers \
[DBNAME] > ./[DBNAME].`date +\%Y\%m\%d-\%H\%M`.sql

Dumpfile packen:

server:~# tar cfz [DUMPfilename].tar.gz [DUMPfilename.sql]

entpacken des Dumpfile:

server:~# tar -xzf [DUMPfilename].tar.gz

BKP-Datenbank anlegen:

server:~# mysqladmin --user=[USERNAME] --password \
--host=[HOSTNAME] \
CREATE [DBNAME]

Backup importieren:

server:~# mysql --user=[USERNAME] --password \
--host=[HOSTNAME] \
[DBNAME] < [DUMPfilename.sql]