How to reset MySQL database root password

Question: I forgot the MySQL root password. How do I reset it?

# mysql -u root
Access denied for user 'root'@'localhost' (using password: NO)'

Solution:

The MySQL root password can be set using the following procedure:

1. Stop the MySQL service:

# service mysqld stop
Stopping MySQL:                                            [  OK  ]

2. Start MySQL in safe mode as it does not ask for root password in safe mode.

# /usr/bin/mysqld_safe --skip-grant-tables &

Note: mysql_safe is a shell script which invokes mysqld, but additionally traps any forceful terminations of the MySQL server and avoids any database corruption.

3. Change the password of the root user:

# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

4. Restart mysqld using mysqladmin to ensure that the service shuts down successfully (as it was started manually in step 2).

# mysqladmin -p shutdown

5. Restart the MySQL service as per normal:

# service mysqld start

Question: I forgot the MySQL root password. How do I reset it?

Solution:

You May Also Like