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 important part here is the “mysql_native_password” if you don’t have this portion, then 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
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';