Take MySQL Database dump contain Constraints tables

Question

To take the backup I follow the below procedure.

1. First I take the list of table in my DB using SHOW TABLES LIKE
2. Then take table structure using SHOW CREATE TABLE
3. Then Saving all table structure and its value in to the file.

Now Backup works fine.

While restoring I am facing the problem.

If some of tables contain foreign key constraint with reference of other table. I am not able to create the table.

I found the problem because I am taking backup table by table .

For example there are 4 table A,B,C,D

• A - Contain Constraint with C
• C - Contain constraint with D.

I Take the backup above table and store them into file like this order A,B,C,D. while Restoring causing error.

My question is How to handle while backup the database if table contain constraints?

I searched lot but I was not able to get good solution. So, Please share how to do this or share if I any thing done wrong.

Thank you.

Answer 1

As for the update to this question, I temporarily disabled the key check now it's worked. this solution posted in this question http://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql

SET FOREIGN_KEY_CHECKS=1;