Scrip Dos_Windows Help

Can this scrip be revised to be used for backing up mysql binary logs automatically without having to copy anything manually? There is only one master server no slave.

Also can you remove unwanted commands from the below script? I will use the root user to run this command instead of creating another user to run this command

My location for
log_file=E:\mysql1\binlogs.000001
binlog_dir=E:\mysql1\binlogs
backup_dir=E:\msql\backup

#
# This script backup binary log files
#

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/dagnus/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
klajdiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ReneGeCommented:
I may look like an idiot, but what language is this?
0
klajdiAuthor Commented:
I have no idea, I just found on the web
0
johanntagleCommented:
That's a unix shell script
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ReneGeCommented:
Thanks johanntagle

klajdi, would you rather need a batch file?
0
johanntagleCommented:
He needs the equivalent of the script that can be run on Windows.  I'd be curious too as I also need to do some Windows scripting though much simpler.
0
ReneGeCommented:
Well I can make a batch file to backup and restore MySQL database, but that unix script, is out of my league.
0
johanntagleCommented:
Let me see if I can interpret it for you:

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/dagnus/mysql_binlog_backup/

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

Open in new window


The above just sets some variables and PATH so that the rest of the commands can find the mysql binaries

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

Open in new window


Just create a logfile with the current date

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

Open in new window

These are helper codes so that you don't need to retype command options.  You can ignore them and I can propose some alternative commands below

Log "[INIT] Starting MySQL binlog backup"

Log "Flushing MySQL binary logs (FLUSH LOGS)"

mysql_command mysql "flush logs"

Open in new window

Put some log entries, then execute the following in the command prompt:
mysql --user=<username> --password=<password> --host=<db_host> --port=<db_port> --batch --skip-column-names mysql -e "flush logs"

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

Log "Current binary log is: $master_binlog"

Open in new window

Execute the following in the command prompt, put the result to $master_binlog variable, and log it:
mysql --user=<username> --password=<password> --host=<db_host> --port=<db_port> --batch --skip-column-names mysql -e "show master status"

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

Open in new window

Execute the following:
mysql --user=<username> --password=<password> --host=<db_host> --port=<db_port> --batch --skip-column-names mysql -e "show master logs"
This will list the binary log files.  The script then loops through the result to copy each file with those names located in the binary log directory, except for the latest (equals the content of $master_binlog) to the designated backup directory.  If an error occurs with any file, the script exits.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ReneGeCommented:
@johanntagle:
-Thanks for dysecting it.
-Before we go further on this, we need to know if the poster needs what script do do what it does.
-I am saying this because he does not know what it does and picked it up somewhere on the web.
-Maybe he only needs a script to backup his MySQL database.

0
klajdiAuthor Commented:
ReneGe and John

Thanks to both of you for your help.

Im not really able to understand the above scripts very well, but what i Need is just a script to backup mysql binary logs and automatically move those backed up files to another location.

So I'm not sure which of the above scripts does this job.
0
klajdiAuthor Commented:
I've requested that this question be deleted for the following reason:

none
0
ReneGeCommented:
I agree with SouthMod
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.