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!