MySQL Password Management Guide: How to Set, Change, Reset, and Secure Passwords (Windows, Linux, Mac)

1. Introduction

The Importance of MySQL Password Management

MySQL is a widely used database management system across the world. However, the foundation of its security management lies in proper password configuration and administration. Without appropriate password management, you may face the following risks:

• Unauthorized Access: Weak passwords make your system vulnerable to external attacks.
• Data Breach: Sensitive information may be exposed by malicious users.
• System Tampering: Deletion or modification of data may affect the normal operation of websites and applications.

To avoid these risks, it is essential to set strong passwords and change them regularly. In this article, we will explain the following aspects of MySQL password management in detail:

✅ How to set passwords for new users
✅ How to change passwords for existing users
✅ How to reset a forgotten password
✅ How to check password strength
✅ Common errors and how to resolve them
✅ Recommended settings to enhance security

By reading this article, you will gain the knowledge and skills necessary to properly manage MySQL passwords, so be sure to read through to the end.

2. How to Set and Change MySQL Passwords

Creating a New User and Setting a Password

This section explains how to create a new user and set a password in MySQL.

1. Log in to MySQL

First, log in as a MySQL administrator (such as the root user).

mysql -u root -p

-u root is an option to log in as the root user, and -p displays a password prompt.

2. Create a New User and Set a Password

Use the CREATE USER command to create a new user and set a password.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'secure_password';

• 'newuser'@'localhost': Specifies the username (newuser) and the host (localhost) from which the user can connect.
• 'secure_password': The password to set (make sure to use a strong password).

3. Grant Appropriate Privileges

You must grant database privileges to the new user. For example, to grant all privileges:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;

• ALL PRIVILEGES: Grants all privileges.
• *.*: Allows access to all databases and tables.
• WITH GRANT OPTION: Allows the user to grant privileges to other users.

4. Apply the Privileges

Execute FLUSH PRIVILEGES to apply the changes.

FLUSH PRIVILEGES;

You have now successfully created a new user with a secure password and appropriate privileges.

Changing the Password of an Existing User

This section explains how to change the password of an existing MySQL user.

1. Log in to MySQL

Log in with administrative privileges.

mysql -u root -p

2. Change Password Using ALTER USER (MySQL 5.7 and Later)

ALTER USER 'existinguser'@'localhost' IDENTIFIED BY 'new_secure_password';

• 'existinguser'@'localhost': The username and host to modify.
• 'new_secure_password': The new password.

3. Change Password Using SET PASSWORD (MySQL 5.6 and Earlier)

SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('new_secure_password');

Using the PASSWORD() function ensures that the password is properly encrypted before being stored.

4. Apply the Changes

Don’t forget to execute FLUSH PRIVILEGES;.

FLUSH PRIVILEGES;

You can now securely change the password of an existing user using this method.

3. How to Reset a MySQL Password If You Forgot It

Even if you forget your MySQL password, you can reset it by following the correct procedure. This section explains how to reset passwords on Windows, Linux, and Mac.

How to Reset a MySQL Password on Windows

On Windows, a common approach to reset the MySQL root password is to use the skip-grant-tables option.

1. Stop the MySQL Service

First, stop the running MySQL service on Windows.

net stop mysql

Alternatively, you can open services.msc and manually stop the MySQL service.

2. Start MySQL Using mysqld --skip-grant-tables

mysqld --skip-grant-tables --skip-networking

3. Log in to MySQL

mysql -u root

4. Set a New Password

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

Or, for MySQL 5.6 and earlier, use SET PASSWORD.

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

5. Restart MySQL

net stop mysql
net start mysql

How to Reset a MySQL Password on Linux / Mac

1. Stop the MySQL Service

sudo systemctl stop mysql

2. Start MySQL in --skip-grant-tables Mode

sudo mysqld_safe --skip-grant-tables --skip-networking &

3. Log in to MySQL

mysql -u root

4. Reset the Password

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

5. Restart MySQL

sudo systemctl start mysql

Changes in Password Reset Methods in MySQL 8.0 and Later

In MySQL 8.0, the password management mechanism has changed, so you should note the following point.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_secure_password';

This allows you to continue using the legacy password authentication method when needed.

4. Troubleshooting Common Errors When Changing MySQL Passwords

You may encounter errors when attempting to change MySQL passwords. This section explains the causes and solutions for common errors.

ERROR 1045 (28000): Access denied for user

Cause of the Error

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

This error occurs when logging in to MySQL if the password is incorrect or privileges are insufficient.

Solution

1. Re-check the password you entered
2. Specify the correct username and host

SELECT User, Host FROM mysql.user;

3. Reset the password (see the password reset section)
4. Configure privileges properly

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

ERROR 1133: Can’t find any matching row in the user table

Cause of the Error

ERROR 1133: Can't find any matching row in the user table

This error occurs when the specified user does not exist.

Solution

1. Check the current user list

SELECT User, Host FROM mysql.user;

2. Recreate the user

CREATE USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';

3. Specify the correct host (use localhost or % appropriately)

ERROR 1820: You must reset your password

Cause of the Error

ERROR 1820: You must reset your password using ALTER USER statement before executing this statement.

This error occurs when the password has expired.

Solution

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

To disable password expiration:

SET GLOBAL default_password_lifetime = 0;

ERROR 2059: Plugin caching_sha2_password could not be loaded

Cause of the Error

ERROR 2059: Plugin caching_sha2_password could not be loaded

In MySQL 8.0 and later, the default authentication plugin changed to caching_sha2_password, which can cause errors with some clients.

Solution

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_secure_password';

