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?

3
103

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.

7
  • My mysqld_safe has no option like --skip-grant-tables ..what gives? – heemayl Dec 3 '15 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 '15 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 '16 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 '16 at 22:01
  • The instruction here is concise and helped me resolved the issue. Thank you! – Eduardo B. Oct 27 '16 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
2
  • 2
    Now after "mysql -u root" I get: ERROR 1524 (HY000): Plugin 'root' is not loaded – dxvargas May 28 '16 at 19:45
  • It sounds like you mixed up the two UPDATE queries. Did you set plugin="root" on accident? – Adam Plocher Apr 24 '17 at 17:34

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