MySQL 8: 3 Ways to Reset Root Password in macOS

Introduction

For MySQL 8 users on macOS, forgetting the root password can be a common dilemma. Thankfully, there are a few different methods to reset the password and regain control. In this guide, we will walk through several approaches to reset the root password, summarize their pros and cons, and provide step-by-step instructions including the necessary code examples when applicable.

Approach #1 – Use MySQL Safe Mode

Starting MySQL in safe mode allows you to run the server with special settings like skipping the grant tables, which control access to the different databases and their privileges. This is useful for resetting the root password without needing the current password.

1. Stop the current MySQL service.
2. Restart it in safe mode with –skip-grant-tables.
3. Log into MySQL without a password.
4. Reset the root password.
5. Flush the privileges to ensure the changes take effect.
6. Stop MySQL and restart it normally.

Example:

sudo mysql.server stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
exit
sudo mysql.server start

Notes: This method is generally considered an easy fix but must be executed with caution as it makes the database vulnerable during the process. Ensure that the server isn’t accessible from the internet during this time to prevent security risks.

Approach #2 – Modify the Configuration File

Another way to reset the root password is by editing the MySQL configuration file (my.cnf) to include the init-file option, which specifies a file that will be executed when the server starts. This file can contain the SQL statement to reset the password.

1. Create a temporary SQL file with the password reset query.
2. Edit the MySQL configuration file to include this file as an init-file.
3. Restart MySQL server to apply changes.
4. Login to MySQL with the new password and revert configuration changes.

Example:

echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';" > /tmp/reset_pass.sql
echo "[mysqld]\ninit-file=/tmp/reset_pass.sql" >> /etc/my.cnf
sudo mysql.server restart
mysql -u root -p
// Enter new password when prompted
rm /tmp/reset_pass.sql
sed -i '' '/init-file/d' /etc/my.cnf
sudo mysql.server restart

Notes: Editing the configuration file is more secure than running in safe mode but involves a risk of misconfiguring other settings. Always back up the configuration file before making changes.

Approach #3 – Use MySQL Initialization Script

MySQL includes an initialization script that you can use to start the server with a custom SQL query, making it possible to reset the root password:

1. Create a temporary initialization SQL script.
2. Use the MySQL –initialize option to start MySQL with this script.
3. Login to MySQL with the new password.

Let’s dive into these steps but with more details.

Step 1: Create a Temporary Initialization SQL Script

1. Open Terminal: Launch the Terminal on your Mac.
2. Create a New SQL Script File:
   • You can use any text editor to create this file. For simplicity, let’s use nano.
   • Run: nano /tmp/mysql-init.sql
   • This command opens the nano editor with a new file mysql-init.sql in the /tmp directory.
3. Add Password Reset Command to the File:
   • Write the following SQL command in the file: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
   • Replace 'MyNewPass' with the new password you want to set.
   • Save the file and exit the editor (in nano, you can do this by pressing CTRL + X, then Y to confirm, and Enter to save).

Step 2: Start MySQL with the Initialization Script

1. Stop MySQL Server (if it’s running):
   • Run: sudo mysql.server stop
   • You might need to provide your macOS user password.
2. Start MySQL with the Initialization Script:
   • Run: sudo mysqld --init-file=/tmp/mysql-init.sql
   • This will start the MySQL server and execute the commands in the mysql-init.sql script.

Step 3: Login to MySQL with the New Password

1. Open a New Terminal Window:
   • Leave the current Terminal window open where MySQL is running.
   • Open a new Terminal window for the next steps.
2. Log in to MySQL:
   • Run: mysql -u root -p
   • Enter the new password when prompted.

Clean Up

• After successfully resetting the password and logging in, you can stop the MySQL server in the original Terminal window (where you ran it with the --init-file option) by pressing CTRL + C.
• Restart the MySQL server normally, for example, using sudo mysql.server start.

Notes: This solution should only be used in situations where the previous methods have failed, as it will create a new data directory and is intended for initializing MySQL rather than resetting a password.

Conclusion

There are various methods to reset the root password in MySQL 8 on macOS, each with its own advantages and considerations. Regardless of the method chosen, it is vital to handle these operations carefully to avoid undesirable outcomes and maintain database security. These solutions should only be performed by users with the necessary permissions and knowledge.