How can I get MySQL data from an external hard drive
eie...@hotmail.com
PHP 4.3.8
Apache 2.0.51
Hi All!
Newbie..
I had a CRM Open Source application installed and running. Windows Xp
crashed. I was able to copy the contents of the entire hard drive onto
a USB External Hard Drive. I have to assume I also copied the data. I
do NOT have a current back up of my data.
I reinstalled WinXP and xammp along with the CRM app and now I need to
get my data from the External Hard Drive.
Am I able to simply copy folders/files from the external drive to my
rebuilt system? If so, which files do I need and where may I find them?
or.. Am I able to link to the data on the external drive and import the
data into my rebuilt system? If so, can you direct me to a procedure to
accomplish this?
or... Is there a better way to get my data?
ANY help is greatly appreciated.
Thank you
EiEiO
Bill Karwin
> I do NOT have a current back up of my data.
Do you think you should establish a practice of making backups in the
future? Read section 5.8 of the MySQL docs:
http://dev.mysql.com/doc/refman/4.1/en/disaster-prevention.html
> I reinstalled WinXP and xammp along with the CRM app and now I need to
> get my data from the External Hard Drive.
>
> Am I able to simply copy folders/files from the external drive to my
> rebuilt system?
This is not recommended as a reliable method of restoring data. But it
seems like it's your only chance at this point.
> If so, which files do I need and where may I find them?
MySQL keeps all data files under what's called the "data directory".
The location of the data directory should be defined in the entry for
"datadir" in your my.ini configuration file. You said you're using
MySQL 3.23, which is very old, so I don't know if the default data
directory was different then. On my installation of of MySQL 4.1 on
Windows, this is "C:\Program Files\MySQL\MySQL Server 4.1\data" by
default.
Under the data directory, MyISAM tables are stored in subdirectories
named per the database, in files with a ".MYD" extension. Files with
".MYI" extension store index data, and this can be reconstructed. Files
with ".FRM" extension store information about the table structure.
Recover these if you can, but these can be reconstructed too.
It's somewhat unlikely that you're using InnoDB tables in MySQL 3.23,
but if you are, these tables are stored differently from MyISAM tables.
InnoDB tables store data for all databases by default in a file called
"ibdata1" under the data directory (not in a subdirectory).
> or.. Am I able to link to the data on the external drive and import the
> data into my rebuilt system?
The table files must be under the data directory to be read by MySQL,
and MySQL can only have one data directory configured at a time.
You could, in theory, configure your MySQL server to use the path to the
data directory on the external drive as the data directory. Then create
backups of each of your databases, using the "mysqldump" tool. Then
reset the data directory config back to the default location on your C:
drive, and restore the backups you made.
You can change the data directory configuration by editing "C:\Program
Files\MySQL\MySQL Server 4.1\my.ini" and changing the value of the
datadir. For example, on my system I have MySQL 4.1 installed, so I
would change this line in my.ini:
datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/"
to this (assuming the USB external drive is on E:):
datadir="E:/Program Files/MySQL/MySQL Server 4.1/Data/"
You should verify your paths in the Windows file explorer. I don't have
MySQL 3.23 installed, and I don't know offhand what the default paths
are for that release.
Restart the MySQL service each time you make config file changes, to
apply the changes. Either use the Control Panel -> Admin Tools ->
Services app, or else open a command shell and use "net stop MySQL"
followed by "net start MySQL".
Good luck!
Regards,
Bill K.
EiEiO
Thank you for your help...
"Data Backup".... that will NEVER... EVER happen again. I could have
saved myself HOURS by backing up.
What I ended up doing:
Uninstalled the CRM application
Uninstalled xampp
Deleted all ini, php, config files
Reinstalled the packaged CRM app (with apache,php & MySQL) as I did
originally
Rebooted into Safe Mode
Deleted the installed htdocs folder & pasted the htdos folder from my
ext. H/D
Deleted the installed mysql folder (it was not under htdocs) & pasted
the mysql folder from my ext. H/D
Rebooted Windows
Logged in to CRM .. All of my settings, custom pages and MOST
importantly MY DATA was intact
IMMEDIATELY!!! Ran a backup
Thank you again for getting me onto the right road.
EiEiO
Bill Karwin
> Thank you for your help...
> "Data Backup".... that will NEVER... EVER happen again. I could have
> saved myself HOURS by backing up.
Yes, many software workers have this happen to them a number of times
before they "see the light" that the time invested into making backups
saves them a lot of gray hairs in the long run. It can also be a
challenge to convince managers that this is worthwhile...
Also important is automating the process of creating backups, so they
happen regularly without you having to remember to do it every time.
Windows has a scheduler service that can run a BAT file, which you
design to execute the mysqldump tool.
See for example:
http://www.pctechguide.com/tutorials/ScheduleTasks.htm
Another tip is to store your database backup files on a separate
physical machine from the one where the backups are made. That way if
the hard disk catches fire or someone pours their coffee into the
machine (this does happen!!), you still have the data.
I'm glad you got your data back intact, even if it was a lot of work!
Regards,
Bill K.