The shell realizes the MySQL mysqldump to realize the data full backup or the incremental backup

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

The code is as follows

Copy Code

# 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

The code is as follows

Copy Code

# 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

The code is as follows	Copy Code
# 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