How to reset MySQL root password on Ubuntu

When you install mysql database server on ubuntu, you will prompted to enter password for user root, if you skip this password or leave it blank, you are out of luck my friend, because you can't login to mysql database server.

This is what happened to me a few days ago, during the installation of mysql server (version 5.7.16 on ubuntu 16.04), i skip entering the password, i was hopping i could login without password for user root, but it doesn't seems to work.

The problem is on mysql 5.7.x if you set empty password during installation, mysql will use auth_socket plugin, auth_socket plugin doesn't need password, but you only can login through UNIX socket connection, therefore any connection using local IP or network will fail.

How to solve this problem?

The solution is to change mysql root password and not leave it blank/empty, first we need to find a way to login to mysql using user other than root (because wa can't login using root at this point).

On ubuntu 16.04, if you look at /etc/mysql/debian.cnf, you will see user called 'debian-sys-maint' and it's password, use that to login into mysql server.

sudo cat /etc/mysql/debian.cnf

Use the username and password on that file to login into mysql

sudo mysql -u debian-sys-maint -h localhost -p

Inside mysql database run this command to reset password for user root:

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpass';

NOTE: change 'newpass' to whatever you want, this will be your root password.

This method works well on mysql server version 5.7.16, ubuntu 16.04

Thank you and have a nice day