gistfile1.txt

#!/bin/sh

#Backup mysql binary logs
#Developed In: bash — Contributed by: Partha Dutta
# http://forge.mysql.com/tools/search.php?page=7

# mysqlbinlogbackup - backup binary logs

slave=
slaves=
slave_port=3306
synch_with_slave=0
opt_binlog_dir=0
backup_host=localhost
backup_port=3306

# Change below for the MySQL user/password used for admin purposes
backup_user=backup_user
backup_password=backup_password

binlog_dir=/var/lib/mysql/log/bin
backup_dir=/var/tmp

# Change below to host where binlogs will be stored
stored_backup_host=localhost

backup_host_dir=/var/backup/db
log_file=/var/log/mysql/binlog_backup.log
no_compress=0
purge_only=0
common_opts=
transfer_delete=0

Log()
{
	echo "`date` : $*" >> $log_file
}

Usage()
{
	cat << EOF_OPTIONS
Usage : mysqlbinlogbackup options

   --host                      Host to connect to (Default : $backup_host)
   --port                      Port to connect to (Default : $backup_port)
   --user                      User to connect to MySQL (Default : $backup_user)
   --password                  Password for user
   --slave                     Slave host to check binlog position
                               (Specify multiple slaves by using --slave
                               multiple times)
   --slave-port                Port for slave host (Default : $slave_port)
   --binlog-dir                Directory for binary logs
   --backup-dir                Backup directory to store binary logs
   --backup-host               Remote server for binary log storage
                               (Default : localhost)
   --backup-host-dir           Remote directory to store binary logs
                               (Default : $backup_host_dir)
   --no-compress               Do not perform compression
   --purge-only                No backup, just purge binary logs.
   --no-transfer-delete               Do not transfer or delete backup tgz file. 
EOF_OPTIONS
}

parse_args()
{
	for arg do
		case "$arg" in
			--host=*)
			backup_host=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--port=*)
			backup_port=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--user=*)
			backup_user=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--password=*)
			backup_password=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--slave=*)
			slave=`echo "$arg" | sed -e 's/^[^=]*=//'`
			if [ -z "$slaves" ]; then
				slaves=$slave
			else
				slaves="$slaves $slave"
			fi
			synch_with_slave=1;
			;;
			--slave-port=*)
			slave_port=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--binlog-dir=*)
			binlog_dir=`echo "$arg" | sed -e 's/^[^=]*=//'`
			opt_binlog_dir=1
			;;
			--backup-dir=*)
			backup_dir=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--backup-host=*)
			stored_backup_host=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--backup-host-dir=*)
			backup_host_dir=`echo "$arg" | sed -e 's/^[^=]*=//'`
			;;
			--no-compress)
			no_compress=1
			;;
			--purge-only)
			purge_only=1
			;;
                        --no-transfer-delete)
                        transfer_delete=1
                        ;;
			--help)
			Usage
			exit 0
			;;
			-?)
			Usage
			exit 0
			;;
			*)
			echo "Invalid argument $arg"
			Usage
			exit 1
			;;
		esac
	done
}

mysql_options()
{
	common_opts="--user=$backup_user --password=$backup_password"
	if [ "$backup_host" != "localhost" ]; then
		common_opts="$common_opts --host=$backup_host --port=$backup_port"
	fi
}

mysql_command()
{
	mysql $common_opts --batch --skip-column-names $1 -e "$2"
}


################################################################################

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH

parse_args $*
mysql_options

# Find out what type of OS we are running on
cat /proc/version | grep -i "red hat" > /dev/null
if [ $? -eq 0 ]; then
	REDHAT=1
else
	REDHAT=0
fi

Log "[INIT] Starting MySQL binlog backup"

master_binlog=`mysql_command mysql "show master status" 2>/dev/null | cut -f1`
Log "Current binary log is: $master_binlog"

