The definition of the View must be contained within the dump file.
To see the definitions, run the following command on the dumpfile:
cat backup.sql | grep -A 3 "CREATE ALGORITHM" > backup_views.sql
cat backup_views.sql
You could then drop the offending view(s) and recreate them manually.
If you have another server with the views established already, you should mysqldump the schema only. It will contain the views as well. @DTest and I wrote earlier posts on this.
Views are always processed last (after all tables) in a mysqldump.
This problem may also occur if one view references another, and the order the views are created doesn't take account of that.
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
Long story short: You do not have the user
'lumicrm'@'%'
on the DB Server.Login to MySQL and run
Let's say you see the user
'lumicrm'@'localhost'
You have to create the user so that the view can be recognized. There are three(3) things you can try
Try This #1
Login to MySQL as
lumicrm
and run this commandGiven the output of that query:
Try This #2
Now you have the MySQL user
'lumicrm'@'localhost'
hacked inTry This #3
Dump the Views to a Text File. Here are my posts on how to do this:
Jun 26, 2011
: Modify DEFINER on Many ViewsJan 19, 2012
: Will mysqldump command also backup all the views that are present in DB or there is a special command for it?May 02, 2012
: Problem with View When Restoring a MySQL BackupEdit the user for the View with the appropriate username. Copy the View Creation Code and Paste into a MySQL Session to create the View.
Give it a Try !!!