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)
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