Перенос MySQL на новый хост: дамп, импорт, переключение

Перенос базы данных MySQL на новый хост - это четыре задачи: провести инвентаризацию того, что вы переносите, скопировать данные логическим дампом, воссоздать то, что дамп не переносит (пользователей, права, иногда definer и часовые пояса), а затем переключить приложение в коротком окне, во время которого ничто не пишет на старый сервер. Для баз данных до нескольких гигабайт весь переезд укладывается в запланированные десять-тридцать минут простоя, а команды - обычные mysqldump и mysql. Риск не в копировании - он в том, чего вы не заметили, пока приложение не направили на новый сервер. Это руководство построено так, чтобы вы заметили это заранее.

Целевая версия везде - MySQL 8.4 LTS. Источником может быть MySQL 5.7, 8.0, 8.4 или сервер другого семейства; различия отмечены там, где они важны.

Сначала инвентаризация#

Выполните это на старом сервере, прежде чем что-либо планировать. Эти запросы отвечают на вопросы, от которых зависит ход переезда.

-- Size per database: decides dump method and downtimeSELECT table_schema,       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mbFROM information_schema.tablesGROUP BY table_schema ORDER BY mb DESC;-- Anything not InnoDB: MyISAM is not covered by --single-transactionSELECT table_schema, table_name, engine FROM information_schema.tablesWHERE engine <> 'InnoDB' AND table_schema NOT IN  ('mysql', 'sys', 'information_schema', 'performance_schema');-- Stored code and its definersSELECT routine_schema, routine_name, routine_type, definer FROM information_schema.routinesWHERE routine_schema = 'appdb';SELECT trigger_name, definer FROM information_schema.triggers WHERE trigger_schema = 'appdb';SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'appdb';SELECT event_name, definer, status FROM information_schema.events WHERE event_schema = 'appdb';-- Version, modes and settings that change behaviourSELECT @@version, @@sql_mode, @@lower_case_table_names, @@time_zone,       @@character_set_server, @@collation_server;

Запишите: общий размер, все таблицы не на InnoDB (сначала конвертируйте их через ALTER TABLE t ENGINE=InnoDB или смиритесь с дампом с блокировками), каждого definer, который не является пользователем приложения, есть ли события и включены ли они, а также версию и sql_mode источника. Составьте ещё и список всех клиентов, которые подключаются, - не только основное приложение, но и cron-задания, воркеры, инструменты отчётов, забытую админку. SELECT user, host, COUNT(*) FROM information_schema.processlist GROUP BY user, host на протяжении дня поймает большинство из них; при переключении каждому нужно будет поменять настройки подключения.

Проверка версий и совместимости#

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

• Зарезервированные слова. В MySQL 8 зарезервировали такие слова, как RANK, GROUPS, ROWS, LEAD, LAG, SYSTEM, CUME_DIST и MEMBER. Столбец с именем rank загрузится нормально (дамп заключает идентификаторы в кавычки), но запросы приложения без кавычек сломаются. Поищите по коду.
• Нулевые даты. Значения 0000-00-00 в столбцах DATE и DATETIME отвергаются при sql_mode по умолчанию в 8.x. Сначала замените их на NULL на источнике или загружайте с сессионным sql_mode, который их допускает, и исправьте после.
• Запросы с `GROUP BY`. ONLY_FULL_GROUP_BY включён по умолчанию начиная с 5.7, но многие старые приложения его отключали. Если в sql_mode источника его нет, запросы приложения могут падать на новом сервере, пока их не исправят или пока сессионный режим не зададут так же.

Переезд с сервера другого семейства, например со старого форка, добавляет имена правил сравнения, которых целевой сервер не знает (ошибки Unknown collation), типы, которых нет в MySQL, и синтаксис для возможностей вроде последовательностей или системно-версионируемых таблиц. Проверьте дамп на временном сервере MySQL 8.4, прежде чем планировать настоящий переезд. Статья MySQL 8.4 LTS: что изменилось перечисляет изменения по сравнению с 8.0, а util.checkForServerUpgrade() в MySQL Shell сообщает о них для конкретного сервера.

Дамп данных#

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

$ mysqldump -h old-host -P 3306 -u root -p \    --single-transaction --routines --events --triggers \    --set-gtid-purged=OFF --no-tablespaces --hex-blob \    appdb | zstd -T0 > appdb.sql.zst

Обратите внимание, чего здесь нет: --databases. Без него в дампе нет операторов CREATE DATABASE и USE, так что его можно загрузить в базу данных с другим именем. Это важно на хостинговом сервере, где база данных создана за вас под своим именем. С --databases appdb дамп в любом случае создал бы appdb и переключился бы на неё.

