Cannot login root user after upgrading MySQL from 5.6 to 5.7

Question

After upgrading MySQL from 5.6 to 5.7, I am not able to log in as root:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I found the following in the log:

2016-10-26T10:23:01.845088Z 0 [Warning] User entry 'root'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.
2016-10-26T10:23:01.845112Z 0 [Warning] User entry 'mysql.sys'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.
2016-10-26T10:23:01.845127Z 0 [Warning] User entry 'debian-sys-maint'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.
2016-10-26T10:23:01.845142Z 0 [Warning] User entry 'phpmyadmin'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.
2016-10-26T10:23:01.845155Z 0 [Warning] Some of the user accounts with SUPER privileges were disabled because of empty mysql.user.plugin value. If you are upgrading from MySQL 5.6 to MySQL 5.7 it means we were not able to substitute for empty plugin column. Probably because of pre 4.1 password hash. If your account is disabled you will need to:
2016-10-26T10:23:01.845183Z 0 [Warning] 1. Stop the server and restart it with --skip-grant-tables.
2016-10-26T10:23:01.845192Z 0 [Warning] 2. Run mysql_upgrade.
2016-10-26T10:23:01.845200Z 0 [Warning] 3. Restart the server with the parameters you normally use.
2016-10-26T10:23:01.845207Z 0 [Warning] For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual
2016-10-26T10:23:01.853461Z 0 [Note] Event Scheduler: Loaded 0 events
2016-10-26T10:23:01.853962Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.16-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2016-10-26T10:23:02.138961Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

So I did as suggested: stopped the server:

> service mysql stop

Started it with skipped grant tables:

> sudo mysqld_safe --skip-grant-tables

When mysqld_safe is running with --skip-grant-tables, I can log in as root but only until I close the terminal where it is running (it is not running as a daemon but stops on closing the terminal).

Then I ran mysql_upgrade as suggested (in another terminal)

> sudo mysql_upgrade

And that upgraded my tables.

I closed the terminal where mysqld_safe was running, stopped the mysql server (service mysql stop). I had to kill all the instances of mysql because otherwise I was getting full log of the following errors:

2016-10-26T10:40:54.073975Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2016-10-26T10:40:54.074060Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

Then I started MySQL again:

> service mysql start

and got the same result as above (Access denied for user 'root'@'localhost' (using password: NO))

How do I fix it? I've been struggling with it for hours and any help appreciated!

Answer 1

I had this same problem when I upgraded Ubuntu from 14.04 to 16.04. I was too lazy to do your suggestion (export databases and start over), so I ended up doing this to fix the problem!

Stop mysql and then launch without grants

> sudo service stop mysql 
> sudo mkdir /var/run/mysqld # needed to launch mysql in ubuntu from command line...may not apply to other distros
> sudo chown mysql:mysql /var/run/mysqld # same reason as above
> sudo mysqld_safe --skip-grant-tables

Then, in another terminal window:

> mysql -uroot 
> select User, host, authentication_string,plugin from mysql.user; #see what users don't have a password (authentication_string and plugin are empty) 
> update mysql.user set authentication_string=PASSWORD('woot') where user='user';
> update mysql.user set plugin='mysql_native_password' where user='user'; 
> FLUSH PRIVILEGES;
> exit; 

Finally, I just needed to kill the temp mysql (mysqld_safe) instance and restart it... On the first terminal

> ^\ #to tell the mysqld_safe process to quit itself 
> sudo service mysqld start 

Answer 2

The problem was that the mysql.user plugin value was empty for all users including debian-sys-maint so I could not even reconfigure the package.It was supposed to be fixed by running mysql_upgrade but for some reason it was not.

So I made dump of all databases, removed MySQL server fully and then reinstalled it and imported my databases back again.

Dump all your databases:

> mysqldump -u root -p --databases db1 db2 > alldb.sql

It was rather silly of me but I did a dump of all databases with --all-databases. It included the mysql.user table where all the users had empty "plugin" value so I ran across the same issue after the first restart of mysql service. So afterwards I made the dump only for my databases.

The dump will be placed in the folder from where you ran the command

Remove MySQL and its settings:

> sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.7 mysql-client-core-5.7
> sudo rm -rf /etc/mysql /var/lib/mysql
> sudo apt-get autoremove
> sudo apt-get autoclean

Install MySQL:

> sudo apt-get install mysql-server

Import your databases back:

> mysql -u root -p < alldb.sql