Бэкап MySQL, который делается руками «когда вспомнил», — это отсутствие бэкапа: в день аварии свежей копии не окажется. Правильная схема — автоматическая: mysqldump снимает дамп по расписанию cron, скрипт сжимает его, удаляет устаревшие копии и отправляет свежую на другой сервер. Настраивается всё это за полчаса и дальше работает без участия человека. В этой статье соберём такую систему по шагам — от отдельного пользователя для бэкапов до проверки восстановления.
Как устроена схема
- cron запускает скрипт каждую ночь.
- mysqldump выгружает базы в SQL-файл без блокировки таблиц.
- gzip сжимает дамп (текстовый SQL ужимается в 5–10 раз).
- Ротация удаляет копии старше заданного срока, чтобы диск не переполнился.
- Выгрузка копирует свежий архив на другой сервер — копия на той же машине не переживёт отказ диска.
Если MySQL у вас ещё не развёрнут, начните со статьи установка MySQL на VPS.
Шаг 1. Пользователь только для бэкапов
Запускать дампы под root — плохая практика: пароль суперпользователя окажется в конфиге скрипта. Создайте учётку с минимально необходимыми правами:
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'ДлинныйПарольБэкапа789!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
Права SELECT хватает для чтения данных, SHOW VIEW, EVENT и TRIGGER нужны, чтобы в дамп попали представления, события и триггеры, а PROCESS — для --single-transaction на всех базах.
Пароль храните не в скрипте, а в файле ~/.my.cnf пользователя, от которого идёт бэкап:
cat > /root/.my.cnf << 'EOF'
[client]
user = backup
password = ДлинныйПарольБэкапа789!
EOF
chmod 600 /root/.my.cnf
Права 600 обязательны — иначе пароль сможет прочитать любой пользователь системы, а свежие версии клиента вообще откажутся использовать файл.
Шаг 2. Правильные ключи mysqldump
Базовая команда для одной базы:
mysqldump --single-transaction --quick --routines --triggers --events \
имя_базы | gzip > /backup/db_$(date +%F).sql.gz
Что делает каждый ключ:
| Ключ | Назначение |
|---|---|
--single-transaction | Согласованный снимок InnoDB без блокировки таблиц — сайт продолжает работать |
--quick | Построчная выгрузка больших таблиц без буферизации в память |
--routines | Включить в дамп хранимые процедуры и функции |
--triggers | Включить триггеры (по умолчанию включено, но лучше явно) |
--events | Включить события планировщика MySQL |
--all-databases | Дамп всех баз разом (альтернатива перечислению) |
Для баз с MyISAM-таблицами --single-transaction согласованность не гарантирует — такие таблицы стоит мигрировать на InnoDB или снимать дамп в окно минимальной нагрузки.
Шаг 3. Скрипт бэкапа с ротацией
Создайте файл /usr/local/bin/mysql_backup.sh:
#!/bin/bash
# Ночной бэкап всех баз MySQL: дамп -> gzip -> ротация -> выгрузка
set -euo pipefail
BACKUP_DIR="/backup/mysql"
KEEP_DAYS=14 # хранить копии 14 дней
DATE=$(date +%F_%H-%M)
LOG="/var/log/mysql_backup.log"
mkdir -p "$BACKUP_DIR"
echo "[$(date '+%F %T')] backup started" >> "$LOG"
# Дамп каждой базы в отдельный файл — удобнее восстанавливать выборочно
for DB in $(mysql -N -e "SHOW DATABASES;" \
| grep -Ev '^(information_schema|performance_schema|sys)$'); do
mysqldump --single-transaction --quick --routines --triggers --events \
"$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
# Проверка: непустой ли архив
if [ ! -s "$BACKUP_DIR/${DB}_${DATE}.sql.gz" ]; then
echo "[$(date '+%F %T')] ERROR: empty dump for $DB" >> "$LOG"
exit 1
fi
done
# Ротация: удалить архивы старше KEEP_DAYS
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
echo "[$(date '+%F %T')] backup finished OK" >> "$LOG"
Сделайте скрипт исполняемым и прогоните вручную:
chmod 700 /usr/local/bin/mysql_backup.sh
/usr/local/bin/mysql_backup.sh
ls -lh /backup/mysql/
Ключевые решения в скрипте: set -euo pipefail останавливает выполнение при любой ошибке (без него mysqldump может упасть, а gzip молча создаст пустой архив «успешного» бэкапа), дамп каждой базы в отдельный файл упрощает выборочное восстановление, а проверка -s ловит пустые файлы.
Шаг 4. Расписание в cron
Добавьте задание в crontab root:
crontab -e
# Бэкап MySQL каждую ночь в 03:17 (не в круглое время — меньше конкуренции с другими задачами)
17 3 * * * /usr/local/bin/mysql_backup.sh
Проверить, что cron видит задание и оно выполняется:
crontab -l
grep mysql_backup /var/log/syslog | tail -5
tail -20 /var/log/mysql_backup.log
Для больших баз выбирайте время минимальной нагрузки: дамп читает данные интенсивно и на слабом диске может замедлить сайт. На NVMe-накопителях этот эффект минимален.
Шаг 5. Копия на другом сервере
Бэкап на том же VPS защищает от DROP TABLE, но не от потери сервера. Добавьте в конец скрипта выгрузку по SSH:
# Выгрузка свежих архивов на резервный сервер (ключевая аутентификация)
REMOTE="backup@10.0.0.9:/srv/backups/mysql/"
rsync -az --remove-source-files=никогда -e "ssh -i /root/.ssh/backup_key" \
"$BACKUP_DIR/" "$REMOTE" >> "$LOG" 2>&1 || \
echo "[$(date '+%F %T')] WARN: rsync failed" >> "$LOG"
Ключ создаётся командой ssh-keygen -t ed25519 -f /root/.ssh/backup_key, публичная часть добавляется на приёмник в ~/.ssh/authorized_keys. В качестве приёмника подойдёт даже самый дешёвый сервер — хранилищу бэкапов не нужны большие ресурсы. Общая стратегия резервного копирования всего сервера, а не только базы, разобрана в статье резервное копирование VPS.
Шаг 6. Проверка восстановления
Бэкап, который ни разу не восстанавливали, — это лотерея. Раз в месяц проверяйте копию на тестовой базе:
gunzip < /backup/mysql/app_db_2026-07-18_03-17.sql.gz | mysql -u root -p restore_test
-- Перед восстановлением создайте пустую базу:
CREATE DATABASE restore_test;
-- После — сверьте число таблиц и строк в ключевых таблицах:
SELECT COUNT(*) FROM restore_test.orders;
Если строки на месте и приложение на тестовой базе поднимается — копия рабочая. Заодно вы будете точно знать время восстановления, что критично при реальной аварии.
Для проектов, где потеря даже часа данных недопустима, дампы дополняют горячей копией на втором сервере — как её настроить, читайте в статье репликация MySQL.
Частые вопросы
Как часто делать бэкап MySQL?
Отталкивайтесь от допустимой потери данных. Для блога или визитки достаточно ночного дампа: потеря суток некритична. Для магазина с заказами — дамп каждые 4–6 часов плюс хранение бинарных логов, которые позволяют докатить изменения до нужной минуты. Если и это слишком грубо — добавляйте репликацию.
Сколько копий хранить?
Рабочий минимум — 7–14 ежедневных копий плюс по одной на конец каждой недели за месяц. Повреждение данных не всегда замечают сразу, поэтому глубина в две недели спасает там, где «вчерашняя копия» уже содержит проблему. В скрипте выше срок задаётся одной переменной KEEP_DAYS.
mysqldump тормозит сайт во время бэкапа?
С ключом --single-transaction InnoDB-таблицы не блокируются — сайт продолжает принимать запросы. Нагрузка сводится к интенсивному чтению с диска; на NVMe она практически незаметна, на медленных дисках дамп большой базы может ощущаться. Планируйте запуск на часы минимального трафика.
Подходит ли mysqldump для очень больших баз?
До 20–50 ГБ логический дамп работает отлично. Дальше главным становится не снятие копии, а время восстановления: заливка сотен гигабайт SQL может занять часы. Для таких объёмов используют физические копии (например, XtraBackup) или снимают дампы с реплики, но для абсолютного большинства сайтов на VPS mysqldump — оптимальный выбор по простоте и надёжности.
Можно ли просто копировать файлы из /var/lib/mysql?
На работающем сервере — нельзя: файлы InnoDB меняются в момент копирования, и такая «копия» с высокой вероятностью не запустится. Копирование каталога данных допустимо только при остановленном MySQL. mysqldump снимает согласованный снимок без остановки службы — потому он и стандарт.
Ночные дампы почти не заметны, когда база живёт на быстром диске. Тариф Start от Cheap-Host за 450 ₽/мес даёт KVM-VPS в Москве с 6 vCPU, 8 ГБ RAM и 96 ГБ NVMe — хватит и на базу, и на хранение двух недель копий. Сервер выдаётся за 45–60 секунд: cheap-host.onl.