mysqldump: tworzenie kopii zapasowych

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

Najczęściej narzędzie mysqldump jest używane do uzyskania kopii zapasowej wszystkich baz danych. Zobacz Rozdział 4.4.1, „Tworzenie kopii zapasowych baz danych”.

mysqldump --opt database > backup-file.sql

Można natomiast odczytać ten plik na serwerze MySQL za pomocą polecenia:

mysql database < backup-file.sql

lub

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

To narzędzie jest również często używane do przenoszenia danych z bazy danych na inny serwer MySQL:

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

Możliwe jest również uzyskanie zrzutu kilku baz danych za pomocą jednego polecenia:

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

Jeśli potrzebny jest zrzut wszystkich baz danych, można użyć:

mysqldump --all-databases > all_databases.sql

Przeanalizujmy przykład najprostszego użycia, wykonajmy zrzut bazy danych „database” poprzez przekierowanie strumienia do pliku „database.sql”:

gdzie:

• -u lub --user=... — nazwa użytkownika
• -h lub --host=... — zdalny host (dla hosta lokalnego można pominąć ten parametr)
• -p lub --password — poproś o hasło
• database — nazwa bazy danych
• database.sql — plik do zrzutu

Aby wykonać zrzut kilku baz danych, należy użyć parametru --databases (lub skróconego -B), przykład:

Aby wykonać zrzut wszystkich baz danych, należy użyć parametru --all-databases (lub skróconego -A), przykład:

Przywracanie zrzutu

Kierujemy strumień w przeciwnym kierunku i przywracamy bazę danych:

Lub przez konsolę mysql:

A jeśli mamy archiwum gz, na przykład, to:

Przykład użycia niektórych parametrów

Na przykład, potrzebujemy danych z „wersji produkcyjnej bazy” dla „wersji deweloperskiej”, czyli potrzebujemy „piaskownicy”. Wybieramy nie więcej niż 100 rekordów:

Lub potrzebujemy tylko struktury, bez danych:

Przykłady inspirowane postem Aleksandra Makarowa – http://rmcreative.ru/blog/post/ljogkiy-damp-mysql

Tworzymy dump tylko triggerów, procedur i zdarzeń:

Skrót do parametrów

Poniżej przedstawiam niektóre parametry, które mogą być potrzebne podczas pracy z narzędziem mysqldump.

--add-drop-database

Dodaje instrukcję DROP DATABASE przed każdą instrukcją CREATE DATABASE.

--add-drop-table

Dodaje instrukcję DROP TABLE przed każdą instrukcją CREATE TABLE.

--add-locks

Dodaje instrukcję LOCK TABLES przed wykonaniem oraz UNLOCK TABLE po wykonaniu zrzutu każdej tabeli (w celu przyspieszenia dostępu do MySQL).

--all-databases, -A

Zapisuje wszystkie tabele ze wszystkich baz danych zarządzanych przez bieżący serwer.

--allow-keywords

Pozwala na tworzenie nazw kolumn, które pokrywają się ze słowami kluczowymi. Unikanie konfliktów jest zapewniane poprzez dodanie nazwy tabeli jako prefiksu do nazwy każdej kolumny.

--comments, -i

Opcja ta umożliwia dodanie do zrzutu dodatkowych informacji, takich jak wersja mysqldump, wersja MySQL, nazwa hosta, na którym znajduje się serwer MySQL.

--compact

Opcja ta wymusza na mysqldump utworzenie zrzutu w jak najbardziej zwartym formacie. Opcja ta jest przeciwieństwem --comments.

--compatible=name

Opcja generuje wyjście zgodne z innymi systemami zarządzania bazami danych (RDBMS) lub starszymi wersjami MySQL. Zamiast słowa kluczowego name można użyć: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Można użyć wielu wartości, oddzielając je przecinkami.

--complete-insert, -c

Używa pełnej formy instrukcji INSERT (z nazwami kolumn).

--create-options

Dodaje dodatkowe informacje do instrukcji CREATE TABLE. Może to być typ tabeli, początkowa wartość AUTO_INCREMENT oraz inne parametry.

--databases, -B

Opcja ta pozwala podać nazwy wielu baz danych, dla których należy utworzyć zrzut.

--delayed

Używa polecenia INSERT DELAYED podczas wstawiania wierszy.

--delete-master-logs

Na głównym serwerze replikacji automatycznie usuwane są logi binarne (logbin) po pomyślnym utworzeniu zrzutu przy użyciu mysqldump. Opcja ta automatycznie włącza opcję „--master-data”.

--disable-keys, -K

Dla każdej tabeli otacza instrukcję INSERT wyrażeniami /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; i /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; w wyjściu zrzutu. Przyspieszy to ładowanie danych na serwer dla tabel typu MyISAM, ponieważ indeksy są tworzone po wprowadzeniu wszystkich danych.

--extended-insert, -e

Używa polecenia INSERT z nowym składnią wielowierszową (zwiększa zwartość i wydajność instrukcji wprowadzania).

--flush-logs, -F

Zapisuje na dysk dane dziennika systemowego z bufora serwera MySQL przed rozpoczęciem tworzenia zrzutu.

--force, -f

