Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- mysqldump and restore of Innodb Tables
- Rajesh Kapur
March 22, 2005, 3:51 pm
rate this thread
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.
Re: mysqldump and restore of Innodb Tables
Rajesh Kapur wrote:
To back up:
mysqldump --opt --user=username --password=password database >
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
echo "SET FOREIGN_KEY_CHECKS=1;"
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
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum