In recent times I have been designing several solutions focused on High Availability and Disaster Recovery. Some of them using Percona Server for MySQL with group replication, some using Percona XtraDB Cluster (PXC). What many of them had in common was the use of ProxySQL for the connection layer. This is because I consider the use of a layer 7 Proxy preferable, given the possible advantages provided in ReadWrite split and SQL filtering. 

The other positive aspect provided by ProxySQL, at least for Group Replication, is the native support which allows us to have a very quick resolution of possible node failures.

ProxySQL has Galera support as well, but in the past, that had shown to be pretty unstable, and the old method to use the scheduler was still the best way to go.

After Percona Live Online 2020 I decided to try it again and to see if at least the basics were now working fine. 

What I Have Tested

I was not looking for complicated tests that would have included different levels of transaction isolation. I was instead interested in the more simple and basic ones. My scenario was:

1 ProxySQL node v2.0.15  (192.168.4.191)
1 ProxySQL node v2.1.0  (192.168.4.108)
3 PXC 8.20 nodes (192.168.4.22/23/233) with internal network (10.0.0.22/23/33) 

ProxySQL was freshly installed. 

All the commands used to modify the configuration are here. Tests were done first using ProxySQL v2.015 then v2.1.0. Only if results diverge I will report the version and results. 

PXC- Failover Scenario

As mentioned above I am going to focus on the fail-over needs, period. I will have two different scenarios:

  • Maintenance
  • Node crash 

From the ProxySQL point of view I will have three scenarios always with a single Primary:

  • Writer is NOT a reader (option 0 and 2)
  • Writer is also a reader

The configuration of the native support will be:

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('192.168.4.22',100,3306,10000,2000,'DC1');
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('192.168.4.22',101,3306,100,2000,'DC1');
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('192.168.4.23',101,3306,10000,2000,'DC1');    
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections,comment) VALUES ('192.168.4.233',101,3306,10000,2000,'DC1');

Galera host groups:

  • Writer: 100
  • Reader: 101
  • Backup_writer: 102
  • Offline_hostgroup: 9101

Before going ahead let us analyze the Mysql Servers settings. As you can notice I am using the weight attribute to indicate ProxySQL which is my preferred write. But I also use weight for the READ Host Group to indicate which servers should be used and how.

Given that we have that:

  • Write
    • 192.168.4.22  is the preferred Primary
    • 192.168.4.23  is the first failover 
    • 192.168.4.233 is the last chance 
  • Read
    • 192.168.4.233/23 have the same weight and load should be balanced between the two of them
    • The 192.168.4.22 given is the preferred writer should NOT receive the same load in reads and have a lower weight value.  

The Tests

First Test

The first test is to see how the cluster will behave in the case of 1 Writer and 2 readers, with the option writer_is_also_reader = 0.
To achieve this the settings for proxysql will be:

insert into mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values (100,102,101,9101,1,1,0,10);

As soon as I load this to runtime, ProxySQL should move the nodes to the relevant Host Group. But this is not happening, instead, it keeps the readers in the writer HG and SHUN them.

+--------+-----------+---------------+----------+---------+
| weight | hostgroup | srv_host      | srv_port | status  |
+--------+-----------+---------------+----------+---------+
| 10000  | 100       | 192.168.4.233 | 3306     | ONLINE  |
| 10000  | 100       | 192.168.4.23  | 3306     | SHUNNED |
| 10000  | 100       | 192.168.4.22  | 3306     | SHUNNED |
| 10000  | 102       | 192.168.4.23  | 3306     | ONLINE  |
| 10000  | 102       | 192.168.4.22  | 3306     | ONLINE  |
+--------+-----------+---------------+----------+---------+

This is, of course, wrong. But why does it happen?

The reason is simple. ProxySQL is expecting to see all nodes in the reader group with READ_ONLY flag set to 1. 

In ProxySQL documentation we can read:

writer_is_also_reader=0: nodes with read_only=0 will be placed either in the writer_hostgroup and in the backup_writer_hostgroup after a topology change, these will be excluded from the reader_hostgroup.

This is conceptually wrong. 

