Summary

1. If you just want to eliminate bugs,at the expense of project security risk(e.g. this is just a personal project or development environment), use@Pras' answer--ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'ThenRefresh permissions
2. If you want to fix it and don't know why, just install and usemysql2(instead ofmysql) and use it -npm mysql2andmysql = require('mysql2');.
3. If you are a developer who is curious and always eager to learn, read on... :)

---

What happened?

We first need to figure out what happened.

MySQL 8 supports pluggable authentication methods. By default, one of the passwords namedcaching_sha2_passwordis used instead of our oldmysql_native_password(source). Apparently, an encryption algorithm using multiple handshakes is more secure than the plain password passing used in24. Year!

Now, the problem is thatmysqljsin Node (the package you install usingnpm i mysqland use it in Node code) does not support this new default authentication for MySQL 8 methods are not yet available. The issue is here:https://github.com/mysqljs/mysql/issues/1507 a> As of July 2019, still open three years later.

Update June 2019:There is a new PR inmysqljs a> to fix this now!

February 2020 Update:Apparently it isplanned to come in mysqljs version 3.

Update July 2020:Apparently it's not inyet (as of at least April 2020), butclaimsnode- mysql2 supportsauthentication switch requests. Please leave a comment below ifnode-mysql2resolves this issue properly - - I will test it myself later.

Update April 2021:The problem seems to still exist, just 3 days agosomeone created a forkand implemented it - but in the mysql.js package It's not official yet. Also, based on the comments below, it seems that themysql2 packageworks fine andproperly supports authentication switching.

---

Your current options

Option 1) [Not Recommended] Downgrade 'MySQL' to use the old 'mysql_native_password' for authentication

This is what everyone here suggests (likethe best answerabove). You can simply go intomysqland run a query stating thatrootcan be authenticated using the oldmysql_native_passwordmethod:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password ...

The good news is, life gets easier and you can still use good old tools like Sequel Pro withoutany questions. But the problem is, you're not taking advantage of something safer (and cool, read below).

Option 2) [Meh...] Replace "Node" package with MySQL Connecter X DevAPI

MySQLIt works like a charm that supportscaching_sha2_passwordauthentication. (Just make sure you use port33060

for

X protocol communication.)What's bad is that you've moved away from the oldmysqlpackage that every one of us was used to and relied on.

The benefit is that your applications are now more secure and you can take advantage of tons of new features that our good old friend doesn't offer! Just check out the

X DevAPI Tutorialand you'll see it has tons of new sexy features that you can come in handy with. You just have to pay the price of the learning curve, which is expected to come with any technology upgrade. :)PS. Unfortunately, this XDevAPI package doesn't yet have type definitions (that TypeScript understands), so if you use

typescript

, you'll have problems. I tried using dts-gen and dtsmake to generate .d.ts without success. So please keep this in mind.Option 3) [Recommended] Replace "mysql.js" with the "mysql2.js" package

As mentioned above, the mysql package (NPM package link) is

still having this issue

(as of April 2021). But the mysql2 package (

NPM package link) is not. So the following should probably be the easy answer!

npm un mysql && npm i mysql2

Please note thatmysql2 is a fork of the popularmysql, but its popularity (

mysql2was 620,000 downloads per week in April times) 2020) is close to the original package (720K downloads per week in April 2021mysql), so it seems reasonable to make the switch!

Execute the following query in MYSQL Workbench

Change user 'root'@'localhost' to be identified by mysql_native_password BY 'password';

whererootis your userlocalhostas your URL andpasswordas your password

Then run this query to refresh permissions:

Refresh permissions;

After doing this try to connect using node.

If this doesn't work, try not using the@'localhost'part.