全量备份与增量备份实践
1.1 全备备份与恢复全备实践
#######################################################################################
1、安装Xtrabackup备份命令
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
yum -y install perl perl-devel libaio libaio-develperl-Time-HiRes perl-DBD-MySQL
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
innobackupex ******
2、创建logs目录并配置权限
mkdir /application/mysql/logs -p
chown -R /mysql.mysql /application/mysql/logs
3、配置/etc/my.cnf文件
[client]
user=root
password=oldboy123
[mysqld]
basedir= /application/mysql/
datadir= /application/mysql/data/
###########binlog############
log_bin = /application/mysql/logs/oldboy-bin
expire_logs_days = 7
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error = /application/mysql/logs/oldboy.err
配置完成重启生效：
[root@db02 data]#/etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL... SUCCESS!
4、全被备份
mkdir /server/backup -p
innobackupex --defaults-file=/etc/my.cnf--user=root --password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp/server/backup/full
5、恢复数据前的准备（合并xtrabackup_log_file和备份的物理文件）
innobackupex --apply-log --use-memory=32M/server/backup/full/
停库：
/etc/init.d/mysqld stop
lsof -i :3306
破坏数据：
cd /application/mysql/
mv data /opt/
恢复：
cp -a /server/backup/full/ /application/mysql/data
chown -R mysql.mysql /application/mysql/data
启动：
/etc/init.d/mysqld start
mysql -e "select * from oldboy.test"
数据恢复成功。
1.2 实现增量备份及恢复：
环境准备：
du -sh /application/mysql/data/
free -m
cd /server/backup/
\rm -rf *
1、全量备份（周一0点）
innobackupex --defaults-file=/etc/my.cnf--user=root --password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp/server/backup/base_full
2、第一次增量备份（周二0点）
a.准备
use oldboy；
mysql> insert into test values(8,'outman'); 插入两条数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(9,'outgirl');
Query OK, 1 row affected (0.00 sec)
select *from test;
b.增量
innobackupex --defaults-file=/etc/my.cnf--user=root --password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp--incremental-basedir=/server/backup/base_full --incremental/server/backup/one_inc
2、第二次增量
mysql> use oldboy
mysql> insert into test values(10,'two_inc1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(11,'two_inc2');
Query OK, 1 row affected (0.00 sec)
innobackupex --defaults-file=/etc/my.cnf--user=root --password=oldboy123--socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --parallel=3--incremental-basedir=/server/backup/one_inc/ --incremental/server/backup/two_inc
增量恢复：
innobackupex --apply-log --use-memory=32M--redo-only /server/backup/base_full/
innobackupex --apply-log --use-memory=32M--redo-only --incremental-dir=/server/backup/one_inc /server/backup/base_full/
innobackupex --apply-log --use-memory=32M--incremental-dir=/server/backup/two_inc /server/backup/base_full/正式数据文件
恢复过程：
[root@db02 backup]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@db02 backup]# cd /application/mysql
[root@db02 mysql]# mv data /tmp/
[root@db02 mysql]# /bin/cp -a/server/backup/base_full data
[root@db02 mysql]# chown -R mysql.mysql data
[root@db02 mysql]# /etc/init.d/mysqld start
Starting MySQL......... SUCCESS!
[root@db02 mysql]# mysql -e "select * fromoldboy.test;"
+----+----------+
| id | name |
+----+----------+
| 1 |oldboy |
| 2 |oldgirl |
| 3 |inca |
| 4 |zuma |
| 5 |kaka |
| 6 |bingbing |
| 7 |xiaoting |
| 8 |outman |
| 9 |outgirl |
| 10 | two_inc1 |
| 11 | two_inc2 |
+----+----------+
由此可以看到增量恢复数据恢复成功！
