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

backupMySQLmysqlbinlog

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

Best Answer

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.

Related Question