2

I've just installed Ubuntu 20.04 and messed something up with MySQL. I don't know how to login:

$ mysql -u root -p                      
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I don't have any data in there, so I don't care if the DB is lost. In fact, I would prefer a clean solution.

What I tried

dpkg-reconfigure

This answer:

$ sudo dpkg-reconfigure mysql-server-8.0
mysqld will log errors to /var/log/mysql/error.log
mysqld is running as pid 7577

Finishes without an error message, but I don't see any way to enter a new password.

mysqld_safe

I tried this, but

$ sudo mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 
'/var/run/mysqld/mysqld.sock' (2)
$ ls -alh /var/run/mysqld/           
total 0
drwxr-xr-x  2 mysql root   40 Mai 17 11:08 .
drwxr-xr-x 38 root  root 1,1K Mai 17 11:08 ..

I guess this might be an issue? What should I do?

Complete reinstall

I followed this guide to completely re-install mysql, but I run into the same issues.

3 Answers 3

3

you can use this code

sudo /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
2
  • 1
    This worked on Ubuntu Server 20.04 for me
    – rubo77
    Apr 16, 2021 at 17:54
  • worked for me too, thank you
    – Akhil S
    Jul 28, 2021 at 16:54
1

Apparently I was on the wrong track. I simply needed to login via

$ sudo mysql -u root

So not the -p option. No password required.

0

MySQL 8.0 defaults to using auth_socket instead of passwords for root user, which means after installing mysql-server-8.0 you can simply login automatically with either:

sudo mysql

or

sudo mysql --user=root --host=localhost

...no need to specify the root user if you're already logged in as root to your shell. However if you are logged in as a different sudo user then you can specify that username instead (and no password) as long as the sudo user was added to MySQL as an auth_socket user.

The only time you need to specify a password if when logging in via TCP (e.g. @127.0.0.1). So if you try something like sudo mysql --user=root --host=127.0.0.1 --protocol=tcp then MySQL will prompt you for a password... but unless you setup a root@127.0.0.1 (TCP) user and password then you will not be able to login using that method.

Keep in mind it is now highly recommend to not setup a root TCP user these days, and instead create an alternative "maintenance" user (e.g. for phpMyAdmin) like admin@127.0.0.1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.