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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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
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:
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:
After restoring a backup file, use the position numbers to process the binary log file. For example, you would use commands something like these: