Last Updated on September 29, 2022 EDT by Jordan
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';