Повний посібник з mysqldump: резервне копіювання, відновлення та автоматизація бази даних MySQL

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

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

Що mysqldump насправді робить під капотом

Коли ви викликаєте mysqldump, клієнт підключається до сервера MySQL, запитує інформаційну схему та словник даних і виводить потік `CREATE DATABASE`, `CREATE TABLE`, `INSERT` та DDL-операторів на стандартний вивід. Ви перенаправляєте цей потік у файл, конвеєр або утиліту стиснення.

Для таблиць InnoDB з `–single-transaction` mysqldump відкриває транзакцію з повторюваним читанням перед зчитуванням будь-яких даних. Це дає вам узгоджений знімок на певний момент часу без отримання глобальних блокувань читання — база даних залишається повністю доступною для запису під час дампу. Для таблиць MyISAM такого механізму не існує; mysqldump повертається до `FLUSH TABLES WITH READ LOCK`, що ненадовго блокує записи.

Розуміння цього розрізнення є критично важливим перед тим, як ви виберете mysqldump для виробничих навантажень. Якщо ваша схема поєднує таблиці InnoDB та MyISAM, `–single-transaction` самого по собі недостатньо — вам знадобиться `–lock-all-tables` або вікно технічного обслуговування.

Передумови та необхідні привілеї

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

• MySQL або MariaDB встановлено та доступно (локальний сокет або TCP/IP).
• Користувач резервного копіювання має мінімально необхідні привілеї:
• `SELECT` для всіх цільових таблиць
• `LOCK TABLES` (обов’язково, якщо тільки `–single-transaction` не використовується виключно з InnoDB)
• `SHOW VIEW` для включення представлень
• `TRIGGER` для включення тригерів
• `PROCESS` при використанні `–single-transaction` на MySQL 8+
• `RELOAD` для `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` якщо вам потрібні координати бінарного журналу для налаштування реплікації

Створіть спеціального користувача для резервного копіювання замість запуску дампів від імені 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;

“`

Запуск mysqldump від імені root з паролем, вбудованим у команду оболонки, розкриває облікові дані у списках процесів та історії оболонки — це значний ризик безпеки на будь-якій спільній або багатокористувацькій системі.

Базовий синтаксис

“`

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

“`

Повний довідник параметрів

Параметри підключення

Параметри області охоплення

Параметри узгодженості та блокування

Параметри виводу та вмісту

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

Вибір правильної стратегії резервного копіювання залежить від розміру бази даних, вимог RTO/RPO та інфраструктури. Ось як mysqldump порівнюється з найпоширенішими альтернативами:

Для баз даних розміром до 10–20 GB у середовищі VPS Хостингу, mysqldump залишається найбільш практичним та портативним рішенням. За цим порогом інструменти фізичного резервного копіювання пропонують значно швидші вікна резервного копіювання та відновлення.

Практичні приклади використання

Приклад 1: Резервне копіювання однієї бази даних

“`bash

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

“`

Підстановка `$(date +%F)` автоматично додає дату у форматі ISO (наприклад, `2025-07-15`) до імені файлу, запобігаючи перезапису.

Приклад 2: Резервне копіювання кількох конкретних баз даних

“`bash

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

“`

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

Приклад 3: Резервне копіювання всіх баз даних

“`bash

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

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

“`

Завжди включайте `–events`, `–routines` та `–triggers` у повні дампи сервера. Ці об’єкти мовчки пропускаються без явних прапорців.

Приклад 4: Узгоджене резервне копіювання InnoDB (безпечне для виробництва)

“`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` виконує ротацію бінарного журналу на початку дампу. `–source-data=2` записує поточне ім’я файлу бінарного журналу та позицію як SQL-коментар, що дозволяє відновлення на певний момент часу шляхом відтворення наступних binlog з цієї позиції.

Приклад 5: Стиснута резервна копія з gzip

“`bash

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

“`

Для серверів з обмеженими ресурсами CPU замініть на `pigz` (паралельний gzip) для використання кількох ядер:

“`bash

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

“`

Приклад 6: Резервна копія лише схеми (структура без даних)

“`bash

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

“`

Корисно для контролю версій схеми у Git або розгортання у тестовому середовищі без копіювання виробничих даних.

Приклад 7: Резервна копія лише даних (без схеми)

“`bash

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

“`

Використовуйте це, коли цільова схема вже існує і вам потрібно лише заповнити або оновити дані.

Приклад 8: Резервне копіювання однієї таблиці

“`bash

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

“`

Приклад 9: Експорт відфільтрованої підмножини рядків

“`bash

mysqldump -u backup_user -p database_name orders

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

> /backups/orders_2025_completed.sql

“`

Параметр `–where` використовується рідко, але є надзвичайно потужним для часткового експорту, архівування даних та налагодження конкретних наборів записів.

Приклад 10: Виключення конкретних таблиць

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Виключення великих, тимчасових таблиць (кеші, сховища сесій, таблиці журналів) може зменшити розмір дампу та тривалість на порядок величини.

Приклад 11: Включення збережених процедур, функцій та тригерів

“`bash

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

