Kompletny przewodnik po mysqldump: tworzenie kopii zapasowych, przywracanie i automatyzacja baz danych MySQL

mysqldump to narzędzie wiersza poleceń dołączone do MySQL i MariaDB, które generuje logiczne kopie zapasowe poprzez serializację obiektów bazy danych i danych jako sekwencji instrukcji SQL. Wynikowy plik zrzutu może odtworzyć identyczną bazę danych na dowolnym kompatybilnym serwerze, co czyni go standardowym narzędziem branżowym do tworzenia kopii zapasowych, migracji między serwerami, aktualizacji wersji i procesów odzyskiwania po awarii.

W przeciwieństwie do fizycznych narzędzi do tworzenia kopii zapasowych, takich jak Percona XtraBackup lub MySQL Enterprise Backup, mysqldump działa na warstwie SQL — odczytuje dane na żywo przez protokół MySQL i zapisuje przenośny, czytelny dla człowieka SQL. Ta przenośność jest jego największą zaletą i, w dużej skali, jego głównym ograniczeniem.

Co mysqldump faktycznie robi pod maską

Gdy wywołujesz mysqldump, klient łączy się z serwerem MySQL, odpytuje schemat informacyjny i słownik danych, a następnie emituje strumień instrukcji `CREATE DATABASE`, `CREATE TABLE`, `INSERT` i DDL na standardowe wyjście. Przekierowujesz ten strumień do pliku, potoku lub narzędzia kompresji.

W przypadku tabel InnoDB z `–single-transaction`, mysqldump otwiera transakcję z powtarzalnym odczytem przed odczytaniem jakichkolwiek danych. Daje to spójną migawkę punktu w czasie bez konieczności uzyskiwania globalnych blokad odczytu — baza danych pozostaje w pełni zapisywalna podczas zrzutu. W przypadku tabel MyISAM taki mechanizm nie istnieje; mysqldump cofa się do `FLUSH TABLES WITH READ LOCK`, co chwilowo blokuje zapisy.

Zrozumienie tej różnicy jest kluczowe przed wybraniem mysqldump dla obciążeń produkcyjnych. Jeśli Twój schemat miesza tabele InnoDB i MyISAM, samo `–single-transaction` jest niewystarczające — będziesz potrzebować `–lock-all-tables` lub okna konserwacyjnego.

Wymagania wstępne i wymagane uprawnienia

Przed uruchomieniem jakiegokolwiek polecenia zrzutu sprawdź następujące kwestie:

• MySQL lub MariaDB jest zainstalowany i dostępny (lokalny socket lub TCP/IP).
• Użytkownik kopii zapasowej posiada minimalne wymagane uprawnienia:
• `SELECT` na wszystkich docelowych tabelach
• `LOCK TABLES` (wymagane, chyba że `–single-transaction` jest używane wyłącznie z InnoDB)
• `SHOW VIEW` aby uwzględnić widoki
• `TRIGGER` aby uwzględnić wyzwalacze
• `PROCESS` przy użyciu `–single-transaction` na MySQL 8+
• `RELOAD` dla `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` jeśli potrzebujesz współrzędnych binarnego dziennika do konfiguracji replikacji

Utwórz dedykowanego użytkownika kopii zapasowych zamiast uruchamiać zrzuty jako root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Uruchamianie mysqldump jako root z hasłem osadzonym w poleceniu powłoki ujawnia dane uwierzytelniające na listach procesów i w historii powłoki — co stanowi poważne zagrożenie bezpieczeństwa w każdym współdzielonym lub wieloużytkownikowym systemie.

Podstawowa składnia

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Pełna dokumentacja opcji

Opcje połączenia

Opcje zakresu

Opcje spójności i blokowania

Opcje wyjścia i zawartości

mysqldump a alternatywne metody tworzenia kopii zapasowych

Wybór właściwej strategii tworzenia kopii zapasowych zależy od rozmiaru bazy danych, wymagań RTO/RPO i infrastruktury. Oto jak mysqldump wypada w porównaniu z najczęstszymi alternatywami:

W przypadku baz danych poniżej 10–20 GB w środowisku Hostingu VPS, mysqldump pozostaje najbardziej praktycznym i przenośnym rozwiązaniem. Powyżej tego progu narzędzia do fizycznych kopii zapasowych oferują znacznie szybsze okna tworzenia i przywracania kopii zapasowych.

Praktyczne przykłady użycia

Przykład 1: Kopia zapasowa pojedynczej bazy danych

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

