mysqldump and restore of Innodb Tables

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


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.

- Rajesh

Re: mysqldump and restore of Innodb Tables

Rajesh Kapur wrote:
Quoted text here. Click to load it

To back up:
   mysqldump --opt --user=username --password=password database >

To restore:
    echo "SET AUTOCOMMIT=0;"
    cat databasedump.sql
    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

Bill K.

Site Timeline