Step by Step guide for creating Master Slave replication in MariaDB
Last updated on February 8th, 2018 at 06:22 pm
In our earlier tutorials,we have already learned to install & configure MariaDB & also learned some basic administration commands for managing MariaDB. We are now going to learn to setup a MASTER SLAVE replication for MariaDB server.
Replication is used to create multiple copies of our database & these copies then can either be used as another database to run our queries on, queries that might otherwise affect performance of master server like running some heavy analytics queries or we can just use them for data redundancy purposes or for both. We can automate the whole process i.e. data replication occurs automatically from master to slave. Backups are be done without affecting the write operations of the master
So we will now setup our master-slave replication, for this we need two machines with Mariadb installed. IP addresses for the both the machines are mentioned below,
Master – 192.168.1.120 Hostname- master.ltechlab.com
Slave – 192.168.1.130 Hostname – slave.ltechlab.com
Once MariaDB has been installed in those machines, we will move on with the tutorial. If you need help installing and configuring maridb, have a look at our tutorial HERE.
Step 1- Master Server Configuration
We are going to take a database named ‘important’ in MariaDB, that will be replicated to our slave server. To start the process, we will edit the files ‘/etc/my.cnf’ , it’s the configuration file for mariadb,
& look for section with [mysqld] & then enter the following details,
Save & exit the file. Once done, restart the mariadb services,
Next, we will login to our mariadb instance on master server,
& then will create a new user for slave named ‘slaveuser’ & assign it necessary privileges by running the following command
Note:- We need values from MASTER_LOG_FILE and MASTER_LOG_POS from out of ‘show master status’ for configuring replication, so make sure that you have those.
Once these commands run successfully, exit from the session by typing ‘exit’.
Step2 – Create a backup of the database & move it slave
Now we need to create backup of our database ‘important’ , which can be done using ‘mysqldump’ command,
Once the backup is complete, we need to log back into the mariadb & unlock our tables,
& exit the session. Now we will move the database backup to our slave server which has a IPaddress of 192.168.1.130,
This completes our configuration on Master server, we will now move onto configuring our slave server.
Step 3 Configuring Slave server
We will again start with editing ‘/etc/my.cnf’ file & look for section [mysqld] & enter the following details,
We will now restore our database to mariadb, by running
When the process completes, we will provide the privileges to ‘slaveuser’ on db ‘important’ by logging into mariadb on slave server,
Next restart mariadb for implementing the changes.
Step 4 Start the replication
Remember, we need MASTER_LOG_FILE and MASTER_LOG_POS variables which we got from running ‘SHOW MASTER STATUS’ on mariadb on master server. Now login to mariadb on slave server & we will tell our slave server where to look for the master by running the following commands,
Note:- Change details of your master as necessary.
Step 5 Testing the replication
We will now create a new tables in our database on master to make sure if the replication is working or not. So, login to mariadb on master server,
select the database ‘important’,
and create a table named test in the db,
then insert some value into it,
To check the added value,
& you will find that your db has a table has the value you inserted.
Now let’s login to our slave database to make sure if our data replication is working,
You will see that the output shows the same value that we inserted on the master server, hence our replication is working fine without any issues.
This concludes our tutorial, please send your queries/questions through the comment box below.