Как сделать бэкап и восстановить базу данных MySQL
Практика3 минРедакция Innovialabs
Коротко
Для большинства баз достаточно mysqldump с флагом --single-transaction, запуска по расписанию и копии дампа на другом сервере; для больших баз и быстрого восстановления используйте Percona XtraBackup.
Короткий ответ
Логический бэкап MySQL делается одной командой:
mysqldump --single-transaction --routines --triggers --events \
-u backup -p shop > shop_$(date +%F).sql
Восстановление — тоже одной:
mysql -u root -p shop < shop_2026-10-10.sql
Но бэкап считается настоящим, только если он делается автоматически, хранится не на том же сервере и регулярно проверяется восстановлением.
Почему важен --single-transaction
Без этого флага mysqldump читает таблицы по очереди, и пока он работает, приложение продолжает писать данные. В итоге заказ может попасть в дамп, а его позиции — нет.
Флаг --single-transaction открывает транзакцию с уровнем изоляции REPEATABLE READ, и все таблицы читаются как единый снимок на один момент времени. При этом таблицы не блокируются, сайт продолжает работать.
Важные оговорки:
- Это работает только для InnoDB. Таблицы MyISAM в такой снимок не попадают консистентно.
- Во время дампа нельзя выполнять DDL (
ALTER TABLE,TRUNCATE) — снимок может сломаться. - Для всех баз сразу используйте
--all-databases, для нескольких —--databases db1 db2.
Полезные флаги:
| Флаг | Зачем |
|---|---|
--routines | хранимые процедуры и функции |
--triggers | триггеры (обычно включены по умолчанию) |
--events | события планировщика |
--source-data=2 или --master-data=2 | позиция binlog в комментарии, нужна для point-in-time recovery |
--hex-blob | безопасный экспорт бинарных колонок |
Как восстановить дамп
- Создайте пустую базу:
CREATE DATABASE shop_restore; - Загрузите дамп:
mysql -u root -p shop_restore < shop.sql - Проверьте количество строк в ключевых таблицах и запустите приложение на копии.
Для сжатого дампа: gunzip < shop.sql.gz | mysql -u root -p shop_restore.
Восстанавливайте сначала в отдельную базу, а не поверх рабочей. Так вы сможете сравнить данные и не потерять то, что ещё живо.
Бэкап по расписанию
Создайте отдельного пользователя с минимальными правами (SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS) и храните пароль в файле ~/.my.cnf с правами 600, а не в командной строке.
Пример задачи cron на ежедневный бэкап в 03:00 со сжатием:
0 3 * * * mysqldump --single-transaction --routines --events shop | gzip > /backup/shop_$(date +\%F).sql.gz
Добавьте очистку старых файлов, например через find /backup -name '*.sql.gz' -mtime +14 -delete, и уведомление, если команда завершилась с ошибкой.
Хранение вне сервера
Бэкап на том же диске не спасёт от отказа диска, взлома или удаления сервера. Работает правило 3-2-1: три копии, на двух разных носителях, одна — в другом месте.
- Отправляйте дампы в объектное хранилище (S3-совместимое) через
rcloneилиaws s3 cp. - Включите версионирование или неизменяемость (object lock), чтобы злоумышленник не мог удалить копии.
- Шифруйте дампы перед отправкой, если в них персональные данные.
Когда нужен Percona XtraBackup
mysqldump создаёт SQL-файл, который при восстановлении выполняется построчно. На больших базах это долго: индексы перестраиваются заново.
Percona XtraBackup делает физический бэкап — копирует файлы данных InnoDB без остановки сервера. Выбирайте его, если:
- база большая и восстановление дампа занимает неприемлемо много времени;
- нужны инкрементальные бэкапы;
- нужно быстро поднять новую реплику.
Ограничения: версия XtraBackup должна соответствовать версии MySQL, а восстановить физическую копию можно только на совместимую версию сервера. Для переноса между версиями и выборочного восстановления таблиц mysqldump удобнее.
Частые ошибки
- Нет
--single-transaction— неконсистентные данные. - Пароль в командной строке виден в списке процессов.
- Бэкапы никто не проверял, и при аварии дамп оказывается пустым или обрезанным.
- Не включён binary log — можно восстановиться только на момент бэкапа, а не на минуту до сбоя.