Solved

Restore MySQL schema from a snapshot of the data directory

Posted on 2013-05-24
8
675 Views
Last Modified: 2013-05-31
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?
0
Comment
Question by:DanielAttard
8 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39196394
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
 
LVL 78

Expert Comment

by:arnold
ID: 39196576
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
 

Author Comment

by:DanielAttard
ID: 39196880
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:DanielAttard
ID: 39196932
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
 
LVL 78

Accepted Solution

by:
arnold earned 250 total points
ID: 39196984
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
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 39199745
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
 

Author Comment

by:DanielAttard
ID: 39199768
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
 
LVL 78

Expert Comment

by:arnold
ID: 39199870
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question