Run the MySQL Server Process Properly
Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:
$> mysql
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
$> mysql
ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)
Specify the Correct Port
It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port number, or a --socket option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can use this command:
$> netstat -ln | grep mysql
Firewall
If you only plan to access the database server from one specific machine, you can grant that machine exclusive permission to connect to the database remotely with the following command. Make sure to replace remote_IP_address with the actual IP address of the machine you plan to connect with:
$ sudo ufw allow from <remote_IP_address> to any port 3306
$ sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
# allow anyone
$ sudo ufw allow 3306
Listen to All Interfaces
Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces. If the server was started with the skip_networking system variable enabled, no TCP/IP connections are accepted. If the server was started with the bind_address system variable set to 127.0.0.1, it listens for TCP/IP connections only locally on the loopback interface and does not accept remote connections.
如果得到
$ netstat -an | grep LISTEN | grep 3306
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
则说明3306只绑定到了 127.0.0.1。
如果3306绑定到了所有interfaces，则为
$ netstat -an | grep LISTEN | grep 3306
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
The location of the MySQL configuration file differs depending on the distribution. In Ubuntu and Debian the file is located at /etc/mysql/mysql.conf.d/mysqld.cnf, while in Red Hat based distributions such as CentOS, the file is located at /etc/my.cnf.
By default, the value is set to 127.0.0.1 (listens only in localhost).
In this example, we’ll set the MySQL server to listen on all IPv4 interfaces by changing the value to 0.0.0.0
bind-address = 0.0.0.0
# skip-networking
For macOS
$ sudo vim /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
# bind-address = 127.0.0.1
# mysqlx-bind-address = 127.0.0.1
To restart the MySQL service on Debian or Ubuntu, type:
$ sudo systemctl restart mysql
Grant Table Setting
The grant tables must be properly set up so that the server can use them for access control. For some distribution types (such as binary distributions on Windows, or RPM and DEB distributions on Linux), the installation process initializes the MySQL data directory, including the mysql system database containing the grant tables. For distributions that do not do this, you must initialize the data directory manually. For details, see Section 2.10, “Postinstallation Setup and Testing”.
What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;
Reference
- https://dev.mysql.com/doc/refman/8.0/en/problems-connecting.html
- https://stackoverflow.com/questions/14779104/mysql-how-to-allow-remote-connection-to-mysql
- https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql
- https://stackoverflow.com/questions/50409788/mysql-8-create-new-user-with-password-not-working