tracking changes made in database structure

Question

Hi all

I'm fairly new to MySQL. I need to know how would i be able to track any changes in my database.

I need a way to find out exactly what changes I made to the database so I can implement the same changes on another server.

For example, if I added a new field in one of the tables, I would like to know what field that was so that I could add that same field to another database.

Your help would be greatly appreciated

Pranay

Answer 1

A non-automated but useful way to keep track of changes to a db is with the MySQL Query Browser. It keeps a history of all operations you run while using it. This makes assembling a change script very straightforward.

Obviously, this is not a solution if you're looking to replicate all changes made by all users. But if you are just looking to keep a set of changes in the correct order and avoid retyping them, it works pretty well.

Answer 2

Have you considered using MySQL Replication to do this? You really don't want to implement this yourself unless you have to for some reason.

Answer 3

The steps people usually go through when facing these problems are:

1. Change the developemnt db in whatever way is needed at the time.

2. Ready to deploy a new version of the application - realize the production database needs to be updated as well while retaining the data.

3. Dump the production and development schema, manually diff the schema creation scripts. Bang head against wall. Write change scripts that updates the production database.

4. Realize the change scripts needs to be written from the start when updating the development database instead of willy-nilly add and change stuff.

5. Find a naming scheme for versioning those change scripts so any installation can be updated from a given version to a newer version.

6. place the DB creation and change scripts in source control.

Make sure you're not repeating 1-3 every time, but get around to do 4-6 so you don't have to repeat 1-3 every time.

Answer 4

phpmyadmin had a GSoC project involving replication this summer by Tomas Srnka. i think it made it’s way to trunk already

Answer 5

Take a look at LiquiBase

It allows you to describe database changes as XML so you can maintain your database structure in your VCS just like your code.

Answer 6

there is many tools to make data compare : - Database Worbench - EMS sql manager - SQL Maestro - Database comparer

but this tools can also be used for creating update script

Answer 7

I have been reading on the net about schemas, is this a useful way to compare databases and find any changes?