MySQL Replication Topologies

You should know that MySQL team has been doing a good job and that product naturally is being a great option when the chat is horizontal scale or Scale-Out too. I mentioned the last “too” cause MySQL has been doing a so confident job in vertical scaling on its availability with hands on InnoDB Storage Engine. But, treating about MySQL Replication and Scale-Out points, MySQL has its good features as that three supported kinds of data replication:

  • Synchronous data replication, used just when you’re using MySQL Cluster (data replication between Data Nodes);
  • Asynchronous and Semi-synchronous replication, to replicate data using servers available as a MASTER and SLAVE, having MASTER a lot of SLAVEs and a SLAVE a unique MASTER.
This post are being write just to highlight the kinds of topology mentioned by Oracle and some other that we can create to solve a specific problem inside a company. To mention the existent kinds, it will need to explain more about the “map of availability”, created by MySQL AB.
As say a friend from USA, it is “easy peasy”  to understand this graph and work with on your organization strategy. Starting from Small Business where normally a little and small amount of availability is required to maintain the business continuity, you can set up just only a instance of MySQL to get it working well, with small management applied to this environment. We can realizing, looking graph that this small business could count with 35 days of downtime on worst cases. As we will rising the graph, we will seeing new situations and the number of nines will growing (high availability nines).
Topology 1: Single
The fist one I will comment is the “Simple” topology, normally used when organization is looking for data redundancy and backup process improvements. Simply, it will operates with two servers actuating as a MASTER and SLAVE. The good touch here is to adapt application to write data on MASTER and just read data from SLAVE. It will provide good improvements and will alleviate workload if you were operating with a single server to respond all app requests .

The main server (rounded with red) acts as a MASTER and the other, as a SLAVE - that last must be configured with read-only=1

In this case your normally will configure MySQL running into SLAVE server with read-only=1, as showed below:
mysql> SET GLOBAL read_only=1;
Topology 2: Multiple
As the name says, on this topology we can have many servers looking for a unique MASTER, building what we know as a multiple topology. It will be pretty suitable when the environment has the necessity to advance to a multiple divided workload, which on you can let you app write data on MASTER, read from one of the SLAVEs servers (you can apply a kind of load-balancing as mysql-proxy or F5 LTM) and let the other to extract security copies to avoid interfere on those production servers. It is a common situation where we have high workload and must backup databases at least two times a day – in this case it is good to use the snapshot backup supported by MySQL Enterprise Backup, Xtrabackup or Zmanda.

You could set up much more servers than two depicted above!

Topology 3: Chain
This topology will simply provide that story of replicate data on Master(1) <- Master/Slave(2) <- Slave(3) architecture. This is good when you have a departmental servers available separately inside your organization to attend many areas with as less time as possible. With this topology replication model, you will be able to adjust applications to write data on server A and B (INSERT and DELETE), scaling writes using both mentioned servers. The third one could be used to serve reports and backup as a read only server (just SELECT). What we cannot forget is to set the log-slave-updates on server’s B my.cnf due to this server will be MASTER and SLAVE at the same time (MySQL Manual Page:

MASTER(A) <-> MASTER(B) -> SLAVE(C) - Attention to configure out -log-slave-updates on server (B)

Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

A -> B -> C

Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

Topology 4: Circular

This kind of replication topology has been generating many discussion around MySQL environments due to the set up with MySQL 5.0, version that not count with the terminator applied on MySQL 5.1. On broad terms, MySQL servers is set up on a circular way where every server is MASTER and SLAVE at the same time. The log-slave-updates replication system variables must be configured on all servers in order to ignore servers that just have executed that current updates.


You can set up MySQL Servers 5.1 ++ in circular replication as A <-> B <-> C

In circular replication, it was sometimes possible for an event to propagate such that it would be replicated on all servers. This could occur when the originating server was removed from the replication circle and so could no longer act as the terminator of its own events, as normally happens in circular replication.

To prevent this from occurring, a new IGNORE_SERVER_IDS option is introduced for the CHANGE MASTER TO statement. This option takes a list of replication server IDs; events having a server ID which appears in this list are ignored and not applied.

In conjunction with the introduction of IGNORE_SERVER_IDS, SHOW SLAVE STATUS has two new fields. Replicate_Ignore_Server_Ids displays information about ignored servers. Master_Server_Id displays the server_id value from the master. (Bug #47037)

See also Bug #25998, Bug #27808.

Additional Resources

White Papers

On Demand Webinars

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply