MySQL MySQL自动生成临时密码后无法访问

Unable to access MySQL after it automatically generated a temporary password

提问by Cheetah Felidae

I have erased and installed OSX 10.11 El Capitanand I have been following through this tutorialto get MySQLup and running on the new OS X. The first step was to download MySQL For Mac OS X 10.9 (x86, 64-bit), DMG Archive(works on 10.11, they recommended in the tutorial). While I were finishing installing the MySQL, I got the message saying that :

我已经删除，安装OSX 10.11 El Capitan和我一直在下面通过本教程来获得MySQL并在新的OS X上运行的第一步是下载的MySQL对于Mac OS X 10.9（86，64位），DMG归档在10.11（作品，他们在教程中推荐）。当我完成安装 MySQL 时，我收到消息说：

2015-10-25T02:10:54.549219Z 1 [Note] A temporary password is generated for root@localhost: R>gFySuiu23U

If you lose this password, please consult the section How to Reset the Root Password in the MySQL reference manual.

That was weird, I have never seen that kind of message. After that, I started MySQL via the Preference Pane and then use /usr/local/mysql/bin/mysql -vcommand on the terminal for another step. I got an error message saying that :

太奇怪了，我从来没有见过这样的消息。之后，我通过 Preference Pane 启动 MySQL，然后/usr/local/mysql/bin/mysql -v在终端上使用command 进行另一个步骤。我收到一条错误消息说：

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

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

I have also tried to access database through Sequel Prousing root as username and blank password, I got access denied message saying that :

我还尝试通过Sequel Pro使用 root 作为用户名和空白密码来访问数据库，我收到了访问被拒绝的消息：

Unable to connect to host 127.0.0.1 because access was denied.

Double-check your username and password and ensure that access from your current location is permitted.

MySQL said: Access denied for user 'root'@'localhost' (using password: NO)

Okay, I also tried this again using root as a username but 'R>gFySuiu23U' as a password (which was generated from MySQL). I got connection failed message saying that :

好的，我也再次尝试使用 root 作为用户名，但使用“R>gFySuiu23U”作为密码（从 MySQL 生成）。我收到连接失败消息说：

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Your password has expired. To log in you must change it using a client that supports expired passwords.

How could I solve this problem? I remember that MySQL has never got automatically generated a temporary password like this, hasn't it ?

我怎么能解决这个问题？我记得 MySQL 从来没有像这样自动生成临时密码，不是吗？

回答by Kumar Nitin

Try this:

尝试这个：

mysql -u root -h 127.0.0.1 -p
Enter password: (enter the random password here)

Ref:https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html

参考：https: //dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html

Following this, you may reset your password using ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

在此之后，您可以使用以下方法重置密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

回答by Lesley

This is what worked for me on OS X Yosemite running MySql v5.7 (installed from the .dmg).

这就是在运行 MySql v5.7（从 .dmg 安装）的 OS X Yosemite 上对我有用的方法。

cd /usr/local/mysql/bin
./mysql -u root -p --connect-expired-password

(Enter the temporary password generated by the installer.)

（输入安装程序生成的临时密码。）

This gets you into sandbox modeand mysql>prompt. Then set desired root password with SET PASSWORD:

这会让您进入沙盒模式并mysql>提示。然后使用SET PASSWORD设置所需的 root 密码：

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mySuperSecretPassword');

回答by Anton Nikiforov

Now that the password MySQL had generated is expired, the problem is reduced to getting this password to work again (1) or generate a new one (2). This can be accomplished by running MySQL with the skip-grant-tables option which would make it ignore the access rights:

既然 MySQL 生成的密码已过期，问题就归结为让此密码再次生效 (1) 或生成新密码 (2)。这可以通过使用 skip-grant-tables 选项运行 MySQL 来完成，这将使其忽略访问权限：

1. Stop your MySQL server.

2. Add the below at the end of the [mysqld] section of my.cnf file and save it.

   skip-grant-tables

3. Start MySQL server.

