Today, I had a problem while restoring a crashed MySQL server…
The daily backup is done via a mysqldump (then the dump files are moved to a separate box). The DB to restore used InnoDB and foreign keys.
I started to restore the data using:
# mysql -u root -p DB <DB-dump.sql
It failed a few seconds later with an error 1005 (errno 150). It was impossible to create some tables!?
In fact, during a mysqdump, there is no check of the constraints between the tables! And mysql read the dump file sequencially.
To avoid this problem, reload your DB with:
mysql> set foreign_key_check = 0; mysql> source DB-dump.sql; mysql> set foreign_key_check = 1;