Shell script for backup binlog files for every 8 hours in MySQL

Question

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

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

Answer 2

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 !!!

• Vinod: 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.