[ClusterLabs] Retrofit MySQL with pacemaker?

[Ken Gaillot]

On Sun, 2020-05-03 at 10:47 +0200, Niels Kobschätzki wrote:
> Hi,
> 
> I have here several master/slave-MySQL-instanced which I want to
> retrofit with corosync/Pacemaker while they are in production and I
> want to do it with minimal downtime. 
> Is that even possible?
> 
> I would set up a 2-node corosync-cluster on both. Then I have the
> problem that there is already a virtual IP and I want to continue
> using it. Do I deco figure the VIP on the current active master, then
> configure the resource? Or is there a better way. How would I go in
> configuring the MySQL-resource?
> 
> Did anyone here did something like that before or do you always start
> with a new setup (which begs the question how do you get the data
> from the old to the new - dump it out or can you attach the new one
> to the old one somehow and then switch at a certain point?)?
> 
> Best,
> 
> Niels

It should be possible with no downtime other than a brief read-only
period while the master role is switched over.

It would even be possible on the same hardware, except that there's
always some risk of misconfiguration causing unexpected fencing when
you're setting up a cluster, and testing fencing would be delicate (or
impossible if your secondary is used read-only for production
purposes).

I'd recommend using new hosts at least for the first transition. The
old hardware could then be used for the next cluster, and so on,
assuming the requirements are roughly the same.

I'd go about it like this:

1. On the new hosts, install, configure, and test corosync and
pacemaker, with fencing. Configure dummy mysql and IP resources and
test failure scenarios until you're comfortable with it. Remove the
dummy mysql resource when you're done.

2. Stop your secondary and transfer its data to both cluster nodes.
Start mysql on both nodes (manually, without a cluster resource and
without replication) and make sure you can read the data. Configure one
to replicate from the live primary, and make sure that's working.
Configure the other to replicate from that one, and make sure that's
working. Stop mysql on both nodes.

3. Configure a mysql clone resource (not promotable yet, so they both
start as secondaries). Ensure that replication still works correctly
and you can read from the database using your dummy floating IP.

4. When you can schedule maintenance time:

- set the primary read-only, and wait for the secondaries to catch up
on replication

- configure mysql on your "main" secondary as the new primary (i.e.
deconfigure replication and set it read-write)

- set promotable=true on the clone; the cluster should take over
deciding which host is primary

- add a colocation constraint for the IP with the mysql master role

- drop the IP on the old primary, and edit your cluster IP resource to
have the correct IP address; the cluster should drop the dummy IP and
add the live one on the new primary

- everything went perfectly smoothly and no troubleshooting is needed
;)
-- 
Ken Gaillot <kgaillot at redhat.com>