Importowanie i eksportowanie baz danych MySQL przy użyciu wiersza poleceń

Opanowanie operacji importu i eksportu baz danych MySQL z wiersza poleceń to umiejętność niezbędna dla każdego administratora baz danych lub inżyniera backendu. Narzędzie `mysqldump` eksportuje bazę danych do przenośnego pliku `.sql` zawierającego wszystkie instrukcje DDL i DML wymagane do pełnej rekonstrukcji schematu i danych, natomiast klient `mysql` obsługuje operację odwrotną — przesyłając ten plik z powrotem do działającej instancji MySQL.

Ten przewodnik obejmuje każdy praktyczny scenariusz: eksport pojedynczej bazy danych, zrzuty wielu baz danych, kopie zapasowe samej struktury, skompresowane transfery, obsługę zestawów znaków oraz bezpieczne przepływy pracy importu — w tym przypadki brzegowe powodujące cichą korupcję danych lub nieudane przywracanie w środowiskach produkcyjnych.

Wymagania wstępne

Przed wykonaniem jakiegokolwiek polecenia z tego przewodnika sprawdź następujące kwestie:

• MySQL Server (5.7, 8.0 lub 8.4) jest zainstalowany i proces `mysqld` działa
• Pliki binarne `mysqldump` i `mysql` znajdują się w systemowym `PATH` (sprawdź za pomocą `which mysqldump`)
• Posiadasz konto MySQL z co najmniej uprawnieniami `SELECT`, `LOCK TABLES`, `SHOW VIEW` i `TRIGGER` do eksportu; `CREATE`, `INSERT`, `ALTER` i `DROP` do importu
• Na miejscu docelowym jest wystarczająca ilość miejsca na dysku — skompresowany zrzut może rozrosnąć się 5–10 razy podczas importu
• Masz dostęp do powłoki serwera (lokalny terminal, SSH lub zarządzane środowisko VPS Hosting)

Eksportowanie baz danych za pomocą mysqldump

`mysqldump` to kanoniczne narzędzie do logicznych kopii zapasowych dołączone do MySQL. Serializuje obiekty bazy danych do czytelnego dla człowieka skryptu SQL. W przeciwieństwie do fizycznych narzędzi do tworzenia kopii zapasowych, takich jak Percona XtraBackup, `mysqldump` jest niezależny od silnika pamięci masowej i działa w różnych wersjach MySQL, a nawet w forach MariaDB.

1. Eksport pojedynczej bazy danych

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

Opis parametrów:

• `-u [username]` — konto MySQL używane do połączenia
• `-p` — wywołuje interaktywny monit o hasło (nigdy nie podawaj hasła bezpośrednio jako `-p[password]` w systemach współdzielonych; jest ono widoczne w wynikach `ps aux`)
• `[database_name]` — docelowy schemat do eksportu
• `> [filename].sql` — przekierowuje stdout do pliku wyjściowego

Przykład:

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

Tworzy to plik zawierający instrukcje `CREATE TABLE`, `INSERT` i `ALTER TABLE` wystarczające do odtworzenia `mydatabase` od podstaw.

Krytyczny przypadek brzegowy: Domyślnie `mysqldump` ustawia globalną blokadę odczytu (`FLUSH TABLES WITH READ LOCK`) na początku zrzutu. Na serwerach InnoDB o dużym ruchu użyj zamiast tego `–single-transaction`, aby wykonać spójną migawkę bez blokowania zapisów:

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction` działa niezawodnie tylko z tabelami InnoDB. Jeśli baza danych zawiera tabele MyISAM, blokada jest nieunikniona.

2. Eksport wielu baz danych

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

Flaga `–databases` instruuje `mysqldump`, aby dołączył do wyjścia instrukcje `CREATE DATABASE` i `USE`, czyniąc zrzut samodzielnym. Bez tej flagi te instrukcje są pomijane, a zrzut zakłada, że docelowa baza danych jest już wybrana w czasie importu.

Przykład:

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. Eksport wszystkich baz danych

“`bash

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

