Резерв и восстановление MySQL с учётом умляутов

Введение

Эта статья основана на переводе и адаптации материала Даниэля Шличтгольца — разработчика MySQLDumper. Оригинал был на немецком; я перевёл и расширил текст, добавив практические инструкции, чек‑листы и пошаговые действия для разных ролей. Статья предназначена для администраторов, разработчиков и специалистов техподдержки, которые сталкиваются с проблемой неверного отображения умляутов (ä ö ü ß) и других специальных символов при резервном копировании и восстановлении баз данных.

Важно: если вы ищете «кнопку, после нажатия которой всё заработает», то этого тут нет. Решение требует диагностики и понимания того, как кодировки обрабатываются на каждом этапе.

Почему возникает проблема

Краткое определение: кодировка — способ представления символов в байтах. latin1 (ISO‑8859‑1) — однобайтовая кодировка, подходящая для западноевропейских языков. UTF‑8 — многобайтовая кодировка Unicode, которая может представлять символы всех языков.

Глобальная причина: разные версии/настройки MySQL и клиенты (скрипты, утилиты) могут использовать разные кодировки по умолчанию. Если кодировка, в которой написан дамп, не совпадает с той, которую ожидает сервер при импорте, байты будут интерпретированы неверно — появятся «вопросы» (?), квадраты или неправильные последовательности типа Ã¤Ã¶Ã¼ вместо äöü.

Ключевые моменты:

Ранние установки MySQL (например, MySQL 3.x) часто использовали latin1 по умолчанию. Многие скрипты тогда не указывали кодировку при подключении.
Начиная с MySQL 4.0/4.1 поддержка UTF‑8 стала более распространённой, и некоторые серверы начали использовать utf8 как стандарт. При этом старые скрипты продолжали ожидать latin1.
Если клиент посылает данные в latin1, а сервер думает, что получает utf8 (или наоборот), байты сохранятся и прочитаются с неверной интерпретацией.
Текстовый файл дампа сам по себе обычно не несёт надёжной метки о кодировке (BOM для UTF‑8 может присутствовать, но PHP/веб‑серверы обычно его не используют), поэтому важно явно указывать кодировку в дампе или при соединении.

Практические сценарии (с примерами)

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

Сценарий A — всё совпадает (работает):

Источник: MySQL 3.x, данные в latin1.
Дамп создан скриптом, который не указывает кодировку — фактически он получил данные в latin1 и записал байты в файл.
При восстановлении MySQLDumper отправляет байты в том виде, в котором они есть (latin1), и сервер настроен по‑умолчанию на latin1. Сервер видит «я получил latin1», конвертирует внутрь (если нужно) и сохраняет правильно. Результат: корректные умляуты.

Сценарий B — сервер ожидает utf8, дамп в latin1 (неправильно отображается):

Источник: MySQL 3.x, дамп в latin1.
При восстановлении MySQLDumper посылает данные как есть (latin1), но сервер по умолчанию настроен на utf8 и думает, что получает utf8. Сервер не конвертирует и сохраняет байты latin1 как utf8. При выводе браузер/приложение пытается декодировать эти байты как utf8 — и получаются вопросительные знаки, квадраты или неверные символы.

Сценарий C — дамп в utf8, сервер ожидает latin1 (появляются сочетания вроде Ã¤Ã¶Ã¼):

Источник: сервер с utf8 по умолчанию, дамп вышел в utf8 (так как сервер отдал данные в utf8).
При восстановлении MySQLDumper отправляет utf8‑байты, но целевой сервер ожидает latin1 и сохраняет байты как latin1. При последующем чтении эти utf8‑байты интерпретируются как последовательности одиночных latin1‑символов, что даёт «мозаичный» вид: например utf8‑последовательность C3 A4 (для ä) прочитается как символы Ã и ¤, что видно как Ã¤. Это именно ситуация, когда один «символ» превращается в две или более странных последовательностей.

