percona

If you’ve started using MySQL 8, you’ll notice your usually commands for creating a user no longer function. That’s because it’s changed in MySQL 8, you need to now create a user, then grant privileges.

Let’s create a user

CREATE USER ‘newuser’@'localhost' IDENTIFIED WITH mysql_native_password BY ‘user_password';

The critical part here is the “mysql_native_password” if you don’t have this portion, you’ll get errors like the following.

PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in mysql_test.php on line 8
PHP Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in mysql_test.php on line 8

This is due to the default authentication plugin being set to sha2, you can change it back to native by adding the following into your my.cnf

default-authentication-plugin=mysql_native_password

Now let’s grant the user privileges.

GRANT ALL ON database_name.* TO ‘newuser’@'localhost';

Now confirm the privileges.

SHOW GRANTS FOR 'newuser’@'localhost';

Maybe you want to Update a Password

ALTER USER ‘root'@'localhost' IDENTIFIED BY 'MyNewPass';