MacBook安装MySQL登录问题

一、问题1

1. MacBook Pro M4通过brew install mysql完成后本地不能连接。默认MySQL安装后创建了root@localhost账号，无密码。但是本地使用mysql -uroot连接后报异常ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)。

 - brew services start mysql

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -u root

To start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql
mysql -uroot

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

homebrew 默认安装的是9.0.1版本。我记得上一台Mac电脑安装完MySQL后，“系统设置”里面专门有个MySQL图标可以控制启停，初始化密码设置等。新版本的找不到了，只在“通用“ -> “登录项和扩展”中找到了是否允许MySQL在后台运行的控制。

解决方法

废话少说，直接说解决方案。

brew services stop mysql
pkill mysqld
rm -rf /opt/homebrew/var/mysql 
brew postinstall mysql
brew services restart mysql

mysql -u root

关键的是第3步rm -rf /opt/homebrew/var/mysql ，也就是清理MySQL的数据文件目录。尝试brew uninstall mysql后发现该目录仍然存在，猜测是该数据目录对应的数据库中设置过密码，需要安装时先清理掉。

安装完记得通过mysql_secure_installation设置root密码。

问题二：MySQL 9.0.1客户端连MySQL 5.7服务器报认证异常

异常信息如下。问题比较直观：MySQL 9不支持mysql_native_password密码认证插件(该插件已从MySQL 9中去掉，MySQL 8.0中已标记为deprecated了）

ERROR 2059 (HY000): Authentication plugin 'mysql_native_password' cannot be loaded: dlopen(/opt/homebrew/Cellar/mysql/9.0.1_8/lib/plugin/mysql_native_password.so, 0x0002): tried: '/opt/homebrew/Cellar/mysql/9.0.1_8/lib/plugin/mysql_native_password.so' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/opt/homebrew/Cellar/mysql/9.0.1_8/lib/plugin/mysql_native_password.so' (no such file), '/opt/homebrew/Cellar/mysql/9.0.1_8/lib/plugin/mysql_native_password.so' (no such file)

解决方法

最快速的方法是把MySQL版本从9.0.1降为8.4。操作如下：

brew uninstall mysql
brew install [email protected]
ln -s /opt/homebrew/opt/[email protected] /opt/homebrew/opt/mysql
echo 'export PATH="/opt/homebrew/opt/mysql/bin:$PATH"' >> ~/.zshrc

mysql -hXX -PXX -uXX -p

• 注意重装[email protected]时可能又会遇到上面的问题一。要么输入前面数据库的密码，要么使用问题一的解决方法再操作一遍来解决。