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:
- pause writes to db1 ("downtime" starts)
- mysqldump db1 and apply to MB
- move app layer settings to use MB for db1
- 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?
Best Answer
This is now possible with tools like Ghostferry: https://github.com/Shopify/ghostferry