Бэкап и восстановление через mysqldump: флаги, большие дампы, ошибки

Для базы данных MySQL размером до нескольких десятков гигабайт mysqldump - правильный инструмент бэкапа, и одна команда делает всё как надо:

$ mysqldump -h db.example.net -P 30412 -u backup -p \    --single-transaction --routines --events --triggers \    --set-gtid-purged=OFF --no-tablespaces \    appdb | gzip > appdb-$(date +%F).sql.gz

--single-transaction даёт согласованный снимок всех таблиц InnoDB, не блокируя приложение. --routines и --events включают хранимые процедуры и запланированные события, которые по умолчанию не попадают в дамп. --set-gtid-purged=OFF и --no-tablespaces устраняют две ошибки, которые чаще всего ломают дампы, снятые обычным пользователем. Восстановление - это gunzip -c file.sql.gz | mysql appdb. Остальная часть руководства объясняет каждый флаг, как ускорить большие дампы и импорт и как читать ошибки, - потому что дамп становится бэкапом только после того, как вы его восстановили.

Всё здесь относится к MySQL 8.4 LTS и его mysqldump. mysqlpump, параллельный инструмент, появившийся в 5.7, в 8.4 удалили; его работу теперь выполняют утилиты дампа MySQL Shell, о которых ниже.

Что создаёт mysqldump#

mysqldump делает логический бэкап: он читает данные обычными запросами и пишет SQL-операторы, которые их воссоздают, - CREATE TABLE, затем многострочные INSERT, затем индексы и триггеры. На выходе текстовый файл, который можно читать, искать по нему grep, редактировать и загружать в любой совместимый сервер, в том числе более новой версии или на другом хосте.

Расплата - скорость. Дамп 10 ГБ данных пишется быстро, но загрузка обратно означает выполнение каждого INSERT и перестройку каждого индекса, и это может занять в несколько раз больше времени, чем сам дамп. Если база данных настолько велика, что восстановление занимает часы, вам нужно знать это до того дня, когда оно понадобится.

Флаги, которые важны#

mysqldump по умолчанию включает --opt - набор разумного поведения: --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert (много строк на один INSERT), --lock-tables, --quick (передавать строки потоком, а не буферизовать всю таблицу) и --set-charset. Вы добавляете к этому, а не собираете с нуля.

Зачем --single-transaction и где его пределы

Без него --lock-tables блокирует таблицы каждой базы данных на чтение, пока они выгружаются, и приложение всё это время не может писать. С --single-transaction mysqldump начинает транзакцию с согласованным снимком и читает всё на этот момент времени, а приложение продолжает писать. На InnoDB, а именно на нём должны быть все ваши таблицы, это согласованно и не блокирует.

У него два предела, о которых стоит знать. Он покрывает только транзакционные таблицы: таблица MyISAM в том же дампе читается в тот момент, когда до неё доходит очередь. И он не защищает от изменений схемы - ALTER TABLE, RENAME TABLE, TRUNCATE или DROP на таблице во время дампа могут привести к тому, что данных этой таблицы в выводе не будет или они будут несогласованными. Не запускайте миграции, пока идёт бэкап, и разносите их по расписанию.

Долгий дамп держит открытым старый снимок, поэтому InnoDB приходится хранить для него старые версии строк. На загруженной базе многочасовой дамп заставляет undo log расти, а purge - отставать. Ночью это обычно терпимо; но это причина не выгружать большую загруженную базу каждый час.

Процедуры, события и триггеры

Значения по умолчанию непоследовательны и подводят людей: триггеры включены, процедуры и события - нет. Приложение, которое зависит от хранимой процедуры, восстановленное из дампа без --routines, падает с «PROCEDURE appdb.close_month does not exist» через несколько недель, когда процедуру впервые вызывают. Добавляйте -R -E к каждому полному бэкапу.

Каждое представление, процедура, триггер и событие содержит предложение DEFINER. Восстановление от имени пользователя, который не является definer и у которого нет SET_ANY_DEFINER (8.4) или SUPER (в старых версиях), завершается ошибкой. Подробнее - в разделе об ошибках ниже.

