How to Change a MySQL Password Safely: Complete Guide with Security Best Practices

1. Introduction

MySQL is one of the most widely used open-source relational database management systems in database administration. To maintain database security, regularly changing passwords is essential. In particular, passwords for users with administrative privileges are frequent targets of cyberattacks, making periodic updates critical. This article explains in detail the specific methods for safely changing MySQL passwords and outlines best practices to strengthen security.

2. Why You Need to Change Passwords in MySQL

2.1 Changing Passwords as a Security Measure

In recent years, cyberattacks have become increasingly sophisticated, raising the risk of unauthorized database access and data breaches. Regularly changing MySQL passwords helps mitigate these risks. Accounts with administrative privileges are especially high-priority targets for attackers, so setting strong passwords and updating them periodically is strongly recommended.

2.2 Recommended Timing for Password Changes

It is recommended to change passwords at least once every six months. However, system administrators and users managing critical data should consider shorter intervals. Additionally, passwords should be changed immediately when an employee leaves the organization or when there are signs of a potential password leak.

3. Things to Check Before Changing a Password

3.1 Confirm Required Privileges

Before changing a password, ensure that the user has the necessary privileges. In MySQL, the root user or an account with administrative privileges can change other users’ passwords. Even when a regular user changes their own password, it is important to verify that appropriate permissions have been granted.

3.2 Check Your MySQL Version

The command used to change a password depends on the MySQL version. For example, in MySQL 8.0 and later, the ALTER USER command is recommended, while older versions often use SET PASSWORD. First, check your version with the following command:

mysql --version

Some commands may not be available depending on your version, so choose the appropriate method accordingly.

4. How to Change a Password in MySQL

4.1 Changing a Password Using the ALTER USER Command

In MySQL 8.0 and later, you can change a user’s password using the ALTER USER command. This is the most recommended method because it is secure and straightforward. The specific command is shown below:

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

This command changes the target user’s password to the specified new password. After making the change, verify that the user can log in to MySQL using the new password.

4.2 Changing a Password Using the SET PASSWORD Command

In MySQL 5.7 and earlier versions, the SET PASSWORD command is commonly used. The procedure is as follows:

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

Although SET PASSWORD is intended for older versions, it securely generates and stores an encrypted password. However, since SET PASSWORD is not recommended in MySQL 8.0 and later, use ALTER USER if you are running a newer version.

4.3 Changing a Password from the Command Line Using mysqladmin

For system administrators familiar with the command line, using the mysqladmin command is a convenient option. mysqladmin is a MySQL administrative tool, and you can change a password with the following command:

mysqladmin -u username -p password 'new_password'

After running the command, you will be prompted to enter the current password. Enter it and press Enter. This method is also suitable for managing databases in local environments such as XAMPP or WAMP.

5. Password Management Best Practices

5.1 Setting Strong Passwords

Using strong passwords significantly reduces the likelihood of attackers successfully guessing them. A recommended password should be at least 12 characters long and include uppercase letters, lowercase letters, numbers, and symbols. For example, a format like “P@ssw0rd!23” is desirable. Using tools such as password generators to create random strings is also a good practice.

5.2 Setting Password Expiration

To enforce periodic password updates, MySQL allows you to set password expiration policies. The following command requires a user to change their password every 90 days:

ALTER USER 'username'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

This configuration enhances security and prevents the continued use of outdated passwords.

6. Common Issues When Changing Passwords and How to Fix Them

6.1 Resolving Permission Errors

If you encounter a “permission error” while changing a password, the user may not have sufficient privileges. Log in again as the root user or an account with administrative privileges and retry the operation. It can also be helpful to verify permissions in the MySQL configuration file (my.cnf).

6.2 Handling Version Mismatch Errors

If your MySQL version is outdated, using newer commands may cause errors. For example, running the ALTER USER command in MySQL 5.7 may result in an error. In this case, use SET PASSWORD instead or consider upgrading MySQL to the latest version.

7. Conclusion

Changing your MySQL password is a fundamental measure for maintaining database security. Use the three methods introduced in this article—ALTER USER, SET PASSWORD, and mysqladmin—to update passwords properly. It is also important to consistently follow best practices such as setting strong passwords and implementing expiration policies. Finally, be prepared to handle potential issues that may arise during the password change process.