“`

Eksportuje to każdy schemat dostępny dla łączącego się użytkownika, w tym systemowe bazy danych `mysql`, `information_schema` i `performance_schema`. Unikaj importowania systemowych baz danych między głównymi wersjami MySQL — schemat tabeli uprawnień zmienił się znacząco między MySQL 5.7 a 8.0, a zaimportowanie starego schematu `mysql` do nowej instancji 8.0 spowoduje uszkodzenie uwierzytelniania.

Aby wykluczyć schematy systemowe:

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Eksport tylko struktury tabel (bez danych)

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

Jest to nieocenione przy kontroli wersji schematu, przeglądach kodu lub uruchamianiu pustego środowiska testowego. Wyjście zawiera tylko DDL, takie jak `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE` i podobne — bez wierszy `INSERT`.

5. Eksport określonych tabel

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

Należy pamiętać, że podczas eksportowania określonych tabel w ten sposób `mysqldump` nie dołącza instrukcji `CREATE DATABASE` ani `USE`. Musisz upewnić się, że docelowa baza danych istnieje przed importem.

6. Eksport z procedurami składowanymi, wyzwalaczami i zdarzeniami

Domyślnie `mysqldump` dołącza wyzwalacze, ale pomija procedury składowane, funkcje i zaplanowane zdarzenia. Aby wykonać pełną kopię zapasową aplikacji:

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Pominięcie `–routines` jest jedną z najczęstszych przyczyn uszkodzonych przywróceń aplikacji — schemat i dane są obecne, ale brakuje logiki biznesowej.

7. Skompresowany eksport

Przekieruj wyjście bezpośrednio przez `gzip`, aby zmniejszyć rozmiar pliku o 60–80%:

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

Dla maksymalnej kompresji dużych baz danych (kosztem czasu CPU):

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

Importowanie baz danych za pomocą klienta mysql

Klient wiersza poleceń `mysql` przyjmuje plik SQL poprzez przekierowanie stdin i wykonuje każdą instrukcję sekwencyjnie na docelowym serwerze.

1. Import do istniejącej bazy danych

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

Przykład:

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Ważne: Jeśli plik `.sql` został wyeksportowany z `–databases` lub `–all-databases`, zawiera już dyrektywy `CREATE DATABASE` i `USE`. W takim przypadku nie podawaj nazwy bazy danych w wierszu poleceń — spowoduje to konflikt:

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Import do nowej bazy danych

Docelowa baza danych musi istnieć przed importem. MySQL nie utworzy jej automatycznie z prostego zrzutu tabel.

Krok 1 — Utwórz bazę danych:

“`bash

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

“`

Krok 2 — Zaimportuj zrzut:

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Zawsze jawnie określaj zestaw znaków i sortowanie podczas tworzenia bazy danych. Poleganie na domyślnych ustawieniach serwera jest częstym źródłem niezgodności kodowania, szczególnie podczas migracji między serwerami z różnymi konfiguracjami `character_set_server`.

3. Import skompresowanego zrzutu

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

Lub używając `zcat` (odpowiednik w większości dystrybucji Linux):

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. Import z monitorowaniem postępu

W przypadku dużych zrzutów standardowy klient `mysql` nie daje żadnych informacji zwrotnych. Użyj `pv` (pipe viewer), aby wyświetlić pasek postępu w czasie rzeczywistym:

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

Zainstaluj `pv` za pomocą `apt install pv` lub `yum install pv`. Na Serwerze Dedykowanym obsługującym wielogigabajtowe produkcyjne bazy danych ta widoczność jest operacyjnie krytyczna.

mysqldump a alternatywne metody tworzenia kopii zapasowych

W środowiskach obsługujących wiele produkcyjnych baz danych na zarządzanym VPS z cPanel, `mysqldump` pozostaje najszerzej obsługiwaną opcją ze względu na swoją kompatybilność i prostotę.

Zaawansowana konfiguracja i przypadki brzegowe

Prawidłowa obsługa zestawów znaków

Niezgodności zestawów znaków są odpowiedzialne za nieproporcjonalnie dużą część uszkodzonych importów. Najbezpieczniejszym podejściem jest bycie precyzyjnym na każdym etapie:

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

Uwaga: `utf8` w MySQL to 3-bajtowy podzbiór, który nie może przechowywać 4-bajtowych znaków Unicode (emoji, niektóre ideogramy CJK). Zawsze używaj `utf8mb4` dla nowych baz danych.

Przyspieszanie dużych importów

Domyślnie MySQL wykonuje pełne zatwierdzenie po każdej instrukcji `INSERT` w zrzucie. W przypadku dużych zbiorów danych jest to katastrofalnie wolne. Dodaj następujące polecenia na początku sesji importu:

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

Alternatywnie eksportuj z `–extended-insert` (domyślnie włączone) i `–disable-keys`, aby grupować wstawienia i odkładać przebudowę indeksów do czasu po załadowaniu danych.

Automatyzacja kopii zapasowych za pomocą Cron

Wpis automatycznej kopii zapasowej klasy produkcyjnej w `/etc/cron.d/mysql-backup`:

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

Używaj dedykowanego użytkownika MySQL z minimalnymi wymaganymi uprawnieniami zamiast `root`. Utwórz go za pomocą:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Zabezpieczanie poświadczeń za pomocą .my.cnf

Podawanie haseł w wierszu poleceń ujawnia je w historii powłoki i listach procesów. Przechowuj poświadczenia w `~/.my.cnf`:

“`ini

