How do i backup database data, so that it fits in a new database structure?

I have one local server where i work on my project, and i have开发者_C百科 another test server where i deploy the project.

Case: I want to transfer the new database structure from the local server to the test server, and keep the data that already was on the test server database.

I tried to do this:

Backup the test server data by:

mysqldump –u USER –p –no-create-info DATABASE < OUTPUTFILE.sql

Then transfer the new structure to the test server, and then:

mysql –u USER –p –h localhost DATABASE < OUTPUTFILE.sql

to get all the data back.

The problem is this:

The table i have altered looks like this:

(local) -------------------------------(test server)

TABLENAME--------------------- TABLENAME

id ------------------------------------------id

name ------------------------------------name

active

So when i try to insert the old data to the new structure i get the errormessage:

ERROR 1136 (21S01) at line 17: Column count doesn't match value count at row 1

since it is trying:

INSERT INTO `TABLENAME` VALUES (1,"test")

Anyone got an idea of what i can do to solve this problem?

Thank you in advance

This is done in the commandline, but could ofcourse be done using mysql:

The solution is to first backup the data on the test server, so that it create full insert statements:

mysqldump –u USER –p PASSWORD --no-create-info --complete-insert  DATABASE > BACKUPFILEData.sql

Then i backup the new structure of the local database:

mysqldump –u USER –p PASSWORD --no-data DATABASE > BACKUPFILEStructure.sql

Now that i have a backup of the old data, and the structure, i can insert the structure in the test server databbase:

mysql –u USER –p Password –h localhost DATABASE < BACKUPFILEStructure.sql

And finally we need to insert the old data again:

mysql –u USER –p Password –h localhost DATABASE < BACKUPFILEData.sql

Voila!

The old data is back in the new db structure

I'd suggest you copy the database locally, alter the table so that it fits the destination layout, then dump the copy, drop it and load the dump on the destination server. As an alternative, you could switch to dumping tables instead of the whole database, not dump the problematic table and use the copy/alter/dump/load for just this one table.