Any Way To Order The Tables For A 'backup Database'?

[Larry Woods]

I have a bunch of tables that have foreign keys but it turns out that alphabetically the tables that reference the foreign keys are before the tables that contain the keys. Therefore I can't restore the database. MySQL doesn't like it if you reference a foreign key if the table that contains the key doesn't exist!

What I want to do is position the referenced tables first in the backup so that I can perform a restore. Otherwise the backup is useless!

Ideas?

Thanks in advance.

[Larry Woods]

'Larry wrote:

I have a bunch of tables that have foreign keys but it turns out that alphabetically the tables that reference the foreign keys are before the tables that contain the keys. Therefore I can't restore the database. MySQL doesn't like it if you reference a foreign key if the table that contains the key doesn't exist!

What I want to do is position the referenced tables first in the backup so that I can perform a restore. Otherwise the backup is useless!

Ideas?

Thanks in advance.

I think that I found my answer. SET FOREIGN KEY=0

Right?

[peterlaursen]

almost!

Code:

SET FOREIGN_KEY_CHECKS = 0;

Refer: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_foreign_key_checks

[peterlaursen]

Additionally:

If you restore with SQLyog 'restore from SQL dump' the statement must be in the file itself. It will not do to execute from editor and next use 'restore from SQL dump' as 'restore from SQL dump' uses its own connection/session. Alternatively (if the script is not too large) paste the contents of the file into the editor.

If you back up with SQLyog there is a (default) option to add the statement SET FOREIGN_KEY_CHECKS = 0 on top. It will look like

Code:

/*!40014 [b]SET[/b] @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, [b]FOREIGN_KEY_CHECKS=0[/b] */;
— statements here
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

('bolded' SET FOREIGN_KEY_CHECKS=0 above)

[Author]

Posts