1

Current Configuration

I currently have 3 servers:

  • server australia with the database canberra
  • servers belgium and germany are a galera cluster with the database bruxelles.

Target Configuration

My final goal is to merge both databases into a single galera cluster:

  • servers australia, belgium and germany will be a galera cluster with the databases canberra and bruxelles.

Constraints and leeways

australia, belgium and germany are public production servers. They all handle read and write queries from various clients 24/7 (SELECT, INSERT, UPDATE, DELETE), so I cannot allow neither downtime nor read-only for any of them.

canberra and bruxelles schemas are obviously distinct, so there is no risk of collision when merging.

canberra and bruxelles schemas are stable (no ALTER), so I can copy the schemas from one server to another at any time, without any need to keep the schemas in sync.

The servers are adressed by clients through DNS aliases :

  • australia is behind pacific.example.com
  • belgium and germany are behind europe.example.com (round-robin handled by HAProxy).

It is easy to add or change DNS aliases (eg. edit europe.example.com to include australia in the round-robin, or to exclude germany).

I am not sure that the galera information is relevant for this particular issue, so any answer working for 2 servers (let's say australia and belgium) should be easily translatable to my issue.

Naive approach

My plan was the following:

  1. Initiate a replication where australia is the master and belgium is the slave.
  2. Have pacific.example.com target germany instead of australia (no more write on the master, all writes on the slave)
  3. Shut down australia
  4. Overwrite australia as a new galera cluster member behind europe.example.com.
  5. Reboot belgium removing slave config
  6. Have pacific.example.com target the galera cluster.

However, I found myself unable to initiate the replication. Trying to make belgium a slave of australia results in the destrution of bruxelles database.

Question

How would you do it?

Improve this question
1
  • Before embarking on this, be sure you can handle the long lag time at COMMIT -- something like 200ms. Also some wsrep tuning may be necessary to even let it run with nodes that far apart. I suggest you set up a small test platform with 3 nodes and experiment with it -- for tuning, timing, transition, etc.
    – Rick James
    Oct 21, 2020 at 15:37

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.