MySQL连接错误解决方法

软件环境

• 操作系统：Ubuntu16.04-server
• MySQL版本：5.7.25

故障一

只能通过localhost登录MySQL

1. 报错如下

$mysql -h172.16.0.1 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '172.16.0.1' is not allowed to connect to this MySQL server

2. 解决方法
   此处参考自：https://stackoverflow.com/questions/19101243/error-1130-hy000-host-is-not-allowed-to-connect-to-this-mysql-server

• 首先查看你的root用户允许的主机ip

mysql>SELECT host FROM mysql.user WHERE User = 'root';
+-----------+
| host |
+-----------+
| localhost |
+-----------+
1 row in set (0.24 sec)
一般结果中只有localhost或同时有localhost和127.0.0.1；

• 然后如果你想指定允许某个ip可访问可执行如下命令

CREATE USER 'root'@'ip_address' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip_address';

• 如果想要允许所有ip执行如下命令

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

• 上面两种最后都要flush启用更改

FLUSH PRIVILEGES;

• 然后在执行一次查询会发现结果多了一行“%”，说明更改成功
  +-----------+
  | host |
  +-----------+
  | % |
  +-----------+
  | localhost |
  +-----------+
  1 row in set (0.24 sec)
  再次登录如果仍旧失败，请看故障2

故障二

1. 报错如下

$mysql -h172.16.0.1 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.0.1' (111)

2. 解决方法

• 查看mysql的配置文件

$vim /etc/mysql/mysql.conf.d/mysqld.cnf

• 将下面一行注释或者修改

注释
#bind-address = 127.0.0.1
修改
bind-address = 0.0.0.0

• 重启mysql启用更改

$service mysql restart

再次尝试登录即可成功登录！