A PXC cluster is a tightly coupled replication cluster, with virtually synchronous replication. One of its benefits is to have the node “virtually” aligned with respect to the data state. 

In this kind of model, the cluster is data-centric, and each node shares the same data view.

tightly coupled

What it also means is that if correctly set the nodes will be fully consistent in data READ.

The other characteristic of the cluster is that ANY node can become a writer anytime.  While best practices indicate that it is better to use one Writer a time as Primary to prevent certification conflicts, this does not mean that the nodes not currently elected as Primary, should be prevented from becoming a writer.

Which is exactly what READ_ONLY flag does if activated.

Not only, the need to have READ_ONLY set means that we must change it BEFORE we have the node able to become a writer in case of fail-over. 

This, in short, means the need to have either a topology manager or a script that will do that with all the relative checks and logic to be safe. Which in time of fail-over means it will add time and complexity when it’s not really needed and that goes against the concept of the tightly-coupled cluster itself.

Given the above, we can say that this ProxySQL method related to writer_is_also_reader =0, as it is implemented today for Galera, is, at the best, useless. 

Why is it working for Group Replication? That is easy; because Group Replication internally uses a mechanism to lock/unlock the nodes when non-primary, when using the cluster in single Primary mode. That internal mechanism was implemented as a security guard to prevent random writes on multiple nodes, and also manage the READ_ONLY flag. 

Second Test

Let us move on and test with writer_is_also_reader = 2. Again from the documentation:

writer_is_also_reader=2 : Only the nodes with read_only=0 which are placed in the backup_writer_hostgroup are also placed in the reader_hostgroup after a topology change i.e. the nodes with read_only=0 exceeding the defined max_writers.

Given the settings as indicated above, my layout before using Galera support is:

For what reason should I use a real multi-Primary setup?
To be clear, not a multi-writer solution where any node can become the active writer in case of needs, as for PXC or PS-Group_replication.
No, we are talking about a multi-Primary setup where I can write at the same time on multiple nodes.
I want to insist on this “why?”.

After having excluded the possible solutions mentioned above, both covering the famous 99,995% availability, which is 26.30 minutes downtime in a year, what is left?

Disaster Recovery? Well that is something I would love to have, but to be a real DR solution we need to put several kilometers (miles for imperial) in the middle. 

And we know (see here and here) that aside some misleading advertising, we cannot have a tightly coupled cluster solution across geographical regions.

So, what is left? I may need more HA, ok that is a valid reason. Or I may need to scale the number of writes, ok that is a valid reason as well.
This means, at the end, that I am looking to a multi-Primary because:

  • Scale writes (more nodes more writes)
    • Consistent reads (what I write on A must be visible on B)
  • Gives me 0 (zero) downtime, or close to that (5 nines is a maximum downtime of 864 milliseconds per day!!)
  • Allow me to shift the writer pointer at any time from A to B and vice versa, consistently.   

Now, keeping myself bound to the MySQL ecosystem, my natural choice would be MySQL NDB cluster.

But my (virtual) boss was at AWS re-invent and someone mentioned to him that Aurora Multi-Primary does what I was looking for.

This (long) article is my voyage in discovering if that is true or … not.

Given I am focused on the behaviour first, and NOT interested in absolute numbers to shock the audience with millions of QPS, I will use low level Aurora instances. And will perform tests from two EC2 in the same VPC/region of the nodes.

instances

You can find the details about the tests on GitHub here Finally I will test:

  • Connection speed
  • Stale read
  • Write single node for baseline
  • Write on both node:
    • Scaling splitting the load by schema
    • Scaling same schema 

Tests results

Let us start to have some real fun. The first test is … 

Connection Speed

The purpose of this test is to evaluate the time taken in opening a new connection and time taken to close it. The action of open/close connection can be a very expensive operation especially if applications do not use a connection pool mechanism.

a1

a3

As we can see ProxySQL results to be the most efficient way to deal with opening connections, which was expected given the way it is designed to reuse open connections towards the backend. 

a2

a4

Different is the close connection operation in which ProxySQL seems to take a little bit longer.  As global observation we can say that using ProxySQL we have more consistent behaviour. Of course this test is a simplistic one, and we are not checking the scalability (from 1 to N connections) but it is good enough to give us the initial feeling. Specific connection tests will be the focus of the next blog on Aurora MM. 

