s0c0 Posted November 11, 2008 Share Posted November 11, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 11, 2008 Share Posted November 11, 2008 Yeah, it's a burtal process. Quote Link to comment Share on other sites More sharing options...
effigy Posted November 11, 2008 Share Posted November 11, 2008 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 Quote Link to comment Share on other sites More sharing options...
s0c0 Posted November 13, 2008 Author Share Posted November 13, 2008 Okay I figured out the easier way: http://blog.cnizz.com/2008/11/11/moving-innodb-tables-database-to-a-new-server/ Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.