Recovering a mySQL instance from another user account (macOS)

I was using my friend's mac temporarily until my own was being serviced. So earlier this week, I deleted my user account on his mac which had a bunch of databases that I needed. I had backed up most of the user data before deleting that account but soon I realized that I missed out backing up the database. The fortunate thing and possibly the reason for this is that the database data directory generally resides outside the user account. In this article, I'll mention bullet points of how I got back this DB data using my friends user account (or you can even create a new one and do the same steps).

• Ensure mariadb is still installed by running brew install mariadb. I found that it was available but homebrew did not have access to the install directory since it was created from a different user account. So first we recover this by running:

sudo chown -R $(whoami) /usr/local/bin

If the command threw an error dump showing which folders it doesn't have access to, you can run the same command for each of the folders. I also ran brew upgrade mariadb to stay upto data with the newer mariadb install (although I wouldn't advise running this generally until you've backed up the databases).

• Next you have to figure out which data directory mariadb uses and run chown on that as well. This can be done by running:

brew services list

This will give a list of the running services and their run configurations. Just open the mariadb tasks' config and see the parameter --datadir in the XML--should be inside arguments block. In my case, this was --datadir=/usr/local/var/mysql. Now copy that path and run:

sudo chown -R $(whoami) /usr/local/var/mysql

At this point, you may wonder, why not simply copy the data directory into one's own mac and then open the database directly? The issue here is that the folder has only .ibd and .frm files which really aren't importable by mySQL. So I have to run the mysql server again with this data dir and then export it.

• Next, I had to stop any ongoing mysql server to ensure that I can start mysql smoothly. I do this by running:

brew services stop mariadb
ps aux | grep mysql

The last command is to check that there are no other mysql instances running. If the command returns any processes running mysql, then those need to be killed using kill -9 command or else, open Activity Monitor, use search to filter down mysql and force kill them manually.

Now, the fresh mysql instance can be started by using:

brew services start mariadb

Alternatively, you can even call the program found at the bin folder mentioned in the XML file of the homebrew service configuration i.e. take the first program string and replace mysqld_safe to mysql.server start and run the command. In my case, I did this by running:

/usr/local/opt/mariadb/bin/mysql.server start

It will return SUCCESS! if the service starts successfully. Otherwise, you would have to check the error logs (was in /usr/local/var/mysql folder for me) to figure out what went wrong and solve it by some googling.

And that's pretty much it, you can now backup the databases as you would normally do. I do this by running:

mysql -u root -p<password>
show databases

And this would give a list of all the databases. And then execute the backup command for each database you want to backup as:

mysqldump -u root -p<password> dbname > dbname.sql

And that's pretty much how it's done. And I airdropped the databases to my own laptop and was done.