Tuesday, August 25, 2015

New MySQL Sandbox 3.1 - GitHub, and usability

I have three pieces of information to share about MySQL::Sandbox:

  • Version 3.1.0 has migrated from Launchpad to GitHub
  • This version is released under the Apache license. Both these changes are meant to improve and promote cooperation on the project.
  • There is an important change related to usability. When using replication with MySQL::Sandbox and MySQL 5.6+, the server UUIDs become more readable (see below).

First, some words on the location changes. About two years ago, I started plans for a rewrite of MySQL::Sandbox. Then, I had some unexpected changes, which involved moving home to a different continent twice within twelve months. The project was neglected, but I haven't dismissed it. While I wait for the rewrite to start, I wanted to get rid of the obstacles for rapid development, and I decided to transfer the current codebase to GitHub. This will allow me to use only one RCS instead of three (My team has abandoned svn too 1).

Apart from the changes described in this post, there is little difference in the code published on GitHub.

Now, to the usability. In my recent series of advanced replication features, I complained often about GTIDs being hard to tell apart. Hers is an example from MySQL replication in action - Part 3: all-masters P2P topology. Look at the server identifiers, and see if you can tell at first glance where does the largest transaction set come from:

$ for N in 1 2 3 4 ; do ./n$N -e 'select @@server_id, @@server_uuid; select @@global.gtid_executed\G'; done
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 18fd3be0-4119-11e5-97cd-24acf2bbd1e4 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         102 | 1e629814-4119-11e5-85cf-aac6e218d3d8 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         103 | 226e3350-4119-11e5-8242-de985f123dfc |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         104 | 270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3

I decided that I could do something. I use MySQL::Sandbox for my testing, and in these conditions there is little risk of server clashing. The architecture of MySQL::Sandbox ensures that servers within a group of sandboxes keep to themselves. I modified the software so that when a server is created with a server-ID, the server-uuid is modified (with a unsupported-and-do-not-try-this-in-production hack). I know: it defies the purpose of having unique identifiers, but frankly, I care more about being able to understand what is going on than worrying about my server-uuid being the same in a different cluster.

The way it works is simple: when a server is created and has a server-id (i.e. we can safely assume that its purpose is to be used in replication), its server-UUID is changed to a new string made of port number and the server-id repeated many times. If the server ID is between 101 and 109 (what usually MySQL::Sandbox does for groups of sandboxes) it is simplified by subtracting 100, and having IDs that are still hard to pronounce, but that can be visually identified at a glance. (such as 00008480-1111-1111-1111-111111111111)

If the server ID is something more complex, then MySQL::Sandbox uses the port number to create the last part as well. For example 00005708-5708-5708-5708-000000005708.

Here is the same scenario shown in the article, but using the newest MySQL Sandbox version. Now server #2 is more easily identified as the source of the largest transaction group.

$ for N in 1 2 3 4 ; do ./n$N -e 'select @@server_id, @@server_uuid; select @@global.gtid_executed\G'; done
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 00008480-1111-1111-1111-111111111111 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 00008480-1111-1111-1111-111111111111:1-3,
00008481-2222-2222-2222-222222222222:1-119,
00008482-3333-3333-3333-333333333333:1-3,
00008483-4444-4444-4444-444444444444:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         102 | 00008481-2222-2222-2222-222222222222 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 00008480-1111-1111-1111-111111111111:1-3,
00008481-2222-2222-2222-222222222222:1-119,
00008482-3333-3333-3333-333333333333:1-3,
00008483-4444-4444-4444-444444444444:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         103 | 00008482-3333-3333-3333-333333333333 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 00008480-1111-1111-1111-111111111111:1-3,
00008481-2222-2222-2222-222222222222:1-119,
00008482-3333-3333-3333-333333333333:1-3,
00008483-4444-4444-4444-444444444444:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         104 | 00008483-4444-4444-4444-444444444444 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 00008480-1111-1111-1111-111111111111:1-3,
00008481-2222-2222-2222-222222222222:1-119,
00008482-3333-3333-3333-333333333333:1-3,
00008483-4444-4444-4444-444444444444:1-3

Should you decide that you don't want MySQL::Sandbox to do this transformation, you can skip it by setting the variable KEEP_UUID before running any sandbox installation command.

Another change that was long overdue is the behavior of the './clear' script within the sandboxes. With the introduction of MySQL 5.7, I faced the problem of having innodb tables in places where there hadn't been before (i.e. the 'mysql' database). As a solution, I made a copy of that database with mysaldump right after the installation, and then I used it to re-create the database after a cleanup. This solution doesn't work, as it leads to more problems than benefits. So I changed the behavior. There is no mysqldump backup, and there is no removal of innodb files. Moreover, since the sys database was introduced, it is not safe to truncate 'mysql.proc', as it would also make the sys schema ineffective. Now, if a cleanup that just removes non-system schemas is enough for you, go for the './clear' script. If your testing has messed up with stored routines, then you would be better off with a reinstallation (which takes just a few seconds anyway.)

And one more thing: This release of MySQL::Sandbox is ready for MySQL 5.7.9. The MySQL team at Oracle has graciously provided a preview build to test some early bug fixes, and MySQL::Sandbox 3.1.00 works smoothly with it.




1. For the ones who are new to my blog, let's make clear that MySQL::Sandbox is my personal project, and my company does not have any relation or influence on such project.

Monday, August 24, 2015

Tungsten Replicator moved to GitHub with Apache license

It had been in the making for long time. Google announced that Google Code would be closing, and since then the Continuent team has been hard at work to handle the transition. You can guess it: this operation would have been quicker if it had been done by a small company like we were one year ago, but being part of a large corporation introduces some constraints that have affected our schedule.

However, our wish has always been, and still is, to keep Tungsten Replicator as an open source product, with full functionalities and with the full benefits that the open source development model offers.

Today, Tungsten Replicator is available on GitHub as vmware/tungsten-replicator, and it is wearing new clothes. It is not GPL anymore. In an effort to facilitate contributions, its license was changed to Apache 2.0.

Feature-wise, there is little difference from the previous release of 4.0. Mainly, we have cleaned up the code and moved out the pieces that no longer fit:

  1. Bristlecone was removed from the package. It is used only for testing, and it will be released separately. There is no need to duplicate it into every Tungsten tarball.
  2. The cookbook recipes have been retired. These scripts were created when the installer was still in its infancy and we had little documentation. Therefore, it was convenient to have wrappers for the common installation operations. Using the manual, it is pretty easy to install master/slave, fan-in, and multi-master topologies. The biggest reason for removing the cookbook, though, is that it was only useful for MySQL replication. If you need heterogenous deployments, the cookbook was an obstacle, rather than being helpful.
  3. Some files were shuffled within the deployment tree. The ./tungsten-replicator/scripts directory was merged with ./tungsten-replicator/bin, the applier templates were moved from samples to a dedicated path, and we also did some other similar cleanup.

Although it has changed location and license, this is not a "release." If you compile the code, it will come up as 4.1, but it is still work in progress. Same as what was happening in the previous repository, we tag the code with the next version, and start working on it until it is deemed ready for release. The latest release for production (4.0.1) is still available from the old directory.

The code is available on GitHub, which makes collaboration much simpler than the previous repository. Take advantage of it: fork it, and help make the best replication tool even better!

MySQL usability issues for replication

In my latest series of advanced replication features, I came across several usability issues, which I would like to recap here. For each section of this list of requests, I make a wish list, with some general comments.

INSTALLATION

