Mac Homebrew安装的MySQL无法远程登录的解决

对于Mac上Homebrew安装的MySQL，默认情况下只能使用本地登录。

而使用其它主机远程登录Mac上的MySQL则会被拒绝访问。

下面修改MySQL的相关配置并使其能被远程主机访问。

1. 登录MySQL

?

1	mysql -u root -p -D mysql

2. 修改user表中root用户的Host值

?

1	update user set host= '%' where user = 'root' ;

查看下修改情况：

?

1 2 3 4 5 6 7 8 9 10

mysql> select user ,host from user ; + ------------------+-----------+ | user             | host      | + ------------------+-----------+ | root             | %         | | mysql.infoschema | localhost | | mysql.session    | localhost | | mysql.sys        | localhost | + ------------------+-----------+ 4 rows in set (0.00 sec)

3. 刷新权限

?

1	flush privileges ;

4. 退出MySQL

?

1

exit

5. 修改MySQL服务绑定的IP

对于Homebrew安装的MySQL，默认的配置文件路径是/usr/local/etc/my.cnf：

?

1 2 3 4 5

# Default Homebrew MySQL server config [mysqld] # Only allow connections from localhost bind-address = 127.0.0.1 mysqlx-bind-address = 127.0.0.1

将bind-address值修改为0.0.0.0：

?

1 2 3 4 5

# Default Homebrew MySQL server config [mysqld] # Only allow connections from localhost bind-address = 0.0.0.0 mysqlx-bind-address = 127.0.0.1

6. 重启MySQL服务

?

1	brew services restart mysql

如果brew重启失败，有以下两种解决方案：

进入/usr/local/Cellar/mysql/<version>/bin目录下，使用mysql.server restart命令重启MySQL。注意"version"是你Mac上安装MySQL的版本号，请根据实际安装版本号来替换

可以选择重启Mac来达到重启MySQL服务的目的。重启Mac后，如果没有设置MySQL服务自启动，需要手动拉起MySQL服务：mysql.server start

验证

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$ mysql -u root -p -h 192.168.0.100 Enter password : Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.21 Homebrew   Copyright (c) 2000, 2020, Oracle and / or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and / or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql>

远程登录成功。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/TCatTime/article/details/112501966