The World of MySQL & MariaDB

MySQL Password Check: Initial, Reset & Troubleshooting Guide

Basic Operations
目次

1. Introduction

MySQL is an essential tool used in many web applications and database systems. However, it’s not uncommon to find yourself in situations during operation where you’ve “forgotten the password” or “don’t know the initial password.” Such issues can have a significant impact on business operations and database security. In this article, we’ll explain in detail how to check MySQL’s initial password and how to reset it if you’ve forgotten it, in a way that’s easy for beginners to understand. We also provide steps for both Linux and Windows environments, so readers can find information that matches their actual setup. Furthermore, the latter part of the article covers how to review and change password policies, as well as how to address common errors. This gives you comprehensive knowledge for operating MySQL more securely and efficiently. By reading this article, you’ll understand and be able to apply the following.
  • How to check MySQL’s initial password
  • Reset procedures when the password is forgotten (Linux and Windows compatible)
  • Troubleshooting and how to set password policies
Please read through to the end and set up an environment where you can use MySQL with confidence.

2. How to Check the Initial MySQL Password

When you install MySQL for the first time, a default initial password is set. Being able to locate this initial password makes the first login and password change process smoother. This section provides a detailed explanation of how to find the initial password on both Linux and Windows environments.

2.1 How to Check the Initial Password on Linux

On Linux, you can find the initial password by checking the log file generated after MySQL installation. Follow the steps below. Steps:
  1. Open a terminal.
  2. Run the following command to view the contents of the log file.
   sudo cat /var/log/mysqld.log | grep 'temporary password'
  1. The command output will display the initial password along with the phrase “temporary password”.
  • Example: 2025-01-19T10:45:32.123456Z 1 [Note] A temporary password is generated for root@localhost: Abc12345!
  • In this case, Abc12345! is the initial password.
  1. Record the initial password and use it when logging into MySQL.
Notes:
  • The initial password is randomly generated for security.
  • After the first login, you will be required to change the password, so set a new one.

2.2 How to Check the Initial Password on Windows

On Windows, you can find the initial password in the log file generated during MySQL’s initial setup or on the installer screen. Refer to the steps below. Steps:
  1. Open the directory where MySQL was installed.
  • The default path is usually something like: C:ProgramDataMySQLMySQL Server X.XLogs * X.X indicates the MySQL version number.
  1. Look for a file named mysqld.log.
  2. Open the log file with Notepad or any text editor.
  3. Search the file for the keyword “temporary password”.
  • The initial password will be displayed alongside the “root@localhost” entry.
  1. Record the initial password and use it when logging into MySQL.
Additional note:
  • Some installers may display the initial password on-screen during installation. In that case, we recommend taking a screenshot of the screen for future reference.

3. How to handle a forgotten password

Even if you forget the MySQL password, you can reset it using several methods. This section explains the password reset procedures for Linux and Windows environments. All methods require administrative privileges, so please perform the operations as a system administrator.

3.1 How to reset the password on Linux

On Linux, the common method is to start MySQL in safe mode (using the –skip-grant-tables option) and set a new password. Steps:
  1. Stop MySQL service Run the following command to stop the MySQL service.
   sudo systemctl stop mysqld
  1. Start MySQL in safe mode In safe mode, user authentication is skipped, allowing access to MySQL without a password. Run the following command.
   sudo mysqld_safe --skip-grant-tables &
  1. Log in to MySQL After starting in safe mode, log in to MySQL with the following command.
   mysql -u root
  1. Set a new password Run the following SQL command to set a new password.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
※ Replace NewPassword123! with an appropriate new password.
  1. Restart the service After setting the password, restart the MySQL service in normal mode.
   sudo systemctl stop mysqld
   sudo systemctl start mysqld
  1. Verify login with the new password Confirm that you can log in to MySQL using the new password you set.

3.2 How to reset the password on Windows

On Windows, you can reset the password by starting MySQL with the --init-file option. Steps:
  1. Stop MySQL service Open the Services Manager and stop the “MySQL Service”.
  2. Create a SQL file for reset Using any text editor, create an SQL file (e.g., reset_password.sql) containing the following content and save it to an appropriate directory.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
※ Replace NewPassword123! with the desired new password.
  1. Start MySQL with the --init-file option Open a command prompt with administrator privileges and run the following command.
   mysqld --init-file="C:path    o
eset_password.sql"
※ Replace C:path o eset_password.sql with the path where you saved the SQL file.
  1. Restart the service Restart MySQL in normal mode. Either start the service again in the Services Manager or run the following command.
   net start mysql
  1. Verify login with the new password Confirm that you can log in to MySQL using the new password you set.

4. Checking and Setting Password Policies

In MySQL, a feature called “password policy” is provided to ensure password security. This allows you to set minimum standards for complexity, length, and other criteria. Here we explain how to check the current policy and how to change the settings if needed.

