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.236Copy Code

Slave node: (CentOS 7 64 bit) : IP 173.82.94.57Copy Code

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 mariadbCopy Code

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

systemctl start mariadbCopy Code

systemctl enable mariadbCopy Code

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_installationCopy Code

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 --permanentCopy Code

Reload the firewall to see the changes take effect.

firewall-cmd --relaodCopy Code

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

vim /etc/my.cnfCopy Code

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
[...]
Copy Code

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 mariadbCopy Code

Now we are going to login to MariaDB as root user:

mysql -u root -pCopy Code

Create the replica db database as the next step.

MariaDB [(none)]> CREATE DATABASE replica_db;Copy Code

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)Copy Code

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)Copy Code

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;Copy Code

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.sqlCopy Code

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 -pCopy Code

And, unlock the tables:

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

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:/homeCopy Code

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.cnfCopy Code

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

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

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.sqlCopy Code

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 mariadbCopy Code

Now log in as the root user to MariaDB.

mysql -u root -pCopy Code

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

MariaDB [(none)]> STOP SLAVE;Copy Code

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;Copy Code

[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.