One of the most difficult situations that a system/database administrator may comprehend is the loss of mysql table data or accidental dropping/overwriting of MySQL database being hosted in a production environment. In a recent study, it was revealed that even now, of the live servers being hosted worldwide, only about 30% employ effective backup/recovery methods that would assure data recovery at the worst case scenario.I had also gone through such a travail recently and found that about 65% of the data were permanently lost which could be recovered only through professional hands employing high-level data-carving/forensic tools.Anyway, i could be very much near the solutions even though it took 4-5 days to go through all possible options of recovery and finally recover data,and put in place, an alternative measure for future. I think
many people will be interested in such solutions:
The Golden Rule : The first thing to be done after any db is created is to be a good backup mechanism.Negligence in this would cost you money,time and most importantly, your reputation.
Step 1: Setting the stage
Firstly, if you are using the database as a backend of a web application and you suddenly find database crashed, but you feel that the data is still being displayed in the application fully or partially, dont waste even a moment and take an online db backup through mysqldump or a GUI backup tool like mysql administrator.Maybe the db crash had occurred and data still persists in the cache which can be extracted through mysqldump.
Another important thing you may look forward to at this juncture is if 'mysql binary logging' is enabled in the server.Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.Just check if following directive is given uncommented in your server's '/etc/my.cnf' configuration file:
log-bin = /var/log/mysql/bin.log
and the directory like the following one is created:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
If this doesn't work, before you attempt recovery on deleted data in any case, it is advisable to isolate the system from further access from network and from further human intervention.Unplug the system from network and using a proven disk imaging solution like Norton Ghost or DriveImage,take an image of entire hard-disk and restore in a system having identical configuration.Moreover, test the mysql server in this copy system if it has the running copy of the db's in the original system so that database dumps can be made and restored in original system.This can be your insurance on playing with and attempting various methods in the filesystem/DB in question.Never attempt recovery in the live system or otherwise, you may end up with a much difficult situation.
Step 2: Entry-level procedure
This should be followed next if the database in question is dropped and no identical database name had come into existence.This ensures recreating all the tables and form data in '/var/lib/mysql' folder(if it's a linux server) using simple file-recovery software. The most effective ones are:
Simply find in which linux partition, the '/var' directory resides and after imaging and restore, attempt recovery through a windows machine by connecting the imaged HDD(not that of live server) as slave.
Step 3: Advanced recovery methods
In case, the db being dropped was later created once again or overwritten with another schema, you cannot ensure recovery by above methods as the recovery software only detects the latest version of the form names and not which was overwritten.Then we should directly attempt database-specific tools as below:
If the database in question is using MyISAM format, the below link maybe of some help.(Also some commercial solutions are available for professional recovery).
But if the DB is using InnoDB format(most widely used in production environments)please go thru some of the methods below:
Above are one of the most accurate and to-the-point recovery procedures available for InnoDB
Step 4: Prevention is better next time
Now as recovery is made, lets not leave it here.The most essential and final step in such a scenario should be putting a backup/preventive mechanism in place.
Note: Please note that the above methods doesn't at all ensure 100% recovery of data and scenario may arise when any this cannot work with destroyed data at all.The author doesn't give any guarantee that the above are risk-free and should be attempted carefully and by experienced hands.
many people will be interested in such solutions:
The Golden Rule : The first thing to be done after any db is created is to be a good backup mechanism.Negligence in this would cost you money,time and most importantly, your reputation.
Step 1: Setting the stage
Firstly, if you are using the database as a backend of a web application and you suddenly find database crashed, but you feel that the data is still being displayed in the application fully or partially, dont waste even a moment and take an online db backup through mysqldump or a GUI backup tool like mysql administrator.Maybe the db crash had occurred and data still persists in the cache which can be extracted through mysqldump.
Another important thing you may look forward to at this juncture is if 'mysql binary logging' is enabled in the server.Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.Just check if following directive is given uncommented in your server's '/etc/my.cnf' configuration file:
log-bin = /var/log/mysql/bin.log
and the directory like the following one is created:
'/var/log/mysql/bin.xxxxxxx' (where x maybe any number)If both exists, we can infer that binary logging is enabled. Please use the following documentation for further proceding with recovery:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
If this doesn't work, before you attempt recovery on deleted data in any case, it is advisable to isolate the system from further access from network and from further human intervention.Unplug the system from network and using a proven disk imaging solution like Norton Ghost or DriveImage,take an image of entire hard-disk and restore in a system having identical configuration.Moreover, test the mysql server in this copy system if it has the running copy of the db's in the original system so that database dumps can be made and restored in original system.This can be your insurance on playing with and attempting various methods in the filesystem/DB in question.Never attempt recovery in the live system or otherwise, you may end up with a much difficult situation.
Step 2: Entry-level procedure
This should be followed next if the database in question is dropped and no identical database name had come into existence.This ensures recreating all the tables and form data in '/var/lib/mysql' folder(if it's a linux server) using simple file-recovery software. The most effective ones are:
- DiskInternals Partition Recovery
- R-Studio Corporate Edition
- File-Scavenger
Simply find in which linux partition, the '/var' directory resides and after imaging and restore, attempt recovery through a windows machine by connecting the imaged HDD(not that of live server) as slave.
Step 3: Advanced recovery methods
In case, the db being dropped was later created once again or overwritten with another schema, you cannot ensure recovery by above methods as the recovery software only detects the latest version of the form names and not which was overwritten.Then we should directly attempt database-specific tools as below:
If the database in question is using MyISAM format, the below link maybe of some help.(Also some commercial solutions are available for professional recovery).
But if the DB is using InnoDB format(most widely used in production environments)please go thru some of the methods below:
- Below link has some firsthand information as well as good documentation of InnoDB recovery toolavailable in 'sourceforge' which is an open projects repository.
- Another InnoDB recovery tool from Percona Ltd
Above are one of the most accurate and to-the-point recovery procedures available for InnoDB
Step 4: Prevention is better next time
Now as recovery is made, lets not leave it here.The most essential and final step in such a scenario should be putting a backup/preventive mechanism in place.
- Most popular one is the Automysqlbackup script that takes daily/weekly/monthly backups of all mysql db's in a server and which has many customisable options.Please check it out here:
- Mysql website has also listed out offcial backup procedures here:
- You could also employ tools like zmanda backup at some expense:
- Finally,some real high-end geek stuff if you have time and resources for implementation ie, Replication
Note: Please note that the above methods doesn't at all ensure 100% recovery of data and scenario may arise when any this cannot work with destroyed data at all.The author doesn't give any guarantee that the above are risk-free and should be attempted carefully and by experienced hands.
