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