SHOW GRANTS only gives you back whatever you are connected as, which was root@localhost.
root@localhost and root@'%' and completely different users. Just do
SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@'%';
root@localhost lets you connect from the DB Server via mysql.sock (the socket file)root@'%' lets you connect via TCP/IP, but you must explicitly connect with that protocol. Otherwise, mysqld connects you as root@localhost. This is true even if you attempted to connect using 127.0.0.1.
SUGGESTION #1
For the sake of security, you should use netblocks instead of '%'. For example, if you web servers at 10.1.2.20, 10.1.2.30, and 10.1.2.40, you should create
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.2.%'
IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION;
instead of having a remote root.
SUGGESTION #2
If you really want the remote root, here is what you do
UPDATE mysql.user SET host='%'
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
That will do what you want, but remote root is not recommended.
SUGGESTION #3
Since root@'%' already exists, then remove the root@localhost.
DELETE FROM mysql.user WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
This will leave root@'%' as the only root user, but you must connect explicitly with TCP/IP.
Great, here is the solution I found. I'm chosing to post it here instead of just removing the question since I believe it may be relevant for further reference.
Basically the idea is to leave mysql alone but to tell the server to resolve the ip. In the case of my centos6 configuration it means editing the /etc/hosts file and appending a my.servers.ip.address localhost line into it.
Hope it helped!
S.
Best Answer
| root | % |- This is the root user from any computer| root | 127.0.0.1 |- This is the root user for localhost without resolved IP for IPv4| root | ::1 |- This is the root user for localhost without resolved IP for IPv6| root | localhost |- This is the root user for localhost with resolved IP| root | mysrverhostname |- This is the root user if you connect from the localhost to its externel IP.You could the delete the first one if you don't like to connect from another computer to this server as root.
All other root users are useful, because you can do the follow types of mysql connections:
and if I remember correctly the "localhost" user get used if you connect using a socket connection.