Stale Reads

Aurora MultiPrimary use the same mechanism of the default Aurora to update the buffer pool:

aurora multi master sharing BP

Using the Page Cache update, just doing both ways. This means that the Buffer Pool of Node2 is updated with the modification performed in Node1 and vice versa.

To verify if an application would be really able to have consistent reads, I have run this test. This test is meant to measure if, and how many, stale reads we will have when writing on a node and reading from the other.

Amazon Aurora multi Primary has 2 consistency model:

Consistency model

As an interesting fact the result was that with the default consistency model (INSTANCE_RAW), we got 100% stale read.

Given that I focused on identifying the level of the cost that exists when using the other consistency model (REGIONAL_RAW) that allows an application to have consistent reads.

The results indicate an increase of the 44% in total execution time, and of the 95% (22 time slower) in write execution. 

a5

a6

a7

It is interesting to note that the time taken is in some way predictable and consistent between the two consistency models. 

The graph below shows in yellow how long the application must wait to see the correct data on the reader node. While in blue is the amount of time the application waits to get back the same consistent read because it must wait for the commit on the writer.

   a8

As you can see the two are more or less aligned. Given the performance cost imposed by using REGIONAL_RAW,  all the other tests are done the defaut INSTANCE_RAW, unless explicitly stated.

Writing tests

All tests run in this section were done using sysbench-tpcc with the following settings: 

sysbench ./tpcc.lua --mysql-host=<> --mysql-port=3306 --mysql-user=<> --mysql-password=<> --mysql-db=tpcc --time=300 --threads=32 --report-interval=1 --tables=10 --scale=15  --mysql_table_options=" CHARSET=utf8 COLLATE=utf8_bin"  --db-driver=mysql prepare

sysbench /opt/tools/sysbench-tpcc/tpcc.lua --mysql-host=$mysqlhost --mysql-port=$port --mysql-user=<> --mysql-password=<> --mysql-db=tpcc --db-driver=mysql --tables=10 --scale=15 --time=$time  --rand-type=zipfian --rand-zipfian-exp=0 --report-interval=1 --mysql-ignore-errors=all --histogram  --report_csv=yes --stats_format=csv --db-ps-mode=disable --threads=$threads run

Write Single node (Baseline)

Before starting the comparative analysis, I was looking to define what was the “limit” of traffic/load for this platform. 

Picture 1

t1 t2

From the graph above, we can see that this setup scales up to 128 threads after that, the performance remains more or less steady. 

Amazon claims that we can mainly double the performance when using both nodes in write mode and use a different schema to avoid conflict.

scalability

 

Once more remember I am not interested in the absolute numbers here, but I am expecting the same behaviour Given that our expectation is to see:

Picture 2

Write on both nodes different schemas

So AWS recommend this as the scaling solution:

split traffic by db table partition to avoid conflicts

And I diligently follow the advice.

I used 2 EC2 nodes in the same subnet of the Aurora Node, writing to a different schema (tpcc & tpcc2). 

Overview

Let us make it short and go straight to the point. Did we get the expected scalability?

Well no:

Picture 3

We just had 26% increase, quite far to be the expected 100% Let us see what happened in detail (if not interested just skip and go to the next test).

Node 1

Picture 5

Node 2

Picture 6

As you can see Node1 was (more or less) keeping up with the expectations and being close to the expected performance.
But Node2 was just not keeping up, performances there were just terrible. 
The graphs below show what happened.

While Node1 was (again more or less) scaling up to the baseline expectations (128 threads), Node2 collapsed on its knees at 16 threads. Node2 was never able to scale up.

Reads

Node 1

t4

Node1 is scaling the reads as expected also if here and there we can see performance deterioration.

Node 2

t7

Node2 is not scaling Reads at all. 

Writes

Node 1

t5

Same as Read

Node 2

t8

Same as read

Now someone may think I was making a mistake and I was writing on the same schema. I assure you I was not.

Check the next test to see what happened if using the same schema.  

Write on both nodes same schema

Overview

Now, now Marco, this is unfair. You know this will cause contention.

