The World of MySQL & MariaDB

Change MySQL User Passwords: Beginner Guide & Troubleshooting

Users and Permissions
目次

1. Introduction

MySQL is a database management system used in many web applications and systems. Among its functions, managing user passwords is a crucial security task. In particular, user password changes are required in situations such as the following.
  • Regular changes aimed at strengthening security.
  • Resetting when a password is forgotten.
  • When an authorized administrator needs to update all users’ passwords to prevent unauthorized access.
This article explains the step-by-step process for changing MySQL user passwords in a way that’s easy for beginners to understand. It also covers troubleshooting tips and FAQs, so please read through to the end.

2. MySQL User Password Change Procedure

MySQL provides several ways to change a user’s password. This explains each method and its applicable scenarios.

Basic Password Change Method: ALTER USER Command

The most common approach is to use the ALTER USER command. This method is recommended for MySQL 5.7 and later.

Command Example:

ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';
  • username: The user name whose password will be changed.
  • host: The host from which the user connects (usually localhost).
  • newpassword: The new password.

Example Usage:

For example, to change the password of a user named testuser to secure123!, write as follows.
ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'secure123!';
Executing this command updates the specified user’s password immediately.

Changing Password Using the SET PASSWORD Command

Another method involves using the SET PASSWORD command. This approach may be used in MySQL versions prior to 5.7 or in certain situations.

Command Example:

SET PASSWORD FOR 'username'@'host' = 'newpassword';

Example Usage:

The following command changes the password of the admin user to NewPass123.
SET PASSWORD FOR 'admin'@'localhost' = 'NewPass123';
While this method is flexible, the ALTER USER command is recommended, so you should generally use the latter.

Reset Procedure When a Password Is Forgotten

If a MySQL user forgets their password, special steps are required.

Steps:

  1. Stop the MySQL server:
   sudo systemctl stop mysql
  1. Start MySQL in safe mode:
   mysqld_safe --skip-grant-tables &
  1. Reset the password:
   UPDATE mysql.user SET authentication_string=PASSWORD('newpassword') WHERE User='username';
   FLUSH PRIVILEGES;
  1. Restart the MySQL server:
   sudo systemctl restart mysql
These steps allow you to set a new password. However, because this process poses security risks, it is recommended to return to normal mode immediately after completion.

3. Password Policy and Security Considerations

When using MySQL, ensuring password security is extremely important. To prevent security vulnerabilities, it is recommended to set a strong password policy and perform regular changes. This section explains MySQL’s password policy features and security measures.

MySQL Password Policy Configuration

MySQL provides the validate_password plugin to control password strength. Enabling this plugin can improve the security of passwords set by users.

Enabling the validate_password Plugin

Enable the plugin with the following command.
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

Configuring the Password Policy

With the validate_password plugin, you can set policies such as the following.
  1. Password Length (length) specify the minimum number of characters.
   SET GLOBAL validate_password.length = 12;
  1. Character Requirements (policy) set the password complexity. There are three levels.
  • LOW: No character type restrictions
  • MEDIUM: Includes numbers, uppercase, lowercase, and special characters
  • STRONG: In addition to the above, prohibits dictionary-based words
   SET GLOBAL validate_password.policy = 'MEDIUM';
  1. Character Type Variety (mixing) a setting that requires specific character types.
   SET GLOBAL validate_password.mixed_case_count = 1;
Applying these settings enforces passwords that are difficult to guess.

Criteria for Strong Passwords

To ensure password security, it is recommended to meet the following criteria.
  • Length of at least 12 characters.
  • Combination of uppercase, lowercase, numbers, and special characters.
  • Avoid dictionary words or personal information (e.g., name, birthday).
  • Introduce randomness (use online password generators).

Recommended Example:

MysqL@2025!Secure Such a password is extremely difficult to guess and resistant to brute-force attacks.

Importance of Regular Password Changes

To operate the system safely over the long term, adopt policies such as the following.
  1. Set a regular change schedule It is recommended to change passwords at least every six months.
  2. Prevent reuse of old passwords Use the validate_password plugin to enable history checks.
   SET GLOBAL validate_password.reuse_history = 5;
  1. Utilize audit logs Use MySQL’s audit features to monitor unauthorized password changes and login attempts.

4. Troubleshooting

When changing a user’s password in MySQL, various errors and issues can arise. This section explains common problems and their solutions.

Errors that occur when changing passwords and how to address them

Error 1: Password does not meet policy requirements

Error example:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
Cause: Occurs when a password that does not satisfy the policy of the validate_password plugin is specified. Solution:
  1. Check the current policy settings:
   SHOW VARIABLES LIKE 'validate_password%';
  1. Temporarily relax the policy (e.g., set the policy to LOW):
   SET GLOBAL validate_password.policy = 'LOW';
  1. Specify a password that meets the policy.

Error 2: Insufficient privileges to change the password

