Thursday, February 17, 2011

Script to purge Mysql binary logs

0 comments


Script to purge or  remove Mysql binary logs is purge_bin_log.sh

It is designed in such a way that it executes every night and removes binary logs older than 7 days.
Here, db.conf file is used to store database connection parameters.

-----------
db.conf
-----------

#/bin/bash
#DB Connection paramters
DBHOST=0.0.0.0
DBNAME=test
DBUSER=user
DBUSERPWD=std123456
DBPORT=3306

-----------------------
purge_bin_log.sh
-----------------------

#!/bin/bash
DBHOST=$(cat db.conf | grep -w DBHOST | awk -F'=' '{ print $2 }' )
echo "DB HOST = "$DBHOST

DBNAME=$(cat db.conf | grep -w DBNAME | awk -F'=' '{ print $2 }' )
echo "DB NAME = "$DBNAME

DBUSER=$(cat db.conf | grep -w DBUSER | awk -F'=' '{ print $2 }' )
echo "DB USER = "$DBUSER

DBUSERPWD=$(cat db.conf | grep -w DBUSERPWD | awk -F'=' '{ print $2 }' )
echo "DB PASSWORD = " $DBUSERPWD

DBPORT=$(cat db.conf | grep -w DBPORT | awk -F'=' '{ print $2 }' )
echo "DB PORT = "$DBPORT

purgedt=$(mysql $DB -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "select date_sub(current_date(), interval 7 day)" -s -N )
echo "Purge binary logs before $purgedt started at $(date +%m%d%y_%H:%M)"
str="PURGE BINARY LOGS BEFORE '"$purgedt"'"
mysql $DB -h $DBHOST -u $DBUSER -P $DBPORT --password=$DBUSERPWD -e "$str"
success=$?
if [ $success = 0 ];
then
   echo "Purge binary logs before $purgedt completed at $(date +%m%d%y_%H:%M)"
else
  echo "!!!Failed!!!"
fi     #if [ $success = 0 ] then
exit 0

A corn job is also created to execute the script at 00:01 every night.
01 00 * * * /root/dbbackup/backup_scripts/purge_bin_log.sh > /root/dbbackup/backup_scripts/purge_bin_log.log

Leave a Reply

Labels