1

I'm writing a shell script for point in time recovery for MySQL Instance. Our requirement is backing up Binlog for every 8 Hours (Daily 3 times) using mysqlbinlog and it should backup binlog starting from last day full database backup time to current time.

Suppose if full database backup happens at 2:00 AM morning and binlog backup happens at 6 AM, 12 PM and 6 PM, then first binlog backup should backup from 2:00AM to 6:00 AM etc.

After completing daily full database backup, it will store output of "Show master Status" in a file called "master_status.txt" and I'm calling it from binlog backup script as below, but whenever there are more than 1 binlog file generated b/w full db backup and binlog backup then script is not taking binlog backup for all binlogs.

binlog=`cat master_status.txt | awk '{print $1}'`
dat="$(date +'%d_%m_%Y_%H_%M_%S')"
mysqlbinlog -u$DB_USER -p$DB_Password "$bin_log" --to-last-log >> "binlog_file_$dat".sql

Version : 5.5.x Engines: InnoDB and MyISAM

1

For those who still need this. May try using binlog puller module by percona folks. It works perfect and you may add a watchdog for it.

0

Instead of calling SHOW MASTER STATUS;, which shows the current binary log, you should use SHOW BINARY LOGS;. It will show all binary logs currently on disk.

Here is an idea: run this every six(6) hours

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 8 HOUR;

This erases all binary logs except the ones with the last 8 hours on binary log events. This may be one or two binary logs. You must then record the list of binary logs from SHOW BINARY LOGS;

Give it a Try !!!

  • Hi Roland, Thanks for reply. Yes, you are right ! But our requirement is bin log files should not be deleted/ flushed for 10 days (before retention). Could you provide me some alternate solution. – Vinod May 1 '15 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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