Replicating a Galera replication group

Galera is very nice multi-master MySQL / MariaDB replication system, with the network communications based on rsync. If you install the "MySQL" package under Debian or Devuan, you will actually get MariaDB instead, with Galera as well. Galera won't do anything until / unless you configure it, but if you do (and it's really not difficult - certainly far easier to manage than "standard" MySQL Master-Slave replication), it gives you a write-anywhere / read-anywhere dynamically-sizeable database server group.

It is a lot more flexible and capable than MySQL's standard Master-Slave (or Master-Master) replication mechanism.

This article discusses how to take a working MariaDB Galera replication group and copy (duplicate) the data from it into another group, which can then be operated independently. My first use case for it was when a Live (customer-facing) application platform needed to be moved to a new hosting provider, and the procedure used was to build a complete new platform, test it thoroughly whilst the old platform was still serving customers, and then at switch-over time, duplicate the database contents and update DNS to switch customers to the new servers, as quickly as possible.

The principle of the database replication process is as follows:

1. Select one active server which it is acceptable to have non-responsive to queries for a short time (the remaining servers will remain read-write with no performance penalty)
   • the ideal choice for this machine is a quorum server, which never gets accessed for standard queries, and exists only to keep the group of servers up to an odd number, often in an odd number of data centres, in order to avoid split-brain
2. Configure the new servers into a replication group, start them, and make sure they perform as expected (eg: create a DB on one machine, check it exists on another, delete it from that machine, check it's gone from the first, etc)
3. Shut down MariaDB on all the new servers
4. Edit the Galera configuration file on one of the new machines (under Debian / Devuan, this is /etc/mysql/mariadb.conf.d/60-galera.cnf):
   • set wsrep_cluster_name to be the same as on the running cluster
   • add the IP address of the quorum server to the wsrep_cluster_address list
     • Note that, possibly surprisingly, you do not need to add the IP address of the new server to any machine in the existing replication group
   • add wsrep_sst_donor="<node name of the quorum server selected above>"
5. † Start MariaDB on that new server and check that it joins the existing replication group. Useful commands (to run on every machine in the group) are:
   • mysql -e "show status like 'wsrep_cluster_size'"
     • should be the number of machines in the original replication group, plus one
   • mysql -e "show status like 'wsrep_local_state_comment'"
     • should be "Synced" for all machines, once this is complete
   • if the new server does not enter the "Synced" state. simply restart MariaDB until it does
6. Stop MariaDB on the new server
7. Edit grastate.dat (which is probably in /var/lib/mysql) on the new server:
   • set uuid to all zeroes
   • set seqno to -1
   • set safe_to_bootstrap to 1
8. Edit the Galera configuration file on the new server:
   • remove the IP address of the quorum server
   • remove wsrep_sst_donor
   • reset the wsrep_cluster_name to that of the new replication group
     • this doesn't have to be different from the old group name, but if you plan to run the two side-by-side for any considerable time, it's advisable that they are different, to avoid any "accidents" or "surprises"
9. Bootstrap the new server (on a Devuan machine, this is done with /etc/init.d/mysql bootstrap)
   • check that it has started and is in a replication group of one:
     • mysql -e "show status like 'wsrep_cluster_size'"
10. † Start MariaDB on the other servers in the new replication group (one at a time or all at once; it makes little difference) and check that they all join:
    • mysql -e "show status like 'wsrep_cluster_size'"
      • should be the number of machines in the new replication group
    • mysql -e "show status like 'wsrep_local_state_comment'"
      • should be "Synced" for all machines, once this is complete
11. Check that the new servers have the same data as the original replication group:
    • same databases
    • same tables
    • same row counts in sample tables

You have now created a separate replication group, with identical data to the first one. Any further updates to each group will replicate within that group, but will not be replicated to the other group.

Note that at no time did you have to either stop or even reconfigure MariaDB / Galera on any of the already-running servers.

† Note that the time taken for replication to occur (in each of the two steps marked † above) depends on the amount of data which differs between the replication groups, therefore in order to minimise this time when you are switching over from one group to another, it is useful to have performed all the above steps sometime recently, so that the amount of data which needs updating at the critical time is minimised.

• For example, if you plan to switch between an existing live DB group to a new one at 20:00 some day, then you should perform the above steps at maybe 18:00 (which may take multiple minutes to complete) and then when you repeat the process at 20:00 it will only take multiple seconds (thanks to Galera replication taking place via rsync).

Go up
Return to main index.