Примечание: для случаев вроде сценария C автор разработал утилиту‑корректор — см. ссылку: http://www.mysqldumper.de/board/viewtopic.php?p=19187#19187

Как диагностировать проблему (короткий чек‑лист)

Узнайте, в какой кодировке данные фактически находятся в дампе.
Посмотрите настройки исходного MySQL (character_set_server, character_set_database, character_set_client, character_set_results, character_set_connection).
Посмотрите настройки целевого MySQL (те же переменные).
Определите, какой клиент/скрипт вы используете для импорта и какую кодировку он передаёт (SET NAMES, параметр –default-character-set для mysql/mysqldump).
Протестируйте небольшой фрагмент: вставьте известную строку с умляутами и посмотрите, что записалось.

Команды для диагностики (выполняются в mysql клиенте):

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

Проверка байтовой последовательности символа «ä» в разных кодировках (пример):

latin1: байт E4 (hex E4)
utf8: последовательность C3 A4 (hex C3 A4)

Вы можете использовать hexdump/xxd или команду od для проверки файла дампа:

xxd dump.sql | head

Или команду iconv для тестовой перекодировки:

iconv -f latin1 -t utf8 dump.sql > dump_utf8.sql

Если iconv падает с ошибкой или даёт неожиданный результат — это подсказка, что исходная кодировка неверно определена.

Практическое руководство: безопасный порядок действий для резервного копирования и восстановления

Ниже — пошаговый SOP для случаев миграции/восстановления баз, в котором явно указываются кодировки.

Шаг 0 — подготовка ролей (кто что делает):

Разработчик/скрипт: гарантирует, что перед подключением выполняется SET NAMES или используется API/драйвер, поддерживающий установку кодировки.
Администратор БД: проверяет переменные character_set на исходном и целевом серверах.
Оператор восстановления: проверяет дамп и при необходимости конвертирует.

Шаг 1 — выясните состояние исходного сервера

Подключитесь к исходному серверу и выполните:

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

Запишите значения: character_set_server, character_set_database, character_set_client, character_set_results, character_set_connection.

Шаг 2 — создайте дамп с явной кодировкой

Если используете mysqldump, явно укажите кодировку:

mysqldump --default-character-set=latin1 -u user -p dbname > dump_latin1.sql
mysqldump --default-character-set=utf8 -u user -p dbname > dump_utf8.sql

Если используете MySQLDumper, проверьте версию. Старые версии могли не отправлять SET NAMES; в новых версиях должно быть соответствующее поле конфигурации или явное выполнение SET NAMES. Если MySQLDumper не позволяет задать кодировку, сохраните дамп и вручную добавьте строку с SET NAMES, см. ниже.

Шаг 3 — проверьте файл дампа

Откройте первые строки дампа. Часто полезно добавить вручную в начало файла дампа такие строки:

/*!40101 SET NAMES 'utf8' */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Или для latin1:

/*!40101 SET NAMES 'latin1' */;

Если дамп содержит CREATE TABLE … DEFAULT CHARSET=utf8 или указание COLLATE — это ясный маркер используемой кодировки в структуре таблиц. Но это не всегда означает, что данные внутри идут в той же кодировке, особенно если дамп создавался скриптом, который не выставлял SET NAMES.

Шаг 4 — импорт на целевой сервер с явной кодировкой

Используйте mysql клиент и укажите кодировку:

mysql --default-character-set=utf8 -u user -p dbname < dump_utf8.sql

или

mysql --default-character-set=latin1 -u user -p dbname < dump_latin1.sql

Если импортируете через MySQLDumper, убедитесь, что перед импортом выполняется SET NAMES ‘…’; если нет — вставьте её в файл дампа в начало.

Шаг 5 — проверка после импорта

Выполните SELECT с известной строкой и посмотрите на HEX():

SELECT sample_column, HEX(sample_column) FROM table WHERE id = 1;

