191

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

lorenzo-s
  • 16,603
  • 15
  • 54
  • 86
  • can you telnet to the machine on port 3306 – mihaisimi Jun 27 '12 at 09:56
  • 3
    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 Jun 27 '12 at 10:05
  • 1
    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
  • possible duplicate of [Enable remote MySQL connection](http://stackoverflow.com/questions/8380797/enable-remote-mysql-connection) – user Mar 10 '14 at 03:34

10 Answers10

443

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 'password'; 
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)

Omry Yadan
  • 31,280
  • 18
  • 64
  • 87
hovanessyan
  • 30,580
  • 6
  • 55
  • 83
  • 5
    Ok, the problem was the binding address. Thank you. @Darhazer Thank you too :) – lorenzo-s Jun 27 '12 at 12:52
  • 3
    To query current grants: `SHOW GRANTS FOR 'root'@'%'; – robsch May 26 '15 at 07:23
  • 5
    To also allow root@% to grant permissions to other users, immediately after step (a) run: GRANT USAGE ON \*.\* TO 'root'@'%' WITH GRANT OPTION; – Caleb Jul 01 '15 at 23:17
  • 1
    Config file is (now) /etc/mysql/mysql.conf.d/mysqld.cnf (at least on standard Ubuntu 16) – jgp Jul 13 '16 at 21:49
  • 2
    I just needed to do this, to be able to remotely connect to the server remotely: `USE mysql; UPDATE user SET Grant_priv='Y' WHERE user='root';` – Sviderskiy Dmitriy Oct 13 '16 at 06:45
  • where to find `my.cnf` file ? – SimpleGuy Mar 27 '17 at 09:36
  • check the package description for your distribution or try to use "find" in /etc with the appropriate parameters – hovanessyan Mar 28 '17 at 10:15
  • 1
    Recommend to use: FLUSH PRIVILEGES; – felixmpa May 15 '17 at 21:03
  • Oh, my problem is on where ubuntu 16 configuration file located. thanks – Ramin Dec 03 '17 at 07:21
  • 1
    'password' seems not to be a selector! Only when used the root password (e.g. 1234) in there, I could finally connect. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION; – Michael B. Dec 11 '17 at 20:25
  • Hello hovanessyan, is it safe to comment "bind-address" attribute? – learner Aug 24 '18 at 12:28
  • @learner the bind-address property defines the network address to which the server will bind to. In the case of 127.0.0.1, which is the loopback network interface, the server will bind to localhost only. In this context I believe it is safe, yes. – hovanessyan Aug 24 '18 at 12:59
  • 1
    One should know that you need to replace 'password' for the current root password or you'll change it...I accidentally changed my root password to `password` in a answer that exposes root user to the internet... – Freedo Jun 25 '19 at 19:29
  • thank you @Caleb for the bit about needing to allow grant privileges – kjones Dec 31 '19 at 20:25
  • 1
    Starting with MySQL 8.0 and up, you can no longer (implicitly) create a user with the `GRANT` command. Use `CREATE USER` followed by `GRANT`. For example, `CREATE USER 'root'@'%' IDENTIFIED BY 'root'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;` – Arseniy Banayev May 03 '20 at 17:36
  • It might look like that the command GRANT ALL PRIVILEGES ON . TO does not work for a root user or changing password, eg. just creating a new all-hosts user, since the command creates a user or changes the password, but for the root user changes (or creates) the password only for the all-hosts " type of user not for the localhost version, and it cannot reset the account to an empty password, to be passwordless. Whereas when not creating/changing the password for a non-root user,it changes it both for the localhost and all-hosts version.Password is possible to remove by manually emptying the col – FantomX1 Sep 27 '20 at 16:19
  • More over the clause 'Identified by' seems to serves as both, the selector and a password setter, since if you use it for some non existing user, it will create it, but if you use it for an existing user but with an incorrect password, then it won't change anything – FantomX1 Oct 09 '20 at 11:20
  • problem running first step: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'xxxxxxxxxxxx'' at line 1 USE INSTEAD THIS COMMAND: GRANT ALL ON root.* TO 'root'@'localhost'; – Fernando Torres Jun 15 '21 at 20:21
49

Run the following query:

use mysql;
update user set host='%' where host='localhost'

NOTE: Not recommended for production use.

HK boy
  • 1,398
  • 11
  • 17
  • 25
Babu James
  • 2,740
  • 4
  • 33
  • 50
  • 3
    This is not the perfect solution. It may work. For me it produced an error: ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY' – Scriptlabs Nov 16 '15 at 12:51
  • 2
    @Scriptlabs, chances are there are more than one entries in user table with a host of 'localhost'. And more than one row with root, including root.localhost, root.127.0.0.1, root.::1. So you are better off adding a separate user rather than updating, which is why I am downvoting this answer. – Mike Purcell Apr 13 '16 at 15:12
  • 7
    isn't FLUSH PRIVILEGES; also necessary right after ? – Nom1fan Dec 27 '17 at 19:48
  • 7
    this is working for me. try this. `update mysql.user set host='%' where user='root'` – nur zazin Dec 20 '19 at 17:02
  • use double "" instead '' or you will get an error – Fernando Torres Jun 15 '21 at 20:25
  • you have to restart mysql or the server for the changes to take effect – Barungi Stephen Oct 04 '22 at 05:58
  • from root I received the following error: ```View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them``` – Alexey Sh. Apr 14 '23 at 20:22
26

MYSQL 8.0 - open mysql command line client

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

use mysql

UPDATE mysql.user SET host='%' WHERE user='root';

Restart mysql service

Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146
user10580057
  • 261
  • 3
  • 3
11

Sometimes

bind-address = 127.0.0.1

should be

bind-address = *

ItalyPaleAle
  • 7,185
  • 6
  • 42
  • 69
user2839051
  • 119
  • 1
  • 2
5

MariaDB running on Raspbian - the file containing bind-address is hard to pinpoint. MariaDB have some not-very-helpful-info on the subject.

I used 

# sudo grep -R bind-address /etc

to locate where the damn thing is.

I also had to set the privileges and hosts in the mysql like everyone above pointed out.

And also had some fun time opening the 3306 port for remote connections to my Raspberry Pi - finally used iptables-persistent.

All works great now.

The Big Zank
  • 51
  • 1
  • 1
2

I'm using AWS LightSail and for my instance to work, I had to change:

bind-address = 127.0.0.1

to

bind-address = <Private IP Assigned by Amazon>

Then I was able to connect remotely.

Jason Johnson
  • 31
  • 3
1

if you have many networks attached to you OS, yo must especify one of this network in the bind-addres from my.conf file. an example:

[mysqld]
bind-address = 127.100.10.234

this ip is from a ethX configuration.

felipe cardenas
  • 81
  • 4
  • 1
    Please add more detail. In what file or framework does that setting need to happen? Please be more specific about the ethX configuration. – BPS Apr 21 '17 at 20:31
1

In my case the "bind-address" setting was the problem. Commenting this setting in my.cnf did not help, because in my case mysql set the default to 127.0.0.1 for some reason.

To verify what setting MySql is currently using, open the command line on your local box:

mysql -h localhost -u myname -pmypass mydb

Read out the current setting:

Show variables where variable_name like "bind%"

You should see 0.0.0.0 here if you want to allow access from all hosts. If this is not the case, edit your /etc/mysql/my.cnf and set bind-address under the [mysqld] section:

bind-address=0.0.0.0

Finally restart your MySql server to pick up the new setting:

sudo service mysql restart

Try again and check if the new setting has been picked up.

iHaveacomputer
  • 1,427
  • 4
  • 14
  • 30
1

Update the bind-address = 0.0.0.0 in the /etc/mysql/mysql.conf.d/mysqld.cnf and from the mysql command line allow the root user to connect from any Ip.

Below was the only commands worked for mysql-8.0 as other were failing with error syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'abcd'' at line 1

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
UPDATE mysql.user SET host='%' WHERE user='root';

Restart the mysql client

sudo service mysql restart
GangaRam Dewasi
  • 631
  • 7
  • 11
-2

mysql_update is what you need.

I don't know why anyone would follow the more complex ways of correcting this issue, when MySql graciously built a tool that already does this...

Kieran Foot
  • 706
  • 5
  • 11