0

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.
Improve this question
1
  • You cannot use replication to initialize a new server. You must copy the data over at least to get it started.
    – Rick James
    Commented Jul 19, 2023 at 16:14

2 Answers 2

Reset to default
1

Since schema2 is small (only 100kb), you can consider , after MasterA ---> MasterC is built, restore the data of schema2 in MasterC again

Improve this answer
2
  • so for schema1, after I move replication, you want me to take a dump from MasterA for schema2 without master-data option (because that might cause locking on MasterA which I want to avoid), and restore it on MasterC? Is that what you mean? but let's say when I take a dump from MasterA at 12:00PM, then to take a dump and transfer it to MasterC, it might take a min, to restore and set up replication filter another minute. What about the changes for those 2 minutes?
    – Avinash Pawar
    Commented Jul 20, 2023 at 5:15
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jul 20, 2023 at 6:08
0

I had to stop the replication from A to C, drop the restored schema and create dump of schema1 and schema 2 together and start the process again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.