Node.js can't authenticate to MySQL 8.0
MySQL 8.0 uses a new default authentication plugin - caching_sha2_password - whereas MySQL 5.7 used a different one - mysql_native_password. Currently, the community Node.js drivers for MySQL don't support compatible client-side authentication mechanisms for the new server plugin.
A possible workaround is to alter the type of user account to use the old authentication plugin:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';Or create a different one that uses that same plugin:
CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';There's a pull request in pipeline to properly address the issue.
Another option is to use the official MySQL Node.js connector (full disclosure: I'm the lead dev), which is based on the X Protocol and already supports the new authentication mode.
A workaround solution:create a new user with mysql_native_password protocol:
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'userpassword';and then grant permissions on the database for the user:
GRANT ALL PRIVILEGES ON userdb.* TO 'dgtong'@'localhost';