A database crash is too critical that it takes your entire website down!

One of the best methods to avoid such website down issues is to replicate database on another server.

There are many ways to copy database. But, the right choice often depends on factors like size of database, type of website, etc.

At Bobcares, we help server owners to setup the right MySQL database replication as part of our Support Services for Web Hosts.

Today, let’s discuss the various options available to replicate database on another server.

Why to replicate database?

Before checking the ways to replicate databases, let’s see why we should copy a database to another server.

MySQL replication can become a life saver during a website outage. We can temporarily switch the database connection to the replicated server. The helps to make website working again.

Additionally, replicating MySQL databases in another server helps in load sharing, increased performance, etc.

Formats used to replicate database on another server

We now know that replication of database is good.

The MySQL replication typically involves multiple servers. There will be a master server and one or more slave servers. Again, the number of servers vary in each set up.

The method of replication largely depends on the MySQL binary log file. This binary log contains a record of all changes to the databases, both data and structure.

We can find the exact method of replication from the “binlog_format” server variable.

Let’s see the details of these different methods now.

Row-Based Replication

In row-based replication, the binary log file in the master server stores the record-level changes in database tables. As the first step of replication, the slave server reads this data. Then, it manipulates its records to produce a copy of the master database.

This method allows to replicate changes to individual table rows. It also requires only fewer table locks.

To view the log format, we need to issue the following command in the MySQL command line:

mysql> SELECT @@DATA.binlog_format;

+-------------------------+
| @@DATA.binlog_format |
+-------------------------+
| ROW |
+-------------------------+
1 row in set (0.00 sec)

Statement-Based Replication

Here, the binary log file stores the SQL statements used to change databases on the master server. The slave reads this data and then executes these SQL statements to produce a copy of the master database.

But, this has a limitation. We cannot replicate certain types of statements using this method. Again, it also tends to create a higher number of database table locks.

Mixed-format replication

Mixed format replication allows to select between statement-based replication and row-based replication. And, it can include actions based on certain user defined conditions.

Types of Replication

Now, let’s see the different ways to replicate MySQL database in another server. These methods normally use row-based, statement-based or mixed-format replication.

In all replication types, the master-slave mysqld connectivity should be intact. Let’s have a look at each of them.

1.Standard asynchronous Replication

In standard asynchronous type, the database transactions in master server is independent. It is not influenced by the replication slaves. Here, the master populates the binary log to slave server.

And, the slave server independently executes change on the slave’s database using the SQL threads.

Therefore, there is no real guarantee that the database changes will be copied in the slave.

2.Semi-synchronous Replication

Semi-synchronous replication is a better option than asynchronous replication.

Here, the master commits a transaction to the slave. It then waits for an acknowledgement message from at least one slave server.

Thus, semi-synchronous replication guarantees that a transaction is correctly copied in the slave server. Still, it does not guarantee that the changes on the slave actually takes place.

Semi-synchronous replication needs to wait for the actual acknowledgement message from the slave. This often becomes a drawback and impacts the performance of the server.

3.Group Replication

This is a comparatively new method introduced in the MySQL Community Edition 5.7. MySQL Group Replication comes as a plugin to MySQL server. This method involves a group of servers. Each server in a group requires configuration and installation of the plugin.

Group Replication relies on row-based replication format to propagate changes consistently among the servers in the group.

4. Percona XtraDB Cluster

Similarly, we can replicate database on another server using “Percona XtraDB cluster“.

In this method, data is written to all servers simultaneously. Also, in this method, any server can trigger a data update.

That’s why, our Support Engineers often recommend this method to customers. Additionally, it offers merits like consistency, avoids conflicts in database transactions, etc.

[Looking for Database replication on your servers? Our Support Specialists can do the task for you right away]

Conclusion

Replicating MySQL databases on another server really helps as a fallback method for your systems. There are different methods to do it. Today, we’ve seen the top methods that our Security Engineers use for replicating databases along with its pros and cons.