2 Percona XtraDB Cluster 5.7 in different datacenters

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hello there,

We have 2 data centers: our Prod data center and the QA data canter. I will refer as “dc” for data center.

On our Prod dc, we have 4 nodes of v5.7 Percona XtraDB cluster. We use the Percona ProxySQL to talk to Percona XtraDB 1-2-3 nodes. The 4th node is use to get the transaction of the node 1-2-3 and it acts as a “master” for the other data center.

Then, Percona XtraDB 1-2-3-4, where node 4 has the binary logs and everything to be a master of any slave.

On the QA dc, we have a Percona Standalone MySQL 5 which is a slave of the MySQL 4.

Then, if our Prod dc goes black, we still have our Percona MySQL 5 with the latest data. We have stand by VMs “back-ends” at QA data center, that way we can be up in no time.

The challenge comes, that a single MySQL won’t be enough to handle our traffic, then I came with this solution:

I setup a new Percona XtraDB on QA data center of 3 nodes, I called them: MySQL 6-7-8.

I made a backup of MySQL 5 using XtraBackup, copied into MySQL 6, then I bootstrap 6, setup MySQL 6 as a salve which master will be MySQL 5. I started MySQL 7 then MySQL 8.

Summary, on Prod data center, we have:
MySQL 1-2-3-4 XtraDB cluster.

On QA data center we have:
MySQL 5 Percona MySQL standalone, and acting as a slave, its master is MySQL 4 in the other data center.
MySQL 6-7-8 XtraDB cluster are synced, where MySQL 6 is the node I bootstrapped and also it is a slave of MySQL 5.

I can reboot MySQL 7 and MySQL 8, and they will synced without issue.

The problem?
If I reboot MySQL 6, when it is offline, MySQL 7-8 will create some transactions.
When MySQL 6 comes back online, it will get the transactions from its master MySQL 5, but MySQL 7-8 create another transactions, then MySQL 6 will be reject from the XtraDB MySQL 7-8 cluster.

Can I do something to be able to restart MySQL 6 in a way it gets the transactions from MYSQL 5, and it can only join XtraDB cluster node 7-8 ?

Thanks in advance.

An diagram?

1 Like
2

First off, “QA” is the wrong term here. You should be calling this “DR”, not “QA”. QA stands for ‘quality assurance’ which is normally not a production traffic system. In most cases, QA servers are “pre-production” staging areas used for testing before implementation into production. What you have described above is clearly your DR, or “disaster recovery” solution.

You never mentioned it, but you need to implement GTID on every MySQL server. This is how you can solve the sync issue. After you enable GTID everywhere, before you stop MySQL6, you must move replication to mysql7 or mysql8. This ensures continuity of transactions from primary DC to DR DC.

One issue is why are mysql7/8 creating transactions? If this is your DR, then all nodes in DR should be in read-only mode unless there is a failure in the primary DC and the DR has taken over.

Another solution is to enable skip_slave_start=1 on mysql6 which would prevent replication from starting automatically on reboot. This makes mysql6 sync with 7-8 first, then you start replication manually. Or you could easily script this to make sure PXC is sync’d before starting replication.

1 Like
3

Hi Mattew,

Thanks a lot for the info. In fact the 2 sys-admins called it “DR” to what I call QA data center. What happened is we have a bunch of QA Virtual Machines “over 100” on the DR data center. I have worked on the company for 3 years, and we haven’t had a situation where we had to switch from Prod dc to DR dc, fail over, etc.

Then, for me: it’s QA because all the environments are label like QA1, QA2, QA3, etc…

Just the 2 sys-admins called it “DR” :slight_smile:

I like the approach of skip_slave_start, I will give it a try on MySQL 6.

You mentioned MySQL 7 and MySQL 8 are “on read-only” mode, can I force to those 2 nodes to be on “read-only”? Please let me know.

I leave the 8 servers up and running since yesterday, I just checked, they are all in synced. I thought for a sec, perhaps some “transactions, checks, etc” would go on MySQL 6-7-8 and that will “add” some transaction and MySQL 6 would get de-synchronized, but not, so far so good.

Cheers,

1 Like
4

No, I said they should be in read-only mode. All replicas should be in read-only mode. Yes, you can set this at startup in my.cnf

[mysqld]
read_only=1
1 Like
5

Hi, I will try out.

This works for me, perfectly. I can now reboot MySQL 6-7-8, rolling reboot, and each node will synced. After a few secs that MySQL 6-7-8 are synced, I will go to MySQL 6, and START SLAVE, and it will get the transactions for MySQL 5, which it was getting from the other datacenter MySQL 1-2-3-4, then MySQL 6-7-8 “XtraDB” are fine with it.

#disable the slave to start on boot-up
skip-slave-start

Thanks a lot !!!