how to recover mysql database from db files

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

how to recover mysql database from db files

Post by s.dot »

Hello,

I have successfully recovered 1 1/2 years worth of work that I thought was gone (6 months later, no less - after endlessly writing and deleting from the same hard drive during that 6 months) and I am nothing short of elated right now! The tool I used was called R-Linux - free and amazing!

Now that I have my work restored, the only thing missing is the databases the scripts connect to. I have recovered ALL of the database files I am confident because it appears my entire old linux partition has been recovered.

So my question would be, how do I go about restoring the databases from the flat files (Ubuntu OS, MySQL database)?

I would like to copy them over to my windows installation and get them working in phpmyadmin.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: how to recover mysql database from db files

Post by Apollo »

Thanks for reminding me of automating my daily backups :)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: how to recover mysql database from db files

Post by s.dot »

No problem dude!
Sad part is i had backups...... locally. Have to have them on 3rd party servers.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: how to recover mysql database from db files

Post by Weirdan »

s.dot wrote:I would like to copy them over to my windows installation and get them working in phpmyadmin.
Normally you just copy them over to where your new mysql server keeps its db files and that's all.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: how to recover mysql database from db files

Post by Benjamin »

It needs to be the same MySQL version, and the innodb_data_file_path needs to be set to the same size in my.cnf.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: how to recover mysql database from db files

Post by s.dot »

I can't seem to get it.

I copied across the data files:

ibfile_log0
ibfile_log1
ibdata1


That didn't work, phpmyadmin gives me the error:
Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly.

The only relevant option i see in my.ini is innodb_log_file_size=5M (which is the correct size of ibfile_log0 and ibfile_log1)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: how to recover mysql database from db files

Post by Benjamin »

Make a backup, stop MySQL, delete those files, then restart MySQL. It will give you an error claiming that it failed to start. Wait about 3 to 5 minutes and test if MySQL is running. If so, you should have access.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: how to recover mysql database from db files

Post by s.dot »

I did an install of linux again and did a fresh install of mysql
I copied the recovered /var/lib/mysql over to the new install of mysql /var/lib/mysql
no errors this time, phpmyadmin just informs me that there aren't any databases

.. a step in the right direction
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
debrah.h48
Forum Newbie
Posts: 3
Joined: Tue May 11, 2010 7:43 am

Re: how to recover mysql database from db files

Post by debrah.h48 »

Here's a page from the MySQL manual that might help you solve your problem: http://dev.mysql.com/doc/mysql/en/backing-up.html

I'm not too sure whether you can do it just on the basis of .frm files - do you have the .ibd files or ibdata file with them too ? If you have those the process becomes far easier... Read that page and see if it helps - and if it does, please come back here and make a post on what you did to solve your problem. That'll certainly be very helpful for all of us..

Thanks & Regards,
Post Reply