Re: How do manage running queries across multiple DB servers?

On 11/2/18 5:40 PM, Robert Freiberger wrote:
> Hello,
> 
> Currently, at my workplace, we have multiple MySQL instances running 
> where they have databases for certain functions. During a deploy, we 
> need to manually SSH into the server and run these queries (stored as 
> SQL files, about 10~25 lines long queries that make table/field changes) 
> manually using the source command.
> 
> The entire process is very error-prone and time-consuming, to say the 
> least. While we have some tools written in house, I was exploring other 
> options of maybe using Puppet to store the queries in a new "deploy 
> module" and have these deployed but I like to hear from the team on 
> suggested methods.
> 
> I'm new to the world of database administration and any comments are 
> greatly welcomed.

I'm guessing the DB don't contain the same data but at least they share 
the schema and what you're trying to do is updating the schema, possibly 
moving some data around and updating stored procedures.

Don Marti gave you a very good advice.

One way to achieve this is storing in a table in the DB the schema 
version and update the schema version in the same transaction that 
change the schema.

To be extra sure you'd put the logic that check the version inside the 
transaction or think about some "locking" system that eg. avoid running 
2 update script at the same time.
You'd consider some kind of locking anyway if you're changing the schema 
because the program using the data and the data should be at the same 
version if you want to keep things easy.
Otherwise if you need some kind of HA... you should give a look at 
Refactoring Databases (Ambler, Sadalage), or maybe there is something 
newer...

To be extra sure you're not going to mess up things not only you should 
compare the current schema version with the script update version but 
apply all the "steps" to reach that version.

There are further "tricks" to expand on this idea, but this should give 
you a clue.

e.g. if your DB is made of "components", eg. on some instances you've 
some tables and in some other you don't you could version every 
component so that it would be easier to run everything you need in the 
same transaction. Or you could write "fast path" script to move you from 
version 3 to version 5 when following all the steps would be too slow etc...

-- 
Ivan Sergio Borgonovo
https://www.webthatworks.it https://www.borgonovo.net