0

I'm working with a project which is using mysql as the database. The application is hosted with many clients and we are doing upgrades for the current live systems often.

There are some instances where the client has change the database structure(adding new tables) and causes some unexpected db crashes.

I need to log all the structural changes which were done at that database, so we can find the correct root cause for that. We can't do it 100% correct with diff tool because it will not show the intermediate changes.

I found http://www.liquibase.org/ tool but seems little bit complex.

Is there any well known technique or a tool to track database structural changes only.

Thilanka
  • 1,733
  • 5
  • 21
  • 36
  • It seems a bad thing to let someone modify the database if they can't just remember/write down their changes. Every change we do goes through a script, that is accompanied by a motivation/explanation and a thorough team check. – GolezTrol Sep 12 '12 at 10:36
  • Important, in sql server there are DDL triggers that can keep help you to log the schema changes but in my sql they are not supported http://stackoverflow.com/questions/8657405/mysql-trigger-to-fire-on-alter-or-drop see this... I think you need to go as per my below answer... – Jigar Pandya Sep 12 '12 at 12:56

2 Answers2

1

If you have an application for your clients to manage these schema changes, you can use a mechanism at application level. If you have a Python and Django-based solution, you could probably use South which provides schema change tracking and rollbacks.

Chris Ballance
  • 33,810
  • 26
  • 104
  • 151
j0nes
  • 8,041
  • 3
  • 37
  • 40
1

well from mysql studio you can generate all object's schema definition and compare them with your standard schema definition and this way you can compare two database schema...

generate scrips of both database (One is client's Database and One is master copy database) and then compare it using file compare tool would be the best practice according to me because this way you can track which collumn was added, which column was deleted, which index was added like wise without any tool download.

Possiable duplication of Compare two MySQL databases ? Hope this helps.

Community
  • 1
  • 1
Jigar Pandya
  • 6,004
  • 2
  • 27
  • 45
  • This will be a diff tool kind of thing. It is not logging continuously. It will not be able to track a table which was drop and create newly. – Thilanka Sep 12 '12 at 10:43