Стратегии бэкапа MySQL: защита базы 500 ГБ от потери данных

Интернет-магазин «ШопРу» обратился к инженерам itfresh.ru после инцидента, стоившего компании четыре часа простоя. Разработчик выполнил некорректный UPDATE без условия WHERE на таблице заказов — 800 000 строк были перезаписаны. Бэкап оказался четырёхдневной давности, а журнал бинарных логов не был включён.

Восстановление из бэкапа означало потерю четырёх дней заказов. Ручное восстановление из логов приложения заняло 12 часов работы двух инженеров и всё равно привело к расхождениям в данных.

Характеристики системы «ШопРу»:

• MySQL 8.0 на выделенном сервере (32 vCPU, 128 ГБ RAM, NVMe SSD).
• Объём базы — 500 ГБ, ежедневный прирост — 2-3 ГБ.
• Пиковая нагрузка — 3 000 запросов в секунду (Чёрная пятница).
• Допустимый простой — не более 30 минут.
• Допустимая потеря данных (RPO) — не более 5 минут.

Нашей задачей было построить многоуровневую систему резервного копирования, способную восстановить базу до состояния на любую секунду с минимальным временем простоя.

mysqldump — штатная утилита MySQL, создающая SQL-скрипт для полного воспроизведения базы данных. Для InnoDB-таблиц ключевой параметр — --single-transaction, который создаёт консистентный снимок базы без блокировки таблиц.

# Полный логический бэкап с консистентным снапшотом
mysqldump \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --set-gtid-purged=ON \
    --source-data=2 \
    --all-databases \
    | gzip > /backup/full_$(date +%Y%m%d_%H%M%S).sql.gz

# Расшифровка параметров:
# --single-transaction  — START TRANSACTION WITH CONSISTENT SNAPSHOT
#                         (не блокирует запись во время дампа)
# --routines            — включить хранимые процедуры и функции
# --triggers            — включить триггеры
# --events              — включить события планировщика
# --set-gtid-purged=ON  — для корректной репликации
# --source-data=2       — записать позицию бинлога в комментарий

# Бэкап отдельной таблицы
mysqldump --single-transaction shopru orders \
    > /backup/orders_$(date +%Y%m%d).sql

# Восстановление
gunzip < /backup/full_20260405_030000.sql.gz | mysql

Проблема mysqldump для базы 500 ГБ: создание дампа занимает 4-6 часов, а восстановление — 8-12 часов. Для «ШопРу» с требованием RTO 30 минут это неприемлемо. Однако mysqldump остаётся полезным для:

• Бэкапа отдельных таблиц или схемы (без данных — --no-data).
• Миграции между версиями MySQL.
• Создания копии базы для разработки и тестирования.
• Кросс-платформенного переноса (MySQL → PostgreSQL через промежуточный формат).

Percona XtraBackup — инструмент для создания физических бэкапов InnoDB без остановки сервера и блокировки таблиц. Он копирует файлы данных InnoDB, параллельно отслеживая изменения через redo log. После копирования применяет накопившиеся изменения, получая консистентную копию.

# Установка Percona XtraBackup
apt install percona-xtrabackup-80

# Полный бэкап (без остановки сервера)
xtrabackup --backup \
    --target-dir=/backup/full/2026-04-05 \
    --user=backup_user \
    --password=*** \
    --parallel=4 \
    --compress \
    --compress-threads=4

# Время полного бэкапа 500 ГБ: ~45 минут (SSD)
# Размер с сжатием: ~180 ГБ

Ключевое преимущество XtraBackup — поддержка инкрементальных бэкапов. После полного бэкапа последующие копируют только изменённые страницы InnoDB:

# Инкрементальный бэкап (только изменения с последнего полного)
xtrabackup --backup \
    --target-dir=/backup/inc/2026-04-05 \
    --incremental-basedir=/backup/full/2026-04-01 \
    --user=backup_user \
    --password=*** \
    --parallel=4

