We have a moderately sized database, more than 5GB in size, several
million rows and 70 tables.

We're running MySQL 5.22 and the database uses innodb throughout with
multiple foreign keys in use.

During development the structure of several tables has been changed many
times, such that we now have a number of rendundent columns.

We've created a new, empty database with our proposed new structure and
I'm now looking for the most efficient way to get our existing data into
this new structure, dropping any data in columns that no longer exist.
Can anyone propose a sensible way to go about this?

Because we're using innodb, dropping columns one at a time takes an age
as every index is rebuilt. Just laoding the database from a mysqldump
file takes about five hours so I'm pretty sure we'll want to load data
from our old database into the new db with the new structure - if anyone
can recommend a strategy to do that, or suggest an alternative, I'd be
most appreciative!

Thanks,

Russell

Well,

Maybe 70 pairs of "select into outfile" - "load data infile". At least,
this way you can select only columns you want to be in your new
database.

Best,

Mikhail Berman

-----Original Message-----
From: Russell Horn [mailto:mysqllists@albanach.com]
Sent: Wednesday, December 13, 2006 12:41 PM
To: mysql@lists.mysql.com
Subject: Copying DB to new structure

We have a moderately sized database, more than 5GB in size, several
million rows and 70 tables.

We're running MySQL 5.22 and the database uses innodb throughout with
multiple foreign keys in use.

During development the structure of several tables has been changed many
times, such that we now have a number of rendundent columns.

We've created a new, empty database with our proposed new structure and
I'm now looking for the most efficient way to get our existing data into
this new structure, dropping any data in columns that no longer exist.
Can anyone propose a sensible way to go about this?

Because we're using innodb, dropping columns one at a time takes an age
as every index is rebuilt. Just laoding the database from a mysqldump
file takes about five hours so I'm pretty sure we'll want to load data
from our old database into the new db with the new structure - if anyone
can recommend a strategy to do that, or suggest an alternative, I'd be
most appreciative!

Thanks,

Russell

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mberman@ivesinc.com