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.
Related Posts:
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
Leave a Reply