Проверьте отображение в целевом приложении/браузере с заданным заголовком Content‑Type: text/html; charset=utf-8 (или latin1 — в зависимости от вашей архитектуры).

Шаг 6 — исправление уже испорченных данных

Если данные уже сохранены неправильно, возможные подходы:
- Если данные в базе сохранены как «латинные» байты, но должны были быть utf8, можно выполнить клиентскую конвертацию и UPDATE таблиц, либо сохранить дамп и перекодировать с помощью iconv, затем снова импортировать с правильными параметрами.
- Иногда проще экспортировать данные в бинарном виде, переконвертировать файл и заново импортировать в новую базу со строгой установкой кодировок.

Пример перекодировки файла дампа:

iconv -f latin1 -t utf8 dump.sql > dump_utf8.sql
mysql --default-character-set=utf8 -u user -p dbname < dump_utf8.sql

Если вы видите последовательности типа Ã¤, значит где‑то данные закодированы в utf8, а читаются как latin1, или наоборот. Правильная последовательность действий — понять, какие байты в файле и какую кодировку сервер ожидает.

Дополнительные подходы и советы

Всегда по возможности используйте UTF‑8 для новых проектов. Это предотвращает многие межъязыковые проблемы.
Явно указывайте кодировку на всех уровнях: HTML/CSS/HTTP заголовки, настройки приложения, соединение с БД (SET NAMES или эквивалент драйвера), параметры mysqldump/mysql.
Не полагайтесь на «дефолты» сервера — они могут отличаться между хостингами и версиями MySQL.
При миграции делайте тестовый импорт небольшой выборки данных и проверяйте корректность отображения.

Чек‑лист: перед экспортом и после импорта

Чек‑лист для оператора:

Узнал character_set на исходном сервере.
Создал дамп с указанием –default-character-set или добавил SET NAMES в начало дампа.
Проверил содержимое дампа (CREATE TABLE … DEFAULT CHARSET и первые INSERT‑ы в hex).
Перед импортом задал –default-character-set для mysql клиентa или убедился, что MySQLDumper выполняет SET NAMES.
После импорта проверил отображение данных в приложении.
Если есть проблемы, сохранил дамп и сделал бэкап базы перед попыткой исправления.

Чек‑лист для разработчика:

При подключении к БД приложение явно устанавливает кодировку (SET NAMES или соответствующая опция драйвера).
Заголовки HTTP/HTML/Meta правильно указывают charset.
Локальные тесты с разными языками пройдены.

Чек‑лист для хостинг‑админа:

Убедился в системных настройках MySQL (my.cnf): character_set_server, collation_server.
Документировал дефолтные значения для пользователей.
Предоставил инструкции по экспорт/импорту для общих случаев.

Сценарии, когда простая перекодировка не поможет (контрпримеры)

Дамп был создан программой, которая при записи в файл модифицировала байты (например, некорректная фильтрация), и исходные байты утеряны.
Данные были многократно «перекодированы» неправильно (utf8→latin1→utf8), и одна простая конвертация не вернёт исходный текст. В таких случаях нужно анализировать каждый столбец и выполнять целевые UPDATE с функциями CONVERT/CAST.
Проблемы на уровне клиента (браузера) — если сервер верно отдаёт utf8, но HTML заголовки установлены в latin1, визуально это будет похоже на проблему с БД, хотя её нет.

Как исправить «мозаичные» символы (практические приёмы)

Определите байты, которые лежат в базе (SELECT HEX(col) …).
Если байты соответствуют utf8‑последовательностям, но колонка помечена latin1 — нужно изменить метаданные и/или рекодировать. Пример подхода:

Экспортировать проблемную колонку в дамп (INSERTs), перекодировать файл с помощью iconv, затем переимпортировать.

В MySQL можно использовать функции CONVERT/CAST в UPDATE:

UPDATE table SET col = CONVERT(BINARY CONVERT(col USING latin1) USING utf8);

