Втрата бази даних — катастрофа для бізнесу. Автоматичні щоденні бекапи — це страховка, яку налаштовують один раз і забувають.
Простий скрипт резервного копіювання
#!/bin/bash
# /usr/local/bin/mysql-backup.sh
DB_USER="root"
DB_PASS="ВашПароль"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M)
KEEP_DAYS=7
mkdir -p "$BACKUP_DIR"
# Отримати список усіх баз даних
DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")
for DB in $DATABASES; do
echo "Backup: $DB"
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
done
# Видалити старі бекапи
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$KEEP_DAYS" -delete
echo "Backup complete: $(ls -lh $BACKUP_DIR | tail -5)"chmod +x /usr/local/bin/mysql-backup.sh
# Додати в cron (щодня о 2:00)
echo "0 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1" >> /etc/crontabВідновлення з бекапу
# Розпакувати та відновити
gunzip < /var/backups/mysql/mydb_2026-01-25_02-00.sql.gz | mysql -u root -p mydbЗберігання бекапів у хмарі (S3/Backblaze)
# Встановити AWS CLI
sudo apt install -y awscli
# Налаштувати
aws configure
# Завантажити в S3
aws s3 sync /var/backups/mysql/ s3://your-bucket/mysql-backups/ --storage-class STANDARD_IAРозбір типових помилок резервного копіювання
mysqldump завершується з "Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)'" — виникає під час використання --single-transaction з користувачем без права PROCESS, потрібного для читання списку процесів під час зняття консистентного знімка.
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operationВидайте потрібні права: GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'backup_user'@'localhost'; — окремий користувач лише для резервних копій безпечніший, ніж root у скрипті.
Файл резервної копії важить 0 байт або обривається посередині — типова ознака того, що закінчилося місце на диску в момент виконання дампа, а скрипт не перевіряє код повернення mysqldump.
df -h /var/backups
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 20G 20G 0 100% /
mysqldump: Got errno 28 on writeerrno 28 — це ENOSPC, "No space left on device". Додайте в скрипт перевірку вільного місця перед стартом і перевірку коду виходу після mysqldump, щоб не зберігати биті архіви так, ніби це успішна резервна копія.
Стратегії резервного копіювання: порівняння
| Метод | Блокує таблиці | Підходить для великих БД | Інструмент |
|---|---|---|---|
| mysqldump --single-transaction | Ні (лише InnoDB) | До ~50 ГБ комфортно | mysqldump (входить до клієнта MySQL) |
| mysqldump без --single-transaction | Так, FLUSH TABLES WITH READ LOCK | Невеликі БД або тестові середовища | mysqldump |
| Фізична резервна копія (hot copy) | Ні | Сотні ГБ і більше | Percona XtraBackup |
| Знімок диска/VPS | Ні | Будь-який розмір, але без гарантії консистентності БД | Знімки хостингу (LVM, ZFS) |
Коли mysqldump не підходить
- База даних більша за 50–100 ГБ — mysqldump створює текстовий SQL-дамп, відновлення якого може займати години; для таких обсягів використовуйте фізичну резервну копію через XtraBackup.
- Потрібна резервна копія без зупинки запису і без навантаження на I/O в робочий час — зняття повного дампа великої бази створює помітне навантаження, плануйте його на нічне вікно.
- Потрібне point-in-time відновлення (відкат на конкретну секунду) — для цього потрібні бінарні логи й окрема стратегія, а не просто періодичний дамп.
Перевірка результату
- Перевірте, що архів не порожній і не битий:
gzip -t /var/backups/mysql/mydb_*.sql.gz— команда має завершитися без помилок. - Розгорніть дамп у тестову базу і звірте кількість таблиць:
mysql -e "SHOW TABLES" testdb | wc -lмає збігатися з вихідною базою. - Перевірте, що cron дійсно виконує завдання:
grep mysql-backup /var/log/syslogмає показувати записи за кожен день. - Переконайтеся, що копія резервної копії справді потрапляє в хмару:
aws s3 ls s3://your-bucket/mysql-backups/ | tail -5має показувати свіжі файли.
Щодо самого налаштування MariaDB перед резервним копіюванням див. статтю про встановлення і налаштування MariaDB, а для відновлення великих дампів — посібник з імпорту великих дампів.
Часті питання
Чи потрібно зупиняти сервер MySQL перед запуском mysqldump?
Ні, зупиняти сервер не потрібно — саме для цього існує прапорець single-transaction, який знімає консистентний знімок даних InnoDB-таблиць без блокування запису. Застосунок продовжує працювати під час дампа, читаючи і записуючи дані як завжди, а резервна копія виходить цілісною завдяки транзакційному механізму, а не зупинці служби.
Чи можна відновити бекап у базу даних з іншою назвою, ніж в оригіналі?
Так, дамп mysqldump — це звичайний SQL-текст із командами створення і наповнення таблиць, тому його можна подати в базу даних з будь-якою назвою, якщо заздалегідь створити цю базу і посилатися на неї під час відновлення. Назва бази в самому дампі не жорстко прив'язана до оригінальної, якщо дамп не створювався з прапорцем усієї бази цілком.
Чи достатньо зберігати бекап локально на тому самому сервері, де працює база даних?
Ні, зберігати резервну копію виключно на тому самому сервері ризиковано: якщо сервер зламано, він вийшов з ладу чи диск пошкоджено, локальна копія зникає разом з оригіналом. Правильна стратегія передбачає вивантаження копії за межі сервера — у хмарне сховище чи на іншу машину, інакше сенс резервного копіювання втрачається.
Чи потрібно додатково бекапити файли сайту, чи достатньо бекапу лише бази даних?
Резервна копія лише бази даних охоплює структуру і вміст таблиць, але не файли сайту — завантажені медіа, конфігурацію застосунку чи власний код, який зазвичай не зберігається в базі. Повноцінна стратегія відновлення передбачає окреме резервне копіювання файлової системи поряд із дампом бази даних.