This is a new MySQL Server install. I created my root password as part of the setup (on Centos 6.4).
When I connect to the server through a terminal, I can connect to MySQL and issue commands, using my root password.
select current_user;
gives me:
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
If I do:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxxxx' WITH GRANT OPTION
I get:
Query OK, 0 rows affected (0.00 sec)
But when I do:
SHOW GRANTS;
Here's what I get:
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
Am I not supposed to see another line for root@% in addition to root@localhost?
The real issue I'm having is that I can't connect to MySQL from outside of localhost (as in, I'm logged in using a terminal session), and if the MySQL server is not giving root universal access (root@%), then that would explain the problem. When I try to connect using PHP (from my local MAC), the following is the returned MySQLi object:
mysqli Object
(
[affected_rows] =>
[client_info] =>
[client_version] => 50008
[connect_errno] => 2002
[connect_error] => Connection refused
[errno] =>
[error] =>
[field_count] =>
[host_info] =>
[info] =>
[insert_id] =>
[server_info] =>
[server_version] =>
[stat] =>
[sqlstate] =>
[protocol_version] =>
[thread_id] =>
[warning_count] =>
)
I realize that granting root access from % is not a great idea, but at this point, I'm trying to figure out why I can't connect to the MySQL server, and when I solve that, I will restrict access.
Best Answer
SHOW GRANTS
only gives you back whatever you are connected as, which wasroot@localhost
.root@localhost
androot@'%'
and completely different users. Just doroot@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 asroot@localhost
. This is true even if you attempted to connect using127.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
instead of having a remote root.
SUGGESTION #2
If you really want the remote root, here is what you do
That will do what you want, but remote root is not recommended.
SUGGESTION #3
Since
root@'%'
already exists, then remove the root@localhost.This will leave
root@'%'
as the only root user, but you must connect explicitly with TCP/IP.