MYSQL: Client does not support authentication protocol…

I started working on this article after I solved an issue connecting my express app to my local MySQL server. I got the error message below on my first attempt to connect to a local MySql database.

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

MySQL versions from 4.1x upwards use a different algorithm to hash passwords than the pre 4.1 versions. If your client does not implement this algorithm, it’s possible you will get this error when you try to connect.

Solution

There are two options available for you;

1. Alter the current user password to use the old algorithm.
2. Create a new user, set a password with the old hashing algorithm, and set privileges.

Modify Current User Password

In your MySQL shell, type the command below inputting your username (root or the current user) in place of user, and password. Don’t remove the quotes.

mysql> ALTER USER ‘user’@’localhost’ IDENTIFIED WITH mysql_native_password by ‘password’;

After this, you can use your new password to connect.

Create a New User

If you are like me and you don’t want to have two users with full privileges, this is for you.

We begin by making a new user within the MySQL shell:

mysql> CREATE USER ‘newuser'@'localhost' IDENTIFIED BY ‘password';

Now we have a new user, but the user is useless currently as it has no permissions. If you quit the shell and attempt to login with the credentials, you will be denied.

Next, we give the user some privileges.

For full privileges:

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@'localhost';

The asterisks represent the database and the table respectively that the user is allowed to access. Leave the asterisks if you want the user to have access to all databases and tables.

For specific privileges:

mysql> GRANT permission1, permission2, ... PRIVILEGES ON *.* TO ‘newuser’@'localhost';

Common privileges are:

• ALL PRIVILEGES - full access to a designated database (or if no database is selected, global access across the system).
• CREATE- allows the user to create new tables or databases.
• DROP- allows the user to delete tables or databases.
• DELETE- allows the user to delete rows from tables.
• INSERT- allows the user to insert rows into tables.
• SELECT- allows the user the SELECT command to read through databases.
• UPDATE- allow the user to update table rows.

Reload the privileges:

mysql> FLUSH PRIVILEGES;

To test out your new user, log out by typing:

mysql> quit;

Then log back in with this command in terminal:

mysql> mysql -u [username] -p;

Last step: alter the user password

mysql> ALTER USER ‘user’@’localhost’ IDENTIFIED WITH mysql_native_password by ‘password’;

Now you can connect your node server to MySQL.

Thanks for reading.