Or add the following to the MySQL configuration file (my.cnf or my.ini) and restart MySQL.

[mysqld]
default_authentication_plugin=mysql_native_password

Restart MySQL:

sudo systemctl restart mysql

5. MySQL Security Hardening and Recommended Settings

Strengthening MySQL security is essential to improve database safety and prevent unauthorized access and data breaches. This section introduces recommended configurations to enhance MySQL security.

Set Strong Passwords

Using weak passwords in MySQL makes your system an easy target for attackers. Configure settings to increase password strength.

Enable a Password Policy

Check the current password policy:

SHOW VARIABLES LIKE 'validate_password%';

Configure a strong password policy:

SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Set Password Expiration

Regularly changing passwords enhances security.

Check the current password expiration setting:

SHOW VARIABLES LIKE 'default_password_lifetime';

Set password expiration to 90 days:

SET GLOBAL default_password_lifetime = 90;

Strengthen root User Security

Restrict Remote Access for the root User

Check the current host configuration for the root user:

SELECT User, Host FROM mysql.user WHERE User = 'root';

Disable remote access for root:

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

Create an Alternative Admin Account and Disable root

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;
RENAME USER 'root'@'localhost' TO 'disabled_root'@'localhost';
FLUSH PRIVILEGES;

Restrict MySQL Remote Connections

Edit the configuration file (my.cnf or my.ini):

[mysqld]
bind-address = 127.0.0.1

Configure the firewall on Linux:

sudo ufw deny 3306

Allow only a specific IP address:

sudo ufw allow from 192.168.1.100 to any port 3306

Remove Unnecessary Accounts and Privileges

Check the current user list:

SELECT User, Host FROM mysql.user;

Remove anonymous users:

DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

Remove the unnecessary test database:

DROP DATABASE test;
FLUSH PRIVILEGES;

Enable MySQL Logging

Enable the error log:

[mysqld]
log_error = /var/log/mysql/error.log

Enable the general query log:

general_log = 1
general_log_file = /var/log/mysql/general.log

Restart MySQL:

sudo systemctl restart mysql

6. Frequently Asked Questions (FAQ)

This section summarizes common questions and solutions related to MySQL password setup, modification, and reset.

Q1: What is the easiest way to recover a forgotten MySQL root password?

Solution

1. Stop MySQL

sudo systemctl stop mysql

2. Start MySQL in skip-grant-tables mode

sudo mysqld_safe --skip-grant-tables --skip-networking &

3. Log in to MySQL

mysql -u root

4. Set a new password

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

5. Restart MySQL

sudo systemctl restart mysql

Q2: What should I do if I see “ERROR 1045 (28000): Access denied for user”?

Solution

1. Verify the password you entered

mysql -u root -p

2. Confirm that the user exists

SELECT User, Host FROM mysql.user;

3. Reset the password (see previous instructions)
4. Grant appropriate privileges

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Q3: Is there a difference between MySQL 5.6 and 8.0 when changing passwords?

Changing Password in MySQL 5.6 and Earlier

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

Changing Password in MySQL 8.0 and Later

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

In MySQL 8.0, since caching_sha2_password is the default authentication method, older clients may require the following adjustment:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_secure_password';

Q4: How can I strengthen the MySQL password policy?

Configure a Strong Password Policy

SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Q5: Is it safe to disable the MySQL root user?

Solution

1. Create an administrator account

CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

2. Disable the root account

RENAME USER 'root'@'localhost' TO 'disabled_root'@'localhost';
FLUSH PRIVILEGES;

Q6: Are there tools to securely manage MySQL passwords?

Solution

1. mysql_config_editor (Official MySQL Tool)

mysql_config_editor set --login-path=local --host=localhost --user=root --password

This allows you to log in without explicitly typing the password.

mysql --login-path=local

2. Password Managers (Bitwarden, 1Password, KeePass, etc.)

• Useful for generating and securely storing strong passwords.

Q7: Can I log MySQL password changes?

Solution

Enable the General Query Log

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

Restart MySQL:

sudo systemctl restart mysql

7. Conclusion

This article explained everything from the basics to advanced techniques of MySQL password management. Below is a summary of the key points along with a checklist to properly manage MySQL passwords.

Key Points of MySQL Password Management

✅ Proper password configuration and management are the foundation of security
✅ Set strong passwords when creating new users
✅ Change passwords regularly and configure expiration policies
✅ Understand recovery procedures for forgotten passwords
✅ Properly troubleshoot errors during password changes
✅ Optimize remote access and root user management for security hardening
✅ Enable logging to track password-related activities

MySQL Password Management Checklist

Recommended Next Actions After Reading This Article

1. Review your current password policy and strengthen it if necessary

SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;

2. Remove unnecessary accounts

DELETE FROM mysql.user WHERE User='';
DROP DATABASE test;
FLUSH PRIVILEGES;

3. Restrict remote access for the root user

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

4. Configure regular password expiration

SET GLOBAL default_password_lifetime = 90;

5. Enable logging for monitoring

[mysqld]
log_error = /var/log/mysql/error.log
general_log = 1
general_log_file = /var/log/mysql/general.log

Restart MySQL:

sudo systemctl restart mysql

Related Articles for Further Learning

📌 Detailed Guide to MySQL User Privilege Management
📌 MySQL Backup and Restore Procedures
📌 How to Optimize MySQL Databases
📌 Configuring MySQL with External Authentication (LDAP or OAuth)

Final Thoughts

MySQL password management is essential knowledge for protecting database security.
Apply the practices covered in this guide and ensure secure database management! 💪