Jason Yu
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
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
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 | *13E093B3B83A3A99FDFE993DA 3F0A46E0F9 F74BE | Y | Y |
| 127.0.0.1 | root | *13E093B3B83A3A99FDFE993DA 3F0A46E0F9 F74BE | Y | Y |
| BFDMIS07 | username | *2470C0C06DEE42FD1618BB990 05ADCA2EC9 D1E19 | N | N |
| 10.10.6.9 | username | *2470C0C06DEE42FD1618BB990 05ADCA2EC9 D1E19 | N | N |
| BFDMIS07 | mis | *107F6D76F0CF44552CA76D43F 45FFDFB40A 36900 | N | N |
| BFDMIS07 | root | *13E093B3B83A3A99FDFE993DA 3F0A46E0F9 F74BE | N | N |
| localhost | mis | *BF06A06D69EC935E85659FCDE D1F6A80426 ABD3B | N | N |
| % | mis | *BF06A06D69EC935E85659FCDE D1F6A80426 ABD3B | N | N |
+-----------+----------+-- ---------- ---------- ---------- ---------- -+-------- -----+---- --------+
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 | *13E093B3B83A3A99FDFE993DA
| 127.0.0.1 | root | *13E093B3B83A3A99FDFE993DA
| BFDMIS07 | username | *2470C0C06DEE42FD1618BB990
| 10.10.6.9 | username | *2470C0C06DEE42FD1618BB990
| BFDMIS07 | mis | *107F6D76F0CF44552CA76D43F
| BFDMIS07 | root | *13E093B3B83A3A99FDFE993DA
| localhost | mis | *BF06A06D69EC935E85659FCDE
| % | mis | *BF06A06D69EC935E85659FCDE
+-----------+----------+--
ASKER
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>
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
if I change *.* to User.*, then it works. That means I granted full privileges to this user for user DB.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DaveBaldwin, thank you for your advise, I will take it and follow the rule.
i will close this case.
i will close this case.
Thanks for the points, glad to help.
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.