Securing Your Homebrew MariaDB Installation: Resetting the Root Password

• Root Access: The highest level of permission within MariaDB, allowing for full control of the database.
• MySQL: A widely used RDBMS that can also be installed with Homebrew.
• MariaDB: An open-source relational database management system (RDBMS) often used as a MySQL alternative.
• Homebrew: A popular package manager for macOS that simplifies software installation, including databases like MariaDB (a MySQL derivative).

The Issue:

By default, Homebrew installs MariaDB without setting a root password. When you try to connect using mysql -uroot, you'll encounter an "access denied" error because there's no password configured.

Resolving the Issue (Using MariaDB):

1. Stop MariaDB:

   brew services stop mariadb
   

2. Start MariaDB in Safe Mode (Skipping Grant Tables):

   mysqld_safe --skip-grant-tables --skip-networking
   

   This temporarily disables password restrictions for this session.

3. mysql
   

4. Set the Root Password:

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

   Replace your_strong_password with a secure password.

5. Exit and Restart MariaDB Normally:

   exit
   brew services start mariadb
   

Additional Considerations:

• Using mysql_secure_installation (Older MariaDB Versions): For MariaDB versions before 10.4, you can use the mysql_secure_installation script (similar to mariadb-secure-installation). However, this script might be deprecated in newer versions.
• Using mariadb-secure-installation (MariaDB >= 10.4): If you have MariaDB version 10.4 or later, use this interactive script instead of steps 3 and 4:

  mariadb-secure-installation
  

  Follow the prompts to set the root password and other security measures.

Security Practices:

• Consider additional security measures like IP-based access restrictions.
• Avoid using the root user for everyday tasks. Create a dedicated user with the necessary permissions.
• Always set a strong, unique password for the root user.

# Stop MariaDB (assuming it's running)
brew services stop mariadb

# Start MariaDB in Safe Mode (skipping grant tables and networking)
mysqld_safe --skip-grant-tables --skip-networking

mysql

This will connect you to MariaDB without a password since you're in safe mode.

Setting the Root Password (Two Options):

Option A: Using ALTER USER (For all MariaDB versions):

# Set the root password (replace 'your_strong_password' with your actual password)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_strong_password';

# Flush privileges to make the changes take effect
FLUSH PRIVILEGES;

Option B: Using mariadb-secure-installation (MariaDB >= 10.4):

# If your MariaDB version is 10.4 or later, use this interactive script:
mariadb-secure-installation

# Follow the prompts to set the root password and other security measures:
#   - Set root password
#   - Remove anonymous users
#   - Disallow root login remotely
#   - Reload privilege tables (similar to FLUSH PRIVILEGES)

# Exit the MariaDB prompt
exit

# Restart MariaDB with normal security measures
brew services start mariadb

Remember:

• Choose the method (Option A or B) based on your MariaDB version.

This method works if you haven't completely locked yourself out and still have access to your macOS user account with administrative privileges (sudo). Here's how:

# Connect to MariaDB using sudo and your macOS user password
sudo mysql

# Once connected, use the `ALTER USER` command to set the root password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_strong_password';
FLUSH PRIVILEGES;

exit

Important Note:

• This method leverages your macOS user's sudo privileges to bypass the MariaDB root password check. However, it's generally not recommended for production environments as it exposes the root password indirectly through your macOS user account.

Modifying the my.cnf Configuration File (Less Common):

This approach involves editing the MariaDB configuration file (my.cnf) to temporarily allow root login without a password. It's a less common method and requires caution. Here's a general outline (consult MariaDB documentation for specific details):

• Once the password is set, remove the skip-grant-tables line from my.cnf, restart MariaDB again, and delete the backup if no longer needed.
• Save the changes, restart MariaDB (brew services restart mariadb), and follow steps similar to Method 1 (connecting with mysql and using ALTER USER) to set the root password.
• Add a temporary line like skip-grant-tables under the [mysqld] section to bypass password checks.
• Create a backup of the original file before making changes.
• Locate the my.cnf file (usually in /etc/my.cnf or ~/Library/Preferences/my.cnf).

Caution:

• Modifying the my.cnf file can expose security risks if not done carefully. Ensure you remove the temporary line after setting the password and restart MariaDB to enforce password checks again.