Імпорт та експорт баз даних MySQL за допомогою командного рядка

Освоєння операцій імпорту та експорту баз даних MySQL з командного рядка є обов’язковою навичкою для будь-якого адміністратора баз даних або backend-інженера. Утиліта `mysqldump` експортує базу даних у портативний файл `.sql`, що містить усі DDL та DML оператори, необхідні для повного відновлення схеми та даних, тоді як клієнтська команда `mysql` виконує зворотну операцію — передає цей файл назад до запущеного екземпляра MySQL.

Цей посібник охоплює кожен практичний сценарій: експорт одиночної бази даних, дампи кількох баз даних, резервні копії лише структури, стиснені передачі, обробку наборів символів та безпечні робочі процеси імпорту — включаючи граничні випадки, що призводять до непомітного пошкодження даних або невдалого відновлення у виробничих середовищах.

Передумови

Перед виконанням будь-якої команди з цього посібника переконайтеся в наступному:

• MySQL Server (5.7, 8.0 або 8.4) встановлено і процес `mysqld` запущено
• Бінарні файли `mysqldump` та `mysql` знаходяться у системному `PATH` (перевірте за допомогою `which mysqldump`)
• Ви маєте обліковий запис MySQL щонайменше з привілеями `SELECT`, `LOCK TABLES`, `SHOW VIEW` та `TRIGGER` для експорту; `CREATE`, `INSERT`, `ALTER` та `DROP` для імпорту
• На цільовому носії достатньо місця на диску — стиснений дамп може розширитися у 5–10 разів під час імпорту
• Ви маєте доступ до оболонки сервера (локальний термінал, SSH або кероване середовище VPS Хостингу)

Експорт баз даних за допомогою mysqldump

`mysqldump` — це канонічний інструмент логічного резервного копіювання, що постачається разом із MySQL. Він серіалізує об’єкти бази даних у зрозумілий людині SQL-скрипт. На відміну від інструментів фізичного резервного копіювання, таких як Percona XtraBackup, `mysqldump` не залежить від рушія зберігання та працює з різними версіями MySQL і навіть форками MariaDB.

1. Експорт одиночної бази даних

“`bash

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

“`

Розбір параметрів:

• `-u [username]` — обліковий запис MySQL, що використовується для підключення
• `-p` — ініціює інтерактивний запит пароля (ніколи не передавайте пароль безпосередньо як `-p[password]` на спільних системах; він видимий у виводі `ps aux`)
• `[database_name]` — цільова схема для експорту
• `> [filename].sql` — перенаправляє stdout до вихідного файлу

Приклад:

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

Це створює файл, що містить оператори `CREATE TABLE`, `INSERT` та `ALTER TABLE`, достатні для відтворення `mydatabase` з нуля.

Критичний граничний випадок: За замовчуванням `mysqldump` встановлює глобальне блокування читання (`FLUSH TABLES WITH READ LOCK`) на початку дампу. На серверах InnoDB з високим трафіком використовуйте натомість `–single-transaction` для отримання узгодженого знімку без блокування записів:

“`bash

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

“`

`–single-transaction` надійно працює лише з таблицями InnoDB. Якщо ваша база даних містить таблиці MyISAM, блокування неминуче.

2. Експорт кількох баз даних

“`bash

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

“`

Прапорець `–databases` вказує `mysqldump` включити оператори `CREATE DATABASE` та `USE` у вивід, роблячи дамп самодостатнім. Без цього прапорця ці оператори опускаються, і дамп передбачає, що цільова база даних вже вибрана під час імпорту.

Приклад:

“`bash

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

“`

3. Експорт усіх баз даних

“`bash

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

“`

Це експортує кожну схему, до якої має доступ підключений користувач, включаючи системні бази даних `mysql`, `information_schema` та `performance_schema`. Уникайте імпорту системних баз даних через межі основних версій MySQL — схема таблиці привілеїв суттєво змінилася між MySQL 5.7 та 8.0, і імпорт старої схеми `mysql` до нового екземпляра 8.0 пошкодить автентифікацію.

Щоб виключити системні схеми:

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Експорт лише структури таблиць (без даних)

“`bash

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

“`

Це безцінно для контролю версій схеми, перевірки коду або розгортання порожнього тестового середовища. Вивід містить лише DDL — `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE` та подібні оператори — без рядків `INSERT`.

