MySQL mysqldum backup script

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