By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,301 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,301 IT Pros & Developers. It's quick & easy.

mysqldump and restore of Innodb Tables

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.