Creating a Master-Slave Architecture with MariaDB
Did you know that it just takes a few steps to create a master-slave architecture with MariaDB?
While your coffee is cooling down, I'd like to quickly walk you through the steps to create your master-slave architecture. There are many database engines that can be used to create a master-slave replication. However, MariaDB has increased its popularity thanks to its great power and support for multiple web development languages such as PHP, Javascript and Django. Also, being an open source relational database with an incredible community behind it makes MariaDB your default option.
Now, let us look at how easy it is to create a master-slave replication with MariaDB! Before we start, we must ensure that both instances (the one that will be Master and the one that will be Slave) are in the same network. To verify this we can do a Ping test between them. For example, we could have a virtual machine in which we have an instance of MariaDB.
Master configuration
Step 1 -
Now, in our example MariaDB will be the Master. So we need to change the configuration of the my.cnf file. We need to delete all the content that is currently shown and should only leave the following content:
We must ensure that the server_id is unique for each one that is used. The log-basename is the file where the logs are stored.
Step 2 -
We now need to restart MariaDB (Master) to save the configuration. Once the instance has been restarted we should create the user whom we will assign the replication permissions with the following commands:
Step 3 -
Now we need to lock the tables so that they can only be seen using the following command:
Now we only need the position of the binary log with the command:
This will show us where our binary files are located and the full name. We need to save these two values (file, position) as we will use them for our Slave instance. Now we should finish the changes we need in the Master, but not before unlocking the tables with the following command:
UNLOCK TABLES.
Slave configuration
Step 4 -
We will now go to the my.cnf file to configure our Slave instance as follows:
We must ensure that server_id is different from 1, since this is the server of our Master. Once we have saved the my.cnf file, we proceed with restarting MariaDB in the SLAVE instance. If we are using Linux, this can be done using the following command:
Step 4 -
Once the my.cnf file is configured, we start MariaDB and we will use the following QUERY CHANGE MASTER TO:
ID and name will show now as 1; "John Doe"
We now have to take into account the following for the configuration:
MASTER_HOST: is the IP where our MASTER is located.
MASTER_USER and PASSWORD: is how we have configured our MASTER in step 2.
MASTER_LOG_FILE and POS: is the information given to us in step 3.
Now we have created the connection from the Slave to our MariaDB MASTER and we start the Slave with the following command:
To check that our connection was made correctly we can run the following command:
If the replication has been created correctly, we will see a result with the following values:
Step 6 -
This is the end of our configuration. We just have to do final testing, e.g. we will create a database called "STUDENTS" and within the database we create a table with the following characteristics (id and name) that is called by the same name.Inside the table we will insert to the column "name" the value "John Doe" we will make a QUERY in order to know that it was inserted and check that it was done.
Id and name will now show up as 1; "John Doe".
Step 7 -
We now need to get back to our SLAVE instance and check if the replication has been created. To do this, we make the QUERY and verify whether the value we inserted has been replicated.
Again, Id and name will now show up as 1; “John Doe”
Conclusion
There are many ways and tools to create a master-slave replication, but using MariaDB provides us an easy and compelling way as we have all the power of MariaDB at our disposal.
Performing data replication has endless advantages, including data accessibility and splitting the load between multiple systems on a large scale. Other benefits of implementing data replication are the following:
- Disaster recovery
- Server performance
- Data analytics support
- Data reliability and availability
I hope this mini-tutorial has been helpful to some of you guys. Enjoy the coding!
Shoot me an email to conne.gil@prismasoftwares.com, if you have any comments or questions!
Sami Derian
Project manager and expert in software development. Sami is responsible for strategic project planning and client relationships. Having worked in different web agencies he developed a passion for e-commerce and marketing.
Sami Derian
Lorem ipsum dolor sit amet consectetur adipiscing elit dolor
Leave a comment