软件环境
操作系统:Ubuntu16.04-server
MySQL版本:5.7.25
故障一
只能通过localhost登录MySQL
报错如下
$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
首先查看你的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
故障二
报错如下
$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)
解决方法
查看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
再次尝试登录即可成功登录!