Бэкап MySQL на сервере: частые ошибки и решения

MAATRIX

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

Дамп восстанавливается с ошибками

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

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

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

Архив бэкапа пустой или обрезанный

Файл дампа есть, но он подозрительно мал или при распаковке ругается на битый архив. Классическая причина — бэкап оборвался из-за нехватки места на диске или ошибки соединения, а скрипт этого не заметил и отчитался об успехе. Всегда проверяйте код возврата команды и размер файла в скрипте:

mysqldump -u root --single-transaction appdb | gzip > dump.sql.gz
if [ ${PIPESTATUS[0]} -ne 0 ]; then echo "BACKUP FAILED"; exit 1; fi

Проверка PIPESTATUS[0] ловит ошибку именно mysqldump, а не gzip в конце конвейера — это тонкость, о которую спотыкаются многие. Без неё скрипт видит успешный gzip и считает бэкап удачным, даже если дамп оборвался. Добавьте в скрипт проверку минимального размера итогового файла: если дамп внезапно стал в разы меньше обычного, это тревожный сигнал, и лучше поднять оповещение, чем молча перезаписать хорошую копию плохой.

mysqldump блокирует базу и подвешивает сайт

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

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

В восстановленной базе нет процедур или прав

База восстановилась, данные на месте, но приложение падает: не находит хранимую процедуру, триггер не срабатывает или пользователи базы отсутствуют. Причина в неполном дампе. mysqldump по умолчанию не включает хранимые процедуры и триггеры — их добавляют флаги --routines и --triggers. А пользователей и их права дамп одной базы вообще не содержит: они хранятся в системной базе mysql.

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

Кончилось место из-за бэкапов

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

find /var/backups/mysql -name "*.sql.gz" -mtime +14 -delete

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

Восстановление проваливается на кодировке

Дамп восстановился, но русский текст превратился в кракозябры. Причина — несовпадение кодировок при снятии или восстановлении дампа. Снимайте дамп с явным указанием кодировки, чтобы текст не искажался ещё на этапе выгрузки:

mysqldump -u root --single-transaction --default-character-set=utf8mb4 appdb > dump.sql

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

Обобщая всё сказанное: почти все ошибки бэкапа MySQL тихие, и объединяет их одно — их не замечают до момента восстановления. Поэтому единственная настоящая защита это регулярная проверка: разворачивайте копию на тестовой базе, сверяйте число строк, читайте текст, замеряйте время. Скрипт, который снимает дамп и молча кладёт файл на диск, даёт лишь иллюзию безопасности. Скрипт, который проверяет код возврата, размер, отправляет копию на другой сервер и раз в месяц тестово восстанавливается, — это уже реальная страховка, на которую можно положиться в час аварии. Поэтому кодировку задают правильно с самого начала, и это ещё один аргумент проверять восстановление заранее, а не в час аварии. Чтобы бэкапы было где надёжно хранить, удобно иметь второй сервер. В MAATRIX можно арендовать VPS под MySQL и резервное хранилище в России, США или Великобритании и оплатить картой РФ, по СБП, криптой или токеном MAAT — иностранная карта не требуется.

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