7

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.

1 Answer 1

7

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.

2
  • Suggestion #2 produces the following error: ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY' Jul 6, 2013 at 14:18
  • Solution #2 worked for me. Thanks.
    – nickdnk
    Apr 7, 2022 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.