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

Инструкция подходит для MySQL 8.0 и 8.4 LTS и MariaDB 10.6-11.x.

Подготовка

  1. Сравните версии на старом и новом сервере: mysql --version или SELECT VERSION();. Перенос дампом работает между разными версиями и даже из MySQL в MariaDB и обратно, но при большой разнице версий проверьте заметки об обновлении: меняются значения по умолчанию, типы аутентификации, зарезервированные слова.

  2. Посмотрите, что переносить:

    sql
    SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

    Системные базы mysql, information_schema, performance_schema и sys целиком не переносите - на новом сервере они свои.

  3. Проверьте место на новом сервере: нужно место под дамп и под сами базы.

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

Перенос баз через mysqldump

База данных, стрелка, файл дампа, стрелка, база данных

В MariaDB 11 утилиты называются mariadb-dump и mariadb. Старые имена mysqldump и mysql пока работают, но выводят предупреждение, поэтому в скриптах для MariaDB лучше сразу писать новые.

Дамп

bash
mysqldump -u root -p --single-transaction --routines --triggers --events \
    --databases shop blog > dump.sql
  • --single-transaction - согласованный снимок без блокировки таблиц InnoDB. Для таблиц MyISAM он не помогает - их блокируют на время дампа или предварительно останавливают запись.
  • --routines, --triggers, --events - хранимые процедуры, функции, триггеры и события. Без --routines и --events они в дамп не попадут.
  • --databases - в дамп добавятся команды CREATE DATABASE и USE, базы создадутся с прежними именами.

Чтобы выгрузить все пользовательские базы, получите их список:

bash
mysql -u root -p -N -e "SHOW DATABASES" | grep -Ev '^(mysql|information_schema|performance_schema|sys)$'

и передайте его в --databases.

Дамп хорошо сжимается:

bash
mysqldump ... --databases shop blog | gzip > dump.sql.gz

Передача и загрузка

bash
scp dump.sql.gz user@new-server:/tmp/
ssh user@new-server
gunzip < /tmp/dump.sql.gz | mysql -u root -p

Без промежуточного файла

Если места мало или базы большие, дамп можно сразу загрузить на новый сервер через SSH:

bash
mysqldump -u root -p --single-transaction --routines --triggers --events --databases shop \
    | gzip | ssh user@new-server "gunzip | mysql -u root -p'пароль'"

Пароль в командной строке виден в списке процессов. Лучше на обоих серверах положить учётные данные в файл ~/.my.cnf с правами 600:

ini
[client]
user=root
password=пароль

и не указывать -u и -p вовсе.

Кодировка

Если базы в utf8mb4, ничего делать не нужно: mysqldump по умолчанию использует эту кодировку. Если в старых базах кодировка cp1251 или latin1, перед переносом проверьте, что после загрузки текст читается правильно, - на тестовой копии, не на боевом сервере.

Перенос пользователей и прав

Три карточки с ключами и отметками прав, замок и база данных

Копировать таблицу mysql.user между разными версиями нельзя: структура системных таблиц меняется. Правильно - выгрузить команды создания пользователей и выдачи прав.

На старом сервере список пользователей:

sql
SELECT user, host FROM mysql.user
WHERE user NOT IN ('root', 'mysql.sys', 'mysql.session', 'mysql.infoschema', 'debian-sys-maint', 'mariadb.sys');

Для каждого пользователя получите команды:

sql
SHOW CREATE USER 'shop'@'localhost';
SHOW GRANTS FOR 'shop'@'localhost';

SHOW CREATE USER выводит пользователя вместе с хешем пароля, так что пароли знать не нужно. Скрипт, который соберёт всё в один файл:

bash
mysql -N -e "SELECT CONCAT('''', user, '''@''', host, '''') FROM mysql.user
  WHERE user NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema','debian-sys-maint','mariadb.sys')" |
while read u; do
    mysql -N -e "SHOW CREATE USER $u" | sed 's/$/;/'
    mysql -N -e "SHOW GRANTS FOR $u" | sed 's/$/;/'
done > users.sql

Перенесите users.sql на новый сервер, просмотрите его и выполните:

bash
mysql < users.sql

Особенности:

  • Плагин аутентификации. В MySQL 8 по умолчанию используется caching_sha2_password, в старых версиях и MariaDB - mysql_native_password. В MySQL 8.4 плагин mysql_native_password по умолчанию отключён (включается параметром mysql_native_password=ON в конфигурации), а в MySQL 9 удалён совсем, поэтому пользователей со старым плагином при переходе на 8.4 нужно перевести на caching_sha2_password. Хеш одного плагина не подходит для другого. Если при выполнении скрипта возникает ошибка или пользователь не может войти, задайте пароль заново: ALTER USER 'shop'@'localhost' IDENTIFIED BY 'пароль';.
  • Перенос в MariaDB из MySQL 8 и обратно - формат SHOW CREATE USER различается, проще создать пользователей вручную с новыми паролями и выдать права по выводу SHOW GRANTS.
  • Хост. Если приложение будет подключаться к базе с другого сервера, адрес в 'user'@'host' должен соответствовать его адресу или подсети.

Для больших инсталляций MySQL удобна утилита MySQL Shell: util.dumpInstance() и util.loadDump() переносят базы вместе с пользователями и работают заметно быстрее mysqldump, потому что выгружают и загружают данные в несколько потоков.

Перенос настроек

Конфигурация лежит в /etc/mysql/ (Debian, Ubuntu) или /etc/my.cnf и /etc/my.cnf.d/ (RHEL и родственные). Не копируйте файл целиком - сравните параметры и перенесите только свои изменения: размер буферов (innodb_buffer_pool_size), max_connections, bind-address, режим SQL (sql_mode), кодировки. Некоторые параметры из старых версий в новых удалены, и сервер с ними не запустится.

Прямое копирование файлов

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

  1. Остановите MySQL на обоих серверах.
  2. Скопируйте папку данных (обычно /var/lib/mysql): rsync -aH /var/lib/mysql/ new-server:/var/lib/mysql/.
  3. Проверьте владельца файлов (mysql:mysql) и, в системах с SELinux, контексты (restorecon -R /var/lib/mysql).
  4. Запустите MySQL на новом сервере.

Пользователи при этом переносятся вместе с базой mysql. Между разными версиями этот способ не используйте.

Проверка и переключение

  1. Сравните число таблиц и строк в важных таблицах на старом и новом сервере (SELECT COUNT(*) FROM ...).
  2. Проверьте, что процедуры и триггеры на месте: SHOW PROCEDURE STATUS WHERE Db = 'shop';, SHOW TRIGGERS FROM shop;.
  3. Войдите на новый сервер от имени каждого пользователя приложения.
  4. Если приложения будут подключаться по сети, проверьте bind-address в настройках сервера и откройте порт 3306 в файрволе только для нужных адресов.
  5. Переключите приложения на новый сервер (адрес в конфигурации или запись DNS), убедитесь, что всё работает, и только потом выключайте старый.