Resetting MySQL root password

Abraham Murciano Benzadon

The Problem

I am running Ubuntu 18.04 and I have installed MySQL recently. I have never actually used it yet though. As far as I remember, I never set any password for any user. (If I did, I know what it would be.)

Today I installed phpMyAdmin and found that I was unable to log in to root through phpMyAdmin. I tried to log in to MySQL directly via the terminal without a password:

mysql -u root

And with the password that I would have set, if I forgot:

mysql -u root -p

But both of these returned

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Attemted Solutions

Attempt 1

I tried following this guide, I stopped the MySQL demon process, I then started the mysqld daemon process using the --skip-grant-tables option. Then when running this command to log in without the password:

mysql -u root

Instead of arriving at the mysql> prompt, I was faced with the following error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket 
'/var/run/mysqld/mysqld.sock' (2)

Attempt 2

I tried to uninstall MySQL using:

sudo apt-get --purge remove mysql-server mysql-common mysql-client

And then I tried to reinstall MySQL using this command:

sudo apt-get install mysql-server mysql-common mysql-client

And then tried to set a password like this:

mysqladmin -u root password my-new-password

Yet I was once again stumped with an error message:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost''

My fellow programmers, I am absolutely clueless as to where to head from here in the continuation of my quest to obtain the powers of MySQL...

Edit

After using sudo (as per vidarlo's answer) I was able to access my databases, and I managed to change my password using this:

mysql> update user set authentication_string=password("Z4hVdwn9qZ") 
       where user='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

But phpMyAdmin still won't log in and returns the error

#1698 - Access denied for user 'root'@'localhost'
vidarlo

Try

sudo mysql -u root

MariaDB uses Unix socket authentication, which means you're authenticated as the user starting the mysql client.

You can read more about it in MariaDB's documentation.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related