MariaDB Master-Slave replication ensures that data from a Master server is replicated across many servers. We’ll look at how data can be moved from one database on a Master node to another on a Slave system.

As part of our Server Management services, Bobcares provides solutions for every query.

Take a look at how our Server administration team handles MariaDB Master-Slave replication.

MariaDB Master-Slave replication Step by Step

Data from a Master server is replicated across several MariaDB servers using Master-Slave replication. This ensures data redundancy and prevents data loss if the Master node goes down.

The replication setup is as follows:

Master node (CentOS 7 64 bit) : IP 173.82.2.236
Slave node: (CentOS 7 64 bit) : IP 173.82.94.57

Step 1: Install MariaDB on both the Master and Slave node

To begin, log in to both the master and slave nodes and run the MariaDB server installation commands.

yum install mariadb-server mariadb

Start the MariaDB service and set it to start automatically when the server boots up.

systemctl start mariadb
systemctl enable mariadb

Step 2: Set MariaDB password on both Master and Slave

MariaDB/MySQL passwords are typically left blank by default, allowing unauthorised users to access the database. We must secure it by configuring a password and hardening it with a few additional settings. Run the following command on both the master and slave nodes to accomplish this.

mysql_secure_installation

Step 3: Configuring the Master node

Let’s configure the Master node now that we’ve hardened our MariaDB instances on both nodes.
First, we must allow MariaDB’s port 3306 to pass through the firewall. Run the following commands to accomplish this.
firewall-cmd --add-port=3306/tcp --zone=public --permanent

Reload the firewall to see the changes take effect.
firewall-cmd --relaod

Secondly, Make a few changes to the /etc/my.cnf file
vim /etc/my.cnf

We need to append the following lines in the [mysqld] section


[mysqld]
server_id=1
log-basename=master
log-bin
binlog-format=row
binlog-do-db=replica_db
[...]


Here, replica db refers to the database that will be created and replicated across the slave.
After that, use the following command to restart the MariaDB service:
systemctl restart mariadb

Now we are going to login to MariaDB as root user:
mysql -u root -p

Create the replica db database as the next step.
MariaDB [(none)]> CREATE DATABASE replica_db;

Create a Slave user with a password. We’ll use slave_user as the Slave username and P@ssW@Rd1890 as the password, for example:

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘P@ssW@Rd1890’;
Query OK, 0 rows affected (0.00 sec)

Then, as shown, flush the privileges.

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

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Then, to see the master status, run the command below.
SHOW MASTER STATUS;

Step 4: Backing up the database in Master server and transferring it to the Slave

Then, to back up all of the Master databases, run the command below.
mysqldump --all-databases --user=root --password --master-data > masterdatabase.sql

In the current working directory, it will create a file called masterdatabase.sql .
Again We need to login to MySQL as root user:
mysql -u root -p

And, unlock the tables:

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

The masterdatabase.sql file should now be copied to the slave server.
So the command will be:
scp masterdatabase.sql root@173.82.94.57:/home

Please keep in mind that our MariaDB slave server is located at 173.82.94.57.

Step 4: Configuring MariaDB Slave

It’s now time to set up the MariaDB Slave node.

Edit the /etc/my.cnf file
vim /etc/my.cnf

We need to append the following entries under the [mysqld] section as shown


[mysqld]
server-id = 2
replicate-do-db=replica_db
[...]


The database created on the Master Server node is replica db. Also, keep in mind that the master and slave servers should have different server-ids. The server-id is 2 in this case.

Save. Then we can exit the file.

mysql -u root -p < /home/masterdatabase.sql

Remember that the masterdatabase.sql file from the master server had already been copied to the slave server’s /home/ directory.

In order to apply the changes, restart the MariaDB service.
systemctl restart mariadb

Now log in as the root user to MariaDB.
mysql -u root -p

Stop the slave server. Instruct the Slave server where to find the Master Log file and start the Slave server

MariaDB [(none)]> STOP SLAVE;

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=’173.82.2.236′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’P@ssW@Rd1890′, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=473;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

Then, to see the slave’s status, run the command below.
MariaDB [(none)]> SHOW SLAVE STATUS\G;

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, Bobcares’ expert engineers demonstrated how to handle MariaDB Master-Slave replication.