How to Change the MySQL Root Password (Step-by-Step Guide for MySQL 5.6, 5.7, and Later)

1. Importance of Changing the MySQL Root Password

To ensure database security, changing the MySQL root user’s password is extremely important. The root user is the highest-privileged account with full access to the entire database system, and proper management is essential. In particular, using the default configuration without modification poses serious security risks, so it is important to clearly understand why changing the password is necessary.

Benefits of Changing the Root Password

• Improved Security: Helps prevent unauthorized access and protects sensitive data.
• Access Control: Clearly defines who can perform specific operations and allows proper privilege management.
• Database Protection: Properly securing the root user enhances the overall reliability of the database.

2. Prerequisites and Important Checks

The method for changing the password varies depending on the MySQL version, so you should first check your MySQL version. Additionally, before changing the password, understand the potential risk of losing access and consider creating a backup before proceeding.

How to Check the MySQL Version

1. Open the terminal or command prompt and enter the following command:

   mysql --version

This command allows you to verify your MySQL version. In version 5.7 and later, the ALTER USER statement is primarily used, while in earlier versions, the UPDATE statement is recommended.

3. Steps to Change the MySQL Root Password

There are mainly two methods to change the MySQL root password: the ALTER USER command and the SET PASSWORD command. The detailed steps are explained below.

Method 1 – Using the ALTER USER Command

In MySQL 5.7 and later versions, use the following command to change the root user’s password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

• Replace 'new_password' with the password you want to set.
• After running the command, log in to MySQL again to confirm that the password has been changed successfully.

Method 2 – Using the SET PASSWORD Command

In MySQL 5.6 and earlier versions, it is common to use the SET PASSWORD statement as shown below.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

• As with the previous method, it is important to verify the change after setting the password. Also, execute FLUSH PRIVILEGES to apply the changes.
• 

4. Handling Special Situations

If you forget the password or cannot change it using the normal procedure, you can use recovery mode.

Password Reset Procedure When Forgotten

1. Stop the MySQL service:

   sudo service mysql stop

2. Start MySQL with the --skip-grant-tables option to allow access without a password:

   mysqld_safe --skip-grant-tables &

3. Access MySQL as root and set a new password:

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

4. Restart MySQL to apply the changes.

Common Errors and Solutions

Below are common errors and their solutions.

• Access denied: Verify that the username and password are correct.
• skip-grant-tables error: Resolve this by checking and correcting the relevant options in the MySQL configuration file.

5. Additional Settings to Strengthen Security

In addition to changing the root password, further security enhancements are also important.

Disabling Remote Access

Disabling remote access for the root user reduces the risk of external attacks. Run the following command:

UPDATE mysql.user SET Host='localhost' WHERE User='root';

After that, run FLUSH PRIVILEGES to apply the changes.

Tips for Setting a Strong Password

• Recommended password format: Use a combination of uppercase letters, lowercase letters, numbers, and special characters, with at least 12 characters.
• For enhanced security, it is also recommended to update your password regularly.

6. Conclusion

Finally, after changing the MySQL root password, following these best practices will further improve security.

• Remove Unnecessary Accounts: Delete user accounts that no longer require access to keep the system secure and simple.
• Manage Privileges: Create administrative accounts other than root and assign appropriate privileges to reduce potential security risks.
• Perform Regular Audits: Review access logs and configurations regularly to ensure overall system security.