LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

How to Setup a Highly Available WordPress Site From Scratch, Part 3

Tags: , , , , , Date/Time: December 28, 2020 @ 12:24 am, by raindog308

How to Setup a Highly Available WordPress Site From Scratch, Part 3In this tutorial series, we are setting up a highly available WordPress web site from scratch.

Part 1 – Introduction, Considerations, and Architecture
Part 2 – Setting Up the VPSes
Part 3 – Setting Up MariaDB Multi-Master Replication
Part 4 – File Replication and Setting Up DRBD
Part 5 – Setting Up OCFS2
Part 6 – Round-Robin DNS, Let’s Encrypt, & Conclusion

Setting up MariaDB Multi-Master Replication

On web1, setup /etc/mysql/maria.conf.d/replication.cnf:

[mysqld]
log-bin
server_id=1
bind-address=1.1.1.1
auto-increment-increment=2
auto-increment-offset=1

It’s best practice to set auto-increment-increment to the number of masters (in our case, 2).  auto-increment-offset should match the server ID for simplicity’s sake.  What these parameters control is how auto-increment fields are handled, to prevent primary key conflicts between masters.

In the same file on web2:

[mysqld]
log-bin
server_id=2
bind-address=2.2.2.2
auto-increment-increment=2
auto-increment-offset=2

The only things changed are server_id and bind-address.  You may ask why server_id uses an underbar and every other parameter name uses a hyphen.  I have wondered this as well.

Then restart Mariadb on each node.

systemctl restart mariadb

Now on web1, create the replication user.  I named mine ‘master’ but you can name it anything you want.  Lock all tables, but do NOT exit the session.  If you exit the session (quit, control-D, etc.) then the lock will be released:

MariaDB [(none)]> create user 'master'@'%' identified by 'complex-password';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant replication slave on *.* to 'master'@'%';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show master status;

+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |      329 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Make note of the file name and position number.

Now go to web2 and create the replication user:

MariaDB [(none)]> CREATE USER 'master'@'%' IDENTIFIED BY 'complex-password-88-kf';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'master'@'%';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

Next we’ll setup the web2 slave:

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '1.1.1.1', MASTER_USER = 'master', MASTER_PASSWORD = 'complex-password', MASTER_LOG_FILE = 'mysqld-bin.000001', MASTER_LOG_POS = 329, MASTER_HEARTBEAT_PERIOD = 60;
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

Note that we’re setting web2’s replication master to web1’s IP.  We’re using the log file name and log position we got from web1’s SHOW MASTER STATUS output.

I also recommend enabling the MASTER_HEARTBEAT_PERIOD parameter.  These sends a heartbeat across replication links every 60 seconds (in this case) so connections aren’t closed. 

You can check the slave status on node 2.  I’ll trim the output a bit:

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 1.1.1.1
                   Master_User: master
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysqld-bin.000001
           Read_Master_Log_Pos: 329
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.000 sec)

Here we see the parameters we entered above.  You also want to see both Slave_IO_Running and Slave_SQL_Running as ‘Yes’.  If you don’t see these as ‘Yes’, or if you see something under ‘Last_IO_Error’ or ‘Last_SQL_Error’ check /var/log/mysql/error.log for hints.  It probably means you’ve either typo’d something in the replication.cnf file, did not restart MariaDB, or made a mistake in your “CHANGE MASTER” command.

Now on web2, show master status:

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |      329 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Now flip back to web1 and setup replication there, unlocking tables first:

MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '2.2.2.2', MASTER_USER = 'master', MASTER_PASSWORD = 'complex-password',MASTER_LOG_FILE = 'mysqld-bin.000001', MASTER_LOG_POS = 329, MASTER_HEARTBEAT_PERIOD = 60;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)

Again you can use the “SHOW SLAVE STATUS” command:

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 2.2.2.2
                   Master_User: master
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysqld-bin.000001
           Read_Master_Log_Pos: 329
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.000 sec)

Now let’s test it.  On web1:

MariaDB [(none)]> connect wp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id:    42
Current database: wp
MariaDB [wp]> create table repltest (id integer, name varchar(30));
Query OK, 0 rows affected (0.007 sec)

MariaDB [wp]> insert into repltest (id,name) values (1, 'Thor');
Query OK, 1 row affected (0.003 sec)

MariaDB [wp]> insert into repltest (id,name) values (2,'Captain America');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into repltest (id,name) values (3,'Iron Man');
Query OK, 1 row affected (0.001 sec)

MariaDB [wp]> commit;
Query OK, 0 rows affected (0.000 sec)

Now on web2:

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

MariaDB [wp]> select * from repltest;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | Thor            |
|    2 | Captain America |
|    3 | Iron Man        |
+------+-----------------+
3 rows in set (0.000 sec)

OK, so web1->web2 replication is working.  Now let’s test in the opposite direction.  On web2:

MariaDB [wp]> create table repltest2 (id integer, name varchar(30));
Query OK, 0 rows affected (0.005 sec)

MariaDB [wp]> insert into repltest2 (id,name) values (1,'Donald Blake');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into repltest2 (id,name) values (2,'Steve Rogers');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> insert into repltest2 (id,name) values (3,'Tony Stark');
Query OK, 1 row affected (0.002 sec)

MariaDB [wp]> commit;
Query OK, 0 rows affected (0.000 sec)

And on web1:

MariaDB [wp]> select * from repltest2;
+------+--------------+
| id   | name         |
+------+--------------+
|    1 | Donald Blake |
|    2 | Steve Rogers |
|    3 | Tony Stark   |
+------+--------------+

3 rows in set (0.000 sec)

We’re now in bi-directional database replication.

If you get any errors, check the following:

(1) You don’t have a firewall blocking connections.

(2) There are a lot of details to type correctly – IPs, log file names, log positions, server_id, passwords, etc.  Carefully check everything.  Don’t be afraid to restart MariaDB, reset passwords, and redo the STOP SLAVE/CHANGE MASTER/START SLAVE commands.

Next Part: Part 4 – File Replication and Setting Up DRBD

I'm Andrew, techno polymath and long-time LowEndTalk community Moderator. My technical interests include all things Unix, perl, python, shell scripting, and relational database systems. I enjoy writing technical articles here on LowEndBox to help people get more out of their VPSes.

No Comments

    Leave a Reply

    Some notes on commenting on LowEndBox:

    • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
    • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
    • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

    Your email address will not be published. Required fields are marked *