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
xtrabackup-2.3.2 备份恢复mysql 5.6
原文：http://www.cnblogs.com/cenliang/p/4909064.html