MySQL root access from all hosts

Question

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...

most likely the MySQL daemon does not listen on 46.x.x.x but on localhost only. Look for bind-address in my.cnf — Maxim Krizhanovsky
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

hovanessyan hovanessyan · Accepted Answer · 2012-06-27T11:53:56

Update:

As mentioned in the comments, since MySql 8 you need to first explicitly create the user, so the command will look like:

CREATE USER 'root'@'%' IDENTIFIED BY 'root'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Original answer:

There's two steps in that process:

a) Grant privileges. As root user execute with this substituting 'password' with your current root password :

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

By default it binds only to localhost, but if you comment the line it binds to all interfaces it finds. Commenting out the line is equivalent to bind-address=*.

To check where mysql service has binded execute as root:

netstat -tupan | grep mysql

Update For Ubuntu 16:

Config file is (now)

/etc/mysql/mysql.conf.d/mysqld.cnf

(at least on standard Ubuntu 16)

MySQL root access from all hosts

10 Answers