I have a DB where all of the tables are InnoDB, and there are a few straightforward FOREIGN KEY constraints. I would like to use mysqldump to back up the data or move it to another machine.
The problem I'm running into is that when I try to import the data using mysql, the data is imported in the order in which it was dumped, that is to say, alphabetically by table.
This ends up violating the key constraints. Is there a way to specify the order in which tables are dumped from mysqldump?
No, but you can disable key checking while you're reloading the file. Invoke mysql, then issue these statements:
mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> SOURCE your_dump_file;
If that's all you're going to do, you can exit mysql. Otherwise, reenable foreign key checking:
mysql> SET FOREIGN_KEY_CHECKS = 1;
I also notice that in the dump, there are what appear to be statements disabling keys, but they're commented out. (I'm dumping from version 3.23.51). How can I get these statements to be not commented out, and will that help? Also, in the comment, there is a !40000. What does that mean?
That means they'll be ignored for servers older than 4.0.0. They're intended for MyISAM tables, actually, for which enabling/disabling keys during loading gives you a performance boost. For InnoDB, they'll be ignored in any case.
Finally, I have INT UNSIGNED AUTO_INCREMENT primary keys in my tables, and if I try to mysqlimport from a file that has a value of "0", instead of writing the row like that, it picks a new value for that column. Is there any way to get mysqlimport to import exactly as written, rather than auto-incrementing?
No. You're abusing the AUTO_INCREMENT column by storing 0 in it. You shouldn't do that, and now you know why.
Thanks very much for any help.
-- Rick
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
