Бекапи MySQL: mysqldump, XtraBackup та Point-in-Time Recovery

Чому бекапи — це не "потім", а "зараз"

Розробник запускає міграцію на production — і SQL-скрипт без належного WHERE пошкоджує 40% таблиці замовлень. Реальний випадок з нашої практики. Паніка, клієнт втрачає гроші щохвилини, менеджери телефонують без зупинки.

Результат? 45 хвилин — і 100% даних відновлено. Врятував автоматичний snapshot, створений за 15 хвилин до інциденту. Більшу частину часу зайняло не саме відновлення, а розгортання дампу на окремому сервері для вибіркового витягування пошкоджених рядків.

Це happy end. Але він можливий тільки коли бекапи налаштовані правильно. У багатьох клієнтів які до нас приходять — бекап тримісячної давнини десь на тому ж сервері. Або cron який тихо фейлить вже півроку.

Цей гайд — про те, як налаштувати бекапи MySQL так, щоб спати спокійно. Від простого mysqldump до production-ready стратегій з інкрементальними бекапами та відновленням до конкретної секунди.

Логічні та фізичні бекапи: в чому різниця

Перш ніж обирати інструмент — треба розуміти що саме він робить. Бекапи MySQL бувають двох типів, і кожен має свої сильні сторони.

Логічні бекапи

Це SQL-дамп: набір команд CREATE TABLE, INSERT INTO, які відтворюють структуру та дані бази з нуля. Інструменти — mysqldump, mysqlpump, phpMyAdmin.

Плюси: портативність (можна перенести між версіями MySQL/MariaDB), читабельність (це звичайний текст), можливість вибрати окремі таблиці. Мінуси: повільне відновлення на великих базах, навантаження на сервер під час дампу.

Фізичні бекапи

Це копія файлів бази даних напряму з диску. Інструменти — Percona XtraBackup, Mariabackup, LVM snapshots.

Плюси: швидке відновлення (просто копіюєш файли назад), мінімальне навантаження під час бекапу. Мінуси: прив'язка до версії MySQL, не можна відновити окрему таблицю (зазвичай).

Повні, інкрементальні та диференціальні

Окрім типу копіювання, бекапи розрізняються за обсягом:

• Повний — копія всієї бази. Найпростіший, але найбільший за розміром.
• Інкрементальний — тільки зміни з моменту останнього бекапу. Швидкий і компактний, але для відновлення потрібен повний + всі інкременти по ланцюжку.
• Диференціальний — зміни з моменту останнього повного бекапу. Компроміс між розміром та простотою відновлення.

Для більшості проєктів оптимальна комбінація: повний бекап раз на тиждень + щоденні інкрементальні.

Правило 3-2-1: золотий стандарт бекапів

Одна з найпоширеніших помилок — зберігати бекап на тому ж сервері, де працює база. Диск помирає — і ви втрачаєте і базу, і бекап одночасно.

Стратегія 3-2-1 вирішує це:

• 3 копії даних (оригінал + 2 бекапи)
• 2 типи носіїв (наприклад, локальний диск + хмарне сховище)
• 1 копія offsite (фізично в іншому місці/дата-центрі)

Hostiserver пропонує Backup Hosting саме для цієї мети — спеціалізоване сховище на базі HDD-масивів з високою відмовостійкістю, доступне через FTP/SFTP/Rsync. Найчастіше його використовують eCommerce проєкти, медіа-портали та SaaS-платформи. Ми допомагаємо налаштувати автоматичну передачу копій відразу після їх створення на локальному сервері.

Retention Policy: схема GFS

Ми використовуємо схему "Grandfather-Father-Son" (GFS) — баланс між глибиною архіву та витратами на сховище:

Для критичних даних (фінансові транзакції, медичні записи) термін зберігання daily-копій збільшується до 30 днів.

mysqldump: надійна класика

mysqldump — це інструмент який є на кожному сервері з MySQL. Простий, перевірений роками, і для баз до 10-20 ГБ — цілком достатній.

Базовий бекап

mysqldump -u root -p --single-transaction --routines --triggers --events --hex-blob mydb > /backup/mydb_$(date +%Y%m%d_%H%M).sql

Розберемо параметри — це стандарт який ми використовуємо в Hostiserver за замовчуванням:

• --single-transaction — обов'язково для InnoDB. Робить бекап без блокування таблиць, сайт продовжує працювати.
• --routines — включає збережені процедури та функції (бізнес-логіка на рівні БД).
• --triggers — включає тригери.
• --events — включає заплановані події MySQL.
• --hex-blob — коректна обробка бінарних даних (BLOB-полів). Без нього бінарні дані можуть пошкодитись при відновленні.

Якщо потрібна реплікація або PITR, додайте --master-data=2 — він зафіксує позицію бінарного лога у вигляді коментаря в дампі.

Бекап з компресією

mysqldump -u root -p --single-transaction mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

SQL-дампи стискаються в 5-10 разів. База на 2 ГБ перетворюється на файл 200-400 МБ.

Бекап всіх баз одразу

mysqldump -u root -p --single-transaction --all-databases --routines --triggers | gzip > /backup/all_db_$(date +%Y%m%d).sql.gz

Відновлення

# Зі звичайного файлу
mysql -u root -p mydb < /backup/mydb_20260302.sql
# З gzip архіву
gunzip < /backup/mydb_20260302.sql.gz | mysql -u root -p mydb

Percona XtraBackup: бекапи без зупинки бази

Для production-серверів з базами від 50 ГБ, де навіть кілька секунд блокування — це проблема, є Percona XtraBackup. Це open-source інструмент який робить "гарячі" фізичні бекапи InnoDB без зупинки MySQL.

