Mysqldump and table order

Question

I am copying my database with mysqldump - uroot -p pagesjaunes > E:\db.sql
But when I try to execute mysql pagesjaunes < db.sql on my home computer, I got an error because mysqldump did not put tables with the correct order in the db.sql file, tables without a foreign key should be, for example, the first.

 DROP TABLE IF EXISTS `fonction`; CREATE TABLE `fonction` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nom` varchar(20) NOT NULL, `id_qualite` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fonction_qualite_fk` (`id_qualite`), CONSTRAINT `fonction_qualite_fk` FOREIGN KEY (`id_qualite`) REFERENCES `qualite` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `qualite`; CREATE TABLE `qualite` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nom` varchar(40) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8; 

I need to put the qualite table qualite , but it works, but I have many tables, and there will be a lot of manual ordering.

So how can I handle this?

Answer 1

I found the solution HERE , I was mistaken, in the file E:\db.sql generated I deleted lines like this

 /*!40014 SET @ OLD_FOREIGN_KEY_CHECKS=@ @FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 

I thought it was just a comment, but actually it is also instructions.

Answer 2

You cannot use the --compact mysqldump parameter because it removes information (comments) about the foreign key dependency that mysql needs to recreate the tables later in the correct order.