“Plugin '0' is not loaded”

Question

After upgading MySQL to newer version I have error when I want to connect to server:

 ERROR 1524 (HY000): Plugin '0' is not loaded

Any ideas?

Answer 1

This is indeed a bug with MySQL and a changed user table structure. Unfortunately, if you don't know what changed, you can't change it back. Also, if you cannot login, you cannot fix anything either.

I fixed this as follows. Please be careful with these instructions as they may not work entirely for your setup and you could lose your data if this goes wrong. Don't blame me if it does!

1. Back-up old data dir

Back-up your old data dir somewhere. This allows you to restore the tables at the end. To find out where your data dir is, you can try to initialise mysql. It will give you an error because the data dir already exists:

mysqld initialize

Output:

mysqld: Can't create directory '/usr/local/mysql/data/' (Errcode: 13 - Permission denied)

Now backup your data dir somewhere:

cp -r /usr/local/mysql/data ~/backup-dir/

2. Re-initialize MySQL

With your data dir safely backed up, remove it and re-initialize mysql. It will give you a new temporary password which you can change later.

rm -rf /usr/local/mysql/data
mysqld initialise

The output will give you a new password for 'root'. If you get permission errors, use sudo (not sure about the drawbacks).

Start the server using mysqld_safe to be sure it works the first time:

mysqld_safe

Optionally, change the root password by logging in to mysql and running alter table:

mysql -u root -p
<enter password>
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New Password';

3.Restore data

Stop the mysql server

mysqld stop

Copy all the backup files to the new data dir (run this for every db you need to restore):

cp -r ~/backup-dir/data/<dbname> /usr/local/mysql/data/

For MyISAM tables, this is enough. If you have InnoDB tables, you also need to copy the InnoDB table space from your backup:

cp ~/backup-dir/data/ibdata* /usr/local/mysql/data/

Then, fix permissions (look at the files already present in /usr/local/mysql/data for the right permissions, mine were 'mysql' for both user and group):

chown -R mysql:mysql /usr/local/mysql/data/*

Start the mysql server

mysqld start

All done! You should be able to login again and your tables should be back containing all data, too.

Answer 2

This looks like a Bug in MySQL as Bug #60432 Modifying mysql.user table can deny users from logging in . Which states that:

If database manager accidentally (or deliberately) modifies mysql.user table by adding any column in position lower (or equal) than "max_user_connections", then after reloading privileges no one is allowed to log in.

Workaround: Undoing the modification made in user table.

Answer 3

Run following commands

sudo /etc/init.d/mysql stop

sudo mkdir -p /var/run/mysqld; sudo chown mysql /var/run/mysqld

sudo mysqld_safe --skip-grant-tables &

and then press enter

now your password get reset, you can change your password again

This works for me