mysqldump: vytváříme zálohy

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

Utilita mysqldump se nejčastěji používá pro vytvoření zálohy všech databází. Viz Kapitola 4.4.1, „Zálohování databází“.

mysqldump --opt database > backup-file.sql

Naopak lze tento soubor načíst na MySQL serveru pomocí příkazu:

mysql database < backup-file.sql

nebo

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

Tato utilita se poměrně často používá také pro přenos dat z jedné databáze na jiný MySQL server:

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

Lze získat dump několika databází pomocí jednoho příkazu:

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

Pokud je potřeba dump všech databází, lze použít:

mysqldump --all-databases > all_databases.sql

Proberme příklad nejjednoduššího použití: provedeme dump databáze „database“ přesměrováním výstupu do souboru „database.sql“:

kde:

• -u nebo --user=... — uživatelské jméno
• -h nebo --host=... — vzdálený hostitel (pro lokální hostitele lze tento parametr vynechat)
• -p nebo --password — vyzvat k zadání hesla
• database — název databáze
• database.sql — soubor pro dump

Pro provedení dumpu několika databází je třeba použít parametr --databases (nebo zkráceně -B), příklad:

A pro provedení dumpu všech databází je třeba použít parametr --all-databases (nebo zkráceně -A), příklad:

Rozbalení dumpu

Přesměrujeme tok dat opačným směrem a obnovíme databázi:

Nebo přes konzoli mysql:

A pokud máme například gz archiv, pak:

Příklad použití některých parametrů

Předpokládejme, že potřebujeme data z „produkční verze databáze“ pro „vývojářskou verzi“, tedy potřebujeme „písečný testovací prostor“. Vybereme maximálně 100 záznamů:

Nebo potřebujeme pouze strukturu bez dat:

Příklady jsou inspirovány příspěvkem Alexandra Makarova – http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Dumpujeme pouze spouštěče, procedury a události:

Rychlý přehled parametrů

Uvedu některé parametry, které se mohou hodit při práci s nástrojem mysqldump.

--add-drop-database

Před každý příkaz CREATE DATABASE přidá příkaz DROP DATABASE.

--add-drop-table

Před každý příkaz CREATE TABLE přidá příkaz DROP TABLE.

--add-locks

Před provedením a po provedení každého dumpu tabulky přidá příkazy LOCK TABLES a UNLOCK TABLE (pro zrychlení přístupu k MySQL).

--all-databases, -A

Uloží všechny tabulky ze všech databází spravovaných aktuálním serverem.

--allow-keywords

Povolí vytvářet názvy sloupců, které se shodují s klíčovými slovy. Zamezení konfliktům je zajištěno přidáním názvu tabulky jako prefixu k názvu každého sloupce.

--comments, -i

Tato možnost umožňuje přidat do dumpu další informace, jako je verze mysqldump, verze MySQL, název hostitele, na kterém běží server MySQL.

--compact

Tato možnost vyžaduje od mysqldump, aby vytvořil dump pomocí co nejkompatnějšího formátu. Tato možnost je opakem --comments.

--compatible=name

Tato možnost generuje výstup, který je kompatibilní s jinými systémy RDBMS nebo staršími verzemi MySQL. Místo klíčového slova name lze použít: „ansi", „mysql323", „mysql40", „postgresql", „oracle", „mssql", „db2", „maxdb", „no_key_options", „no_table_options", „no_field_options". Lze použít více hodnot oddělených čárkou.

--complete-insert, -c

Použije se úplná forma příkazu INSERT (s názvy sloupců).

--create-options

Přidá další informace do příkazů CREATE TABLE. Může jít o typ tabulky, počáteční hodnotu AUTO_INCREMENT a další parametry.

--databases, -B

Tato možnost umožňuje zadat názvy více databází, pro které má být vytvořen dump.

--delayed

Při vkládání řádků použije příkaz INSERT DELAYED.

--delete-master-logs

Na hlavním replikačním serveru se automaticky odstraní binární logy (logbin) poté, co byl úspěšně vytvořen dump pomocí mysqldump. Tato možnost automaticky aktivuje možnost „--master-data".

--disable-keys, -K

Pro každou tabulku obalí příkaz INSERT výrazy /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; a /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; ve výstupu dumpu. To zrychlí načítání dat na server pro tabulky typu MyISAM, protože indexy jsou vytvářeny až po vložení všech dat.

--extended-insert, -e

Použije příkaz INSERT s novým víceřádkovým syntaxí (zvyšuje kompaktnost a výkon příkazů vložení).

--flush-logs, -F

Před začátkem vytváření dumpu zapíše data systémového deníku z vyrovnávací paměti serveru MySQL na disk.

--force, -f

Pokračovat i v případě, že během vytváření dumpu dojde k chybě.

