How to manage schema changes on many identical schema-based databases with mysql?

Question

I'm developping a web platform to manage student registrations in schools of my region. For that I have 17 databases running on MySQL (5.7.19) where there is one which is the main database and the 16 others represent schools. Schools databases (must) have the exactly the same schema, each containing data corresponding to the associated school. I separated this way to avoid latency as each school can register many applications (16k on average), so the requests could get heavier over time.

Now I have a serious problem: when I change the schema of a school's database, I have to manually do it for those of other schools to keep the schema consistency because my sql requests are made independently of the school. For example, if i add a new field in table_b of database_school5, i have to manually do the same on table_b of all remaining databases.

What can I do to manage theses changes efficiently? Is there an automatic solution? Is there an adapted DBMS for this problem?

Somebody told me that PostgreSQL can achieve this easily with INHERITANCE, but this only concerns the tables, unless I've done some poor research.

I want every time I make a change to a database schema, whether it is adding a table, adding a field, removing a field, adding a constraint, etc., the changes are automatically transferred to the other databases.

Thanks in advance.

Maybe something like this? https://flywaydb.org/ There's a lot of alternatives to that tool. I find that managing schema changes in git is the best way to go, — Evert, Jan 26 '19 at 18:07
you said "I find that managing schema changes in git is the best way to go", how can i proceed to do it — Harrys Crosswell, Jan 29 '19 at 10:20
Harrys: You start by using a tool such as flywaydb.org or one of the many alternatives. I don't have personal experience with that tool, but I've used others. Those tools require you to write deltas or 'migrations'. Store those migrations in a git repository. Does that answer your question? — Evert, Jan 29 '19 at 16:43

score 0 · Answer 1 · answered Jan 26 '19 at 16:40

SELECT ... FROM information_schema.tables
    WHERE schema_name LIKE 'database_school%'
      AND table_name != 'the 17th table'
      AND schema_name != 'database_school5'  -- since they have already done it.

That will find the 16 names. What you put into ... is a CONCAT(...) to construct the ALTER TABLE ... statements.

Then you do one of these:

Plan A: Manually copy/paste those ALTERs into mysql commandline tool to perform them.

Plan B: Wrap all of it in a Stored Procedure that will loop through the results of the SELECT and prepare+execute each one.

How to manage schema changes on many identical schema-based databases with mysql?

1 Answers1