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

Question

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.

score 453 · Accepted Answer · edited Aug 04 '22 at 09:59

453

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.

edited Aug 04 '22 at 09:59

Faizan

86
13

answered Jun 17 '16 at 10:36

Tomas Votruba

23,240
9
79
115

3

Thank you for this. This is also an excellent answer on how to reset mysql root pass so you might want to slightly edit the title to help it come up before we already run into the socket issue. – Wtower Aug 22 '16 at 09:34
1

I'm glad it helped. How would you edit the title to keep origin message? I understand the solution requires to reset password, but it does not solve the issue in the title. This is more complex failure of more points. For root password reset, I'd go here: http://stackoverflow.com/a/6401963/1348344 – Tomas Votruba Aug 22 '16 at 11:44
Thanks this seems to work. Any idea why the plugin definition needs to be tweaked? – matanster Jan 28 '17 at 18:25
1

@matanster Not really, this is pure step-by-step trial-fail constructed. What OS do you use? – Tomas Votruba Jan 28 '17 at 22:53
If you cant use mysql_safe here's a way to achieve this using systemctl 'set enviroment' http://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7 – Rhyuk Jan 30 '17 at 15:15
8

Your mysqld_safe call is incorrect. You need to instead run: `sudo mkdir -p /var/run/mysqld; sudo chown mysql /var/run/mysqld; sudo mysqld_safe --skip-grant-tables &` – Cerin Feb 26 '17 at 19:37
The init.d scripts don't work with the mysqld_safe process on Ubuntu 16. – Cerin Feb 26 '17 at 19:45
1

Worked for me after applying @Cerin's steps. I also recommend changing the second update statement to `update user set plugin="mysql_native_password" where User='root';` so it doesn't unintentionally affect other users. – Chris B Mar 06 '17 at 04:10
@Cerin Thanks for feedback. Can you edit the post and suggest changes? I'd be happy to accept them. – Tomas Votruba Mar 10 '17 at 13:00
@Cerin Did you get my message? I'm not sure what exact lines to replace. – Tomas Votruba Jul 05 '17 at 19:37
yeah, this solution it work for me, thank you. (same problem at macOS sierra 10.12.6, percona 5.7.18-16, installed by homebrew) – watchzerg Sep 03 '17 at 11:28
And be sure to re-establish a root password. – Rick James May 18 '18 at 15:31
You might want to mark this answer as the correct one with greeny, since it indeed is correct :) – Aleksei Matiushkin May 20 '18 at 03:25
I am having same issue on windows how to fix that – Bilal Maqsood May 25 '18 at 09:35
4

Please add a posible solution of the socket issue to the answer. Like this one `mkdir -p /var/run/mysqld && chown mysql:mysql /var/run/mysqld` It may save someone's time – Peter Dvukhrechensky Aug 30 '18 at 06:58
Thanks for the comment, I've updated the answer. Is that correct? – Tomas Votruba Aug 30 '18 at 07:55
1

Perfect Answer.!!!! Have been fiddling with the mysql config for too long, after resetting the root password. – Mohd Abdul Mujib Sep 28 '18 at 12:30
Thanks! This was a super useful discussion. In the end I was not yet able to get my problem fixed with it, but most parts. Combining a couple of approaches I made a shell script that might come in handy for someone in the future: https://toxyl.github.io/mysql-innodb-recovery/ – Tox Dec 01 '18 at 21:08
I was not able to stop mysql (step 3) when it was in safe mode (Ubuntu 16), and if you have multiple daemons running you will end up with issues (so be sure it's actually shutdown!). To stop mysql in safe mode I had to run this command: 'mysqladmin -u root -p shutdown' – ChronoFish Jan 07 '19 at 15:46
1

great work, 3 hours later i'm glad i found this answer, installing mysql server is fun because stuff like makes you question your profession – acidjazz Mar 29 '19 at 01:42
Indeed :D, I felt like quitting when I faced this for a week. I was just trying to store name and email in the user table. – Tomas Votruba Mar 29 '19 at 01:44
I see the following under journalctl -xn, it seems that the authentication plugin and password are mutually exclusive? "2019-05-07 14:06:14 9 [Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored." – kirenpillay May 07 '19 at 12:09

Bablu Ahmed · Answer 2 · 2018-12-24T03:30:10.463

59

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

edited Dec 24 '18 at 03:30

answered Sep 13 '18 at 23:18

Bablu Ahmed

4,412
5
49
64

4

only this works for me! backup and restore socket is pretty smart – touhid udoy Dec 23 '18 at 08:59
1

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) before the "done!" line in your answer when trying to login with 123456. – trainoasis Mar 02 '20 at 14:42
I solved doing the described steps in phpmyadmin (i.e. change password, change plugin). For some reason 'set authentication_string=password(...)' made it stop working (Error 1045 (28000): Access denied...). – Fil Mar 30 '20 at 21:56
1

Although this is a solution, whenever I restart my computer(ubuntu 20.04), I have to run all of these commands. Any permanent solution? – Anupam Jan 31 '21 at 09:55

score 27 · Answer 3 · edited Feb 01 '22 at 12:55

