backup mysql binary logs

| Posted in MySQL

Saya paling suka membackup hehe…mau delete log files lama juga sayang kan, mana tau suatu saat di minta polisi mencari jejak kan tinggal kasih.  Untuk keperluan membackup mysql binary logs diperlukan bash script dan saya modifikasi dari http://forge.mysql.com/tools/tool.php?id=73

backup_user=binlog
backup_password=password
backup_port=3306
backup_host=localhost
log_file=/var/log/binlog_backup.log
binlog_dir=/san/mysql-us/mysqllogs
backup_dir=/home/gtoms/mysql_binlog_backup/

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

Log()
{

echo “`date` : $*” >> $log_file
}

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”
}

Log “[INIT] Starting MySQL binlog backup”

Log “Flushing MySQL binary logs (FLUSH LOGS)”

mysql_command mysql “flush logs”

master_binlog=`mysql_command mysql “show master status” 2>/dev/null | cut -f1`

Log “Current binary log is: $master_binlog”

copy_status=0

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}”
rsync -a $binlog_dir/$b $backup_dir >& /dev/null
if [ $? -ne 0 ]; then
copy_status=1
break
fi
else
break
fi
done

if [ $copy_status -eq 1 ]; then
Log “[ERR] Failed to copy binary logs cleanly…aborting”
exit 1
fi

Write a comment