The World of MySQL & MariaDB

MySQL Root Password Guide: Check, Change, Reset

Users and Permissions
目次

1. How to check MySQL root password

The MySQL root password is a crucial element in database administration. However, especially for beginners, you may wonder “I don’t know the password” or “Where is the initial password?” In this section, we explain concrete methods to check the MySQL root password.

How to check the initial password

From MySQL 5.7 onward, the root user’s initial password is automatically generated and recorded in a log file during installation. The steps to check this initial password are as follows.

Steps

  1. Open a terminal (or command prompt) to check the MySQL log file.
  2. Run the following command.
sudo grep 'temporary password' /var/log/mysqld.log
  1. The output will include something like the following.
[Note] A temporary password is generated for root@localhost: EaGapdplN0*m
  1. In this case, EaGapdplN0*m is the root user’s initial password.

Notes

  • The initial password can be used only once, so you need to change it promptly after logging in.
  • If the log file cannot be found or you encounter permission errors, run the command with administrator privileges.

How to check the current password

If you want to check an existing root password, for security reasons there is no command or method that directly reveals it. Therefore, if you don’t know the current password, refer to the “What to do if you forget the root password” section below and consider resetting the password.

2. How to Change the MySQL Root Password

Regularly changing the MySQL root password is important for maintaining security. This section explains in detail how to change the password using the current root password.

Basic Steps to Change the Password

The steps to log into MySQL and set a new password are as follows.

Procedure

  1. Open a terminal or command prompt and enter the following command to log into MySQL.
   mysql -u root -p
  • -u root indicates that the username is root.
  • Adding -p prompts for a password at login.
  1. To change the password, run the following SQL command.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
  1. To reload privilege information, run the following command.
   FLUSH PRIVILEGES;
  1. After the command runs successfully, log out of MySQL.
   exit

