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:
1 | 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:
1 | PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in... |
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
1 | default-authentication-plugin=mysql_native_password |
Now let’s grant the user privileges.
1 | GRANT ALL ON database_name.* TO ‘newuser’@’localhost’; |
Now confirm the privileges.
1 | SHOW GRANTS FOR ‘newuser’@’localhost’; |