Cannot change root access in MySQL to %

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] =>
[s

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.

StackExchange Database Administrators Q#45806, answer score: 7