Overview - I installed a new Ubuntu marketplace instance on DigitalOcean that was designed for a 1-click install of a full LAMP server. The system was up minutes later, however, the configuration became a bit more complicated than I had experienced just a day previous with Debian, its MySQL variant, and PHP 7.4.
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"
- 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.
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';