Understanding MySQL 5.7 Default Root Password Mechanism and Secure Access Practices

Significant Changes in MySQL 5.7 Security Model

Starting with MySQL 5.7, the database management system implemented fundamental improvements in security policies. Unlike earlier versions that allowed empty or default passwords for root access, version 5.7 introduced stricter initial security configurations. This change is primarily reflected in the automatic generation of temporary passwords during installation, which are designed for one-time use, forcing administrators to immediately change to custom passwords upon first login.

Locating and Extracting Temporary Passwords

After initial installation of MySQL 5.7 on Linux systems, the system automatically generates a random temporary password and records it in log files. This design ensures that even fully automated installations do not leave predictable default credentials. The temporary password is typically stored in /var/log/mysqld.log, though the exact path may vary depending on the Linux distribution and installation method.

The standard command for extracting the temporary password is:

grep 'temporary password' /var/log/mysqld.log

After executing this command, the terminal will display output similar to the following format:

2023-01-01T12:00:00.000000Z 1 [Note] A temporary password is generated for root@localhost: AbCdEfGhIjK1

Here, AbCdEfGhIjK1 represents the system-generated temporary password. It's important to note that this password is case-sensitive and contains special characters, complying with MySQL 5.7's password strength requirements.

Complete Workflow of mysql_secure_installation

After obtaining the temporary password, the next step is to run the mysql_secure_installation tool. This interactive script not only changes the root password but also performs a series of security hardening operations:

1. Password Verification Phase: Log into the MySQL instance using the temporary password
2. Password Strength Policy Configuration: Set password complexity requirements based on security needs
3. Anonymous User Removal: Delete any anonymous accounts that may exist in the default installation
4. Remote Root Login Disabling: Restrict root account access to localhost only
5. Test Database Cleanup: Remove test databases created during installation
6. Privilege Table Reload: Ensure all security changes take effect immediately

Example execution process:

$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
# Enter the temporary password extracted from the log here

The existing password for the user root has expired. Please set a new password.

New password: 
# Enter a new password meeting strength requirements
Re-enter new password: 
# Confirm the new password
# Complete other security configurations as prompted

Technical Analysis of Alternative Solutions

Beyond the officially recommended temporary password method, there are other viable solutions in the community. These methods primarily address specific scenarios or problem variants but should be used cautiously to avoid security risks.

Authentication Plugin Modification Solution: In some Ubuntu distributions, MySQL 5.7 may default to using the auth_socket plugin for root authentication. In such cases, the authentication method can be modified through the following steps:

# Log in using the debian-sys-maint account (password obtained from /etc/mysql/debian.cnf)
$ mysql -u debian-sys-maint -p

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Auxiliary Account Creation Solution: For scenarios that don't require direct use of the root account, dedicated administrative accounts with appropriate privileges can be created:

$ sudo mysql --user=root mysql

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'strong_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Security Best Practices and Considerations

When implementing the above solutions, the following security principles must be adhered to:

• Password Strength Requirements: MySQL 5.7 enables the password validation plugin by default, requiring passwords to contain at least 8 characters with mixed case letters, numbers, and special characters
• Log File Permissions: Ensure MySQL log files are readable only by necessary users to prevent temporary password exposure
• Timely Password Updates: Change temporary passwords immediately after first login to avoid exploitation during security windows
• Principle of Least Privilege: Create dedicated accounts for different purposes to avoid excessive use of root privileges
• Regular Auditing: Periodically check account configurations using SELECT User, Host, plugin FROM mysql.user;

By deeply understanding the security mechanism changes in MySQL 5.7 and correctly applying the methods introduced in this article, developers can ensure optimal balance between security and availability in database systems. These practices not only solve initial access issues but also establish a solid foundation for subsequent database security management.