Принцип роботи: XtraBackup копіює файли InnoDB та паралельно записує redo-логи. Потім на етапі prepare застосовує ці логи для отримання консистентного стану. Все це — поки база продовжує обробляти запити.

Повний бекап

# Створення бекапу
xtrabackup --backup --target-dir=/backup/full
# Підготовка (обов'язково перед відновленням!)
xtrabackup --prepare --target-dir=/backup/full
# Відновлення
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Інкрементальний бекап

# Спочатку повний бекап
xtrabackup --backup --target-dir=/backup/full
# Інкремент за понеділок
xtrabackup --backup --target-dir=/backup/inc_mon --incremental-basedir=/backup/full
# Інкремент за вівторок
xtrabackup --backup --target-dir=/backup/inc_tue --incremental-basedir=/backup/inc_mon

Інкрементальні бекапи копіюють лише змінені сторінки InnoDB — тому вони набагато менші і швидші за повні.

Автоматизація: бекапи які працюють без вас

Ручні бекапи — це бекапи які забувають робити. Автоматизація через cron — мінімум, який має бути на кожному сервері.

Щоденний бекап через cron

# Редагування crontab
crontab -e
# Бекап щодня о 3:00 ночі
0 3 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

Скрипт бекапу

#!/bin/bash
# /usr/local/bin/backup_mysql.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M)
RETENTION_DAYS=14
# Створення бекапу
mysqldump -u backup_user -p'secure_password' \
  --single-transaction --routines --triggers --events --hex-blob \
  --all-databases | gzip > "$BACKUP_DIR/all_db_$DATE.sql.gz"
# Перевірка успішності
if [ $? -eq 0 ]; then
  echo "[$DATE] Бекап створено успішно: $(du -h $BACKUP_DIR/all_db_$DATE.sql.gz | cut -f1)"
else
  echo "[$DATE] ПОМИЛКА: бекап не створено!" >& 2
  # Тут можна додати відправку алерту
fi
# Видалення старих бекапів
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] Видалено бекапи старші $RETENTION_DAYS днів"

[mysqldump]
user=backup_user
password=secure_password

Моніторинг бекапів

Бекап-система яка мовчить — це бекап-система яка може бути зламана. Додайте перевірки:

• Алерт якщо файл бекапу менший за очікуваний розмір (порожній дамп = проблема)
• Алерт якщо бекап не з'явився за розкладом
• Щотижнева перевірка цілісності: gunzip -t backup.sql.gz

Point-in-Time Recovery: відновлення до конкретної секунди

Уявіть: о 14:35 розробник випадково запустив UPDATE без WHERE і перезаписав email всіх клієнтів. Останній повний бекап — о 3:00 ночі. Без PITR ви втрачаєте все що сталось між 3:00 та 14:35 — замовлення, реєстрації, оплати.

Point-in-Time Recovery вирішує це. Принцип: MySQL записує кожну зміну в binary log. Після відновлення повного бекапу ви "програєте" binary logs до потрібного моменту.

PITR — стандартна практика для Enterprise-клієнтів та проєктів де втрата навіть години даних критична (eCommerce, фінанси, SaaS). Ми налаштовуємо ротацію бінарних логів та їхню регулярну відправку на Backup Hosting. Це дозволяє при аварії "накотити" всі зміни що відбулися після останнього нічного бекапу.

Налаштування binary log

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 14
max_binlog_size = 100M

Відновлення до конкретного часу

# 1. Відновити повний бекап
mysql -u root -p mydb < /backup/full_20260302_0300.sql
# 2. Програти binary logs до моменту ДО помилки
mysqlbinlog --stop-datetime="2026-03-02 14:34:59" \
  /var/log/mysql/mysql-bin.000042 \
  /var/log/mysql/mysql-bin.000043 | mysql -u root -p mydb

Результат: база відновлена з усіма даними до 14:34:59 — за секунду до помилки.

Бекап який не перевіряли — не існує

Це не перебільшення. Ми бачили випадки коли клієнт місяцями робив бекапи через cron, а потім при відновленні виявилось що файли порожні — бо MySQL пароль змінився і mysqldump тихо фейлив.

Як перевіряти

• Щодня: перевірка розміру файлу (не нульовий? не аномально маленький?)
• Щотижня: тестове відновлення на staging-сервер
• Щомісяця: повне тестове відновлення з перевіркою даних

Автоматична перевірка

# Перевірка що файл не порожній і більше 1 МБ
BACKUP_FILE="/backup/mysql/all_db_$(date +%Y%m%d)*.sql.gz"
MIN_SIZE=1048576  # 1 MB в байтах
FILE_SIZE=$(stat -c%s $BACKUP_FILE 2>/dev/null || echo 0)
if [ "$FILE_SIZE" -lt "$MIN_SIZE" ]; then
  echo "ALERT: Бекап підозріло малий або відсутній!" | mail -s "Backup Alert" admin@example.com
fi

Який інструмент обрати

Ось порівняння основних інструментів бекапу MySQL/MariaDB для різних сценаріїв:

5 помилок з бекапами які ми бачимо постійно

1. Бекап на тому ж диску що і база. Диск помирає — втрачаєте все. Завжди копіюйте бекапи на окремий носій або в хмарне сховище.

2. Ніхто не перевіряє бекапи. Cron тихо фейлить місяцями. Додайте моніторинг розміру файлів і алерти на помилки.

3. Бекап без --single-transaction. mysqldump блокує таблиці — сайт лежить. Для InnoDB завжди додавайте цей прапорець.

4. Забули про процедури та тригери. mysqldump за замовчуванням НЕ включає stored procedures, triggers та events. Використовуйте --routines --triggers --events.

5. Пароль у plaintext в crontab. Будь-хто з доступом до сервера побачить пароль через ps aux. Використовуйте ~/.my.cnf з правами 600.

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