--set-gtid-purged=OFF и --no-tablespaces устраняют две ошибки, которые чаще всего ломают загрузку не суперпользователем. --hex-blob позволяет двоичным столбцам пережить любую обработку текста по пути. Каждый флаг объяснён в статье бэкап и восстановление через mysqldump.

Для баз данных настолько больших, что однопоточные дамп и загрузка не укладываются в ваше окно, - примерно больше 20 ГБ, в зависимости от индексов, - util.dumpSchemas() и util.loadDump() из MySQL Shell работают параллельно и по ходу дела умеют удалять definer и исправлять типичные проблемы совместимости. loadDump требует включённого local_infile на целевом сервере; проверьте это, прежде чем на него полагаться.

Пользователи, права и definer#

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

-- On the old server: print each account and its grantsSHOW CREATE USER 'app'@'%';SHOW GRANTS FOR 'app'@'%';

SHOW CREATE USER выводит учётную запись с хэшем пароля, так что её можно воссоздать с тем же паролем, не зная его, - с одним исключением. Учётные записи на mysql_native_password несут хэш этого плагина, а в MySQL 8.4 плагин по умолчанию отключён, поэтому воссозданная учётная запись не сможет войти. Вместо этого задайте новый пароль с caching_sha2_password по умолчанию - это ещё и хороший момент сменить учётные данные, которые, возможно, годами лежали в старых файлах конфигурации. Как правильно воссоздавать учётные записи и роли, описано в статье пользователи и привилегии MySQL.

Definer - вторая половина. Каждое представление, процедура, триггер и событие называет учётную запись, которая его создала, а загрузка объекта, чей definer - не вы, требует в 8.4 привилегии SET_ANY_DEFINER; если такой учётной записи на новом сервере нет вовсе, объект ещё и падает при использовании. Самый простой путь при загрузке обычным пользователем приложения - удалить definer, и тогда definer всего станете вы:

$ zstd -dc appdb.sql.zst \  | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' \  | mysql -h new-host -P 30412 -u app -p appdb_new

На RE:NODE сервер MySQL запускается с базой данных приложения, пользователем приложения для неё и паролем root, всё сгенерировано. Загружайте в базу данных с этим именем, направьте приложение на этого пользователя, а root используйте, только если дампу нужны привилегии, которых нет у пользователя приложения.

Загрузка и проверка#

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

$ zstd -dc appdb.sql.zst | mysql -h new-host -P 30412 -u root -p appdb_new

Проверяйте цифрами, а не на глаз. information_schema.tables.table_rows для InnoDB - это оценка, и она различается между серверами даже при одинаковых данных; считайте точно:

