无法从终端登录 mysql 上的任何帐户 [英] Cannot log into any account on mysql from terminal

问题描述

自从按照建议运行:https://stackoverflow.com/a/50131831/3310334

alter user 'root'@'localhost' identified with mysql_native_password by 'Gzbz1H!fZ@#LyF33IqP$rAS8H#0iNc4lK8l2Md@EHxJyFK2YgfQwiKxz*0#lykWvKdWzhxh6EYKu&6ZPp1#9$%YMPb6EfDPYf2h';

我不能再访问mysql了:

I can't access mysql anymore:

% mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
% sudo mysql
Password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
% mysql
ERROR 1045 (28000): Access denied for user 'theonlygusti'@'localhost' (using password: NO)
% sudo mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

我完全按照以前的方式复制/粘贴我的密码(并且密码曾经是相同的).

I copy/paste my password exactly as I used to (and the password used to be the same).

事实上，我注意到我更改"的任何帐户如上所述，我无法再通过终端登录，但它们在 Node.js 中使用 require('mysql') 包工作.

In fact, I have noticed that any account that I "altered" as above I cannot log into through the terminal anymore, but they work from within Node.js using require('mysql') package.

如何在不丢失所有数据库的情况下重新获得对 root 帐户的访问权限?

How can I regain access to the root account without losing all my databases?

我只是通过运行使问题可能变得更糟

I just made the problem probably-worse by running

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Gzbz1H!fZ@#LyF33IqP$rAS8H#0iNc4lK8l2Md@EHxJyFK2YgfQwiKxz*0#lykWvKdWzhxh6EYKu&6ZPp1#9$%YMPb6EfDPYf2h';

从 javascript 内部(以 root 身份连接后).

from inside javascript (after connecting as root).

成功了.

现在我无法使用 JavaScript 或终端连接到 root.

Now I can't connect to root using JavaScript or the terminal.

我刚刚尝试了以下建议:https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

I just tried following advice: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

% cat init.sql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Gzbz1H!fZ@#LyF33IqP$rAS8H#0iNc4lK8l2Md@EHxJyFK2YgfQwiKxz*0#lykWvKdWzhxh6EYKu&6ZPp1#9$%YMPb6EfDPYf2h';
% brew services stop mysql
% mysqld --init-file="$PWD/init.sql"

但我仍然无法通过将与上述完全相同的密码复制/粘贴到密码提示中来进行连接.

But I still can't connect by literally copy/pasting exactly the same password as above into the password prompt.

我还尝试了该链接中详述的其他重置方法，使用 --skip-grant-tables:

I have also tried the other reset method detailed in that link, using --skip-grant-tables:

% kill `cat /usr/local/var/mysql/theonlygustis-MacBook-Pro.pid`
% mysqld --skip-grant-tables
% mysql
> flush privileges;
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'alter user 'root'@'localhost' identified with mysql_native_password by 'Gzbz1H!fZ@#LyF33IqP$rAS8H#0iNc4lK8l2Md@EHxJyFK2YgfQwiKxz*0#lykWvKdWzhxh6EYKu&6ZPp1#9$%YMPb6EfDPYf2h';
> flush privileges;
> exit
% kill `cat /usr/local/var/mysql/theonlygustis-MacBook-Pro.pid`
% mysqld
% mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

它也不起作用.

推荐答案

前面的部分提供了专门针对 Windows 和 Unix 以及类 Unix 系统的密码重置说明.或者，在任何平台上，您都可以使用 mysql 客户端重置密码(但这种方法不太安全):

The preceding sections provide password-resetting instructions specifically for Windows and Unix and Unix-like systems. Alternatively, on any platform, you can reset the password using the mysql client (but this approach is less secure):

1. 如有必要，停止 MySQL 服务器，然后使用 --skip-grant-tables 选项重新启动它.这使任何人都可以在没有密码的情况下使用所有权限进行连接，并禁用帐户管理语句，例如 ALTER USER 和 SET PASSWORD.因为这是不安全的，如果服务器是用 --skip-grant-tables 选项启动的，它也会通过启用 skip_networking 来禁用远程连接.

1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it also disables remote connections by enabling skip_networking.

使用mysql客户端连接MySQL服务器；不需要密码，因为服务器是用 --skip-grant-tables 启动的:

Connect to the MySQL server using the mysql client; no password is necessary because the server was started with --skip-grant-tables:

<代码>外壳>mysql//或 sudo mysql

在 mysql 客户端中，告诉服务器重新加载授权表，以便帐户管理语句起作用:

In the mysql client, tell the server to reload the grant tables so that account-management statements work:

1. mysql>刷新权限；然后更改'root'@'localhost' 帐户密码.将密码替换为您要使用的密码.要更改具有不同主机名部分的 root 帐户的密码，请修改使用该主机名的说明.

1. mysql> FLUSH PRIVILEGES; Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use. To change the password for a root account with a different host name part, modify the instructions to use that host name.

<代码>mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

您现在应该能够使用新密码以 root 身份连接到 MySQL 服务器.停止服务器并正常重启(没有 --skip-grant-tables 选项，也没有启用 skip_networking 系统变量).

You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally (without the --skip-grant-tables option and without enabling the skip_networking system variable).

如果没有帮助.可以看官网https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

If does not help. Can see official page https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

这篇关于无法从终端登录 mysql 上的任何帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！