0

I'm having a weird problem where I cannot login to my MySQL server with sudo as root on a ubuntu 19.10 server. I want to be able to reset my root password.

The error message I got was: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Anyone had similar problems? Thanks!

peidaqi
  • 653
  • 5
  • 15
  • Does this answer your question? [MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)](https://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw) – matwr Feb 24 '20 at 19:17
  • 1
    If you don't recall your root password and for whatever reason `mysql` isn't letting you in from your root user's shell, you may need to shut down mysql and then restart after toggling [`skip-grant-tables`](https://stackoverflow.com/questions/41984956/cant-reset-root-password-with-skip-grant-tables-on-ubuntu-16) and restarting.This starts the server and allows anyone to log in without using credentials (so.. if this is a live database other folks are accessing... be careful). Then you can connect again, change your root password, exit, and toggle the setting and restart the service normally. – JNevill Feb 24 '20 at 19:23
  • Somehow I cannot seem to be able to start mysqld with: "sudo mysqld --skip-grant-tables". The system is not returning any errors but just refuse to start. – peidaqi Feb 24 '20 at 19:29
  • An "ERROR 1045 Access denied" shows that your mysql service is already running, so you won't be able to start it again. – Jesus Uzcanga Feb 24 '20 at 19:52
  • Is this a fresh install? Did you set that mysql password before or you are trying to use the Linux root user's password? – Jesus Uzcanga Feb 24 '20 at 19:53
  • The link in my comment describes exactly the way to toggle that setting in Ubuntu 19.04 (where the --skip-grant-tables doesn't work). – JNevill Feb 24 '20 at 19:57

1 Answers1

0

Ok - so I solved this problem myself.

The correct way of resetting the root password for mysql is to:

  1. Stop all mysql server sessions.
  2. Start mysqld manually using - this basically tells MySQL to skip any credentials check and allow anyone to login, hence you want to skip-networking to avoid network users.

sudo mysqld --skip-grant-tables --skip-networking

  1. Use this to login as root:

sudo mysql -u root

  1. In the mysql prompt, do:

FLUSH PRIVILEGES; SET PASSWORD FOR root@'localhost' = PASSWORD('password'); FLUSH PRIVILEGES;

  1. Stop mysqld and restart mysql normally (you may need to kill the mysqld process).

In my case, there was some problems with the installation and the /var/run/mysqld folder was not correctly set up. It may be a bug with MySQL and it was not properly reported.

After creating the folder with the correct setmod (user:group is mysql:mysql) mysqld runs without any problem.

peidaqi
  • 653
  • 5
  • 15