--hex-blob

Tato možnost umožňuje reprezentovat binární data v polích typu BINARY, VARBINARY, BLOB a BIT v šestnáctkovém formátu. Takže posloupnost „abc" bude nahrazena za 0×616263.

--ignore-table=db_name.tbl_name

Povolí ignorovat tabulku tbl_name databáze db_name při vytváření dumpu. Pokud je třeba vyloučit z dumpu více tabulek, je třeba použít více možností „--ignore-table", přičemž v každé možnosti se uvede jedna tabulka.

--insert-ignore

Přidá klíčové slovo IGNORE do příkazu INSERT.

--lock-all-tables, -x

Uvedení této možnosti způsobí zablokování všech tabulek ve všech databázích po dobu vytváření kompletního dumpu všech databází.

--lock-tables, -l

Uvedení této možnosti způsobí zablokování tabulek databáze, pro kterou je vytvářen dump.

--no-autocommit

Zahrne všechny příkazy INSERT týkající se jedné tabulky do jedné transakce, což vede ke zvýšení rychlosti načítání dat.

--no-create-db, -n

Potlačí vytvoření příkazů CREATE DATABASE v dumpu, které se automaticky přidávají při použití možností --databases a --all-databases.

--no-data, -d

Potlačí vytvoření příkazů INSERT v dumpu, což může být užitečné při vytváření dumpu struktury databáze bez samotných dat.

--opt

Tato možnost je určena pro optimalizaci rychlosti zálohování dat a je zkratkou zahrnující následující volby: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Od verze MySQL 4.1 je možnost --opt výchozí, tj. všechny výše uvedené parametry jsou implicitně povoleny, i když nejsou explicitně uvedeny. Chcete-li tomuto chování zabránit, použijte možnost --skip-opt.

--order-by-primary

Uvedení této možnosti způsobí, že každá tabulka bude seřazena podle primárního klíče nebo prvního unikátního indexu.

--port, -P

Číslo TCP portu používaného pro připojení k hostiteli.

--protocol={TCP|SOCKET|PIPE|MEMORY}

Tato možnost umožňuje zadat protokol připojení k serveru.

--quick, -q

Umožní začít vytvářet dump bez čekání na úplné načtení dat ze serveru, čímž se ušetří paměť.

--quote-names, -Q

Obalí názvy databází, tabulek a sloupců zpětnými apostrofy `. Od verze MySQL 4.1 je tato možnost výchozí.

--replace

Přidá klíčové slovo REPLACE do příkazu INSERT. Tato možnost se poprvé objevila v MySQL 5.1.3.

--result-file=/path/to/file, -r /path/to/file

Tato možnost přesměruje dump do souboru file. Tato možnost je zejména užitečná v systému Windows, bez použití příkazového řádku, kdy lze výsledek přesměrovat do souboru pomocí sekvencí > a >>.

--routines, -R

Tato možnost vytvoří dump uložených procedur a funkcí. Dostupné od MySQL 5.1.2.

--single-transaction

Tato možnost vytvoří dump jako jednu transakci.

--skip-comments

Tato možnost umožňuje potlačit výstup dalších informací do dumpu.

--socket=/path/to/socket, -S /path/to/socket

Soubor soketu pro připojení k localhost.

--tab=/path/, -T /path/

Při použití této možnosti se v adresáři path pro každou tabulku vytvoří dva samostatné soubory: tbl_name.sql, který obsahuje příkaz CREATE TABLE, a tbl_name.txt, který obsahuje data tabulek oddělená znakem tabulátoru. Formát dat lze explicitně přepsat pomocí možností --fields-xxx a --lines-xxx.

--tables

Přepíše akci možnosti --databases (-B). Všechny argumenty následující za touto možností jsou považovány za názvy tabulek.

--triggers

Vytvoří se dump spouštěčů. Tato možnost je výchozí. Pro její vypnutí použijte možnost --skip-triggers.

--events, -E

Vytvoří se dump událostí. Viz MySQL Event Scheduler nebo vestavěný správce událostí v MySQL.

--tz-utc

Při použití této možnosti bude do dumpu přidán příkaz ve formátu SET TIME_ZONE='+00:00', což umožní výměnu dumpu v různých časových pásmech.

--verbose, -v

Rozšířený režim výstupu. Výstup podrobnějších informací o činnosti programu.

--version, -V

Vypsat informace o verzi programu.

--where='where-condition', -w 'where-condition'

Vykonat dump pouze vybraných záznamů. Všimněte si, že uvozovky jsou povinné.

--xml, -X

Představí dump databáze ve formátu XML.

--first-slave, -x

Zablokuje všechny tabulky ve všech databázích.

--debug=..., -#

Sledovat průběh programu (pro ladění).

--help

Vypsat nápovědu a ukončit program.