The Master-Slave Replication in MySQL – MariaDB Databases

The Master-Slave Replication in MySQL

In typical database storage and data retrieval situations, master-slave replication is ideal for saving various problems related to the performance, support, and back of different databases. It comes as a part of a broader solution to alleviate any system failures.

It also enables data from one DB server to another (master) by replicating the same to one or more database servers, which are known as slaves. There is a change in the terminology use of ‘master-slave’; however, we will follow the same now for this article.

The master server will log all the updates, which then instantly ripple through the slaves and make necessary changes. Slave servers output the message stating that it had been received and updated successfully. Subsequent updates are thereby allowed.

The master-slave replication model can be synchronous as well as asynchronous. The primary difference is that the timing of the propagation of changes may vary. If any changes are made to the master and slave happen simultaneously, then it is called synchronous. If the changes made are queued up and written in a sequential model, it is called asynchronous.

Master-slave replication of database

MariaDB Databases

The actual target usage of master-slave replication in both the NoSQL database of MariaDB and the relational database of MySQL DB includes the below:

  • Scale-out solutions
  • Analytics
  • Data security
  • Data distribution (long-distance), and

Let us explore the ways as to how the users can take the best advantage of master-slave replications.

Proper backups: To use the replication as a backup solution, it is vital to replicate data from the master to a slave and then back up the data slave. Slaves can be paused and further shut down without affecting any running operation at the master. So, you can ultimately produce a compelling snapshot of the live data, which may otherwise require to shut down the master.

Scale-out: The users can also use the replication to scale-out the solution. This means, when you want to split a load of database queries among many servers, it can be done with replication within some restrictions. As replication will work as a distribution of changes from the master to multiple slaves, usage of replication to scaleout may work the best in an environment where there is an increased number of reads and a decreased number of writes or updates.

Spreading the overall load: There are many situations in a typical database administration environment where you may have a single master and want to replicate various databases to different slaves. For example, you may sometimes wish to distribute various sales data to other departments to help spread the data analysis load. For better assistance in load balancing, you may avail of the remote administration services offered by

Performance boost: As the number of slaves connected to the master increases, the load will also increase as the slaves use a client connection to the master. Also, as the slaves must receive a full copy of the binary log of the master, the network load on the master also may be increasing, which ultimately causes a bottleneck. So, if you use many slaves connected to the master and the master gets busy processing many requests, you may want to expedite the speed and performance of the replication. This can be achieved by creating a deeper replication structure that will enable the master to replicate to only one slave easily. The remaining slaves can connect to this newly made primary slave for the individual replication needs.

Alleviating failover: You can easily set up the master and slave or multiple slaves and to write a script to monitor the master ad check whether it is up. You can also instruct the applications and slaves to change the master in case of any application failure.

Security: You may use SSL encrypted for safety with the transfer of binary log required for replication, but both of these master and a slave, should support SSL network connection. If either of these hosts does not help the SSL connections, then replication through SSL connection is impossible. Setting up the replication using the SSL connection may be similar to setting up the server and clients using the SSL. You can also create or obtain an appropriate security certificate to use on the master and even another one to use on each of the slaves.

You can do the DB cluster set up in two ways as:

Automatic through the one-click widget

For this, you may get a MariaDB or MySQL database cluster with the required replication type and get it up within a matter of minutes by using the ‘Jelastic’ one-click installation widget. To run this process, you need to give your email ID and confirm it via notification. To get more info, you may go through the Auto-Clustering notes related to MariaDB/MySQL.

Manual Installation

If you plan to configure Master-Slave replication through manual installation to get more number of slave replicators or to specify some of the custom configurations, you may follow the below instructions.

  • First, log into to the Jelastic dashboard
  • Let Jelastic create a new environment
  • At the Environment topology dialog, choose MariaDB or MySQL as your database
  • Next, set the cloudlet limit and key in the name of your environment, i.e., masterbase, etc
  • Wait for a minute to get your custom environment created

You can use the same method to create more environmentswith the MariaDB or close the same. You may further call it a slave base. It may be located in other hardnodes, which is more reliable and secured for storing your data.

Skipping is not ideal at the development stage to find any bugs or so. But the system should be properly tested as any errors on the master’s side will stop the functioning of the slave and end up losing the synchronization. In this case, any other errors may go innocuous and ultimately spoil the entire database.

Different things you can do with iPhones and iPads

Previous article

Operating Quickly Is The Primary Objective Of Modern Business Management

Next article

You may also like


Comments are closed.

More in Tech