При переезде базы импорт системной базы mysql падает «Table 'user' already exists»: как перенести пользователей и права между версиями через SHOW GRANTS

Разработка ПО
1

Что было. Перенос MySQL/MariaDB на новый сервер. Данные приложений перенеслись дампом, а попытка выгрузить и загрузить системную базу mysql (где хранятся пользователи) закончилась Table 'user' already exists и несовместимостями.

Почему так. Системная база это внутренность конкретной версии сервера: набор таблиц, их структура и способ хранения паролей отличаются между MySQL 5.7, 8.0, MariaDB 10.x и 11.x. Импортировать её дампом с другой версии нельзя и не нужно. Участники предложили перенести пользователей как команды, а не как таблицы; автор подтвердил.

Способ. На старом сервере получить список пользователей и для каждого его права:

SELECT CONCAT("SHOW GRANTS FOR '", user, "'@'", host, "';") FROM mysql.user
 WHERE user NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema','mariadb.sys','debian-sys-maint');

Выполнить полученные SHOW GRANTS FOR …;: вывод это готовые SQL-команды GRANT …, которые копируются на новый сервер. Пароли: на MySQL 8 SHOW CREATE USER 'u'@'h'; даёт CREATE USER … IDENTIFIED WITH … AS '<хеш>', что переносит хеш без знания пароля; на MariaDB SHOW GRANTS включает IDENTIFIED BY PASSWORD '<хеш>'. Если переносится между разными СУБД (MySQL ↔ MariaDB) или между сильно разными версиями, хеши могут не подойти: задать пароли заново и сообщить владельцам приложений.

Скрипт целиком (от пользователя с правами чтения mysql.*):

mysql -N -e "SELECT CONCAT('SHOW CREATE USER \'',user,'\'@\'',host,'\';') FROM mysql.user WHERE user NOT LIKE 'mysql.%' AND user<>'root'" \
 | mysql -N | sed 's/$/;/' > users.sql
mysql -N -e "SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user WHERE user NOT LIKE 'mysql.%' AND user<>'root'" \
 | mysql -N | sed 's/$/;/' > grants.sql

На новом сервере: mysql < users.sql && mysql < grants.sql && mysql -e 'FLUSH PRIVILEGES'. Утилита pt-show-grants из Percona Toolkit делает то же одной командой.

Порядок переезда, который не ломается.

  1. Новый сервер установлен, обновлён, mysql_secure_installation пройден.
  2. Пользователи и права (как выше). Плагины аутентификации согласованы с приложениями (старые клиенты не умеют caching_sha2_password).
  3. Данные: mysqldump --single-transaction --routines --triggers --events --databases app1 app2 (без --all-databases, чтобы не тащить mysql), импорт.
  4. mysql_upgrade/mariadb-upgrade при смене версии.
  5. Проверка приложений на новом сервере до переключения; старый сервер остаётся в режиме только чтение до финального дампа.

Что не переносить файлами. Каталог данных целиком между версиями (отдельный разбор про несовместимые журналы), таблицы mysql.*, ib_logfile*.

Команда описана в документации MariaDB.