The World of MySQL & MariaDB

Check and Reset MySQL’s Initial Password – Troubleshoot

Basic Operations
目次

1. Introduction

MySQL is a popular database management system used in many environments, and verifying and managing the “initial password” as part of the initial setup is extremely important. In particular, mishandling the initial password can lead to security risks and access issues. This article explains how to check and change MySQL’s initial password, as well as solutions for any problems that may arise, in a way that’s easy for beginners to understand.

2. What is the MySQL initial password

Overview of the initial password

When you install MySQL for the first time, the database administrator user (usually “root”) is assigned an initial password. This password behaves differently depending on the MySQL version.

Differences by version

  • MySQL 5.7 and later: The initial password is automatically generated during installation and written to the log file.
  • Earlier versions: It is common for the installation to have no password (an empty state).

Why the initial password matters

If the initial password is not properly managed, the risk of unauthorized access increases. After installation, you should always verify the password and change it if necessary.

3. How to Check the Initial Password

For Linux Environments

If you installed MySQL on a Linux environment, you can check the initial password using the steps below.
  1. Log in and open a terminal.
  2. Run the following command to view the log file.
   sudo grep 'temporary password' /var/log/mysqld.log
  1. In the output line, you will see the phrase “temporary password” followed by the initial password.
Example:
2025-01-22T10:22:33.123456Z 1 [Note] A temporary password is generated for root@localhost: Abc123!@#

For Windows Environments

In a Windows environment, you need to manually check the mysqld.log file.
  1. Open the MySQL installation folder (usually C:ProgramDataMySQLMySQL Server X.X).
  2. Locate the mysqld.log file and open it with a text editor.
  3. Search for “temporary password” just as you would on Linux.

Notes

  • If the initial password does not appear in the log file, refer to the troubleshooting section below.

4. How to Change the Initial Password

Using MySQL with its default settings increases security risks. Therefore, it is recommended to change the initial password immediately after installation. Below are the detailed steps to do so.

mysql_secure_installation Command Change Procedure

mysql_secure_installation is a command-line tool that simplifies MySQL’s initial configuration. Using this tool, you can perform security hardening in one step, including changing the initial password.
  1. Open a terminal or command prompt and run the following command.
   sudo mysql_secure_installation
  1. You will be prompted to enter the initial password. Enter the password you noted during installation.
  2. An option to set the password policy will be displayed. Choose an appropriate strength (e.g., a strong password is recommended).
  3. Enter the new password, then re-enter it for confirmation.
  4. Proceed with the remaining security settings as instructed, such as removing anonymous users and disabling remote root login.

Manual Password Change Procedure

You can also change the password directly via SQL on the command line.
  1. Log in to MySQL.
   mysql -u root -p
  1. Execute the SQL command to change the password.
   ALTER USER 'root'@'localhost' IDENTIFIED BY '新しいパスワード';
※Replace 新しいパスワード with a strong, secure password.
  1. Run the following to apply the changes.
   FLUSH PRIVILEGES;
  1. Exit MySQL.
   exit

Password Policy and Strength Settings

MySQL may have the password policy enabled by default. This policy rejects weak passwords to enhance security.
  • To view the current policy, run:
   SHOW VARIABLES LIKE 'validate_password%';
  • To relax the policy, modify the following setting:
   SET GLOBAL validate_password.policy=LOW;

Things to Keep in Mind

  • After changing the password, you might lose access to MySQL. If that happens, check your configuration files and connection settings.
  • Choosing a strong password helps reduce the risk of unauthorized access.

5. How to Handle When the Initial Password Cannot Be Retrieved

If you cannot locate the MySQL initial password, it may be due to issues with log file settings or procedures. This section explains the causes of the problem and specific solutions.

When the Initial Password Is Not Logged

Starting with MySQL 5.7, the initial password is written to the log file, but depending on settings or environment, it may not be logged. Points to Check
  1. Log File Location: The default path for MySQL log files is as follows.
  • Linux: /var/log/mysqld.log
  • Windows: C:ProgramDataMySQLMySQL Server X.Xmysqld.log Please check the configuration file (my.cnf or my.ini) to ensure the log destination hasn’t been changed.
  1. Log Level Configuration: If the MySQL log level is not set correctly, the initial password may not be recorded. Edit the my.cnf file and add or verify the following settings.
   [mysqld]
   log_error=/var/log/mysqld.log

