A standard mysqldump will not work for moving InnoDB tables to a new server. It will create the tables and the structure, but will fail on inserting the data. Why? If you have any foreign key constraints mysqldump is not “smart” enough to insert the rows containing the primary keys before the rows using those keys as foreign keys. You will get MySQL error 150: Foreign key constraint is incorrectly formed.
There are two methods two get around this:
The Hard Way
- Create your database on the new server
- Using a mysqldump create all the InnoDB tables (if this fails create them as MyISAM), do not run any of the ALTER statements prior to this
- Now using the mysqldump insert all the data
- At this point if you had to create the tables as MyISAM, convert them to InnoDB
- Finally using the mysqldump run your ALTER statements to add constraints and indexes
Why this works? By creating your tables with no foreign keys or indexes when you insert the data those rows will not be dependent on any other tables. Once the data is in there (assuming your data has referential integrity) then you can add your constraints. This time the constraints will not fail because all of the necessary rows and keys are in the database now.
The Easy Way
Using phpMyAdmin access your database, go to export, and select the “Enclose export in a transaction” checkbox. This will perform a mysqldump that creates tables first, then inserts the data, and then adds your constraints.