Основні команди mysqldump
mysqldump — стандартна утиліта для створення логічних резервних копій MySQL і MariaDB. Вона створює SQL-файл з командами для відтворення структури та даних бази.
Бекап однієї бази даних
# Базовий бекап
mysqldump -u root -p mydb > mydb_backup.sql
# Зі стисненням (рекомендовано)
mysqldump -u root -p mydb | gzip > mydb_$(date +%Y%m%d_%H%M%S).sql.gz
# Без блокування таблиць (для InnoDB)
mysqldump -u root -p --single-transaction --quick mydb | gzip > mydb.sql.gzЗавжди використовуйте
--single-transaction для баз даних InnoDB — це створює консистентний знімок без блокування таблиць. Без цього прапора сайт під час бекапу може "зависнути".Корисні прапори mysqldump
| Прапор | Опис |
|---|---|
--single-transaction | Консистентний бекап InnoDB без блокувань |
--quick | Читати рядки по одному (заощаджує RAM) |
--no-data | Лише структура, без даних |
--no-create-info | Лише дані, без CREATE TABLE |
--all-databases | Бекап усіх баз даних |
--routines | Увімкнути збережені процедури |
--triggers | Увімкнути тригери (типово ON) |
Бекап усіх баз даних
mysqldump -u root -p --all-databases --single-transaction \
--routines --triggers | gzip > all_databases_$(date +%Y%m%d).sql.gzВідновлення з дампу
# Відновлення звичайного дампу
mysql -u root -p mydb < mydb_backup.sql
# Відновлення стисненого дампу
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb
# Або через zcat
zcat mydb_backup.sql.gz | mysql -u root -p mydbАвтоматизація через cron
sudo nano /etc/cron.d/mysql-backup# Бекап щодня о 2:00, зберігати 7 днів
0 2 * * * root mysqldump -u backup_user -pPassword --single-transaction \
--all-databases | gzip > /backups/mysql/all_$(date +\%Y\%m\%d).sql.gz && \
find /backups/mysql/ -name "*.sql.gz" -mtime +7 -deleteСтворення користувача лише для бекапів
mysql -u root -p -e "
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongBackupPass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;"Не використовуйте root для автоматичних бекапів — створіть окремого користувача з мінімальними привілеями. Пароль зберігайте у
~/.my.cnf, а не в скриптах.Пароль у ~/.my.cnf (без введення пароля)
cat ~/.my.cnf
[mysqldump]
user=backup_user
password=StrongBackupPass
chmod 600 ~/.my.cnf
# Тепер можна без -p:
mysqldump mydb > backup.sqlТипові помилки mysqldump
| Помилка | Причина | Що робити |
|---|---|---|
| Got error: 1044: Access denied for user to database | У користувача немає привілею SELECT на конкретну базу, хоча GRANT видано не на всі схеми | Видайте права явно: GRANT SELECT, LOCK TABLES ON mydb.* TO 'backup_user'@'localhost'; |
| Error 2013: Lost connection to MySQL server during query | Дамп великої таблиці перевищив wait_timeout, або не використано прапор --quick | Додайте --quick і збільшіть wait_timeout у my.cnf для сесії бекапу |
| Warning: A partial dump from a server that has GTIDs... | На сервері увімкнено GTID, і дамп без явного зазначення режиму може зламати реплікацію під час відновлення на іншому сервері | Додайте прапор --set-gtid-purged=OFF, якщо дамп переноситься не на репліку того самого кластера |
| ERROR 1146 (42S02): Table doesn't exist під час відновлення | Дамп було знято з прапором --no-data або процес дампу перервався посередині | Перевірте повноту дампу і перестворіть його без --no-data, якщо потрібні і структура, і дані |
Порівняння способів бекапу MySQL
| Спосіб | Тип бекапу | Блокування таблиць | Підходить для |
|---|---|---|---|
| mysqldump | Логічний (SQL-файл) | Немає за --single-transaction для InnoDB | Малих і середніх баз, перенесення між версіями |
| Percona XtraBackup | Фізичний (файли даних) | Немає, повністю гарячий бекап | Великих баз, де важлива швидкість відновлення |
| Binlog + періодичний знімок | Інкрементальний, point-in-time | Немає | Відновлення стану на точну секунду перед збоєм |
Коли mysqldump не підходить
- База важить сотні гігабайт — логічний дамп і його відновлення можуть тривати години, фізичний бекап (XtraBackup) у рази швидший
- Потрібне відновлення на довільну секунду перед інцидентом — для цього потрібні бінарні логи, а не дамп раз на добу
- Таблиці у рушії MyISAM з постійним активним записом — навіть із прапорами блокування в момент зняття дампу помітне користувачам
- Дамп знімається з master-сервера під високим навантаженням реплікації — краще знімати з репліки, щоб не створювати зайве навантаження
Для баз, де логічний дамп уже не встигає за часом, є стаття «Гарячий бекап MySQL з Percona XtraBackup», а для відновлення на точний час — «Binlog MySQL: відновлення бази на точку у часі».
Перевірка результату
Не довіряйте бекапу, який ніколи не перевірявся на відновлення:
# Дамп не повинен бути порожнім або обриватися посередині
tail -5 mydb_backup.sql
# Останній рядок зазвичай "-- Dump completed on ..."
# Кількість таблиць у дампі має збігатися з базою-джерелом
grep -c "^CREATE TABLE" mydb_backup.sql
mysql -u root -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"
# Пробне відновлення у тимчасову базу
mysql -u root -p -e "CREATE DATABASE mydb_restore_test;"
mysql -u root -p mydb_restore_test < mydb_backup.sql
mysql -u root -p -e "DROP DATABASE mydb_restore_test;"Якщо бекапиться сайт на WordPress, варто звіритися з окремим чеклистом у статті «WordPress резервне копіювання: UpdraftPlus і ручний метод» — там враховано ще й файли, а не лише базу.