Recovering a mySQL instance from another user account (macOS)
Tags: mysql, mariadb, recovery, database
Published: 2020-12-25 13:51:49

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:
```bash
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:
```bash
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:
```bash
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](https://dba.stackexchange.com/a/16911) 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:
```bash
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:
```bash
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:
```bash
/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:
```bash
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:
```bash
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.