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
[enlighter linenumbers=“true”]CREATE USER ‘newuser’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘user_password’;[/enlighter]The important part here is the “mysql_native_password” if you don’t have this portion, then you’ll get errors like the following.
[enlighter linenumbers=“true”]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
[enlighter linenumbers=”true”]default-authentication-plugin=mysql_native_password[/enlighter]Now let’s grant the user privileges.
[enlighter linenumbers=“true”]GRANT ALL ON database_name.* TO ‘newuser’@’localhost’;[/enlighter]Now confirm the privileges.
[enlighter linenumbers=“true”]SHOW GRANTS FOR ‘newuser’@’localhost’;[/enlighter]