MySQL root access from all hosts

authenticationMySQL

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…

Best Answer

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)

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Mysql – Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server

Possibly a security precaution. You could try adding a new administrator account:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;

Although as Pascal and others have noted it's not a great idea to have a user with this kind of access open to any IP. If you need an administrative user, use root, and leave it on localhost. For any other action specify exactly the privileges you need and limit the accessibility of the user as Pascal has suggest below.

Edit:

From the MySQL FAQ:

If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this allows you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 5.4.4, “Access Control, Stage 1: Connection Verification”.

Best Answer

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Mysql – Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server

Related Topic