27

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/

If unix_socket=OFF is set in the same section, enable it by changing it to unix_socket=ON or this fix does not apply.

edited Feb 01 '22 at 12:55

Community

1
1

answered Jan 07 '19 at 10:11

rustyx

80,671
25
200
267

3

THIS one. All the other answers explain how to work around the error. This one explains how to RESOLVE it. – Envite Mar 27 '19 at 19:35
If you're on MariaDB 10.4.3 and later, the unix_socket auth plugin should be installed by default, but if you incorrectly reference it as plugin="auth_socket", instead of plugin="unix_socket" when updating your "user", you will receive this somewhat misleading message. My answer elaborates on how I messed this up. – Rohn Adams Sep 15 '19 at 04:09
If you're not on Debian or Ubuntu you might need to still load the plugin. "In other systems, although the plugin's shared library is distributed with MariaDB by default as auth_socket.so, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB." [[https://mariadb.com/kb/en/authentication-plugin-unix-socket/]] So in my case I still needed to add the plugin for Manjaro – ashkan117 Feb 06 '22 at 04:13

score 13 · Answer 4 · edited Feb 07 '20 at 08:55

13

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>

edited Feb 07 '20 at 08:55

user123456

364
1
3
16

answered Jan 07 '19 at 15:10

arunava maiti

391
3
3

2

but at which step may i set the pass for root? because i tried ```$ mysql -u root -p``` but access denied yet. ```Access denied for user 'root'@'localhost' (using password: YES)``` I tried to input 'YES' password – Alexey Nikonov Aug 12 '19 at 13:28

score 9 · Answer 5 · edited Jul 23 '18 at 18:40

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

score 7 · Answer 6 · answered Sep 03 '18 at 03:38

7

Try it: sudo mysql_secure_installation

Work's in Ubuntu 18.04

answered Sep 03 '18 at 03:38

Gorcer

188
2
9

score 6 · Answer 7 · answered Sep 15 '19 at 04:06

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

Switched to plugin="mysql_native_password" temporarily. Performed my tasks.
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"
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.

score 5 · Answer 8 · answered Sep 30 '20 at 22:40

5

I tried with and it works

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

flush privileges;
quit;

answered Sep 30 '20 at 22:40

Mouneem Essabbar

94
1
3

This worked for me on mysql version 8.0.22 for ubuntu 20.04+. [This](https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password) says `PASSWORD()` function is removed from mysql 8.0. – Shrivathsav Seshan Jan 31 '21 at 05:02

treissler · Answer 9 · 2023-01-06T15:57:46.193

It works for me (ubuntu 22.04):

1) stop and remove mysql and related services

sudo kill $(pgrep mysql)
sudo apt-get remove --purge mysql-\*
sudo rm -rf /etc/mysql

2) download APT repo https://dev.mysql.com/downloads/repo/apt/

3) follow guide https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

shortlist from the guide:

sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb

inside the dialog leave settings as is

sudo apt-get update
sudo apt-get install mysql-server

it should be a dialog: set ROOT password and leave auth setting as is. I've tried to change it and got auth error.

Finally

sudo apt-get update
sudo apt-get install mysql-workbench-community

Hope it will save your time, good luck :)

score -1 · Answer 10 · answered Feb 23 '21 at 06:24

-1

This may work

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

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

answered Feb 23 '21 at 06:24

T3D

39
5

score -1 · Answer 11 · answered Dec 15 '22 at 08:11

My cmd prompt was displaying the same error and sql installation command was stuck. Below commands worked for me.

Open a new terminal. Terminate the current open mysql_secure_installation from first terminal,

sudo killall -9 mysql_secure_installation

Start the mysql client and you will move to sql prompt:

sudo mysql

Run the following SQL query:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'setYourPasswordHereWithinQuotes';

exit

Let's secure it by entering in cmd:

sudo mysql_secure_installation

Whenever promoted for password, use the setYourPasswordHereWithinQuotes password you set in above sql query.

DONE!

score -9 · Answer 12 · edited May 09 '19 at 14:56

-9

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.

edited May 09 '19 at 14:56

Kunal Mukherjee

5,775
3
25
53

answered May 09 '19 at 12:34

genghenggao

1

Can you explain that further? How does restarting the server solve the given error message? – Nico Haase May 09 '19 at 13:54

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

1) First was some socket issue

2) With access denied

3) Incorrect auth plugin

12 Answers12

Full code solution

1. First, run these bash commands

2. Then run mysql commands => copy paste this to CLI manually

3. Run more bash commands

4. Socket issue (from your comments)

Blind paths and possible edge errors

Use 127.0.0.1 instead of localhost

Skip the socket issue

Skip the `my.cnf` file

Linked

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

1) First was some socket issue

2) With access denied

3) Incorrect auth plugin

12 Answers12

Full code solution

1. First, run these bash commands

2. Then run mysql commands => copy paste this to CLI manually

3. Run more bash commands

4. Socket issue (from your comments)

Blind paths and possible edge errors

Use 127.0.0.1 instead of localhost

Skip the socket issue

Skip the my.cnf file

Linked

Skip the `my.cnf` file