How do you update the structure of an entire MySQL database?

Question

ok, say you have two dbs. one you use as a master template which goes through various revisions, let’s say we’re now on revision 3. but the second db is a copy of the template as it was at revision 1.

because db 2 is in use, you don’t want to drop any tables, but you do need to update the structure of the entire db to the latest version of db 1.

is there any method of doing this without having to manually go through and make all the same changes again and again (over many dbs at various versions) and without losing any of the data?

i know data integrity is the problem, because some data may no longer be valid etc etc, but isn't there a way to override that?

ideally i just want to be able to use the mysqldump of the structure of db 1 and overwrite any conflicting tables in db 2 with the new versions from db 1 without losing all the data. if there's any way to do this in phpmyadmin, that would be grand.

Answer 1

You can try MySQL workbench; it allows you to

1. Create schema from a MySQL database.
2. import the schema into the MySQL workbench by reverse engineering the db version 3,
3. and then compare it with the schema in db version 1 to generate a SQL alter script.

Answer 2

Either your DB connector does that for you (some Python frameworks can evolve a DB) or you must do it manually.

If you must do it manually, the best approach is to be able to create the whole database with some data from scripts. This must be fully automatic. After creating the base version, you apply script after script to evolve the DB (altering tables, etc).

Do this in automatic tests and you can automatically migrate the DB when installing the next version at a customer's site.