You can recover MySQL database server password with following five easy steps.
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new mysql root account password i.e. reset mysql password.
Step # 5: Exit and restart the MySQL server.
Here are commands you need to type for each step (login as the root user):
Step # 1 : Stop mysql service
The first thing to do is stop MySQL. If you are using Ubuntu or Debian the command is as follows:
sudo /etc/init.d/mysql stop
For CentOS, Fedora, and RHEL the command is:
sudo /etc/init.d/mysqld stop
Output:
Stopping MySQL database server: mysqld.
Step # 2: Start to MySQL server w/o password: (Safe Mode)
Next we need to start MySQL in safe mode – that is to say, we will start MySQL but skip the user privileges table. Again, note that you will need to have sudo access for these commands so you don’t need to worry about any user being able to reset the MySQL root password:
# mysqld_safe --skip-grant-tables &
Output:
[1] 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[6025]: started
Step # 3: Connect to mysql server using mysql client:
All we need to do now is to log into MySQL and set the password.
# mysql -u root
Output:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Note: No password is required at this stage as when we started MySQL we skipped the user privileges table.
Step # 4: Setup new MySQL root user password
mysql> use mysql;
(Enter the new password for the root user as follows:)
mysql> update user set password=PASSWORD(“new-password”) where User=’root’;
(and finally, flush the privileges:)
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[6186]: ended [1]+ Done mysqld_safe --skip-grant-tables
Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p
You will be prompted for your new password.