[TriLUG] mysqldump of databases with foreign keys

[Bradford Powell]

Replying to my own message...

It appears that foreign key checks can be disabled with
SET FOREIGN_KEY_CHECKS = 0;

(they should of course be re-enabled after restoring)

This also points out the importance of including version numbers when
asking questions. I am using mysql 4.0.20. Versions >= 4.1.1 include
statements to disable key checks in the dump file automatically.

-- bradford powell

On Fri, 9 Jul 2004, Bradford Powell wrote:

> I asked this question on IRC, but apparently no one was around with any
> ideas...
> 
> How does one go about making a backup of a mysql database that has foreign
> keys? When I use mysqldump, it just dumps the tables in alphabetical
> order. This results in some foreign key constraints being defined before
> the tables they reference. Obviously this is an error condition. Is there
> some way to automatically reorder the dump to take dependancies into
> account or to inactivate foreign keys until after the dump is complete?
> 
> I never have this problem with pg_dump...
>