Comprehensive Guide to Removing MySQL Root Password: From Error 1045 to Secure Configuration

Problem Background and Error Analysis

In MySQL database usage, users may accidentally set a root user password, leading to access denial. A typical error message is: #1045 - Access denied for user 'root'@'localhost' (using password: NO). This indicates the client attempted a password-less connection, but the server requires authentication. To resolve this, the password for root@localhost must be set to empty.

Core Solution: SET PASSWORD Command

MySQL offers the SET PASSWORD command to directly modify user passwords. Syntax varies slightly by version:

-- MySQL 5.x versions
SET PASSWORD FOR root@localhost = PASSWORD('');

-- MySQL 8.x versions  
SET PASSWORD FOR root@localhost = '';

Before executing this command, ensure connection to the MySQL server via other means (e.g., using a known password). After execution, the password is cleared, allowing subsequent connections without a password.

Alternative Method: mysqladmin Tool

Beyond SQL commands, the command-line tool mysqladmin can modify passwords:

mysqladmin -u root -poldpassword password ''

Note: There is no space between -p and the old password, as a space might cause syntax errors. On Windows systems, single quotes may be interpreted literally; using double quotes is advised: password "". This method is suitable for quick changes but requires the old password to be correct.

Version Compatibility and Security Plugin Handling

Starting from MySQL 5.7, the validate_password plugin is enabled by default, preventing the use of empty passwords. To remove the password, disable this plugin first:

UNINSTALL PLUGIN validate_password;
SET PASSWORD FOR root@localhost = '';

However, note that in some versions (e.g., MySQL 8.0), the plugin name might differ; verify its existence before execution. Directly modifying the mysql.user table is not recommended, as the password field changed to authentication_string in MySQL 5.7 and later, and syntax errors are common.

Security Best Practices and Auto-Login Configuration

Completely removing the password can increase security risks, especially in production environments. Using a .my.cnf file for auto-login while retaining the password is advised:

[client]
user = root
password = your_password

Place this file in the user's home directory (e.g., ~/.my.cnf) and set appropriate permissions (e.g., 600) to prevent unauthorized access. This allows command-line tools to automatically use credentials without interactive password entry.

Troubleshooting and Recovery Steps

If the password is forgotten and connection is impossible, start MySQL with the --skip-grant-tables option to bypass privilege checks:

1. Stop the MySQL service.
2. Start the service with --skip-grant-tables.
3. Connect to MySQL without a password.
4. Execute a password update command, such as ALTER USER 'root'@'localhost' IDENTIFIED BY '';.
5. Refresh privileges: FLUSH PRIVILEGES;.
6. Restart the MySQL service.

This method is for emergency recovery only.

Conclusion and Recommendations

Removing the MySQL root password can be achieved via SET PASSWORD or mysqladmin, but version differences and security constraints must be considered. In development environments, an empty password may simplify operations, but production environments should use .my.cnf files to maintain security. Always adhere to the principle of least privilege and regularly review configurations to mitigate potential risks.