My system is as follows:

  • Ubuntu 16.04.4 LTS
  • Mysql 5.7.22

Stop the mysql process:

sudo /etc/init.d/mysql stop

Use mysqld_safe using the –skip-grant-tables option to start the process:

sudo mysqld_safe --skip-grant-tables &

If you get an error about: mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file doesn’t exist, check 2 things.

  1. If you have a /var/run/mysqld directory, if not, make one and change the permissions with chown.
  2.  In the /etc/mysql/ directory, there is a file called, my.cnf open it and add the following:
sudo chown mysql:mysql /var/run/mysqld
[mysqld]
port=3306
socket=/var/run/mysqld/mysql.sock

Save and close this file and re-run sudo mysqld_safe –skip-grant-tables &

You should see a message that reads: mysqld_safe Started mysqld daemon with databases from /var/lib/mysql

Output from mysqld_safe command

Open another terminal and log into mysql

mysql -u root

Now you can reset your password. Run these commands:

mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD('pass123') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

If you get an error when running the UPDATE user command that reads: ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’, do this next.
Assuming this is for the mysql database:

mysql> use mysql;
mysql> show tables;

List of tables in mysql
If you don’t see a row called password, but there is a user field, run this:

mysql> describe user;

 

List of sql fields in user table

There is a row called ‘authentication_string’ that holds the password.

You will need to run this command to reset the password. Just like the command above, but referencing the correct field in the user table.

mysql> use mysql;
mysql> UPDATE user SET authentication_string=PASSWORD('pass123') WHERE user='root';
FLUSH PRIVILEGES;
quit

Log back in and test your new mysql password

 mysql -u root -p