Error example:
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
Cause: Occurs when the user lacks the necessary privileges. Solution:
  1. Check the privileges:
   SHOW GRANTS FOR 'username'@'host';
  1. Grant the required privileges:
   GRANT ALTER USER ON *.* TO 'username'@'host';
   FLUSH PRIVILEGES;

Error 3: Unable to log in after changing the password

Cause:
  • The encryption method differs when the password was changed.
  • The host name is not set correctly.
Solution:
  1. Verify the password encryption method:
   SELECT plugin FROM mysql.user WHERE User='username';
  • In MySQL 5.7, mysql_native_password is common.
  • In MySQL 8.0 and later, caching_sha2_password is the default.
  1. Change the encryption method:
   ALTER USER 'username'@'host' IDENTIFIED WITH 'mysql_native_password' BY 'newpassword';
  1. Verify the host configuration:
   SELECT Host FROM mysql.user WHERE User='username';
Modify the host as needed:
   UPDATE mysql.user SET Host='%' WHERE User='username';
   FLUSH PRIVILEGES;

Other common issues

Can still log in with the old password after changing it

Cause: The privileges are not reloaded (FLUSH PRIVILEGES) after the password change. Solution: After changing the password, be sure to run the following.
FLUSH PRIVILEGES;

Password change does not take effect

Cause: The MySQL configuration file may not have been read correctly. Solution: Restart MySQL to reload the configuration.
sudo systemctl restart mysql

5. FAQ (Frequently Asked Questions)

We have compiled below the most common questions and answers from readers regarding changing MySQL user passwords. This section provides concrete advice useful in real-world operations.

Q1. How to handle a situation where a user cannot log in after a password change?

Causes:

  1. The password is incorrect.
  2. The host name is not set correctly.
  3. The password encryption method is different.

Solution:

  1. Reconfirm the password: Verify that the password was changed in the correct format.
   SELECT User, Host FROM mysql.user WHERE User='username';
  1. Check and correct the host name:
   UPDATE mysql.user SET Host='%' WHERE User='username';
   FLUSH PRIVILEGES;
  1. Set the encryption method appropriately:
   ALTER USER 'username'@'host' IDENTIFIED WITH 'mysql_native_password' BY 'newpassword';

Q2. How to allow password changes only for specific users?

Solution:

In MySQL, you can permit password changes by granting the ALTER USER privilege to specific users.
  1. Grant the privilege:
   GRANT ALTER USER ON *.* TO 'username'@'host';
   FLUSH PRIVILEGES;
  1. Verify the privilege:
   SHOW GRANTS FOR 'username'@'host';
With this configuration, you can restrict users to only change passwords without granting any other privileges.

Q3. How to check password change history?

Solution:

By default, MySQL does not have a password history feature, but you can track changes using audit logs or custom scripts.
  1. Enable audit logging: In MySQL Enterprise Edition, the audit log plugin is available.
   INSTALL PLUGIN audit_log SONAME 'audit_log.so';
  1. Check log files: Configure standard logging to track change history.
   tail -f /var/log/mysql/mysql.log

Q4. What happens if you don’t change the password?

Security Risks:

  1. Increased risk of unauthorized access Using easily guessed or old passwords raises the likelihood of an attacker gaining entry.
  2. Compliance violations In some industries, regular password changes are mandatory.

Recommendations:

  • Change passwords at least every six months.
  • Use the validate_password plugin to enforce strong passwords.

Q5. Why are all sessions disconnected when a password is changed?

Cause:

In MySQL, when a user’s password is changed, all active sessions for that user are forcibly terminated. This is a security feature.

Solution:

When reconnecting, use the new password. Also, it is recommended to perform the change during off-peak hours to avoid impacting operations.

6. Summary

Changing MySQL user passwords is an essential task for maintaining database security. This article detailed the specific steps for changing passwords, security considerations, and also covered troubleshooting and FAQs.

Key Points

  1. Explanation of Basic Password Change Methods
  • Explain the concrete steps using the ALTER USER and SET PASSWORD commands.
  • Also covers password reset methods.
  1. Setting Password Policies to Strengthen Security
  • Introduce how to use the validate_password plugin to enforce strong passwords.
  1. Providing Troubleshooting and FAQs
  • Offer concrete solutions for errors and issues that arise.
  • Address additional questions with practical FAQs.

The Importance of Regular Password Management

  • Regularly changing passwords helps reduce the risk of unauthorized access and data leaks.
  • Combine strong passwords with security settings to improve overall system safety.

Next Steps

  • Refer to the MySQL official documentation If you need more detailed information, please use the MySQL official manual.
  • Strengthen other security settings Consider measures such as reviewing user privileges and configuring audit logs to further enhance security.
Based on this content, let’s manage MySQL users more securely. We hope this article helps readers with their system administration!

Reference Sites

MySQL :: MySQL 8.0 リファレンスマニュアル

…

MySQL :: MySQL Documentation

…