Yes I do! But nonetheless I was curious to see what was going to happen and how the platform would deal with that level of contention. 
My expectations were to have a lot of performance degradation and increased number of locks. About conflict I was not wrong, node2 after the test reported: 

+-------------+---------+-------------------------+
| table       | index   | PHYSICAL_CONFLICTS_HIST |
+-------------+---------+-------------------------+
| district9   | PRIMARY |                    3450 |
| district6   | PRIMARY |                    3361 |
| district2   | PRIMARY |                    3356 |
| district8   | PRIMARY |                    3271 |
| district4   | PRIMARY |                    3237 |
| district10  | PRIMARY |                    3237 |
| district7   | PRIMARY |                    3237 |
| district3   | PRIMARY |                    3217 |
| district5   | PRIMARY |                    3156 |
| district1   | PRIMARY |                    3072 |
| warehouse2  | PRIMARY |                    1867 |
| warehouse10 | PRIMARY |                    1850 |
| warehouse6  | PRIMARY |                    1808 |
| warehouse5  | PRIMARY |                    1781 |
| warehouse3  | PRIMARY |                    1773 |
| warehouse9  | PRIMARY |                    1769 |
| warehouse4  | PRIMARY |                    1745 |
| warehouse7  | PRIMARY |                    1736 |
| warehouse1  | PRIMARY |                    1735 |
| warehouse8  | PRIMARY |                    1635 |
+-------------+---------+-------------------------+

Which is obviously a strong indication something was not working right. In terms of performance gain, if we compare ONLY the result with the 128 Threads : Picture 4

Also with the high level of conflict we still have 12% of performance gain.

The problem is that in general we have the two nodes behave quite badly.
If you check the graph below you can see that the level of conflict is such to prevent the nodes not only to scale but to act consistently.

Node 1

Picture 7

Node 2

Picture 8

Reads

In the following graphs we can see how node1 had issues and it actually crashed 3 times, during tests with 32/64/512 treads.
Node2 was always up but the performances were very low. 

Node 1

t10

Node 2

t13

Writes

Node 1

t11

Node 2

t14

Recovery from crashed Node

About recovery time reading the AWS documentation and listening to presentations, I often heard that Aurora Multi Primary is a 0 downtime solution.
Or other statements like: “in applications where you can't afford even brief downtime for database write operations, a multi-master cluster can help to avoid an outage when a writer instance becomes unavailable. The multi-master cluster doesn't use the failover mechanism, because it doesn't need to promote another DB instance to have read/write capability”

To achieve this the suggestion I found, was to have applications pointing directly to the Nodes endpoint and not use the Cluster endpoint.
In this context the solution pointing to the Nodes should be able to failover within a seconds or so, while the cluster endpoint:

fail over times using mariadb driver

Personally I think that designing an architecture where the application is responsible for the connection to the database and failover is some kind of refuse from 2001. But if you feel this is the way, well go for it.

What I did for testing is to use ProxySQL, as plain as possible, with nothing else then the basic monitor coming from the native monitor.

I then compare the results with the tests using the Cluster endpoint.
In this way I adopt the advice of pointing directly at the nodes, but I was doing things in our time.  

The results are below and they confirm (more or less) the data coming from Amazon.

a10

A downtime of 7 seconds is quite a long time nowadays, especially if I am targeting the 5 nines solution that I want to remember is 864 ms downtime per day.

Using ProxySQL is going closer to that, still too long to be called 0 (zero) downtime.

I also have fail-back issues when using the AWS cluster endpoint.

Given it was not able to move the connection to the joining node seamlessly. 

Last but not least when using the consistency level INSTANCE_RAW, I had some data issue as well as PK conflict:
FATAL: mysql_drv_query() returned error 1062 (Duplicate entry '18828082' for key 'PRIMARY')   

Conclusions

As state the beginning of this long blog the reasons expectations to go for a multi Primary solution were:

  • Scale writes (more nodes more writes)
  • Gives me 0 (zero) downtime, or close to that (5 nines is a maximum downtime of 864 milliseconds per day!!)
  • Allow me to shift the writer pointer at any time from A to B and vice versa, consistently.   

