MySQL synchronization - Looking for advice for this specific scenario

Often we have this scenario:

• We have our database on the production.
• We realise (or the client requests) schema changes.
• We change locally.
• We need to upload that new schema without losing data.

---

For what I've read so far, I notice that we can:

• Use a GUI to make this synchronization.
• Use some sort of subversion to track database changes.
• Create a database table to track changes.
• Use some sort of automatized task when deploying (bash process I believe?).

GUI is a nice approach because it's an easy way of doing. But is not automated and we cannot revert to previous "versions".

Subversion we can get different versions of our database schema. But is very complicated to understand and to create a workflow that could comprise the schema on one side, and the data on the other side.

The SQL table approach seems to difficult to understand that I can't even see the good and bad parts there.

The bash automatized task seems to be a very nice approach because, if well configured, it enters nicely on deployment workflow. But it seems that we cannot have control above old versions of our database.

---

Truth being told, I don't see a SO important need for have the database under any subversion system because, hopefully it's not something that we change every day, like other files on our application.

With all this, I'm inclined to search for a bash process that could, on deployment, do two things:

1. Retrieve data from production server. (Because it will be there where the most update data will exist.)

2. Update remote schema w开发者_如何学JAVAith local one. (Because it is locally that we will first change our schema.)

What do you think? What do you suggest? Is there a standard way for dealing with this (it seems to me) common scenario?

---

My favorite is the general approach taken by Ruby on Rails for database migrations. In rails, you create a migration file, which is using a domain specific language. But, the idea is that each migration has a version number. Also, each migration includes both implementation and a rollback steps. Finally, when migrations are executed, a local table in the database identifying the schema version is updated to identify the current version of the database. These migration files are checked into source control.

So, I recommend doing the following:

1. Create a schema change implementation and rollback script for each schema change
2. Give these scripts a version number
3. Each script should validate that the database is currently the expected version, to ensure that the schema changes are performed in the proper order
4. Create a master script that interrogates the current version of the database, accepts the desired version of the database, and executes the proper implementation or rollback scripts to get the database schema to that version

So, if the database is currently version 14, and you call the master script with desired version 17, it will know to execute the schema change scripts for versions 15, 16, and 17. If the database is currently 15 and the desired version is 12, it will know to execute the rollback scripts for versions 15, 14, and 13.

All of these scripts should definitely be checked into source control.