4.1 How to Check the Password Policy

To check the current password policy, use the validate_password plugin. When this plugin is enabled, MySQL validates passwords according to the configured policy. Steps:
  1. Log in to MySQL.
   mysql -u root -p
  1. To view the current policy settings, run the following command.
   SHOW VARIABLES LIKE 'validate_password%';
  1. The result will display settings like the following.
  • validate_password.policy: Password policy strength level
    • 0 (LOW): alphanumeric characters only
    • 1 (MEDIUM): requires alphanumeric characters and special characters
    • 2 (STRONG): requires alphanumeric characters, special characters, uppercase letters, and lowercase letters
  • validate_password.length: Minimum password length
  • validate_password.mixed_case_count: Minimum number of uppercase and lowercase characters required
  • validate_password.number_count: Minimum number of digits required
  • validate_password.special_char_count: Minimum number of special characters required

4.2 Changing the Password Policy Settings

You can relax or tighten the password policy as needed. Examples are shown below. Steps to change the policy settings:
  1. Log in to MySQL.
   mysql -u root -p
  1. Run the SQL commands to modify the current settings. Example 1: Relax the policy (set to LOW level)
   SET GLOBAL validate_password.policy = 0;
   SET GLOBAL validate_password.length = 8;
Example 2: Strengthen the policy (set to STRONG level)
   SET GLOBAL validate_password.policy = 2;
   SET GLOBAL validate_password.length = 12;
   SET GLOBAL validate_password.special_char_count = 2;
  1. Verify that the settings have been applied.
   SHOW VARIABLES LIKE 'validate_password%';
Notes:
  • To ensure the settings persist after a MySQL service restart, it is recommended to add them to the my.cnf or my.ini file.
  • Example:
   [mysqld]
   validate_password.policy=1
   validate_password.length=10

4.3 Considerations When Changing Settings

  • Balancing security and convenience Strengthening the password policy improves security but can make management more cumbersome. In environments with many beginners, overly strict policies should be avoided.
  • Impact on operations If existing users do not meet the new policy, password changes after the policy update will trigger errors. Check the impact scope before making changes.
  • Preventing password reuse In production environments, consider enabling a feature that prevents reuse of previously used passwords.

5. Troubleshooting

When performing MySQL password verification or reset procedures, unexpected errors may occur. This section provides detailed explanations of common trouble examples and their solutions.

5.1 Common Errors and Their Causes

Error 1: Permission error (Access Denied for User)
  • Cause: Occurs when the username or password specified for logging into MySQL is incorrect, or when the necessary privileges are insufficient.
  • Solution:
  1. Verify that you are using the correct username and password.
  2. Log in as the root user or ask an administrator to verify the privileges.
  3. If necessary, start MySQL in safe mode (–skip-grant-tables) and correct the privileges.
Error 2: MySQL service fails to start
  • Cause: Occurs when there is an error in MySQL’s configuration file (my.cnf or my.ini) or when disk space is insufficient.
  • Solution:
  1. Check MySQL’s error log file.
    • On Linux: /var/log/mysqld.log
    • On Windows: C:ProgramDataMySQLMySQL Server X.XLogsmysqld.log
  2. If there are errors in the configuration file, fix them. In particular, verify that any newly added settings are correct.
  3. If disk space is low, delete unnecessary files or add more storage.
  4. After fixing, restart the service. bash sudo systemctl restart mysqld
Error 3: Password change not taking effect
  • Cause: The privilege tables may not have been updated after the password change.
  • Solution:
  1. After running the password change command, update the privilege tables with the following command. sql FLUSH PRIVILEGES;
  2. Restart the MySQL service and try logging in with the new password.

5.2 Recovery in Safe Mode

If the above solutions do not resolve the issue, you can attempt recovery using safe mode. Steps:
  1. Stop the MySQL service.
   sudo systemctl stop mysqld
  1. Start MySQL in safe mode.
   sudo mysqld_safe --skip-grant-tables &
  1. While in safe mode, log into MySQL and correct the privileges or password of the problematic user.
  2. After fixing, exit safe mode and restart MySQL in normal mode.
   sudo systemctl restart mysqld

5.3 How to Use Error Logs

MySQL’s error log is one of the most important sources of information for troubleshooting. Follow the steps below to check the error log and use it to resolve issues. Steps:
  1. Identify the location of the error log.
  • On Linux (common path): /var/log/mysqld.log
  • On Windows: C:ProgramDataMySQLMySQL Server X.XLogsmysqld.log
  1. Display the log file.
   tail -n 100 /var/log/mysqld.log
※ To view the latest errors, you can add the -f option to monitor in real time.
   tail -f /var/log/mysqld.log
  1. Read the log messages and identify the cause of the error.

