My local environment is:
- fresh Ubuntu 16.04
- with PHP 7
with installed MySQL 5.7
sudo apt-get install mysql-common mysql-server
When I tried to login to MySQL (via CLI):
mysql -u root -p
I came across an cyclic issue with 3 steps.
1) First was some socket issue
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
Solution: restarting PC.
Which led to another error:
2) With access denied
ERROR 1698 (28000): Access denied for user 'root'@'localhost'.
Possible issue? Wrong password for “root” user!
Solution: reset root password with this tutorial.
With correct password and working socket, there comes last error.
3) Incorrect auth plugin
mysql "ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded"
Here I stopped or somehow got to 1) again.
I got a solution!
When resetting root password at step 2), also change the auth plugin to
use mysql; update user set authentication_string=PASSWORD("") where User='root'; update user set plugin="mysql_native_password" where User='root'; # THIS LINE flush privileges; quit;
This allowed me to log in successfully!
Full code solution
1. run bash commands
1. first, run these bash commands
sudo /etc/init.d/mysql stop # stop mysql service sudo mysqld_safe --skip-grant-tables & # start mysql without password # enter -> go mysql -uroot # connect to mysql
2. then run mysql commands => copy paste this to cli manually
use mysql; # use mysql table update user set authentication_string=PASSWORD("") where User='root'; # update password to nothing update user set plugin="mysql_native_password" where User='root'; # set password resolving to default mechanism for root user flush privileges; quit;
3. run more bash commands
sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start # reset mysql # try login to database, just press enter at password prompt because your password is now blank mysql -u root -p
4. Socket issue (from your comments)
When you see a socket error, a community came with 2 possible solutions:
sudo mkdir -p /var/run/mysqld; sudo chown mysql /var/run/mysqld sudo mysqld_safe --skip-grant-tables &
(thanks to @Cerin)
mkdir -p /var/run/mysqld && chown mysql:mysql /var/run/mysqld
(thanks to @Peter Dvukhrechensky)
Blind paths and possible edge errors
Use 127.0.0.1 instead of localhost
mysql -uroot # "-hlocalhost" is default
Can lead to “missing file” or slt error.
mysql -uroot -h127.0.0.1
Skip the socket issue
I’ve found many ways to create
mysqld.sock file, change access rights or symlink it. It was not the issue after all.
The issue also was not there. If you are not sure, this might help you.
Answered By – Tomas Votruba
Answer Checked By – Katrina (BugsFixing Volunteer)