Kontynuuj nawet jeśli podczas tworzenia zrzutu wystąpił błąd.

--hex-blob

Opcja ta umożliwia przedstawienie danych binarnych w polach typu BINARY, VARBINARY, BLOB i BIT w formacie szesnastkowym. W ten sposób sekwencja „abc” zostanie zastąpiona przez 0×616263.

--ignore-table=db_name.tbl_name

Pozwala zignorować tabelę tbl_name bazy danych db_name podczas tworzenia zrzutu. Jeśli z zrzutu należy wykluczyć wiele tabel, należy użyć wielu opcji „--ignore-table”, podając po jednej tabeli w każdej z nich.

--insert-ignore

Dodaje słowo kluczowe IGNORE do instrukcji INSERT.

--lock-all-tables, -x

Użycie tej opcji powoduje zablokowanie wszystkich tabel we wszystkich bazach danych na czas tworzenia pełnego zrzutu wszystkich baz danych.

--lock-tables, -l

Użycie tej opcji powoduje zablokowanie tabel bazy danych, dla której tworzony jest zrzut.

--no-autocommit

Włącza wszystkie instrukcje INSERT dotyczące jednej tabeli do jednej transakcji, co prowadzi do zwiększenia szybkości ładowania danych.

--no-create-db, -n

Wyłącza tworzenie w zrzucie instrukcji CREATE DATABASE, które są automatycznie dodawane przy użyciu opcji --databases i --all-databases.

--no-data, -d

Wyłącza tworzenie instrukcji INSERT w zrzucie, co może być przydatne przy tworzeniu zrzutu struktury bazy danych bez samych danych.

--opt

Opcja ta służy do optymalizacji szybkości tworzenia kopii zapasowych i jest skrótem obejmującym następujące opcje: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Począwszy od MySQL 4.1, opcja --opt jest używana domyślnie, tzn. wszystkie powyższe parametry są włączone domyślnie, nawet jeśli nie są podane. Aby wykluczyć takie zachowanie, należy użyć opcji --skip-opt

--order-by-primary

Użycie tej opcji powoduje, że każda tabela jest sortowana według klucza głównego lub pierwszego unikalnego indeksu.

--port, -P

Numer portu TCP używanego do łączenia się z hostem.

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

Opcja ta pozwala określić protokół łączenia się z serwerem.

--quick, -q

Pozwala rozpocząć tworzenie zrzutu, nie czekając na pełne załadowanie danych z serwera, oszczędzając tym samym pamięć.

--quote-names, -Q

Otacza nazwy baz danych, tabel i kolumn apostrofami odwrotnymi `. Począwszy od MySQL 4.1, ta opcja jest włączona domyślnie.

--replace

Dodaje słowo kluczowe REPLACE do instrukcji INSERT. Opcja ta po raz pierwszy pojawiła się w MySQL 5.1.3.

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

Opcja ta kieruje zrzut do pliku file. Opcja ta jest szczególnie wygodna w systemie Windows, bez użycia wiersza poleceń, gdy można przekierować wynik do pliku za pomocą sekwencji > i >>.

--routines, -R

Opcja ta tworzy zrzut procedur i funkcji przechowywanych. Dostępna od MySQL 5.1.2.

--single-transaction

Opcja ta tworzy zrzut w postaci jednej transakcji.

--skip-comments

Opcja ta pozwala wyłączyć wyświetlanie dodatkowych informacji w zrzucie.

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

Plik gniazda do połączenia z localhost.

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

Podczas używania tej opcji w katalogu path dla każdej tabeli tworzone są dwa osobne pliki: tbl_name.sql, zawierający instrukcję CREATE TABLE, oraz tbl_name.txt, który zawiera dane tabeli rozdzielone znakiem tabulacji. Format danych może być jawnie nadpisany za pomocą opcji --fields-xxx i --lines-xxx.

--tables

Nadpisuje działanie opcji --databases (-B). Wszystkie argumenty następujące po tej opcji są traktowane jako nazwy tabel.

--triggers

Tworzony jest zrzut wyzwalaczy. Opcja ta jest włączona domyślnie. Aby ją wyłączyć, należy użyć opcji --skip-triggers.

--events, -E

Tworzony jest zrzut zdarzeń. Zobacz MySQL Event Scheduler lub wbudowany menedżer zdarzeń w MySQL.

--tz-utc

Podczas używania tej opcji do zrzutu zostanie dodana instrukcja w rodzaju SET TIME_ZONE='+00:00', co pozwoli na wymianę zrzutów w różnych strefach czasowych.

--verbose, -v

Tryb szczegółowego wyjścia. Wyświetlanie bardziej szczegółowych informacji o działaniu programu.

--version, -V

Wyświetl informacje o wersji programu.

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

Wykonaj zrzut tylko wybranych rekordów. Należy pamiętać, że cudzysłowy są obowiązkowe.

--xml, -X

Prezentuje zrzut bazy danych w formacie XML.

--first-slave, -x

Blokuje wszystkie tabele we wszystkich bazach danych.

--debug=..., -#

Śledź przebieg programu (do celów debugowania).

--help

Wyświetl informacje o pomocy i zakończ działanie programu.