[client]

user=root

password=YourSecurePassword

“`

Natychmiast ustaw uprawnienia:

“`bash

chmod 600 ~/.my.cnf

“`

Po tej konfiguracji wszystkie polecenia `mysqldump` i `mysql` automatycznie pobierają poświadczenia bez flag `-u` i `-p`.

Eksport zdalnej bazy danych

Aby zrzucić bazę danych ze zdalnego serwera MySQL:

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Upewnij się, że zdalna instancja MySQL zezwala na połączenia z Twojego adresu IP i że port 3306 jest otwarty w zaporze sieciowej. W przypadku szyfrowanych transferów tuneluj przez SSH:

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Praktyczna macierz decyzyjna

Kluczowe wnioski techniczne

• Zawsze używaj `–single-transaction` dla baz danych InnoDB, aby uniknąć blokowania zapisów aplikacji podczas eksportu
• Zawsze jawnie określaj `utf8mb4` — nigdy nie polegaj na domyślnych założeniach zestawu znaków serwera
• Dołączaj `–routines`, `–triggers` i `–events` do każdej pełnej kopii zapasowej aplikacji, aby nie utracić logiki biznesowej
• Nigdy nie importuj tabel schematów systemowych (`mysql.*`) między głównymi wersjami MySQL
• Przechowuj poświadczenia w `~/.my.cnf` z `chmod 600` — nigdy nie podawaj haseł jako argumentów inline
• W przypadku baz danych przekraczających 10 GB rozważ `mysqlpump` lub równoległe narzędzia zrzutu MySQL Shell, ponieważ `mysqldump` stanie się wąskim gardłem
• Weryfikuj każdą kopię zapasową, wykonując testowe przywracanie w izolowanym środowisku przed poleganiem na niej w przypadku odzyskiwania po awarii
• Podczas hostowania wielu baz danych klientów izoluj środowiska za pomocą oddzielnych Paneli Sterowania VPS, aby zapobiec dostępowi między dzierżawcami podczas operacji przywracania
• Połącz strategię tworzenia kopii zapasowych bazy danych z ważnym Certyfikatem SSL na każdej warstwie aplikacji dostępnej przez internet, która łączy się z MySQL, aby zapobiec przechwytywaniu poświadczeń podczas transmisji

FAQ

Jaka jest różnica między mysqldump a mysqlpump?

`mysqldump` jest jednowątkowy i tworzy pojedynczy plik SQL — niezawodny i powszechnie kompatybilny. `mysqlpump`, wprowadzony w MySQL 5.7.8, obsługuje równoległy eksport wielu baz danych i tabel jednocześnie, znacznie skracając czas zrzutu na serwerach wielordzeniowych. Jednak `mysqlpump` ma znane problemy ze spójnymi kopiami zapasowymi baz danych z mieszanymi silnikami i jest mniej odpowiedni do migracji między wersjami.

Czy mogę zaimportować zrzut MySQL 5.7 do MySQL 8.0?

Tak, z zastrzeżeniami. Dane użytkowników i schematy aplikacji importują się poprawnie. Jednak nigdy nie importuj bezpośrednio systemowej bazy danych `mysql` — wtyczka uwierzytelniania zmieniła się z `mysql_native_password` na `caching_sha2_password` w wersji 8.0, a zaimportowanie starych tabel uprawnień spowoduje uszkodzenie uwierzytelniania. Odtwórz użytkowników ręcznie za pomocą instrukcji `CREATE USER` i `GRANT`.

Dlaczego import kończy się błędem „ERROR 1005: Can’t create table” z powodu ograniczeń klucza obcego?

Dzieje się tak, gdy tabele są importowane w kolejności naruszającej zależności klucza obcego. Rozwiązaniem jest dodanie `SET foreign_key_checks = 0;` na początku sesji importu i `SET foreign_key_checks = 1;` po jej zakończeniu. Alternatywnie eksportuj z `–single-transaction`, co zachowuje integralność referencyjną w samym pliku zrzutu.

Jak wyeksportować tylko dane bez instrukcji CREATE TABLE?

Użyj flagi `–no-create-info`: `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. Jest to przydatne, gdy musisz ponownie załadować dane do istniejącego schematu bez zmiany jego struktury.

Jaki jest najbezpieczniejszy sposób transferu zrzutu MySQL między dwoma zdalnymi serwerami?

Przekieruj zrzut bezpośrednio przez SSH bez zapisywania pliku pośredniego na dysku: `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. Jest to zarówno szybsze, jak i bezpieczniejsze niż kopiowanie pliku `.sql` w postaci zwykłego tekstu, szczególnie w środowisku Współdzielonego Hostingu, gdzie limity miejsca na dysku stanowią ograniczenie.