How to reset my MySQL database root password?
This is a very common issue if you are working in a big environment or a system where everything is automated. People always forgot their MySQL password. This article will help you recover your password without any data corruption.
Environment
-
Red Hat Enterprise Linux including:
- Red Hat Enterprise Linux 3
- Red Hat Enterprise Linux 4
- Red Hat Enterprise Linux 5
- Red Hat Enterprise Linux 6
- Red Hat Enterprise Linux 7
- Red Hat Enterprise Linux 8
-
MySQL-server
Issue
Forgot the MySQL root password. And you want to know how to reset your password. How do I reset the mysql-server password?
# mysql -u root'
Access denied for user 'root'@'localhost' (using password: NO)'
Resolution
To reset the MySQL root password We have to follow the below procedure :
- The first Step stop the MySQL service:
# service mysqld stop
Stopping MySQL: [ OK ] -
Use the below command to start MySQL :
# /usr/bin/mysqld_safe --skip-grant-tables &
Note: On RHEL 3, mysqld_safe was called safe_mysqld:
# /usr/bin/safe_mysqld --skip-grant-tables &
Note: mysql_safe is a shell script this will only invoke mysqld but additionally traps any forceful terminations of the MySQL server and this will avoid any database corruption.
-
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; -
Now Restart mysqld first use mysqladmin to ensure that the service shutdown successfully to avoid any other issue.
# mysqladmin -p shutdown
-
Now restart the MySQL service as per normal:
# service mysqld start
Root Cause
- The root password for mysqld was forgotten or lost.
- You are a new admin and the old admin did not handover passwords.
- Other team members reset the password and missed it.
- Use of file to create a new server but MySQL password is not mentioned in doc.