#!/bin/sh
# 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
Great script Partha.. Thank you