Honestly I feel we have completely failed the scaling point.

Facepalm Jesus

Probably if I use the largest Aurora I will get much better absolute numbers, and it will take me more to encounter the same issues, but I will.

In any case if the Multi muster solution is designed to provide that scalability, it should do that with any version.

I did not have zero downtime, but I was able to failover pretty quickly with ProxySQL.

Finally, unless the consistency model is REGIONAL_RAW, shifting from one node to the other is not prone to possible negative effects like stale reads.

Because that I consider this requirement not satisfied in full. 

Given all the above, I think this solution could eventually be valid only for High Availability (close to be 5 nines), but given it comes with some limitations I do not feel comfortable in preferring it over others just for HA, at the end default Aurora is already good enough as a High available solution. 

references

https://www.youtube.com/watch?v=p0C0jakzYuc

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-multi-master.html

https://www.slideshare.net/marcotusa/improving-enterprises-ha-and-disaster-recovery-solutions-reviewed

https://www.slideshare.net/marcotusa/robust-ha-solutions-with-proxysql

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-multi-master.html#aurora-multi-master-limitations  

mysql community performance cloud Aurora scaling high availability

If you have a business no matter how small, you are collecting data, and you need to have your data accessible to make informed decisions about how to make your business better. The more successful you become the more data you are producing and the more you become dependent by it. This is when you start to realize your must have your data in a safe place like a database instead some spreadsheet.

But to put your data in a database is not enough, you must be sure the database will be robust, resilient, and always available when you or your customers need it.

When design architectures for robust database architectures, we always discuss about High Availability (HA) and Disaster Recovery (DR). These two concepts are elements of the larger umbrella that is the Business continuity plan.

