Problem with mysqldump and view

You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema?

In that post I show these minimum privileges for a mysqldump

• SELECT
• SHOW VIEW (If any database has Views)
• TRIGGER (If any table has one or more triggers)
• LOCK TABLES (If you use an explicit --lock-tables)

You should run this command:

SHOW GRANTS FOR [email protected];

If SHOW VIEW is not there, that's the reason why.

UPDATE 2014-04-16 23:06 EDT

When you did this

mysqldump --all-databases --routines >> all.sql

I see you did not specify the user and password. That being the case, you were not logged in as root@localhost. You will have to be explicit in specifying the root user

mysqldump -uroot -p --all-databases --routines >> all.sql

You will see the password prompt. Enter the root@localhost password and you are off and running.

You could also specify the password too

mysqldump -uroot -ppassword --all-databases --routines >> all.sql

Give it a Try !!!

WILD SUGGESTIONS

If you are using .~/my.cnf and still getting an error, you might be hitting this situation in Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user '

If the config file is .~/my.cnf is really /root/.my.cnf, perhaps you are not logged in as Linux root. You may have to run sudo.

Please run this command

mysql -ANe"SELECT USER(),CURRENT_USER()"

If you do not see root@localhost twice, then you are not authenticating correctly.

In .my.cnf you need to make sure that user and password are under the [client] section

[client]
user=root
password=rootpassword

not under the [mysql] section.

UPDATE 2014-04-17 13:53 EDT

I cannot help look at that bug report and wonder the following: Since you have DEFINER=tungbt@192.168.12.197, it is possible that root@localhost is behaving like tungbt@192.168.12.197 ? I say this because according to the MySQL Documentation on CREATE VIEW: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition.

You could change the definer of the view to root@localhost and try the mysqldump again

I had a similar problem with not being able to do a mysqldump on my view as root:

mysqldump: Couldn't execute 'show create table `v_view01`': View 'my_db.v_view01' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

In my case, it was because my underlying schema changed, so the view that relied on it was no longer valid. Even though, I was executing the dump as root, it still says "or invoker of view lacks rights...". The solution was simple:

drop view v_view01

Since the view was already outdated, I just dropped it, then the mysqldump proceeded like normal.