In 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

• 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)

• One Week From Tomorrow…THE WORLD WILL LOSE THEIR MINDS!Lines Are Already Forming! - November 21, 2024
• Crunchbits Discontinuing Popular Annual Plans – The Community Mourns! - November 20, 2024
• RackNerd’s Black Friday 2024: Bigger, Better, and Now in Dublin! - November 19, 2024