LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices



Reply
 
Search this Thread
Old 01-06-2011, 05:16 AM   #1
Ajith@NeST
LQ Newbie
 
Registered: Feb 2009
Posts: 8

Rep: Reputation: 0
Unhappy MySQL tables dropped : How to recover


Hello,

I am working now as a system administrator with a premier university in Kerala State of India. We are having our webservers and corresponding databases hosted in Fedora linux which have sufficient number of projects of university hosted on them.

One of the units of the University dealing with the tabulation and publications of marks online (Undergraduate courses)recently reported their data updated in the servers in our facility being lost. We found that this occurred due to non-communication of the purpose of database with server administrator and the subsequent dropping of the database without notice.Issue is that the DB contained important mark details of about 126 UG courses of the University updated over 2 months which require considerable manpower and time to update once again.

Is there any way to recover a dropped DB in MySQL (Version 5.0)or to restore DB to previous form prior to the dropping of table?? It would be very helpful if someone suggest a way.FYI, the binary logging option was also not enabled in the server which could have enabled 'mysqlbinlog' utility to recovert the lost DB.

Please Help

Thanks and Regards,

Ajith Kumar
 
Old 01-06-2011, 05:27 AM   #2
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291
Hello,

The only two ways I know of to recover a dropped database is by using the binlog utility, which in your case would be impossible if it wasn't activated and the second one is by restoring from backup, which you do not mention in your post. Do you have a backup? If not, then let me say that's very poor sysadmin work. Backup is one of the most important tasks and also the most overlooked and forgotten. Furthermore, if the data you had is so important as you state it is, then you SHOULD have a backup made regularly.

Kind regards,

Eric
 
Old 01-06-2011, 07:22 AM   #3
choogendyk
Senior Member
 
Registered: Aug 2007
Location: Massachusetts, USA
Distribution: Solaris 9 & 10, Mac OS X, Ubuntu Server
Posts: 1,189

Rep: Reputation: 105Reputation: 105
If it is that important to the University, then you definitely need a backup policy and plan in place. You could get details on your options and set it up yourself based on information here http://dev.mysql.com/doc/refman/5.1/...p-methods.html, or you could implement ZRM (Zmanda Recovery Manger) for MySQL, which integrates it all for you -- http://www.zmanda.com/backup-mysql.html. That is open source, but you can also get a support contract from Zmanda if needed. They have headquarters in both India (Pune) and the U.S. (Sunnyvale, CA).

Last edited by choogendyk; 01-06-2011 at 07:58 AM.
 
Old 01-07-2011, 01:46 AM   #4
Ajith@NeST
LQ Newbie
 
Registered: Feb 2009
Posts: 8

Original Poster
Rep: Reputation: 0
Is it possible to recover the database using file-recovery software by simply finding the MYD and other files deleted and restoring it to database?? We had earlier explored enabling binary logging, but couldnt start server after this option was given in my.cnf.Thats why the option went not implemented.

Last edited by Ajith@NeST; 01-07-2011 at 01:48 AM.
 
Old 01-07-2011, 02:22 AM   #5
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291Reputation: 1291
Hello,

AFAIK it's possible to restore the database if you have all the files that make up the database (frm, MYD, MYI) by copying them into the database folder and restarting MySQL. The problem is that your permissions on the database and tables will be lost and you'll need to set them again. If you have problems / errors with the MyISAM tables then you could check out this page for repair options. Since you mentioned MYD I'm assuming you used MyISAM for your tables and not InnoDB.

One very important advice, if you succeed in restoring your database, put a backup plan/method in action right away. It'll save you lots of headaches. You can have it set up in less then five minutes using AutoMySQLBackup for example.

Best of luck.

Kind regards,

Eric
 
  


Reply

Tags
data recovery, linux, mysql, server


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql ibdata1 is very large 95% drive in use - dropped tables but size ibdata remains guest Linux - Server 2 03-20-2009 01:53 PM
Recover cacti tables with rrd files, how? geraldomanaus Linux - Server 1 08-26-2008 10:05 PM
mysql reinstallation distorted by previous tables in /var/mysql mad4linux Linux - Software 0 10-04-2005 02:39 PM
mysql dropped database.... can it be restored? figmentium Linux - General 2 08-11-2005 04:13 PM
How do I recover old partition tables in linux? PsychoFarmer Linux - Software 5 01-04-2004 06:14 PM


All times are GMT -5. The time now is 01:43 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration