How To Recover or Reset Lost MySQL Password On Linux?

I have some PHP applications that run with the MySQL database server. I have set up MySQL database a long time ago and I have forgotten root password of MySQL server. How can I recover and reset the MySQL Server password? One way to reset MySQL password is stopping MySQL daemon. Starting it with recovery mode without authentication. Login with root and make required password changes.

Stop MySQL Server Daemon

MySQL server locks databases during running. So we need to release the lock by stopping MySQL service.

$ sudo /etc/init.d/mysql stop

Start MySQL Server Daemon Recovery Mode

We will start MySQL server daemon in recovery mode so there will be on authentication to connect MySQL server daemon. We need root privileges to accomplish these operations.

$ sudo mysqld_safe --skip-grant-tables &

Connect MySQL Server Without Password

We can connect to MySQL server without password just providing user name root like below.

$ mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 2 
Server version: 5.7.16-0ubuntu0.16.10.1 (Ubuntu) 
 
Copyright (c) 2000, 2016, 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>

Set New Password For Root User

We will set the password by updating users tables password column

USE mysql;
UPDATE USER SET password=PASSWORD("Thisismypassword") WHERE USER='root';
FLUSH privileges;
quit

We will set a new password for the root user.

Start MySQL Server Daemon Normally Again

We will start MySQL daemon again.

$ /etc/init.d/mysql restart

Leave a Comment