Podstawienie `$(date +%F)` automatycznie dołącza datę ISO (np. `2025-07-15`) do nazwy pliku, zapobiegając nadpisywaniu.

Przykład 2: Kopia zapasowa wielu określonych baz danych

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

Flaga `–databases` powoduje, że mysqldump emituje instrukcje `CREATE DATABASE` i `USE`, dzięki czemu zrzut jest samowystarczalny do przywracania.

Przykład 3: Kopia zapasowa wszystkich baz danych

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Zawsze uwzględniaj `–events`, `–routines` i `–triggers` w zrzutach całego serwera. Te obiekty są po cichu pomijane bez jawnych flag.

Przykład 4: Spójna kopia zapasowa InnoDB (bezpieczna dla produkcji)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` rotuje binarny dziennik na początku zrzutu. `–source-data=2` zapisuje bieżącą nazwę pliku binarnego dziennika i pozycję jako komentarz SQL, umożliwiając odzyskiwanie do punktu w czasie poprzez odtwarzanie kolejnych binlogów od tej pozycji.

Przykład 5: Skompresowana kopia zapasowa z gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

W przypadku serwerów z ograniczonym CPU, zastąp `pigz` (równoległy gzip), aby wykorzystać wiele rdzeni:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Przykład 6: Kopia zapasowa tylko schematu (struktura bez danych)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Przydatne do kontroli wersji schematu w Git lub wdrażania w środowisku testowym bez kopiowania danych produkcyjnych.

Przykład 7: Kopia zapasowa tylko danych (bez schematu)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Użyj tego, gdy docelowy schemat już istnieje i potrzebujesz tylko wypełnić lub odświeżyć dane.

Przykład 8: Kopia zapasowa pojedynczej tabeli

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Przykład 9: Eksport filtrowanego podzbioru wierszy

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

Opcja `–where` jest rzadko używana, ale niezwykle przydatna do częściowych eksportów, archiwizacji danych i debugowania określonych zestawów rekordów.

Przykład 10: Wykluczanie określonych tabel

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Wykluczenie dużych, tymczasowych tabel (pamięci podręczne, magazyny sesji, tabele logów) może zmniejszyć rozmiar i czas trwania zrzutu o rząd wielkości.

Przykład 11: Uwzględnianie procedur składowanych, funkcji i wyzwalaczy

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Przykład 12: Kopia zapasowa zdalnej bazy danych

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Podczas tworzenia kopii zapasowej zdalnego serwera, ruch domyślnie przechodzi przez sieć bez szyfrowania. Dodaj flagi `–ssl-ca`, `–ssl-cert` i `–ssl-key` lub tuneluj przez SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Przywracanie kopii zapasowej mysqldump

Przywracanie pojedynczej bazy danych

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Jeśli docelowa baza danych jeszcze nie istnieje, najpierw ją utwórz:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Przywracanie wszystkich baz danych z pełnego zrzutu serwera

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Ponieważ `–all-databases` osadza instrukcje `CREATE DATABASE` i `USE`, nie jest potrzebny argument docelowej bazy danych.

Przywracanie ze skompresowanej kopii zapasowej

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Lub przy użyciu podstawiania procesów:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Przywracanie pojedynczej tabeli z pełnego zrzutu bazy danych

Jest to częsty scenariusz operacyjny, który oryginalny plik zrzutu czyni nietrywialnym. Użyj `sed` lub `grep` aby wyodrębnić odpowiednią sekcję:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternatywnie użyj `mysql_extract_table.sh` lub zaimportuj do tymczasowej bazy danych i skopiuj tabelę:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Odzyskiwanie do punktu w czasie przy użyciu binarnych dzienników

Jeśli Twój zrzut został wykonany z `–source-data=2` i binarne logowanie jest włączone, możesz odzyskać dane do dowolnego punktu po zrzucie:

1. Zidentyfikuj pozycję binarnego dziennika z komentarza nagłówka pliku zrzutu.
2. Przywróć bazowy zrzut.
3. Zastosuj kolejne zdarzenia binarnego dziennika do żądanego znacznika czasu:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Automatyzacja kopii zapasowych za pomocą Cron

Podstawowe zadanie codziennej kopii zapasowej

Przechowuj dane uwierzytelniające w `~/.my.cnf` zamiast osadzać je w poleceniach cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Ustaw restrykcyjne uprawnienia:

“`bash

chmod 600 ~/.my.cnf