Какие привилегии нужны пользователю для бэкапа#

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

CREATE USER 'backup'@'%' IDENTIFIED BY RANDOM PASSWORD;GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON appdb.* TO 'backup'@'%';

SELECT читает строки. Для дампа хранимых процедур через --routines нужно больше: либо глобальная привилегия SELECT, либо SHOW_ROUTINE - динамическая привилегия, добавленная в 8.0.20, - если только учётная запись бэкапа сама не является definer процедур; GRANT SHOW_ROUTINE ON *.* TO 'backup'@'%' - узкий вариант. SHOW VIEW выгружает определения представлений, TRIGGER - триггеры, EVENT - события, а LOCK TABLES нужна для нетранзакционных таблиц. Дамп без --no-tablespaces требует глобальной привилегии PROCESS, а --source-data требует RELOAD и REPLICATION CLIENT. Как создавать такие учётные записи, описано в статье пользователи и привилегии MySQL.

Восстановление дампа#

# Into an existing, empty database$ mysql -h db.example.net -P 30412 -u app -p appdb < appdb-2026-10-08.sql# From a compressed file, with a progress bar (pv is optional)$ pv appdb-2026-10-08.sql.gz | gunzip | mysql -h db.example.net -P 30412 -u app -p appdb# A dump made with --databases names its own database; give mysql no database$ mysql -h db.example.net -P 30412 -u root -p < all-databases.sql

Изнутри клиента SOURCE /path/to/appdb.sql делает то же самое с выводом по каждому оператору. В Windows PowerShell перенаправления < нет; используйте Get-Content dump.sql | mysql ... для небольших файлов или, лучше, mysql ... -e "source C:/backups/appdb.sql", потому что конвейер PowerShell может перекодировать текст.

Дамп без --add-drop-table упадёт на первой же существующей таблице, а дамп с ним (по умолчанию) удаляет и пересоздаёт каждую содержащуюся в нём таблицу, не трогая таблицы, которые в дампе не упомянуты. Поэтому восстановление «поверх» работающей базы даёт смесь: таблицы из дампа плюс все таблицы, созданные после него. Для чистого восстановления сначала удалите и пересоздайте базу данных либо восстановите в новую базу и переключите на неё приложение.

Восстановление одной таблицы

Поскольку дамп - это текст, одну таблицу можно восстановить, не загружая остальное. Раздел каждой таблицы начинается с комментария -- Table structure for table, за которым идёт её имя:

$ zcat appdb.sql.gz | sed -n '/^-- Table structure for table `orders`/,/^-- Table structure for table/p' \    > orders.sql

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

Большие дампы: сжатие, время и MySQL Shell#

Текстовый файл mysqldump сжимается чрезвычайно хорошо, часто до десятой части размера. gzip есть везде; zstd быстрее при похожей степени сжатия, а zstd -T0 использует все ядра:

$ mysqldump ... appdb | zstd -T0 -3 > appdb.sql.zst$ zstd -dc appdb.sql.zst | mysql ... appdb

Передавайте вывод сразу в компрессор, а не пишите сначала несжатый файл - и ради места на диске, и потому что запись на диск часто оказывается узким местом. При дампе через интернет --compression-algorithms=zstd сжимает ещё и протокол клиент-сервер, что помогает на медленном канале.

mysqldump однопоточный в обе стороны. Начиная примерно с 20-50 ГБ или когда восстановление должно уложиться в окно обслуживания, лучше подходят утилиты дампа MySQL Shell. Они выгружают таблицы параллельными порциями в каталог сжатых файлов и загружают их обратно тоже параллельно:

$ mysqlsh app@db.example.net:30412 -- util dump-schemas appdb \    --outputUrl=/backups/appdb-2026-10-08 --threads=4$ mysqlsh root@new-host:3306 -- util load-dump /backups/appdb-2026-10-08 --threads=4

