mysql - pgloader - 无法以用户“root”身份连接到“localhost”（端口 3306）的 mysql：条件 QMYND：MYSQL-UNSUPPORTED-AUTHENTICATION 已发出信号

Question

我正在尝试将我的 Rails 应用程序从 mysql 迁移到 postgres。由于我们已经运行了应用程序，所以我正在使用 pgloader 将 mysql 数据移动到 postgres 数据库。但是当我这样做时

pgloader mysql://root:root_password@127.0.0.1/mysql_database postgresql://postgres_user:postgres_pass@127.0.0.1/postgres_database

我收到错误 - 无法以用户“root”身份连接到“127.0.0.1”（端口 3306）的 mysql：条件 QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION 已发出信号。我可以很容易地从终端登录到 mysql。提前致谢。

标签： mysqlruby-on-railspostgresqlpgloader

Answer 1

The problem is that currently pgloader doesn't support caching_sha2_password authentication plugin, which is default for MySQL 8, whereas older MySQL versions use mysql_native_password plugin. The corresponding issue is opened on Github.

Based on this comment, the workaround here is to edit my.cnf (if you don't know where it is, look here) and in [mysqld] section add

default-authentication-plugin=mysql_native_password

Then restart your MySQL server and execute:

ALTER USER 'youruser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

After that the error must be gone.