Shulou(Shulou.com)06/01 Report--

The following together to understand the use of scripts should be how to backup Mysql database and binlog, I believe we will certainly benefit from reading, text in fine not much, hope to use scripts should be how to backup Mysql database and binlog This short content is what you want.

Let's cut to the chase and go straight to the script.

The first is to make a full database backup of the specified library files for mysql

vim mysqlbak.sh

#!/ bin/bash

#Define database directory to find mysqldump

mysqlDir=/usr

#Define user name and password for backing up database

user=root

userpwd=123456

dbname=db1

#Define backup directories

databackupdir=/mysqlbak

#Define message body file

emailfile=$databackupdir/email.txt

#Define email address

email=www@163.com

#Define backup log files

logfile=$databackupdir/mysqlbackup.log

#DATE=`date +%Y%m%d`

DATE=`date -I`

echo "" > $emailfile

echo $( date +"%Y-%m-%d %H:%M:%S" ) >> $emailfile

cd $databackupdir

#Define backup file names

dumpfile=mysql_$DATE.sql

gzdumpfile=mysql_$DATE.sql.tar.gz

#Use mysqldump to backup database,--flush-logs to generate a new log

$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile

#Compress backup files

if [ "$? " == 0 ];then

#Compress the backed up file and save the compressed result in the email content

tar zcvf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName: $gzdumpfile" >> $emailfile

echo "DataBase Backup Success! " >> $emailfile

#Delete SQL files before compression

rm -rf $dumpfile

else

echo "Database Backup Fail! " >> $emailfile

fi

#Write log files

echo "-------------------------------------------------" >> $logfile

cat $emailfile >> $logfile

#Send email notifications

cat $emailfile| mail -s "Mysql Backup" $email

After the above script is executed, a tar.gz file will be generated in the backup directory. This file is the full database backup file. After the backup is successful, an email will be sent to the specified email address. The received content is screenshot content. Backup is successful!

Next is the backup script for binlog logs

vim binlogbak.sh

#!/ bin/bash

#Define database directories and data directories

mysqldir=/usr/local/mysql

datadir=$mysqldir/binlog

#Define user name and password for backing up database

user=root

userpwd=123456

#Define backup directories

databackupdir=/mysqlbak

logbackupdir=$databackupdir/logs

#Define message body file

emailfile=$databackupdir/email.txt

#Define email address

email=www@163.com

#Define backup log files

logfile=$databackupdir/mysqlbackup.log

#DATE=`date +%Y%m%d`

echo "" > $emailfile

echo $( date +"%Y-%m-%d %H:%M:%S" ) >> $emailfile

#Flushes logs to make the database use new binary log files

/usr/bin/mysqladmin -u$user -p$userpwd flush-logs

cd $datadir

#Get binary log list

filelist=`cat mysql_binlog.index`

icounter=0

for file in $filelist

do

#Note that the symbol and the space between the two operation items are indispensable, and the same is true below.

icounter=`expr $icounter + 1`

done

nextnum=0

ifile=0

for file in $filelist

do

binlogname=$file

nextnum=`expr $nextnum + 1`

#Skip last binary log (binary log file currently used by database)

if [ $nextnum -eq $icounter ];then

echo "Skip lastest! " > /dev/null

else

dest=$logbackupdir/$binlogname

#Skip binary log files that have already been backed up

if [ -e $dest ];then

echo "Skip exist $binlogname! " > /dev/null

else

#Backup log files to backup directories

cp $binlogname $logbackupdir

if [ "$? " == 0 ];then

ifile=`expr $ifile + 1`

echo "$binlogname Backup Success! " >> $emailfile

fi

fi

fi

done

if [ $ifile -eq 0 ];then

echo "No Binlog Backup! " >> $emailfile

else

echo "Backup $ifile File(s). " >> $emailfile

echo "Backup MySQL Binlog OK! " >> $emailfile

fi

#Write log files

echo "-------------------------------------------------" >> $logfile

cat $emailfile >> $logfile

#Send email notifications

cat $emailfile| mail -s "Mysql Backup" $email

After the above script is executed, it will generate multiple binlogs that have not been backed up in the backup directory. The files are incremental backup files of the previous day. After the backup is successful, an email will be sent to the specified email address. The received content is screenshot. The content is backup success!

Prepare scheduled tasks for automatic execution, backup the whole database at 1, 3, and 24 o'clock every week, and backup the log at 1 o'clock every night.

crontab -e

0 00 * * 1,3 /mysqlbak.sh

0 1 * * * /binlogbak.sh

After reading this article on how to backup Mysql database and binlog logs using scripts, many readers will definitely want to know more about related content. For more industry information, you can pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.