[SOLVED] Moving an InnoDB tables to a different server

[s0c0]

I had to move to some InnoDB tables to a different server today and it didn't work cause of all the foreign key constraints.  We tried using mysqldump and copying the actual files over to the new server all of which failed.  I were rather afraid to copy the ibdate file which was suggested by the MySQL site http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html.

 

What I instead did was copy the table structures over as MyISAM.  Then add all the data over.  Then finally I did all the alter statements to add my foriegn key constraints, indexing etc...  This worked, but dear god there has to be an easier way next time.

[fenway]

Yeah, it's a burtal process.

[effigy]

What options did you use for mysqldump? I have this in a shell script for InnoDB:

mysqldump \

-u root \

-p \

--skip-opt \

--single-transaction \

--add-drop-table \

--create-options \

--quick \

--extended-insert \

--set-charset \

--disable-keys \

$1 > $dump_file

[s0c0]

Okay I figured out the easier way:

 

http://blog.cnizz.com/2008/11/11/moving-innodb-tables-database-to-a-new-server/