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'
Best Answer
Try
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.