Reset MySQL “root” Password from terminal

It’s very helpful in case I don’t remember MySQL root password at all neither any other account that have root privileges.

Here are all the steps:

Open terminal then stop MySQl server

sudo service mysql stop

Start MySQL server with option: skip-grant-tables

sudo mysqld_safe --skip-grant-tables &

if the following error occur:

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists

create directory /var/run/mysqld manually then change it’s ownership to mysql user

sudo mkdir /var/run/mysqld && sudo chown mysql:mysql /var/run/mysqld

Connect to the MySQL server using root but with empty password

mysql -uroot

by running the above command I should now be able to get into MySQL command line interface (cli)

MySQL Command Line Interface

Reset root password

in this scenario I will reset root password into blank

mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("") where User='root';
mysql> update user set plugin="mysql_native_password" where User='root';
mysql> flush privileges;
mysql> quit

Restart MySQL Server

sudo service mysql stop
sudo service mysql start

notes: this article has been tested with Ubuntu Xenial and MySQL Server 5.7.22