Mysql mysqldum backup script
This article mainly talks about how to use shell to achieve mysql full volume, incremental backup. Incremental backups copy mysql-bin.00000* to the specified directory at 3 a.m. Monday through Saturday, while full backups export all databases using mysqldump every Sunday at 3 a.m. and delete mysq-bin.00000* from the previous week. The backup operations for mysql are then retained in the bak.log file. As shown below:
Start: May 2, 2013 15:10:57 End: May 2, 2013 15:12:16 20130502.sql.tgz succ is generated by DBFullyBak.sh and backed up once a week;mysql-bin. 00001 copying;mysql-bin. 00002 skip!; 02 May 2013 16:53:15 Bakup succ! is generated by DBDailyBak.sh once a day.
Achieve:
1. Script Full Volume Backup
# vim /root/DBFullyBak.sh the following
#!/ bin/bash
# Program
# use mysqldump to Fully backup mysql data per week!
# History
# 2013-04-27 guo first
# Path
# ....
BakDir=/home/mysql/backup
LogFile=/home/mysql/backup/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y year %m month %d day %H:%M:%S"`
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
if [ ! -d "$BakDir" ]; then
mkdir -p $BakDir
fi
cd $BakDir
/usr/local/mysql/bin/mysqldump -uroot -p123456 --quick --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile
/bin/tar czvf $GZDumpFile $DumpFile
/bin/rm $DumpFile
Last=`date +"%Y year %m month %d %H:%M:%S"`
echo Start:$Begin End:$Last $GZDumpFile successful!!! >> $LogFile
cd $BakDir/daily
rm -f *
2. Script incremental backups
# cat /root/www.example.com/DBDailyBak.sh
#!/ bin/bash
# Program
# use cp to backup mysql data everyday!
# History
# 2013-05-02 guo first
# Path
# ....
BakDir=/home/mysql/backup/daily
BinDir=/data/mysql
LogFile=/home/mysql/backup/bak.log
BinFile=/data/mysql/mysql-bin.index
if [ ! -d "$BakDir" ]; then
mkdir -p $BakDir
fi
/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs
#This is used to generate new mysql-bin.00000* files
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#This for loop is used to compare $Counter,$NextNum to determine if the file exists or is current.
for file in `cat $BinFile`
do
base=`basename $file`
#basename is used to truncate mysql-bin.00000* file name, remove./ mysql-bin.000005./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BakDir/$base
if(test -e $dest)
#test -e is used to check whether the target file exists. If it exists, write exist! Go to $LogFile.
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
fi
fi
done
echo `date +"%Y year %m month %d day %H:%M:%S"` $Next Bakup successful! >> $LogFile
3. Set up crontab task to execute backup script daily
The copy code is as follows:
# crontab -l //content is below
#Perform a full backup script every Sunday at 3:00 AM
0 3 * * 0 /root/DBFullyBak.sh >/dev/null 2>&1
#Do incremental backups Monday through Saturday at 3:00 AM
0 3 * * 1-6 /root/DBDailyBak.sh >/dev/null 2>&1
