Cascaded replication, GTID and moving database

MySQL & MariaDB
1

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

image
M are for master and S for slave obviously.

The plan I worked is

  1. do a backup (using mysql-shell) on any S yellow
  2. copy backup to all green nodes.
  3. load the data (I use mysql-shell again with command util.loadDump('/dump/db1', {'updateGtidSet': 'replace', 'skipbinlog': true})
  4. 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

  1. lock the db on M yellow
  2. change the configuration to use M green as database server
  3. 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 reset GTID_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 myself GTID_PURGED.

Do you have any clue, GTID is supposed to simplify such case but I don’t find a solution here.

Best.

2

For this specific setup maybe it’s better to avoid GTID for the yellow-to-green replication stream. I suggest just using binlog file and position to avoid dealing with the GTID problems.

1 Like
3

Hi @Ivan_Groenewold

can you detail a bit ?

  • Once gtid is activated (I use it between source and replicas of same color) is it possible to use old-style replica (using binlog file and position) ?
  • how can I find the correct positioning to specify on the green master when I what to make it slave of the yellow master, knowing I plan to do the database dump in the yellow slave ?
1 Like
4

Yes, you can still use old style replication between yellow and green.
When you dump a new database, you can use mysqldump --single-transaction --master-data options.

1 Like
5

thanks for your answer, I have questions though

  • Is there any way to use mysqlsh (I did not find the option) because I would like to have multhreaded dump due to the size of the Db (60GB). it proved to be really fast for backup and restore.
  • I dumped the data using mysqldump as you suggested and as I use to do before switching to GTID, I always got an error when starting the replica Last_IO_Errno: 13114, Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event; the first event 'mysql-bin.000004' at 3158712, the last event read from './mysql-bin.000004' at 123, the last byte read from './mysql-bin.000004' at 3158731.'

I’m a bit stuck, I don’t know if activating GTID was a proper choice.

1 Like
6

try mysqldump --single-transaction --all-databases --master-data --gtid_mode=off

Then checkout this document on how to use mysqlsh to load your data: MySQL :: MySQL Shell 8.0 :: 11.4 Parallel Table Import Utility

1 Like