To cover the different needs, we (Database Architects) use/apply two main models: Tightly Coupled cluster, and Loosely Coupled cluster (latest presentation https://www.slideshare.net/marcotusa/best-practicehigh-availabilitysolutiongeodistributedfinal).

The combination of the two different model allow us to build solid, resilient, scalable HA solution geographically distributed.

Like this:

Picture1

 

Until now the part that was NOT natively supported in the architecture above was how to failover the replication channel from a node to another node in case of crash.

This element is currently cover by custom script(https://github.com/y-trudeau/Mysql-tools/tree/master/PXC) develop by my colleague Yves Trudeau.

 

But Oracle in MySQL 8.0.22 introduced the Asynchronous failover feature. In this blog I am going to check if this feature is enough to cover what we need to avoid using external movable/custom script or if instead there is still work to do.

My scenario is the following:

I have a PXC 8.0.20 cluster on DC1 and I am going to replicate towards a Group Replication cluster 8.0.22.

First, I need to establish the replication, to do so I follow the standard steps:

Once I am up and running and have a situation like this:

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 38809616-e149-11ea-b0cc-080027d0603e | gr1         |        3306 |       ONLINE |     PRIMARY |         8.0.22 |
| group_replication_applier | 959cc074-e14b-11ea-b90c-080027d0603e | gr2         |        3306 |       ONLINE |   SECONDARY |         8.0.22 |
| group_replication_applier | a56b38ec-e14b-11ea-b9ce-080027d0603e | gr3         |        3306 |       ONLINE |   SECONDARY |         8.0.22 |
| group_replication_applier | b7039e3b-f744-11ea-854c-08002734ed50 | gr4         |        3306 |       ONLINE |   SECONDARY |         8.0.22 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

 

Then I am ready to create the replication channel.

To do so I am first doing keeping the OLD style without Auto-failover. To do so:

On one of the PCX nodes:

Create user replica_dc@’192.168.4.%’ identified by ‘secret’;
Grant REPLICATION SLAVE on *.* to replica_dc@’192.168.4.%’;

On the PRIMARY of my Grou Replication cluster:

CHANGE MASTER to
master_user='<user>',
master_password='<secret>',
master_host='192.168.4.22',
master_auto_position=1,
source_connection_auto_failover=0,
master_retry_count=6,
master_connect_retry=10
for channel "dc1_to_dc2";

 

A brief explanation about the above:

  • source_connection_auto_failover=0,  <--This enable/disable auto-failover
  • master_retry_count=6,                       <-- The number of attempts
  • master_connect_retry=10                  <-- Specifies the interval between reconnection attempts

Once Replication is up, we will have this scenario:

async failover 8022 pxc base async1

Now in case of a crash of the PXC node from which I replicate for, my Group replication cluster will stop replicating:

async failover 8022 pxc base async2

To prevent this to happen I will use the new functionality to allow Asynchronous replication failover.

To do so the first thing I have to do is to add the list of possible SOURCE in the new table that is locate in MySQL (mysql.replication_asynchronous_connection_failover), to do it I don’t need to use an insert command but instead a function:

asynchronous_connection_failover_add_source(channel, host, port, network_namespace, weight)

where:

  • channel: The replication channel for which this replication source server is part of the source list.
  • host: The host name for this replication source server.
  • port: The port number for this replication source server.
  • network_namespace: The network namespace for this replication source server (when specified).
  • weight: The priority of this replication source server

In my case I will have:

SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.22', 3306, null, 100);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.23', 3306, null, 80);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.233', 3306, null, 50);

Now, and this is important, given the information is located in a table in the mysql.schema , the information present in the Primary is automatically replicated all over the cluster. Given that all nodes SEE the list of potential Source.

But given we are running our Group Replication cluster in Single Primary mode we can have only the Primary acting as REPLICA, because only the Primary can write. Hope that is clear.

To activate the failover we just need to pass the command:

First let us check the status:

SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';
+--------------+---------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------+---------------------------------+
| dc1_to_dc2   | 0                               |
+--------------+---------------------------------+


Ok all good I can activate the auto_failover:

stop slave for channel 'dc1_to_dc2';
CHANGE MASTER TO source_connection_auto_failover=1 for channel "dc1_to_dc2";
start slave for channel 'dc1_to_dc2';
SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';

+--------------+---------------------------------+
| CHANNEL_NAME | SOURCE_CONNECTION_AUTO_FAILOVER |
+--------------+---------------------------------+
| dc1_to_dc2   | 1                               |
+--------------+---------------------------------+

Perfect all is running, and this is my layout now:

async failover 8022 pxc base async3

At this point if something happens to my current SOURCE the auto-failover will be triggered, and I should see my GR Primary move to the other node.

After I kill the pxc_node2 the error log of the primary will present:

[ERROR] [MY-010584] [Repl] Slave I/O for channel 'dc1_to_dc2': error reconnecting to master This email address is being protected from spambots. You need JavaScript enabled to view it.:3306' - retry-time: 10 retries: 1 message: Can't connect to MySQL server on '192.168.4.23' (111), Error_code: MY-002003
...
[ERROR] [MY-010584] [Repl] Slave I/O for channel 'dc1_to_dc2': error reconnecting to master This email address is being protected from spambots. You need JavaScript enabled to view it.:3306' - retry-time: 10 retries: 6 message: Can't connect to MySQL server on '192.168.4.23' (111), Error_code: MY-002003

[System] [MY-010562] [Repl] Slave I/O thread for channel 'dc1_to_dc2': connected to master This email address is being protected from spambots. You need JavaScript enabled to view it.:3306',replication started in log 'FIRST' at position 53656167
[Warning] [MY-010549] [Repl] The master's UUID has changed, although this should not happen unless you have changed it manually. The old UUID was 28ae74e9-12c7-11eb-8d57-08002734ed50.

The Primary identify the SOURCE failed, try N time as asked waiting for each try T seconds. After that it try to change the master choosing it form the given list. If we use a different weight value, the node that will be elected will be the active one with the highest weight value.

Once a node is elected as SOURCE, the replication channel WILL NOT fail back also if a node with higher weight value will become available. The replication SOURCE change only if there is a failure in the communication between SOURCE-REPLICA pair.

This is what I have now:

async failover 8022 pxc async failover4

 

 

Is this enough?

Well let us say that we are very close, but no this is not enough.

Why?

Because this cover ONLY a side of the picture. Which is the REPLICA node able to change the SOURCE. But if we are talking of a cluster like Group Replication, we need to have a mechanism that will allow the CLUSTER (and not only the single node) to fail-over.

What does it mean?

Here I mean that IF the Primary fails (remember only a Primary can become a REPLICA because only a Primary can write), another Primary will be elected by the cluster, and I would expect that if my previous Primary was replicating from a SOURCE, then the new one will do the same starting from the last valid applied position.

I am sure Oracle had already identified this as a possible issue, given to me this new feature sounds something done to make the architecture based on MySQL more resilient. As such the above looks like the most logic step forward when thinking to Tightly Coupled cluster like Group Replication.

Summary of commands

Set the source:

change master to master_user='replica_dc', master_password=<secret>, master_host='192.168.4.22', master_auto_position=1, source_connection_auto_failover=0,   master_retry_count=6, master_connect_retry=10 for channel "dc1_to_dc2";

Set the list:

SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.22', 3306, null, 100);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.23', 3306, null, 80);
SELECT asynchronous_connection_failover_add_source('dc1_to_dc2', '192.168.4.233', 3306, null, 50);