As the maintainer of MySQL Sandbox, a tool that wants to facilitate the installation of MySQL for testing, I am particularly sensitive to the annoyances during installation. I have covered the matter extensively in recent weeks and many times before. My point is that the logging of the installation must be useful for the DBA, not to the MySQL developers.

  • Make a big distinction between [Info] and [Warning] messages. Warnings should only appear when there is some risk. Using a warning to tell the user that the InnoDB log file was created is a nonsense.
  • … and then make [Info] messages suppressible. Currently, I can suppress warnings, but then I would suppress also the real warnings, in addition to the fake ones.
  • Make mysqld –initialize use the correct syntax for timestamps, instead of issuing a warning about the obsolete syntax that was used. (See In search of cleanliness : the elusive quiet installation for a full explanation)

MULTI-SOURCE

Here we have two main problems. The first one is that the introduction of multi source channels has left some new ways of shooting yourself in the foot. By mixing the new explicitly named channels with the hidden one (but still used in regular replication!) which is named "" (= the empty string), we may start to see a new series of blunders in replication.

The second set of problems comes from weak integration between existing features and the new one. There are operation modes, such as semi-synchronous, delayed, and parallel replication that were designed with a single data stream in mind, and that lack the capability of being tuned for different channels.

  • Make it impossible to use the unnamed channel and the named ones at the same time. Currently only a very weak protection exists against mixing named and unnamed channels. Also the syntax for channel ’’ should result in an error when named channels are defined.
  • Integrate features that were designed for single sources to work better with multi source: semi-sync replication, parallel applier, delayed replication.

GTID

The ugliness of GTID implementation comes from the assumption that users should value more the uniqueness of the server ID than the ease of maintenance. This leads to the solution of choosing UUIDs as server identifiers, and tying such identifiers to GTIDs. Personally, I don't care if my server has the same ID used in a deployment next door or in another continent. I care about being able to recognize my servers easily and group them according to my needs. Moreover, the server-UUID is only used for a few functions, but replication accuracy still depends on the old server-IDs.

That said, even if GTIDs were easier to read, they still suffer from lack of usability in many ways, as was documented in an earlier article.

  • The server-UUID should be human friendly. It should be something that can be pronounceable, easy to read, and easy to tell apart from others. Valid candidates for better server identifiers can be simple numbers (like server-id), alphanumeric text (such as ‘alpha,’ ‘mynode101,’ ‘London–42’)
  • It should be possible to define the server-UUID (or its human-friendly replacement) without hacking the auto.cnf file
  • The GTID shown in monitoring commands ( show master/slave status, select @@global.gtid_executed, various performance_schema tables) should show the precise transaction number, not the transaction set, i.e. server-uuid:203, not server-uuid:1–203.
  • Enable GTID by default;
  • Remove restrictions to GTID usage;
  • Make an easy procedure to skip unwanted events, instead of injecting empty transactions;

CHANGE MASTER TO

The shortcomings of GTID are also reflected on the main command for replication setup: CHANGE MASTER TO. Some of the shortcomings are listed below. Perhaps there is something more that escapes me now.

  • Allow CHANGE MASTER TO to use GTID to set the replication starting point. Currently you can only do it with binlog name + position;
  • Allow CHANGE MASTER TO to use server-uuid (or their human friendly replacement) as argument for IGNORE_SERVER_IDS=…

MASTER STATUS

The usefulness of MASTER STATUS is reduced by the inability of combining its output with something else. Since the status is only available as a SHOW statement, we can only use its output through an external procedure, not in SQL.

Most damning, though, is the grouping of GTIDs listed in SHOW MASTER STATUS with multi-source replication, where determining what was created in the current server requires multiple operations.

  • its output should be available in a table
  • When using multi-source, there should be a distinction between GTID generated in the current server and the ones that were received through replication

SLAVE STATUS

In part I of the replication analysis, we have seen that the only place where we have completeness of information is SHOW SLAVE STATUS, while all the replication tables in mysql and performance_schema together still lack some of the elements that this ancient command allows. There is much that can be done for improving the monitoring tables.

  • show ALL the information from SHOW SLAVE STATUS in performance_schema tables.
  • In multi-source, show executed GTID for the channel that is being listed. Currently, it shows all GTIDs for every channel.

CRASH-SAFE tables in mysql

This is related to the previous section. Some info that is available in SHOW SLAVE STATUS is still missing from the crash-safe tables. And some more data, which is available to the replication threads, should be logged as well.

  • Show received and executed GTID alongside binlog and relay log info
  • Add schema info (in which schema the event was applied)

SLAVE WORKER TABLE

Continuing the grievance about SHOW SLAVE STATUS, the monitoring table dedicated to parallel replication can give more info. When an error occurs during parallel replication, the error message shows the thread identifier, the GTID, and the log where the problem has arisen. Why the same info cannot be in the tables as well?

  • Add GTID info
  • Add schema info
  • Remove Checkpoint_group_bitmap field (which shows as gibberish) or make it displayable.

performance_schema TABLES

Part of the above wishes apply to the performance_schema tables as well. Information known to the server is not made available for monitoring.

  • Show both received and executed GTIDs for all replication_% tables that display progress.
  • Enhance replication tables to include all information related to replication.

WISHES FOR FUTURE FEATURES

As MySQL 5.7 approaches GA, we can start worrying about new features coming. I think that the data dictionary will be one of the features that will have the bigger impact on usability.

What concerns me is that some of the features of a data dictionary will clash with what is already implemented in information_schema and performance_schema. I'd like to see, as early as possible, an integration path for these features. I don't care if in this version we need to adjust our queries to the new structure, provided that there will be only one source of information and that the change provides real benefits.

A further wish for the future: when introducing changes related to security, make them substantial rather than noisy. Adding warnings to commonly used commands results often in unnecessary work for DBAs and developers.

Finally, and coming back to the main focus of this post, when thinking of replication I invite the MySQL team to innovate outside the box. There are many fields where replication can be enhanced and made more powerful. Here is a simple list:

  1. Make replication open to heterogeneous sources and destination. Replicating from and to no-sql entities is becoming more important by the day.
  2. Make replication more agile, by adopting and promoting an open standard for binary logs.
  3. Replication monitoring is now based on a single server. We need internal features that make easier the task of creating monitoring dashboards that can see the whole cluster at once. Adding the appropriate monitoring tables and giving slaves the ability of communicating back to the masters their status (which would then broadcast back to the other slaves) would improve the replication process —and the ability of recovering from a failure— immensely.

Wednesday, August 19, 2015

MySQL replication in action - Part 4 - star and hybrid topologies

Previous episodes:

Introducing star topology.

In all-masters P2P topologies, we have seen that we have a way of deploying a topology where all nodes are masters, and achieve better efficiency and stability than ring topologies. That method comes at the price of a complex setup, which requires, for a N-node cluster, N*(N-1) connections.

We can achieve the same result as in a P2P all-masters topology by trading connections for stability. In a star topology (Figure 1) all nodes are masters, but they do not connect to each other directly. There is a special node, named hub, which receives the changes produced by each endpoint and spreads them to the others.

Topologies star

Figure 1 - A star topology

Compared to ring replication, a star topology does not achieve the dangerous depth of processing (see Part 3) of N-1, where the last node requires N-1 acts of replication to be updated. In fact, the depth of processing of a star topology can be either 1, if the data is produced in the hub, or 2, if it is produced in the endpoints (See figures 2 and 3).

Topologies star processing Topologies star processing

Figure 2 - A star topology depth of processing from hub

