0

I was given some research data on a hard drive. All the files are in one folder and consists of .frm files, .MYD files, .MYI files, .TRG files and .TRG files.

I downloaded and installed MAMP so that I can run an MySQL Server. I launched PHPMYAdmin in a browser and chose the import option. I then navigated to the data folder and realized that it was looking for a .zip file with the structure .sql.zip. I then proceeded to zip all of the content to comply and according to the WinZip dialog box, it was going to take 3 hours! This cannot be the correct action.

I found this solution from 8 years ago (derobert): Recover Database Files From MYD MYI FRM Files which has a linux solution (copy the files into the /var/lib/mysql/dbname directory)

There was also a solution for Windows in which a single .frm file and a ibdata1 file is copied to the data folder (alnel). Since my intention was not to copy the data to my notebook but, instead, read it directly from the drive, I tried another solution mentioned and it turned into a nightmare....

I created a symbolic link in the data folder: c:\MAMP\bin\mysql\data called mydata

c:\mklink /D mydata e:\MyResearchData

This created a link as expected. I then reloaded PHPMyAdmin expecting to see it listed. I did not. Recognizing that the /D switch is for a soft link, I tried a hard link using /H. However, that didn't over-write the previous link. I then proceeded to delete the link and it deleted all of my data! Recovery was not as simple as if I had deleted the data from the C:\ as it was not in the Recycle Bin. I was able to use file recovery software but now I am gun-shy about trying anything unproven.

I did eventually copy all the files into the MySQL data folder (suggested by Vishal)(c:\MAMP\bin\mysql\data\MyResearchData) and refreshed PHPMyAdmin ... no luck.

I'm hoping someone can lead me in the correct direction.

Thank you in advance!

Adrian Hood Sr
  • 405
  • 5
  • 18
  • Copy everything from /myresearchdata to /data. The ibdata file must be in the/data folder. Did that work? – Krish Apr 04 '18 at 22:17
  • Thank you! It turns out that was using the incorrect path. The correct path was c:\mamp\db\mysql\ I copied the folder into this folder and refreshed phpMyAdmin and it appeared as expected. – Adrian Hood Sr Apr 05 '18 at 02:16
  • Also, to address my original question on how to access files on an external drive. I was able to create a hard symbolic link (using /J) inside the `c:\mamp\db\ ` folder. Here is the command I used after entering the dos shell in administrative mode. `mklink /J MyResearch F:\MyResearch` – Adrian Hood Sr Apr 05 '18 at 18:38

1 Answers1

0

You've figured out one possible way. The other way is to find My.ini (mysql config file) in which you will find datadir and basedir like

# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

change the datadir to whatever location you have your MySQL data and restart your server. Since your current data dir is "c:\mamp\db\mysql" look for that line in the config file.

if you are going to use it often, i'd suggest installing MySQL server, workbench and mysql notifier using mysql installer for windows. It's much more easier to manage. With workbench you can do many things.

Krish
  • 5,917
  • 2
  • 14
  • 35
  • Not that I need it now but what if I had two different databases on different external drives? Is that a matter of just appending another line with `datadir = SecondDataBasePath` – Adrian Hood Sr Apr 05 '18 at 20:19
  • keep only one server, export & import the other database using mysqldump or workbench. That way, you have both db at the same time within your schema and can do queries – Krish Apr 05 '18 at 20:21
  • I know next time to request the mysqldump output instead of just the raw files. – Adrian Hood Sr Apr 05 '18 at 21:11