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.

Martin Thoma
  • 19,277

3 Answers3

6

you can use this code

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

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
Avi Chhetri
  • 161
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.

Martin Thoma
  • 19,277
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.

Jesse Nickles
  • 442