EmergencyEMERGENCY? Get 24/7 Help Now!

Migrating several single standalone MySQL server to one Percona XtraDB Cluster… MariaDB to the rescue !

 | December 18, 2012 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

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 :

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.

PREVIOUS POST
NEXT POST
Frederic Descamps

Frédéric joined Percona in June 2011, he is an experienced Open Source consultant with expertise in infrastructure projects as well in development tracks and database administration. Frédéric is a believer of devops culture.

4 Comments

  • 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)

  • 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…

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.