From my previous article How I Stood Up WordPress In a Day, we stood up a “Quick and Dirty” version. It was a fast and easy setup but an all in one. What happens if your WordPress site really takes off? This is not highly scalable as it is limited to the resources of the box. Perhaps your hosting provider lets you increase the size of the VM.
Eventually you will get to a point where you reach the max. This is called vertical scaling. It is one of the easier methods but only gets so far and leads to monolithic infrastructures.
We need to be able to horizontally scale but adding highly redundant nodes. The database is the first piece of this. Since we implemented in Google Cloud, we will be using their managed SQL instance. In AWS this is called RDS.
Another issue we do not yet address is the fact that images are stored locally on the WordPress server itself.
We will address that later on and provide a link to that article. Here is our article on that – Moving WordPress Images To Google Storage.
With that said, removing MySQL server from the WordPress server does leave more resources for the WordPress server itself.
Always run a backup before a major change. In this case we use Google Disk Snapshots for our Google VM and took one before.
Provisioning Google MySQL
For this, we opted the “create” method. Google does have the “migrate” option which involves adding the new instance as a read replica. This is a small WordPress site so we will simply create a backup and restore it and go from there.
Connecting to Google MySQL Instance
Here we will connect from the VM to the instance as root. You can see the Server version includes “Google”. We will then create the wordpress database and access. This is not the most secure of GRANT but we are copying what was there. It can be locked down based on best practices. The CREATE options will be highly dependent on your existing setup and we’ll talk about it further in the troubleshooting options
$ mysql -h 10.30.128.3 -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 50 Server version: 5.7.14-google-log (Google) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE wordpress CHARACTER SET latin1 COLLATE latin1_swedish_ci; mysql> GRANT ALL PRIVILEGES ON wordpress.* TO "wordpress"@"%" IDENTIFIED BY "XXXXXXXXXX"; mysql> FLUSH PRIVILEGES; mysql> quit;
Now we need to backup the existing wordpress database
$ mysqladmin -u wordpress -p wordpress > 20191107-wordpress.sql
And then import it
$ mysql -h 10.30.128.3 -u wordpress -p wordpress < 20191107-wordpress.sql Enter password: $
In the Google console we can then validate this.
We need to modify wp-config.php as follows. If your user and password changed, those need to be updated as well.
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define( 'DB_NAME', 'wordpress' ); /** MySQL database username */ define( 'DB_USER', 'wordpress' ); /** MySQL database password */ define( 'DB_PASSWORD', 'XXXXXX' ); /** MySQL hostname */ #define( 'DB_HOST', 'localhost' ); define( 'DB_HOST', '10.30.128.3' );
Once you save the changes go to your WordPress site and test. If you get an install.php page, stop right there and back out the change. We have some troubleshooting steps below.
If validation is successful, run a backup again, both in for the Google disk snapshot for the VM and in the Google MySQL instance. This way we have a known good immediately following the migration.
It is highly recommended to backup before you perform any of the short or long term decommission.
There are a few causes to get redirected to the install.php page
- Incorrect database settings, including host, user, password, database name and table prefixes
- Collation/Characterset – case insensitive versus sensitive
- Not actually importing the database
- Improper wordpress user permissions
This can be checked using the following commands. It is best to keep the same settings when creating. It is also described here – https://stackoverflow.com/questions/9827164/wordpress-keeps-redirecting-to-install-php-after-migration
mysql> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | latin1 | latin1_swedish_ci | +--------------------------+----------------------+ 1 row in set (0.00 sec)
The others can be wrapped up into database connectivity. We tested this by connecting as the wordpress user and importing as that user after the database was connected.
Decomissioning old database
At each of these steps it is important to test the site to ensure it doesn’t break. If you are still somehow pointing to your local mysql instance, it can break. You will definitely find that out during these steps.
We do not want old mysql data laying around so the first steps to complete afterwards are to disable and shutdown mysql. This also helps us confirm we are using the new MySQL instance.
$ sudo systemctl stop mysql $ sudo systemctl disable mysql Synchronizing state of mysql.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install disable mysql Removed /etc/systemd/system/multi-user.target.wants/mysql.service.
Long term we want to delete the backup so we don’t have extra data laying around and remove mysql and its database files.
$ sudo apt-get remove --purge mysql-server mysql-community-server
Now let’s remove our backup.
$ rm 20191107-wordpress.sql
At this point we have accomplished scaling to a potentially highly available database. This database also can be dynamically sized to accommodate extra load. For the sake of this article, we chose the smallest size possible due to the current load. Should this go viral though, the database can easily be scaled.