util.loadDump требует local_infile=ON на целевом сервере, потому что загружает данные через LOAD DATA LOCAL INFILE. Для изменения этой настройки нужны глобальные привилегии, поэтому на хостинговом сервере проверьте её через SELECT @@local_infile;, прежде чем на неё полагаться. Дампы Shell к тому же проверяют совместимость и умеют удалять definer и исправлять другие проблемы при переезде на сервер, где вы не суперпользователь, - это опции ocimds и compatibility.

Как ускорить восстановление#

Файл дампа уже отключает самые дешёвые проверки в самом начале: он задаёт UNIQUE_CHECKS=0 и FOREIGN_KEY_CHECKS=0 для сессии и оборачивает строки каждой таблицы в ALTER TABLE ... DISABLE KEYS (что влияет только на MyISAM). Остаётся стоимость коммита и журналирования каждой строки. Если у вас есть учётная запись root, помогают три вещи:

-- Before the import, on the target serverSET GLOBAL innodb_flush_log_at_trx_commit = 2;   -- flush the redo log once a secondSET GLOBAL sync_binlog = 0;                      -- let the OS flush the binary log-- After the import, put them backSET GLOBAL innodb_flush_log_at_trx_commit = 1;SET GLOBAL sync_binlog = 1;

Обе меняют надёжность на скорость - сбой во время импорта потеряет примерно последнюю секунду, - и это нормально для восстановления, которое можно перезапустить с начала. Повышение innodb_buffer_pool_size и innodb_redo_log_capacity на время загрузки тоже помогает большому импорту; в 8.x обе настройки динамические. Настройка, полностью отключающая redo log, ALTER INSTANCE DISABLE INNODB REDO_LOG, делает загрузку в свежий экземпляр намного быстрее и оставляет весь экземпляр невосстановимым, если он упадёт до того, как вы её снова включите. Используйте её только на новом сервере, который можно пересобрать, и никогда - на сервере с другими данными.

Дампы по расписанию и хранение копий#

Дамп, который никто не восстанавливал, - это гипотеза. Дамп, лежащий на том же диске, что и база данных, - это гипотеза с единой точкой отказа. Рабочий порядок для небольшой базы данных:

#!/bin/bashset -euo pipefailSTAMP=$(date +%F-%H%M)DEST=/backups/mysqlmysqldump --login-path=backup --single-transaction -R -E --triggers \  --set-gtid-purged=OFF --no-tablespaces appdb | zstd -q -T0 > "$DEST/appdb-$STAMP.sql.zst"# keep 14 daysfind "$DEST" -name 'appdb-*.sql.zst' -mtime +14 -delete

--login-path читает учётные данные из ~/.mylogin.cnf, один раз созданного через mysql_config_editor, так что пароля нет ни в скрипте, ни в списке процессов. set -euo pipefail останавливает скрипт, если mysqldump упал. Часть pipefail важна: без неё код выхода конвейера - это код компрессора, поэтому дамп, умерший на полпути, всё равно даёт небольшой сжатый файл правдоподобного вида, и вы узнаёте об этом при восстановлении. Сравнивайте размер самого нового файла со вчерашним в качестве дешёвой сигнализации и копируйте файлы куда-нибудь ещё: статья дампы баз данных в S3 по расписанию показывает загрузку и хранение.

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

Восстановление на момент времени через двоичный журнал#

Ночной дамп означает, что ошибка в 17:00 стоит вам всего, что произошло с прошлой ночи. Двоичный журнал закрывает этот разрыв: он записывает каждое изменение после дампа, и mysqlbinlog может воспроизвести их вплоть до момента перед ошибкой. Для этого нужны три вещи - включённое двоичное журналирование (по умолчанию в MySQL 8), дамп, снятый с --source-data=2, чтобы в нём были записаны соответствующие файл журнала и позиция, и файлы двоичного журнала, хранящиеся как минимум столько же, сколько длится промежуток между дампами.

# The dump's header names its starting point$ zgrep -m1 'CHANGE REPLICATION SOURCE TO' appdb.sql.gz-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000042', SOURCE_LOG_POS=157;# Restore the dump, then replay changes up to just before the bad statement$ mysqlbinlog --start-position=157 --stop-datetime="2026-10-08 16:59:00" \    binlog.000042 binlog.000043 | mysql -u root -p