Этот пример не универсален — он зависит от того, как именно были сохранены байты.

Всегда работайте на копии данных. Перед массовыми UPDATE сделайте snapshot/дамп.

Ментальные модели и эвристики

Правило одного языка: лучше поддерживать внутри системы одну кодировку (UTF‑8) и экспонировать её наружу во всех интерфейсах.
Трёхзвенная модель: 1) Источник данных (кодировка в таблице/сервере), 2) Транспорт (кодировка соединения — SET NAMES / клиентский параметр), 3) Представление (файл дампа/браузер). Ошибка в любом звене ломает всю систему.
Последовательность байтов важнее «меток»: если файл содержит байты C3 A4 — это utf8‑ä, даже если файл помечен как latin1. Надо руководствоваться содержимым.

Диагностическое дерево решений (Mermaid)

flowchart TD
  A[Есть проблема с умляутами?] --> B{Проблема при восстановлении из дампа?}
  B -- Да --> C[Проверить кодировку дампа 'xxd/iconv']
  C --> D{Дамп содержит utf8-байты?}
  D -- Да --> E[Импортируйте с --default-character-set=utf8 или вставьте SET NAMES 'utf8']
  D -- Нет --> F[Дамп, вероятно, в latin1 — импорт с latin1 или перекодировать iconv]
  B -- Нет --> G{Данные в БД отображаются неправильно при SELECT?}
  G -- Да --> H[Проверить character_set* переменные на сервере и клиенте]
  H --> I{Совпадают ли client и server?}
  I -- Нет --> J[Установить SET NAMES при подключении / настроить драйвер]
  I -- Да --> K[Проверить приложение/HTTP заголовки и мета-тег charset]
  K --> L[Если всё совпадает, проверить байты: SELECT HEX'']

Критерии приёмки (как убедиться, что всё исправлено)

Дамп импортируется без ошибок и без замены спецсимволов на ?.
SELECT с тестовой записью с умляутами возвращает ожидаемую строку.
Веб‑интерфейс/приложение отображает символы правильно при верно указанном Content‑Type.
HEX() значений в базе соответствует ожидаемым байтовым последовательностям для выбранной кодировки.

Краткое резюме

Проблема с умляутами — это почти всегда проблема несовпадения кодировок между источником, транспортом и приёмником.
Диагностика: проверьте переменные character_set в MySQL, посмотрите байты в дампе, используйте iconv для тестовой перекодировки.
Решение: экспорт/импорт с явным указанием кодировки (mysqldump –default-character-set, mysql –default-character-set, SET NAMES в дампе), обновление MySQLDumper или ручное добавление SET NAMES в дамп.
Для исправления «испорченных» данных используйте экспорт, перекодировку и повторный импорт или целевые UPDATE с CONVERT, работая только на копиях данных.

Важно: всегда делайте резервную копию перед попытками массового исправления данных.

Однострочные определения (глоссарий)

UTF‑8 — многобайтовая кодировка Unicode, способная представлять символы всех языков.
latin1 — однобайтовая кодировка (ISO‑8859‑1), подходящая для западноевропейских языков.
BOM — специальная метка в начале файла, указывающая на порядок байтов и кодировку (редко используется для UTF‑8 в веб‑контексте).
SET NAMES — SQL‑команда, инструмент для согласования кодировки между клиентом и сервером MySQL.

Сопроводительные советы для локализации и хостинга

Документируйте дефолтные кодировки ваших серверов и укажите их в панели управления хостинга.
Для российских/многоязычных сайтов выбор UTF‑8 является наилучшей практикой и существенно упрощает миграции.

Краткое содержание:

Поняли причину: несовпадение кодировок.
Выяснили, как диагностировать: переменные сервера, hex/xxd, iconv.
Получили SOP: экспорт с указанием кодировки, проверка дампа, импорт с указанием кодировки.
Даны чек‑листы для ролей и рекомендации по исправлению ошибок.