mysqldump and restore of Innodb Tables

106 views
Skip to first unread message

Rajesh Kapur

unread,
Mar 22, 2005, 10:51:33 AM3/22/05
to
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

unread,
Mar 22, 2005, 3:25:44 PM3/22/05
to
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=password database >
databasedump.sql

To restore:
(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat databasedump.sql
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
) | mysql --user=username --password=password 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.

Reply all
Reply to author
Forward
0 new messages