2015-10-25
目录
一、源码安装
二、全量备份
三、全备恢复
四、增量备份
五、增备恢复
六、自动备份
一、源码安装
#配置yum仓库 cd /etc/yum.repos.d wget http://mirrors.opencas.cn/epel/epel-release-latest-6.noarch.rpm wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm rpm -ivh epel-release-latest-6.noarch.rpm rpm -ivh rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm yum clean all && yum makecache #下载源码包 wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/source/tarball/percona-xtrabackup-2.3.2.tar.gz #解压源码包 tar -zxf percona-xtrabackup-2.3.2.tar.gz -C /usr/local/src/ && cd /usr/local/src/percona-xtrabackup-2.3.2/ #安装基础包 yum -y install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel #配置环境 cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF #编译安装 make -j4 && make install #优化路径 vim ~/.bash_profile PATH=$PATH:$HOME/bin:/usr/local/xtrabackup/bin source ~/.bash_profile 或者 export PATH=/usr/local/xtrabackup/bin:$PATH
二、全量备份
#创建备份用户 mysql> grant usage,reload,lock tables,replication client,create tablespace,super on *.* to 'backup'@'localhost' identified by 'backup'; mysql> grant usage,reload,lock tables,replication client,create tablespace,super on *.* to 'backup'@'%' identified by 'backup'; #创建备份目录 mkdir -p /data/backup/{innobackup,log,mysql,scripts} mkdir -p /data/backup/innobackup/{full,increment} #创建测试表 mysql> create database backup_test; mysql> use backup_test; mysql> create table test(id int auto_increment not null primary key,name varchar(20)); mysql> insert into test(name) values('test1'),('test2'),('test3'),('test4'); #创建全量备份 innobackupex --user=backup --password=backup /data/backup/innobackup/full/
三、全备恢复
#关闭mysql服务器 service mysqld stop #备份原始数据目录 mv /data/mysql/3306/ /data/mysql/3306.bak #创建新数据目录 mkdir -p /data/mysql/3306 #回滚日志 innobackupex --apply-log /data/backup/innobackup/full/2015-10-25_12-36-16/ #恢复全备 innobackupex --copy-back /data/backup/innobackup/full/2015-10-25_12-36-16/ #修改目录权限 chown -R mysql.mysql /data/mysql/3306 #启动mysql服务器 service mysqld start
四、增量备份
#创建基础备份（直接使用全量备份） innobackupex --user=backup --password=backup /data/backup/innobackup/full/ #添加增量数据 mysql> use backup_test; mysql> insert into test(name) values('test5'),('test6'),('test7'),('test8'); #创建增量备份 innobackupex --user=backup --password=backup --incremental /data/backup/innobackup/increment/ --incremental-basedir=/data/backup/innobackup/full/2015-10-25_12-36-16/
五、增备恢复
#关闭mysql服务器 service mysqld stop #备份原始数据目录 mv /data/mysql/3306/ /data/mysql/3306.bak1 #创建新数据目录 mkdir -p /data/mysql/3306 #回滚基础备份日志中已提交数据 innobackupex --apply-log --redo-only /data/backup/innobackup/full/2015-10-25_12-36-16/ #回滚第1个增量备份日志中已提交数据，并合并到基础备份日志 innobackupex --apply-log --redo-only /data/backup/innobackup/full/2015-10-25_12-36-16/ --incremental-dir=/data/backup/innobackup/increment/2015-10-25_14-16-00/ #回滚合并后，基础备份日志中，未提交数据 innobackupex --apply-log /data/backup/innobackup/full/2015-10-25_12-36-16/ #恢复全部备份 innobackupex --copy-back /data/backup/innobackup/full/2015-10-25_12-36-16/ #修改目录权限 chown -R mysql.mysql /data/mysql/3306 #启动mysql服务器 service mysqld start
六、自动备份
#上传备份脚本 /data/backup/scripts/inno_backup.sh #添加计划任务 crontab -e #backup mysql by leocen@2015-10-25 50 2 * * * /bin/bash /data/backup/scripts/inno_backup.sh >/dev/null 2>&1 #创建第1个全量备份 innobackupex --user=backup --password=backup /data/backup/innobackup/full/ #执行备份脚本 /bin/bash /data/backup/scripts/inno_backup.sh
inno_backup.sh
#!/bin/bash ### AUTHOR: Leocen ### DATE: 2015/10/25 ### REV: V0.1 source /etc/profile source /root/.bash_profile MYSQL_LOG=/data/backup/log/mysql_bk.log TODAY=`date +%Y%m%d` target_full_dir=/data/backup/innobackup/full target_increment_dir=/data/backup/innobackup/increment mysql_conf=/etc/my.cnf user=backup password=backup # send mail configuration DATE=`date '+%Y-%m-%d %H:%M:%S'` echo "-------------------------------------$TODAY-----------------------">$MYSQL_LOG WEEK_DAILY=`date +%a` case "$WEEK_DAILY" in "Mon") export BAK_LEVEL=Increment ;; "Tue") export BAK_LEVEL=Increment ;; "Wed") export BAK_LEVEL=Increment ;; "Thu") export BAK_LEVEL=Increment ;; "Fri") export BAK_LEVEL=Increment ;; "Sat") export BAK_LEVEL=Increment ;; "Sun") export BAK_LEVEL=Full ;; "*") export BAK_LEVEL=error esac echo "Today is : $WEEK_DAILY Backup level=$BAK_LEVEL">>$MYSQL_LOG case "$BAK_LEVEL" in "Increment") full_db_dir=`ls $target_full_dir` cd $target_increment_dir rm -rf * echo "Start incremental backup ........" sleep 5 innobackupex --defaults-file=$mysql_conf --user=$user --password=$password --incremental-basedir=$target_full_dir/$full_db_dir --incremental $target_increment_dir RSTAT=$? echo "RSTAT=$RSTAT" echo "RSTAT=$RSTAT">>$MYSQL_LOG cd /data/backup/innobackup tar -zcvf $HOSTNAME_"$TODAY"_Inc.tar.gz increment/ mv $HOSTNAME_"$TODAY"_Inc.tar.gz /data/backup/mysql/ #/usr/bin/rsync --log-file=/data/backup/log/rsync.log -a /data/backup/innobackup/ivi_"$TODAY"_Inc.tar.gz 10.105.29.161::backup/ivi/ >/dev/null 2>&1 #if [ $? = 0 ];then #echo "rsync completed!" #rm -f /data/backup/innobackup/ivi_"$TODAY"_Inc.tar.gz #else #echo "rsync uncompleted!" #RSYNCLOG="rsync error!" #echo "=====================$RSYNCLOG on `date`====================">>$MYSQL_LOG #fi ;; "Full") cd $target_full_dir rm -rf * echo "Start full backup .........." sleep 5 innobackupex --defaults-file=$mysql_conf --user=$user --password=$password $target_full_dir RSTAT=$? echo "RSTAT=$RSTAT" echo "RSTAT=$RSTAT" >>$MYSQL_LOG cd /data/backup/innobackup tar -zcvf $HOSTNAME_"$TODAY"_Full.tar.gz full/ mv $HOSTNAME_"$TODAY"_Full.tar.gz /data/backup/mysql/ #/usr/bin/rsync --log-file=/data/backup/log/rsync.log -a /data/backup/innobackup/ivi_"$TODAY"_Full.tar.gz 10.105.29.161::backup/ivi/ >/dev/null 2>&1 #if [ $? = 0 ];then #echo "rsync completed!" #rm -f /data/backup/innobackup/ivi_"$TODAY"_Full.tar.gz #else #echo "rsync uncompleted!" #RSYNCLOG="rsync error!" #echo "=====================$RSYNCLOG on `date`====================">>$MYSQL_LOG #fi ;; "*") exit 99 ;; esac if [ $RSTAT = 0 ];then LOGMSG="backup end successfully!" echo $LOGMSG else LOGMSG="backup end in error." echo $LOGMSG # mailx -s "Mysql database backup was failured." -r arvinzhou@pateo.com.cn -c rich@pateo.com.cn kobezhu@pateo.com.cn,arvinzhou@pateo.com.cn < /data/backup/script/mail fi echo "=====================$LOGMSG on `date`====================">>$MYSQL_LOG
参考资料
[1] 唐汉明.深入浅出MySQL 数据库开发、优化与管理维护（第2版）[M].北京：人民邮电出版社，2014
[2] Schwartz.高性能MySQL（第3版）[M].北京：电子工业出版社，2013
[3] Download Percona XtraBackup