Резервное копирование MySQL без остановки

Копируйте базу данных с мастера на вторичный MySQL-сервер (slave) и выполняйте дампы на slave по расписанию. Это даёт согласованные бэкапы без прерывания работы основного сервера. Важно защитить учётные данные и регулярно проверять восстановление.

Введение

Эта инструкция показывает простой и практичный способ делать согласованные резервные копии MySQL без остановки сервиса на основном (master) сервере. Идея: настроить репликацию с мастера на вторичный сервер (slave) и уже на slave создавать SQL-дампы периодически. При использовании такого подхода вы избегаете необходимости блокировать таблицы или останавливать сервис на production-сервере.

Опубликованный пример взят из практики и не является единственно возможным решением. Проверьте подход в вашей среде и убедитесь, что он отвечает требованиям отказоустойчивости и безопасности.

Важно: перед применением повторно протестируйте процесс восстановления — только наличие дампа ещё не гарантирует успешное восстановление.

Предварительная заметка

Чтобы следовать этому руководству, у вас должен быть настроен второй MySQL-сервер (slave) и настроена репликация от master к slave. Настройка репликации не входит в объём этой статьи; используйте официальную документацию MySQL или проверенные пошаговые руководства для этого шага.

Все команды и скрипты, описанные далее, выполняются на slave-сервере — именно там будут создаваться бэкапы.

Я тестировал процесс на Debian; в других дистрибутивах пути к бинарникам и системные утилиты могут отличаться (/usr/local/sbin/mysqlbackup.sh и т.д.).

Что делает предложенное решение — кратко

Репликация гарантирует, что slave получает те же изменения, что и master.
Скрипт на slave останавливает репликацию, делает дамп всех баз в единообразном состоянии, затем снова запускает репликацию.
Дамп сохраняется в каталог (например, /home/sqlbackup) и старые дампы удаляются по политике хранения.

Факто-бокс

Место хранения примера: /home/sqlbackup
Политика хранения в примере: 2 дня
Расписание примера: каждые 3 часа

Создание каталога для бэкапов

На slave создайте каталог для хранения дампов:

mkdir /home/sqlbackup

Скрипт автоматического бэкапа

Создайте файл /usr/local/sbin/mysqlbackup.sh с таким содержимым (ниже — оригинальный пример). Обязательно замените yourrootsqlpassword на пароль root MySQL на slave или используйте безопасные альтернативы, описанные ниже.

#!/bin/sh

datum=`/bin/date +%Y%m%d-%H`

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave

/usr/bin/mysqldump --user=root --password=yourrootsqlpassword --lock-all-tables \
      --all-databases > /home/sqlbackup/backup-${datum}.sql

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword start-slave

for file in "$( /usr/bin/find /home/sqlbackup -type f -mtime +2 )"
do
  /bin/rm -f $file
done

exit 0

Пример имени файла дампа в оригинале: backup-20070423-18.sql — это дамп, сделанный 23 апреля 2007 г. в 18:00. В примере формат даты — YYYYMMDD-HH.

Важно

Никогда не храните пароли в открытом виде в скриптах, доступных для чтения другими пользователями. См. раздел “Безопасность” ниже.
Параметр –lock-all-tables обеспечивает согласованный снимок при mysqldump; на slave это безопасно, так как основной сервер остаётся доступным.

Сделать скрипт исполняемым

chmod 755 /usr/local/sbin/mysqlbackup.sh

Планирование заданий (cron)

Отредактируйте crontab и добавьте задачу, чтобы запускать бэкап каждые 3 часа:

crontab -e

и добавьте строку

0 */3 * * * /usr/local/sbin/mysqlbackup.sh &> /dev/null

Настройте периодичность в соответствии с требованиями RPO вашей системы.

Альтернативы и улучшения

Безопасное хранение учётных данных

Используйте файл ~/.my.cnf (с правами 600) на сервере, чтобы не передавать пароль в командной строке:

[client]
user=root
password=секрет

Тогда в скрипте можно не указывать –user и –password, а mysqldump и mysqladmin возьмут параметры из файла.

Использование более безопасных инструментов для онлайн-бэкапа

Percona XtraBackup — позволяет делать горячие инкрементные бэкапы без блокировок бинарных логов и быстро восстанавливать большие наборы данных.
LVM- или ZFS-снимки — полезны для очень больших баз, если файловая система и конфигурация позволяют.

Инкрементные/дифференциальные стратегии

Используйте бинарные логи и/или инструменты инкрементного резервного копирования, чтобы уменьшить объём передаваемых данных и ускорить восстановление.

Централизованное хранение

Переносите дампы на отдельное хранилище (S3, NFS, bck-server) после создания, чтобы избежать потери при выходе из строя slave.

Тестирование восстановления — критично

Регулярно проверяйте, что дампы действительно восстанавливаются. Простейший сценарий теста:

Подготовить пустую машину/контейнер.
Развернуть MySQL совместимой версии.
Восстановить дамп командой: mysql < backup-YYYYMMDD-HH.sql
Проверить целостность данных и работоспособность приложений.

Критерии приёмки

Дамп успешно импортируется без ошибок.
Примеры выборки данных соответствуют ожидаемым результатам.
Время восстановления укладывается в приемлемое для бизнеса окно.

Частые ошибки и устранение неисправностей

Скрипт не запускается от cron: проверьте PATH, права и SHELL; лучше указывать абсолютные пути в скрипте (как в примере).
Пароль в командной строке: виден в выводе ps — используйте ~/.my.cnf.
Репликация отстает: перед дампом скрипт останавливает slave, затем запускает — убедитесь, что slave catch-up не занимает слишком много времени.
Удаление файлов: в цикле удаления используется find -mtime +2; проверьте корректность логики и часовой пояс сервера.

Роль-ориентированный чек-лист

Администратор БД:

Настроить репликацию master -> slave.
Проверить целостность репликации.
Провести тест восстановления.

Системный администратор:

Настроить каталог /home/sqlbackup и права доступа.
Настроить cron и права на скрипт.

Офис безопасности/операций:

Обеспечить шифрование или передачу дампов в безопасное хранилище.
Контролировать доступ к учётным данным.

Краткое руководство по безопасности

Не храните пароли в явном виде в скриптах: используйте ~/.my.cnf или механизмы Vault.
Ограничьте права на каталог с бэкапами до минимального набора пользователей (chmod 700 /home/sqlbackup).
Шифруйте дампы при необходимости, особенно если они содержат персональные данные.
Контролируйте аудит и уведомления на случай несанкционированного доступа.

Примеры альтернативных команд (без пароля в строке)

mysqldump с использованием options файлa:

/usr/bin/mysqldump --defaults-extra-file=/root/.my.cnf --lock-all-tables --all-databases > /home/sqlbackup/backup-${datum}.sql

Когда этот метод не подходит

Если у вас очень большие БД и полный дамп каждые несколько часов невозможен по объёму и времени — рассматривайте инкрементные решения и XtraBackup.
Если необходимы моментальные снимки точечного восстановления с минимальной задержкой — используйте репликацию + бинарные логи для восстановления до точки во времени.

Резюме

Использование slave-сервера для создания дампов позволяет выполнять согласованные резервные копии без остановки основного MySQL-сервера. Обязательно обеспечьте безопасное хранение паролей, автоматизацию и регулярную проверку восстановления. Подберите стратегию хранения и периодичность в соответствии с требованиями бизнеса.

Примечание