The World of MySQL & MariaDB

Beginner’s Guide: Set, Change & Reset MySQL Passwords

Basic Operations
目次

1. Introduction

The Importance of MySQL Password Management

MySQL is a widely used database management system worldwide, and its key to security management is proper password configuration and operation. Failing to manage passwords properly can expose you to the following risks.
  • Unauthorized access: Setting weak passwords makes you more vulnerable to external attacks.
  • Data leakage: Malicious users could cause confidential information to be exposed.
  • System tampering: Deleting or altering data can affect the normal operation of websites or applications.
To avoid these risks, it is important to set strong passwords and change them regularly. This article provides a detailed explanation of the following topics related to MySQL passwords. ✅ 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 their solutions ✅ Recommended settings to strengthen security By reading this article, you will acquire the knowledge and skills needed to manage MySQL passwords properly, so please read through to the end.

2. How to Set and Change MySQL Passwords

Creating a New User and Setting a Password

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

1. Log in to MySQL

First, log in as a MySQL administrator (such as the root user).
mysql -u root -p
-u root is the option to log in as the root user, and -p is the option to display a prompt for entering the password.

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 to create (newuser) and the host the user can access from (localhost).
  • 'secure_password': the password to set (choose one with appropriate strength).

3. Grant Appropriate Privileges

The new user needs to be granted database operation privileges. For example, to grant all privileges, do the following.
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: also permits granting privileges to other users.

4. Apply the Privileges

Run FLUSH PRIVILEGES to apply the changes.
FLUSH PRIVILEGES;
Now you have created a new user and set an appropriate password and privileges.

Changing the Password of an Existing User

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

1. Log in to MySQL

First, log in with administrative privileges.
mysql -u root -p

2. Changing the 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 change.
  • 'new_secure_password': the new password.

3. Changing the Password Using SET PASSWORD (MySQL 5.6 and earlier)

SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('new_secure_password');
By using the PASSWORD() function, the password can be properly encrypted and stored.

4. Apply the Changes

Be sure to run FLUSH PRIVILEGES;.
FLUSH PRIVILEGES;
With this method, you can safely change the password of an existing user.

3. How to Reset a Forgotten MySQL Password

Even if you forget your MySQL password, you can reset it by following the proper steps. This article explains how to reset the password on Windows, Linux, and Mac environments.

How to Reset MySQL Password on Windows

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

1. Stop the MySQL Service

First, stop the MySQL service that is running 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';
Alternatively, 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 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 to the Reset Method for MySQL 8.0 and Later

In MySQL 8.0, the password management mechanism has changed, and you need to be aware of the following points.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_secure_password';
This allows you to use the traditional password authentication method.

4. Summary of Error Handling When Changing MySQL Passwords

When you try to change a MySQL password, errors can occur. This article explains the causes and solutions for these 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 you try to log in to MySQL and the password is incorrect or the privileges are insufficient.

Solution

  1. Double-check the password you entered
  2. Specify the correct username and hostname
SELECT User, Host FROM mysql.user;
  1. Reset the password (see the password reset section for steps)
  2. Set the privileges correctly
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 list of users
SELECT User, Host FROM mysql.user;
  1. Recreate the user
CREATE USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';
  1. Specify the appropriate host (use localhost or % as needed)

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 set the password expiration to unlimited:
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 method has 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';
Alternatively, add the following to MySQL’s configuration file (my.cnf or my.ini) and restart.
[mysqld]
default_authentication_plugin=mysql_native_password
Restart MySQL:
sudo systemctl restart mysql

5. Strengthening MySQL Security and Recommended Settings

Strengthening MySQL security is important for improving database safety and preventing unauthorized access and data leaks. This article introduces the recommended settings you should implement to enhance MySQL security.

Set Strong Passwords

In MySQL, using simple passwords makes you an easy target for attackers. Let’s apply settings to improve password strength.

Enable Password Policy

Check the current password policy:
SHOW VARIABLES LIKE 'validate_password%';
Set 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 can strengthen security. Check the current password expiration:
SHOW VARIABLES LIKE 'default_password_lifetime';
Set the 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 for the root user:
SELECT User, Host FROM mysql.user WHERE User = 'root';
Disable remote access for the root user:
UPDATE mysql.user SET Host='localhost' WHERE User='root';
FLUSH PRIVILEGES;

