09.12.2024

Kompletny przewodnik po mysqldump – programie do tworzenia kopii zapasowych baz danych

mysqldump to narzędzie wiersza poleceń udostępniane przez MySQL, które umożliwia tworzenie logicznych kopii zapasowych baz danych MySQL. Narzędzie to jest szeroko stosowane do tworzenia kopii zapasowych baz danych, eksportowania baz danych na inne serwery i migracji baz danych między wersjami MySQL lub różnymi systemami. Jest to prosty, wydajny i elastyczny sposób tworzenia kopii zapasowych i przywracania baz danych, co czyni go kluczowym narzędziem dla każdego administratora bazy danych.

Ten kompletny przewodnik obejmuje wszystko, co musisz wiedzieć o mysqldump, w tym instalację, składnię, przykłady użycia i najlepsze praktyki.

Czym jest mysqldump?

mysqldump tworzy logiczną kopię zapasową bazy danych MySQL, co oznacza, że wyprowadza dane i strukturę w postaci poleceń SQL.
Te polecenia SQL mogą być następnie wykorzystane do odtworzenia bazy danych, dzięki czemu mysqldump jest idealny do tworzenia kopii zapasowych, migracji i transferu danych.
Obsługuje eksportowanie całych baz danych, pojedynczych tabel lub określonych danych z różnymi opcjami dostosowywania.

Podstawowa składnia mysqldump

Podstawową składnią polecenia mysqldump jest:

mysqldump [OPTIONS] database_name [nazwa_bazy] [tables] > backup_file.sql

[OPTIONS]: Opcjonalne flagi modyfikujące zachowanie mysqldump (np. określające użytkownika, hasło, hosta itp.).
database_name: Nazwa bazy danych, której kopię zapasową chcesz utworzyć.
[tables]: (Opcjonalnie) Określone tabele do utworzenia kopii zapasowej. W przypadku pominięcia, kopia zapasowa zostanie utworzona dla całej bazy danych.
> backup_file.sql: Przekierowuje dane wyjściowe do pliku (backup_file.sql), który będzie zawierał polecenia SQL do odtworzenia bazy danych.

Wymagania wstępne

MySQL lub MariaDB zainstalowane na serwerze lub kliencie.
Konto użytkownika z wystarczającymi uprawnieniami (uprawnienia SELECT i LOCK) w bazie danych, której kopia zapasowa ma zostać utworzona.
Dostęp do wiersza poleceń (terminala) w systemie Linux/macOS lub wiersza poleceń w systemie Windows.

Najczęściej używane opcje mysqldump

-u lub –user: Określa nazwę użytkownika MySQL.
-p lub –password: Pyta o hasło użytkownika.
-h lub –host: Określa hosta (np. localhost lub adres IP).
-P lub –port: Określa numer portu dla serwera MySQL.
–databases: Umożliwia tworzenie kopii zapasowych wielu baz danych.
–all-databases: Tworzy kopię zapasową wszystkich baz danych na serwerze.
–no-data: Eksportuje tylko strukturę bazy danych (bez danych).
–single-transaction: Wykonuje spójną migawkę bazy danych, przydatną dla tabel InnoDB.
–add-drop-table: Dodaje instrukcje DROP TABLE przed instrukcjami CREATE TABLE, zapewniając, że tabele są usuwane i odtwarzane podczas przywracania.
–routines: Obejmuje procedury składowane i funkcje w kopii zapasowej.
–triggers: Uwzględnia wyzwalacze w kopii zapasowej.
–compress: Kompresuje dane przesyłane między klientem a serwerem MySQL, jeśli jest to obsługiwane.

Podstawowe przykłady

Przykład 1: Tworzenie kopii zapasowej pojedynczej bazy danych

mysqldump -u root -p database_name > backup_file.sql

Pyta o hasło użytkownika root.
Zrzuca zawartość pliku database_name do pliku backup_file.sql.

Przykład 2: Tworzenie kopii zapasowej wielu baz danych

mysqldump -u root -p –databases database1 database2 > multiple_databases_backup.sql

Użyj opcji –databases, a następnie nazw baz danych.
Zrzuca bazy danych database1 i database2 do pliku multiple_databases_backup.sql.

Przykład 3: Tworzenie kopii zapasowej wszystkich baz danych

mysqldump -u root -p –all-databases > all_databases_backup.sql

Tworzy kopię zapasową wszystkich baz danych na serwerze MySQL.
Zawiera instrukcje CREATE DATABASE i USE, dzięki czemu można łatwo przywrócić wszystkie bazy danych.

Przykład 4: Tworzenie kopii zapasowej określonej tabeli

mysqldump -u root -p database_name table_name > table_backup.sql

Zrzuca strukturę i dane określonej tabeli (table_name) z bazy danych database_name do pliku table_backup.sql.