Figure 3 - A star topology depth of processing from endpoints

You can think of a star topology as a set of ring topologies strung together.

Topologies star as ring

Figure 4 - A star topology is like many rings together

The way it works is simple. All endpoint nodes are configured as we have seen for fan-in and P2P topologies. The hub node, instead, has an extra option: it enables log-slave-updates. With this addition, the hub is able to receive data from any master, and every endpoint can then pull the data from the hub.

The advantage is clear: While we had 12 connections for a 4 node deployment in an all-masters P2P topology, a star topology only requires 6 of them. To add a 5th node, you needed to add 8 connections (4 in the new node, and one each in the previous nodes) for a total of 20 connections. In a star topology, you would only add 2 connections (one from the new endpoint to the hub, and one from the hub to the new endpoint) for a total of 8.

If your deployment requires many nodes and all need to be masters, a star topology is a good candidate.

There is, of course, a price to pay. In addition to the risk of bad performance (of which I have talked at length in Multiple masters : attraction to the stars,) a star topology has a single point of failure (SPOF) and you must get ready to deal with it. We will see a full example, but for now you need to know that, in order to successfully promote an endpoint to the role of hub, you need to enable log-slave-updates in one or more endpoints that you want to be hub-candidates.

Installing a star topology

The procedure to install a star topology in MySQL 5.7 requires several steps for each endpoint:

  1. in the endpoint:
    • create a channel named hub-to-$nodename, where $nodename is the name of the endpoint;
    • start slave for channel 'hub-to-$nodename'
  2. in the hub:
    • create a channel named $nodename-to-hub
    • start slave for channel '$nodename-to-hub'

