This article is mainly about how to use the shell to achieve a full amount of MySQL, incremental backup. Incremental backups are replicated mysql-bin.00000* to the specified directory at Monday-Saturday 3 o'clock in the morning, while full backups use mysqldump to export all of the databases, hold every Sunday 3 o'clock in the morning, and delete the mysq-bin.00000* left last week. The backup operation on MySQL will then remain in the Bak.log file. The following figure:
Start: May 02, 2013 15:10:57 End: May 02, 2013 15:12:16 20130502.sql.tgz succ is produced by Dbfullybak.sh, backed up once a week; mysql-bin.000001 Copying;mysql-bin.000002 skip!; May 02, 2013 16:53:15 Bakup succ! is produced by dbdailybak.sh, once a day.
Realize:
1. Write a full-volume backup script
Copy Code code as follows:
# vim/root/dbfullybak.sh//Add the following
#!/bin/bash
# program
# Use mysqldump to fully backup MySQL data per week!
# History
# 2013-04-27 Guo
# Path
# ....
Bakdir=/home/mysql/backup
Logfile=/home/mysql/backup/bak.log
Date= ' Date +%y%m%d '
begin= ' date + '%y year%m month%d%h:%m:%s '
CD $BakDir
dumpfile= $Date. sql
gzdumpfile= $Date. sql.tgz
/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: End of $Begin: $Last $GZDumpFile succ >> $LogFile
CD $BakDir/daily
Rm-f *
2. Write an incremental backup script
Copy Code code as follows:
# cat/root/dbdailybak.sh//content for next
#!/bin/bash
# program
# use CP to backup MySQL data everyday!
# History
# 2013-05-02 Guo
# Path
# ....
Bakdir=/home/mysql/backup/daily
Bindir=/home/mysql/data
Logfile=/home/mysql/backup/bak.log
Binfile=/home/mysql/data/mysql-bin.index
/usr/local/mysql/bin/mysqladmin-uroot-p123456 Flush-logs
#这个是用于产生新的mysql-bin.00000* File
Counter= ' wc-l $BinFile |awk ' {print $} '
Nextnum=0
#这个for循环用于比对 $Counter, $NextNum These two values to determine whether the file is present or up to date.
For file in ' Cat $BinFile '
Todo
Base= ' basename $file '
#basename用于截取mysql-bin.00000* filename, remove the./mysql-bin.000005 front./
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 detect whether the target file exists, and the existence is written exist! to $logfile.
Then
Echo $base exist! >> $LogFile
Else
CP $BinDir/$base $BakDir
echo $base copying >> $LogFile
Fi
Fi
Done
Echo ' date + '%y%m month%d day%h:%m:%s ' $Next bakup succ! >> $LogFile
3. Set the crontab task to perform a backup script every day
Copy Code code as follows:
# Crontab-l//content for next
#每个星期日凌晨3:00 Perform a full backup script
0 3 * * 0/root/dbfullybak.sh >/dev/null 2>&1
#周一到周六凌晨3:00 make incremental backups
0 3 * * 1-6/root/dbdailybak.sh >/dev/null 2>&1
Appendix:
Sh-n/root/dbfullybak.sh can be used to detect if the shell syntax is correct