Creating Users and Databases in MySQL 8 and Percona 8

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

[/enlighter]

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]

Maybe you want to Update a Password

[enlighter linenumbers=“true”]ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;[/enlighter]