Резервное копирование MySQL: mysqldump и Percona XtraBackup

Логическое и физическое резервное копирование: в чём разница

У резервного копирования MySQL есть два принципиально разных подхода. Логическое копирование экспортирует содержимое базы данных в виде текстовых SQL-команд — инструкций CREATE TABLE и INSERT, которые при восстановлении нужно заново выполнить, полностью пересоздавая структуру таблиц и все их индексы с нуля. Физическое копирование, напротив, работает на уровне файлов данных на диске, копируя их напрямую в формате, в котором СУБД хранит информацию внутри себя.

mysqldump — стандартный инструмент логического резервного копирования, входящий в поставку MySQL и MariaDB без дополнительной установки. Percona XtraBackup — инструмент физического резервного копирования, разработанный отдельно компанией Percona и распространяемый бесплатно с открытым исходным кодом.

Выбор между ними определяется размером базы данных и требованиями к простою при снятии копии: для небольших и средних баз данных (до нескольких гигабайт) простота и универсальность mysqldump обычно перевешивают его ограничения, тогда как для крупных промышленных баз данных физическое копирование через XtraBackup становится практически обязательным из-за принципиально более высокой скорости и меньшей нагрузки на сервер во время создания копии.

mysqldump: базовое использование

Простейший вызов mysqldump экспортирует одну базу данных целиком в текстовый файл с SQL-командами, но для реального использования в продакшне почти всегда нужен набор дополнительных флагов.

mysqldump --single-transaction --routines --triggers --events \
    -u backup_user -p mydatabase | gzip > mydatabase-$(date +%F).sql.gz

Флаг --single-transaction критичен для таблиц InnoDB: он открывает транзакцию с уровнем изоляции REPEATABLE READ перед началом экспорта, благодаря чему все данные попадают в дамп в согласованном состоянии на момент старта, без блокировки таблиц для остальных клиентов, которые продолжают читать и писать данные параллельно с созданием резервной копии. Флаги --routines, --triggers и --events добавляют в дамп хранимые процедуры, триггеры и события планировщика MySQL, которые без этих флагов по умолчанию не попадают в экспорт и легко забываются при восстановлении, если резервная копия делалась без явного указания этих опций.

mysqldump -u backup_user -p --all-databases --single-transaction > all-databases.sql

Для сервера с несколькими базами данных зачастую удобнее делать отдельный файл на каждую базу данных, а не один общий дамп через --all-databases: это упрощает выборочное восстановление конкретной базы без необходимости разбирать огромный общий файл, и позволяет параллельно бэкапить разные базы данных независимыми процессами.

Ограничения mysqldump на больших базах данных

mysqldump прекрасно справляется с базами данных умеренного размера, но по мере роста объёма данных начинают проявляться два практических ограничения. Первое — время: экспорт данных в текстовый SQL-формат, а затем восстановление через повторное выполнение всех этих SQL-команд с пересборкой индексов с нуля, на базах данных от десятков гигабайт и выше может занимать многие часы, что напрямую увеличивает RTO при необходимости срочного восстановления.

Второе ограничение — нагрузка на сервер во время самого процесса экспорта: даже с --single-transaction, снимающим блокировку с таблиц, чтение всего объёма данных для формирования дампа создаёт заметную дополнительную нагрузку на дисковую подсистему и CPU сервера, конкурирующую с обычными запросами приложения, особенно на серверах, уже работающих близко к пределу своих ресурсов.

ls -lh mydatabase-2026-11-03.sql.gz
time mysqldump --single-transaction -u backup_user -p mydatabase > /dev/null

Практическое правило: если полный дамп через mysqldump занимает заметную долю от допустимого окна резервного копирования (например, больше часа при желаемой частоте бэкапов раз в несколько часов) или создаёт ощутимую просадку производительности приложения во время своей работы, это сигнал переходить на физическое резервное копирование через XtraBackup, разбираемое ниже.

