Recovering lost MySQL data after an Homebrew upgrade in MacOS

Switching from Intel to M1 was like a breeze thanks to Rosetta 2. Sometimes things are not going that smooth though. I explain how to get your local SQL data back after a brew upgrade.

If you’re still using packages remaining from your old Homebrew installation, there’s a big chance that your packages are still sitting in the old brew directories and called via Rosetta.

For most of the time, this extra layer won’t cause any trouble in terms of compatibility issues or performance loss. However, when you attempt to upgrade one or more interdependent packages, you start to suffer from path change issues.

In my case, I was simply trying to install a package which is irrelevant with my local development environment; but Homebrew suddenly came up with a message indicating that my local MySQL server was installed without a root password.

What?! I didn’t want a MySQL installation, did I? What happened to my existing data then?

I was right. When I attempted to restart the MySQL service and connect to my localhost, I saw no existing databases. They were gone.

Fortunately, I remembered that I had migrated from an existing Mac OS installation of an Intel Mac, and my MySQL data directory was created by the Homebrew version which was using /usr directory as the base, instead of the new one using the /opt directory.

Apparently, the new MySQL package installed by Homebrew was using a different path than the old one.So the solution for me was just to copy the old data to the new location.

Let’s do it together in case you’re in the same trouble.

Step 1: Stop the running MySQL instance:

brew services stop mysql@5.7 

(If this doesn’t work, just open the Activity Monitor, search for the mysqld process and force kill it).

Step 2: Rename the new data directory (to get a backup in case you already created something)

cd /opt/homebrew/var/ && mv mysql mysql_temp_backup

Step 3: Copy the old data:

cp -R /usr/local/var/mysql /opt/homebrew/var/mysql

Step 4: Start the MySQL service

brew services start mysql@5.7

That should be all. Now when you try to access your localhost, you should be seeing your existing databases and tables.

Once you ensure that you got everything back, you can delete the old data directory (/usr/local/var/mysql) to free up some disk space. Caution: Do not delete your old data directory until you ensure that your MySQL is running from /opt/ directory. You can double check it in the terminal with the command:

ps aux|grep mysql

The output should include “datadir=/opt/homebrew/var/mysql”.