This is the fifth part of our Tutorial series: 'Setting up a redundant VPS environment'. If you are setting up a new redundant VPS environment, we recommend starting with part 1 and to not skip any parts.
In this part, we show you how to combine a MariaDB master-slave setup with MaxScale on your PHP application/website, or your WordPress website. In this part, we cover this matter in the following topics:
- Options to link your database
- Installing and configuring Keepalived
- Linking your database to a PHP application
- Linking your database to a WordPress website
Follow all steps in this article using a user with root privileges, unless mentioned otherwise.
Options for linking your database
There are several options to link your database to your application: In your application, you can send SQL queries to the private network IP address of your master VPS and, for example, the read queries to the slave VPS, or all queries to the master. However, this is not very flexible if you use a manual or automatic failover.
Another and probably 'easier' way to link your SQL database to your website or application with more flexibility, regardless of whether you use automatic or manual failover, is to use a virtual IP address for write and read queries. You provide your VPS with this virtual IP address via Keepalived.
A virtual IP address of Keepalived links a fictitious/virtual IP address which is linked to one or more existing IP addresses on one or more servers. This virtual IP address automatically transfers from one VPS to the other during downtime. It is a fictitious address that does nothing other than regulate to which existing IP address traffic is being directed.
The big advantage of using a virtual IP address is that in your website or application, you simply set up this virtual IP address for write and/or read queries. For example, it does not matter which VPS is the master. As long as the master has the virtual IP address, write queries get to the right server. We recommend using one of the following three configurations:
- One virtual IP address for both write and read queries: All SQL queries go to the master VPS. In the case of a failover, the slave VPS becomes the new master VPS and receives the virtual IP address. So, the slave VPS will then process all SQL queries in the event of a failover.
- One virtual IP address for write queries and one virtual IP address for read queries: For a light form of load balancing, you can set two virtual IP addresses: one address for write queries to the master, and one address for read queries to the slave.
-
One virtual IP address for write queries and HA-Proxy for read queries: An elegant solution would be to have one virtual IP address send write queries to your master VPS and use HA-Proxy for read queries to distribute read queries over all your linked database servers. HA-Proxy, however, falls outside the scope of these tutorials.
HA-IP and HA-IP Pro cannot be used on private networks at the time of writing. If/when this is implemented, we will adapt this manual and this will be the preferred method.
The first two examples above are explained in more detail in this article.
In case a failover occurs, keep in mind that you check whether the virtual IP address has passed to the slave or not (this depends, among other things, on the cause of the failover). If not, you manually disable Keepalived on the master with the command:
systemctl stop keepalived
Installing and configuring Keepalived
We use a virtual IP address which we get from Keepalived for the write actions. This virtual IP address is a 'fake' IP address that is linked to an existing IP address. You have control over which server this virtual IP address is linked to and it can also automatically switch to another VPS when a VPS becomes unreachable. Needless to say it's a useful tool for many redundant scenarios.
Execute all the steps below on all VPSs in your cluster as a root user.
Step 1
Install Keepalived using the command below.
CentOS:
yum -y install keepalived
Debian / Ubuntu:
apt-get install keepalived
Step 2
Turn on Keepalived and ensure that it automatically enables when your VPS is restarted.
systemctl enable keepalived
Step 3
Open the Keepalived configuration file using your favourite editor, for example:
nano /etc/keepalived/keepalived.conf
This file does not yet exist in Ubuntu. You will see an empty file on Ubuntu.
Step 4
Make sure that the content of the file looks like the examples below. For the sake of clarity, the contents of both the master and the slave are included here for the two aforementioned scenarios:
- One virtual IP address for both write and read queries
- One virtual IP address for write queries and one virtual IP address for read queries
An explanation follows among the examples and shows which data you need to adjust to that of your own VPS.
Please note: in the case of CentOS, there is probably considerably more code in this configuration file. In that case, delete the entire content and replace it with the above.
- state MASTER / BACKUP: Specify state MASTER in the configuration of your master VPS and state BACKUP on the slave VPS(s).
- interface eth1: Enter the name of your network interface here. You check your network interface name with the command ip a. In this case, we use the interface of the private network
- virtual_router_id: Enter a unique number per IP address (not per VPS).
- priority: The highest number gets priority over the lowest. So, enter a higher number for the master VPS than on the slave(s).
- unicast_src_ip: Enter the IP address of the VPS on which you go through these steps. We use the IP address of the private network here.
- unicast_peer: Enter the IP addresses of the other VPSs in your database cluster, placing each IP address on a separate line.
- virtual_ipaddress: Enter the virtual IP address you want to use. In principle, it does not matter which virtual IP address you use, as long as you enter the same virtual IP address on the master and slave(s) and that is on the same subnet as the IP addresses of your private network.
Step 5
Then restart keepalived to process the changes.
systemctl restart keepalived
If you now view your IP addresses with ip a, you will see the virtual IP address on your master. It will not be visible on your slave VPS(s).
Tip: Immediately test your virtual IP by disabling it on your master (systemctl stop keepalived) and viewing your IP addresses on your slave (ip a). Do not forget to turn on keepalived after your test (systemctl start keepalived).
Finally
In your application, depending on the choices you have made previously, you now configure all SQL queries on your primary Keepalived IP address that is linked to your master VPS. Read queries may be executed on the secondary IP address that is currently linked to your slave VPS or on your master VPS.
Below, we show how this works with two virtual IPs for PHP and WordPress. These principles also work the same in other types of applications (e.g. Java).
Linking your database to your PHP application
If you use one virtual IP address for write and read queries, no special adjustments are required. The only thing you change in your PHP configuration is that you use the virtual IP address to write SQL queries, for example:
<?php
/* Database settings */
$server = "192.168.1.100";
$username = "username";
$password = "password";
$link_to_server = mysqli_connect(
$server,
$username,
$password,
'wordpress');
if (!$link_to_server) {
printf("Unable to connect to write server. Error: %s", mysqli_connect_error());
exit;
}
mysqli_close($link_to_server);
?>
If you use two virtual IPs, or one virtual IP and HA-Proxy (or a specific address for read queries), then you define a write and read server separately in PHP. Below is an example of how you subsequently separate the write and read queries in PHP, with a short explanation of the configuration under the code.
<?php
/* Write settings */
$write_server = "192.168.1.100";
$write_username = "user";
$write_password = "password";
/* read settings */
$read_server = "192.168.1.101";
$read_username = "user";
$read_password = "password";
$link_to_writer = mysqli_connect(
$write_server,
$write_username,
$write_password,
'wordpress');
if (!$link_to_writer) {
printf("Unable to connect to write server. Error: %s", mysqli_connect_error());
exit;
}
$link_to_reader = mysqli_connect(
$read_server,
$read_username,
$read_password,
'wordpress');
if (!$link_to_reader) {
printf("Unable to connect read server. Error: %s", mysqli_connect_error());
exit; }
mysqli_close($link_to_writer);
mysqli_close($link_to_reader);
?>
Linking your database to a WordPress site
If you use a single virtual IP address (see the Keepalived section), then your configuration is very simple. In wp-config.php, you only adapt your database to the virtual IP address:
// ** MySQL settings ** // /** The name of the database for WordPress */ define( 'DB_NAME', 'WordPress' ); /** MySQL database username */ define( 'DB_USER', 'example' ); /** MySQL database password */ define( 'DB_PASSWORD', 'your_password' ); /** MySQL hostname */ define( 'DB_HOST', '192.168.1.100' );
If you use one virtual IP address for write queries and another IP address for read queries, then there is more to it and you should use the steps below.
WordPress is the most used CMS system in the world. A thorough support for database clusters is therefore more than desirable. Fortunately there's excellent database cluster support in the form of an advanced plugin: HyperDB. With this plugin, your WordPress website uses the database cluster you configured in the previous two parts.
Follow the steps below on all VPSs in your master/slave setup.
Step 1
First add the HyperDB plugin to your WordPress website(s). This works slightly different with HyperDB than with regular plugins: The files that HyperDB uses are not placed in the standard plug-in directory. So, first, get HyperDB directly on your VPS (the latest version can be found here):
wget https://downloads.wordpress.org/plugin/hyperdb.1.8.zip
Step 2
Next, unpack hyperdb.1.8.zip and then remove the .zip file:
unzip hyperdb.1.8.zip rm -f hyperdb.1.8.zip
Step 3
HyperDB comes with 2 files: db-config.php and db.php. Place db-config.php in the same directory that contains wp-config.php and db.php in the /wp-content/ directory. If you place the files somewhere else, your WordPress site will no longer work.
mv /home/transip/hyperdb/db-config.php /var/www/example.com/ mv /home/transip/hyperdb/db.php /var/www/example.com/wp-content/
Please note that you should change the directory above to the actual directory which contains your WordPress website.
Step 4
Open the db-config.php file with your favourite editor, for example:
nano /var/www/voorbeeld.nl/db-config.php
Step 5
Search for the code below in this file.
/** Sample Configuration 1: Using the Default Server **/ /** NOTE: THIS IS ACTIVE BY DEFAULT. COMMENT IT OUT. **/
We will use sample 1, but not sample 2. Comment out sample 2 as shown in the example below or delete this piece in its entirety.
/** Sample Configuration 2: Partitioning **/
/**
* This example shows a setup where the multisite blog tables have been
* separated from the global dataset.
*
*
*$wpdb->add_database(array(
* 'host' => 'global.db.example.com',
* 'user' => 'globaluser',
* 'password' => 'globalpassword',
* 'name' => 'globaldb',
*));
*$wpdb->add_database(array(
* 'host' => 'blog.db.example.com',
* 'user' => 'bloguser',
* 'password' => 'blogpassword',
* 'name' => 'blogdb',
* 'dataset' => 'blog',
*));
*$wpdb->add_callback('my_db_callback');
*function my_db_callback($query, $wpdb) {
* // Multisite blog tables are "{$base_prefix}{$blog_id}_*"
* if ( preg_match("/^{$wpdb->base_prefix}\d+_/i", $wpdb->table) )
* return 'blog';
*}
*/
Step 6
Now scroll back to Sample Configuration 1 and adjust the code as in the example below.
$wpdb->add_database(array( 'host' => DB_HOST, // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 1, 'read' => 0, )); /** * This adds the same server again, only this time it is configured as a slave. * The last three parameters are set to the defaults but are shown for clarity. */ $wpdb->add_database(array( 'host' => DB_SLAVE, // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, ));
- The first part is the configuration of the master. Here, the write and read lines are added. This means that the master only performs write actions (we get back to read actions in step 8).
- In the second part, you change the name DB_HOST to DB_SLAVE. Leave the settings as they are. We will get back later on how you ensure that the master and slave can change roles in the event of a failover.
- If you want to add a third or fourth slave, repeat the lower part but change the name to DB_SLAVE1, DB_SLAVE2, etc.
Finally, save the changes and close the file (ctrl + x> y).
Step 7
Then open your WordPress configuration file.
nano /var/www/voorbeeld.nl/wp-config.php
Step 8
Adjust the next piece ...
/** MySQL hostname */
define ('DB_HOST', 'localhost');
... so that it looks like below. Modify the IPs to your Keepalived Virtual IP and your private network IP address respectively (if you do not use Keepalived, use the private network IP address of your master). Set the private network on web server 1
/** MySQL master */
define ('DB_HOST', '192.168.1.100');
/** MySQL slave */
define ('DB_SLAVE', '192.168.1.101');
For read queries, set the variable DB_SLAVE to send read queries to the private network IP address of the SQL slave.
Optional: You are free to have wp-config.php on web server 1 write read queries to the private network IP address of the DB master and on web server 2 to the private network IP address of the DB slave. Please note that in that case, you set read=1 for the master. This can be more convenient if, for example, you want to distribute the load proportionately and not many write queries are executed on the WordPress website.
Finally, save the changes and close the file (ctrl + x> y).
What to do in case of a failover situation
Suppose the situation occurs in which your SQL master goes offline. MaxScale then ensures that your slave becomes the new master manually or automatically. The Keepalived IP address will change automatically regardless. When the old master comes online, it will be added as a slave but will receive the Keepalived IP address again.
This would lead to write queries no longer being written to the VPS which is currently working as a master. In such a case, we recommend first disabling Keepalived on the old master, so it can synchronize any missed data. Then, you enable Keepalived again and you execute a manual switchover command (see the previous section), to promote the old master again to master of the cluster.
Alternatively, you can configure MaxScale on the original master VPS to use a script in case of a new_slave event (i.e. the old master is added as a new slave). For example, you can have the script then disable Keepalived on the original master (see this page under Script events and our article on split-brain, for more information about working with MaxScale events and scripts).
You are now done configuring your SQL cluster! We recommend checking the last part for additional tips for your database setup, such as useful management commands and additional MariaDB Monitor options.