MySQL ClusterSet : rename ClusterSet

by Mark Nielsen
Copyright May 2023

MySQL ClusterSet : rename ClusterSet

TODO: this document needs to be tested. The general steps are true. I need to add examples and test all the steps manually. I am going from memory.

Technically true

It is technically true you cannot rename a ClusterSet. However, with downtime it is possible, little downtime is preferable.

Assumptions

Old ClusterSet name in "oldc."
New ClusterSet name we want is "newc"
Primary cluster is called "primary" with hosts p1, p2, and p3.
Replica cluster is called "replica" with hosts r1, r2, r3.

Make sure p1 is the primary host in the primary cluster

Refer to https://www.percona.com/blog/mysql-group-replication-how-to-elect-the-new-primary-node/

Basically

See which server in primary

select member_host,member_state,member_role,member_version from performance_schema.replication_group_members;

If the primary is not "p1", Connect to the primary : mysqlsh -h <HOST> -u root
Switch primary to p1, using the uuid and: select group_replication_set_as_primary('<UUID>');

Make sure all applications are pointing to p1.

Make sure all applications are pointing to p1 and not any router. This may required minimal downtime are you may have to restart the applications.

Failover to replica, some downtime.

Destroy the clusters and clusterSets. Make sure all applications are pointing to p1 and not any router. PLEASE test these steps yourself on test machines and document the steps before doing it in production!

Stop Applications
Connect to mysqlsh on host p1 : login and do mysqlsh -u root
Failover to replica cluster

In mysqlsh:

  c =   dba.getCluster();
  cs = c.getCluserSet();
  cs.setPrimaryCluster("replica");

Start applications pointing to host r1
Verify applications are happy.
log into r1 and then connect to mysqlsh : mysqlsh -u root OR mysqlsh -h r1 -u root

Check the status on the ClusterSet. "replica" should be the active cluster.

  c =	dba.getCluster();
  cs = c.getCluserSet();
  cs.status(({extended:1});

Remove "primary cluster". The replica cluster is taking all the writes.

 c = dba.getCluster();
cs = c.getCluserSet();
cs.removeCluster("primary");

Remove primary Cluster and replica hosts r2 and r3, some downtime.

Shutdown r1 and remove ClusterSet settings.

Stop all applications.
service stop mysqld
change to the data directory
TODO: verify this : rm -f auto.cnf and the other one
start mysql on r1
Verify is is not part of a ClusterSet (this should error out): mysqlsh -u root
Start applications and verify they and write and use host r1.

Remake ClusterSet, no downtime.

Connect to host r1 with mysqlsh. Log into r1 and do mysqlsh -u root
Make cluster
Add r2 and r3 hosts
Make ClusterSet
Make Replica cluster
Connect to host p1. Log into p1 and do mysqlsh -u root
Add p2 and p3 to cluster.

Failover to "primary" cluster, little downtime

Stop applications
Failover ClusterSet to "primary" ClusterSet.
Verify ClusterSet.
Start applications to write to p1.
Verify applications are happy.
Verify p1 is taking connections and is writing.

Setup router and point applications to router.

It is beyond the scope of this article to deal with a router. You want to install a router and have the applications point to it. You want to "bootstrap" a router.

https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset-deploy.html https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-innodb-cluster.html
https://dbsguru.com/how-to-bootstrap-mysql-router-for-innodb-cluster/