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

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

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).

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.

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