Delete an entry from the list

SELECT asynchronous_connection_failover_delete_source('dc1_to_dc2', '192.168.4.22', 3306, '');

Check if a replication channel is using automatic failover:

SELECT CHANNEL_NAME, SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration where CHANNEL_NAME = 'dc1_to_dc2';

To change the SOURCE setting:

stop slave for channel 'dc1_to_dc2';
CHANGE MASTER TO source_connection_auto_failover=1 for channel "dc1_to_dc2";
start slave for channel 'dc1_to_dc2';

Conclusions

This is a very nice feature that will significantly increase the architecture resilience when using asynchronous replication in the architecture.

Still I think we need to wait to have it fully cover what is needed. This is a great first step but not enough, we still must manage the replication if a node in the REPLICA cluster fails.

I really hope we will see the evolution of that soon.

In the meantime, GREAT WORK MySQL/Oracle team, really!!

References

https://dev.mysql.com/doc/refman/8.0/en/replication-functions-source-list.html

https://www.slideshare.net/marcotusa/best-practicehigh-availabilitysolutiongeodistributedfinal

https://github.com/y-trudeau/Mysql-tools/tree/master/PXC

https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html

More Articles ...

  1. Using SKIP LOCK in MySQL For Queue Processing
  2. Deadlocks are our Friends
  3. Achieving Consistent Read and High Availability with Percona XtraDB Cluster 8.0 (Part 2)
  4. Achieving Consistent Read and High Availability with Percona XtraDB Cluster 8.0 (Part 1)
  5. Sysbench and the Random Distribution effect
  6. #StopTRUMP
  7. Dirty reads in High Availability solution
  8. My take on: Percona Live Europe and ProxySQL Technology Day
  9. Another time, another place… about next conferences
  10. A small thing that can save a lot of mess, meet: SET PERSIST
  11. My first impression on Mariadb 10.4.x with Galera4
  12. Reasoning around the recent conferences in 2019
  13. ProxySQL Native Support for Percona XtraDB Cluster (PXC)
  14. How Not to do MySQL High Availability: Geographic Node Distribution with Galera-Based Replication Misuse
  15. MySQL High Availability On-Premises: A Geographically Distributed Scenario
  16. MySQL 8: Load Fine Tuning With Resource Groups
  17. PXC loves firewalls (and System Admins loves iptables)
  18. No orange pants this year
  19. Leveraging ProxySQL with AWS Aurora to Improve Performance
  20. How to Implement ProxySQL with AWS Aurora
  21. ProxySQL server version impersonation
  22. ProxySQL Firewalling
  23. ProxySQL PXC Single Writer Mode and auto failover, re-bootstrap
  24. How ProxySQL deal with schema (and schemaname)
  25. How ProxySQL deal with schema (and schemaname) Long story
  26. Sweet and sour can become bitter
  27. Group-Replication, sweet & sour
  28. ProxySQL and Mirroring what about it?
  29. InnoDB Page Merging and Page Splitting
  30. Setup ProxySQL as High Available (and not a SPOF)
  31. ProxySQL – Percona Cluster (Galera) integration
  32. ProxySQL and MHA integration

Latest conferences

We have 3160 guests and no members online

login

Remember Me