Scaling databases for WordPress can be hard – just getting a bigger server is expensive and isn’t the most appropriate way of addressing the root issues you may be facing.
Here I will be discussing database replication with AWS RDS instances and how it can then applied to WordPress using Automattic’s own HyperDB. HyperDB helps the folks over at wordpress.com to create scalable, highly available and failover-safe environments.
Server replication is one of the many things that AWS RDS makes easy to set up. Simply log into your AWS console; head over to the RDS control panel and click into your WordPress database instance. In the top right of the RDS instance page you will see the Instance Actions drop-down. Press and select ‘Create (Aurora) read replica’. Go through the steps to setup the instance. It’s a good idea to create this new read replica in another availability zone so it’s highly available. To manually set this up with SQL servers, you can take a look at this guide from MySQL.
After finishing the setup, the instance will be created and the databases synced. Connect to the read replica and check the status of the replication using the command SHOW SLAVE STATUS and wait until ‘SQL_remaining_delay’ is 0. This means the database has been fully replicated across.
Next, we will set up WordPress to see both databases: to read from the freshly created read replica, and write (and maybe read) from the master (original) database.
Download https://wordpress.org/plugins/hyperdb/ (Note this is not installed as a plugin) from the plugin directory and move the file named db.php into your wp-content directory. This file is the brains of HyperDB and allows numerous ways of optimising and configuring your database setup for WordPress, from partitioning to replication and failover.
Here I will just be showing you how to set up the master DB with a read-only replica. Now that HyperDB is ready and waiting for our configuration we tell it what databases to use in the db-config.php file. This is placed in your web root (where your wp-config.php file should be). The snippet below shows an example database setup.
First, we define the default server connection. With this, WordPress will just act like normal. The DB connection settings are pulled from wp-config.php where they should be defined as in the default WordPress setup. The attributes to note are ‘read’ and ‘write’. These specify a priority for HyperDB to decide which server is used for reading or writing.
