5

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.

2
  • 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 Commented Jun 11, 2012 at 16:32
  • I made this one for mysql table comparison tablediff.com
    – Mihai
    Commented Aug 30, 2019 at 9:35

2 Answers 2

2

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

The MySQL Comparison Bundle is free for non-commercial use, personal use, and for those in the charity and education sectors. source: https://www.red-gate.com/products/mysql/mysql-comparison-bundle/index

2
  • 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
    Commented Jun 19, 2012 at 10:09
  • 2
    @Jeff - Here at Redgate we've just released both our comparison tools free for non-commercial use. It would be great if you could modify your post to reflect this! Many thanks! Commented Apr 13, 2016 at 17:27
4

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.

1
  • 1
    Added ` | grep -E "(-- Table|<|>)"` on my sdiff and it works great! Thanks for the handy one (three) liner!
    – MaddHacker
    Commented Nov 16, 2012 at 15:17

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