5. Експорт конкретних таблиць

“`bash

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

“`

Зверніть увагу, що при експорті конкретних таблиць таким чином `mysqldump` не включає оператори `CREATE DATABASE` або `USE`. Ви повинні переконатися, що цільова база даних існує перед імпортом.

6. Експорт зі збереженими процедурами, тригерами та подіями

За замовчуванням `mysqldump` включає тригери, але опускає збережені процедури, функції та заплановані події. Для повного резервного копіювання застосунку:

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Забуття `–routines` є однією з найпоширеніших причин невдалого відновлення застосунків — схема та дані присутні, але бізнес-логіка відсутня.

7. Стиснений експорт

Передайте вивід безпосередньо через `gzip` для зменшення розміру файлу на 60–80%:

“`bash

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

“`

Для максимального стиснення великих баз даних (за рахунок часу CPU):

“`bash

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

“`

Імпорт баз даних за допомогою клієнта mysql

Клієнт командного рядка `mysql` приймає SQL-файл через перенаправлення stdin і послідовно виконує кожен оператор на цільовому сервері.

1. Імпорт до існуючої бази даних

“`bash

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

“`

Приклад:

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Важливо: Якщо файл `.sql` було експортовано з `–databases` або `–all-databases`, він вже містить директиви `CREATE DATABASE` та `USE`. У такому випадку не вказуйте ім’я бази даних у командному рядку — це створює конфлікт:

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Імпорт до нової бази даних

Цільова база даних повинна існувати до того, як ви зможете імпортувати до неї. MySQL не створить її автоматично з простого дампу таблиць.

Крок 1 — Створіть базу даних:

“`bash

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

“`

Крок 2 — Імпортуйте дамп:

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Завжди явно вказуйте набір символів та зіставлення під час створення бази даних. Покладатися на стандартні налаштування сервера — часте джерело невідповідностей кодування, особливо при міграції між серверами з різними конфігураціями `character_set_server`.

3. Імпорт стисненого дампу

“`bash

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

“`

Або за допомогою `zcat` (еквівалент у більшості дистрибутивів Linux):

“`bash

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

“`

4. Імпорт з моніторингом прогресу

Для великих дампів стандартний клієнт `mysql` не надає жодного зворотного зв’язку. Використовуйте `pv` (pipe viewer) для відображення індикатора прогресу в реальному часі:

“`bash

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

“`

Встановіть `pv` за допомогою `apt install pv` або `yum install pv`. На Виділеному Сервері, що обробляє виробничі бази даних розміром у кілька гігабайт, ця видимість є критично важливою з операційної точки зору.

mysqldump порівняно з альтернативними методами резервного копіювання

Для середовищ, що запускають кілька виробничих баз даних на керованому VPS з cPanel, `mysqldump` залишається найбільш універсально підтримуваним варіантом завдяки своїй сумісності та простоті.

Розширена конфігурація та граничні випадки

Правильна обробка наборів символів

Невідповідності наборів символів відповідають за непропорційно велику частку пошкоджених імпортів. Найбезпечніший підхід — бути явним на кожному етапі:

“`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

“`

Примітка: `utf8` у MySQL є 3-байтовою підмножиною, яка не може зберігати 4-байтові символи Unicode (емодзі, певні ідеографи CJK). Завжди використовуйте `utf8mb4` для нових баз даних.

Прискорення великих імпортів

За замовчуванням MySQL виконує повний коміт після кожного оператора `INSERT` у дампі. Для великих наборів даних це катастрофічно повільно. Додайте наступне на початку вашого сеансу імпорту:

“`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

“`

Альтернативно, експортуйте з `–extended-insert` (увімкнено за замовчуванням) та `–disable-keys` для групування вставок і відкладення перебудови індексів до завершення завантаження даних.

Автоматизація резервного копіювання за допомогою Cron

Запис автоматизованого резервного копіювання виробничого рівня у `/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

“`

Використовуйте виділеного користувача MySQL з мінімально необхідними привілеями замість `root`. Створіть його за допомогою:

“`sql

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

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

FLUSH PRIVILEGES;

“`

Захист облікових даних за допомогою .my.cnf

Передача паролів у командному рядку розкриває їх в історії оболонки та списках процесів. Зберігайте облікові дані у `~/.my.cnf`:

