MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client
Execute the following query in MYSQL Workbench
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
Where root
as your user
localhost
as your URL
and password
as your password
Then run this query to refresh privileges:
flush privileges;
Try connecting using node after you do so.
If that doesn't work, try it without @'localhost'
part.
Let's first make it clear what's going on.
MySQL 8 has supports pluggable authentication methods. By default, one of them named caching_sha2_password
is used rather than our good old mysql_native_password
(source). It should be obvious that using a crypto algorithm with several handshakes is more secure than plain password passing that has been there for 24 years!
Now, the problem is mysqljs
in Node (the package you install with npm i mysql
and use it in your Node code) doesn't support this new default authentication method of MySQL 8, yet. The issue is in here: https://github.com/mysqljs/mysql/issues/1507 and is still open, after 3 years, as of July 2019.
UPDATE June 2019: There is a new PR in mysqljs now to fix this!
UPDATE Feb 2020: Apparently it's scheduled to come in version 3 of mysqljs.
UPDATE July 2020: Apparently it's still not in yet (as of April 2020 at least), but it's claimed that node-mysql2 is supporting Authentication switch request. Please comment below if node-mysql2
is working fine for this issue -- I will test it later myself.
Your Current Options
Option 1) Downgrade "MySQL" to authenticate using good old "mysql_native_password"
That's what everybody suggests here (e.g. top answer above). You just get into mysql
and run a query saying root
is fine using old mysql_native_password
method for authentication:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password ...
The good thing is, life is going to be simple and you can still use good old tools like Sequel Pro without any issue. But the problem is, you are not taking advantage of a more secure (and cool, read below) stuffs available to you.
Option 2) Replace "Node" package with MySQL Connecter X DevAPI
MySQL X DevAPI for Node is a replacement to Node's Mysqljs package, provided by http://dev.mysql.com official guys.
It works like a charm supporting caching_sha2_password
authentication. (Just make sure you use port 33060
for X Protocol communications.)
The bad thing is, you have left our old mysql
package that everyone is so used to and relies on.
The good thing is, your app is more secure now and you can take advantage of a ton of new things that our good old friends didn't provide! Just check out the tutorial of X DevAPI and you'll see it has a ton of new sexy features that can come in handy. You just need to pay the price of a learning curve, which expectedly comes with any technology upgrade. :)
PS. Unfortunately, this XDevAPI Package doesn't have types definition (understandable by TypeScript) yet, so if you are on typescript, you will have problems. I tried to generate .d.ts using dts-gen
and dtsmake
, but no success. So keep that in mind.
Cheers!
Using the old mysql_native_password
works:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootPassword';
-- or
CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'bar';
-- then
FLUSH PRIVILEGES;
This is because caching_sha2_password
is introduced in MySQL 8.0, but the Node.js version is not implemented yet. You can see this pull request and this issue for more information. Probably a fix will come soon!