✨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
✨修改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://stackoverflow.com/questions/52320576/in-mysql-server-8-0-the-password-function-not-working
⭐转载请注明出处
本文作者：双份浓缩馥芮白
原文链接：https://www.cnblogs.com/Flat-White/p/18166721
版权所有，如需转载请注明出处。