CODEWITHSUNDEEP
mysql
Boardy
Boardy

Reputation: 36205

Restoring data without recreating MySQL Tables

This may sounds like a stupid question but can't find anything on google, probably using the wrong key words.

Anyway, I have been working on a project - version 1 which has a MySQL Database. I ready to release to version 2 but there are changes to the database tables, e.g. extra columns.

If I backup the current database with the data and create a database with the new structure. How can I add the data from the old database into the new database.

I know there won't be any problems with the existing data being added to the new database structure as the existing fields haven't changed, its just extra columns.

Thanks for your help.

Upvotes: 0

Views: 265

Answers (2)

Igor
Igor

Reputation: 2659

I use mysqldump with some addition keys in this case, something like

mysqldump --host=localhost --user=root --no-create-db --no-create-info --complete-insert --extended-insert

That will produce the complete inserts with column names, so you may not to worry about the final table structure, if you did not change the column names, even the order of columns may change in this case.

Upvotes: 1

MrGomez
MrGomez

Reputation: 23886

Consider using ALTER TABLE to resolve this issue.

The key is to take the new fields in your database and append them to the end of your entities, like so: ALTER TABLE myTable ADD COLUMN myColumn (... further specification ...)

MySQL will expand the table and set the new fields to the defaults you specify. You can then layer any new data on top of the old, as long as there are no conflicts, as you describe.

Option B, when the online solution is expensive, is to use mysqldump, then alter the output to fit the new table specification. As long as the columns align properly (this may require a simple regular expression to parse, in the worst case), you should be able to recreate the data by importing it into the new schema.

See also, this answer.

Upvotes: 1

Related Questions