Cannot change root access in MySQL to %

Question

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.

Answer 1

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.

• EastsideDev: Suggestion #2 produces the following error: ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
• nickdnk: Solution #2 worked for me. Thanks.