-- Generate exact counts for every table; run the output on both servers and diffSELECT CONCAT('SELECT ''', table_name, ''' AS t, COUNT(*) AS n FROM `',              table_name, '` UNION ALL') AS qFROM information_schema.tablesWHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';

Уберите завершающий UNION ALL из последней строки, выполните запрос на обеих сторонах и сравните. Затем сравните то, что подсчёт упускает: последнюю строку в самых загруженных таблицах (SELECT MAX(id), MAX(updated_at) FROM orders), несколько известных записей с текстом с диакритикой, чтобы поймать порчу кодировки (смотрите utf8mb4 и правила сравнения), наличие процедур и событий (SHOW PROCEDURE STATUS WHERE Db = DATABASE(), SHOW EVENTS) и то, что значения AUTO_INCREMENT перенеслись (SHOW CREATE TABLE их включает).

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

Настройки, которые не переезжают#

Дамп копирует данные, а не поведение сервера. Вот различия, из-за которых приложение ведёт себя странно после переезда, который «прошёл успешно»:

• Часовые пояса. Если приложение использует именованные пояса (CONVERT_TZ(ts, 'UTC', 'Europe/Berlin') или SET time_zone = 'Europe/Berlin'), на новом сервере должны быть загружены таблицы часовых поясов, иначе CONVERT_TZ возвращает NULL, а SET time_zone падает с Unknown or incorrect time zone. Проверьте через SELECT CONVERT_TZ('2026-01-01 00:00', 'UTC', 'Europe/Berlin');. Числовые смещения вроде '+00:00' работают всегда.
• `lower_case_table_names`. База данных с сервера на Windows или macOS, где имена таблиц нечувствительны к регистру, может содержать запросы, которые обращаются к Orders и orders как к одному и тому же. В Linux по умолчанию регистр учитывается, а переменную нельзя изменить после инициализации сервера. Исправляйте запросы, а не сервер.
• `sql_mode`. Сравните @@GLOBAL.sql_mode на обоих. Если старый сервер работал в более мягком режиме, либо исправьте приложение, либо в качестве временной меры пусть оно задаёт старый режим для своей сессии при подключении.
• Планировщик событий. События копируются, но выполняются, только если на новом сервере event_scheduler равен ON (по умолчанию в 8.x). Проверьте, что события, которые должны выполняться, выполняются, - а те, что вы отключили на старом сервере, отключены и на новом.
• Расстояние. Если база данных переезжает дальше от приложения, каждый запрос платит за лишний путь туда и обратно. Приложение, выполняющее сорок запросов на страницу, ощущает 20 мс добавленной задержки как большую часть секунды. Серверы RE:NODE находятся в Германии; держите приложение рядом с базой данных.

Переключение#

Для обычного приложения надёжное переключение - короткое запланированное окно:

1. Уменьшите время кэширования там, где оно есть. Если приложение находит базу данных по DNS-имени, которым вы управляете, уменьшите его TTL за день, чтобы изменение быстро распространилось.
2. Остановите запись на старый сервер. Переведите приложение в режим обслуживания и остановите воркеры и cron-задания. Если у вас есть root на старом сервере, SET GLOBAL super_read_only = ON гарантирует, что больше ничто туда не пишет, - включая забытого клиента, которого вы не внесли в список.
3. Снимите финальный дамп и загрузите его на новый сервер вместо репетиционной копии. Если сначала удалить и пересоздать целевую базу данных, таблицы с репетиции не смешаются с новыми.
4. Проверьте теми же подсчётами и проверками, что и раньше. Теперь это быстро, потому что вы всё оформили скриптом.
5. Поменяйте настройки подключения у каждого клиента - хост, порт, пользователь, пароль, имя базы данных - и запустите приложение, затем воркеры и задания.
6. Наблюдайте за ошибками приложения и соединениями нового сервера в течение первого часа.

Сохраните старый сервер в режиме только для чтения хотя бы на несколько дней. Если что-то всплывёт, будет с чем сравнить; если в первый час что-то пойдёт совсем не так, переключение обратно - это изменение конфигурации, а не восстановление. Для более крупных баз данных, где дамп и загрузка не уложились бы в короткое окно, альтернатива - загрузить начальную копию, сделать новый сервер репликой старого через CHANGE REPLICATION SOURCE TO, пока он не догонит, а затем переключиться за секунды. Для этого нужны двоичное журналирование и привилегии репликации на источнике, сетевой доступ между серверами и контроль над конфигурацией обоих - это оправдано для большой загруженной базы данных и не нужно для большинства. Сторону изменения схемы без остановки приложения описывает статья миграции без простоя, а перенос именно сайта на WordPress - статья перенос WordPress на новый хост.

FAQ#

Сколько времени занимает перенос базы данных MySQL?

Копирование занимает примерно столько, сколько дамп плюс загрузка, и загрузка - медленная половина, потому что перестраивается каждый индекс. База данных в 1 ГБ обычно переезжает за несколько минут; 20 ГБ в один поток могут занять час и больше. Отрепетируйте один раз на реальных данных и засеките время - это число плюс проверка и есть ваш простой.

Можно ли переехать вообще без простоя?

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

Нужно ли копировать системную базу данных mysql?

Нет, и загружать её из другой версии в MySQL 8.4 не стоит. Воссоздайте нужные учётные записи по выводу SHOW CREATE USER и SHOW GRANTS и смените пароли у всех учётных записей, которые использовали mysql_native_password.

Почему после переноса приложение пишет, что пользователь не существует?

Либо учётную запись не воссоздали, либо её шаблон хоста не совпадает с новым адресом приложения, либо она использует mysql_native_password, который в 8.4 по умолчанию отключён. Если же в ошибке упоминается definer, значит, представление или процедура называет учётную запись, которой на новом сервере нет, - удалите или воссоздайте definer.

Можно ли перенести базу данных через phpMyAdmin?

Для небольших баз данных - да: экспортируйте SQL со старого сервера и импортируйте на новый. Он работает внутри веб-запроса, поэтому большие базы данных упираются в лимиты загрузки и времени. Лимиты и настройки разобраны в статье импорт и экспорт в phpMyAdmin; если база больше нескольких сотен мегабайт, используйте mysqldump и клиент mysql.