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

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.

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.

One virtual IP address for both write and read queries

Master configuration

! Configuration File for keepalived
global_defs {
   vrrp_skip_check_adv_addr
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    virtual_router_id 51
    priority 105
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.1.1
    unicast_peer {
            192.168.1.2
    }
    virtual_ipaddress {
        192.168.1.100
   }
}

Slave configuration

! Configuration File for keepalived

global_defs {
   vrrp_skip_check_adv_addr
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.1.2
    unicast_peer {
            192.168.1.1
    }
    virtual_ipaddress {
        192.168.1.100
   }

One virtual IP address for write queries and one virtual IP address for read queries

Suppose you choose this option in your configuration, you then expand the configuration so that you have two virtual IPs. The master VPS has the priority for one IP address, the slave for the other. For example, the configuration of this would look like this:

Master configuration

! Configuration File for keepalived

global_defs {
   vrrp_skip_check_adv_addr
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    virtual_router_id 51
    priority 105
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }

    unicast_src_ip 192.168.1.1
    unicast_peer {
            192.168.1.2
    }

    virtual_ipaddress {
        192.168.1.100
   }
}

vrrp_instance VI_2 {
    state BACKUP
    interface eth1
    virtual_router_id 52
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.1.1
    unicast_peer {
            192.168.1.2
    }
    virtual_ipaddress {
        192.168.1.101
   }
}

Slave configuration

! Configuration File for keepalived

global_defs {
   vrrp_skip_check_adv_addr
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.1.2
    unicast_peer {
            192.168.1.1
    }

    virtual_ipaddress {
        192.168.1.100
   }
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    virtual_router_id 52
    priority 105
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.1.2
    unicast_peer {
            192.168.1.1
    }
    virtual_ipaddress {
        192.168.1.101
   }
}

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 (systemctlstopkeepalived) 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);
?>

Explanation of code

• For the IP address in the variable $write_server, use the Keepalived IP address if you use an automatic failover setup with Keepalived, or the private network IP address of your Master VPS if you do not use Keepalived.
   
• For read queries, set the variable $read_server on web server 1 to send read queries to the SQL master and on web server 2 to the SQL slave. If your master carries out a lot of write queries intensively, you may want to send all read queries to your slave.
  Optional: If you do not use automatic failover, send all read and write queries to the virtual Keepalived IP address.
   
• The $write_username and $read_username are your SQL users and are the same for both fields, as well as the respectively associated passwords.
   
• The reader lets you execute all read queries (SELECT .... FROM ....) in your PHP code. All other queries are done by the writer.

  The ins and outs of PHP code fall outside the scope of this tutorial series. 

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.

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 Configuration1 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 underScript events and our article on split-brain, for more information about working with MaxScale events and scripts).   

You are now doneconfiguring 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.