# Размер инкрементального бэкапа: 5-15 ГБ (зависит от объёма изменений)
# Время: 5-10 минут

# Подготовка к восстановлению: применяем инкременты к полному бэкапу
xtrabackup --prepare --apply-log-only \
    --target-dir=/backup/full/2026-04-01

xtrabackup --prepare \
    --target-dir=/backup/full/2026-04-01 \
    --incremental-dir=/backup/inc/2026-04-02

xtrabackup --prepare \
    --target-dir=/backup/full/2026-04-01 \
    --incremental-dir=/backup/inc/2026-04-03

# и так далее для каждого инкремента...

# Восстановление: останавливаем MySQL, заменяем datadir
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full/2026-04-01
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# Время восстановления 500 ГБ: ~20 минут (SSD)

Для «ШопРу» мы настроили следующую схему: полный бэкап по воскресеньям, инкрементальные — каждую ночь. Время восстановления (RTO) — около 30 минут, что укладывается в требования клиента.

Бинарные логи (binlog) MySQL записывают каждое изменение данных. Это позволяет «проиграть» изменения от момента бэкапа до нужной секунды — Point-in-Time Recovery (PITR).

# Включаем бинарные логи в my.cnf
[mysqld]
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW          # Записывать изменённые строки, не SQL
binlog_row_image = FULL      # Полные образы строк (до и после)
expire_logs_days = 14        # Хранить бинлоги 14 дней
max_binlog_size = 256M       # Ротация файла при достижении 256 МБ
sync_binlog = 1              # Синхронная запись на диск
gtid_mode = ON               # Глобальные ID транзакций
enforce_gtid_consistency = ON

# Проверяем текущие бинлоги
mysql -e "SHOW BINARY LOGS;"
# +------------------+-----------+
# | Log_name         | File_size |
# +------------------+-----------+
# | mysql-bin.000042 | 256000000 |
# | mysql-bin.000043 | 134217728 |
# +------------------+-----------+

Сценарий восстановления: разработчик выполнил деструктивный UPDATE 5 апреля в 14:32:15. Бэкап сделан ночью в 03:00. Нам нужно восстановить базу до 14:32:14 — за секунду до ошибки.

# Шаг 1: Восстанавливаем из ночного бэкапа XtraBackup
xtrabackup --prepare --target-dir=/backup/full/2026-04-05
xtrabackup --copy-back --target-dir=/backup/full/2026-04-05

# Шаг 2: Находим позицию бинлога на момент бэкапа
cat /backup/full/2026-04-05/xtrabackup_binlog_info
# mysql-bin.000042  156732890  uuid:1-58432

# Шаг 3: Применяем бинлоги до момента перед ошибкой
mysqlbinlog \
    --start-position=156732890 \
    --stop-datetime="2026-04-05 14:32:14" \
    /var/log/mysql/mysql-bin.000042 \
    /var/log/mysql/mysql-bin.000043 \
    | mysql

# Результат: база восстановлена до состояния
# на 14:32:14 — за секунду до ошибки

Бинарные логи на SSD-накопителе при нагрузке «ШопРу» генерируют 5-15 ГБ в день. Мы настроили их копирование на отдельный сервер каждые 5 минут, обеспечивая RPO = 5 минут:

# /usr/local/bin/backup-binlogs.sh
#!/bin/bash
rsync -avz --progress \
    /var/log/mysql/mysql-bin.* \
    backup-server:/backup/binlogs/shopru/

# Cron: каждые 5 минут
*/5 * * * * /usr/local/bin/backup-binlogs.sh >> /var/log/binlog-backup.log 2>&1

Стандартная репликация MySQL воспроизводит изменения на реплике практически мгновенно. Это защищает от аппаратного сбоя мастера, но не от логических ошибок: DROP TABLE или некорректный UPDATE выполнятся на реплике через секунды.

