远程连接 MySQL 数据库(Ubuntu)

1. MySQL本地可以登录，但是不能远程登录。

[[email protected] ~]# mysql -h 39.105.76.170 -uroot -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '39.105.76.170' (111)

2. MySQL服务器是可以ping通的

[[email protected] ~]# ping 39.105.76.170
PING 39.105.76.170 (39.105.76.170) 56(84) bytes of data.
64 bytes from 39.105.76.170: icmp_seq=1 ttl=64 time=0.242 ms
64 bytes from 39.105.76.170: icmp_seq=2 ttl=64 time=0.262 ms
^C
--- 39.105.76.170 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.242/0.252/0.262/0.010 ms

3. MySQL的端口是默认的

[[email protected] ~]# netstat -nlp|grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      24061/mysqld    

4.解决办法

将mysql配置文件中的bind-address = 127.0.0.1注释掉

[[email protected] ~]# vi /etc/mysql/mysql.conf.d/mysqld.cnf 


 Instead of skip-networking the default is now to listen only on
 localhost which is more compatible and is not less secure.
bind-address           = 127.0.0.1

重启MySQL

[[email protected] ~]:~# sudo service mysql restart

重新远程连接

[[email protected] ~]# mysql -h39.105.76.170 -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.11-0kord6 (Ubuntu)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>