Устранение распространенных проблем миграции MySQL и ошибок передачи данных

Миграции MySQL часто терпят неудачу по нескольким типичным причинам. Импорт останавливается на внешнем ключе. Символы превращаются в вопросительные знаки. Дамп из MySQL 5.7 не загружается корректно в MySQL 8.0. Данные загружаются, но приложение ломается, потому что хранимая процедура, триггер, пользователь или режим SQL не перенеслись так, как вы ожидали. Ни одна из этих проблем не является необычной, но с ними гораздо легче справиться, если рассматривать миграцию как повторяемый процесс, а не как одноразовое копирование.

Лучшая привычка при миграции — репетировать. Возьмите реальную резервную копию, восстановите ее на промежуточном сервере, выполните те же команды импорта, которые планируете использовать в продакшене, и запишите все предупреждения. Репетиция покажет, полный ли у вас дамп, совместима ли конфигурация целевого сервера и сколько времени на самом деле занимает загрузка. Она также даст вам план отката, который более реалистичен, чем «мы разберемся во время окна обслуживания».

Начните с определения типа сбоя

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

Совместимость: различия версий, зарезервированные слова, удаленные функции, измененные значения по умолчанию.
Кодировка: несоответствие кодировок и правил сортировки.
Ограничения: внешние ключи, уникальные ключи, проверочные ограничения, генерируемые столбцы.
Покрытие объектов: отсутствующие триггеры, процедуры, события, представления, пользователи или привилегии.
Производительность: импорт слишком медленный, диск заполнен, бинарные логи растут, индексы создаются слишком долго.
Поведение приложения: данные импортированы, но запросы или записи ведут себя иначе.

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

Несоответствие версий: MySQL 5.7 на 8.0 и аналогичные переходы

Обновления основных версий часто преподносят сюрпризы. MySQL 8.0 изменил значения по умолчанию, зарезервированные слова, поведение аутентификации, внутреннее устройство словаря данных и поведение оптимизатора по сравнению с 5.7. Некоторые старые синтаксисы все еще работают; некоторые — нет. MariaDB добавляет еще один уровень совместимости, поскольку она не является полной заменой для каждой функции MySQL.

Перед миграцией запишите настройки источника:

SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

На целевом сервере выполните те же проверки и сравните. sql_mode заслуживает особого внимания. Дамп, который загружается на снисходительном источнике, может завершиться ошибкой на более строгом целевом сервере с такими ошибками, как недопустимые даты, отсутствующие значения по умолчанию для столбцов NOT NULL или нулевые даты, которые больше не принимаются в целевом режиме.

Если вы столкнулись с ошибкой типа:

ERROR 1067 (42000): Invalid default value for 'created_at'

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

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

Проблемы с плагинами аутентификации

Миграция, включающая переключение приложения, часто завершается ошибкой до выполнения любого запроса, потому что клиенты не могут аутентифицироваться. MySQL 8.0 по умолчанию использует caching_sha2_password, в то время как старые клиенты могут ожидать mysql_native_password.

Проверьте пользователей на целевом сервере:

SELECT user, host, plugin FROM mysql.user;

Лучшим решением обычно является обновление клиентской библиотеки или драйвера. Если это невозможно до переключения, вам может понадобиться временная учетная запись совместимости:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';

Относитесь к этому как к решению о совместимости, а не как к общей передовой практике. Настройки аутентификации влияют на безопасность, и правильный ответ зависит от версий ваших клиентов и модели риска.

Проблемы с кодировкой и правилами сортировки

Проблемы с кодировкой болезненны, потому что импорт может успешно завершиться, в то время как данные уже повреждены. Классические симптомы: ?, кракозябры, сломанные символы с диакритическими знаками или неудачные вставки с эмодзи.

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

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';

Проверьте также столбцы:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
  AND character_set_name IS NOT NULL;

Для большинства современных приложений utf8mb4 является правильной целевой кодировкой, поскольку она поддерживает полный диапазон Unicode, включая эмодзи. Старое имя utf8 в MySQL не то же самое, что полный UTF-8 в старых версиях; это обычно 3-байтовая кодировка.

При дампе и импорте будьте явными:

mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql

Если исходные данные на самом деле latin1, не объявляйте их слепо как utf8mb4 и не надейтесь. Сначала определите, являются ли байты допустимыми в исходной кодировке. Некоторые старые системы содержат «дважды закодированные» данные, где столбец утверждает одну кодировку, но приложение сохранило байты из другой. Это требует проверенного преобразования, а не глобального поиска и замены.

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

Ошибки внешних ключей

Ошибки внешних ключей обычно означают одно из четырех:

Дочерние таблицы были импортированы до родительских.
Дамп неполный и отсутствуют ссылочные строки.
Исходные данные уже содержали несогласованные ссылки.
Целевая схема отличается от исходной.

