Link to home
Start Free TrialLog in
Avatar of Jason Yu
Jason YuFlag for United States of America

asked on

can't remote login to MySQL DB using Aqua tool

After I set up a new LAMP server, i can log in locally using root account on the linux box. but when i install Aqua tool and tried to log in from my desktop. It gives me error and doesn't let me log in.

I tried to add the records to user table on MySQL. I added two users accounts and my desktop IP into the User table, and restart the service. But still doesn't work.

please advise.

thank you.
login-error.jpg
user-table.jpg
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

You're trying to login as 'root' and 'root' can only connect from '127.0.0.1' and 'localhost'.  In addition, you should not being using 'root' remotely, that is considered a security problem.  Also, the account you added does not show the complete hostname that you are showing in your error page.  Everything has to match up, the username, the password, and the hostname that you are connecting from.  Though if it is just a user account, 'anyhost' is often used as the hostname.

From http://dev.mysql.com/doc/refman/5.1/en/adding-users.html ...

The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.
Avatar of Jason Yu

ASKER

I run the following command successfully but still couldn't remote access with mis account.

mysql> GRANT ALL PRIVILEGES ON User.* TO 'mis'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)



The user table is as below;

mysql> select Host, User, Password, Select_priv, Grant_priv from user;
+-----------+----------+-------------------------------------------+-------------+------------+
| Host      | User     | Password                                  | Select_priv | Grant_priv |
+-----------+----------+-------------------------------------------+-------------+------------+
| localhost | root     | *13E093B3B83A3A99FDFE993DA3F0A46E0F9F74BE | Y           | Y          |
| 127.0.0.1 | root     | *13E093B3B83A3A99FDFE993DA3F0A46E0F9F74BE | Y           | Y          |
| BFDMIS07  | username | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N           | N          |
| 10.10.6.9 | username | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N           | N          |
| BFDMIS07  | mis      | *107F6D76F0CF44552CA76D43F45FFDFB40A36900 | N           | N          |
| BFDMIS07  | root     | *13E093B3B83A3A99FDFE993DA3F0A46E0F9F74BE | N           | N          |
| localhost | mis      | *BF06A06D69EC935E85659FCDED1F6A80426ABD3B | N           | N          |
| %         | mis      | *BF06A06D69EC935E85659FCDED1F6A80426ABD3B | N           | N          |
+-----------+----------+-------------------------------------------+-------------+------------+
got this error:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'minkaweb'@'localhost' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql>
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, i indeed followed that article and created that user, but i couldn't assign full privileges to that user. Could you help?

if I change *.* to User.*, then it works. That means I granted full privileges to this user for user DB.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Mysql:

Client API version      5.5.29

OS: Cent OS newest one.


mysql

MySQL Support      enabled
Active Persistent Links      0
Active Links      0
Client API version      5.5.29
MYSQL_MODULE_TYPE      external
MYSQL_SOCKET      /var/lib/mysql/mysql.sock
MYSQL_INCLUDE      -I/usr/include/mysql
MYSQL_LIBS      -L/usr/lib64/mysql -lmysqlclient
I got it partially solved. If I use mis account to connect to the information_shema database, the server let me connect. but if I try to connect to User db, it failed.

I guess User database is part of the meta db and doesn't permit ordinary user connect remotely. If I use root and login locally, I can modify this user database.

My two questions:

Is it possible to permit mis user to access user db remotely?

How can i use " GRANT ALL PRIVILEGES ON *.* TO 'minkaweb'@'localhost' WITH GRANT OPTION;" to enable full access for other users?





thank you.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
DaveBaldwin, thank you for your advise, I will take it and follow the rule.
i will close this case.
Thanks for the points, glad to help.