Percona XtraBackup: горячее физическое копирование без блокировок

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

apt install percona-xtrabackup-80
xtrabackup --backup --target-dir=/var/backups/xtrabackup/full-$(date +%F) \
    --user=backup_user --password=secret

Физическая природа копии означает, что при восстановлении не требуется заново выполнять миллионы SQL-команд и пересобирать индексы — файлы данных просто копируются обратно на своё место, и MySQL запускается с уже готовой, полностью сформированной структурой данных на диске, что для больших баз данных даёт заметное ускорение как самого резервного копирования, так и восстановления по сравнению с mysqldump.

Оборотная сторона физического копирования — меньшая переносимость: дамп mysqldump в виде текстовых SQL-команд можно применить практически к любой версии и конфигурации MySQL или совместимой СУБД, тогда как физическая копия XtraBackup жёстко привязана к той же основной версии сервера баз данных и требует большей аккуратности при переносе между заметно различающимися конфигурациями серверов.

Подготовка и восстановление резервной копии XtraBackup

Копия, снятая через xtrabackup --backup, не готова к немедленному использованию — на момент завершения копирования в неё могли попасть незавершённые транзакции, и требуется отдельный шаг подготовки (prepare), приводящий данные в согласованное состояние, как будто MySQL был корректно остановлен именно в этот момент.

xtrabackup --prepare --target-dir=/var/backups/xtrabackup/full-2026-11-03

После подготовки восстановление сводится к остановке MySQL, очистке (или переносу в сторону) текущего каталога данных, копированию подготовленной резервной копии на его место и восстановлению корректных прав владения файлами перед повторным запуском сервера.

systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql.old
xtrabackup --copy-back --target-dir=/var/backups/xtrabackup/full-2026-11-03
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Именно на этом этапе восстановления, а не на этапе создания копии, MySQL приходится останавливать — это стоит явно учитывать при расчёте RTO: сама резервная копия снимается без простоя, но фактическое восстановление после реальной аварии всё равно требует времени на остановку сервера, копирование файлов данных обратно и повторный запуск.

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

XtraBackup поддерживает инкрементальные бэкапы, содержащие только страницы данных, изменившиеся с момента предыдущей полной или инкрементальной копии, — это определяется по номеру LSN (Log Sequence Number), внутреннему счётчику изменений в журнале InnoDB.

xtrabackup --backup --target-dir=/var/backups/xtrabackup/inc-1 \
    --incremental-basedir=/var/backups/xtrabackup/full-2026-11-03 \
    --user=backup_user --password=secret

Типичная схема — одна полная резервная копия раз в неделю и инкрементальные копии несколько раз в день между полными: это заметно сокращает время создания резервных копий в будние дни и объём занимаемого ими места на диске, поскольку каждая инкрементальная копия содержит только фактически изменившиеся данные, а не всю базу данных заново.

Восстановление из цепочки инкрементальных копий требует последовательного применения (prepare) каждой из них поверх полной резервной копии в правильном хронологическом порядке, начиная с самой полной и заканчивая нужной по времени инкрементальной, — пропуск или неверный порядок применения одной из копий в цепочке сделает итоговое состояние данных некорректным, поэтому автоматизация этого процесса скриптом, а не выполнение вручную под давлением времени во время реальной аварии, заметно снижает риск ошибки.

Автоматизация расписания резервного копирования

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

0 2 * * * /usr/bin/mysqldump --single-transaction -u backup_user -pSECRET mydatabase \
    | gzip > /var/backups/mysql/mydatabase-$(date +\%F).sql.gz \
    2>> /var/log/mysql-backup.log

Обратный слеш перед знаком процента в записи crontab (\%F) обязателен — без него cron интерпретирует символ процента особым образом как разделитель строк внутри самой записи задания, что сломает подстановку даты в имя файла резервной копии, — эта деталь регулярно становится источником файлов бэкапа с буквально неправильным именем вместо ожидаемой подставленной даты.

