3

How would I be able to track all changes to my database?

I need a way to find out exactly what changes I made to the database so that I can make 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.

Ryan Kohn
  • 13,079
  • 14
  • 56
  • 81

7 Answers7

9

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

  1. Change the development 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.

Lesmana
  • 25,663
  • 9
  • 82
  • 87
Anonym
  • 3,098
  • 4
  • 31
  • 32
  • +1: pay heed to "getting around to 4-6". Better yet, find a way to do controlled upgrades during development and keep the SQL scripts and apply them to your production DB. Then include a "change log" in the DB that you insert into whenever an upgrade script is run. – D.Shawley Sep 10 '09 at 18:00
2

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.

ChssPly76
  • 99,456
  • 24
  • 206
  • 195
  • The advantage to LiquiBase over the (good) advaice goven by @Anonym is that Liquibase allows you to manage and control your db changes as opposed to just reacting to change. – DaveParillo Sep 10 '09 at 21:21
0

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.

dnagirl
  • 20,196
  • 13
  • 80
  • 123
0

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.

D.Shawley
  • 58,213
  • 10
  • 98
  • 113
  • 1
    That'll only work if the databases are actually a replica of eachother. More often you have different installations with different data - but both need to be updated to the same schema when you update the rest of the software that talks to the db. – nos Sep 10 '09 at 13:41
  • Thanks for your reply, but replication doesn't seem to be the solution because all I need to know is the changes that have been made to the database. –  Sep 10 '09 at 14:12
0

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

knittl
  • 246,190
  • 53
  • 318
  • 364
  • phpmyadmin will die as soon as php_script_max_time is reached, so it's good for one thing: doing small changes; otherwise it's as useless as it can get. seriously, don't use it. use command line. – dusoft Sep 11 '09 at 09:00
0

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

Hugues Van Landeghem
  • 6,755
  • 3
  • 34
  • 59
  • Thanks for the reply. I am using EMS sql manager, how would i be able to use this tool to compare the databases –  Sep 11 '09 at 08:44
  • Also, WebYog SQLYog Ultimate is pretty neat https://www.webyog.com/product/sqlyog – vicenteherrera Sep 24 '13 at 11:22
0

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