How to Reset When the Initial Password Is Unknown

If the password is not logged or you have lost the initial password, you can reset it using the steps below. Steps:
  1. Start MySQL in Safe Mode: In safe mode, you can access MySQL while skipping authentication.
   sudo systemctl stop mysqld
   sudo mysqld_safe --skip-grant-tables &
  1. Log in to MySQL: Access MySQL without authentication.
   mysql -u root
  1. Reset the Password: Execute the SQL command to reset the password.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
※ Please set a strong password for new_password.
  1. Apply the Changes:
   FLUSH PRIVILEGES;
  1. Restart MySQL: Exit safe mode and restart in normal mode.
   sudo systemctl restart mysqld

If the Issue Is Not Resolved

If the above steps do not resolve the issue, consider the following options.
  • Reinstall MySQL: If the database is not in use, you can reinstall MySQL to return it to its default state.
  • Contact Official Documentation or Support: If specific error messages are displayed, refer to the official MySQL documentation or forums.

6. FAQ

This section compiles the most frequently asked questions and answers from readers about MySQL’s initial password. Use it as a reference to quickly resolve any doubts or issues.

Q1: What should I do if I forget the initial password?

If you forget the initial password, you can reset it using the following steps.
  1. Stop MySQL.
   sudo systemctl stop mysqld
  1. Start MySQL in safe mode.
   sudo mysqld_safe --skip-grant-tables &
  1. Log into MySQL and set a new password.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   FLUSH PRIVILEGES;
  1. Restart MySQL to return to normal mode.
   sudo systemctl restart mysqld

Q2: Is there a way to verify after changing the initial password?

To verify that the changed password is correct, follow these steps.
  1. Log into MySQL.
   mysql -u root -p
  1. If you can log in by entering the password, the setting was successful.
  2. For good measure, check the authentication info with the following SQL command.
   SELECT user, host, authentication_string FROM mysql.user;

Q3: How do I change the password policy?

MySQL’s default configuration enforces a password policy, but you can relax the requirements.
  1. Check the current password policy.
   SHOW VARIABLES LIKE 'validate_password%';
  1. To change the policy, run the following.
   SET GLOBAL validate_password.policy = LOW;
  1. You can also adjust the minimum length and complexity settings as needed.
   SET GLOBAL validate_password.length = 8;

Q4: I can’t find the log file that recorded the initial password. What should I do?

If the log file can’t be found, try the following methods.
  1. Check the configuration file (my.cnf or my.ini) and locate the path for log_error.
  2. If the setting is incorrect, explicitly set the log file destination.
   [mysqld]
   log_error=/var/log/mysqld.log
  1. Restart MySQL to apply the changes.

Q5: What happens if the MySQL initial password does not exist?

In some versions, the initial password may be empty (unset). In that case, set a new password using the following steps.
  1. Log into MySQL.
   mysql -u root
  1. Set the password.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

7. Summary

The initial password for MySQL is a crucial element for securely managing your database. In this article, we explained in detail how to check the initial password, how to change it, and troubleshooting. Below are the key points summarized.

Article Highlights

  • Importance of the Initial Password: For security, it is recommended to verify and change it immediately after installation.
  • How to Check: You can easily verify it by referring to the log file (mysqld.log).
  • How to Change: You can change the initial password using the mysql_secure_installation tool or by executing SQL directly.
  • Troubleshooting: If you cannot locate the initial password, you can reset it by adjusting log settings or using safe mode.
  • FAQ: We helped resolve readers’ questions by providing answers to common password-related queries.

Message to Readers

When using MySQL, it is essential to rigorously manage the initial password to minimize security risks. Additionally, regularly changing passwords and adhering to security policies enables safe and efficient database operation.

Next Steps

Use this article as a reference to review MySQL’s initial configuration and implement necessary security measures. If issues arise, consult the troubleshooting section and FAQ in the article while proceeding with resolution.