0

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.

theonlygusti
  • 11,032
  • 11
  • 64
  • 119

1 Answers1

0

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:

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

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

veter svobodnyi
  • 1
  • 2
  • Maybe this help 1. Add default_authentication_plugin = mysql_native_password to the [mysqld] section of my.cnf. Then restart mysql. 2. Enter mysql and create a new user by doing something like CREATE USER 'root'@'localhost' IDENTIFIED BY 'password'; 3. Grant privileges as necessary. E.g. GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost'; and then FLUSH PRIVILEGES; – veter svobodnyi Jan 22 '21 at 00:18
  • _create_ the user 'root'@'localhost' ? I am doubtful it will work.... – theonlygusti Jan 22 '21 at 00:20
  • I still can't log in after writing `[mysqld]` newline `default_authentication_plugin = mysql_native_password` to `/etc/my.cnf` – theonlygusti Jan 22 '21 at 00:24
  • what shows from this command. 1. 'sudo mysql'. 2. 'SELECT user,authentication_string,plugin,host FROM mysql.user;' ? root user have mysql_native_password ? – veter svobodnyi Jan 22 '21 at 00:31
  • I can't log in to mysql like that, so I can't run the `SELECT` – theonlygusti Jan 22 '21 at 00:32
  • Maybe you enter wrong password. Try this 'mysql -u {username} -p{password}'. -p{password} without space. – veter svobodnyi Jan 22 '21 at 00:38
  • I just completely wiped mysql I will just reinstall and restart – theonlygusti Jan 22 '21 at 00:45