Resolving MySQL Root Access Denied Issues After Homebrew Installation on macOS

Problem Background and Analysis

When installing MySQL on macOS using Homebrew, many users encounter root access denied issues, specifically Error 1045: "Access denied for user 'root'@'localhost' (using password: NO)". This problem typically occurs after a fresh MySQL installation when users attempt to set root passwords or connect to the database.

Root Cause Investigation

Analysis reveals that the primary cause of this issue lies in residual files from previous MySQL installations or configuration conflicts. Even on brand new machines, if there were previous MySQL-related installations or incomplete Homebrew cleanup, it can lead to abnormal permission configurations. This manifests as incomplete initialization of MySQL privilege tables or incorrect authentication method settings for the root user.

Complete Solution

To thoroughly resolve this issue, follow these steps:

Step 1: Complete Removal of Old Versions

First, completely remove any existing MySQL versions and related files from the system:

brew remove mysql
brew cleanup
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /usr/local/var/mysql

These commands sequentially: remove the MySQL package, clean Homebrew cache, unload launch agent, delete launch configuration file, and remove MySQL data directory. Ensure all related files are completely eliminated.

Step 2: Reinstall MySQL

Perform a fresh installation:

brew install mysql

Homebrew will automatically download and install the latest stable version of MySQL.

Step 3: Database Initialization

Initialize the MySQL database using:

unset TMPDIR
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

This step creates necessary system tables, including the mysql.user table for privilege management.

Step 4: Start MySQL Service

Start the MySQL server:

mysql.server start

At this point, the MySQL service should start normally and allow password-less login.

Step 5: Security Configuration

Run the security installation script:

/usr/local/Cellar/mysql/5.5.10/bin/mysql_secure_installation

Note: Adjust the version number in the path according to the actually installed version. This script guides users through setting root passwords, removing anonymous users, disabling remote root login, and other security options.

Step 6: Configure Auto-start

Set up MySQL to start automatically:

launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Technical Principle Deep Dive

MySQL's privilege system is based on a series of tables in the mysql database, where the mysql.user table stores user authentication information. When access denied errors occur, it's typically due to:

• Incorrect authentication plugin configuration for root user in user table
• Empty or malformed password fields
• Overly restrictive host limitations
• Corrupted or incompletely initialized privilege tables

Through thorough cleanup and reinstallation, proper initialization of privilege tables is ensured, thereby resolving root user authentication issues.

Verifying Installation Results

After installation completion, verify MySQL functionality using:

mysql -u root -p

After entering the set password, you should successfully connect to the MySQL server. Execute basic SQL commands to confirm database functionality:

SHOW DATABASES;
SELECT User, Host FROM mysql.user;

Preventive Measures and Best Practices

To prevent similar issues, recommend:

• Thoroughly clean old versions before installing new ones
• Regularly use brew cleanup for cache maintenance
• Back up important database configurations and data
• Use mysql_secure_installation script for security hardening
• Create dedicated database users for production environments, avoiding direct root account usage

Conclusion

Through systematic cleanup and reinstallation procedures, MySQL root access denied issues after Homebrew installation on macOS can be effectively resolved. The key lies in completely removing all residual files and ensuring proper initialization of privilege tables. This approach not only solves current access problems but also establishes a stable foundation for subsequent MySQL usage.