How to reset my MySQL database root password?

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 :

  1. The first Step stop the MySQL service:
    # service mysqld stop
    Stopping MySQL: [ OK ]
  2. 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. 

  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.   Now Restart mysqld first use mysqladmin to ensure that the service shutdown successfully to avoid any other issue.

    # mysqladmin -p shutdown

  5. 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top