Views not being dumped by mysqldump

Question

I want mysqldump to dump views. I'm running the following mysqldump command from PHP:

mysqldump -h127.0.0.1 -uroot -proot --database main -R --extended-insert=FALSE --complete-insert=TRUE

and it doesn't seem to be dumping views (that I created with CREATE VIEW). How can I get it to dump views as well as tables?

Solution

According to this you can have problems with views because you don't have explicit SHOW VIEW privilege for the database.

"The workaround to the problem is for the administrator to manually grant the SHOW VIEW privilege to users who are granted CREATE VIEW, since MySQL doesn't grant it implicitly when views are created."

In all other cases, views should be in the resulting dump file...a bit strange though...it first creates temporary table structure for view, and then the view itself. This is because view can be created on view...and for mysqldump, there is no way to know in what order that happened, so it creates temporary tables, in order to avoid errors when dumpfile is executed in order to restore the database.

Comments

[Fragsworth]

I will try adding the SHOW VIEW privilege to see if it works.