MySQL Full library backup/incremental backup script

http://hongge.blog.51cto.com/

Backups of MySQL databases in a production environment are recurring operations, so it is common to write script implementations that periodically execute backup scripts through Crond scheduled tasks

mysqldump Backup scenarios:

Sunday 1 o'clock in the morning full-Library backup

From Monday to Saturday, incremental backups every 4 hours

Set up crontab tasks, execute backup scripts daily

# crontab–e

#每个星期日凌晨1:00 Perform a full backup script

0 1 * * 0/root/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六每隔4个小时增量备份一次

0 */4 * * 1-6/root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh Script content:

[email protected] ~]# cat mysqlfullbackup.sh

#!/bin/sh

# Name:mysqlFullBackup.sh

# define Database Directory

Mysqldir=/usr/local/mysql

# define the user name and password used to back up the database

User=root

userpwd=123456

dbname=test_db

# define Backup Directory

Databackupdir=/opt/mysqlbackup

[!-D $databackupdir] && mkdir $databackupdir

# define message body file

emailfile= $databackupdir/email.txt

# define Email Address

[Email protected]

# define BACKUP log files

logfile= $databackupdir/mysqlbackup.log

Date= ' Date-i '

echo "" > $emailfile

echo $ (date + "%y-%m-%d%h:%m:%s") >> $emailfile

CD $databackupdir

# define backup file name

Dumpfile=mysql_$date.sql

Gzdumpfile=mysql_$date.sql.tar.gz

# using Mysqldump to back up the database, set the parameters according to the specific situation

$mysqlDir/bin/mysqldump-u$user-p$userpwd--flush-logs-x $dbname > $dumpfile

# Compress backup Files

If [$?-eq 0]; Then

Tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName: $gzdumpfile" >> $emailfile

echo "DataBase Backup success!" >> $emailfile

Rm-f $dumpfile

Else

echo "DataBase Backup fail!" >> $emailfile

Fi

# Write Log file

echo "--------------------------------------------------------" >> $logfile

Cat $emailfile >> $logfile

# Send an email notification

Cat $emailfile | Mail-s "MySQL Backup" $email

mysqldailybackup.sh Script content:

[email protected] ~]# cat mysqldailybackup.sh

#!/bin/sh

# Name:mysqlDailyBackup.sh

# define database directory and data directory

Mysqldir=/usr/local/mysql

datadir= $mysqldir/data

# define the user name and password used to back up the database

User=root

userpwd=123456

# define backup directory, daily backup file backup to $databackupdir/daily

Databackupdir=/opt/mysqlbackup

dailybackupdir= $databackupdir/daily

[!-D $dailybackupdir] && mkdir-p $databackupdir/daily

# define message body file

emailfile= $databackupdir/email.txt

# define Email Address

[Email protected]

# define log files

logfile= $databackupdir/mysqlbackup.log

echo "" > $emailfile

echo $ (date + "%y-%m-%d%h:%m:%s") >> $emailfile

#

# Refresh the log to make the database use the new binary log file

$mysqldir/bin/mysqladmin-u$user-p$userpwd Flush-logs

CD $datadir

# Get a binary log list

Filelist= ' Cat Mysql-bin.index '

Icounter=0

For file in $filelist

Do

Icounter= ' expr $icounter + 1 '

Done

Nextnum=0

Ifile=0

For file in $filelist

Do

Binlogname= ' basename $file '

nextnum= ' expr $nextnum + 1 '

# Skip the last binary log (the binary log file currently used by the database)

If [$nextnum-eq $icounter]; Then

echo "Skip lastest!" >/dev/null

Else

dest= $dailybackupdir/$binlogname

# Skip binary log files that have been backed up

If [-e $dest]; Then

echo "Skip exist $binlogname!" >/dev/null

Else

# Back up log files to backup directory

CP $binlogname $dailybackupdir

If [$?-eq 0]; Then

ifile= ' expr $ifile + 1 '

echo "$binlogname backup success!" >> $emailfile

Fi

Fi

Fi

Done

If [$ifile-eq 0];then

echo "No Binlog backup!" >> $emailfile

Else

echo "Backup $ifile File (s)." >> $emailfile

echo "Backup MySQL Binlog ok!" >> $emailfile

Fi

# Send an email notification

Cat $emailfile | Mail-s "MySQL Backup" $email

# Write Log file

echo "--------------------------------------------------------" >> $logfile

Cat $emailfile >> $logfile

http://hongge.blog.51cto.com/

MySQL Full library backup/incremental backup script