This article focuses on how to use the shell to achieve full MySQL, incremental backup. Incremental backups replicate mysql-bin.00000* to the specified directory in Monday-Saturday 3 o'clock in the morning, while full-scale backups use mysqldump to export all databases, every Sunday 3 o'clock in the morning, and delete the mysq-bin.00000* left last week. The backup operation for MySQL is then retained in the Bak.log file. Such as:
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 the dbdailybak.sh, once a day.
Realize:
1. Write a full-scale backup script
# vim/root/dbfullybak.sh//Add the following content
#!/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 day%h:%m:%s "'
echo Start: $Begin end: $Last $GZDumpFile Successful!!! >> $LogFile
CD $BakDir/daily
Rm-f *
2. Writing an incremental backup script
# cat/root/dbdailybak.sh//content as below
#!/bin/bash
# program
# Use the 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
#这个是用于产生新的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 '
Do
Base= ' basename $file '
#basename用于截取mysql-bin.00000* file name, 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 if the target file exists, and it writes exist! 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 the crontab task and execute the backup script daily
Copy the code code as follows:
# Crontab-l//content as below
#每个星期日凌晨3:00 Perform a full backup script
0 3 * * 0/root/dbfullybak.sh >/dev/null 2>&1
#周一到周六凌晨3:00 do an incremental backup
0 3 * * 1-6/root/dbdailybak.sh >/dev/null 2>&1
This article is from the "Linunx" blog, please be sure to keep this source http://lijianmin2008.blog.51cto.com/621678/1925078
MySQL mysqldum backup script