“`ini

[client]

user=root

password=YourSecurePassword

“`

Негайно встановіть дозволи:

“`bash

chmod 600 ~/.my.cnf

“`

Після цього всі команди `mysqldump` та `mysql` автоматично отримують облікові дані без прапорців `-u` та `-p`.

Експорт віддаленої бази даних

Щоб зробити дамп бази даних з віддаленого сервера MySQL:

“`bash

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

“`

Переконайтеся, що віддалений екземпляр MySQL дозволяє підключення з вашої IP-адреси і що порт 3306 відкритий у брандмауері. Для зашифрованих передач використовуйте тунель через 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

“`

Практична матриця прийняття рішень

Ключові технічні висновки

• Завжди використовуйте `–single-transaction` для баз даних InnoDB, щоб уникнути блокування записів застосунку під час експорту
• Завжди явно вказуйте `utf8mb4` — ніколи не покладайтеся на припущення щодо стандартного набору символів сервера
• Включайте `–routines`, `–triggers` та `–events` у кожну повну резервну копію застосунку, інакше ви ризикуєте втратити бізнес-логіку
• Ніколи не імпортуйте таблиці системної схеми (`mysql.*`) через межі основних версій MySQL
• Зберігайте облікові дані у `~/.my.cnf` з `chmod 600` — ніколи не передавайте паролі як вбудовані аргументи
• Для баз даних розміром понад 10 GB оцініть `mysqlpump` або утиліти паралельного дампу MySQL Shell, оскільки `mysqldump` стане вузьким місцем
• Перевіряйте кожну резервну копію, виконуючи тестове відновлення в ізольованому середовищі, перш ніж покладатися на неї для аварійного відновлення
• При розміщенні кількох клієнтських баз даних ізолюйте середовища за допомогою окремих Панелей керування VPS, щоб запобігти міжклієнтському доступу під час операцій відновлення
• Поєднуйте стратегію резервного копіювання бази даних з дійсним SSL Сертифікатом на будь-якому рівні веб-застосунку, що підключається до MySQL, щоб запобігти перехопленню облікових даних під час передачі

Часті запитання

У чому різниця між mysqldump та mysqlpump?

`mysqldump` є однопотоковим і створює єдиний SQL-файл — надійний та універсально сумісний. `mysqlpump`, представлений у MySQL 5.7.8, підтримує паралельний експорт кількох баз даних і таблиць одночасно, значно скорочуючи час дампу на багатоядерних серверах. Однак `mysqlpump` має відомі проблеми з узгодженим резервним копіюванням баз даних зі змішаними рушіями і менш підходить для міграцій між версіями.

Чи можна імпортувати дамп MySQL 5.7 до MySQL 8.0?

Так, з застереженнями. Дані користувачів та схеми застосунків імпортуються без проблем. Однак ніколи не імпортуйте системну базу даних `mysql` безпосередньо — плагін автентифікації змінився з `mysql_native_password` на `caching_sha2_password` у версії 8.0, і імпорт старих таблиць привілеїв порушить автентифікацію. Відтворюйте користувачів вручну за допомогою операторів `CREATE USER` та `GRANT`.

Чому мій імпорт завершується помилкою "ERROR 1005: Can’t create table" через обмеження зовнішніх ключів?

Це відбувається, коли таблиці імпортуються в порядку, що порушує залежності зовнішніх ключів. Виправлення полягає в тому, щоб додати `SET foreign_key_checks = 0;` на початку сеансу імпорту та `SET foreign_key_checks = 1;` після завершення. Альтернативно, експортуйте з `–single-transaction`, що зберігає посилальну цілісність у самому файлі дампу.

Як експортувати лише дані без операторів CREATE TABLE?

Використовуйте прапорець `–no-create-info`: `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. Це корисно, коли вам потрібно перезавантажити дані до існуючої схеми без зміни її структури.

Який найбезпечніший спосіб передачі дампу MySQL між двома віддаленими серверами?

Передайте дамп безпосередньо через SSH без запису проміжного файлу на диск: `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. Це і швидше, і безпечніше, ніж копіювання відкритого текстового файлу `.sql`, особливо при роботі в середовищі Спільного Веб-хостингу, де квоти на диск є обмеженням.