In this tutorial series, we are setting up a highly available WordPress web site from scratch. 

Part 1 – Introduction, Considerations, and Architecture
Part 2 – Ordering the VPSes 
Part 3 – Ansible
Part 4 – Gluster
Part 5 – WordPress install
Part 6 – MariaDB Multi-Master (this article)
Part 7 – Round-Robin DNS, Let’s Encrypt, & Conclusion

We’re going to continue leveraging Gluster in order to make MariaDB setup easy.

What we’re using is MariaDB’s Galera cluster.  Galera will keep all three nodes in sync, and it’s multi-master so that if we write a post on node1, a visitor comments on node2, or another visitor replies on node3, all three databases will be immediately updated and reflect the same changes.

Here’s the state of our Gluster:

root@node1:/gluster/www.lowend.party# cd /gluster/
root@node1:/gluster# ll
total 13
drwxrwxrwx 5 root root 4096 Mar 2 19:13 .
drwxr-xr-x 21 root root 4096 Mar 2 19:09 ..
-rw-r--r-- 1 root root 50 Mar 2 19:11 testfile.txt
drwxrwxr-x 5 www-data adm 4096 Mar 2 19:31 www.lowend.party

Remember that we’ve got WordPress setup on node1, and in node1’s MariaDB.  On node1, let’s dump that database to our Gluster volume:

root@node1:/gluster# mysqldump wp > wp.sql
root@node1:/gluster# ll
total 44
drwxrwxrwx 5 root root 4096 Mar 2 19:32 .
drwxr-xr-x 21 root root 4096 Mar 2 19:09 ..
-rw-r--r-- 1 root root 50 Mar 2 19:11 testfile.txt
-rw-r--r-- 1 root root 31529 Mar 2 19:32 wp.sql
drwxrwxr-x 5 www-data adm 4096 Mar 2 19:31 www.lowend.party

Now import that on nodes 2 and 3:

mysql wp < /gluster/wp.sql

On all nodes, shut down MariaDB.

servicectl stop mariadb

On node1, create /etc/mysql/mariadb.conf.d/60-galera.cnf as follows:

[galera]
# Mandatory settings
wsrep_on = ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name = "MariaDB Galera Cluster"
wsrep_cluster_address = gcomm://5.78.68.150,5.78.91.194,5.78.74.126
binlog_format = row
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_sst_method=rsync

# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0

And put that file on Gluster, which means it’s immediately on all nodes:

cp /etc/mysql/mariadb.conf.d/60-galera.cnf /gluster

On nodes 2 and 3:

cp /gluster/60-galera.cnf /etc/mysql/mariadb.conf.d/

Initializing the Galera Cluster

On Node 1:

galera_new_cluster

That will start MariaDB also.

On Node 2 and 3:

systemctl start mariadb

On Node 1:

MariaDB [(none)]> SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size";
+--------------+
| cluster size |
+--------------+
| 3            |
+--------------+
1 row in set (0.001 sec)

Now let’s try it out!  I’m going to create a test table, based on my favorite TV show.

On Node 1:

MariaDB [(none)]> use wp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [wp]> create table hogans_heroes ( id integer, rank varchar(15), name varchar(30) );
Query OK, 0 rows affected (0.008 sec)

MariaDB [wp]> insert into hogans_heroes (id, rank, name) values 
( 1, 'Colonel', 'Robert Hogan');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into hogans_heroes (id, rank, name) values 
( 2, 'Sergeant', 'James Kinchloe');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into hogans_heroes (id, rank, name) values 
( 3, 'Corporal', 'Peter Newkirk');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into hogans_heroes (id, rank, name) values 
( 4, 'Corporal', 'Louis LeBeau');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into hogans_heroes (id, rank, name) values 
( 5, 'Sergeant', 'Andrew Carter');
Query OK, 1 row affected (0.002 sec)

Let’s take a look at node2’s MariaDB now.  On node2:

MariaDB [wp]> select * from hogans_heroes;
+------+----------+----------------+
| id   | rank     |  name          |
+------+----------+----------------+
| 1    | Colonel  | Robert Hogan   |
| 2    | Sergeant | James Kinchloe |
| 3    | Corporal | Peter Newkirk  |
| 4    | Corporal | Louis LeBeau   |
| 5    | Sergeant | Andrew Carter  |
+------+----------+----------------+
5 rows in set (0.000 sec)

Perfect!  But wait…in Season 6, Ivan Dixon (who played Sgt. James Kinchloe) left the show and was replaced by Kenneth Washington’s new character, Sgt.  Richard Baker.  Let’s make that update on node3:

MariaDB [wp]> delete from hogans_heroes where name = 'James Kinchloe';
Query OK, 1 row affected (0.003 sec)
MariaDB [wp]> insert into hogans_heroes (id,rank,name) values 
( 6,'Sergeant','Richard Baker');
Query OK, 1 row affected (0.002 sec)

Let's check if that got replicated to node1:

MariaDB [wp]> select * from hogans_heroes;
+------+----------+---------------+
| id.  | rank     | name          |
+------+----------+---------------+
| 1    | Colonel  | Robert Hogan  |
| 3    | Corporal | Peter Newkirk |
| 4    | Corporal | Louis LeBeau  |
| 5    | Sergeant | Andrew Carter |
| 6    | Sergeant | Richard Baker |
+------+----------+---------------+
5 rows in set (0.000 sec)

Our MariaDB Galera mutli-master replication is working flawlessly.

The only items remaining are to get round-robin DNS and Let’s Encrypt setup, and that’s what we’ll do in the concluding part to this tutorial tomorrow.

• Author
• Recent Posts

raindog308

Dread Lord of LowEnd Content at LowEndBox

I'm raindog308, techno polymath and long-time LowEndTalk community Administrator. My technical interests include all things Unix, perl, python, golang, shell scripting, vintage operating systems such as MVS, and relational database systems such as Oracle, PostgreSQL, and MySQL.

I also enjoy using LEBs! I have an empire of little guys for VPNs, larger guys for apps, and big guys for databases and storage. When I'm not in front a screen I'm into German Shepherd dogs, quality knives, target shooting, theology, tabletop roleplaying games, and forest hiking.

I enjoy writing technical articles here on LowEndBox to help people get more out of their systems. You can find me on LowEndTalk @raindog308

Latest posts by raindog308 (see all)

• MetWeb has a 30% Off Deal on Cheap VPS Offers in Utah for Our Readers! - December 21, 2024
• Is Your Soul as Dark as a Christmas Stocking’s Coal?Make Your Online World Match - December 20, 2024
• Hosteroid has a HOT, Limited Stock Offer in Vienna or Amsterdam! - December 19, 2024