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