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.
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)'
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;
Now Restart mysqld first use mysqladmin to ensure that the service shutdown successfully to avoid any other issue.
Now restart the MySQL service as per normal:
- 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.