“`

Приклад 12: Резервне копіювання віддаленої бази даних

“`bash

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

“`

При резервному копіюванні віддаленого сервера трафік за замовчуванням передається мережею у незашифрованому вигляді. Додайте прапорці `–ssl-ca`, `–ssl-cert` та `–ssl-key` або тунелюйте через SSH:

“`bash

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

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

“`

Відновлення резервної копії mysqldump

Відновлення однієї бази даних

“`bash

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

“`

Якщо цільова база даних ще не існує, спочатку створіть її:

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

“`

Відновлення всіх баз даних з повного дампу сервера

“`bash

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

“`

Оскільки `–all-databases` вбудовує оператори `CREATE DATABASE` та `USE`, аргумент цільової бази даних не потрібен.

Відновлення зі стисненої резервної копії

“`bash

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

“`

Або з використанням підстановки процесу:

“`bash

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

“`

Відновлення однієї таблиці з повного дампу бази даних

Це поширений операційний сценарій, який оригінальний файл дампу робить нетривіальним. Використовуйте `sed` або `grep` для вилучення відповідного розділу:

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

“`

Альтернативно, використовуйте `mysql_extract_table.sh` або імпортуйте у тимчасову базу даних та скопіюйте таблицю:

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

“`

Відновлення на певний момент часу за допомогою бінарних журналів

Якщо ваш дамп було зроблено з `–source-data=2` і бінарне журналювання увімкнено, ви можете відновитися до будь-якого моменту після дампу:

1. Визначте позицію бінарного журналу з коментаря заголовка файлу дампу.
2. Відновіть базовий дамп.
3. Застосуйте наступні події бінарного журналу до потрібної позначки часу:

“`bash

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

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

“`

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

Базове щоденне завдання резервного копіювання

Зберігайте облікові дані у `~/.my.cnf` замість того, щоб вбудовувати їх у команди cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Встановіть суворі дозволи:

“`bash

chmod 600 ~/.my.cnf

“`

Потім створіть завдання 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

“`

Скрипт резервного копіювання виробничого рівня

Для Виділених серверів, що розміщують кілька баз даних, більш надійний скрипт обробляє журналювання помилок, перевірку дискового простору та віддалене вивантаження:

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

“`

Посилення безпеки для операцій mysqldump

Управління обліковими даними є найбільш часто занедбаним аспектом безпеки резервного копіювання. Ніколи не передавайте `-pYourPassword` безпосередньо в командному рядку — він видимий у виводі `ps aux` та історії оболонки. Натомість використовуйте один із цих підходів:

• `~/.my.cnf` з `chmod 600` (для кожного користувача)
• `/etc/mysql/backup.cnf` з `chmod 640`, що належить root та доступний для читання групою резервного копіювання
• Змінна середовища `MYSQL_PWD` (видима у `/proc`, використовуйте лише в ізольованих контейнерах)
• MySQL Vault або HashiCorp Vault для корпоративних середовищ

Дозволи файлів резервних копій мають бути обмежувальними:

“`bash

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

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

“`

Шифрування у стані спокою: Для конфіденційних даних шифруйте файли резервних копій перед їх зберіганням або передачею:

“`bash

mysqldump –single-transaction database_name

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

“`

Шифрування транспорту: При дампі з віддаленого сервера завжди використовуйте SSL/TLS або SSH-тунель. У середовищі VPS з cPanel інтерфейс резервного копіювання cPanel обробляє це автоматично, але ручні операції mysqldump вимагають явних прапорців SSL.

Поширені підводні камені та способи їх уникнення

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

“`bash

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

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

“`

Відсутність `–column-statistics=0` спричиняє збої, коли клієнт MySQL 8.0 робить дамп з сервера MySQL 5.7 або MariaDB. Клієнт MySQL 8 намагається зробити дамп статистики стовпців, яку старіші сервери не підтримують:

“`bash

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

“`

Забуття `–routines`, `–triggers` та `–events` мовчки пропускає критичні об’єкти бази даних. Ці прапорці не увімкнені за замовчуванням (крім `–triggers`) і часто забуваються у нерегулярних дампах.

Дампи великих таблиць, що спричиняють OOM: mysqldump за замовчуванням буферизує цілі результуючі набори в пам’яті. Для дуже великих таблиць додайте `–quick` (увімкнено за замовчуванням у більшості версій, але варто перевірити), щоб передавати рядки по одному замість буферизації:

“`bash

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

“`

Відновлення на іншу версію MySQL: Дампи з MySQL 8.0 можуть містити синтаксис, що не підтримується в MySQL 5.7 (наприклад, функціональні індекси, невидимі стовпці). Завжди тестуйте відновлення у середовищі з відповідною версією перед тим, як покладатися на міграції між версіями.

