mysql大数据量且多存储引擎场景下的完整+增量自动异地备份的可靠方案

假想场景：数据库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

解后恢复到数据目录

更多资料：http://www.percona.com/software/percona-xtrabackup

脚本内容：

把脚本修改为适合自已的实际场景的，几乎只需要修改变量设定部分，变量设置不正确，脚本运行会报错，通过脚本的日志和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."

原文：

http://www.zhaoyanan.cn/mysql-innobackupex.html

http://www.zhaoyanan.cn/mysql-mysqldump-auto.html

转载于:https://blog.51cto.com/zhaoyanan/1126027