AWS MySQL updates

Long story short, gonna have like 10-20 mysql db instances. There are all going to be exactly the same schema. After we release to prod, that should jump to 300-400 db instances. We use Red Gate to manage all our crap in MSSQL Server, but obviously they don't have that for mysql.

What are you guys using to push out schema changes to all your db instances at once?

Why do you have 400 interdependent instances?

I wouldn't say they are interdependent. And my terminology might be off because I am not normally the DB guy. But noticing what MySQL workbench has things named, I think I used the wrong words.

Say you have an AWS Aurora connection to an instance. that instance contains ~400 schemas that all have the same structure. Now the table structure for one changes, I am going to want to make that table structure change in all ~400 schemas that are same. I don't know the tools everyone is using to manage a change like that, of it they just script it out. Which is probably what I am gong to end up doing.

//edit - realized I never answered the question

Because each client has to have their data separated from each other, AKA cannot be residing in the same table.

45kicks said:

Because each client has to have their data separated from each other, AKA cannot be residing in the same table.

Click to expand...

Seems awfully silly way of doing things. That's why the world was graced with 'keys'. You have your regular ID column as a key as well as a 'company' column as a key. I can't figure a PCI requirement that would need separate tables for everything let alone the nightmare of 400 code/config changes for all the clients. It's ridiculous. I would strongly advocate pushing back on it.

modi123 said:

Seems awfully silly way of doing things. That's why the world was graced with 'keys'. You have your regular ID column as a key as well as a 'company' column as a key. I can't figure a PCI requirement that would need separate tables for everything let alone the nightmare of 400 code/config changes for all the clients. It's ridiculous. I would strongly advocate pushing back on it.

Click to expand...

Its not a PCI requirement that I am aware of, but a few companies work this way because of "security teams", lawyers, and fear of data lose and being sued. This is the third company I have dealt with that does things this way because of lawyers/data insurance. Honestly though, this is what is designed, and no way in hell they walk back on this right now, so this is what I get to work with.

There is one app for all clients that sits on top of the db, so the schemas need to all be the same, so their shouldn't be much in the way of differences between them all, its just deploying any changes that we need to manage. Like I said we have a ton of SQL devs using Red Gate and some custom app to keep everything in line, but I didn't know if there was something as comprehensive for mysql, or a better way then just making scripts for all this.

I am thinking i will just make a powershell script and just track all changes through git, but idk.

You can't have multiple DBs on the same instance for each of the clients? That's a lot of overhead to create a new instance to host a single DB just so client data is kept separate.

But the requirement is not unique. I've seen it in healthcare and financial sectors from many clients.