if [ $synch_with_slave -eq 1 ]; then
	Log "Checking slaves' binary log execute pos"
	rm -f /var/tmp/binlogs.txt
	for i in $slaves
	do
		binlog=`mysql --host=$i --port=$slave_port --user=$backup_user --password=$backup_password --vertical mysql -e "show slave status" 2>/dev/null | grep "Relay_Master_Log_File" | awk '{print $2}'`
		if [ -z "$binlog" ]; then
			Log "Failed to retrieve binlog from $i"
			exit 1
		else
			Log "$i: $binlog"
			echo $binlog >> /var/tmp/binlogs.txt
		fi
	done
	slave_binlog=`sort /var/tmp/binlogs.txt | head -n 1`
	Log "Oldest slave binlog = $slave_binlog"
	if [ $slave_binlog != $master_binlog ]; then
		Log "Warning! Will use ${slave_binlog} instead of ${master_binlog} as current"
		master_binlog=$slave_binlog
	fi
fi

if [ $purge_only -eq 1 ]; then
	Log "purge-only option set, Purging binary logs to $master_binlog"
	mysql_command mysql "purge master logs to '$master_binlog'"
	Log "[END] Backup complete"
	exit 0
fi

first_log=
last_log=
copy_status=0
backup_list=/tmp/binlog.list.$$

# Determine binlog dir
if [ $opt_binlog_dir -eq 0 ]; then
	if [ $REDHAT -eq 1 ]; then
		bdir=`my_print_defaults -c /etc/my.cnf mysqld | grep "^\-\-log-bin=" | head -n 1`
	else
		bdir=`my_print_defaults -c /etc/mysql/my.cnf mysqld | grep "^\-\-log-bin=" | head -n 1`
	fi
	if [ $? -eq 0 ]; then
		binlog_dir=`echo $bdir | cut -d"=" -f2`
		binlog_dir=`dirname $binlog_dir`
	fi
fi

Log "Flushing MySQL binary logs (FLUSH LOGS)"
mysql_command mysql "flush logs"

for b in `mysql_command mysql "show master logs" | cut -f1`
do
	if [ -z $first_log ]; then
	first_log=$b
fi
if [ $b != $master_binlog ]; then
	Log "Copying binary log ${b} to ${backup_dir}"
	cp $binlog_dir/$b $backup_dir >& /dev/null
	if [ $? -ne 0 ]; then
		copy_status=1
		break
	fi
	echo $b >> $backup_list
	last_log=$b
else
	break
fi
done

if [ $copy_status -eq 1 ]; then
	Log "[ERR] Failed to copy binary logs cleanly...aborting"
	exit 1
else
	# Tar them up
	cd $backup_dir
	tar_file=${backup_dir}/binlog.${first_log}.${last_log}.tar
	Log "Creating tar file $tar_file"
	tar cvf $tar_file -T $backup_list &> /dev/null
	if [ $? -eq 0 ]; then
		for b in `cat $backup_list`
		do
			/bin/rm -f $b
		done
		if [ $no_compress -eq 0 ]; then
			Log "Compressing tar file..."
			gzip $tar_file
		fi
		Log "Purging binary logs from MySQL system from $first_log to $master_binlog"
		mysql_command mysql "purge master logs to '$master_binlog'"
	else
		Log "[ERR] Failed to create tar archive"
		exit 1
	fi
	rm -f $backup_list

        # Abort if we don't want to transfer or delete.
        if [ $transfer_delete -gt 0 ]; then
          exit 0
        fi

	# Copy the tarball back to $stored_backup_host
	Log "Copying backup to $stored_backup_host:/$backup_host_dir"
	if [ $no_compress -eq 0 ]; then
		scp -q ${tar_file}.gz $stored_backup_host:/$backup_host_dir
		scp_status=$?
	else
		scp -q ${tar_file} $stored_backup_host:/$backup_host_dir
		scp_status=$?
	fi
	if [ $scp_status -eq 0 ]; then
		if [ $no_compress -eq 0 ]; then
			/bin/rm -f ${tar_file}.gz
		else
			/bin/rm -f $tar_file
		fi
		Log "[END] Backup complete"
	else
		Log "[ERR] Failed to copy backup file to $stored_backup_host"
	fi
fi