1

I'm trying implement MySQL upgrade approach that would require either no downtime at all or it would be counted in seconds. One of the problems is that these databases are RDS MySQL databases so there is no SUPER permissions that gives less control on what you can do with replicas. Anyway, this is how I see it can work.

Let's have simplified cluster schema: M-->R-->--DMS-->--RE Where:

  • M - main read/write database
  • R - read replica that used for external replication via AWS DMS
  • RE - external replication database where we put that data

We need to upgrade database M (M1). That would also require to update read replica R (R1), but we don't want to re-sync everything for DMS so we would like to continue that replication RE from the place where we were before upgrade, do the final schema should look like: M1-->R1-->--DMS-->--RE

So, to achieve that we are starting new read replica from M, promote it, create new DMS (DMS1) replication that would sync changes from M1, so the schema will look like:

M-->R-->--DMS-->--RE
|
DMS1-->M1

Then from M1 I can start R1 replica and then continue DMS replication from R1 already, so we will have something like:

M-->R
|
DMS1-->M1-->R1-->--DMS-->--RE

and after that we can get rid of M and R. Now the question. As in the new cluster I'll continue from R1 binlog, which has completely different positions rather than R. How can I get the right position in R1 that would represent the same position in R?

2
  • Do you have GTIDs turned on? Then they should take care of such.
    – Rick James
    Aug 24, 2022 at 19:14
  • No, we don't have that. binlog name and position .... Aug 25, 2022 at 5:36

1 Answer 1

0

The solution we implemented might be not ideal, but it worked for us. We created table with the single VARCHAR column in it and inserted GUID. Then we were downloading binlogs from both servers and looking for that GUID. By using the schema in the question the exact algorithm was:

  • Stop R replica
  • Ingest GUID
  • Download binlogs from R and RE and find positions there
  • Start R replica and stop it at the positions got from R binlog
  • Make sure that DMS delivered all the changes (can be done by querying the table with GUID)
  • Reconfigure DMS source to start looking from RE
  • Resume DMS from position saved from RE binlog

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.