Can I Import an updated structure into a MySQL table without losing its current content?

Question

We use MySQL tables to which we add new fields from time to time as our product evolves. I'm looking for a way to export the structure of the table from one copy of the db, to another, without erasing the contents of the table I'm importing to.

For example say I have copies A and B of a table, and I add fields X,Y,Z to table A. Is there a way to copy the changed structure (fields X,Y,Z) to table B while keeping its content intact?

I tried to use mysqldump, but it seems I can only copy the whole table with its content, overriding the old one, or I can use the "-d" flag to avoid copying data (dumping structure only), but this will create an empty table when imported, again overriding old data.

Is there any way to do what I need with mysqldump, or some other tool?

Answer 1

I just had the same problem and solved it this way:

Export the structure of the table to update. Export the structure of the development table.

run this code for the first file "update.sql" needs to be changed according to your exported filename.

cat update.sql|awk -F / '{
 if(match($0, "CREATE TABLE")) {
   { FS = "`" } ; table = $2
 } else {
   if(match($0,"  `")) {
     gsub(",",";",$0)
     print "ALTER TABLE `" table "` ADD" $0
    }
 }
}' > update_alter.sql

run the same command for the second file

cat development.sql|awk -F / '{
 if(match($0, "CREATE TABLE")) {
   { FS = "`" } ; table = $2
 } else {
   if(match($0,"  `")) {
     gsub(",",";",$0)
     print "ALTER TABLE `" table "` ADD" $0
    }
 }
}' > development_alter.sql

run this command to find the differences in the output files

diff --changed-group-format='%<' --unchanged-group-format='' development_alter.sql update_alter.sql > update_db.sql

In the file update_db.sql there will now be the code you are looking for.