Przykład 5: Tworzenie kopii zapasowej tylko struktury bazy danych

mysqldump -u root -p –no-data database_name > database_structure.sql

Opcja –no-data wyklucza dane i eksportuje tylko struktury tabel.

Zaawansowane przykłady

Przykład 6: Korzystanie z opcji –single-transaction dla dużych baz danych

mysqldump -u root -p –single-transaction database_name > large_backup.sql

Opcja –single-transaction jest idealna dla tabel InnoDB i zapewnia spójną kopię zapasową bez blokowania tabel.
Zalecana dla dużych baz danych, ponieważ minimalizuje czas blokowania tabel.

Przykład 7: Skompresowana kopia zapasowa

mysqldump -u root -p database_name | gzip > backup_file.sql.gz

Używa gzip do kompresji danych wyjściowych mysqldump w locie.
Skutkuje to mniejszym plikiem kopii zapasowej (backup_file.sql.gz), oszczędzając miejsce na dysku.

Przykład 8: Dołączanie procedur składowanych, funkcji i wyzwalaczy

mysqldump -u root -p –routines –triggers database_name > full_backup.sql

Opcja –routines obejmuje procedury i funkcje składowane.
Opcja –triggers obejmuje wyzwalacze w kopii zapasowej.

Przykład 9: Wykluczanie określonych tabel

mysqldump -u root -p database_name –ignore-table=database_name.table_to_ignore > backup.sql

Opcja –ignore-table wyklucza określoną tabelę z kopii zapasowej.
Opcję tę można powtórzyć dla każdej tabeli, która ma zostać wykluczona.

Przywracanie kopii zapasowej mysqldump

Aby przywrócić kopię zapasową utworzoną za pomocą mysqldump, należy użyć polecenia mysql:

Przykład 10: Przywracanie pojedynczej bazy danych

mysql -u root -p database_name < backup_file.sql

Pyta o hasło roota.
Przywraca bazę danych z pliku backup_file.sql.

Przykład 11: Przywracanie wielu baz danych lub wszystkich baz danych

mysql -u root -p < all_databases_backup.sql

To polecenie może być użyte do przywrócenia wielu baz danych lub wszystkich baz danych, jeśli plik all_databases_backup.sql został utworzony przy użyciu opcji –databases lub –all-databases.

Przykład 12: Przywracanie ze skompresowanej kopii zapasowej

gunzip < backup_file.sql.gz | mysql -u root -p database_name

Używa gunzip do dekompresji pliku kopii zapasowej w locie i przesyła dane wyjściowe do polecenia mysql.

Najlepsze praktyki dotyczące korzystania z mysqldump

Użyj –single-transaction dla InnoDB:
- Ta opcja zapewnia spójną kopię zapasową bez blokowania tabel bazy danych, co jest szczególnie ważne w przypadku dużych baz danych.
Zaplanuj regularne tworzenie kopii zapasowych:
- Użyj zadań cron w systemie Linux, aby zautomatyzować proces tworzenia kopii zapasowych. Na przykład, utwórz codzienne zadanie tworzenia kopii zapasowej:
  0 2 * * * mysqldump -u root -pYourPassword database_name > /path/to/backup/database_name_$(date \%F).sql
- To zadanie tworzy kopię zapasową codziennie o 2 nad ranem.
Przechowuj kopie zapasowe poza siedzibą firmy:
- Przechowuj kopie zapasowe na innym serwerze lub w chmurze, takiej jak AWS S3 lub Dysk Google, aby zapewnić odzyskiwanie danych w przypadku awarii sprzętu.
Weryfikuj kopie zapasowe:
- Okresowo sprawdzaj, czy kopie zapasowe można prawidłowo przywrócić, testując je w oddzielnym środowisku.
Używaj kompresji dla dużych baz danych:
- Użyj gzip lub bzip2, aby skompresować pliki kopii zapasowych i zaoszczędzić miejsce na dysku.
Względy bezpieczeństwa:
- Unikaj określania haseł bezpośrednio w wierszu poleceń, ponieważ może to być widoczne dla innych użytkowników. Zamiast tego należy użyć pliku konfiguracyjnego lub poprosić o podanie hasła.
- Należy chronić pliki kopii zapasowych za pomocą odpowiednich uprawnień, aby zapobiec nieautoryzowanemu dostępowi.

Wnioski

mysqldump to wszechstronne i potężne narzędzie do zarządzania kopiami zapasowymi baz danych MySQL. Zrozumienie jego składni i opcji pozwala tworzyć kopie zapasowe dostosowane do konkretnych potrzeb, niezależnie od tego, czy masz do czynienia z dużymi bazami danych, migracją danych czy konfigurowaniem automatycznych kopii zapasowych. Postępuj zgodnie z najlepszymi praktykami, aby upewnić się, że Twoje dane są zawsze bezpieczne i można je przywrócić w razie potrzeby.