Отложенная реплика (delayed replica) — реплика, которая намеренно отстаёт от мастера на заданный интервал. Если ошибка произошла 10 минут назад, а реплика отстаёт на час — на ней ещё хранятся корректные данные.

# Настройка отложенной реплики (MySQL 8.0)
# На реплике:
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'master.shopru.local',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = '***',
    SOURCE_AUTO_POSITION = 1,
    SOURCE_DELAY = 3600;  -- отставание 1 час (в секундах)

START REPLICA;

# Проверка статуса
SHOW REPLICA STATUS\G
# Seconds_Behind_Source: 3600
# SQL_Delay: 3600
# SQL_Remaining_Delay: NULL

# Восстановление после ошибки:
# 1. Останавливаем реплику
STOP REPLICA;

# 2. Применяем события до момента перед ошибкой
START REPLICA UNTIL SQL_BEFORE_GTIDS = 'uuid:58430';
# или
START REPLICA UNTIL SOURCE_LOG_FILE = 'mysql-bin.000043',
              SOURCE_LOG_POS = 12345678;

# 3. Переключаем приложение на реплику
# (или экспортируем таблицу и импортируем на мастер)

Для «ШопРу» мы настроили две реплики:

• Реплика 1 — без задержки, для чтения (отчёты, поиск). Разгружает мастер на 40%.
• Реплика 2 — с задержкой 1 час, для защиты от логических ошибок. Размещена в другом дата-центре.

Все компоненты системы бэкапов «ШопРу» объединены в единый скрипт, запускаемый по расписанию:

#!/bin/bash
# /usr/local/bin/mysql-backup.sh
set -euo pipefail

BACKUP_ROOT="/backup/mysql"
DATE=$(date +%Y-%m-%d)
DAY_OF_WEEK=$(date +%u)  # 1=Пн, 7=Вс
RETENTION_FULL=30        # Полные бэкапы: 30 дней
RETENTION_INC=14         # Инкрементальные: 14 дней
LOG="/var/log/mysql-backup.log"

log() { echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> $LOG; }

if [ "$DAY_OF_WEEK" -eq 7 ]; then
    # Воскресенье: полный бэкап
    log "Starting full backup"
    DEST="${BACKUP_ROOT}/full/${DATE}"
    mkdir -p "$DEST"

    xtrabackup --backup \
        --target-dir="$DEST" \
        --user=backup_user \
        --password="$(cat /root/.mysql_backup_pass)" \
        --parallel=4 \
        --compress \
        --compress-threads=4 \
        2>> $LOG

    # Сохраняем путь к последнему полному бэкапу
    echo "$DEST" > ${BACKUP_ROOT}/last_full_path
    log "Full backup completed: $(du -sh $DEST | cut -f1)"
else
    # Будни: инкрементальный бэкап
    log "Starting incremental backup"
    LAST_FULL=$(cat ${BACKUP_ROOT}/last_full_path)
    DEST="${BACKUP_ROOT}/inc/${DATE}"
    mkdir -p "$DEST"

    xtrabackup --backup \
        --target-dir="$DEST" \
        --incremental-basedir="$LAST_FULL" \
        --user=backup_user \
        --password="$(cat /root/.mysql_backup_pass)" \
        --parallel=4 \
        2>> $LOG

    log "Incremental backup completed: $(du -sh $DEST | cut -f1)"
fi

# Копируем на удалённый сервер
rsync -az --delete "$DEST" backup-server:/backup/mysql/shopru/
log "Remote sync completed"

# Ротация: удаляем старые бэкапы
find ${BACKUP_ROOT}/full/ -maxdepth 1 -mtime +${RETENTION_FULL} -exec rm -rf {} \;
find ${BACKUP_ROOT}/inc/ -maxdepth 1 -mtime +${RETENTION_INC} -exec rm -rf {} \;
log "Rotation completed"

# Cron: каждую ночь в 03:00
# 0 3 * * * /usr/local/bin/mysql-backup.sh

