mysqldump без --single-transaction: бэкап, который не согласован

MAATRIX

Бэкап снялся без ошибок, файл дампа лежит на месте, размер разумный — и всё равно после восстановления данные не сходятся: заказ есть, а строки в связанной таблице остаток товара нет, или сумма в отчёте не бьётся с историей операций. Проблема почти всегда одна: дамп сняли без --single-transaction, и во время выгрузки в базу продолжала идти запись. Разберём, что именно ломается и почему один флаг решает вопрос — но не для всех таблиц.

Что делает mysqldump без флага

По умолчанию mysqldump не работает как единая транзакция. Он идёт по таблицам последовательно и выгружает их одну за другой, ставя блокировки по своему усмотрению (в зависимости от версии и опций — это может быть FLUSH TABLES WITH READ LOCK или блокировки на уровне отдельных таблиц). Пока снимается таблица orders, в неё могут прилетать новые записи уже после того, как чтение началось, а к моменту, когда очередь доходит до таблицы order_items, база успевает уйти вперёд.

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

Если хочется разобраться в целом, какие ошибки чаще всего портят бэкапы MySQL, см. отдельный разбор: бэкап MySQL на сервере — частые ошибки и решения.

Проверить, что дамп снимался без согласованности, просто — посмотрите в начало файла:

head -50 dump.sql | grep -i "lock tables\|single transaction\|SET autocommit"

Если в дампе нет строки про START TRANSACTION в начале секции с данными, а есть LOCK TABLES ... WRITE, значит выгрузка шла через блокировки, а не через транзакционный снимок.

Как работает --single-transaction

Флаг --single-transaction заставляет mysqldump перед началом выгрузки открыть одну транзакцию с уровнем изоляции REPEATABLE READ (это уровень изоляции InnoDB по умолчанию) и снимать все таблицы в её рамках:

mysqldump -u root -p --single-transaction --routines --triggers --events appdb > dump.sql

Механизм простой и при этом надёжный: в начале транзакции InnoDB фиксирует consistent read view — согласованный взгляд на данные на тот момент. Все последующие чтения внутри этой транзакции видят базу такой, какой она была в момент старта, независимо от того, сколько новых строк добавится или изменится параллельно. Это тот же механизм MVCC (multi-version concurrency control), на котором строится обычная параллельная работа InnoDB — читающие транзакции не мешают пишущим и наоборот.

Практически это значит: пока mysqldump десять минут выгружает большую базу, приложение продолжает как ни в чём не бывало писать в неё — вставлять заказы, обновлять остатки, менять статусы. Ни один INSERT, UPDATE или DELETE не блокируется дампом (кроме короткой начальной паузы на установку read view, которая на практике почти незаметна). А в файле дампа окажется база ровно в том состоянии, в котором она была в момент запуска команды — заказ и его позиции окажутся согласованы между собой, потому что оба читались через один и тот же снимок.

Ключевое отличие от подхода с блокировками: без --single-transaction согласованность (когда её вообще пытаются обеспечить) достигается тем, что пишущие операции ждут, пока не снимется дамп. С --single-transaction согласованность достигается тем, что дамп читает старую версию строк, а пишущие операции работают параллельно, не дожидаясь друг друга. Это классический компромисс MVCC: вместо блокировки — версионирование.

Когда это критично

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

• Интернет-магазины и биллинг. Заказ создаётся в одной таблице, позиции заказа — в другой, движение по балансу — в третьей. Если дамп идёт таблица за таблицей несколько минут, а покупки продолжаются, часть заказов попадёт в дамп без соответствующих позиций или наоборот.
• Очереди и статусы задач. Таблица jobs меняет статус pending → processing → done в реальном времени. Несогласованный дамп может зафиксировать задачу как processing, хотя связанный с ней результат уже записан в другую таблицу как done — после восстановления это выглядит как зависшая задача.
• Финансовые операции и счётчики. Таблица с текущим балансом и таблица с историей транзакций должны биться друг с другом. Рассинхронизация здесь не просто неудобство, а прямая потеря доверия к данным.
• Базы с внешними ключами между таблицами разного размера. Большая таблица снимается долго, маленькая — быстро. Чем больше разрыв по времени между снятием связанных таблиц, тем выше шанс поймать несоответствие.

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

Ограничение: только для транзакционных таблиц

Здесь важна честная оговорка, ради которой стоит читать статью до конца, а не бежать вставлять флаг в крон и считать вопрос закрытым. --single-transaction даёт согласованность только для таблиц с движком, поддерживающим транзакции — практически всегда речь об InnoDB. Механизм MVCC и consistent read view — это возможность движка хранения, а не самого mysqldump. Если движок не хранит версии строк и не умеет работать в транзакции, флагу попросту не с чем работать.

Таблицы MyISAM (или любой другой нетранзакционный движок, если он у вас остался) при --single-transaction всё равно снимаются несогласованно относительно момента запуска — точнее, относительно всех остальных таблиц, включая InnoDB. Для них mysqldump либо кратко блокирует таблицу на чтение прямо перед выгрузкой, либо — что хуже — не даёт вообще никакой гарантии, в зависимости от версии и опций. Смешанная база, где часть таблиц InnoDB, а часть MyISAM, — это гарантированный источник рассинхронизации между двумя группами таблиц, даже с включённым флагом.

Проверить движки всех таблиц базы одним запросом:

SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema = 'appdb' AND engine <> 'InnoDB';

Если запрос вернул строки — это таблицы, для которых --single-transaction не даёт гарантий. Практический выход один: перевести такие таблицы на InnoDB (в подавляющем большинстве случаев это оправдано и для MyISAM больше нет причин, ради которых стоит мириться с её ограничениями):

ALTER TABLE old_table ENGINE=InnoDB;

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

Практическая команда и что вокруг неё

Рабочий вызов для базы на InnoDB, который стоит взять за основу:

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=OFF \
  appdb > appdb_$(date +%F).sql

Пояснения по опциям, которые часто соседствуют с --single-transaction и о которых забывают:

Одна тонкость, о которую спотыкаются: --single-transaction не защищает от DDL-операций (ALTER TABLE, DROP TABLE) во время дампа. Если во время выгрузки кто-то меняет структуру таблицы, транзакция может завершиться с ошибкой или дамп получится неконсистентным по структуре. На проде такие операции во время бэкапа — редкость, но если у вас есть автоматические миграции по расписанию, разводите их по времени с окном бэкапа.

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

Как проверить, что дамп действительно согласован

Слепо доверять флагу не стоит — стоит проверять результат. Простой способ убедиться, что дамп снят корректно: запустить тестовую нагрузку записи параллельно с дампом на копии базы и сверить контрольные суммы связанных таблиц до и после.

Более прикладной вариант для регулярной проверки — сверка счётчиков после восстановления дампа в тестовую среду:

mysql -u root -p testdb -e "
SELECT
  (SELECT COUNT(*) FROM orders) AS orders_cnt,
  (SELECT COUNT(*) FROM order_items) AS items_cnt,
  (SELECT SUM(total) FROM orders) AS orders_sum;
"

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

Для баз, где логическую консистентность дампа хочется гарантировать ещё надёжнее (очень большие базы, строгие требования к RPO), рассмотрите физический бэкап через xtrabackup — он копирует файлы данных InnoDB напрямую и тоже обеспечивает согласованность без блокировки записи, но за счёт другого механизма и с другими компромиссами по скорости восстановления. Частые проблемы этого инструмента разобраны в статье Percona XtraBackup на сервере — частые ошибки и решения, а базовую настройку самого MySQL под бэкапы — в материале как установить и настроить бэкап MySQL на VPS.

Частые вопросы