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?

Hydra Starmaster
  • 1,733
  • I am not familiar with this particular error but see this discussion: https://lists.launchpad.net/maria-developers/msg08177.html – Jos Dec 03 '15 at 11:56
  • @Jos: that discussion, maybe for a similar situation, doesn't offer a solution. I've googled a lot, but I've not found a complete solution exposed. Anyway, I've posted a working answer. – Hydra Starmaster Dec 03 '15 at 12:20
  • About the reason for the error message: The unix_socket plugin is part of MariaDB (not MySQL) and needs to be loaded in order for grants using it to work: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/ – JonnyJD Jul 04 '18 at 15:50

2 Answers2

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.

Hydra Starmaster
  • 1,733
  • My mysqld_safe has no option like --skip-grant-tables ..what gives? – heemayl Dec 03 '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 03 '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
  • Saved me from tearing my hair. Thank you so much. Works in 2017! – Amjad May 09 '17 at 06:38
  • I had to create /var/run/mysqld directory with owner mysql before mysqld stopped crashing. don't set 777 permissions as mentioned above! – JPT Jan 08 '18 at 13:32
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
Roy Yan
  • 121