My idea is a bit different and much crazier. Let's say I want to move labswiki from s6 to s5:

• Create labswiki database in s5 manually.
• Create all tables empty manually (maybe that can be later also automated but wikiuser doesn't have CREATE grant)
• Turn on the config in mw which would immediately start duplicating all write queries between two clusters.
  • With exception of the ones involving auto_increment in which it has to first get the id from s6 db and insert with that id set (so it's duplication but it involves extra field set). I assume anything relying on RAND() or date functions would have to be treated the same. See it more like MW-level SBR.
  • Some write queries might fail (for example when it tries to update a page id row while that page row doesn't exist yet) but that's fine. (you'll see why).
• Run a mw maint script that would go through every row in each table, like db-compare and compares them. If a row is different, the source of truth is the s6 and we will update the s5 row accordingly. I assume we might want to lock the rows on both clusters before doing any writes.
• Run it multiple times. Just to be sure all holes are covered.
• Flip the switch in mw which would still write to both clusters but now the source of truth is s5, not s6. Also, reads switch to happen from s5 now instead of s6.
• Once we are comfortable things are fine. Remove the "s6" as the replica/secondary cluster of this wiki in mw config. So mediawiki stops duplicating writes.
• Once we are really comfortable. Drop the database from s6.

With this, you don't need any RO time at all. You don't need to use any backup/recovery or mysqldump at all or go through transactions. MediaWiki will just compare rows live via a maint script and slowly builds the wiki's database. This is specially for small wikis would be quite nice since the whole process won't take longer than an hour and we would easily move one wiki from one cluster to another.

Would anything need to be done on parsercache or external store level? I assume no?
We should definitely put lots of safety checks in place to ensure consistency is preserved and that not a single row is lost.

I am a bit worried with a possible race condition that could happen if we don't use RO.
When we switch the config in MW and deploy, it won't arrive at the same time everywhere, meaning some hosts may start consider source of truth s5 while others already have s6, what would happen in that situation?

I don't think having RO is bad per se, if it is just a few minutes to ensure everything is exactly the same (especially with large tables).

In T410145#11383915, @Marostegui wrote:

Would anything need to be done on parsercache or external store level? I assume no?

Nope, they are only identified via dbname. Otherwise, when we moved wikidata out of s5, things would have had broken royally.

We should definitely put lots of safety checks in place to ensure consistency is preserved and that not a single row is lost.

I wonder if we can run db-compare too between two sections. One fun complexity is rows that are deleted in the source of truth and must be deleted on secondary but haven't been deleted.

I am a bit worried with a possible race condition that could happen if we don't use RO.
When we switch the config in MW and deploy, it won't arrive at the same time everywhere, meaning some hosts may start consider source of truth s5 while others already have s6, what would happen in that situation?

That should be fine and data should stay consistent. At least in paper but if writes are too many, it can cause issues e.g. collision of auto_increment id

I don't think having RO is bad per se, if it is just a few minutes to ensure everything is exactly the same (especially with large tables).

Given above, I think RO won't be needed for small wikis, but we definitely should do RO for frwiki or somewhat larger wikis during the mw deploy.

Another point to consider: https://meta.wikimedia.org/wiki/Wikimedia_Foundation_Community_Affairs_Committee/Sister_Projects_Task_Force/Results_of_the_consultation_about_Wikispore_and_Wikinews#Wikinews it's very likely most if not all wikinews projects will be closed and archived which means number of closed wikis will reach ~170. Having a small archive cluster that we could move all of these wikis to would help s3 a lot.

In T410145#11412961, @Ladsgroup wrote:

Another point to consider: https://meta.wikimedia.org/wiki/Wikimedia_Foundation_Community_Affairs_Committee/Sister_Projects_Task_Force/Results_of_the_consultation_about_Wikispore_and_Wikinews#Wikinews it's very likely most if not all wikinews projects will be closed and archived which means number of closed wikis will reach ~170. Having a small archive cluster that we could move all of these wikis to would help s3 a lot.

That's great - however, remember that for closed projects (with no writes) this whole process is way way easier.

In T410145#11412966, @Marostegui wrote:

In T410145#11412961, @Ladsgroup wrote:

Another point to consider: https://meta.wikimedia.org/wiki/Wikimedia_Foundation_Community_Affairs_Committee/Sister_Projects_Task_Force/Results_of_the_consultation_about_Wikispore_and_Wikinews#Wikinews it's very likely most if not all wikinews projects will be closed and archived which means number of closed wikis will reach ~170. Having a small archive cluster that we could move all of these wikis to would help s3 a lot.

That's great - however, remember that for closed projects (with no writes) this whole process is way way easier.

There will be some writes, stewards can still edit closed projects plus there are db writes for linter and some other stuff happening but obviously, they are quite low in frequency and no matter how we are planning to move them (mw or mysql), it's going to be easy.