软件环境
- 操作系统：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
再次尝试登录即可成功登录！