Resetting the root password in MySql

I know that this are already a hundred other articles already written that cover this however is appears that NONE of the previously published works cover the nuances of MySql 5.7. I recently found myself up against he MySql 5.7 server brick wall on a new Ubuntu 16.04 LTS installation.

One would think hey it’s a new installation it should be dead easy and to be hones it was the complete opposite. The installation is fairly straight forward just do the following;

$ sudo apt update
$ sudo apt upgrade
$ sudo apt install mysql-server mysql-client
$ sudo mysql_secure_installation
$ mysql -u root -p

Upon login to the fresh mysql server you’ll be asked to set a new root password immediately and you are good to go until you forget said password. I’d like to through a note about the mysql_secure_installation which is a step most tutorials miss.

So I found myself in the situation months have passed by and I needed root level access which was of course blocked by my own failing memory.

$ mysql -u root -p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Totally not cool because I tried a half dozen passwords and variations but still my memory was blank. So I did what any self respecting server jockey would do and turned to the infinite information store of the internet knowledge base that is Google.

$ service mysql stop
$ mysqld_safe --skip-grant-tables &

It’s pretty straight forward and very familiar as I have done this more than once before. I’m mean it’s not the first time I’ve recovered a db password, usually it’s for other people. Unfortunately MySql 5.7 behaves a little differently. As evident from this response:

2017-07-14T13:19:38.418474Z mysqld_safe Logging to syslog.
2017-07-14T13:19:38.421452Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-07-14T13:19:38.423940Z mysqld_safe Directory '/var/run/mysqld' for UNIX 
socket file don't exists.

[1]+  Exit 1                  mysqld_safe --skip-grant-tables

So as you might have guessed MySql is taking exception to the missing directory. Once we quietly slip around the problem by creating the missing directory, you can see the mysqld_safe command responded with a pid and some other relatively benign notices.

$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld
$ mysqld_safe --skip-grant-tables &

[1] 974
2017-07-14T13:26:17.290556Z mysqld_safe Logging to syslog.
2017-07-14T13:26:17.293431Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-07-14T13:26:17.310114Z mysqld_safe Starting mysqld daemon with 
databases from /var/lib/mysql

Fantastic! Now we can get to the business of resetting the Mysql 5.7 server root password.

mysql -u root mysql
UPDATE user SET password=PASSWORD('NEW_PASSWORD') WHERE user='root';

NONE of the previously published works cover the nuances of MySql 5.7 Server

Unfortunately MySql had other plans and gave me this lovely perplexing notice in return:

 

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

The fix is elusively simple the good folks in MySql land have eliminated the password column by essentially renaming authentication_string which means we can fix our password reset by simply doing the following;

UPDATE user SET authentication_string=PASSWORD('NEW_PASSWORD')
 WHERE user='root';

 Query OK, 1 row affected, 1 warning (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 1

FLUSH PRIVILEGES;
exit

$ service mysql restart
$ mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> exit

As you can see the essence of resetting the root password in MySql 5.7 Server is the same with some subtle gotchas.