全量備份與增量備份實踐

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 |

+----+----------+

由此可以看到增量恢復數據恢復成功！