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

share|improve this question
    
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. –  FreshPhilOfSO Aug 16 '13 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 Aug 16 '13 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 Aug 16 '13 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 Aug 16 '13 at 13:39
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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