4. In terminal, type

   mysql -u root -p

1. 停止你的 MySQL 服务器。

2. 在 my.cnf 文件的 [mysqld] 部分的末尾添加以下内容并保存。

   skip-grant-tables

3. 启动 MySQL 服务器。

4. 在终端中，输入

   mysql -u root -p

to get into MySQL command prompt.

进入 MySQL 命令提示符。

1. In the command prompt, type

   USE mysql;

5. 在命令提示符下，键入

   USE mysql;

to get into the mysql database where it keeps database users.

进入保存数据库用户的 mysql 数据库。

1. Type

   UPDATE user SET password_expired = 'N' WHERE User = 'root';

6. 类型

   UPDATE user SET password_expired = 'N' WHERE User = 'root';

to let MySQL know the password is not expired (1) or

让 MySQL 知道密码未过期 (1) 或

UPDATE user SET authentication_string = PASSWORD('YourNewPassword'), password_expired = 'N' WHERE User = 'root';

to assign a new password YourNewPassword to root (2).

将新密码 YourNewPassword 分配给 root (2)。

回答by Marcelo

Doing these steps under OSX 10.11 El Capitan and MySQL 5.7.X, should do the trick.

在 OSX 10.11 El Capitan 和 MySQL 5.7.X 下执行这些步骤应该可以解决问题。

Considering that you already have MySQL installed then..

考虑到您已经安装了 MySQL，然后..

Open a terminal window and type:

打开终端窗口并输入：

1. sudo /usr/local/mysql/support-files/mysql.server stop
2. sudo mysqld_safe --skip-grant-tables

1. 须藤/usr/local/mysql/support-files/mysql.server 停止
2. 须藤 mysqld_safe --skip-grant-tables

Since the command fired in the step 2 will be under on going state, you need to open another terminal window and then type:

由于在步骤 2 中触发的命令将处于运行状态，因此您需要打开另一个终端窗口，然后键入：

1. mysql -u root -p
2. UPDATE mysql.user SET password_expired='N', authentication_string=PASSWORD('') WHERE User='root';
3. quit;
4. sudo /usr/local/mysql/support-files/mysql.server restart

1. mysql -u 根 -p
2. 更新 mysql.user SET password_expired='N', authentication_string=PASSWORD('') WHERE User='root';
3. 退出;
4. 须藤/usr/local/mysql/support-files/mysql.server 重启

Important: in the step 2 you must replace for your password.

重要提示：在第 2 步中，您必须替换您的密码。

Hope it will wok for you.

希望它对你有用。

回答by Dave Everitt

MySQL password expired

MySQL 密码已过期

Resetting the password will solve the problem temporarily, however, from MySQL 5.7.4 to 5.7.10 (I think to encourage better security) the default value for the default_password_lifetimevariable is 360 (about a year). For those versions, if you make no changes to this variable(or to individual user accounts) all user passwords expire after 360 days.

重置密码会暂时解决问题，但是，从 MySQL 5.7.4 到 5.7.10（我认为是为了鼓励更好的安全性）default_password_lifetime变量的默认值是 360（大约一年）。对于这些版本，如果您不更改此变量（或个人用户帐户），则所有用户密码将在 360 天后过期。

Typically, from a script you might get the message: "Your password has expired. To log in you must change it using a client that supports expired passwords."

通常，您可能会从脚本中收到消息：“您的密码已过期。要登录，您必须使用支持过期密码的客户端对其进行更改。”

So, to prevent automatic password expiry, log in as root (mysql -u root -p), then, for clients that automatically connect to the server(e.g. scripts.) change the password expiration settings for those clients:

因此，为了防止密码自动过期，请以 root ( mysql -u root -p)身份登录，然后，对于自动连接到服务器的客户端（例如脚本），更改这些客户端的密码过期设置：

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER;

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER;

oryou can disable automatic password expiration for all users:

或者您可以为所有用户禁用自动密码过期：