Хранение паролей доступа прямо в строке crontab, как в примере выше, — практика, допустимая разве что для быстрой демонстрации: в реальном продакшне пароль для пользователя резервного копирования стоит хранить в отдельном защищённом конфигурационном файле ~/.my.cnf с правами доступа 600, читаемом только владельцем, и не указывать его открытым текстом ни в самом скрипте, ни в записи планировщика.

Проверка того, что бэкап действительно восстанавливается

Резервная копия без подтверждённой возможности восстановления — это файл, а не защита данных. Регулярное тестовое восстановление на отдельном сервере, не связанном с продакшном, — единственный надёжный способ убедиться, что созданные дампы и физические копии действительно рабочие, а не просто присутствуют в каталоге бэкапов.

gunzip -c mydatabase-2026-11-03.sql.gz | mysql -u root -p test_restore_db
mysql -u root -p test_restore_db -e "SELECT COUNT(*) FROM orders;"

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

Хранение резервных копий вне сервера

Резервная копия, лежащая на том же сервере, что и исходная база данных, защищает только от логической ошибки вроде случайно выполненного DROP TABLE, но не от отказа диска, аварии сервера целиком или компрометации сервера злоумышленником, способным удалить и сами данные, и лежащие рядом с ними резервные копии.

aws s3 cp mydatabase-2026-11-03.sql.gz s3://backups-bucket/mysql/ \
    --endpoint-url https://s3.example.com --storage-class STANDARD_IA

Автоматическая выгрузка каждой свежесозданной резервной копии в отдельное, географически удалённое от основного сервера хранилище — обязательный, а не опциональный элемент надёжной схемы резервного копирования баз данных. Дополнительно стоит настроить политику ротации, автоматически удаляющую резервные копии старше определённого срока хранения, чтобы объём занимаемого места и, соответственно, стоимость хранения не росли неограниченно.

Шифрование резервных копий и права доступа к ним

Резервная копия базы данных обычно содержит полный слепок всех чувствительных данных проекта — от адресов электронной почты до платёжной информации, если она хранится в самой базе, — и потому требует не менее внимательного отношения к безопасности, чем сама рабочая база данных. Файл дампа с правами доступа 644, читаемыми любым локальным пользователем сервера, — распространённая и опасная небрежность.

mysqldump --single-transaction -u backup_user -p mydatabase \
    | gzip \
    | gpg --symmetric --cipher-algo AES256 --output mydatabase-$(date +%F).sql.gz.gpg
chmod 600 mydatabase-*.sql.gz.gpg

Шифрование резервной копии через gpg с симметричным алгоритмом сразу в конвейере создания дампа означает, что незашифрованная версия данных ни на одном этапе не попадает на диск в открытом виде — это особенно важно при последующей выгрузке резервной копии в удалённое хранилище, доступ к которому потенциально шире, чем доступ к самому продакшн-серверу. Пароль или ключ для расшифровки стоит хранить отдельно от самих резервных копий, следуя тому же принципу разделения данных и ключей доступа, что разбирался в статье про план восстановления после сбоя.

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

Итоги: что выбрать для конкретной базы данных

Для небольших и средних баз данных, где создание дампа занимает разумное время и не создаёт заметной нагрузки на продакшн-сервер, mysqldump остаётся простым, универсальным и абсолютно достаточным решением — его дампы к тому же легко читать, редактировать текстовым способом при необходимости и переносить между разными версиями и даже разными СУБД, совместимыми с диалектом SQL MySQL.

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

Независимо от выбранного инструмента, две практики остаются неизменно обязательными: резервные копии должны регулярно выгружаться на хранилище, физически отделённое от исходного сервера, и должны периодически проходить тестовое восстановление — без выполнения этих двух условий даже самый технически совершенный инструмент резервного копирования не гарантирует реальной защиты данных.