Need little help with a production issue.
Case: I had to migrate two schema from MasterA to MasterC. It would have been a simple exercise but because it was decided to migrate schema1 first and later schema2, it got messed up. So I restored schema1 on MasterC and set up a replication channel for the same. Later, before actual migration, it was decided to migrate schema2 as well. So I took another mysqldump for schema2 and mistakenly created another channel on MasterC. So in essence there were two channels from MasterC to MasterA and I received the following error.
Replicate_Wild_Do_Table: schema1.%,schema2.% Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'MasterA.026866' at 318443201, the last event read from './MasterA.026866' at 318580655, the last byte read from './MasterA.026866' at 318580655.' Last_SQL_Errno: 1756
The error is expected, which I realised later, because two different channels from same machine cannot to connect to single master.
I cannot redo the entire exercise as I will have to take another dump for both the schema together and setup a single channel, but because schema1 is 1TB in size it might take two more days to dump and restore which is not possible because of time constraint.
Let me explain my question in more detail.
Master A Slave A Master C schema1 (1TB) Backup Server New empty master schema2 (100KB)
So, my question is when I started replication for schema1, lets say I used positions (log file = MasterA.000010 pos=1234567), and when I will try to setup replication for schema2 how should I proceed and what positions should I use.
One thing is clear that I will have to use single channel, but as replication channel for schema1 is already running, how do I proceed for schema2, what procedure should I follow?
Also, because MasterA is busy, the practice I follow to dump the schema is to use a backup slave instead of running the dump on master and later I move the replication from backup to master. So when I start replication, the following is the replication flow
MasterA ---> Backup SlaveA ---> MasterC
After MasterC catches up, I move the replication from SlaveA to MasterA, so the replication would look like this
MasterA ---> MasterC Note - this is a GTID based replication with Auto_Position = 0.