Restore MySQL schema from a snapshot of the data directory

Is it possible to restore MySQL schema from a snapshot of the data directory?  I experienced corruption in one of my tables and I am having difficulty restoring my data from my only .SQL backup file (which file happens to be 8.5GB in size).  I do have a snapshot of the data directory from a couple of days ago, but I can't seem to figure out how to connect to that directory.  I have tried to modify the configuration file by simply changing the path to the datadir, but this does not work.

Any ideas?
DanielAttardAsked:
Who is Participating?
 
arnoldConnect With a Mentor Commented:
what you can do is restore the file as a new database.
items in [] are optional depending on your setup.
the only thing to make sure is that the backup does not include a use <databasename> directive.


mysql [-u root] [-ppassword] newdatabasename < databasebackup.sql


The binlogs are used to roll something back in by restoring the database first to a state based on a full backup and then the binlog is used to replay transactions to bring the state of the database closer to a time in point before a corruption/mistaken command was issued.
0
 
Tomas Helgi JohannssonCommented:
Hi!

What sort of underlying db engine are you using ? MyISAM or InnoDB ?
What error are you getting when you are restoring the sql backup file (I'm assuming that it is a file generated through mysqldump or similar ) ?

There are some configurations on the mysql server side to consider when restoring large databases.

Regards,
    Tomas Helgi
0
 
arnoldCommented:
The snapshot was likely taken in a state that may still have the database requiring repair.

Are you saying that you can to run check table tablename?
And repair table tablename (quick|extended|use_frm)

Does the system have bin logging?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DanielAttardAuthor Commented:
Hi Arnold, thanks for the comment.

I have a couple of snapshots, each a few days old.  I believe that my db may have become corrupt within last day, restoring to a couple days ago should be fine for my purposes.

I am not familiar with the various commands that can be run from the mysql command prompt.  I guess I should read-up on them and then consider using them.

Yes, the database has bin logging, probably unintentional on my part since I ignore them.  Was even considering to delete them.  Should I delete them if I have no need to ever roll anything back to a prior date?
0
 
DanielAttardAuthor Commented:
Hi Tomas, thank you for responding to my question.  

My tables are InnoDB, and the sql backup file that I have is 8.5GB, which I am learning is perhaps too much data to have in a single backup file.  The only tool I could find that would reliably open this huge sql file was 010Editor, which enabled me to inspect the file and see the good part, and then the corrupt part of my file.  Since my .sql backup file appears to be corrupt, I am thinking that I will have to restore my databases and tables from a snapshot of the data directory that is only 1 or 2 days old.  

Here are the steps that I took to update the location of my data in mysql:

1.  First step is to stop the mysql service.  This can be done from the mysql command line terminal window, or do start->run->services.msc and you can then find the mysql service where it can be stopped.

2.  Next step was to update the location of the datadir variable in the mysql configuration file, located at:

C:\Program Files\MySQL\MySQL Server 5.1  

Edit the my.ini file and look for the datadir variable in the [mysqld] section.  The path in my case was:  

datadir=C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/data

You need to copy-paste this line, comment out the original line so you keep it for reference, and then edit the new line as necessary and point to the location of where your data is located.  Take note of the forward-slash, not the traditional back-slash used in Windows.  I don't know why this is the case, but I've read somewhere that this setting requires forward slashes.  (If someone can explain what this is about, I would be interested to know.)

3.  The next step involves moving the required data files into place.  The new path for the data will need to contain the ibdata file(s) which hold all of your data, and then the folders for each database which contain the .frm files.  These files contain the information about the table namespace, structure, type, etc.  The bin files are not required.  They represent a log of transactions run against the database.  There may also be ib_logfile0 and ib_logfile1 files which are also not required.  If these files are not present, they will be recreated by mysql on the next database startup.

4.  The final step after the datadir variable has been updated, and the files have been moved into place, is to startup the mysql service again.  This can be done from the mysql terminal window, or invoking services.msc and then finding the mysql service and then start.

These are the steps I followed to restore mysql schema from a historical snapshot of the data directory.  If I've left anything out, please let me know.
0
 
ZberteocConnect With a Mentor Commented:
If you have a snapshot of the data directory why not replace the directory content with the one from the snapshot? This way you don'y have to modify any ini file and/or datadir variable. So the steps would be:

1. Stop the MySQL service
2. Delete the files in the data directory to make room
3. Copy the snapshot content to the original location
4. Start MySQL
0
 
DanielAttardAuthor Commented:
Thanks for the comments Arnold and Zberteoc.  Your suggestions make perfect sense.  I will give them a try next time I run into this issue.
0
 
arnoldCommented:
I would recommend against deleting any data even if it appears to be wrong at the moment.
moving it provides for a fall back position if the snapshot is worse.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.