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).

.

.

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 .

In this case your normally will configure MySQL running into SLAVE server with read-only=1, as showed below:

.

.

.

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.

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: http://bit.ly/nGTQO1).

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.

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.

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

• MySQL Replication – Enhancing Scalability and Availability with MySQL 5.5
• Getting Started with MySQL Replication on Windows

On Demand Webinars

• MySQL Replication – Latest Enhancements in MySQL 5.5
• Getting Started with MySQL Replication on Windows
• Geographic Replication with MySQL Cluster