Zero downtime upgrade on large database

[Stefan Maurer]

Hi everybody!

We are a recently founded software company and would finally like to implement CI/CD in our development process (please don't ask what we did the past two years :D) as well as migrating our databases to a MySQL Group Replication. Our largest database (for a CoVid-19 laboratory) is approx. 150GB in size (text only, no documents or images, but lot of "moving" data), overall approx. 200 tables, with a handful of partitioned tables. For further examples I will use this database size as many things may sound fine on smaller DBs but fail on a larger scale.


The application using "my" database should be migrated to Kubernetes (but the problem also arises in a "standard" environment with everything running on one single server). "Somehow" (IMO it's irrelevant how the migration is triggered as long as it is triggered) the migration is started, but how could I ensure a consistent database is always available?

I know that this is a "stupid" question as it's not narrowed down enough but I'm still in the process of writing a concept and understanding what I want and how to achieve it, so pointing me into any direction would be absolutely sufficient.

My first idea would be to separate one of the server (let's call it "take it offline") in the group to upgrade it, then add it back to the group and let MySQL do the magic to synchronize data inserted on the "online" servers with the "offline" server and vice-versa. I'm currently setting up a test environment to play around a little, so if this approach is futile, I'd appreciate the heads up ;)

Does anyone of you have experience or knowledge on how to update big databases without interrupting the servers and still be able to cover any errors that unfortunately made it through testing and are only showing up on the productive environment without having a person manually watch the update anyway?

I stumbled upon blue/green deployment which would sound interesting to me - but how could the data be synchronized between those two environments if blue receives updates while green is updated and the update deletes e.g. a column? How could blue know that this specific column can't be synchronized?


As mentioned before I'd be very glad just to get a tip with which method I could realize this (if any ;))

Thank you all very much in advance - have a nice day