ChatGPT解决这个技术问题 Extra ChatGPT

MySQL root access from all hosts

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

can you telnet to the machine on port 3306
most likely the MySQL daemon does not listen on 46.x.x.x but on localhost only. Look for bind-address in my.cnf
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?
possible duplicate of Enable remote MySQL connection

B
Benjamin Loison

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)


Ok, the problem was the binding address. Thank you. @Darhazer Thank you too :)
To query current grants: `SHOW GRANTS FOR 'root'@'%';
To also allow root@% to grant permissions to other users, immediately after step (a) run: GRANT USAGE ON *.* TO 'root'@'%' WITH GRANT OPTION;
Config file is (now) /etc/mysql/mysql.conf.d/mysqld.cnf (at least on standard Ubuntu 16)
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';
H
HK boy

Run the following query:

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

NOTE: Not recommended for production use.


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, 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.
isn't FLUSH PRIVILEGES; also necessary right after ?
this is working for me. try this. update mysql.user set host='%' where user='root'
use double "" instead '' or you will get an error
M
Martin Tournoij

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


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 '*.* TO 'root'@'localhost'' at line 1
Got an error on this too. I omitted the backslash, and it worked.
This answer worked for me. Great!
I
ItalyPaleAle

Sometimes

bind-address = 127.0.0.1

should be

bind-address = *


T
The Big Zank

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.


J
Jason Johnson

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.


f
felipe cardenas

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.


Please add more detail. In what file or framework does that setting need to happen? Please be more specific about the ethX configuration.
i
iHaveacomputer

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.


G
GangaRam Dewasi

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

K
Kieran Foot

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