pgloader - Failed to connect to mysql at "localhost" (port 3306) as user "root": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled

Question

I am trying to migrate my rails application from mysql to postgres. Since we have already running application so I am moving mysql data to postgres database using pgloader. But when I do

pgloader mysql://root:[email protected]/mysql_database postgresql://postgres_user:[email protected]/postgres_database

I get error - Failed to connect to mysql at "127.0.0.1" (port 3306) as user "root": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled. I can easily log in to mysql from terminal though. Thanks in advance.

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.