Restoring Databases After Computer Crash

[jcmonty]

I have SQLYog version 6.x (6.03, I think) that was running on a Windows XP Pro box. This computer crashed and I am now using a temporary Win XP Home box. I haven't installed SQLYog on the new machine, yet, but plan to do that soon. I had several MySQL databases that I was managing with SQLYog on the old computer before it crashed, but unfortunately, didn't have .sql dumps of the databases before it crashed. I do, however, have the old hard drive that I pulled out of the old machine and have installed it on the new temporary machine.

My question is, if I re-install MySQL and SQLYog on the new machine, can I somehow import that raw MySQL data files to recover the databases from the old hard drive?

Thanks.

[peterlaursen]

This question is not SQLyog related. You generally cannot read mysql data any other way than letting a server use the data (only exception I can think of is data stored using the CSV storage engine). SQLyog (and other clients) does not read data on disk – the server does. Clients only send SQL statements to the server.

The most simple way to rescue the old data is simply to copy the complete mysql 'data' folder from the old drive to a new MySQL installation (stop MySQL while doing). Actually you can even do this across different platforms. MySQL data is in all users' 'Application Data' folder (recent server versions) or as a subfolder to the server installation folder (older versions).

However the mysql version on new machine will need to be the same or higher than the old server version, as downgrading system tables is almost impossible. If new MySQL version is higher than the old one you must execute the 'mysql_upgrade' program as the first thing after starting the new server with the old data folder.

So:

1) install MySQL

2 stop MySQL service

3) replace the MySQL data folder with the one from the old drive

4) start MySQL service

5) with command line navigate to server /bin folder and execute mysql_upgrade (“mysql_upgrade -uroot -p” will normally be enough, but there are more parameters to use for special setups)

6) Install SQLyog and you will see your old data used by new server.

note: the 'command line' referred to in 5) is not the mysql command line client, but the Windows command line ('DOS box').

[jcmonty]

Peter,

Thank you for your detailed reply and I apologize that my post really wasn't related to SQLYog.

Before I had seen your post, I had already attempted copying in the files from the old hard drive data folder into the data folder of the new MySQL installation on the computer I am using now. I then fired up SQLYog and discovered that it did, in fact, show all of my old databases. Unfortunately, only the databases that had *.myd files associated with them showed the tables. The databases that had just *.frm files associated with them didn't display their tables.

Anyway, thanks again and I'm off to the mysql.com lists/forums to see what I can discover.

Cheers.

[peterlaursen]

if you lost the .myd files then you will have no data. (myd = mysql data)

[peterlaursen]

.. but that (my last post) applies only to MYISAM tables. If you have InnoDB tables there will only be a .frm file for every table – no datafile (.myd) and no indexes file (.myi) as all InnoDB data are not stored in the table folder (unless you had set the innodb per_table option) but in ibdata* and ib_logfile* (for non-commited transactions) files.

You have to copy the *COMPLETE data folder* from the old harddrive in one operation. Do not attempt to copy data for individual databases and tables if you use InnoDB.

[Author]

Posts