Ubuntu 20.04 - LAMP Server (DigitalOcean), Apache 2.4, MySql 8, PHP 7.4.3 & WordPress 5.7

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"
• 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';

ZenCart 1.3.9 - Template Problem

ISSUE:

After working to create a custom theme I found the default template and any new templates became unavailable through the typical method within the admin console.  I attempted to resolve the issue by re-installing and stepping through each modification systematically with no success.  This led to the manual modification of the database to choose the theme of choice.

WORK AROUND:

• Obtain the database name, database username and password.
• Create a new template being sure to include a template_info.php with the correct information.  Note the directory name.
• Enter MySQL (-p will prompt for the user password)

       # mysql -u database_username -p

•  Select database to use.

       mysql> use database_name;

• Change the default template dir.

       mysql> UPDATE database_template_select SET template_dir = 'new_template'; 

• Exit database

       mysql> \q

Template directory has now been changed and will be reflected in the admin console.

ZenCart - MySQL Database Backup & Restore (mirror really)

While there seems to be a tremendous amount of information on MYSQL and backing up databases as well as restoring them, I ran into some challenges and had to follow the steps below to mirror a database.

My goal was to simply have the ability to backup my database while going through some testing.  I also wanted to verify that once I had a working "development" environment that I could export/import into a new "production" environment.  While mysqldump should accomodate this request possibly in one simple command, I had issues and ended up using mysqldump to export and the "source" command to populate a new database.  

# mysqldump -u user_name -p database_name > database_bkup_file_name.sql

Note:  Adding the '-p' will cause mysqldump to prompt for the password for the user that you stated in "user_name".

You should theoretically then be able to use mysqldump to then populate a database as follows:

# mysqldump -u user_name -p database_name < database_bkup_file_name.sql

Based on some challenges and a desire to move forward and not necessarily become a MySQL admin, I simply created a new database, added the proper user & permissions followed by:

mysql> use new_database_name;

mysql> source name_of_database_backup_file.sql;

 
It should have been obvious that the new database was being populated but if you desire to verify, you can simply view the tables.

mysql> show tables;

While this proved handy, it does not allow you to update a database.  I will be making some incremental changes to a "dev" environment and will want to update the "prod" environment.  In order to accomplish this, I will need to drop the "prod" database and then recreate it and populate it with data.  I can see how learning to properly use the mysqlimport command may be the better answer.