Migrate data in mysql from old schema to new schema

Question

Hello,

We have recently improved the schema of our production database, changing column names and indexes etc. We also changed the storage engine to InnoDB to make use of transactions and foreign keys.

What is the best way to import the data from the old schema into the new schema? Bare in mind that column names have changed (including primary keys).

Thanks in advance. Toby.

Answer 1

1. As if you have changed the column names you may create out files and import.

SELECT * FROM old-table INTO OUTFILE '/path/to/filename.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

and then you may do LOAD Data: LOAD DATA INFILE '/path/to/filename.txt' INTO TABLE new-table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Ref: http://dev.mysql.com/doc/refman/5.0/en/select.html http://dev.mysql.com/doc/refman/5.0/en/load-data.html

2. Otherwise if possible following will do the job as far as number of columns are same:

insert into new-table select * from old-table;

If number of columns are not same then:

insert into new-table select col1,col2,... from old-table;