Solving Common MySQL Issues on macOS: A Guide for Developers

Introduction

Working with MySQL on macOS can sometimes lead to unexpected issues, whether you're setting up a new database or maintaining an existing one. In this post, we'll discuss how to address two common MySQL problems on macOS, particularly for those using Homebrew: changing the password policy and troubleshooting service start-up issues.

Adjusting MySQL Password Policy

The Issue

You might encounter an error (ERROR 1819 (HY000): Your password does not satisfy the current policy requirements) when trying to set a new MySQL password that doesn't include uppercase letters.

The Solution

1. Log in to MySQL
   

   mysql -u root -p
   

2. Check Current Password Policy
   

   SHOW VARIABLES LIKE 'validate_password%';
   

3. Adjust Policy

   • Lower the password policy level:
     

      SET GLOBAL validate_password.policy = LOW;
     

- Change the password length requirement, if necessary:

    ```sql
    SET GLOBAL validate_password.length = 8;
    ```

1. Set New Password
   

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   FLUSH PRIVILEGES;
   

2. Restart MySQL
   

   brew services restart mysql
   

Troubleshooting MySQL Start-Up Issues

The Issue

Sometimes, after running brew services restart mysql, MySQL is Loaded but not Running.

The Solution

1. Check MySQL Logs

   • Locate and view the error log:
     

     cat /usr/local/var/mysql/hostname.err
     

2. Check MySQL Status
   

   brew services list
   

3. Start MySQL Manually

   • This can give more immediate feedback:
     

     mysql.server start
     

4. Check for Port Conflicts
   

     lsof -i :3306
   

5. Check File Permissions
   

     sudo chown -R $(whoami) /usr/local/var/mysql/
   

6. Repair MySQL Installation

   • Reinstall MySQL if necessary:
     

     brew uninstall mysql
     brew install mysql
     

Conclusion

Dealing with MySQL on macOS can present challenges, but with the right approach, these can be easily overcome. Whether adjusting the password policy or troubleshooting service start-up, understanding how to navigate these issues is crucial for any developer.