Solved

Restore MySQL schema from a snapshot of the data directory

Posted on 2013-05-24
8
638 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 24

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 76

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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 76

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 76

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now