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