The Benefits of MySQL Multi-Source Replication

In the world of software development, an often-overlooked component which drives dynamic, data-driven websites is the underlying database. While it is not technically ignored, this is the one element that does not receive as much consideration in an e-commerce website.

There are many database technologies that can be used, often coming in the form of either closed-source, proprietary systems or open-source, community-developed systems. Examples of the former include Oracle and SQL Server, and representatives of the latter include MySQL and PostGRESQL.

Both have their advantages and disadvantages, but very often, MySQL is the database framework of choice for e-commerce businesses. This is because open-source is free to use, has a robust array of capabilities, and can be scaled very quickly and easily to meet the needs of any client.

The art of accurately backing up a server is called “replication.” The idea is to provide as near as possible an identical backup of the server that can be swapped for the master any time it’s needed. That need could be maintenance or a bug or a break in. The master database is taken offline and the slave becomes the new master, and a new slave is started. Until recently, MySQL database replication could only be done from one master to one slave.

Multi-Source Replication

One of the key features in the latest releases of MySQL is the functionality known as “multi-source replication.” This simply means that multiple database servers can be backed up onto a single database server.

The primary advantage of this is that there is much less “database bloat,” and a much more efficient usage of processing power and other critical resources. For example, tables with overlapping fields and data can be consolidated into one unit.

In more technical terms, MySQL multi-source replication is actually based upon what is known as a “master-slave architecture,” where any updates made to the master database will be automatically propagated down to the slave database.

This crosstalk between the two databases is done via a communications-based medium, which is nothing more than a simple network connection. It is important to note that one master can connect to only one slave database, and vice versa. Under this example, there is no way that one slave database can connect easily to multiple master databases.

This is where the functionality offered by multi-source replication comes into play: One slave database can now connect to multiple master databases. It is also important to note at this point that updates from the master database(s) down to the slave database can either be synchronous or asynchronous. With the former, all of the updates are done at once; whereas with the latter, all of the updates are queued up and deployed to the slave database at random intervals.

The Benefits of Multi-Source Replication

There are many benefits to be gained by implanting multi-source replication into your MySQL database(s) configuration. These include the following:

1)   Updates from the multiple master databases can now be received by the slave database in real time;

2)   Transactions from multiple master databases can now be propagated downwards towards the slave database via multiple communication channels (or multiple network connections) versus just one communication channel;

3)   Multiple transactions to the slave database can now be accomplished while not conducting any type or kind of database conflict/resolution processes.

Sources of Confusion to Be Avoided

In the world of MySQL database development and implementation, there is often confusion between multi-master replication and multi-source replication. With the former, any updates made to one master database are automatically propagated across the other master databases.

In these specific instances, transaction ordering, transaction conflict detection, transaction conflict resolution (also known as “rollback”) are all specifically implemented. But with the latter, there is no concern with regards to transaction conflict detection or resolution. Rather, it is assumed that the application which has been developed already has any type or kind of database conflicts resolved. The MySQL 5.7 Reference Manual specifically states:

“Multi-source replication does not implement any conflict detection or resolution when applying the transactions, and those tasks are left to the application if required.”

If you are uncertain about the best MySQL replication options for your organization, Datavail can help you evaluate your current database environment against your company’s business priorities and future goals. With more than 600 DBAs worldwide, Datavail is the largest database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL, regardless of the build you ultimately select.