1

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

  1. Create a copy of the current database using mysqldump

  2. On the copy make the changes to required

  3. Update the current applications to use the new column names and test against the copy

  4. 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?

Improve this question
4
  • If you dump the DB using MySQL dump, a good tactic would be to actually modify the plain text dump file before you do the import - would be an easy way to do the renames.
    – Philᵀᴹ
    Commented Aug 16, 2013 at 9:35
  • That would be fine for creating the copy, it's getting the changes back into the production DB with live data that's the issue...
    – Dan Kelly
    Commented Aug 16, 2013 at 9:45
  • The version of MySQL server, size of the tables, and storage engine of the tables can impact your options. What are those?
    – Michael - sqlbot
    Commented Aug 16, 2013 at 11:45
  • Version is MySQL 5.0, Storage Engines are a mix of InnoDB and MyISAM. Part of the cleanup is to move to 5.1 and standardise onto InnoDB. Largest table is 61M, then 4.6M
    – Dan Kelly
    Commented Aug 16, 2013 at 13:39

1 Answer 1

Reset to default
1

We've ended up writing our own application that manages the Table and Column renames

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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