[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Scrip Dos_Windows Help

Posted on 2011-10-19
15
Medium Priority
?
373 Views
Last Modified: 2012-06-15
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
0
Comment
Question by:klajdi
  • 6
  • 3
  • 3
12 Comments
 
LVL 10

Expert Comment

by:ReneGe
ID: 36997177
I may look like an idiot, but what language is this?
0
 

Author Comment

by:klajdi
ID: 36997253
I have no idea, I just found on the web
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36997755
That's a unix shell script
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 10

Expert Comment

by:ReneGe
ID: 36997772
Thanks johanntagle

klajdi, would you rather need a batch file?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36997775
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
 
LVL 10

Expert Comment

by:ReneGe
ID: 36997779
Well I can make a batch file to backup and restore MySQL database, but that unix script, is out of my league.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 1000 total points
ID: 36997842
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
 
LVL 10

Expert Comment

by:ReneGe
ID: 36999271
@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
 

Author Comment

by:klajdi
ID: 36999926
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
 
LVL 10

Assisted Solution

by:ReneGe
ReneGe earned 1000 total points
ID: 36999981
0
 

Author Comment

by:klajdi
ID: 37267145
I've requested that this question be deleted for the following reason:

none
0
 
LVL 10

Expert Comment

by:ReneGe
ID: 37267146
I agree with SouthMod
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses
Course of the Month20 days, 13 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question