CODEWITHSUNDEEP
viewmysql
Fragsworth
Fragsworth

Reputation: 35497

Views not being dumped by mysqldump

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?

Upvotes: 3

Views: 15574

Answers (2)

kervin
kervin

Reputation: 11858

This works for me

mysqldump --skip-comments --skip-opt --complete-insert --add-drop-table --database dbname -p

Upvotes: 0

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

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.

Upvotes: 2

Related Questions