Hello,
we have two sets of mysql replication environment, one which hosts all the DBs currently and the other is brand new and is supposed to host some of the Db in the future.
I’m working on the procedure to move DBs from yellow to green cluster without doing interruption (or 1 minute at most).
All nodes are running mysql version 5.7.33
and have GTIDs enabled.
a rough idea of the setup is
M are for master and S for slave obviously.
The plan I worked is
- do a backup (using
mysql-shell
) on any S yellow - copy backup to all green nodes.
- load the data (I use
mysql-shell
again with commandutil.loadDump('/dump/db1', {'updateGtidSet': 'replace', 'skipbinlog': true}
) - I configure M green as slave (using
REPLICATION FILTER DO_DB_REPLICATE=(db1)
) and launch the replication.
so the day we need to do the switch-over application side, we
- lock the db on M yellow
- change the configuration to use M green as database server
- delete the replication from Green M to Yellow M
That works fine, all nodes in the chain have the same value for GTID_EXECUTED, and green nodes have GTID_PURGED
defined to continue the replication after the restoration until I stop it when the application did the switchover.
Now the problem comes when I want to move another Db from yellow M to green M. I do the same process but
-
util.loadDump('/dump/db2', {'updateGtidSet': 'replace', 'skipbinlog': true})
shouts that it cannot resetGTID_PURGED
because “GTID_EXECUTED is not empty
” - If I do
util.loadDump('/dump/db2')
I don’t have the clue to set the GTID (well there is the info in the backup file@.json
) but anyway mysql prevents me to modify myselfGTID_PURGED
.
Do you have any clue, GTID
is supposed to simplify such case but I don’t find a solution here.
Best.