mysqldump and restore of Innodb Tables

[Rajesh Kapur]

Hello,

We are on MySQL 4.0.21 on linux. We use InnoDB tables and foreign key
constraints extensively. The mysqldump backs up the database tables in
alphabetical order with foreign key constraints defined in the create
statement of each table. These foreign key constraints are violated at the
time of restore. We have tried the following two solutions...

(1) We have tried to backup the database tables in the order of their
dependencies. This works but the backup scripts need to be constantly
maintained as new tables are added/removed from the database.

(2) phpMyAdmin export does the database dump and puts the table constraints
as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin
dumps cannot be automated to the best of my knowledge.

Does anyone have a solution/script to backup databases with InnoDB tables
such that constraints will not be violated at the time of restore.

Thanks!
- Rajesh

[Bill Karwin]

Rajesh Kapur wrote:

Does anyone have a solution/script to backup databases with InnoDB tables
such that constraints will not be violated at the time of restore.

To back up:
mysqldump --opt --user=username --password=passwo rd database >
databasedump.sq l

To restore:
(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHE CKS=0;"
cat databasedump.sq l
echo "SET FOREIGN_KEY_CHE CKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
) | mysql --user=username --password=passwo rd database

This should resolve the foreign key violations, and also disabling the
autocommit should make the restore go much faster.

I based the above on suggestions in the user comments at
http://dev.mysql.com/doc/mysql/en/mysqldump.html

Regards,
Bill K.