When using mysqldump
to backup MySQL, I got following error.
mysqldump --all-databases --routines >> all.sql
mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user 'tungbt'@'192.168.12.197' for column 'id' in table 'hdcn_hd' (1143)
hdkien
is a view
CREATE ALGORITHM=UNDEFINED DEFINER=`tungbt`@`192.168.12.197` SQL SECURITY DEFINER VIEW `hdcn`.`hdkien` AS (...striped...)
User tungbt
@192.168.12.197
has already had the privilege to select on table hdcn_hd
and I can select from view hdkien
without problem.
mysql> select * from hdkien limit 1;
+------+-----------+
| id | shd |
+------+-----------+
| 876 | ADFADFA1 |
+------+-----------+
More info:
- MySQL version:
mysql-community-server-5.5.37-4.el6.x86_64
- OS: CentOS 6.5
Why I got the error when running mysqldump
and how can I resolve it?
Update 1 (2014/04/17)
I run mysqldump
with user 'root'@'localhost'
.
mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...striped...' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
The user 'tungbt'@'192.168.12.197'
is the definer of view hdcn.hdkien
(and the SQL SECURITY
is DEFINER
.
+------------------------------------------------------------------------------------------------------------------+
| Grants for tungbt@192.168.12.197 |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tungbt'@'192.168.12.197' IDENTIFIED BY PASSWORD '...striped...' |
| GRANT ALL PRIVILEGES ON `hdcn`.* TO 'tungbt'@'192.168.12.197' |
+------------------------------------------------------------------------------------------------------------------+
Update 2
$ mysql -ANe"SELECT USER(),CURRENT_USER()"
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
Update 3
mysql> SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
| 42 | 5.5.37-log |
+------------------+--------------+
tungbt
@192.168.12.197
still exist?