“`

Następnie utwórz zadanie cron:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Skrypt kopii zapasowej klasy produkcyjnej

W przypadku Serwerów Dedykowanych hostujących wiele baz danych, bardziej rozbudowany skrypt obsługuje rejestrowanie błędów, sprawdzanie miejsca na dysku i zdalne odciążanie:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Wzmacnianie bezpieczeństwa operacji mysqldump

Zarządzanie danymi uwierzytelniającymi jest najczęściej zaniedbywanym aspektem bezpieczeństwa kopii zapasowych. Nigdy nie przekazuj `-pYourPassword` bezpośrednio w wierszu poleceń — jest widoczny w wyjściu `ps aux` i historii powłoki. Zamiast tego użyj jednego z tych podejść:

• `~/.my.cnf` z `chmod 600` (per-użytkownik)
• `/etc/mysql/backup.cnf` z `chmod 640`, własnością roota, czytelny przez grupę kopii zapasowych
• Zmienna środowiskowa `MYSQL_PWD` (widoczna w `/proc`, używaj tylko w izolowanych kontenerach)
• MySQL Vault lub HashiCorp Vault dla środowisk korporacyjnych

Uprawnienia pliku kopii zapasowej muszą być restrykcyjne:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Szyfrowanie w spoczynku: W przypadku wrażliwych danych szyfruj pliki kopii zapasowych przed ich przechowywaniem lub przesyłaniem:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Szyfrowanie transportu: Podczas zrzucania danych ze zdalnego serwera zawsze używaj SSL/TLS lub tunelu SSH. W środowisku VPS z cPanel, interfejs kopii zapasowych cPanel obsługuje to automatycznie, ale ręczne operacje mysqldump wymagają jawnych flag SSL.

Typowe pułapki i jak ich unikać

Niezgodności zestawów znaków są najczęstszą przyczyną uszkodzonych przywróceń. Zawsze jawnie określaj zestaw znaków:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Brakujące `–column-statistics=0` powoduje błędy, gdy klient MySQL 8.0 zrzuca dane z serwera MySQL 5.7 lub MariaDB. Klient MySQL 8 próbuje zrzucić statystyki kolumn, których starsze serwery nie obsługują:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Zapominanie o `–routines`, `–triggers` i `–events` po cichu pomija krytyczne obiekty bazy danych. Te flagi nie są domyślnie włączone (z wyjątkiem `–triggers`) i są często zapominane w doraźnych zrzutach.

Zrzuty dużych tabel powodujące OOM: mysqldump domyślnie buforuje całe zestawy wyników w pamięci. W przypadku bardzo dużych tabel dodaj `–quick` (domyślnie włączone w większości wersji, ale warto to zweryfikować), aby przesyłać wiersze strumieniowo jeden po drugim zamiast buforować:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Przywracanie do innej wersji MySQL: Zrzuty z MySQL 8.0 mogą zawierać składnię nieobsługiwaną w MySQL 5.7 (np. indeksy funkcjonalne, niewidoczne kolumny). Zawsze testuj przywracanie w środowisku z dopasowaną wersją przed poleganiem na migracjach między wersjami.

Dryf wartości auto-increment: Jeśli przywracasz tabelę do istniejącego schematu, który już zawiera wiersze, instrukcje `INSERT` zakończą się niepowodzeniem z powodu konfliktów klucza głównego, chyba że uwzględnisz `–add-drop-table` lub ręcznie wyczyścisz docelową tabelę.

Używanie mysqldump do migracji baz danych

mysqldump jest standardowym podejściem do migracji baz danych między serwerami — na przykład podczas przenoszenia witryny WordPress z Współdzielonego Hostingu WWW na VPS lub przenoszenia na środowisko VPS z Panelem Sterowania z większymi zasobami.

Zalecany przepływ pracy migracji:

1. Zrzuć źródłową bazę danych z pełnymi opcjami:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Prześlij bezpiecznie używając rsync przez SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Utwórz docelową bazę danych z pasującym zestawem znaków:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Przywróć i zweryfikuj:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Zaktualizuj konfigurację aplikacji, aby wskazywała na nowego hosta bazy danych.

W przypadku aplikacji korzystających również z infrastruktury poczty e-mail, upewnij się, że rekordy DNS i konfiguracje Hostingu Poczty E-mail są aktualizowane równolegle z migracją bazy danych, aby uniknąć przerw w działaniu usługi.

Weryfikacja integralności kopii zapasowej

Kopia zapasowa, która nigdy nie była testowana, nie jest kopią zapasową — jest niesprawdzonym założeniem. Wdróż procedurę weryfikacji:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Uruchamiaj ten skrypt weryfikacyjny co tydzień za pomocą cron i powiadamiaj o błędach.

Macierz decyzyjna: kiedy używać mysqldump

Lista kontrolna kluczowych wniosków technicznych

• Zawsze używaj `–single-transaction` dla baz danych tylko z InnoDB, aby uniknąć blokad zapisu podczas tworzenia kopii zapasowej.
• Zawsze uwzględniaj `–routines –triggers –events` w każdym zrzucie przeznaczonym jako kompletna kopia zapasowa.
• Przechowuj dane uwierzytelniające w `~/.my.cnf` lub `/etc/mysql/backup.cnf` z `chmod 600/640` — nigdy inline w skryptach lub poleceniach cron.
• Dodaj `–column-statistics=0` przy używaniu klienta MySQL 8.0 z serwerem MySQL 5.7 lub MariaDB.
• Zawsze określaj `–default-character-set=utf8mb4` zarówno podczas zrzutu, jak i przywracania, aby zapobiec uszkodzeniu kodowania znaków.
• Kompresuj wszystkie kopie zapasowe za pomocą gzip lub pigz; szyfruj wrażliwe zrzuty za pomocą AES-256 przed transferem poza siedzibę.
• Uwzględniaj `–flush-logs –source-data=2` w zrzutach produkcyjnych, aby umożliwić odzyskiwanie do punktu w czasie za pomocą binarnych dzienników.
• Automatyzuj czyszczenie retencji za pomocą `find … -mtime +N -delete`, aby zapobiec wyczerpaniu dysku.
• Testuj przywracanie według harmonogramu — weryfikuj liczby wierszy i wyrywkowo sprawdzaj integralność danych w porównaniu z produkcją.
• W przypadku schematów z mieszanymi silnikami używaj `–lock-all-tables` zamiast `–single-transaction`, aby zagwarantować spójność.

Często zadawane pytania

Czy mysqldump blokuje tabele podczas tworzenia kopii zapasowej?

Przy `–single-transaction` na czystej bazie danych InnoDB, żadne blokady tabel nie są uzyskiwane poza krótkim początkowym opróżnieniem. Tabele MyISAM zawsze wymagają blokady odczytu (`LOCK TABLES`), ponieważ nie obsługują transakcji. Schematy z mieszanymi silnikami wymagają `–lock-all-tables` dla spójnej migawki, co blokuje zapisy przez czas trwania zrzutu.

Jak wykonać kopię zapasową tylko schematu bazy danych bez żadnych danych?

Użyj flagi `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Eksportuje to wszystkie instrukcje `CREATE TABLE`, `CREATE VIEW`, procedury składowane i wyzwalacze bez żadnych instrukcji `INSERT`.