Бэкап, который не был протестирован — это не бэкап, а файл, который занимает место на диске. Мы настроили автоматическую проверку восстановления каждую неделю:

#!/bin/bash
# /usr/local/bin/mysql-backup-verify.sh
# Запускается на отдельном сервере (не на production!)

LAST_FULL=$(ssh prod-server cat /backup/mysql/last_full_path)
DATE=$(date +%Y-%m-%d)
LOG="/var/log/backup-verify.log"
TEST_DIR="/tmp/backup-test"
TESTDB_PORT=3307

log() { echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> $LOG; }

log "Starting backup verification"

# Копируем бэкап
rsync -az prod-server:${LAST_FULL}/ ${TEST_DIR}/

# Подготавливаем бэкап
xtrabackup --decompress --target-dir=${TEST_DIR}
xtrabackup --prepare --target-dir=${TEST_DIR}

# Запускаем тестовый экземпляр MySQL
mysqld_safe --datadir=${TEST_DIR} --port=${TESTDB_PORT} \
    --socket=/tmp/mysql-test.sock &

sleep 10

# Проверяем целостность таблиц
mysqlcheck --all-databases --check \
    --port=${TESTDB_PORT} \
    --socket=/tmp/mysql-test.sock \
    2>> $LOG

RESULT=$?

# Проверяем количество записей в критичных таблицах
ORDERS=$(mysql --port=${TESTDB_PORT} --socket=/tmp/mysql-test.sock \
    -N -e "SELECT COUNT(*) FROM shopru.orders")
USERS=$(mysql --port=${TESTDB_PORT} --socket=/tmp/mysql-test.sock \
    -N -e "SELECT COUNT(*) FROM shopru.users")

log "Verification result: exit_code=${RESULT}, orders=${ORDERS}, users=${USERS}"

# Останавливаем тестовый MySQL
mysqladmin --port=${TESTDB_PORT} --socket=/tmp/mysql-test.sock shutdown
rm -rf ${TEST_DIR}

# Отправляем уведомление
if [ $RESULT -eq 0 ]; then
    curl -s "https://api.telegram.org/bot***/sendMessage" \
        -d chat_id=CHAT_ID \
        -d text="✅ Backup verify OK: orders=${ORDERS}, users=${USERS}"
else
    curl -s "https://api.telegram.org/bot***/sendMessage" \
        -d chat_id=CHAT_ID \
        -d text="❌ Backup verify FAILED! Check logs."
fi

Мониторинг свежести бэкапов через Prometheus + Alertmanager:

# Экспортер проверяет возраст последнего бэкапа
# /usr/local/bin/backup-exporter.sh
#!/bin/bash
LAST_BACKUP=$(stat -c %Y /backup/mysql/last_full_path)
NOW=$(date +%s)
AGE_HOURS=$(( (NOW - LAST_BACKUP) / 3600 ))

echo "# HELP mysql_backup_age_hours Age of last backup in hours"
echo "# TYPE mysql_backup_age_hours gauge"
echo "mysql_backup_age_hours ${AGE_HOURS}"

# Алерт в Prometheus:
# alert: MySQLBackupTooOld
#   expr: mysql_backup_age_hours > 36
#   for: 1h
#   annotations:
#     summary: "MySQL backup older than 36 hours"

Итоговое сравнение всех методов резервного копирования, внедрённых для «ШопРу»:

* С применением инкрементов. ** Дополнительно к восстановлению из XtraBackup. *** Переключение приложения на реплику.

Результаты внедрения многоуровневой системы бэкапов:

Стоимость хранения выросла, но она на три порядка меньше потерь от одного инцидента. Если ваш бизнес зависит от базы данных MySQL и текущий бэкап — это mysqldump по cron раз в неделю — обращайтесь к специалистам itfresh.ru. Мы построим систему резервного копирования, соответствующую вашим требованиям по RPO и RTO.