Some weeks ago I had to migrate some independent MySQL servers (some standard MySQL masters, and some just standalone) to a Percona XtraDB Cluster of 3 nodes.
So the easiest way would be to configure each node to become also an asynchronous slave of one of the production servers.
Like illustrated here:
But in this case there was one major issue, the tables where MyISAM and this is not really recommended with Galera replication even if it’s now supported.
Preparing the slaves would then become a loop :
|
1 2 3 4 5 |
for each production server restoring the dump on the node that will be the dedicated slave convert the table in InnoDB configure and start the replication finally perform SST on the other nodes.... |
So in this case that should have be done 3 times… that could take some time ! And then, if the single production servers where more than 3 ?
So I decided to use a wonderful feature of MariaDB 10: Multi-source replication.
MariaDB’s multi-source-replication doesn’t have any conflict resolution. The assumption is that there are no conflicts in data between the different masters. Which is the case here, all standalone servers handle different schemas.
I configured the MariaDB server to be slave of all production servers, changed the table engine to InnoDB on it and chose one node of the PXC to be the async slave of that MariaDB server. It worked like a charm and the migration of production to Percona XtraDB Cluster became very easy.
Thank you MariaDB’s team for this feature.
When a sever (a node) is member of a Percona XtraDB Cluster is also standard slave (standard asynchronous replication of MySQL), it can spread in the cluster all statements received from the master if log_slave_updates is enabled.
Resources
RELATED POSTS
In your pseudo code, perhaps you meant that you would do SST / get galera running and then for each node, import a .sql script and start standard MySQL replication from the respective hosts (If I understand correctly, the SST would just throw away local data and get a copy of the donor)
How migrate stand alone server to percona cluster.share link
Kindly share link for MySQL stand alone to percona migration.is it possible configure replication
M,
The problem here is that the tables were also MyISAM, so the plan would have been to restore one every node (or all dumps on the same node) and let do the SST to the other nodes and start the replication.
In my example where I restore the dump on dedicated slave, so if we have node1, node2 and node3, that are respectively slaves of master1, master2 and master3, you first restore the dump1 (backup of master1) on node1, you alter the tables to InnoDB and then you start the other node one bye one with node1 as donor…. and then you do it again on node2, by stopping node1 and node3, … this is very long process…
If I understand correctly, the SST would just throw away local data and get a copy of the donor
More MyISAM on cluster will fuck your GTID =)
Possible to give a little direction on Async setup of that PCX slave node, Ive got to migrate 3 medium size DB from mariaDB to a Percona cluster in STRICT mode