Дрейф значень auto-increment: Якщо ви відновлюєте таблицю у існуючу схему, яка вже має рядки, оператори `INSERT` зазнають збою через конфлікти первинного ключа, якщо ви не включите `–add-drop-table` або не очистите цільову таблицю вручну спочатку.

Використання mysqldump для міграції баз даних

mysqldump є стандартним підходом для міграції баз даних між серверами — наприклад, при переміщенні сайту WordPress з Спільного веб-хостингу на VPS або переході на середовище VPS з панелями керування з більшими ресурсами.

Рекомендований робочий процес міграції:

1. Зробіть дамп вихідної бази даних з повними параметрами:

“`bash

mysqldump –single-transaction –routines –triggers –events

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

“`

1. Передайте безпечно за допомогою rsync через SSH:

“`bash

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

“`

1. Створіть цільову базу даних з відповідним набором символів:

“`bash

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

“`

1. Відновіть та перевірте:

“`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. Оновіть конфігурацію застосунку, щоб вказати на новий хост бази даних.

Для застосунків, що також покладаються на поштову інфраструктуру, переконайтеся, що DNS-записи та конфігурації Поштового хостингу оновлюються паралельно з міграцією бази даних, щоб уникнути перебоїв у роботі сервісу.

Перевірка цілісності резервної копії

Резервна копія, яка ніколи не тестувалася, — це не резервна копія, а нетестоване припущення. Впровадьте процедуру перевірки:

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

“`

Запускайте цей скрипт перевірки щотижня через cron та сповіщайте про збої.

Матриця рішень: коли використовувати mysqldump

Технічний контрольний список ключових висновків

• Завжди використовуйте `–single-transaction` для баз даних лише з InnoDB, щоб уникнути блокувань запису під час резервного копіювання.
• Завжди включайте `–routines –triggers –events` у будь-який дамп, призначений як повна резервна копія.
• Зберігайте облікові дані у `~/.my.cnf` або `/etc/mysql/backup.cnf` з `chmod 600/640` — ніколи не вбудовуйте їх у скрипти або команди cron.
• Додавайте `–column-statistics=0` при використанні клієнта MySQL 8.0 проти сервера MySQL 5.7 або MariaDB.
• Завжди вказуйте `–default-character-set=utf8mb4` як під час дампу, так і під час відновлення, щоб запобігти пошкодженню кодування символів.
• Стискайте всі резервні копії за допомогою gzip або pigz; шифруйте конфіденційні дампи за допомогою AES-256 перед позамайданчиковою передачею.
• Включайте `–flush-logs –source-data=2` у виробничі дампи для забезпечення відновлення на певний момент часу через бінарні журнали.
• Автоматизуйте очищення збережених копій за допомогою `find … -mtime +N -delete`, щоб запобігти вичерпанню дискового простору.
• Тестуйте відновлення за розкладом — перевіряйте кількість рядків та вибірково перевіряйте цілісність даних порівняно з виробництвом.
• Для схем зі змішаними рушіями використовуйте `–lock-all-tables` замість `–single-transaction` для гарантії узгодженості.

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

Чи блокує mysqldump таблиці під час резервного копіювання?

З `–single-transaction` на чистій базі даних InnoDB жодних блокувань таблиць не набувається, окрім короткого початкового скидання. Таблиці MyISAM завжди вимагають блокування читання (`LOCK TABLES`), оскільки вони не підтримують транзакції. Схеми зі змішаними рушіями вимагають `–lock-all-tables` для узгодженого знімку, що блокує записи на час дампу.

Як зробити резервну копію лише схеми бази даних без будь-яких даних?

Використовуйте прапорець `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Це експортує всі оператори `CREATE TABLE`, `CREATE VIEW`, збережені процедури та тригери без будь-яких операторів `INSERT`.

Чому мій mysqldump завершується з помилками «column statistics»?

Це відбувається, коли клієнт MySQL 8.0 підключається до сервера MySQL 5.7 або MariaDB. Додайте `–column-statistics=0` до своєї команди. Альтернативно, оновіть сервер до MySQL 8.0 або використовуйте бінарний файл клієнта, що відповідає версії сервера.

Чи може mysqldump виконувати інкрементальне резервне копіювання?

Ні. mysqldump завжди створює повний логічний дамп зазначеної області. Можливість інкрементального резервного копіювання вимагає архівування бінарних журналів (`mysqlbinlog`) у поєднанні з базовим mysqldump, зробленим з `–flush-logs –source-data=2`. Справжнє інкрементальне фізичне резервне копіювання вимагає Percona XtraBackup або MySQL Enterprise Backup.

Який найбезпечніший спосіб автоматизувати mysqldump без розкриття паролів?

Створіть спеціального користувача MySQL для резервного копіювання з мінімально необхідними привілеями, зберігайте його облікові дані у розділі `[mysqldump]` файлу `~/.my.cnf` або окремому файлі параметрів з `chmod 600` та посилайтеся на нього за допомогою `–defaults-file=/path/to/backup.cnf`. Цей підхід повністю виключає облікові дані зі списків процесів, історії оболонки та визначень завдань cron.