Обычный обходной путь для массовой загрузки:

SET FOREIGN_KEY_CHECKS = 0;
-- импорт данных
SET FOREIGN_KEY_CHECKS = 1;

Это может быть уместно для полного логического восстановления из надежного дампа. Это не инструмент для очистки. Повторное включение FOREIGN_KEY_CHECKS не выполняет полную повторную проверку каждой существующей строки, как многие думают, поэтому вы можете импортировать плохие связи и не заметить этого до более позднего времени.

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

SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
  AND p.id IS NULL
LIMIT 20;

Используйте такие запросы для ваших реальных связей, особенно для важных таблиц, таких как заказы, платежи, учетные записи и разрешения.

Ошибки дублирования ключей

Ошибка дублирования ключа означает, что в целевом сервере уже есть значение, которое входящие данные пытаются вставить:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

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

Если вы объединяете данные, определите политику разрешения конфликтов до импорта. INSERT IGNORE скрывает дубликаты, пропуская строки. REPLACE INTO удаляет существующую строку и вставляет новую, что может вызвать каскадные изменения и изменить столбцы с автоматическим обновлением. ON DUPLICATE KEY UPDATE более явный, но все равно требует тщательных правил.

Для миграций я предпочитаю использовать промежуточные таблицы для слияния. Загрузите входящие данные в таблицы staging_*, проверьте конфликты, затем напишите продуманные операторы INSERT ... SELECT или UPDATE ... JOIN. Это медленнее в разработке, но позволяет избежать молчаливого выбрасывания данных.

Отсутствующие триггеры, процедуры, события и представления

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

mysqldump -u user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  appdb > appdb.sql

Представления и процедуры могут не импортироваться из-за учетных записей определителя (definer). Представление может ссылаться на:

DEFINER=`old_user`@`old_host`

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

После импорта сравните количество объектов:

SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;

SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;

Также проверьте запланированные события, если ваше приложение на них полагается:

SHOW EVENTS FROM appdb;

Медленный импорт и большие таблицы

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

iostat -xz 1
df -h
top

Для логических дампов используйте расширенные вставки. mysqldump делает это по умолчанию в большинстве случаев, но убедитесь, что вы не используете --skip-extended-insert, если вам не нужны читаемые человеком различия больше, чем скорость.

Для импорта InnoDB больший innodb_buffer_pool_size может помочь, если на целевом сервере есть доступная память. Не устанавливайте его настолько высоким, чтобы ОС начала подкачку. Во время одноразовой загрузки некоторые команды временно ослабляют настройки долговечности, такие как innodb_flush_log_at_trx_commit, или отключают бинарное логирование для сеанса импорта. Эти варианты обменивают возможность восстановления после сбоя или восстановления на определенный момент времени на скорость, поэтому их следует использовать только тогда, когда вы можете позволить себе перезапустить импорт из известной резервной копии.

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

Для очень больших таблиц рассмотрите инструменты физического резервного копирования или утилиты дампа и загрузки MySQL Shell вместо простого mysqldump. Логические дампы переносимы и легко проверяются, но они не всегда самый быстрый путь для наборов данных размером в несколько сотен гигабайт.

Ошибки дискового пространства

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

Проверьте перед импортом:

df -h
du -sh /var/lib/mysql

Внутри MySQL проверьте размеры таблиц:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;

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

Проверка после миграции

Миграция не завершена, когда команда импорта завершилась. Проверьте результат.

Начните с подсчета строк для важных таблиц:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;

Одних подсчетов строк недостаточно. Сравните суммы или проверьте критически важные для бизнеса данные:

SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;

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

Протестируйте рабочие процессы приложения на целевом сервере до переключения:

Вход в систему и создание сессии.
Создание и обновление основных записей.
Поиск и отчеты, которые зависят от правил сортировки или индексов.
Фоновые задания, триггеры и запланированные события.
Проверки разрешений и действия администратора.

Тест приложения важен, потому что база данных может быть технически импортирована, но поведенчески неверна.

Практический контрольный список для устранения неполадок миграции

Когда появляется ошибка миграции, используйте эту последовательность:

Сохраните точное сообщение об ошибке и, если возможно, неудачный оператор SQL.
Определите категорию: совместимость, кодировка, ограничение, покрытие объектов, производительность или поведение приложения.
Сравните версии MySQL источника и цели, sql_mode, кодировки и правила сортировки.
Для ошибок ограничений проверьте конкретные родительские и дочерние строки или дублирующиеся ключи.
Для проблем с кодировкой прекратите импорт, пока не узнаете, являются ли байты источника допустимыми и как клиентское соединение их интерпретирует.
Для медленного импорта проверьте диск, память, бинарные логи и обслуживание индексов, прежде чем менять случайные переменные.
После исправления повторно запустите миграцию на промежуточном сервере, прежде чем применять ее в продакшене.

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