Due to experiencing multiple issues I wanted to document what I experienced for historical sake.
MySQL syntax for user creation and database permissions (GRANT) in version 8 are different. For someone who is a DBA or hardcore MySQL user, this likely wouldn't be an issue, but it took more than an hour of googling to find the correct information, which turned out to be several issues. Much of what I found was partially accurate creating greater confusion but in the end, I found syntax that worked correctly.
- Make sure you create the user and its accompanying password, then Grant access to a given database. Previously I had done this in one long command. Also, for some reason, I was not able to use 'localhost' and needed to use '%'. And when trying to "grant privileges", I received this error "ERROR 1410 (42000): You are not allowed to create a user with GRANT"
- Example Solutions
- create user 'mysqluser'@'%' identified with caching_sha2_password by 'asecurepassword';
grant all privileges on database.* to 'mysqluser'@'%';
- PHP & MySQL - I believe this next issue has to do with the version of PHP you are running and what authentication method is enabled. I know there are various articles that speak to how and why etc, but I COULD NOT get my new database user accounts to connect and in the end, this MySQL command resolved my issues and allowed my WordPress user account to connect and one for phpmyadmin.
alter user 'mysqluser'@'%' identified with mysql_native_password by 'asecurepassword';
NOTE: I believe the MySQL "alter" command above would assist someone experiencing similar issues following a MySQL upgrade from a version prior to 8.
1/14/2023
Ran into issues with "grant" commands.
ERROR 1410 (42000): You are not allowed to create a user with GRANT
The following syntax corrected my issues.
CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';
GRANT ALL ON `database`.* TO 'user'@'localhost';
