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

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.

This question is related to mysql linux ubuntu auth-socket

The answer is


I got a solution!

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

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.


Try it: sudo mysql_secure_installation

Work's in Ubuntu 18.04


This may work

CREATE USER 'user'@'localhost' IDENTIFIED BY 'pwd';

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';


In case someone lands here after making the same mistake I did:

  1. Switched to plugin="mysql_native_password" temporarily. Performed my tasks.
  2. Attempted to switch back to the "auth_socket" plugin, but incorrectly referenced it as plugin="auth_socket" which resulted in mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded"
  3. Lacking a way to login to fix this mistake, I was forced to have to stop mysql and use mysql_safe to bypass authentication in order to switch to the appropriate plugin plugin="unix_socket"

Hopefully this saves someone some time if they receive the original poster's error message, but the true cause was flubbing the plugin name, not actually lacking the existence of the "auth_socket" plugin itself, which according to the MariaDB documentation:

In MariaDB 10.4.3 and later, the unix_socket authentication plugin is installed by default, and it is used by the 'root'@'localhost' user account by default.


You can try these some steps:

Stop Mysql Service 1st sudo /etc/init.d/mysql stop

Login as root without password sudo mysqld_safe --skip-grant-tables &

After login mysql terminal you should need execute commands more:

use mysql;

UPDATE mysql.user SET authentication_string=PASSWORD('solutionclub3@*^G'), plugin='mysql_native_password' WHERE User='root';

flush privileges;

sudo mysqladmin -u root -p -S /var/run/mysqld/mysqld.sock shutdown

After you restart your mysql server If you still facing error you must visit : Reset MySQL 5.7 root password Ubuntu 16.04


You can try as follows it works for me.

Start server:

sudo service mysql start

Now, Go to sock folder:

cd /var/run

Back up the sock:

sudo cp -rp ./mysqld ./mysqld.bak

Stop server:

sudo service mysql stop

Restore the sock:

sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe:

 sudo mysqld_safe --skip-grant-tables --skip-networking &

Init mysql shell:

 mysql -u root

Change password:

Hence, First choose the database

mysql> use mysql;

Now enter below two queries:

mysql> update user set authentication_string=password('123456') where user='root';
mysql> update user set plugin="mysql_native_password" where User='root'; 

Now, everything will be ok.

mysql> flush privileges;
mysql> quit;

For checking:

mysql -u root -p

done!

N.B, After login please change the password again from phpmyadmin

Now check hostname/phpmyadmin

Username: root

Password: 123456

For more details please check How to reset forgotten password phpmyadmin in Ubuntu


You can try with the below commands:

hduser@master:~$ sudo /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.
hduser@master:~$ sudo /etc/init.d/mysql start
[ ok ] Starting mysql (via systemctl): mysql.service.

For Ubuntu 18.04 and mysql 5.7

  • step 1: sudo mkdir /var/run/mysqld;

    step 2: sudo chown mysql /var/run/mysqld

    step 3: sudo mysqld_safe --skip-grant-tables & quit (use quit if its stuck )

login to mysql without password

  • step 4: sudo mysql --user=root mysql

    step 5: SELECT user,authentication_string,plugin,host FROM mysql.user;

    step 6: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'

now login with

  • mysql -u root -p <root>

The mysql command by default uses UNIX sockets to connect to MySQL.

If you're using MariaDB, you need to load the Unix Socket Authentication Plugin on the server side.

You can do it by editing the [mysqld] configuration like this:

[mysqld]
plugin-load-add = auth_socket.so

Depending on distribution, the config file is usually located at /etc/mysql/ or /usr/local/etc/mysql/


I tried with and it works

use mysql; # use mysql table
update user set authentication_string="" where User='root'; 

flush privileges;
quit;

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to linux

grep's at sign caught as whitespace How to prevent Google Colab from disconnecting? "E: Unable to locate package python-pip" on Ubuntu 18.04 How to upgrade Python version to 3.7? Install Qt on Ubuntu Get first line of a shell command's output Cannot connect to the Docker daemon at unix:/var/run/docker.sock. Is the docker daemon running? Run bash command on jenkins pipeline How to uninstall an older PHP version from centOS7 How to update-alternatives to Python 3 without breaking apt?

Examples related to ubuntu

grep's at sign caught as whitespace "E: Unable to locate package python-pip" on Ubuntu 18.04 How to Install pip for python 3.7 on Ubuntu 18? "Repository does not have a release file" error ping: google.com: Temporary failure in name resolution How to install JDK 11 under Ubuntu? How to upgrade Python version to 3.7? Issue in installing php7.2-mcrypt Install Qt on Ubuntu Failed to start mongod.service: Unit mongod.service not found

Examples related to auth-socket

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