Configure Two MariaDB Servers for Master-to-Master Replication
This tutorial will guide you through the setup of an SQL master-to-master replication. If you’re unfamiliar with the concept, a master-to-master replication involves a group of computers or servers capable of both storing and updating data collectively.
In this instance, we will pair two servers, allowing them to replicate data bidirectionally. This setup designates each server as both a master and a slave database server simultaneously.
Requirements
For this tutorial we shall assume that we have the following already set-up and ready to be re-configured:
– 2 servers with pre-installed MariaDB server (If you haven’t done this yet there is a tutorial in this website[1] that you can follow)
– A sample database
– Root Access to the server
– An SSH client (You can download putty[2] or bitvise[3] depends on your operating system and liking)
The servers I have are configured with the following information. While your server IP may differ, rest assured the process remains identical. Please proceed without confusion.
server_a (192.168.0.12)
server_b (192.168.0.15)
The designated sample database for this process will be named replicate.db. Currently, this database is devoid of any data.
Once you have all the prerequisites in place, we can initiate the setup for our master-to-master replication.
Server_a Installation Guide
This will serve as our initial master, and to enable replication, we need to make edits to the configuration files. Let’s proceed with the necessary adjustments.
sudo nano /etc/my.cnf
And under the [mysqld] section you have to add the following information.
log-bin
server_id=1
replicate-do-db=replicate
bind-address=192.168.0.12
Next, restart our sql server.
systemctl restart mariadb
Log in to the MariaDB server as the root user and create a master user with the required grants. Please ensure you provide the essential details for both master_username & master_password..
CREATE USER '$master_username'@'%' IDENTIFIED BY '$master_password';
GRANT REPLICATION SLAVE ON *.* TO '$master_username'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
Executing the command ‘SHOW MASTER STATUS’ will provide you with the current binary log, indicating the precise location from which the other master should commence replication.
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 626 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Make sure to take note of both the position and file details. This information will be crucial for subsequent steps in the process.
Server_b Installation Guide
Before proceeding with replication, ensure you copy the identical sample database from server_a to this server. This server will serve as our second master, and we’ll now proceed to make the necessary edits to the configuration files to enable replication.
sudo nano /etc/my.cnf
And under the [mysqld] section you have to add the following information.
log-bin
server_id=2
replicate-do-db=replicate
bind-address=192.168.0.15
Next, restart our sql server.
systemctl restart mariadb
Login to the MariaDB server as root user and create a master user with necessary grants. Please note that you have to supply the following details for master_username & master_password.
CREATE USER '$master_username'@'%' IDENTIFIED BY '$master_password';
GRANT REPLICATION SLAVE ON *.* TO '$master_username'@'%';
FLUSH PRIVILEGES;
The next step involves transferring the information we obtained from server_a and applying it to this server. Please type the following commands:
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST = '192.168.0.12', MASTER_USER = '$master_username', MASTER_PASSWORD = '$master_password', MASTER_LOG_FILE = 'mariadb-bin.000001', MASTER_LOG_POS = 626;
SLAVE START;
SHOW MASTER STATUS;
Execute the command ‘SHOW MASTER STATUS’ on server_b, and it will provide you with the current binary log information. This data signifies the exact location from which the other master (in this case, server_a) should commence replication.
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
| mariadb-bin.000002 | 635 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Make sure to take note of both the position and file details. This information will be crucial for subsequent steps in the process.
Completing the replication on server_a
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST = '192.168.0.15', MASTER_USER = '$master_username', MASTER_PASSWORD = '$master_password', MASTER_LOG_FILE = 'mariadb-bin.000002', MASTER_LOG_POS = 635;
SLAVE START;Master-to-master replication testing
CREATE TABLE replicate.sample (`name` varchar(18));
Login to server_b, you should be able to see the new ‘sample’ table on the replicate database.
SHOW TABLES IN replicate;
You should see the following information:
+---------------------+
| Tables_in_replicate |
+---------------------+
| sample |
+---------------------+
1 row in set (0.00 sec)
To confirm that replication is functioning correctly from server_b to server_a, execute the same SQL commands on server_b and check for the existence of the table on the other server. If the table is present, it indicates that our master-to-master replication is working as expected.
[1]: https://www.blendhosting.com/community/tutorials/8/installing-lamp-linux-apache-mysql-and-php-stack-on-centos-7-64bit/
[2]: https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html
[3]: https://www.bitvise.com/ssh-client-download