Dumping views with mysqldump in the right order.

Question

I have a script that backs up our database, which contains multiple tables and views constructed from tables.

The command used is: mysqldump -u UserName -ppassword -h hostname DatabaseName > dump.sql;

I have noticed however that some view definitions are backed up before the definitions of the tables. This causes an issue when restoring using the classic

mysql -u UserName -p < dump.sql

As when it tries to create the view, the table it needs does not exist yet. It is possible to edit the dump files to be restored, but I was wondering:

Is there a way to either make sure that mysqldump backs up the tables and views in the right order? Or is there a way to restore from a dump that will find the right tables to create first (or create sane temporary tables)?

Edit for version: mysqldump Ver 10.11 Distrib 5.0.51b, for redhat-linux-gnu (x86_64)

Answer 1

In my experience, mysqldump always puts the "create view" statements at the end, after all of the "create table" statements. So I'm not sure why you are having a problem.

That being said, if you import the dump with the -f flag it should resolve your issue, because the "Create view" will throw an error, but the view should still be created and work fine once the table is in place.

Example:

mysql -f -u UserName -p < dump.sql