【教程】Homebrew安装MySQL 8 & 设置root密码 & 修改MySQL认证插件

✨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; # 刷新权限

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

✨修改MySQL认证插件

根据上述测试可知

MySQL认证插件实际上由msyql.user.plugin字段记录

因此也可以通过SQL语句查看/修改认证插件

经过测试通过以下SQL实际上只更改了msyql.user.plugin字段记录

并没有更改实际使用的认证插件

SELECT plugin FROM mysql.user WHERE user='root'; # 查看认证插件
UPDATE mysql.user SET plugin='caching_sha2_password' WHERE user='root'; # 更新plugin字段记录为caching_sha2_password
UPDATE mysql.user SET plugin='mysql_native_password' WHERE user='root'; # 更新plugin字段记录为mysql_native_password

如果需要更改请使用以下SQL

为避免修改认证插件后原先密码无法和authentication_string字段校验一致导致无法连接到数据库

强烈建议先重置root密码为空

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY ''; FLUSH PRIVILEGES; # 修改认证插件为caching_sha2_password并重置root密码为空
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ''; FLUSH PRIVILEGES; # 修改认证插件为mysql_native_password并重置root密码为空

✨参考及引用

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://www.cnblogs.com/Flat-White/p/18166721

posted @ 2024-04-29 21:55 双份浓缩馥芮白阅读(3198) 评论(0) 编辑收藏举报

刷新页面返回顶部

双份浓缩馥芮白