SET GLOBAL default_password_lifetime = 0;

SET GLOBAL default_password_lifetime = 0;

Links:

链接：

MySQL: Password Expiration and Sandbox Mode
MySQL: Password Expiration Policy
Password expiration policy in MySQL Server 5.7

MySQL：密码过期和沙盒模式
MySQL：密码过期策略
MySQL Server 5.7 中的密码过期策略

回答by najemnyvrah

I'm running macOS Sierra(10.12.3) and I installed mysql-5.7.17-macos10.12-x86_64.dmg.

我正在运行 macOS Sierra(10.12.3) 并安装了 mysql-5.7.17-macos10.12-x86_64.dmg。

The answer from @lesley worked for me with the exception that I needed to add ./to ensure I was calling the mysql binary in my current working directory. Which is where the aforementioned package was installed.

@lesley 的答案对我有用，但我需要添加一个例外，./以确保我在当前工作目录中调用 mysql 二进制文件。这是安装上述软件包的地方。

If you cdto /usr/local/mysql/binand run mysql -u root -p --connect-expired-password, you could receive the following error.

如果你cd要/usr/local/mysql/bin和运行mysql -u root -p --connect-expired-password，你可以收到以下错误。

mysql: unknown option '--connect-expired-password'

I did. Because simply running mysqlwithout providing a path, called a previously installed version of the MariaDB client.

我做到了。因为只是在mysql不提供路径的情况下运行，调用了以前安装的MariaDB 客户端版本。

So to ensure you are executing the correct binary, you can either

因此，为了确保您正在执行正确的二进制文件，您可以

provide the absolute path

提供绝对路径

/usr/local/mysql/bin/mysql -u root -p --connect-expired-password

or the relative path after changing directories

或更改目录后的相对路径

cd /usr/local/mysql/bin
./mysql -u root -p --connect-expired-password

Both ways should work. Once you are connected to the client, the instruction are the same as above from @lesley.

两种方式都应该有效。连接到客户端后，说明与@lesley 的上述说明相同。

Enter your temporary password generated by the installer and set your new password.

输入安装程序生成的临时密码并设置新密码。

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourNewPassword'); 

回答by hygull

I faced the same problem. I followed the installation process guide from https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_HowTo.htmland downloaded DMG archive and installed MySQL on my MAC OS X 10.12.2.

我遇到了同样的问题。我按照https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_HowTo.html 中的安装过程指南下载了 DMG 存档并在我的 MAC OS X 10.12.2 上安装了 MySQL。

Finally executed the following commands on new Terminal.

最后在新终端上执行以下命令。

cd /usr/local/mysql/bin

./mysql -u root -p --connect-expired-password

It worked.

有效。

回答by Sudarsana Gudipati

This particular one did the trick for me:

这个特别的对我有用：

As specified in this link: https://www.variphy.com/kb/mac-os-x-reset-mysql-root-password

如此链接中所述：https: //www.variphy.com/kb/mac-os-x-reset-mysql-root-password

Do all the steps except executing

执行除执行外的所有步骤

    UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';

Execute

执行

    UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';

And then execute FLUSH PRIVILEGES;

然后执行FLUSH PRIVILEGES；

回答by ji-ruh

For Mysql 5.7 I use

对于 Mysql 5.7，我使用

shell $ > sudo grep 'temporary password' /var/log/mysqld.log

shell $ > sudo grep '临时密码' /var/log/mysqld.log

回答by james meek

Answer 7 worked for me: El capitan, MySQLinstalled from dmg and autogenerated password, but made sure to cdto /usr/local/bin/mysqlbefore entering ./mysql -root -pObvious, but I didn't the first time.

答案 7 对我有用：El capitan，MySQL从 dmg 和自动生成的密码安装，但在进入 Obvious之前确保这样cd做，但我不是第一次。/usr/local/bin/mysql./mysql -root -p

Now to find where all my databases and tables are and how to link them in.

现在找到我所有的数据库和表的位置以及如何链接它们。