Cannot log into any account on mysql from terminal

Question

Ever since running as suggested: 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';

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).

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.

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';

from inside javascript (after connecting as root).

It was successful.

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

---

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.

---

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)

It also doesn't work.

question from:https://stackoverflow.com/questions/65837402/cannot-log-into-any-account-on-mysql-from-terminal

Answer 1

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. 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.

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

   shell> mysql //or sudo mysql

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

3. 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';

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).

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