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
You should run this command:
SHOW GRANTS FOR tungbt@192.168.12.197;
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
Best Answer
My guess is that you cant use limit in combination with
--order-by-primary
One way around this is to define the order by within the
-where
argument. If you know the primary key for the table:Note that
<primary key>
is a place holder for the columns in the primary key.if you don't know the primary key in advance you can use the INFORMATION_SCHEMA to determine that:
and then use the information in mysqldump.