Return to Question

We are migrating to a model where every "customer" lives in its own dedicated schema (database per tenant). This will allow us to later move some accounts to different DB clusters or upgrade specific accounts to newer schemas, for example. However from the get-go we need to automate how databases are managed in bulk or the whole effort will end up in smoke.

Is there a solution (for linux) that is recommended when dealing with many databases? We are familiar with and use Chef for configuration of systems - anyone managing databases with it, for example?

Additional info: the split is dictated by regulatory reasons and to scale out.

The number of schemas would be about 1000 (today), hosted in about 10 MySQL instances, so about 100 schemas per DB instance. New accounts would grow the system at a rate of 100 schemas-per-DB-instance.

My goal is to apply DevOps practices to database change management. So the schema lives in a git repo. Next, we need to be able to selectively or in bulk upgrade specific accounts to bring them to certain "schema version" that's in git.

Problems are:

1. Figuring out what changes to apply to what databases, sort of like a "make tool", except that make works with files, but I need to work with databases.
2. How to know what database is NOW vs TARGET. I'm assuming a meta table is needed per schema.
3. How to manage interrupted upgrades - anything's possible so I need to ensure the change gets applied until completion, with possible restarts.

I was thinking of creating a file per one of two database objects we have: a table and a stored procedure. Fortunately these are the only objects I need to care about. Then put all that into Git. Each object in the database has a corresponding version row in its meta table. So if I change a versioned file, I can run a "diff" to tell which databases are outdated.

Upgrading the schema means running a command to upgrade the database objects to the state in the git tag. For stored procedures that's easy: DROP/CREATE. For table schema changes, patching is more difficult because the syntax for changing/adding keys or column types is rich. So I opt to write the "change steps" myself, per object. So my instructions would consist of SQL statement(s) to bring v1 to v2 (consecutive versions) per object. The tool would apply the steps per object until database as a whole is upgraded.

Is this something I'd need to write myself from scratch or anything already on the market that you recommend?

We are migrating to a model where every "customer" lives in its own dedicated schema (database per tenant). This will allow us to later move some accounts to different DB clusters or upgrade specific accounts to newer schemas, for example. However from the get-go we need to automate how databases are managed in bulk or the whole effort will end up in smoke.

Additional info: the split is dictated by regulatory reasons and to scale out.

The number of schemas would be about 1000 (today), hosted in ~10 MySQL instances, so roughly 100 schemas per DB instance. New accounts would grow the system at a rate of 100 schemas-per-DB-instance.

My goal is to apply DevOps practices to database change management. So the schema lives in a git repo. Next, we need to be able to selectively or in bulk upgrade specific accounts to bring them to certain "schema version" that's in git.

Problem is figuring out what changes to apply to what databases, sort of like a diff and make CLI tools but for the database schemas.

I was thinking of creating a file per one of two database objects: a table and a stored procedure. Fortunately these are the only objects I need to track. Each object in the database has a corresponding file in the git. So if I change a definition in the file, I can run a "db-diff" (a tool I'd create) to tell which objects in what databases are outdated.

To bring the DB schema in sync with the git version of the same object would work something like this:

• for stored procedures that's easy: DROP/CREATE; I'd utilize the SP's routine_comment field in the information_schema.routines to put there its current git tag. If the version there is outdated, replace it with the SP in the file in git.

• for table objects, it is not clear to me how to compare what's in git vs in DB. One major annoyance of MySQL is that the show create.. result doesn't show exactly the definition that you asked for in ALTER/CREATE. For example, if you have a bigint column, you end up with the silly bigint(20). To a diff tool that's a different schema than what we have in the git.

Is this something I'd need to write myself from scratch or anything already on the market that you recommend?

We are migrating to a model where every "customer" lives in its own dedicated schema (database per tenant). This will allow us to later move some accounts to different DB clusters or upgrade specific accounts to newer schemas, for example. However from the get-go we need to automate how databases are managed in bulk or the whole effort will end up in smoke.

Is there a solution (for linux) that is recommended when dealing with many databases? We are familiar with and use Chef for configuration of systems - anyone managing databases with it, for example?

We are migrating to a model where every "customer" lives in its own dedicated schema (database per tenant). This will allow us to later move some accounts to different DB clusters or upgrade specific accounts to newer schemas, for example. However from the get-go we need to automate how databases are managed in bulk or the whole effort will end up in smoke.

Is there a solution (for linux) that is recommended when dealing with many databases? We are familiar with and use Chef for configuration of systems - anyone managing databases with it, for example?

Additional info: the split is dictated by regulatory reasons and to scale out.

The number of schemas would be about 1000 (today), hosted in about 10 MySQL instances, so about 100 schemas per DB instance. New accounts would grow the system at a rate of 100 schemas-per-DB-instance.

My goal is to apply DevOps practices to database change management. So the schema lives in a git repo. Next, we need to be able to selectively or in bulk upgrade specific accounts to bring them to certain "schema version" that's in git.

Problems are:

1. Figuring out what changes to apply to what databases, sort of like a "make tool", except that make works with files, but I need to work with databases.
2. How to know what database is NOW vs TARGET. I'm assuming a meta table is needed per schema.
3. How to manage interrupted upgrades - anything's possible so I need to ensure the change gets applied until completion, with possible restarts.

I was thinking of creating a file per one of two database objects we have: a table and a stored procedure. Fortunately these are the only objects I need to care about. Then put all that into Git. Each object in the database has a corresponding version row in its meta table. So if I change a versioned file, I can run a "diff" to tell which databases are outdated.

Upgrading the schema means running a command to upgrade the database objects to the state in the git tag. For stored procedures that's easy: DROP/CREATE. For table schema changes, patching is more difficult because the syntax for changing/adding keys or column types is rich. So I opt to write the "change steps" myself, per object. So my instructions would consist of SQL statement(s) to bring v1 to v2 (consecutive versions) per object. The tool would apply the steps per object until database as a whole is upgraded.

Is this something I'd need to write myself from scratch or anything already on the market that you recommend?