56

On Ubuntu 15.10, at a point (maybe after an install & remove of mariadb) mysql was unable to operate. Tasks are up, but the server is down.

At the command:

mysql -u root -p

the system reply with like a:

mysql "ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded"

No way to log in, no plugin found, no errors in config files (all to default).

How to get back in control, and make mysql server run?

Improve this question
3

2 Answers 2

Reset to default
104

The "unix_socket" has been called by mysql authentication process (maybe related to a partial migration of database to mariadb, now removed). To get all stuff back working go su:

sudo su

then follow:

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -uroot

This will completely stop mysql, bypass user authentication (no password needed) and connect to mysql with user "root".

Now, in mysql console, go using mysql administrative db:

use mysql;

To reset root password to mynewpassword (change it at your wish), just to be sure of it:

update user set password=PASSWORD("mynewpassword") where User='root';

And this one will overwrite authentication method, remove the unix_socket request (and everything else), restoring a normal and working password method:

update user set plugin="mysql_native_password";

Exit mysql console:

quit;

Stop and start everything related to mysql:

/etc/init.d/mysql stop
kill -9 $(pgrep mysql)
/etc/init.d/mysql start

Don't forget to exit the su mode.

Now mySQL server is up and running. You can login it with root:

mysql -u root -p

or whatever you wish. Password usage is operative.

That's it.

Improve this answer
7
  • My mysqld_safe has no option like --skip-grant-tables ..what gives?
    – heemayl
    Dec 3, 2015 at 13:00
  • I'm on mysqld 5.6.27-0ubuntu1. You should find that option looking at: mysqld --verbose --help
    – Hydra Starmaster
    Dec 3, 2015 at 13:49
  • 2
    This got me going. With some differences instead of mysqld_safe which complained the argument --skip-grant-tables didn't exist I used mysqld. However it kept complaining it couldn't create the socket files in /var/run/mysqld/. I checked the apparmor config and it had the proper permissions. To fix this I had to mkdir /var/run/mysqld/ and then give completely open permissions: chmod -R 777 /var/run/mysqld/. Then I could finally start the daemon and change the plugin to 'mysql_native_password'.
    – dennmat
    May 12, 2016 at 15:41
  • 1
    My user table has no password field (Ubuntu 16 mysql 5.7.13). I used this SQL: UPDATE mysql.user SET authentication_string = PASSWORD('foobar123'), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';
    – Anthony Scaife
    Aug 14, 2016 at 22:01
  • The instruction here is concise and helped me resolved the issue. Thank you!
    – Eduardo B.
    Oct 27, 2016 at 17:34
12

Here is my steps of doing this:

/etc/init.d/mysql stop
sudo killall mysqld_safe
sudo killall mysqld
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=PASSWORD("mynewpassword") where User='root';
update user set plugin="mysql_native_password";
quit;
/etc/init.d/mysql stop
sudo kill -9 $(pgrep mysql)
/etc/init.d/mysql start
Improve this answer
2
  • 2
    Now after "mysql -u root" I get: ERROR 1524 (HY000): Plugin 'root' is not loaded
    – dxvargas
    May 28, 2016 at 19:45
  • It sounds like you mixed up the two UPDATE queries. Did you set plugin="root" on accident?
    – Adam Plocher
    Apr 24, 2017 at 17:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.