На практике для этого нужны учётная запись root, RELOAD и REPLICATION CLIENT для дампа и доступ к файлам двоичного журнала, который хостинговый сервер может дать, а может и не дать. Если не даёт, реалистичная альтернатива - более частые дампы тех таблиц, которые меняются сильнее всего. В любом случае выясните, что у вас есть, до инцидента, а не во время него.

Ошибки и что делать с каждой#

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)for this operation' when trying to dump tablespaces

Начиная с 8.0.21 дамп информации о табличных пространствах требует глобальной PROCESS. Добавьте --no-tablespaces; операторы табличных пространств в дампе приложения вам почти наверняка не нужны.

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER,SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

При восстановлении это обычно строки SET @@GLOBAL.GTID_PURGED или SET @@SESSION.SQL_LOG_BIN= 0, которые есть в дампе с сервера с включёнными GTID. Снимите дамп заново с --set-gtid-purged=OFF или удалите эти строки из начала файла.

ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist

В дампе есть представления или процедуры, чей definer отсутствует на новом сервере, или вы восстанавливаете от имени пользователя, который не может назначить такого definer. Удалите definer перед загрузкой:

$ zcat appdb.sql.gz | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | mysql ... appdb

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ...)':Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

mysqldump версии 8.x обращается к более старому серверу или серверу не от Oracle, в котором нет таблицы COLUMN_STATISTICS. Добавьте --column-statistics=0.

ERROR 2006 (HY000) at line 4127: MySQL server has gone awayERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

Один INSERT в дампе больше, чем принимает сервер. Повысьте max_allowed_packet на сервере (в 8.x по умолчанию 64 МБ, максимум 1 ГБ) и передайте --max-allowed-packet=512M в mysql либо снимите дамп заново с уменьшенным --net-buffer-length, чтобы расширенные вставки были меньше.

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Дамп из MySQL 8 загружается в MySQL 5.7 или сервер другого семейства, где нет правила сравнения по умолчанию из 8.0. Замените его в файле на utf8mb4_unicode_ci или, лучше, восстанавливайте в MySQL 8. Смотрите utf8mb4 и правила сравнения.

FAQ#

Блокирует ли mysqldump базу данных?

С --single-transaction и таблицами InnoDB - нет: чтение и запись продолжаются всё время, а дамп видит согласованный снимок. Без него --lock-tables по умолчанию блокирует запись в каждую базу данных, пока она выгружается. Изменения схемы во время дампа в любом случае могут нарушить согласованность.

Сколько времени занимает восстановление файла mysqldump?

Больше, чем дамп, часто в три-пять раз, потому что каждый индекс приходится перестраивать строка за строкой. Измерьте это один раз на реальном файле на временном сервере. Если ответ дольше, чем вы можете простаивать, переходите на параллельные дамп и загрузку MySQL Shell или на физические бэкапы.

Можно ли восстановить дамп MySQL 8.0 в MySQL 8.4?

Да. Логические дампы почти всегда без проблем загружаются в ту же или более новую версию. Обратное направление - дамп 8.4 в 8.0 или любой дамп 8.x в 5.7 - может упасть на правилах сравнения и синтаксисе, которых старый сервер не знает.

Экспорт phpMyAdmin - это то же самое, что mysqldump?

Для небольших баз данных - достаточно близко: SQL-экспорт phpMyAdmin создаёт похожие операторы. Но он выполняется внутри веб-запроса, поэтому большие экспорты упираются в лимиты времени и памяти PHP, а процедуры и события в его опциях легко забыть. Его настройки разобраны в статье импорт и экспорт в phpMyAdmin.

Как сделать бэкап всех баз данных на сервере?

mysqldump --all-databases включает системную схему mysql, а это не лучший способ переносить учётные записи между серверами 8.x. Выгружайте базы данных приложений через --databases db1 db2, а пользователей копируйте отдельно через SHOW CREATE USER и SHOW GRANTS. Полная процедура - в статье перенос MySQL на новый хост.