假想场景：数据库Mysql5.1.X，已做“主-从”，在其中一台从库上备份，主库不执行备份，从库不停止，主从数据不一样的时间差尽量短，在备份中有slave信息（主库的二进制日志名和位置），以便于在不停主库的情况下，新建主-从，或者随时可恢复到某个时间点。MyISAM和InnoDB都存在，备份要保证数据一致性。
基于以上场景，采用了innobackupex+bash shell，实现自动完整+增量备份，实现自动邮件通知和FTP上传，以及本地和异地的备份滚动。
脚本中也有其它被注释了的备份方式，以供参考。
下载包，这里我下载了二制包
wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.0.4-484.tar.gz
把命令拷贝到/usr/bin下即可
脚本的大致思路：以天为单位，首次运行是完整备份，下次将增量备份，恢复到最近的数据需要恢复所有增量备份。如果完整备份失败，那么要删除当天的备份目录，再开始。因为脚本是判断lsn.txt文件是否存在来决定本次该完整备份还是增量备份。
在介绍脚本之前，首先要说说Innodb存储引擎的热备
Innodb 存储引擎由于是事务性存储引擎，有redo 日志和相关的undo 信息，而且对数据的一致性和完整性的要求也比MyISAM 要严格很多，所以Innodb 的在线（热）物理备份要比MyISAM 复杂很多，比如你要考虑到备份的数据一致性，对备份MYSQL的影响，缓存的污染，CPU、磁盘I/O和网络的压力，恢复的难度，时间和恢复的方式等等，一般很难简单的通过几个手工命令来完成，大都是通过专门的Innodb在线物理备份软件来完成。
现在有比较好的软件：
一个是开源的:
http://www.percona.com/software/percona-xtrabackup
innobackupex和xtrabackup
innobackupex是一个perl脚本集合，可以备份所有存储引擎，并可以保证其数据一致性，它会调用xtrabackup
xtrabackup是一个C语言编写的程序，只能备份innodb和xtrdb存储引擎的表
一个是被MySQL官方合并到付费企业版的
http://www.innodb.com/products/hot-backup/
官方文档：http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/index.html
mysqlbackup和ibbackup
mysqlbackup是一个C语言编写的程序，可以备份所有存储引擎，并可以保证其数据一致性，是一个会调用ibbackup的命令
ibbackup只能备份innodb和存储引擎的表
另外innobackup可能是之前www.innodb.com做的一个perl命令集合。
至少在mysqlbackup3.5软件包中，innobackup和ibbackup命令被软链接到mysqlbackup命令，innobackup和ibbackup命令在未来很可能被替代。
innobackupex安装配置和使用
到官网下载相应版本的包，二进制包、RPM包、源码包均可。经测试，发现通过源码包安装非常麻烦，因为安装过程容易出很多问题。建议直接下载二进制包使用，设置一个PATH或者拷贝到/usr/bin下即可使用了
权限配置说明：
RELOAD and LOCK TABLES (unless the --no-lock option is specified) in order to FLUSH TABLES WITH
READ LOCK prior to start copying the files and
REPLICATION CLIENT in order to obtain the binary log position,
CREATE TABLESPACE in order to import tables (see Importing and Exporting Individual Tables) and
SUPER in order to start/stop the slave threads in a replication environment.
mysql>
CREATE USER 'bakuser'@'localhost' IDENTIFIED BY 'pass';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bakuser'@'localhost';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,SUPER ON *.* TO 'bakuser'@'pass';
FLUSH PRIVILEGES;
或：
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,SUPER ON *.* TO 'bakuser'@'localhost' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;注意，通过socket连接mysql实际上对root@localhost授权就够了。
示例：
下面是单独运行命令成功过的示例：
innobackupex --socket=/opt/mysql/var/mysql.sock --user=bakuser --password=pass --defaults-file=/opt/mysql/my.cnf --throttle=2 --slave-info --safe-slave-backup --use-memory=200MB /opt/data_bak/mysql_bak
在某次完整备份后第一次增量备份：
innobackupex --socket=/opt/mysql/var/mysql.sock --user=bakuser --password=pass --defaults-file=/opt/mysql/my.cnf --throttle=2 --slave-info --safe-slave-backup --use-memory=200MB --incremental --incremental-basedir=BASEDIR /opt/data_bak/mysql_bak
在最近完整备份后第二、三次增量备份：
或基于目录
innobackupex --socket=/opt/mysql/var/mysql.sock --user=bakuser --password=pass --defaults-file=/opt/mysql/my.cnf --throttle=2 --slave-info --safe-slave-backup --use-memory=200MB --incremental --incremental-basedir=BASEDIR /opt/data_bak/mysql_bak
innobackupex --socket=/opt/mysql/var/mysql.sock --user=bakuser --password=pass --defaults-file=/opt/mysql/my.cnf --throttle=2 --slave-info --safe-slave-backup --use-memory=200MB --incremental --incremental-basedir=incrementaldir1 /opt/data_bak/mysql_bak
或基于日志LSN
innobackupex --socket=/opt/mysql/var/mysql.sock --user=bakuser --password=pass --defaults-file=/opt/mysql/my.cnf --throttle=2 --slave-info --safe-slave-backup --use-memory=200MB --incremental --incremental-lsn=0:173917517 /opt/data_bak/mysql_bak
innobackupex --socket=/opt/mysql/var/mysql.sock --user=bakuser --password=pass --defaults-file=/opt/mysql/my.cnf --throttle=10 --slave-info --safe-slave-backup --use-memory=200MB --incremental --incremental-lsn=0:173918957 /opt/data_bak/mysql_bak恢复：
先合并增量到完整备份
innobackupex --apply-log --redo-only BASE-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2 这是最后一个，也就是最新的增量
innobackupex --apply-log BASE-DIR解后恢复到数据目录
脚本内容：
把脚本修改为适合自已的实际场景的，几乎只需要修改变量设定部分，变量设置不正确，脚本运行会报错，通过脚本的日志和innobackupex.log可以查看原因，比如MYSQL的配置文件路径（切记my.cnf中需要有datadir和innodb等相关设置），权限，本地备份目录，FTP目录等一定要再三检查。
#!/bin/bash
#History
######################################################
#update author
#2012/10/15 zhaoyn create
#2012/10/22 zhaoyn add ftp
#2012/11/28 zhaoyn add mysqlhotcopy
#2012/12/05 zhaoyn Improve function
#2012/12/12 zhaoyn Backup of stored procedures and events
#2012/12/24 zhaoyn Increase the log output
#2013/01/09-11 zhaoyn Various physical storage engine hot backup, InnoDB incremental backup command
#Note: use hot backup script, may also need some other pre-configured.
# wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.0.4-484.tar.gz
# http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/index.html
# 30 */2 * * * root /root/sh/backup_mysql.sh >> backup_mysql.log 2>&1
######################################################
########## variable #######################################
### base config ###
backupdir=/data_bak/mysql_bak/innobackupex
#mysqlhost="127.0.0.1"
#mysqlport="3306"
mysqlsocket="/opt/mysql/var/mysql.sock"
mysqldbname="dbname"
mysqluser="root"
mysqlpw="pass"
### hotcopy ###
hc_mysqluser="bakuser"
hc_mysqlpw="pass"
mysqlconfig="/opt/mysql/my.cnf"
iops=8000 # This option specifies a number of I/O operations (pairs of read+write) per second.
incr="yes" # yes or no, to turn on or off the daily incremental backups
### ftp ###
ftpip='1.2.3.4'
ftpport='21'
ftpuser='backup'
ftppw='pass'
ftpbackupdir="innobackupex"
ftpsw="yes" # To turn on or off the FTP upload
ftpdeldate=$(date -d "15 days ago" +%Y%m%d)
### mail ###
servername="dbserver"
mailfromadd='dbserver<dbserver@domain.com>'
mailtoadd='user1<user1@domain.com>'
#mailccadd='user2<user2@domain.com>'
### lang path time ###
export LANG=C
export LC_ALL=C
export PATH=/opt/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
datetime=$(date +%Y%m%d-%H%M)
todaydate=$(date +%Y%m%d)
deldate=$(date -d "7 days ago" +%Y%m%d)
####### function #######################################
function mailto() {
# mail
/usr/sbin/sendmail -t <<EOF
From: $mailfromadd
To: $mailtoadd
Cc: $mailccadd
Subject: $servername Backup directory $backupdir does not exist.
----------------------------------
$servername Backup directory $backupdir does not exist
----------------------------------
EOF
}
function mailto2() {
# mail
/usr/sbin/sendmail -t <<EOF
From: $mailfromadd
To: $mailtoadd
Cc: $mailccadd
Subject: $servername complete the mysql-backup
----------------------------------
$servername complete the mysql-backup, innobackupex $teststring
----------------------------------
EOF
}
function mailto3() {
# mail
/usr/sbin/sendmail -t <<EOF
From: $mailfromadd
To: $mailtoadd
Cc: $mailccadd
Subject: $servername backup problems.
----------------------------------
$servername backup problems.
----------------------------------
EOF
}
echo ""
echo ""
echo "`date` Start."
echo "##################################"
####### delete old backups #######################################
echo "`date` Delete old backup."
if [ -d "$backupdir" ] && [ "$deldate" -gt 20120901 ]; then
cd $backupdir
# find "$backupdir" -maxdepth 1 -name "*mysqldump*.gz" -mtime +"$deldays" -exec rm -f {} \;
# find "$backupdir" -maxdepth 1 -name "*mysqlhotcopy*" -mtime +"$deldays" -exec rm -f {} \;
find "$backupdir" -maxdepth 1 -type d -name "$deldate" | xargs rm -rf
else
echo "Backup directory does not exist, or did not get to date."
mailto
exit 1
fi
echo "`date` Start Backup."
####### mysqldump ####################################
#mysqldump -S"$mysqlsocket" -u$mysqluser -p$mysqlpw --opt --routines --triggers --master-data=2 \
#--default-character-set=utf8 --lock-all-tables --quote-names --flush-logs --hex-blob --force \
#"$mysqldbname" > "$mysqldbname"_mysqldump_${datetime}.sql
#echo "`date` Begin to compress."
#tar czpf "$mysqldbname"_mysqldump_${datetime}.tar.gz "$mysqldbname"_mysqldump_${datetime}.sql
#sync;sleep 2;sync;sleep 2
#rm -f "$mysqldbname"_mysqldump_${datetime}.sql
####### physical cold backup ####################################
#/etc/rc.d/init.d/mysqld stop
#sleep 2s ;sync;sync
#echo "`date` Begin to compress."
#tar -czvpf mysql_${datetime}.tar.gz /etc/my.cnf /opt/mysql/var
#/etc/rc.d/init.d/mysqld start
####### physical hot backup ####################################
### 1. unix/linux, only myisam, mysqlhotcopy ###
#mysqlhotcopy -S "$mysqlsocket" -u "$mysqluser" -p "$mysqlpw" "$mysqldbname" --flushlog ./
#sync;sleep 2;sync;sleep 2
#mv "$mysqldbname" "$mysqldbname"_mysqlhotcopy_${datetime}
#mysqldump -S"$mysqlsocket" -u$mysqluser -p$mysqlpw --opt --routines --default-character-set=utf8 \
#--quote-names --force --no-data "$mysqldbname" > "$mysqldbname"_mysqldump_structure-func_${datetime}.sql
#echo "`date` Begin to compress."
#tar czpf "$mysqldbname"_mysqlhotcopy_${datetime}.tar.gz \
#"$mysqldbname"_mysqlhotcopy_${datetime} "$mysqldbname"_mysqldump_structure-func_${datetime}.sql
#sync;sleep 2;sync;sleep 2
#rm -rf "$mysqldbname"_mysqlhotcopy_${datetime} "$mysqldbname"_mysqldump_structure-func_${datetime}.sql
### 2. unix/linux, any storage engine, innobackupex, all databases ###
test -e "$todaydate" || mkdir "$todaydate"
cd "$todaydate"
i=0
if [ ! -f lsn.txt ]; then
echo "`date` Start full backup."
# fullbackup
innobackupex --socket="$mysqlsocket" --user="$mysqluser" --password="$mysqlpw" --defaults-file="$mysqlconfig" \
--throttle="$iops" --slave-info --safe-slave-backup --use-memory=200MB --no-timestamp "$datetime"_fullbackup >> \
innobackupex.log 2>&1
grep "last_lsn" "$datetime"_fullbackup/xtrabackup_checkpoints | awk -F[" "] '{print $3}' >> lsn.txt
mysqldump -S"$mysqlsocket" -u$mysqluser -p$mysqlpw --opt --routines --default-character-set=utf8 --quote-names \
--force --no-data "$mysqldbname" > "$datetime"_fullbackup/"$mysqldbname"_mysqldump_structure-func_${datetime}.sql
sync;sleep 2;sync;sleep 2
tar czpf "$datetime"_"$mysqldbname"_fullbackup.tar.gz "$datetime"_fullbackup
rm -rf "$datetime"_fullbackup
i=1
fi
last_lsn=`tail -1 lsn.txt`
teststring=`tail -1 innobackupex.log | awk -F': ' '{print $(NF-0)}'`
if [ "$last_lsn" == "" ] && [ "$i" -eq 0 ] && [ "$incr" == "yes" ]; then
echo "`date` Did not get to last_lsn, cancel incremental backup, exit."
mailto3
exit 1
elif [ "$teststring" != 'completed OK!' ] && [ "$i" -eq 0 ] && [ "$incr" == "yes" ]; then
echo "`date` Last backup was not successful, cancel incremental backup, exit."
mailto3
exit 1
elif [ "$teststring" == 'completed OK!' ] && [ "$i" -eq 0 ] && [ "$incr" == "yes" ]; then
# incremental backup
innobackupex --socket="$mysqlsocket" --user="$mysqluser" --password="$mysqlpw" --defaults-file="$mysqlconfig" \
--throttle="$iops" --slave-info --safe-slave-backup --use-memory=200MB --no-timestamp \
--incremental --incremental-lsn="$last_lsn" "$datetime"_incremental >> innobackupex.log 2>&1
grep "last_lsn" "$datetime"_incremental/xtrabackup_checkpoints | awk -F[" "] '{print $3}' >> lsn.txt
mysqldump -S"$mysqlsocket" -u$mysqluser -p$mysqlpw --opt --routines --default-character-set=utf8 --quote-names \
--force --no-data "$mysqldbname" > "$datetime"_incremental/"$mysqldbname"_mysqldump_structure-func_${datetime}.sql
sync;sleep 2;sync;sleep 2
tar czpf "$datetime"_"$mysqldbname"_incremental.tar.gz "$datetime"_incremental
rm -rf "$datetime"_incremental
fi
teststring=`tail -1 innobackupex.log | awk -F': ' '{print $(NF-0)}'`
if [ "$teststring" != 'completed OK!' ]; then
echo "`date` The backup failed, exit."
mailto3
exit 1
elif [ "$teststring" == 'completed OK!' ] && [ "$i" -eq 1 ]; then
echo "`date` The full backup was successful."
mailto2
fi
### 3. cross-platform, any storage engine, mysqlbackup, all databases ###
#mysqlbackup --socket="$mysqlsocket" --user=$mysqluser --password="$mysqlpw" --default-character-set=utf8 \
#--compress-level=1 --sleep=30 --with-timestamp backup --backup-dir=./
####### FTP backup ####################################
if [ "$ftpsw" == 'yes' ] && [ "$ftpdeldate" -gt 20120901 ]; then
echo "`date` Start uploading to offsite."
ftp -v -n -i <<END
open $ftpip $ftpport
user $ftpuser $ftppw
bin
cd $ftpbackupdir/$ftpdeldate
mdelete *.gz
cd /$ftpbackupdir
rmdir $ftpdeldate
mkdir $todaydate
cd $todaydate
mput ${datetime}*.gz
bye
END
else
echo "`date` Not open FTP, or did not get to date. done."
fi
echo "`date` All completed."
建议：脚本中只启用了innobackupex备份，实际上，MYSQL备份最好逻辑备份和物理备份都同时进行，并且还定期做一份数据结构的备份，使各种备份的优势互补。
附mysqldump的备份脚本：
#!/bin/bash
#History
######################################################
#update author
#2012/10/15 zhaoyn create
#2012/10/22 zhaoyn add ftp
#2012/11/28 zhaoyn add mysqlhotcopy
#2012/12/05 zhaoyn Improve function
#2012/12/12 zhaoyn Backup of stored procedures and events
#2012/12/24 zhaoyn Increase the log output
# 30 4 * * * root /root/sh/backup_mysqldump.sh >> backup_mysqldump.log 2>&1
######################################################
########## variable #######################################
### base config ###
backupdir=/opt/data_bak/mysql_bak/mysqldump
#mysqlhost="127.0.0.1"
#mysqlport="3307"
mysqlsocket="/opt/mysql/var/mysql.sock"
mysqlbinpath="/opt/mysql/bin"
mysqldbname="dbname"
mysqluser="root"
mysqlpw="pass"
### ftp ###
ftpip='1.2.3.4'
ftpport='10021'
ftpuser='backup'
ftppw='pass'
ftpbackupdir="backup_db/mysqldump"
ftpsw="yes" # To turn on or off the FTP upload
### mail ###
servername="dbname"
mailfromadd='dbname<dbname@backup.domain.com>'
mailtoadd='user1<user1@domain.com>'
#mailccadd='user2<user2@domain.com>'
### lang path time ###
export LANG=C
export LC_ALL=C
export PATH="$mysqlbinpath":/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
datetime=$(date +%Y%m%d-%H%M)
todaydate=$(date +%Y%m%d)
ftpdeldate=$(date -d "35 days ago" +%Y%m%d)
deldate=$(date -d "15 days ago" +%Y%m%d)
deldays=15
####### function #######################################
function mailto() {
# mail
/usr/sbin/sendmail -t <<EOF
From: $mailfromadd
To: $mailtoadd
Cc: $mailccadd
Subject: $servername mysqldump directory $backupdir does not exist.
----------------------------------
$servername mysqldump directory $backupdir does not exist.
----------------------------------
EOF
}
function mailto2() {
# mail
/usr/sbin/sendmail -t <<EOF
From: $mailfromadd
To: $mailtoadd
Cc: $mailccadd
Subject: $servername complete the mysqldump
----------------------------------
$servername complete the mysqldump.
----------------------------------
EOF
}
function mailto3() {
# mail
/usr/sbin/sendmail -t <<EOF
From: $mailfromadd
To: $mailtoadd
Cc: $mailccadd
Subject: $servername mysqldump problems.
----------------------------------
$servername mysqldump problems.
----------------------------------
EOF
}
echo ""
echo ""
echo "`date` Start."
echo "##################################"
####### delete old backups #######################################
echo "`date` Delete old backup."
if [ -d "$backupdir" ] && [ "$deldate" -gt 20120901 ]; then
cd $backupdir
find "$backupdir" -maxdepth 1 -name "*mysqldump*.gz" -mtime +"$deldays" -exec rm -f {} \;
# find "$backupdir" -maxdepth 1 -type d -name "$deldate" | xargs rm -rf
# find "$backupdir" -maxdepth 1 -type f -name *"$deldate"*.gz | xargs rm -f
else
echo "Backup directory does not exist, or did not get to date."
mailto
exit 1
fi
echo "`date` Start Backup."
####### mysqldump ####################################
mysqldump -S"$mysqlsocket" -u$mysqluser -p$mysqlpw --opt --routines --triggers --master-data=2 \
--default-character-set=utf8 --lock-all-tables --quote-names --flush-logs --hex-blob --force \
"$mysqldbname" | gzip -7 > "$mysqldbname"_mysqldump_${datetime}.sql.gz
sync;sleep 2;sync;sleep 2
mysqldump -S"$mysqlsocket" -u$mysqluser -p$mysqlpw --opt --routines --triggers \
--default-character-set=utf8 --quote-names --force --no-data \
"$mysqldbname" | gzip > "$mysqldbname"_mysqldump_structure_${datetime}.sql.gz
sync;sleep 2;sync;sleep 2
if [ -s "$mysqldbname"_mysqldump_${datetime}.sql.gz ] && [ -s "$mysqldbname"_mysqldump_structure_${datetime}.sql.gz ]; then
echo "`date` mysqldump completed."
mailto2
else
echo "`date` The backup file does not exist, or the size is zero, exit"
mailto3
exit 1
fi
####### FTP backup ####################################
if [ "$ftpsw" == 'yes' ] && [ "$ftpdeldate" -gt 20120901 ]; then
echo "`date` Start uploading to offsite."
ftp -v -n -i <<END
open $ftpip $ftpport
user $ftpuser $ftppw
bin
cd $ftpbackupdir
mdelete *${ftpdeldate}*.gz
mput *${datetime}*.gz
bye
END
else
echo "`date` Not open FTP, or did not get to date. done."
fi
echo "`date` All completed."
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