Important Considerations

  • Use a strong password
  • It is recommended that the new password include the following elements.
    • Uppercase and lowercase letters
    • Numbers
    • Symbols (e.g., @, #, $, !, etc.)
    • At least 12 characters in length
    Example: MyS3cure!2025
  • Pay attention to sessions
  • If you change the password during a MySQL session, you may need to reconnect the session.
  • Verify privileges
  • Make sure the root user has the appropriate privileges. If you cannot make the change for any reason, contact your system administrator.

Common Issues When Changing the Password

Cannot log in after changing the password

  • Cause: The FLUSH PRIVILEGES command may not have been executed.
  • Solution: Log in again and run FLUSH PRIVILEGES;.

New password does not meet security policy

  • Cause: MySQL may be configured with a strict password policy (strength requirements).
  • Solution: Set a password that meets the strength requirements, or relax the policy as needed.

3. How to Deal with a Forgotten root Password

If you forget the MySQL root password, you won’t be able to access it using normal login methods. However, by temporarily disabling MySQL’s security features, you can reset the password. This section explains the specific steps.

Steps to Reset the root Password

1. Stop the MySQL Service

Stopping the MySQL service temporarily halts the database. Please use the following command.
sudo systemctl stop mysqld
  • When the command runs successfully, the MySQL service stops.

2. Start MySQL with Authentication Disabled

To disable MySQL’s authentication, start it with the --skip-grant-tables option.
sudo mysqld_safe --skip-grant-tables &
  • This command starts MySQL in a state that skips authentication.
  • Note: Because security is reduced in this state, perform this only when no other users can access the system.

3. Reset the root Password

Log into MySQL with authentication skipped and reset the password.
  1. Log in to MySQL.
   mysql -u root
  1. Set a new root password with the following command.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
  1. Reload the privilege tables.
   FLUSH PRIVILEGES;
  1. When finished, log out of MySQL.
   exit

4. Restart the MySQL Service

Restart the MySQL service to re-enable normal authentication.
sudo systemctl restart mysqld
After restarting, log in to MySQL using the newly set password.

Important Notes

  • Ensuring Security
  • While using the --skip-grant-tables option, MySQL is completely unprotected. Perform this quickly and avoid unnecessary risk.
  • Impact on Non-root Users
  • This method applies only to the root account. Be careful not to affect other users.

Troubleshooting

mysqld_safe Command Not Found

  • Cause: Some Linux distributions may not have mysqld_safe installed.
  • Solution: You can start MySQL directly as follows.
  sudo mysqld --skip-grant-tables &

Cannot Log In After Changing Password

  • Cause: MySQL may not have applied the changes.
  • Solution: Log in again and run FLUSH PRIVILEGES;.

4. Best Practices for Strengthening MySQL Security

While properly managing the MySQL root password is important, it alone is not enough. To operate the database securely, additional security measures are required. This section introduces concrete methods to strengthen MySQL security.

Set Strong Passwords

Database security heavily depends on password strength. When setting strong passwords, follow the guidelines below.

Password Creation Guidelines

  • Length: 12 characters or more
  • Character Types: Combine uppercase, lowercase, numbers, and symbols
  • Example: 2#SecureMySQL_2025!

Applying Security Policies

MySQL provides options to set a password policy. Use the following command to view the current policy and change it if needed.
SHOW VARIABLES LIKE 'validate_password%';
To change the policy, set it as follows.
SET GLOBAL validate_password_policy = 'MEDIUM';
SET GLOBAL validate_password_length = 12;

Remove Unnecessary Accounts and Data

Identify and Delete Unnecessary Accounts

MySQL may create default test accounts or anonymous users during installation. Since these can pose security risks, delete any unnecessary accounts.
  1. Check the current user list.
   SELECT user, host FROM mysql.user;
  1. Delete unnecessary users.
   DROP USER 'username'@'hostname';

Delete Test Database

Also delete the test database created during installation.
DROP DATABASE test;

Restrict Access to the root Account

Limiting access to the root account can enhance security.

Restrict to Local Access Only

By default, the root account may be configured to allow remote connections. Restrict it to local access only.
UPDATE mysql.user SET host = 'localhost' WHERE user = 'root';
FLUSH PRIVILEGES;

Create a Separate Administrative Account

Instead of using the root account directly, it is recommended to create an administrative account dedicated to specific tasks.
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

Minimize Privileges

Assigning only the minimum necessary privileges to each user improves security.

Review Privileges

Check existing user privileges.
SHOW GRANTS FOR 'username'@'hostname';

Revoke Privileges

Remove unnecessary privileges.
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';

Automate MySQL Security Settings

MySQL provides the mysql_secure_installation command to easily apply security settings. Running this command can quickly perform the following configurations.
  • Delete test accounts and databases
  • Disable remote access for the root account
  • Apply security policies

Command to Run

sudo mysql_secure_installation

Regular Backups

Finally, as part of strengthening security, regular database backups are recommended. This ensures data can be restored in case of an emergency.

Example of Taking a Backup

  1. Backup all databases
   mysqldump -u root -p --all-databases > backup.sql
  1. Backup a specific database only
   mysqldump -u root -p database_name > database_name_backup.sql

5. Frequently Asked Questions (FAQ)

We have compiled common questions and answers about MySQL root password management and security settings below. Use them for troubleshooting and resolving further doubts.

Q1. I can’t find the initial MySQL password in the logs. What should I do?

A1: In MySQL 5.7 and later, the initial password is recorded in the log file (usually /var/log/mysqld.log). However, if the log cannot be found or has been deleted, try the following methods.
  1. Reinstall MySQL; a new initial password will be generated.
  2. If you still cannot log in, refer to the “How to handle a forgotten root password” guide to reset the password.

Q2. Can non-root users change their passwords using the same procedure?

A2: Yes, non-root users can change their passwords using the same steps. However, the user must have administrative privileges. Run the following command to change the password.
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

Q3. The mysqld_safe command is not found. What should I do?

A3: On some Linux distributions, mysqld_safe may not be installed. In that case, you can start the mysqld command with the --skip-grant-tables option.
sudo mysqld --skip-grant-tables &
However, this method also disables authentication, so the same cautions apply.

Q4. I changed the password but cannot log in. Why?

A4: The following reasons may be causing this.
  1. Privileges not refreshed: After changing the password, run the following command to refresh privileges.
   FLUSH PRIVILEGES;
  1. Typo: When entering the new password, ensure that case and special characters are correct.
  2. Security policy issue: MySQL may reject weak passwords based on its configuration. Check the policy and set a strong password.

Q5. Is there a way to strengthen MySQL security in one go?

A5: To efficiently configure MySQL security settings, we recommend using the mysql_secure_installation command. This command automatically performs the following configurations.
  • Removal of test users and databases
  • Disabling remote access for the root account
  • Applying a strong password policy
Run it as follows.
sudo mysql_secure_installation

Q6. Does the password management method differ by MySQL version? A6: Yes, the method of managing passwords can differ depending on the MySQL version. The main differences are:

  • MySQL 5.7 and later: The initial password is auto-generated and recorded in the log file.
  • MySQL 5.6 and earlier: The initial password may be empty (no password set).
To check the MySQL version you are using, run the following command.
mysql --version

Q7. How can I allow remote connections for the root user?

A7: For security reasons, remote connections are discouraged, but if needed you can allow them with the following command.
  1. Configure the setting to allow remote connections.
   UPDATE mysql.user SET host = '%' WHERE user = 'root';
  1. Refresh the privilege information.
   FLUSH PRIVILEGES;
Note: If you allow remote connections, be sure to implement additional security measures such as firewalls and IP restrictions.

Q8. Please briefly explain the reset procedure if the root password is forgotten.

A8:
  1. Stop the MySQL service.
   sudo systemctl stop mysqld
  1. Start it with authentication disabled.
   sudo mysqld_safe --skip-grant-tables &
  1. Reset the password.
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   FLUSH PRIVILEGES;
  1. Restart the service.
   sudo systemctl restart mysqld