Configuring MySQL Permissions for WordPress on Ubuntu 18.04 and WSL

I discussed in my previous article on the changes needed to automate the upgrade of Ubuntu 18.04 on the Windows Subsystem for Linux (WSL). The recommended commands that I had been provided were intended for Ubuntu 16.04, yet I discovered some steps are a little different on 18.04.

My goal is to create a script that fully automates the setup of WordPress on WSL, based on this article by Jason Cross. As I walked through his tutorial I noticed another more significant difference when it came to setting up the WordPress database.

The instructions inform us to configure a MySQL root password when prompted during the LAMP server installation. On Ubuntu 18.04 we are not prompted to set this password. Admittedly, I totally missed this difference my first time through the procedure. So, when I got to the step to create the vanilla WordPress site, I received an access denied error when trying to create the database.

Error 1698 (28000): Access denied for user ‘root’@’localhost’

This makes sense of course because the command is asking for a password, but none has been set.

What Changed?

As of the time this article was written, Ubuntu 18.04 installs with MySQL 5.7. Specifically, mine installed with MySQL 5.7.28. While both Ubuntu 18.04 and 16.04 currently install MySQL 5.7, the 18.04 installer configures the root account to authenticate using an auth socket instead of a hashed password. This is to better secure the root account, however it can be changed back to the legacy method.

Following the installation of your LAMP server on 18.04, you can view the list of default accounts by running this command:

sudo mysql -e "SELECT user,authentication_string,plugin,host FROM mysql.user;"

Note that you you do not need to provide a username or password in this command to run this query. This is because you are authenticating with the auth socket method. You should receive a list that looks something like this.

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *A1B72F6785627C8A4D01FD750BB94EFDC3C4C272 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

To complete our WordPress installation we have two basic solutions: change the root account to use a password or create a user account for WordPress to use.

Solution #1: Change root to use a password

The most common solution that I found across the Internet was to change the root account to use a hashed password again. Effectively, this undoes the security improvement putting the environment in a less secure state. For this reason, this is not the solution I would recommend. This solution could be your only option, however, for compatibility with other services and solutions. (Rumor has it that phpMyAdmin requires the root account to use a password. I have not confirmed this yet.)

To change the root account to use a hashed password and set it, run the following command.

sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';"

With this change in place you can now create the WordPress database using the previous method.

# requires the installation of WP-CLI
wp config create --dbname=wordpress_local --dbhost=localhost --dbuser=root --dbpass=<password>
wp db create

You will also need to specify the username and password in any future mysql commands.

Solution #2: Create a WordPress database user

I like this option personally and this is what I am using in my WSL environment. It keeps the root account more secure and creates a dedicated service account for WordPress to access MySQL. If the mysql database will be shared with multiple applications then you can limit this accounts access to just the WordPress content.

Here is the command that I use to create the WordPress user in MySQL.

sudo mysql <<WPUSER
INSERT INTO mysql.user (User,Host,authentication_string,ssl_cipher,x509_issuer,x509_subject) VALUES('wpuser','localhost',PASSWORD('<password>'),'','','');
GRANT ALL PRIVILEGES ON *.* TO 'wpuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
WPUSER

I am granting all privileges to this account, so I don’t necessarily recommend this for a production environment. I am using this method exclusively for WordPress in a development and testing context on WSL.

With this user created, our WordPress database creation command can now written and executed like this.

# requires the installation of WP-CLI
wp config create --dbname=wordpress_local --dbhost=localhost --dbuser=wpuser --dbpass=<password>
wp db create

Ready to WordPress

Now you can work through Jason’s procedure on Ubuntu 18.04 to get your own local WordPress install. Just use one of the solutions above and your installation should be good to go.

Advertisement