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?
Here is Solutions:
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:
/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:
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:
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.
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
Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