Posted on

Howto: Installing MySQL on macOS with root password reset

Following the recent macOS Mojave update I figured I should update MySQL 5.7.16 to 8.0.something, too. This didn’t work out because of the impossible security hash introduced with it. None of my tools are compatible with it apparently.

After some frustrations and loosing all my databases (Migration/Upgrading also didn’t work) I went back to 5.7. The latest version of MySQL 5.7 is 5.7.24. Easy enough, right? Wrong…
It took me a few hours to figure out. But here is my “first launch” setup guide.

After the installation is complete the MySQL manual sort of tells you you’re done and good luck. What it doesn’t mention however is that the temporary password you get is expired before you even use it. You get your password and as far as I can tell their Password Reset instruction is lacking completeness, too.

I think they assume everyone on the planet uses something like PHPMyAdmin to reset it or whatever. Maybe that works, I don’t know. I don’t use PHPMyAdmin. So I was utterly stuck for over 2 hours to figure this one out.

MySQL installation and initialisation steps

This is the regular setup procedure. No special skills required.

  1. Download the .DMG installer from the MySQL website.
  2. In the final installation step look out for a dialog box. It has your temporary password. Copy and store it somewhere.
  3. Finish and close the MySQL installer.
  4. Open System Preferences and open the MySQL preference pane.
  5. Stop the MySQL server if it’s running. Mine wasn’t.

Reset the MySQL root password

These steps are a bit more of a hassle and unfortunately they are very essential.
MySQL 8 does this better and more intuitive. I also don’t remember doing this on any other MySQL 5.7 setup I ever installed.

In Terminal run the following command:
arnan$: /usr/local/mysql/bin/mysql_safe --skip-grant-tables

This will load MySQL and occupies the tab. Which is fine.
Press `cmd T` to open a new tab in Terminal.

Then run this command in the new Terminal tab:
arnan$: /usr/local/mysql/bin/mysql -u root

On the MySQL prompt that appears run this command
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('new_password') where user='root';

Replace new_password with your desired password.

I then couldn’t MySQL to stop or respond, so I rebooted my computer. Yea, weird…

Once rebooted I opened up Terminal again and ran the following command:
arnan$: /usr/local/mysql/bin/mysql -u root -p 'new_password'

Replace new_password with the password you just choose.

And on the MySQL prompt run these:
mysql> SET PASSWORD = PASSWORD('new_password');
mysql> \q

Again, replace new_password with your password.

And that’s it. It should now work as you’d expect.
I could finally access MySQL with Sequel Pro and do stuff again. Now to set up my test sites and development environment again…

Disclaimer: This is what worked for me. It’s not guaranteed to work for you. I pieced this together from 4 different topics on Stack Overflow, the MySQL manual and 2 forum threads. Use at your own risk. If things break, simply uninstall MySQL and start over.