A Quick Tutorial – Resetting MySQL Root Password on Mac OS

by tyler garrett | Jul 4, 2017 | Business, Databasehelp, MySQL, Solutions, Tableauhelp

Lost your MySQL root password on your Mac? Don’t panic! It happens more often than you think.

We are here to help.

Fortunately, the process for resetting it is straightforward. This guide will walk you through the necessary steps to regain access to your MySQL server.

Understanding the Process

The core of the password reset involves stopping the MySQL server, restarting it in safe mode without password checking, connecting as root, updating the password, and then restarting the server normally. Let’s break it down.  

Step 1: Stop the MySQL Server

Before you can reset the password, you need to ensure the MySQL server is not running. The method to stop it depends on how you installed and manage MySQL. Here are a few common scenarios:

• Using Homebrew: Open your Terminal application and run the following command: Bashbrew services stop mysql
• Using the MySQL Preference Pane: If you installed MySQL using the official installer, you might have a preference pane in your System Preferences. Open System Preferences, find the MySQL icon, and click “Stop MySQL Server.”  
• Using mysqld_safe directly: If you know the location of your MySQL installation, you can try: Bashsudo /usr/local/mysql/support-files/mysql.server stop (Adjust the path if your MySQL installation is in a different location.)

Step 2: Restart MySQL in Safe Mode (Without Password Checking)

Now, you’ll restart the MySQL server with the --skip-grant-tables option. This tells the server to start without loading the grant tables, which contain user privileges and passwords. This allows anyone to connect without a password.  

Open a new Terminal window and run one of the following commands, depending on your installation:

• Using mysqld_safe: Bash/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

The & at the end runs the command in the background, allowing you to use the same Terminal window for the next steps.  

• If mysqld_safe is not in /usr/local/mysql/bin/, try: Bashsudo /usr/local/mysql/bin/mysqld --skip-grant-tables --user=mysql & Again, adjust the path if necessary. Important: Keep this Terminal window open while you proceed to the next steps. Closing it will likely shut down the MySQL server.

Step 3: Connect to MySQL as Root

With the server running in safe mode, you can now connect to the MySQL server as the root user without being prompted for a password. Open a new Terminal window and enter:

Bash

mysql -u root

You should see the MySQL command prompt (mysql>).

Step 4: Update the Root Password

Now that you’re connected, you can update the root password. The specific command depends on your MySQL version:

• MySQL 5.7.6 and later: Use the ALTER USER statement: SQLALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword'; Replace 'YourNewPassword' with the new password you want to set. Remember to use strong and unique passwords in a production environment.
• MySQL 5.7.5 and earlier: Use the UPDATE statement on the mysql.user table: SQLUPDATE mysql.user SET Password=PASSWORD('YourNewPassword') WHERE User='root'; FLUSH PRIVILEGES; Again, replace 'YourNewPassword' with your desired new password. FLUSH PRIVILEGES; reloads the grant tables, ensuring your changes take effect immediately.  

Step 5: Exit the MySQL Client

Once you’ve successfully updated the password, exit the MySQL client by typing:

SQL

exit

and pressing Enter.

Step 6: Stop the MySQL Server (Normal Mode)

Now, you need to stop the MySQL server that’s running in safe mode. Go back to the Terminal window where you started mysqld_safe (or the mysqld command) and press Ctrl+C to stop the process.

If you used brew services start mysql earlier, you can use:

Bash

brew services stop mysql

If you used the MySQL Preference Pane, go back and click “Stop MySQL Server.”

Step 7: Restart the MySQL Server (Normal Mode)

Finally, restart the MySQL server in its normal mode. Use the same method you used in Step 1 to stop it, but this time use the “start” command:

• Using Homebrew: Bashbrew services start mysql
• Using the MySQL Preference Pane: Click “Start MySQL Server.”
• Using mysql.server: Bashsudo /usr/local/mysql/support-files/mysql.server start

Step 8: Verify the New Password

You should now be able to connect to your MySQL server using the new root password. Open a new Terminal window and try connecting:

Bash

mysql -u root -p

You will be prompted to enter your password. Type the new password you set in Step 4 and press Enter. If you see the mysql> prompt, you have successfully reset your MySQL root password!

Important Considerations:

• Security: Running MySQL with --skip-grant-tables is a security risk. Ensure you stop the server and restart it in normal mode as soon as you’ve reset the password.  
• File Permissions: If you encounter permission issues during this process, you might need to adjust file ownership or permissions for your MySQL data directory.
• MySQL Version: While the core process remains similar, the specific commands might vary slightly depending on your MySQL version. Refer to the official MySQL documentation for your version if you encounter issues.
• Backup: It’s always a good practice to have a backup of your MySQL data before making any significant changes.

If you’ve misplaced it, there’s a quick process to dig through.

Let’s start with the easy method – did you save it somewhere?

Click the magnifying glass on the top right and search for it, if you built notes for your password.

Here’s the text for the temporary password that you can search on your mac.

[Note] A temporary password is generated for root@localhost: rt4rvI,%lfr<

Maybe you forgot your Mac OS X ‘ROOT’ password? Or potentially didn’t save the Temporary password in your notes?

If you did save your root password, it’s likely you saved it in a similar fashion to us.

How to Reset MySQL root password on your Mac

Here’s a quick how to reset mysql root password on your mac tech tutorial!

1.  Stop the mysqld server.  Typically this can be done by from ‘System Prefrences’ > MySQL > ‘Stop MySQL Server’

2.  Start the server in safe mode with privilege bypass

From a terminal:

sudo /usr/local/mysql/bin/mysqld_safe –skip-grant-tables

3.  In a new terminal window:

sudo /usr/local/mysql/bin/mysql -u root

UPDATE mysql.user SET Password=PASSWORD(‘NewPassword’) WHERE User=’root’;

FLUSH PRIVILEGES;

\q

4.  Stop the mysql server again and restart it in normal mode.

Hopeful you’re in a better place. Contact us for further panic mode buttons.