MySQL permission denied for database I have a remote MySQL server running on x.x.x.x. And there is a database named abc.

I can do the following

mysql -h x.x.x.x -uroot
use abc


But when I try to

mysql -h x.x.x.x -uroot --database=abc


I get

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'abc'


Why is that? And how do I fix it?

edit:

I have now resolved the issue by doing the following:


ssh in the box
run mysql -uroot
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;


But I still don't understand why.

mysql> SELECT USER(),CURRENT_USER();

+-----------------------------+----------------+
| USER()                      | CURRENT_USER() |
+-----------------------------+----------------+
| root@Maxs-MacBook-Air.local | root@%         |
+-----------------------------+----------------+


mysql> SHOW GRANTS FOR root@'%';

+------------------------------------------------------------------+
| Grants for root@%                                                |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%'                                 |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------+

mysql&gt; SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';
ERROR 1142 (42000): SELECT command denied to user 'root'@'Maxs-MacBook-Air.local' for table 'user'


Max
You cannot run 

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';


because you do not have remote rights to the mysql database.

You only have remote rights to mydb

GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION;


When you ran

mysql -h x.x.x.x -uroot --database=abc


you evidently do not have remote rights to database abc.

When you ran

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;


you should now have rights to every database.