Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We'd like to update an old database schema for a new software release that requires changes in database schema (additional colums, column renames, table renames, datatypes, ...). Is there any comparison tool for MySQL data base that can help to adapt the old schema to the new one without the loss of any stored data where possible? (free software preferred)

The changes can be made by hand, yes. But we're looking for a more comfortable way and to catch all the changes made.

share|improve this question

closed as off-topic by bluefeet, ypercube, Marian, Paul White, Max Vernon Oct 10 at 13:50

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – bluefeet, ypercube, Marian, Paul White, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

    
This is an interesting question because the second question that was ever posted in the DBA StackExchange is what you are asking now : dba.stackexchange.com/q/2/877 –  RolandoMySQLDBA Jun 11 '12 at 16:32

3 Answers 3

up vote 1 down vote accepted

Red Gate now has a MySQL Compare tool similar to their popular SQL Compare for Microsoft SQL Server.

http://mysql-compare.com/info?source=products-a-page

They have a free trial you can download to get you started.

share|improve this answer
    
I used this tool to get a visual overview over changes and diffs. It was useful in our case, but i missed a feature to rename table columns that are different. But it helped a lot. Thanks –  Benjamin Jun 19 '12 at 10:09

This isn't the a very sexy answer but mysqldump & sdiff.

mysqldump -u root -p --no-data -h oldHost databases db1 db2 db3 > /tmp/old.sql
mysqldump -u root -p --no-data -h newHost databases db1 db2 db3 > /tmp/new.sql
sdiff /tmp/old.sql /tmp/new.sql

sdiff will give you a side by side comparison with differences paired w/ a > or < indicator. I've found this to be useful in the exact scenario you describe.

The example supposes you have the new schema initialized on a different host, but this certainly isn't a requirement. It could be a different instance (e.g. port) on the same host or the same instance with different database names.

share|improve this answer
    
Added ` | grep -E "(-- Table|<|>)"` on my sdiff and it works great! Thanks for the handy one (three) liner! –  MaddHacker Nov 16 '12 at 15:17

I do not know about free tool but there is SQLyog - MySQL GUI which has schema and Data synchronization tool which shows the diffrence between two databases and on action performs sync operation.

SQLyog comes with 30 days trial though, Have a try.

share|improve this answer
    
Also if you are looking for free utilities then try using Percona-Toolkit but it's a console based application percona.com/doc/percona-toolkit/2.1/pt-table-sync.html percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html –  Mahesh Patil Jun 11 '12 at 13:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.