Simplest Way To Reset Mysql Root Password – CentOs, Fedora, RHEL, Debian, Ubuntu.

Published by on | Leave a response

mysql

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.

Posted in

Leave a Reply

}