0

There are 2 MySQL instances: MA and MB. They are completely separate from each other (no replication between them). Each instance contains multiple databases:

  • MA serves db1, db2
  • MB serves db3, db4

I want to migrate db1 to MB without any downtime, so the result will be:

  • MA serves db2
  • MB serves db1, db3, db4

I can instruct the application layer to pause write transactions to MA for a while ( < 10 seconds). Assume that it is possible to have replication lag (if set up) between MA and MB to be < 10 seconds.

Of course the most obvious way to do it is:

  1. pause writes to db1 ("downtime" starts)
  2. mysqldump db1 and apply to MB
  3. move app layer settings to use MB for db1
  4. unpause writes to db1 ("downtime" ends)

However, the above method is not acceptable when the mysqldump/apply takes a long time (> 10 seconds) for bigger databases. I have seen the method in https://exotel.com/blog/engineering/mysql-database-migration-with-zero-downtime/ but I think it only works when moving all databases in an instance, but for my case there is db2 in MA that still needs to be online at all times.

I have tried to fiddle around with database-specific replication, but I don't think it works well (I can guess that it might work with MySQL 8.0 filtered replication feature, but we are at 5.7 and not looking forward to an update).

Do you know of any way to do this?

0

This will require some downtime. Think about it:

  1. Application writes to Node A
  2. Changes to Node A are replicated to Node B in an eventually consistent manner
  3. Application suddenly switches writing to Node B -> replication from Node A and updates from application now happening simultaneously -> pretty much guarantees conflicts

You can't guarantee that the replication from A to B will be instant. You need downtime to allow the replication from A to B to complete before you start writing to B. How much downtime will depend on how write-intensive your application is and how fast the replication is.

  • As I said, I can pause the app layer for a short while (you can say that "it is allowed to have downtime of <= 10 seconds"). I'm stuck at the step "Changes to Node A are replicated to Node B in an eventually consistent manner" because I only want to replicate specific database (in example above, only replicate db1->MB, NOT db2->MB). Also, the question is more on how to have as little downtime as possible. The linked article from exotel shows that it is possible to do it with almost no downtime, but I can't make it work in my scenario. – Leonth Feb 7 '18 at 12:31
0

It is complex, but you can migrate a table in realtime if you are willing to build some infrastructure into your app.

One thread is copying the table over in small-ish chunks. The writing code is either writing only to the old copy or to both copies, depending on whether it is beyond the highwater mark of the copy. Certain interlocks are in place to prevent writing while a chunk is being copied.

The chunk size dictates the "downtime" in the sense that there is a slight delay there. Actually, most of the time, there is no delay.

When finished, the clients need to switch to the new copy. After that, the code should be cleaned up to stop talking to the old server.

This design works for arbitrarily large tables. But it may have issues with FOREIGN KEYs.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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