MySQL root access from all hosts

最新推荐文章于 2025-05-13 09:10:08 发布

wilsonke

最新推荐文章于 2025-05-13 09:10:08 发布

阅读量1.4k

点赞数

分类专栏：综合

综合专栏收录该内容

71 篇文章

订阅专栏

I've installed MySQL server on a remote Ubuntu machine. The root user is defined in the mysql.user table this way:

mysql> SELECT host, user, password FROM user WHERE user = 'root';
+------------------+------+-------------------------------------------+
| host             | user | password                                  |
+------------------+------+-------------------------------------------+
| localhost        | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| ip-10-48-110-188 | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 127.0.0.1        | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| ::1              | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------------------+------+-------------------------------------------+

I can access with user root from the same remote machine command-line interface using the standard mysql client. Now I want to allow root access from every host on the internet, so I tried adding following row (it's an exact duplicate of the first row from previous dump, except for the host column):

mysql> SELECT host, user, password FROM user WHERE host = '%';
+------------------+------+-------------------------------------------+
| host             | user | password                                  |
+------------------+------+-------------------------------------------+
| %                | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
+------------------+------+-------------------------------------------+

But my client on my personal PC continues to tell me (I obscured the server IP):

SQL Error (2003): Can't connect to MySQL server on '46.x.x.x' (10061)

I can't tell if it's a authentication error or a network error. On the server firewall I enabled port 3306/TCP for 0.0.0.0/0, and that's ok for me...

edited Oct 16 '13 at 6:47

asked Jun 27 '12 at 9:34

lorenzo-s
5,760 3 13 44

did you flush privileges? – gunnx Jun 27 '12 at 9:38

@gunnx Yes, I did. – lorenzo-s Jun 27 '12 at 9:42

most likely the MySQL daemon does not listen on 46.x.x.x but on localhost only. Look for bind-address in my.cnf – Darhazer Jun 27 '12 at 10:05

So, the world+dog now have the hash of your root password, the knowledge that root is accessible from any host on the Internet and the first byte of your IP address. You don't think this is just a tiny bit concerning? – eggyal Jun 27 '12 at 10:36

@eggyal No, I don't. – lorenzo-s Jun 27 '12 at 10:52

show 2 more comments

1 Answer

active oldest votes

up vote 19 down vote accepted

There's two steps in that process:

a) Grant privileges. As root user execute:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

b) bind to all addresses:

The easiest way is to comment out the line in your my.cnf file:

#bind-address = 127.0.0.1

and restart mysql

service mysql restart

To check where mysql service has binded execute as root:

netstat -tupan | grep mysql