The World of MySQL & MariaDB

How to Change the MySQL Root Password Securely (Step-by-Step Guide)

Users and Permissions
目次

1. Importance of Changing the MySQL Root Password

Changing the MySQL root user password is crucial for ensuring database security. The root user is the highest-privileged account with full access to the entire database, and proper management is essential. Especially, using the default settings poses serious security risks, so it is critical to understand why changing the password is necessary.

Benefits of Changing the Root Password

  • Enhanced Security: Prevents unauthorized access and helps protect your data.
  • Access Control: Clearly defines who can perform what operations and enables proper privilege management.
  • Database Protection: Safely managing the root user improves the overall reliability of the database.

2. Preparation and Prerequisites

The method for changing the password varies depending on the MySQL version, so you should first check your MySQL version. Also, before making any changes, it is recommended to back up your data and understand the risks of restricted access.

How to Check the Version

  1. Open a terminal or command prompt and enter the following command:
   mysql --version
This command will display your MySQL version. For MySQL 5.7 and later, the ALTER USER statement is generally used, while for earlier versions the UPDATE or SET PASSWORD statements are recommended.

3. Steps to Change the MySQL Root Password

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

Method 1 – Using ALTER USER

For MySQL 5.7 and later versions, you can use the following command to change the root user password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
  • Replace 'new_password' with your desired password.
  • After running the command, log in to MySQL again to verify that the change was successful.

Method 2 – Using SET PASSWORD

For MySQL 5.6 and earlier versions, it is common to use the SET PASSWORD statement:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
  • As with the first method, it is important to verify the change. Additionally, run FLUSH PRIVILEGES to apply the update.

4. Handling Special Situations

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

Steps to Reset a Forgotten Password

  1. Stop the MySQL service:
   sudo service mysql stop
  1. Start MySQL with the --skip-grant-tables option to allow access without a password:
   mysqld_safe --skip-grant-tables &
  1. Log in to MySQL as root and set a new password:
   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
  1. Restart MySQL to apply the changes.

Common Errors and Solutions

Here are some common errors and their fixes:
  • Access denied: Check that the username and password are correct.
  • skip-grant-tables error: Review and adjust the related options in the MySQL configuration file.

5. Additional Security Enhancements

Beyond changing the root password, it is also important to strengthen security further.

Disabling Remote Access

You can reduce external access risks by disabling root remote access. Run the following command:
UPDATE mysql.user SET Host='localhost' WHERE User='root';
Afterwards, run FLUSH PRIVILEGES to apply the change.

Tips for Creating Strong Passwords

  • Recommended password format: use a mix of uppercase letters, lowercase letters, numbers, and special characters, with at least 12 characters in total.
  • For better security, it is also advisable to update passwords periodically.

6. Conclusion

Finally, after changing the MySQL root password, following these best practices will further strengthen your database security:
  • Remove unnecessary accounts: Delete unused user accounts to keep the system simple and secure.
  • Manage privileges: Create administrative accounts other than root and assign appropriate privileges to mitigate security risks.
  • Conduct regular audits: Review access logs and configurations to ensure the overall security of your system.