mysqllinuxubuntuauth-socket

MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded"

My local environment is:

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 RackSpace 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 step 1. again.

Solution

  • I got a solution!

    When resetting the root password at step 2), also change the auth plugin to mysql_native_password:

    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. 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)

    Or

    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

    Works better.

    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.

    Skip the my.cnf file

    The issue also was not there. If you are not sure, this might help you.