Full and incremental backup MySQL scripts

Document Introduction
This document uses Mysqldump toDatabasefor backup, mysqldump is a SQL-level backup mechanism that guides the data tables into SQL script files, in different Mysqlit is relatively appropriate to upgrade between versions, which is also the most commonly used backup method, mysqldump is slower than direct copy.

This document describes automatic backups of MySQL databases, including full and incremental backups. Where full backups are performed once a week, incremental backups are performed daily. After the backup is successful, it is automatically uploaded to the FTP server. MySQL needs to turn on the binary log.

Backup Policy Placement
Put the script under the/usr/bin directory
(1), enable binary log
The Binlog approach is relatively flexible, hassle---saving, and can support incremental backups.
You must restart Mysqld when Binlog is enabled. First, close mysqld, open/etc/my.cnf, and add the following lines:
[Mysqld]
Log-bin

then start the mysqld on it. During the run, hostname-bin.000001 and Hostname-bin.index are generated, the previous file is mysqld record all updates to the data, and the subsequent file is the index of all binlog and cannot be easily deleted. For more detailed information on Binlog, please see the manual.

(2), set up Crontab task, execute backup script daily
Vi/etc/crontab
Add the following:
0 XX * * * root/usr/bin/backap_mysql.sh

The script is as follows:

#!/bin/bash
#set-X
#此脚本的主要用途是备份mysql服务器上的数据库. and automatically uploaded to the server via FTP. A message will be sent when the backup is complete.
ECHO-E "The primary purpose of this script is to back up the database on the MySQL server. and automatically uploaded to the server via FTP. "
Host=www.chlinux.net
Pass=chenqibin
Name=root
Date= ' date + '%y%m%d '
Wan_dir= "/wan_dir"
zeng_back= "/backup"
Data_dir= "/usr/local/mysql/data"
Mysql_bin= "/usr/local/mysql/bin"
error_log= "$WAN _dir/backup_error_$date.log"
backup_log= "$ZENG _dir/backup_$date.log"
Gzdumpfile= "$DATE. sql.tar.gz"
db= "/var/log/backup_$date.txt"

CD $DATA _dir

Ls-l $DATA _dir | grep "^d" | Awk-f "" ' {print $9} ' >> $db

function Wan () {
#检测完全备份目录是否存在, created if it does not exist.
If [-D $WAN _dir]
Then
echo "Full backup directory exists" >> $backup _log
Else
echo "Full backup directory does not exist, start creating ..."
/bin/mkdir $WAN _dir
Fi

emailfile= "$WAN _dir/mail.log"
[email protected]
echo "" > $eMailFile
echo "-----------------------" >> $eMailFile
echo "' Date +"%y-%m-%d%h:%m:%s "'" >> $eMailFile
echo "-------------------------" >> $eMailFile
CD $WAN _dir
For dbname in $ (cat $db)
Do
Mysqldump--flush-logs-u$name-p$pass--skip-lock-tables--quick $dbname > $dbname. sql
If [$? = 0]
Then
Find $ZENG _back-name "*.log"-mtime +32-exec rm {} \; >/dev/null 2>&1
CD $WAN _dir
TAR-ZCVF $dbname. $gzdumpfile $dbname. sql
echo "Backup MySQL succeed" >> $eMailFile
Mail-s "MySQL Backup" $email < $eMailFile
Else
echo "Backup MySQL fail" >> $eMailFile
Mail-s "MySQL Backup fail" $email < $eMailFile
Fi
Done
#完全备份后删除本地增量备份文件, keep only incremental backup files for the last one weeks
Find $ZENG _back-name "*.sql.tar.gz"--mtime +7-exec rm-rf {} \; >> $backup _log

#将备份好的上传到FTP服务器
CD $WAN _dir
For Db_back in $ (cat $db)
Do
FTP-NV $Host <<eof
User Wolf "Wolf#123"
Put $db _back. $gzdumpfile
Quit
Eof
Done
}
function Zeng () {
/bin/mkdir/zeng_dir

emailfile= "$ZENG _dir/mail.log"
[email protected]

echo "" > $eMailFile
echo "-----------------------" >> $eMailFile
echo "' Date +"%y-%m-%d%h:%m:%s "'" >> $eMailFile
echo "-------------------------" >> $eMailFile

time=$ (Date "-D ten day Ago" +%y-%m-%d%h:%m:%s)
starttime=$ (Date "-D 1 day ago" + "%y-%m-%d%h:%m:%s")
Start= "--start-datetime"
#删除10天前的二进制文件
Mysql-u$name-p$pass-e "purge master logs before ${time}" && echo "delete ten days before log" | Tee-a $eMailFile
Filename= ' Cat $DATA _dir/chlinux-bin.index | Awk-f "/" ' {print $} '
Cd/zeng_dir

For I in $filename
Do
echo "$StartTime start Backup Binlog" >> $eMailFile

For Db_name in $ (cat $db)
Do
mysqlbinlog-u$name-pchenqibin-d $db _name $Start = "$StartTime" $DATA _dir/$i >> $db _name. $DATE. sql
If [$? = 0]
Then
Cd/zeng_dir
TAR-ZCVF $db _name. $gzdumpfile $db _name. $DATE. sql
echo "Backup MySQL succeed" >> $eMailFile
Mail-s "MySQL Backup" $email < $eMailFile
Else
echo "Backup MySQL fail" >> $eMailFile
Mail-s "MySQL Backup fail" $email < $eMailFile
Fi
Done
Done
Find $ZENG _back-name "*.log"-name +32-exec rm {} \; >/dev/null 2>&1
Cd/zeng_dir
#删除上次备份的完整备份的文件
Find $WAN _dir-name "*.tar.gz"--mtime +7-exec rm-rf {} \;

#将备份好的上传到FTP服务器
For Db_back in $ (cat $db)
Do
FTP-NV $Host <<eof
User Wolf "Wolf#123"
Put $db _back. $gzdumpfile
Quit
Eof
Done
}
Backfile= ' Ls-l/wan_dir | Wc-l '
if [$backfile! = 0]
Then
echo "Full backup already exists, incremental backup Now"
Sleep 10
Zeng
Else
echo "No full backup yet, full backup Now"
Sleep 30
Wan
Fi

Main variable Description:
Host #FTP的IP
Pass #FTP的密码
Name #FTP和mysql的用户名
DATE #时间
Wan_dir #完整备份的目录
Zeng_back #增量备份的目录
Data_dir #mysql数据目录
Error_log #错误日志
Gzdumpfile #压缩后的后缀名
DB #mysql数据库名

This article is from the "Target: India" blog, please be sure to keep this source http://shunzi115.blog.51cto.com/5184443/1826163

Full and incremental backup MySQL scripts