Create an Alias for the root Account to Avoid Attacks

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

Modify 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 list of users:
SELECT User, Host FROM mysql.user;
Delete anonymous users:
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;
Delete unnecessary test databases:
DROP DATABASE test;
FLUSH PRIVILEGES;

Enable MySQL Monitoring Logs

Enable the error log:
[mysqld]
log_error = /var/log/mysql/error.log
Enable the query log:
general_log = 1
general_log_file = /var/log/mysql/general.log
Restart MySQL:
sudo systemctl restart mysql

6. Frequently Asked Questions (FAQ)

We have compiled common questions and their solutions regarding MySQL password setup, changes, and resets.

Q1: What is the simplest recovery method if you forget the MySQL root password?

Solution

  1. Stop MySQL
sudo systemctl stop mysql
  1. Start MySQL in skip-grant-tables mode
sudo mysqld_safe --skip-grant-tables --skip-networking &
  1. Log in to MySQL
mysql -u root
  1. Set a new password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';
  1. Restart MySQL
sudo systemctl restart mysql

Q2: How to address the “ERROR 1045 (28000): Access denied for user” message?

Solution

  1. Verify that the entered password is correct
mysql -u root -p
  1. Check that the user has been created correctly
SELECT User, Host FROM mysql.user;
  1. Reset the password (see the steps above)
  2. Set privileges appropriately
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Q3: Are there differences in how to change passwords between MySQL 5.6 and 8.0?

Password change for MySQL 5.6 and earlier

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

Password change for MySQL 8.0 and later

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';
Also, because MySQL 8.0 uses caching_sha2_password as the default authentication method, you should switch to the following if you need compatibility with older clients.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_secure_password';

Q4: How can I strengthen MySQL’s password policy?

Setting 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 user
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION;
  1. Disable the root account
RENAME USER 'root'@'localhost' TO 'disabled_root'@'localhost';
FLUSH PRIVILEGES;

Q6: Are there tools for securely managing 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 entering the password.
mysql --login-path=local
  1. Password managers (Bitwarden, 1Password, KeePass, etc.)
  • Convenient for generating and managing secure passwords.

Q7: Can password changes in MySQL be logged?

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. Summary

In this article, we explained knowledge about MySQL password management from basics to advanced topics. Finally, we organized the key points and provide a checklist for proper MySQL password management.

Key Points for MySQL Password Management

✅ Proper password setting and management are the foundation of security measures ✅ Set strong passwords when creating new users ✅ Change passwords regularly and set expiration dates ✅ Understand recovery procedures for forgotten passwords ✅ Properly handle errors that occur during password changes ✅ Optimize remote connections and root user management to strengthen security ✅ Enable logging to track password change history

MySQL Password Management Checklist

Checklist ItemStatus
Is the MySQL root password set to a strong one?✅ / ❌
Do you use the CREATE USER command when creating new users?✅ / ❌
Is the validate_password plugin enabled and are you using strong passwords?✅ / ❌
Do you change passwords regularly and set expiration dates?✅ / ❌
Have you disabled remote access for the root user?✅ / ❌
Have you removed unnecessary user accounts and anonymous accounts?✅ / ❌
Do you understand how to reset passwords using skip-grant-tables mode?✅ / ❌
Do you know how to handle errors such as ERROR 1045 and ERROR 1820 when changing passwords?✅ / ❌
Have you enabled MySQL logs (e.g., general_log and error_log)?✅ / ❌

Next Actions Based on This Article

  1. Review the current password policy and strengthen it
SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
  1. Delete unnecessary accounts
DELETE FROM mysql.user WHERE User='';
DROP DATABASE test;
FLUSH PRIVILEGES;
  1. Restrict remote access for the root user
UPDATE mysql.user SET Host='localhost' WHERE User='root';
FLUSH PRIVILEGES;
  1. Set up regular password changes
SET GLOBAL default_password_lifetime = 90;
  1. Enable logging and 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

📌 Details of MySQL User Privilege Management 📌 MySQL Backup and Restore Procedures 📌 Methods for MySQL Database Optimization 📌 Configuring MySQL with External Authentication (LDAP and OAuth)

Conclusion

MySQL password management is essential knowledge for protecting database security. Put the contents of this article into practice and ensure secure data management! 💪