Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are working on a project which employs MySQL to store our data. We develop and test on a test server before rolling out to our production server. During the development process, we make structural changes to our database by adding/changing columns within a table or adding more tables to the database.

How do we propagate these changes from our test server to the production server without destroying existing data?

Our test server is on Mac OS X and production server is on Red Hat Enterprise. MySQL is at version 5.1.39. Thank you for reading and helping.

share|improve this question

4 Answers 4

up vote 1 down vote accepted

This might fit better over at StackOverflow, but I'll throw in my two cents.

This is part of the application deployment process that you'll need to figure out. You need to take a look at the database schema on the production server and the schema on the test server and then figure out exactly what changes are required to make them match. You'll have to go through table-by-table, column-by-column. Depending on the changes required, it's possible that you can't do a non-destructive deployment. Some changes are invasive enough that the only option is to migrate the data (sometimes this means exporting and reloading it, possibly with some data manipulation in between, but hopefully it means creating a replacement table in the production database and using SQL to dump the contents of the old table into it (possibly with some manipulation)).

In the future, I would recommend keeping database changes prominently in mind during the development process. Always remember that you're going to have to get those changes into the production server, and if you track your changes as you make them and plan out how they're going to be deployed, you can mak the deployment a lot easier.

share|improve this answer

I think this belongs to stackoverflow.

You can sql your changes in. either command line, or with a front end like phpmyadmin

 CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )

to modify a table:

ALTER TABLE table_name
ADD (   column_1 	column-definition,
    column_2 	column-definition,
    ... 	
    column_n 	column_definition );
share|improve this answer

As others have said, this is a stackoverflow question... but hey, while I'm here...

Look into how modern web application development frameworks handle this problem. Rails has an awesome way of dealing with it, called "migrations", that basically requires you describe each of your schema changes separately, then keeps track of the current schema version and applies the schema changes required to bring a database into sync with what should be the current reality. It's a life-changing feature.

share|improve this answer

We tend to manage these in two ways.

  • The good.

    For Django projects we use Django Evolution which basically does the same thing as womble describes of Rails "migrations". It's a framework for managing model changes and automagically realising them.

  • The not so good.

    Where we a less fortunate and have to deal with PHP projects, we stipulate that developers have to check their SQL changes in with the associated code commits, to a common location in the repository. The SQL changes comprise of two things; a clean statement that modifies the database and a complete schema that reflects the new state of the database. We have some tools which simplify the process of collecting this information and putting it into acceptable formats. At the time of code deployment the SQL file containing the change statements is executed against the database.

share|improve this answer

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.