✨Homebrew安装MySQL 8

brew install mysql

==> Caveats
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

Homebrew安装的MySQL默认情况下没有root密码

不填写密码的情况下直接可以通过Navicat连接（需要先开启MySQL服务）

如需通过Homebrew安装MySQ L 5.7 可参考

https://www.cnblogs.com/Flat-White/p/15826760.html

✨设置root密码

启动MySQL

brew services start mysql

连接MySQL

mysql -u root

设置root密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
# ALTER USER 'root'@'localhost' IDENTIFIED BY '需要设置的密码';
FLUSH PRIVILEGES; # 刷新权限

or

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
# ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '需要设置的密码';
FLUSH PRIVILEGES; # 刷新权限

以下内容仅仅花费一个下午反复测试才弄清楚缘由（苦笑.jpg）

注意：经过测试以下设置root密码SQL语句对于MySQL 8.3.0均不适用

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
UPDATE mysql.user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('root'))))) WHERE user='root';

• 第一条是因为PASSWORD()函数在MySQL 8中已弃用，并且root密码不再由PASSWORD字段存储

• 第二条在MySQL认证插件不为mysql_native_password时不可用

在 2024-4-29 时通过brew安装的MySQL 8.3.0默认认证插件为caching_sha2_password

可以通过以下SQL查询当前MySQL使用的认证插件

SELECT plugin FROM mysql.user WHERE user='root';

经过测试如果SQL执行顺序如下

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
SELECT authentication_string FROM mysql.user WHERE user='root';

ALTER USER 'root'@'localhost' IDENTIFIED BY '';
SELECT authentication_string FROM mysql.user WHERE user='root';

UPDATE mysql.user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('root'))))) WHERE user='root';
SELECT authentication_string FROM mysql.user WHERE user='root';

FLUSH PRIVILEGES;

由于第一条SQL已经更改MySQL认证插件为mysql_native_password

此时CONCAT('*', UPPER(SHA1(UNHEX(SHA1('root')))))与第一条SQL更新的authentication_string字段值实际上一致

因此同样可以通过“root”连接到数据库

✨重置root密码为空

如果还能连接上MySQL

可以选择如下三条SQL语句中任意一句重置密码为空

重置完密码后记得刷新权限

ALTER USER 'root'@'localhost' IDENTIFIED BY '';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
UPDATE mysql.user SET authentication_string='' WHERE user='root' AND host = 'localhost';
FLUSH PRIVILEGES; # 刷新权限

如果不能连接上MySQL

brew services stop mysql
brew uninstall mysql
rm -rf /opt/homebrew/var/mysql # 删除MySQL数据目录
brew install mysql

✨参考及引用

https://stackoverflow.com/questions/7534056/mysql-root-password-change

https://stackoverflow.com/questions/52320576/in-mysql-server-8-0-the-password-function-not-working

https://stackoverflow.com/questions/52320576/in-mysql-server-8-0-the-password-function-not-working

⭐转载请注明出处

本文作者：双份浓缩馥芮白

原文链接：https://www.cnblogs.com/Flat-White/p/18166721

版权所有，如需转载请注明出处。