Dlaczego mysqldump kończy się niepowodzeniem z błędami „column statistics”?

Dzieje się tak, gdy klient MySQL 8.0 łączy się z serwerem MySQL 5.7 lub MariaDB. Dodaj `–column-statistics=0` do swojego polecenia. Alternatywnie zaktualizuj serwer do MySQL 8.0 lub użyj pliku binarnego klienta pasującego do wersji serwera.

Czy mysqldump może wykonywać przyrostowe kopie zapasowe?

Nie. mysqldump zawsze tworzy pełny logiczny zrzut określonego zakresu. Możliwość przyrostowego tworzenia kopii zapasowych wymaga archiwizacji binarnych dzienników (`mysqlbinlog`) w połączeniu z bazowym mysqldump wykonanym z `–flush-logs –source-data=2`. Prawdziwe przyrostowe fizyczne kopie zapasowe wymagają Percona XtraBackup lub MySQL Enterprise Backup.

Jaki jest najbezpieczniejszy sposób automatyzacji mysqldump bez ujawniania haseł?

Utwórz dedykowanego użytkownika kopii zapasowych MySQL z minimalnymi wymaganymi uprawnieniami, przechowuj jego dane uwierzytelniające w sekcji `[mysqldump]` pliku `~/.my.cnf` lub oddzielnym pliku opcji z `chmod 600` i odwołuj się do niego za pomocą `–defaults-file=/path/to/backup.cnf`. Takie podejście całkowicie eliminuje dane uwierzytelniające z list procesów, historii powłoki i definicji zadań cron.