5.4 What to Do If the Issue Remains Unresolved

  • Refer to the official documentation The MySQL official documentation contains detailed troubleshooting information for each version. MySQL Official Documentation
  • Use support forums Search community forums or Stack Overflow for similar issues and look for solutions.
  • Consult an expert If you cannot resolve the issue, consult a system administrator or MySQL specialist.

6. FAQ

When working on MySQL password management or resets, various questions and issues can arise. this section, we anticipate questions that readers might ask and clearly explain the solutions for each.

Q1: What should I do if I can’t find the initial password?

A1: If you can’t locate the MySQL initial password, try the following steps.
  1. Recheck the log file
  • On Linux: /var/log/mysqld.log
  • On Windows: C:ProgramDataMySQLMySQL Server X.XLogsmysqld.log The initial password is usually logged with the phrase “temporary password”.
  1. Check the installer screen On Windows, the MySQL installer may display the initial password. Verify whether you captured a screenshot right after installation.
  2. Consider reinstalling If you still cannot find the initial password, uninstalling and reinstalling MySQL will generate a new initial password.

Q2: Why can’t I log in even after resetting the password?

A2: If you cannot log in after resetting the password, check the following points:
  1. Verify the username and host
  • In MySQL, the combination of username and host is important (e.g., 'root'@'localhost'). Using '%' allows access from any host.
  • Command to list users: sql SELECT User, Host FROM mysql.user;
  1. Forgot to refresh the privilege tables If you don’t reload the privilege tables after resetting the password, the new password won’t take effect. Run the following command.
   FLUSH PRIVILEGES;
  1. Restart the MySQL service After resetting the password, restart the MySQL service before attempting to log in.
   sudo systemctl restart mysqld

Q3: Is it possible to disable the password policy?

A3: Disabling the password policy is possible, but it raises security risks, so consider it carefully. You can disable the validate_password plugin using the following steps.
  1. Log in to MySQL.
   mysql -u root -p
  1. Run the command to uninstall the plugin.
   UNINSTALL PLUGIN validate_password;
  1. Verify that the setting has been disabled.
   SHOW PLUGINS;
Note: This action will allow simple passwords, so especially in production environments, strengthen other security measures instead.

Q4: How to handle errors that occur during a password reset?

A4: For errors encountered during a reset, refer to the following steps.
  1. Check the error message
  • Check the error log for detailed messages.
    • On Linux: /var/log/mysqld.log
    • On Windows: C:ProgramDataMySQLMySQL Server X.XLogsmysqld.log
  1. Fix in safe mode If errors persist, start MySQL in safe mode (--skip-grant-tables) and correct the problematic settings.
  2. Delete and recreate the existing user If the issue is related to a user, deleting and recreating that user can also resolve it.
   DROP USER 'root'@'localhost';
   CREATE USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

Q5: If the password is shared with other administrators, what impact does changing it have?

A5: When multiple administrators are involved, changing the password can have the following impacts.
  1. Scope of impact If other administrators have systems or scripts that still use the old password, connection errors will occur.
  2. Advance notice When changing the password, notify other administrators in advance and have them update any scripts or configurations as needed.
  3. Post-change coordination After the change, ensure a secure method for sharing the new password (e.g., using an encrypted messaging tool).

7. Summary

Checking and resetting MySQL passwords is one of the essential tasks that cannot be avoided in database operations. In this article, we comprehensively explain everything from how to check the initial password, to reset procedures when a password is forgotten, as well as setting password policies and troubleshooting, all in a way that beginners can follow.

Key points covered in this article

  1. How to check the initial password
  • In Linux environments, you can obtain the initial password by checking the /var/log/mysqld.log file, and in Windows environments by checking the mysqld.log file.
  1. How to reset a forgotten password
  • We covered reset procedures for both Linux and Windows environments using safe mode and the --init-file option.
  1. Checking and setting password policies
  • We explained how to use the validate_password plugin to set policies appropriate for your environment.
  1. Troubleshooting
  • We provided concrete solutions for common errors such as permission issues and service startup problems.
  1. Supplementary solutions in the FAQ
  • We offered answers to questions and issues that arise when checking or resetting passwords.

Recommendations for readers

To operate MySQL safely and efficiently, keep the following points in mind.
  • Enforce strict password management Set strong passwords and store them properly. Also, regularly changing passwords can enhance security.
  • Leverage error logs When trouble occurs, make it a habit to check the error logs to pinpoint the cause.
  • Refer to the official documentation MySQL’s official resources provide up-to-date information specific to each version. Consult them as needed.

Next steps

By applying the steps introduced in this article, you should have acquired basic MySQL password management and troubleshooting skills. As a next step, consider the following.
  • Further strengthen security Improve overall database security by configuring firewalls and using SSL connections.
  • Reevaluate backup operations Establish a regular backup schedule to ensure thorough protection against data loss.
Manage MySQL properly to achieve smooth, trouble-free operation. We hope this article helps you achieve that.