mac用brew安装mysql报错1045(28000)

操作环境

MacOs Catalina Version10.15.7

通过brew安装mysql (stable 8.0.25)

报错信息1045 (28000)

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

问题所在

通过网上查找资料，HomeBrew的成员Mike McQuaid 解释：

You will have had an existing database with an existing password there.

解决方法

删库重启

To remove all your data and start again run:
brew services stop mysql         #关闭mysql服务
pkill mysqld         #杀死所有mysql进程
rm -rf /usr/local/var/mysql/         #注意:这将删除您现有的数据库
brew postinstall mysql
brew services restart mysql
mysql -uroot

# 关闭mysql服务
**@** ** % brew services stop mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
# 杀死所有mysql进程
**@** ** % pkill mysqld
# 注意:这将删除您现有的数据库
**@** ** % rm -rf /usr/local/var/mysql/
# brew再来安一遍mysql
**@** ** %  brew postinstall mysql
==> Postinstalling mysql
==> /usr/local/Cellar/mysql/8.0.25_1/bin/mysqld --initialize-insecure --user=mac --basedir=/usr/local/Cellar/mysql/8.0.25_1 --datadir=/usr/loca
# brew 从新启动mysql
**@** ** % brew services restart mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
# root不要密码进
**@** ** % mysql -uroot
# 进来了哇🐸
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is **
Server version: 8.0.25 Homebrew

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

通过上述操作之后就成功进入了mysql

获取mysql信息

退出，brew info mysql 获取mysql信息

** @ **  % brew info mysql
mysql: stable 8.0.25 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
Conflicts with:
  mariadb (because mysql, mariadb, and percona install the same binaries)
  percona-server (because mysql, mariadb, and percona install the same binaries)
/usr/local/Cellar/mysql/8.0.25_1 (300 files, 293.9MB) *
  Poured from bottle on 2021-0*-** at hour:minite:second
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/mysql.rb
License: GPL-2.0-only with Universal-FOSS-exception-1.0
==> Dependencies
Build: cmake ✘, pkg-config ✘
Required: icu4c ✔, libevent ✔, lz4 ✔, openssl@1.1 ✔, protobuf ✔, zstd ✔
==> Caveats
We\'ve installed your MySQL database without a root password.
# 我们在没有root密码的情况下安装了MySQL数据库。
To secure it run:
# 运行如下bash代码来保护它：
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default
# MySQL被配置为默认情况下只允许本地主机的连接

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
# 现在启动mysql，并在登录时重新启动
  brew services start mysql
Or, if you don't want/need a background service you can just run:
# 或者，如果您不想要(需要)后台服务，您只需运行：
  mysql.server start

为root设置登陆密码

根据提示，运行mysql_secure_installation，页面如下：

**@** ** % mysql_secure_installation

Securing the MySQL server deployment.
# 确保MySQL服务器部署安全。

Connecting to MySQL using a blank password.
# 使用一个空密码连接到MySQL。

VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security.
# 验证密码组件可用于测试密码并提高安全保护。
It checks the strength of passwordand allows the users to set only those passwords which are secure enough. 
# 该组件会检查密码强度，达到让用户只能设置一个足够安全的密码。
Would you like to setup VALIDATE PASSWORD component?
# 您想要安装验证密码组件吗？
# 我就不安了

Press y|Y for Yes, any other key for No: 
# 键入y｜Y代表Yes，其他键都代表No
# 我直接就no了

设置密码

Please set the password for root here.
# 请为root设置密码

New password: 

Re-enter new password: 

–skip-grant-tables

结果…由于开始不知道怎么解决，胡乱设置了**–skip-grant-tables**，于是就报错了：

 ... Failed! Error: The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

额，再继续找解决方法！！！！终于，在StackChange找到了！

需要重新加载权限表flush privileges，于是进入mysql

mysql> flush privileges;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';

mysql> flush privileges;

再退出，从新运行mysql_secure_installation
…
然后继续上面👆1、是否需要验证密码组件，2、重设密码

接下来是问删不删这个匿名用户

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. 
# 默认情况下，MySQL安装有一个匿名用户，允许任何人登录MySQL，而无需为它们创建用户帐户。
This is intended only for testing, and to make the installation go a bit smoother.
# 这个匿名用户仅用于测试，并使安装更加顺利。
You should remove them before moving into a production environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
# 这里我是删除了这个匿名用户
Success.

继续，问远程登陆打开不，

Normally, root should only be allowed to connect from 'localhost'. 
# 通常，root用户只被允许从'localhost'连接。
This ensures that someone cannot guess at the root password from the network.
# 这可以确保其他人无法通过猜出root用户的密码通过互联网远程连接

# 不允许远程登录mysql？
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : no
# 我是no，允许✅远程登陆访问的

紧接着，问test数据库删不删：

By default, MySQL comes with a database named 'test' that anyone can access. 
# 默认情况下，Mysql有一个名为‘test'并且任何人都可访问的数据库，
This is also intended only for testing, and should be removed before moving into a production environment.
# 当然它仅仅用于测试。。。总之，删除！


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
# 我是删除了的
 - Dropping test database...
Success.

 - Removing privileges on test database...
 # 删除test数据库后，关于该数据库的权限也要被删除
Success.

最后，重新加载权限表

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
# 重新加载权限表，确保到目前为止所做的所有更改都立即生效。

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
# 当然要reload，立即生效啊

All done! 
# 当当当当，完成了！！！

接下来，就可以登陆了！

**@** ** % mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is *u guess*
Server version: 8.0.25 Homebrew

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>