We have a production database that we have identified needs some maintenance as a colleague leaves - some column deletion and renaming, and a couple of table renames
We're trying to find the best (quickest) way to do this so that it causes the least inconvenience to the users.
Ideally we'd like to do the following
Create a copy of the current database using mysqldump
On the copy make the changes to required
Update the current applications to use the new column names and test against the copy
Somehow push the changes to production database and release updated apps into the wild
If we restrict the problem to column renames then it seems that we should be able to find an application to help us manage the change.
MySQL Workbench uses
CHANGE COLUMN to rename columns. Having looked around SQYLyog and Red Gate MySQL Compare have both been suggested as being suitable. However while both of these recognise the columns have been renamed, they both use
DROP COLUMN / ADD COLUMN which does not preserve the data
Red Gate SQL Data Compare doesnt recognise that the columns are the same after the rename so that is out as an option.
We'd like to avoid having to take the database server offline to make the changes, but is that the only option?