You must also remember to enable log-slave-updates in the hub (and in one or more endpoints that you want to set as candidate to hub replacement in case of failure.

The procedure for MariaDB 10 is the same, with the different syntax that we have noted in the previous articles.

To see a practical example, we will use another script from mysql-replication-samples. Using a group of 5 sandboxes, we will create a system with 4 endpoint and one hub.

$ ./set_star_topology.sh 5.7.8 mysql
installing node 1
installing node 2
installing node 3
installing node 4
installing node 5
group directory installed in $HOME/sandboxes/multi_msb_5_7_8
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# server: 5:
# option 'master-info-repository=table' added to node1 configuration file
# option 'relay-log-info-repository=table' added to node1 configuration file
# option 'gtid_mode=ON' added to node1 configuration file
# option 'enforce-gtid-consistency' added to node1 configuration file
. sandbox server started
# option 'master-info-repository=table' added to node2 configuration file
# option 'relay-log-info-repository=table' added to node2 configuration file
# option 'gtid_mode=ON' added to node2 configuration file
# option 'enforce-gtid-consistency' added to node2 configuration file
. sandbox server started
# option 'master-info-repository=table' added to node3 configuration file
# option 'relay-log-info-repository=table' added to node3 configuration file
# option 'gtid_mode=ON' added to node3 configuration file
# option 'enforce-gtid-consistency' added to node3 configuration file
# option 'log-slave-updates' added to node3 configuration file
. sandbox server started
# option 'master-info-repository=table' added to node4 configuration file
# option 'relay-log-info-repository=table' added to node4 configuration file
# option 'gtid_mode=ON' added to node4 configuration file
# option 'enforce-gtid-consistency' added to node4 configuration file
. sandbox server started
# option 'master-info-repository=table' added to node5 configuration file
# option 'relay-log-info-repository=table' added to node5 configuration file
# option 'gtid_mode=ON' added to node5 configuration file
# option 'enforce-gtid-consistency' added to node5 configuration file
. sandbox server started
# HUB node3 port: 8381
# node node1 port: 8379
./node1/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node1'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8379, MASTER_AUTO_POSITION=1 for channel 'node1_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node1_hub'"
./node1/use -e "START SLAVE FOR CHANNEL 'hub_node1'"
# node node2 port: 8380
./node2/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node2'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8380, MASTER_AUTO_POSITION=1 for channel 'node2_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node2_hub'"
./node2/use -e "START SLAVE FOR CHANNEL 'hub_node2'"
# node node4 port: 8382
./node4/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node4'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'node4_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node4_hub'"
./node4/use -e "START SLAVE FOR CHANNEL 'hub_node4'"
# node node5 port: 8383
./node5/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node5'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8383, MASTER_AUTO_POSITION=1 for channel 'node5_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node5_hub'"
./node5/use -e "START SLAVE FOR CHANNEL 'hub_node5'"

We see operations similar to the ones used for other multi-source topologies. Notice that for the hub we also set log-slave-updates. We can use the same script used for all-masters topology to see if this deployment works as expected. Before doing that, though, we will enable log-slave-updates in one of the endpoints (node4) which will be our hub candidate when we try a failover.

$ ./node4/add_option log-slave-updates
# option 'log-slave-updates' added to configuration file
. sandbox server started

Note: you may need to wait a few seconds for the slave channels between the hub and the candidate to resume operations.

After that is done, we can test the deployment:

$ ./test_all_masters_replication.sh
# NODE node1 created table test_node1
# NODE node2 created table test_node2
# NODE node3 created table test_node3
# NODE node4 created table test_node4
# NODE node5 created table test_node5
# Data in all nodes
101
1 101 8379 node1 2015-08-14 22:21:02
1 102 8380 node2 2015-08-14 22:21:02
1 103 8381 node3 2015-08-14 22:21:02
1 104 8382 node4 2015-08-14 22:21:02
1 105 8383 node5 2015-08-14 22:21:02
102
1 101 8379 node1 2015-08-14 22:21:02
1 102 8380 node2 2015-08-14 22:21:02
1 103 8381 node3 2015-08-14 22:21:02
1 104 8382 node4 2015-08-14 22:21:02
1 105 8383 node5 2015-08-14 22:21:02
103
1 101 8379 node1 2015-08-14 22:21:02
1 102 8380 node2 2015-08-14 22:21:02
1 103 8381 node3 2015-08-14 22:21:02
1 104 8382 node4 2015-08-14 22:21:02
1 105 8383 node5 2015-08-14 22:21:02
104
1 101 8379 node1 2015-08-14 22:21:02
1 102 8380 node2 2015-08-14 22:21:02
1 103 8381 node3 2015-08-14 22:21:02
1 104 8382 node4 2015-08-14 22:21:02
1 105 8383 node5 2015-08-14 22:21:02
105
1 101 8379 node1 2015-08-14 22:21:02
1 102 8380 node2 2015-08-14 22:21:02
1 103 8381 node3 2015-08-14 22:21:02
1 104 8382 node4 2015-08-14 22:21:02
1 105 8383 node5 2015-08-14 22:21:02

Now, let's have a look at the GTIDs:

$ for N in 1 2 3 4 5; do ./n$N -e 'select @@server_id, @@server_uuid; select @@global.gtid_executed\G'; done
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 519dd7f0-42c4-11e5-8995-590303071a70 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 519dd7f0-42c4-11e5-8995-590303071a70:1-3,
5615010a-42c4-11e5-873f-9904485cf6bc:1-3,
5a8809f8-42c4-11e5-9c60-f36f50446736:1-3,
5ecdf2ca-42c4-11e5-b861-a22aea18b64b:1-3,
630bab66-42c4-11e5-8807-bb8c8c1ce62c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         102 | 5615010a-42c4-11e5-873f-9904485cf6bc |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 519dd7f0-42c4-11e5-8995-590303071a70:1-3,
5615010a-42c4-11e5-873f-9904485cf6bc:1-3,
5a8809f8-42c4-11e5-9c60-f36f50446736:1-3,
5ecdf2ca-42c4-11e5-b861-a22aea18b64b:1-3,
630bab66-42c4-11e5-8807-bb8c8c1ce62c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         103 | 5a8809f8-42c4-11e5-9c60-f36f50446736 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 519dd7f0-42c4-11e5-8995-590303071a70:1-3,
5615010a-42c4-11e5-873f-9904485cf6bc:1-3,
5a8809f8-42c4-11e5-9c60-f36f50446736:1-3,
5ecdf2ca-42c4-11e5-b861-a22aea18b64b:1-3,
630bab66-42c4-11e5-8807-bb8c8c1ce62c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         104 | 5ecdf2ca-42c4-11e5-b861-a22aea18b64b |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 519dd7f0-42c4-11e5-8995-590303071a70:1-3,
5615010a-42c4-11e5-873f-9904485cf6bc:1-3,
5a8809f8-42c4-11e5-9c60-f36f50446736:1-3,
5ecdf2ca-42c4-11e5-b861-a22aea18b64b:1-3,
630bab66-42c4-11e5-8807-bb8c8c1ce62c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         105 | 630bab66-42c4-11e5-8807-bb8c8c1ce62c |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 519dd7f0-42c4-11e5-8995-590303071a70:1-3,
5615010a-42c4-11e5-873f-9904485cf6bc:1-3,
5a8809f8-42c4-11e5-9c60-f36f50446736:1-3,
5ecdf2ca-42c4-11e5-b861-a22aea18b64b:1-3,
630bab66-42c4-11e5-8807-bb8c8c1ce62c:1-3

Here's the interesting thing, where the power of GTID is shown to its full potential. Although each endpoint is only connected to the hub, it gets all the GTID from the other nodes. This makes possible the procedure of hub replacement that we will see in a moment.

To show what the monitoring looks like in a star topology, we could run a full SHOW SLAVE STATUS for each node, but that would take too much space. Basically, you will see the same output that we have seen for all-masters, with a big difference: endpoints have only one channel, while the hub has 4. Let's see just a simplified account:

$ ./use_all 'SHOW SLAVE STATUS\G' | grep 'server:\|Running:\|Channel_Name:'
# server: 1:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: hub_node1
# server: 2:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: hub_node2
# server: 3:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: node1_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: node2_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: node4_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: node5_hub
# server: 4:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: hub_node4
# server: 5:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Channel_Name: hub_node5

So, you have a crowded situation in the hub and a simpler one in the endpoints. There is nothing new in the monitoring tables, compared to what we've seen in the past articles, therefore let's finish this chapter by seeing what happens with MariaDB 10.

$ ./set_star_topology.sh ma10.0.20 mariadb
installing node 1
installing node 2
installing node 3
installing node 4
installing node 5
group directory installed in $HOME/sandboxes/multi_msb_ma10_0_20
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# server: 5:
# option 'gtid_domain_id=1010' added to node1 configuration file
. sandbox server started
# option 'gtid_domain_id=1020' added to node2 configuration file
. sandbox server started
# option 'log-slave-updates' added to node3 configuration file
# option 'gtid_domain_id=1030' added to node3 configuration file
. sandbox server started
# option 'gtid_domain_id=1040' added to node4 configuration file
. sandbox server started
# option 'gtid_domain_id=1050' added to node5 configuration file
. sandbox server started
# HUB node3 port: 19023
# node node1 port: 19021
./node1/use -e "CHANGE MASTER 'hub_node1' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19023, MASTER_USE_GTID=current_pos "
./node3/use -e "CHANGE MASTER 'node1_hub' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19021, MASTER_USE_GTID=current_pos "
./node3/use -e "START SLAVE 'node1_hub' "
./node1/use -e "START SLAVE 'hub_node1' "
# node node2 port: 19022
./node2/use -e "CHANGE MASTER 'hub_node2' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19023, MASTER_USE_GTID=current_pos "
./node3/use -e "CHANGE MASTER 'node2_hub' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19022, MASTER_USE_GTID=current_pos "
./node3/use -e "START SLAVE 'node2_hub' "
./node2/use -e "START SLAVE 'hub_node2' "
# node node4 port: 19024
./node4/use -e "CHANGE MASTER 'hub_node4' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19023, MASTER_USE_GTID=current_pos "
./node3/use -e "CHANGE MASTER 'node4_hub' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19024, MASTER_USE_GTID=current_pos "
./node3/use -e "START SLAVE 'node4_hub' "
./node4/use -e "START SLAVE 'hub_node4' "
# node node5 port: 19025
./node5/use -e "CHANGE MASTER 'hub_node5' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19023, MASTER_USE_GTID=current_pos "
./node3/use -e "CHANGE MASTER 'node5_hub' TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=19025, MASTER_USE_GTID=current_pos "
./node3/use -e "START SLAVE 'node5_hub' "
./node5/use -e "START SLAVE 'hub_node5' "

The test script gives the same outcome that we've seen for MySQL 5.7. The status of the slave connections is only slightly different dues to the implementation:

$ ./use_all 'SHOW ALL SLAVES STATUS\G' | grep 'server:\|Running:\|Connection_name:'
# server: 1:
              Connection_name: hub_node1
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# server: 2:
              Connection_name: hub_node2
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# server: 3:
              Connection_name: node1_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Connection_name: node2_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Connection_name: node4_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Connection_name: node5_hub
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# server: 4:
              Connection_name: hub_node4
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# server: 5:
              Connection_name: hub_node5
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

What may be surprising is the content of the crash-safe table.

$ for N in 1 2 3 4 5; do ./n$N -e 'select @@server_id, @@gtid_domain_id; select * from mysql.gtid_slave_pos' ; done
+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         101 |             1010 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |      5 |       102 |      2 |
|      1020 |      6 |       102 |      3 |
|      1030 |      8 |       103 |      2 |
|      1030 |      9 |       103 |      3 |
|      1040 |     11 |       104 |      2 |
|      1040 |     12 |       104 |      3 |
|      1050 |     14 |       105 |      2 |
|      1050 |     15 |       105 |      3 |
+-----------+--------+-----------+--------+
+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         102 |             1020 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |      5 |       102 |      2 |
|      1020 |      6 |       102 |      3 |
|      1030 |      8 |       103 |      2 |
|      1030 |      9 |       103 |      3 |
|      1040 |     11 |       104 |      2 |
|      1040 |     12 |       104 |      3 |
|      1050 |     14 |       105 |      2 |
|      1050 |     15 |       105 |      3 |
+-----------+--------+-----------+--------+
+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         103 |             1030 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |      5 |       102 |      2 |
|      1020 |      6 |       102 |      3 |
|      1040 |      8 |       104 |      2 |
|      1040 |      9 |       104 |      3 |
|      1050 |     11 |       105 |      2 |
|      1050 |     12 |       105 |      3 |
+-----------+--------+-----------+--------+
+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         104 |             1040 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |      5 |       102 |      2 |
|      1020 |      6 |       102 |      3 |
|      1030 |      8 |       103 |      2 |
|      1030 |      9 |       103 |      3 |
|      1040 |     11 |       104 |      2 |
|      1040 |     12 |       104 |      3 |
|      1050 |     14 |       105 |      2 |
|      1050 |     15 |       105 |      3 |
+-----------+--------+-----------+--------+
+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         105 |             1050 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |      5 |       102 |      2 |
|      1020 |      6 |       102 |      3 |
|      1030 |      8 |       103 |      2 |
|      1030 |      9 |       103 |      3 |
|      1040 |     11 |       104 |      2 |
|      1040 |     12 |       104 |      3 |
|      1050 |     14 |       105 |      2 |
|      1050 |     15 |       105 |      3 |
+-----------+--------+-----------+--------+

Here we see that each endpoint shows the GTIDs of the data received from the other nodes, and the data produced in the endpoint itself. However, the hub only shows the data received from the endpoints, and not the data that was created in the hub. This bizarre situation is due to the position of log-slave-update in one node only. The issue was explained in one maria-developers thread, but it feels like a bug to me.

Replacing the hub

Since the hub is the SPOF in a star topology, before attempting to install one, we should at least have an idea of how to replace the hub in case of failure. In our test deployment, we have a candidate to replace the hub in case of need. Let's apply some load to the system by inserting data simultaneously in all endpoints, and killing the hub while the load goes on.

With the hub gone, we will see that the endpoints are not updated:

$ for N in 1 2 3 4 5; do ./n$N -e 'select @@server_id, @@server_uuid; select @@global.gtid_executed\G'; done
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 3d9dec68-42ce-11e5-86be-185460e24dd9 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-1163,
41918672-42ce-11e5-ae5e-27905d218c5f:1-236,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-82,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-75
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         102 | 41918672-42ce-11e5-ae5e-27905d218c5f |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-92,
41918672-42ce-11e5-ae5e-27905d218c5f:1-1163,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-82,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-75
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         104 | 4a72292c-42ce-11e5-9b80-e17cc877fb87 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-92,
41918672-42ce-11e5-ae5e-27905d218c5f:1-236,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-1163,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-75
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         105 | 4ef58dae-42ce-11e5-9981-28967a9fad86 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-92,
41918672-42ce-11e5-ae5e-27905d218c5f:1-236,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-82,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-1163

You see that for each node we get a large transaction number (it's the position reached by the endpoint itself) and for the other nodes we only have small numbers. Now we can promote node4 to be the new hub. To do so, we need to remove the connections between the dead hub and the endpoints, and replace them with the new ones.

./node4/use -e "STOP  SLAVE FOR CHANNEL 'hub_node4'"
./node4/use -e "STOP  SLAVE "
./node4/use -e "RESET  SLAVE FOR CHANNEL 'hub_node4'"
./node4/use -e "RESET  SLAVE "
# HUB node4 port: 8382
# node node1 port: 8379
./node1/use -e "STOP  SLAVE FOR CHANNEL 'hub_node1'"
./node1/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'hub_node1'"
./node4/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8379, MASTER_AUTO_POSITION=1 for channel 'node1_hub'"
./node4/use -e "START SLAVE FOR CHANNEL 'node1_hub'"
./node1/use -e "START SLAVE FOR CHANNEL 'hub_node1'"
# node node2 port: 8380
./node2/use -e "STOP  SLAVE FOR CHANNEL 'hub_node2'"
./node2/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'hub_node2'"
./node4/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8380, MASTER_AUTO_POSITION=1 for channel 'node2_hub'"
./node4/use -e "START SLAVE FOR CHANNEL 'node2_hub'"
./node2/use -e "START SLAVE FOR CHANNEL 'hub_node2'"
# node node5 port: 8383
./node5/use -e "STOP  SLAVE FOR CHANNEL 'hub_node5'"
./node5/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'hub_node5'"
./node4/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8383, MASTER_AUTO_POSITION=1 for channel 'node5_hub'"
./node4/use -e "START SLAVE FOR CHANNEL 'node5_hub'"
./node5/use -e "START SLAVE FOR CHANNEL 'hub_node5'"

After about one minute, we poll the system again, and we see that all the endpoints have caught up with the rest.

$ for N in 1 2 3 4 5; do ./n$N -e 'select @@server_id, @@server_uuid; select @@global.gtid_executed\G'; done
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 3d9dec68-42ce-11e5-86be-185460e24dd9 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-1163,
41918672-42ce-11e5-ae5e-27905d218c5f:1-1163,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-1163,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-1163
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         102 | 41918672-42ce-11e5-ae5e-27905d218c5f |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-1163,
41918672-42ce-11e5-ae5e-27905d218c5f:1-1163,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-1163,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-1163
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         104 | 4a72292c-42ce-11e5-9b80-e17cc877fb87 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-1163,
41918672-42ce-11e5-ae5e-27905d218c5f:1-1163,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-1163,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-1163
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         105 | 4ef58dae-42ce-11e5-9981-28967a9fad86 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 3d9dec68-42ce-11e5-86be-185460e24dd9:1-1163,
41918672-42ce-11e5-ae5e-27905d218c5f:1-1163,
45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3,
4a72292c-42ce-11e5-9b80-e17cc877fb87:1-1163,
4ef58dae-42ce-11e5-9981-28967a9fad86:1-1163

The small sequence that is still in the middle (45f1991e-42ce-11e5-a853-1f71d8e7014b:1-3) is the one belonging to the failed hub.

Hybrid topologies

What we have seen so far are clean cut topologies, where all our servers fit into a simple definition, be it master-slave,fan-in, all-masters, or star. But with the elements that we have examined, we can create topologies that are a mix of the simple ones.

The ingredients to create your own customized deployments are the following:

  • GTID: don't leave home without it! When dealing with complex topologies, knowing where your data comes from is paramount.
  • Point-to-point connections, the ones introduced by multi-source enhancements in MySQL 5.7 and MariaDB 10: You should never mix old fashion connections (with nameless channels: see MySQL replication in action - Part 2 - Fan-in topology)
  • log-slave-updates: by using this property wisely, you will create your own hubs when needed.

To make an example of what we can do, we will enhance a star topology to include two P2P masters and two regular slaves.

Topologies hybrid1

Figure 5 - A hybrid topology including 1 star, 2 P2P, and 2 simple slaves

In Figure 5, node #4 is connected with node #5 in P2P all-masters. Neither node has log-slave-updated enabled. From node #4 we also have a simple slave (node #6).

We have a mirror deployment with nodes #2, #7, and #8, but this time the connection node has log-slave-updated enabled.

If you want to try this at home, you can use a proof-of-concept from mysql-replication-samples. Here is the relevant chunk from that script:

./node1/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node1'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8379, MASTER_AUTO_POSITION=1 for channel 'node1_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node1_hub'"
./node1/use -e "START SLAVE FOR CHANNEL 'hub_node1'"
# node node2 port: 8380
./node2/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node2'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8380, MASTER_AUTO_POSITION=1 for channel 'node2_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node2_hub'"
./node2/use -e "START SLAVE FOR CHANNEL 'hub_node2'"
# node node4 port: 8382
./node4/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8381, MASTER_AUTO_POSITION=1 for channel 'hub_node4'"
./node3/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'node4_hub'"
./node3/use -e "START SLAVE FOR CHANNEL 'node4_hub'"
./node4/use -e "START SLAVE FOR CHANNEL 'hub_node4'"
# node node5 port: 8383
./node5/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'node4_node5'"
./node4/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8383, MASTER_AUTO_POSITION=1 for channel 'node5_node4'"
./node4/use -e "START SLAVE FOR CHANNEL 'node5_node4'"
./node5/use -e "START SLAVE FOR CHANNEL 'node4_node5'"

# node node6 port: 8384
./node6/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8382, MASTER_AUTO_POSITION=1 for channel 'node4_node6'"
./node6/use -e "START SLAVE FOR CHANNEL 'node4_node6'"

# node node7 port: 8385
./node7/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8380, MASTER_AUTO_POSITION=1 for channel 'node2_node7'"
./node2/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8385, MASTER_AUTO_POSITION=1 for channel 'node7_node2'"
./node7/use -e "START SLAVE FOR CHANNEL 'node2_node7'"
./node2/use -e "START SLAVE FOR CHANNEL 'node7_node2'"

# node node8 port: 8386
./node8/use -e "CHANGE MASTER TO master_host='127.0.0.1', master_user='rsandbox', master_password='rsandbox', master_port=8380, MASTER_AUTO_POSITION=1 for channel 'node2_node8'"
./node8/use -e "START SLAVE FOR CHANNEL 'node2_node8'"

To test the deployment, we create one table in every slave (all nodes except #6 and #8) and then check what each node has received from replication:

# server: 1:
t1  # (created)
t2  # host2-host3-host1
t3  # host3-host1
t4  # host4-host3-host1
t7  # host7-host2-host3-host1
# server: 2:
t1  # host1-host3-host2
t2  # (created)
t3  # host3-host2
t4  # host4-host3-host2
t7  # host7-host2
# server: 3:
t1  # host1-host3
t2  # host2-host3
t3  # (created)
t4  # host4-host3
t7  # host7-host2-host3
# server: 4:
t1  # host1-host3-host4
t2  # host2-host3-host4
t3  # host3-host4
t4  # (created)
t5  # host5-host4
t7  # host7-host2-host3-host4
# server: 5:
t4  # host4-host5
t5  # (created)
# server: 6:
t4  # host4
# server: 7:
t1  # host1-host3-host2-host7
t2  # host2-host7
t3  # host3-host2-host7
t4  # host4-host3-host2-host7
t7  # (created)
# server: 8:
t1 # host1-host3-host2-host8
t2 # host2-host3-host2-host8
t3 # host3-host2-host8
t4 # host4-host3-host2-host8
t7 # host7-host2-host8

Most interestingly, in node #5, which is in P2P with node #4, but without log-slave-updates, we only find what was produced in node#4. We get a similar outcome in node #6.

Conversely, in node #7 and #8 we get everything from every master, except from node #5, because it lacks the pass-through of log-slave-update in node #4, its connection to the cluster.

Topologies hybrid2

Figure 6 - A hybrid topology including 3 P2P, and 2 simple slaves

In a similar way, we can extend a all-masters P2P topology to have 2 more nodes in master-to-master replication with one node (Figure 6). The outcome of this deployment will be the same seen for Figure 5, with the difference that this topology requires more connections.

Topologies hybrid3

Figure 7 - A hybrid topology including 2 P2P clusters joined by a node.

Using the same principle, we can join two clusters by enabling log-slave-updates in one of their nodes and putting these enhanced nodes in P2P replication. In Figure 7 you see how you may join two all-masters P2P topologies.

Topologies hybrid4

Figure 8 - A hybrid topology including 2 star clusters joined by their hubs.

Joining two star topologies does not require any extra operations except running CHANGE MASTER TO, because the hubs are already enabled for dispatching events.

Topologies hybrid5

Figure 9 - A hybrid topology including 1 P2P cluster and a star cluster joined by a node.

Finally, you can join two different topologies by linking one node from each, like in figure 9.

Summing up

Star topologies are an interesting alternative to more robust deployments. They require more maintenance efforts in case of a failure, but they are agile and easy to expand.

Setting up a star topology makes us understand the building blocks of complex topologies. Armed with this knowledge, we can create hybrid topologies that fit our needs.

What's next

We have explored all the most interesting aspects of single and multiple master deployments. There are many more enhancements in replication, but the most striking one is the ability of running parallel appliers in the slave. This will be the focus of our next (and last) episode.

Monday, August 17, 2015

MySQL replication in action - Part 3: all-masters P2P topology

Previous episodes:


In the previous article, we saw the basics of establishing replication from multiple origins to the same destination. By extending that concept, we can deploy more complex topologies, such as the point-to-point (P2P) all-masters topology, a robust and fast way of moving data.

Introduction to P2P all-masters topology

A P2P (Point-to-point) topology is a kind of deployment where replication happens in a single step from the producer to the consumers. For example, in a master/slave topology, replication from the master (producer) reaches every slave (consumer) in one step. This is simple P2P replication. If we use a hierarchical deployment, where every slave that is connected to the master is also replicating to one or more slaves, we will have a 2-step replication (Figure 1). Similarly, in circular replication, we have as many steps as the number of nodes minus one (Figure 2.)

Hierarchical master slave processing

Figure 1 - Hierarchical replication depth of processing

Circular replication processing

Figure 2 - Circular replication depth of processing

Why is this important? The number of steps affects performance, resilience, and, potentially, accuracy.

  • Performance depends on the number of steps. Before the final leaf of the topology graph gets the data, it will replicate N times, one for each step. In figure 1, host4 will be updated twice as slower as host2. In Figure 2, host4 will be three times slower than host2, as it has to wait for two steps before data reaches its tables.
  • Resilience, or the capacity to withstand failures, also depends on the number of intermediate steps. Intermediate masters are single points of failure (SPOF) that can break a branch of the topology graph, or the whole deployment. In this context, a master/slave deployment has one SPOF; the topology in figure 1 has 2, and the circular replication has 4 of them.
  • Accuracy can be different if the data goes from master to slave directly, compared to the data going through one or more intermediaries. If data is applied and then extracted again, its chances of reaching the final destination unchanged depend on the intermediate masters to have exactly the same configuration as its predecessors in the chain.

With multi-source replication, we can overcome the limitations of circular topologies, and create a functionally corresponding deployment that has no SPOF, and it is, by virtue of its direct connections, faster and potentially more accurate than its predecessors.

Point to point all masters

Figure 3 - All-masters P2P replication

An all-masters P2P topology is a lot like fan-in topology, but with the number of nodes, masters, and slaves being the same. If all the nodes are fan-in slaves, and are also masters at the same time, every node can get data from the others and can send data at the same time.

Point to point all masters processing

Figure 4 - All-masters P2P replication depth of processing

In an all-masters P2P topology, each node replicates to every other node. Compared to circular replication, this deployment requires more connections per node (it's a small price to pay) but the data flows faster and more cleanly, as the origin of each transaction is easier to track.

Deploying a P2P all-masters topology in MySQL 5.7

The procedure is the same that we have seen for fan-in replication, but with a few differences:

  • Every node needs to be a master, and therefore it must have binary logs configured;
  • The procedure for connecting to the other nodes needs to be repeated for each node. In a N-node deployment, you will end up having, for each node, N-1 slave channels.

We will repeat the installation that we used for FAN-IN, running the same script from mysql-replication-samples. The difference in invocation will be that we ask for ALL-MASTERS:

$ ./multi_source.sh 5.7.8 mysql ALL-MASTERS
installing node 1
installing node 2
installing node 3
installing node 4
group directory installed in $HOME/sandboxes/multi_msb_5_7_8
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# option 'master-info-repository=table' added to node1 configuration file
# option 'relay-log-info-repository=table' added to node1 configuration file
# option 'gtid_mode=ON' added to node1 configuration file
# option 'enforce-gtid-consistency' added to node1 configuration file
# option 'master-info-repository=table' added to node2 configuration file
# option 'relay-log-info-repository=table' added to node2 configuration file
# option 'gtid_mode=ON' added to node2 configuration file
# option 'enforce-gtid-consistency' added to node2 configuration file
# option 'master-info-repository=table' added to node3 configuration file
# option 'relay-log-info-repository=table' added to node3 configuration file
# option 'gtid_mode=ON' added to node3 configuration file
# option 'enforce-gtid-consistency' added to node3 configuration file
# option 'master-info-repository=table' added to node4 configuration file
# option 'relay-log-info-repository=table' added to node4 configuration file
# option 'gtid_mode=ON' added to node4 configuration file
# option 'enforce-gtid-consistency' added to node4 configuration file
# executing "stop" on $HOME/sandboxes/multi_msb_5_7_8
executing "stop" on node 1
executing "stop" on node 2
executing "stop" on node 3
executing "stop" on node 4
# executing "start" on $HOME/sandboxes/multi_msb_5_7_8
executing "start" on node 1
. sandbox server started
executing "start" on node 2
. sandbox server started
executing "start" on node 3
. sandbox server started
executing "start" on node 4
. sandbox server started
# Setting topology ALL-MASTERS
# node node1
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8380, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node2'
START SLAVE for channel  'node2'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8381, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node3'
START SLAVE for channel  'node3'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8382, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node4'
START SLAVE for channel  'node4'
--------------

# node node2
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8379, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node1'
START SLAVE for channel  'node1'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8381, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node3'
START SLAVE for channel  'node3'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8382, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node4'
START SLAVE for channel  'node4'
--------------

# node node3
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8379, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node1'
START SLAVE for channel  'node1'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8380, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node2'
START SLAVE for channel  'node2'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8382, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node4'
START SLAVE for channel  'node4'
--------------

# node node4
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8379, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node1'
START SLAVE for channel  'node1'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8380, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node2'
START SLAVE for channel  'node2'
--------------
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=8381, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node3'
START SLAVE for channel  'node3'
--------------

$HOME/git/mysql-replication-samples/test_all_masters_replication.sh -> $HOME/sandboxes/multi_msb_5_7_8/test_all_masters_replication.sh

The procedure is similar, but since we are connecting all nodes instead of just one, the list of operations is longer. You can see that we have enabled GTID and crash-safe tables, as we did for FAN-IN, and we have executed a grand total of 12 'CHANGE MASTER TO' statements. AT the end of the installation, we have a test script that will tell us if replication is working. This script will create one table for each node, and then check that each node has got 4 tables

$ ./test_all_masters_replication.sh
# NODE node1 created table test_node1
# NODE node2 created table test_node2
# NODE node3 created table test_node3
# NODE node4 created table test_node4
# Data in all nodes
101
1 101 8379 node1 2015-08-12 19:40:35
1 102 8380 node2 2015-08-12 19:40:35
1 103 8381 node3 2015-08-12 19:40:35
1 104 8382 node4 2015-08-12 19:40:35
102
1 101 8379 node1 2015-08-12 19:40:35
1 102 8380 node2 2015-08-12 19:40:35
1 103 8381 node3 2015-08-12 19:40:35
1 104 8382 node4 2015-08-12 19:40:35
103
1 101 8379 node1 2015-08-12 19:40:35
1 102 8380 node2 2015-08-12 19:40:35
1 103 8381 node3 2015-08-12 19:40:35
1 104 8382 node4 2015-08-12 19:40:35
104
1 101 8379 node1 2015-08-12 19:40:35
1 102 8380 node2 2015-08-12 19:40:35
1 103 8381 node3 2015-08-12 19:40:35
1 104 8382 node4 2015-08-12 19:40:35

The output shows that each node has got 4 tables. Replication is working as expected. We can have a look at the monitoring options, to see how useful and clear they are in this topology.

As we did for fan-in topologies, we load the Sakila database in one of the nodes, to get some differences, and then look at the GTID situation:

$ for N in 1 2 3 4 ; do ./n$N -e 'select @@server_id, @@server_uuid; select @@global.gtid_executed\G'; done
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         101 | 18fd3be0-4119-11e5-97cd-24acf2bbd1e4 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         102 | 1e629814-4119-11e5-85cf-aac6e218d3d8 |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         103 | 226e3350-4119-11e5-8242-de985f123dfc |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
+-------------+--------------------------------------+
| @@server_id | @@server_uuid                        |
+-------------+--------------------------------------+
|         104 | 270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c |
+-------------+--------------------------------------+
*************************** 1. row ***************************
@@global.gtid_executed: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3

It's not a pretty sight. It's what we saw for fan-in, but multiplied by 4. Now we know that the price to pay for this efficient topology is an increase in monitoring complexity.

Let's have a look inside:

node1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 8380
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1164948
               Relay_Log_File: mysql-relay-node2.000002
                Relay_Log_Pos: 1165161
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1164948
              Relay_Log_Space: 1165370
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 102
                  Master_UUID: 1e629814-4119-11e5-85cf-aac6e218d3d8
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 1e629814-4119-11e5-85cf-aac6e218d3d8:1-119
            Executed_Gtid_Set: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node2
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 8381
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 891
               Relay_Log_File: mysql-relay-node3.000002
                Relay_Log_Pos: 1104
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 891
              Relay_Log_Space: 1313
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 103
                  Master_UUID: 226e3350-4119-11e5-8242-de985f123dfc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 226e3350-4119-11e5-8242-de985f123dfc:1-3
            Executed_Gtid_Set: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node3
*************************** 3. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 8382
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 891
               Relay_Log_File: mysql-relay-node4.000002
                Relay_Log_Pos: 1104
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 891
              Relay_Log_Space: 1313
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 104
                  Master_UUID: 270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
            Executed_Gtid_Set: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: node4
3 rows in set (0.00 sec)

This is a partial view of replication in this deployment. It only applies to node #1, where we see the status of its slave channels. We need to run the same command in all nodes to make sure that replication is healthy everywhere. As mentioned before, we have 12 channels to monitor. Looking at one node only will give us a possibly misleading picture.

And here we can see once more why it was a bad decision not to have a table for master status:

node1 [localhost] {msandbox} (mysql) > show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 891
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,
1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,
226e3350-4119-11e5-8242-de985f123dfc:1-3,
270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3
1 row in set (0.00 sec)

If we want to match GTID positions in master and slave, we need to get the value of Executed_Gtid_set from master status, or the same information from @@global.gtid_executed, then find the GTID containing the server_uuid belonging to this master within that long string, and finally extract the GTID sequence.

  1. Get the raw info: "18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3,1e629814-4119-11e5-85cf-aac6e218d3d8:1-119,226e3350-4119-11e5-8242-de985f123dfc:1-3,270c0ebe-4119-11e5-a1c9-b7fbc4e42c2c:1-3"
  2. Find the server UUID: "18fd3be0-4119-11e5-97cd-24acf2bbd1e4"
  3. Find the relevant GTID: "18fd3be0-4119-11e5-97cd-24acf2bbd1e4:1-3"
  4. Extract the GTID: "3"

The information in mysql.slave_relay_log_info and performance_schema.replication_* tables will not help us to simplify the task of monitoring replication. All the shortcomings that we have noticed for fan-in are also present for all-masters topologies. The main difference is that the information in SHOW MASTER STATUS and SHOW SLAVE STATUS is more crowded.

Deploying a P2P all-masters topology in MariaDB 10

The installation is fairly similar to MySQL 5.7. We only see the same syntax differences already noted for fan-in topologies.

$ ./multi_source.sh ma10.0.20 mariadb ALL-MASTERS
installing node 1
installing node 2
installing node 3
installing node 4
group directory installed in $HOME/sandboxes/multi_msb_ma10_0_20
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# Setting topology ALL-MASTERS
# node node1
--------------
CHANGE MASTER 'node2' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19022, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node2'
--------------
CHANGE MASTER 'node3' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19023, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node3'
--------------
CHANGE MASTER 'node4' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19024, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node4'
--------------

# node node2
--------------
CHANGE MASTER 'node1' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19021, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node1'
--------------
CHANGE MASTER 'node3' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19023, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node3'
--------------
CHANGE MASTER 'node4' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19024, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node4'
--------------

# node node3
--------------
CHANGE MASTER 'node1' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19021, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node1'
--------------
CHANGE MASTER 'node2' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19022, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node2'
--------------
CHANGE MASTER 'node4' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19024, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node4'
--------------

# node node4
--------------
CHANGE MASTER 'node1' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19021, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node1'
--------------
CHANGE MASTER 'node2' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19022, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node2'
--------------
CHANGE MASTER 'node3' TO MASTER_HOST='127.0.0.1', MASTER_PORT=19023, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_USE_GTID=current_pos
START SLAVE  'node3'
--------------

And the test script produces similar results:

$ ./test_all_masters_replication.sh
# NODE node1 created table test_node1
# NODE node2 created table test_node2
# NODE node3 created table test_node3
# NODE node4 created table test_node4
# Data in all nodes
101
1 101 19021 node1 2015-08-12 20:20:46
1 102 19021 node2 2015-08-12 20:20:47
1 103 19021 node3 2015-08-12 20:20:47
1 104 19021 node4 2015-08-12 20:20:47
102
1 101 19022 node1 2015-08-12 20:20:46
1 102 19022 node2 2015-08-12 20:20:47
1 103 19022 node3 2015-08-12 20:20:47
1 104 19022 node4 2015-08-12 20:20:47
103
1 101 19023 node1 2015-08-12 20:20:46
1 102 19023 node2 2015-08-12 20:20:47
1 103 19023 node3 2015-08-12 20:20:47
1 104 19023 node4 2015-08-12 20:20:47
104
1 101 19024 node1 2015-08-12 20:20:46
1 102 19024 node2 2015-08-12 20:20:47
1 103 19024 node3 2015-08-12 20:20:47
1 104 19024 node4 2015-08-12 20:20:47

After loading the Sakila database into node #2, we see a familiar pattern, already noted for fan-in. The GTID is shown as a comma delimited list of all the data streams that have conveyed in each server.

$ for N in 1 2 3 4; do ./n$N -e 'select @@server_id; select @@global.gtid_current_pos\G' ; done
+-------------+
| @@server_id |
+-------------+
|         101 |
+-------------+
*************************** 1. row ***************************
@@global.gtid_current_pos: 1020-102-119,1040-104-3,1030-103-3,1010-101-3
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
*************************** 1. row ***************************
@@global.gtid_current_pos: 1010-101-3,1040-104-3,1030-103-3,1020-102-119
+-------------+
| @@server_id |
+-------------+
|         103 |
+-------------+
*************************** 1. row ***************************
@@global.gtid_current_pos: 1010-101-3,1040-104-3,1020-102-119,1030-103-3
+-------------+
| @@server_id |
+-------------+
|         104 |
+-------------+
*************************** 1. row ***************************
@@global.gtid_current_pos: 1010-101-3,1030-103-3,1020-102-119,1040-104-3

Looking at SHOW ALL SLAVES STATUS, there are no surprises. The information that was missing from fan-in (GTID executed) is still missing from the slave status.

node1 [localhost] {msandbox} ((none)) > SHOW ALL SLAVES STATUS\G
*************************** 1. row ***************************
              Connection_name: node2
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 19022
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3230973
               Relay_Log_File: mysql-relay-node2.000002
                Relay_Log_Pos: 3231260
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3230973
              Relay_Log_Space: 3231559
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 102
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Current_Pos
                  Gtid_IO_Pos: 1020-102-119
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 263
    Slave_received_heartbeats: 3
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 1020-102-119,1040-104-3,1030-103-3,1010-101-3
*************************** 2. row ***************************
              Connection_name: node3
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 19023
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 882
               Relay_Log_File: mysql-relay-node3.000002
                Relay_Log_Pos: 1169
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 882
              Relay_Log_Space: 1468
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 103
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Current_Pos
                  Gtid_IO_Pos: 1030-103-3
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 14
    Slave_received_heartbeats: 3
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 1020-102-119,1040-104-3,1030-103-3,1010-101-3
*************************** 3. row ***************************
              Connection_name: node4
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 19024
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 882
               Relay_Log_File: mysql-relay-node4.000002
                Relay_Log_Pos: 1169
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 882
              Relay_Log_Space: 1468
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 104
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Current_Pos
                  Gtid_IO_Pos: 1040-104-3
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 14
    Slave_received_heartbeats: 3
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 1020-102-119,1040-104-3,1030-103-3,1010-101-3
3 rows in set (0.00 sec)

The contents of the crash-safe data does not offer surprises either. It's the same that we've seen for fan-in, multiplied by 4.

$ for N in 1 2 3 4; do ./n$N -e 'select @@server_id, @@gtid_domain_id; select * from mysql.gtid_slave_pos' ; done
+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         101 |             1010 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1020 |    124 |       102 |    118 |
|      1020 |    125 |       102 |    119 |
|      1030 |      5 |       103 |      2 |
|      1030 |      6 |       103 |      3 |
|      1040 |      8 |       104 |      2 |
|      1040 |      9 |       104 |      3 |
+-----------+--------+-----------+--------+

+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         102 |             1020 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1030 |      5 |       103 |      2 |
|      1030 |      6 |       103 |      3 |
|      1040 |      8 |       104 |      2 |
|      1040 |      9 |       104 |      3 |
+-----------+--------+-----------+--------+

+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         103 |             1030 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |    124 |       102 |    118 |
|      1020 |    125 |       102 |    119 |
|      1040 |      8 |       104 |      2 |
|      1040 |      9 |       104 |      3 |
+-----------+--------+-----------+--------+

+-------------+------------------+
| @@server_id | @@gtid_domain_id |
+-------------+------------------+
|         104 |             1040 |
+-------------+------------------+
+-----------+--------+-----------+--------+
| domain_id | sub_id | server_id | seq_no |
+-----------+--------+-----------+--------+
|      1010 |      2 |       101 |      2 |
|      1010 |      3 |       101 |      3 |
|      1020 |    124 |       102 |    118 |
|      1020 |    125 |       102 |    119 |
|      1030 |      8 |       103 |      2 |
|      1030 |      9 |       103 |      3 |
+-----------+--------+-----------+--------+

Summing up

Using the methods already learned for fan-in deployments, an all-masters P2P topology is easy to install, albeit longer and more complex.

Monitoring this topology presents the same hurdles already seen for fan-in, increased by the number of connections. For a N-node deployment, we will need to monitor N*(N-1) channels.

The lack of a table for master status is felt more acutely in this topology, as the current data is more difficult to parse.

What's next

This topology shows that we can deploy a very efficient multi-source replication system, at the expense of having many connections and enduring more complex monitoring data.

We can, however, compromise between the need of having many masters and the complexity of the deployment. We will see the star topology, where, by introducing a SPOF in the system we can deploy a more agile all-masters topology. And we will also see some hybrid deployments, all made possible by the multi-source enhancements in MySQL 5.7 and MariaDB 10.

Vote on Planet MySQL