Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let the data have been backed up at time interval of 2 hr and taking backup at time "A" and after 1 hr Database crashes so how can I recover in MySql from Logs.

share|improve this question
    
As I know that in SQL server there is a mechanism of Tail log backup in recovery mode and to recover the data at the point of failure. –  JP Chauhan Nov 12 '12 at 6:46
add comment

2 Answers 2

First You have to restore your backup file which was taken at time A , then you have to read all MySQL binary logs using mysql utility mysqlbinlog.

For example, if your log files were numbered 1 to 49 before the backup and logs 50 to 52 were written after the backup,you will need to process logs 50 to 52 after restoring the backup. If your binary logs are named with a prefix of bin, the log processing command looks like this:

shell> mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql

All the binary log files that you want to process should be handled in a single mysqlbinlog command. There may be inter-file dependencies that will not be satisfied if you process them separately.

If a given binary log file was in the middle of being written during backup, you must extract from it only the part that was written after the backup, plus all log files written after that. Also, if you accidentally drop a table (or database, or some other act that causes data corruption), then you want to restore your backup and the incremental activity in binary logs.

If you do not want to re-execute the dropped table, you can run everything up to the bad command by capturing the binary log file only up to that point. To handle partial-file extraction, mysqlbinlog supports options that enable you to specify the time or log position at which to begin extracting log contents:

The --start-position option can be used to specify extraction beginning at a given log position.

After restoring a backup file, use the position numbers to process the binary log file. For example, you would use commands something like these:

mysqlbinlog --start-position=23456 binlog.000004 | mysql -uroot -p
share|improve this answer
add comment

It depends upon what backup method you maintain. How are you maintaining backups? Is it a full mysqldump or using binary logging? In your case I'd prefer opting for binary logging and you may take the the sqls triggered into binary log to construct your DR to the last point of your DB crash even if its too critical and no time eating process to build DR, you can go for Master/Slave still you might require binary logs for it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.