下载安装xtrabackup
[root@xag57 src]# wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.13/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.13-r3e7ca7c-el7-x86_64-bundle.tar
[root@xag57 src]# ls
boost_1_59_0.tar.gz cmake-3.7.1 cmake-3.7.1.tar.gz mysql-5.7.21 mysql-5.7.21.tar.gz Percona-XtraBackup-2.4.13-r3e7ca7c-el7-x86_64-bundle.tar
[root@xag57 src]# tar xf Percona-XtraBackup-2.4.13-r3e7ca7c-el7-x86_64-bundle.tar
[root@xag57 src]# yum install percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm -y
[root@xag57 src]# which xtrabackup
/usr/bin/xtrabackup
[root@xag57 src]# innobackupex -v
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
innobackupex version 2.4.13 Linux (x86_64) (revision id: 3e7ca7c)
創建測試數據
SQL->CREATE TABLE t1(Id int(11) NOT NULL,TestName varchar(30) NOT NULL DEFAULT '',PRIMARY KEY (Id)) ENGINE=InnoDB;
SQL->
insert into t1 values(1,'a');
insert into t1 values(2,'b');
insert into t1 values(3,'c');
commit;
root@localhost : test【03:50:06】29 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+----------+
xtrabackup 全量备份
#创建用于备份恢复的用户 pxb 并赋予权限
mysql> create user pxb@'localhost' identified by '123456';
mysql> grant reload,process,lock tables,replication client on *.* to pxb@localhost;
#创建存放目录
[root@xag57 local]# mkdir -pv /data/pxb
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/pxb’
#进行数据库全备
--no-lock:该选项表示关闭FTWRL的表锁,只有在所有表都是Innodb表并且不关心backup的binlog pos点,
如果有任何DDL语句正在执行或者非InnoDB正在更新时(包括mysql库下的表),都不应该使用这个选项,
后果是导致备份数据不一致,如果考虑备份因为获得锁失败,可以考虑--safe-slave-backup立刻停止复制线程。
[root@xag57 local]# cd /data/pxb
[root@xag57 pxb]#
innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=123456 --socket=/usr/local/mysql/mysql.sock /data/pxb
[root@xag57 pxb]# ls
2019-03-26_15-59-08
[root@xag57 pxb]# ll 2019-03-26_15-59-08/
total 12336
-rw-r----- 1 root root 489 Mar 26 15:59 backup-my.cnf
-rw-r----- 1 root root 357 Mar 26 15:59 ib_buffer_pool
-rw-r----- 1 root root 12582912 Mar 26 15:59 ibdata1
drwxr-x--- 2 root root 4096 Mar 26 15:59 mysql
drwxr-x--- 2 root root 8192 Mar 26 15:59 performance_schema
drwxr-x--- 2 root root 8192 Mar 26 15:59 sys
drwxr-x--- 2 root root 86 Mar 26 15:59 test
-rw-r----- 1 root root 113 Mar 26 15:59 xtrabackup_checkpoints
-rw-r----- 1 root root 484 Mar 26 15:59 xtrabackup_info
-rw-r----- 1 root root 2560 Mar 26 15:59 xtrabackup_logfile
#其中,mysql/、 performance_schema/、 sys/ 、test/ 下存放的是数据库文件。
#backup-my.cnf,备份命令用到的配置选项信息;
#ib_buffer_pool, buffer pool 中的热数据,当设置 innodb_buffer_pool_dump_at_shutdown=1 ,在关闭 MySQL 时,会把内存中的热数据保存在磁盘里 ib_buffer_pool 文件中,位于数据目录下。
#ibdata1,备份的共享表空间文件;
#xtrabackup_logfile,备份的重做日志文件。
[root@xag57 pxb]# cd 2019-03-26_16-18-18/
[root@xag57 2019-03-26_16-18-18]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=101
redo_log_version=1
server_uuid=45927d5b-4f98-11e9-bd41-000c29c994f8
master_key_id=0
#xtrabackup_binlog_info,mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置;
[root@xag57 2019-03-26_16-18-18]# cat xtrabackup_binlog_info
binlog.000001 154
#xtrabackup_checkpoints,备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息;
[root@xag57 2019-03-26_16-18-18]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2564162
last_lsn = 2564171
compact = 0
recover_binlog_info = 0
#xtrabackup_info,记录备份的基本信息,uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息。
[root@xag57 2019-03-26_16-18-18]# cat xtrabackup_info
uuid = b974386d-4f9f-11e9-88eb-000c29c994f8
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=pxb --password=... --socket=/usr/local/mysql/mysql.sock /data/pxb
tool_version = 2.4.13
ibbackup_version = 2.4.13
server_version = 5.7.21-log
start_time = 2019-03-26 16:18:18
end_time = 2019-03-26 16:18:24
lock_time = 0
binlog_pos = filename 'binlog.000001', position '154'
innodb_from_lsn = 0
innodb_to_lsn = 2564162
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
全量恢復
root@localhost : test【04:31:40】2 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+----------+
3 rows in set (0.00 sec)
root@localhost : test【04:31:47】3 SQL->delete from t1 where id=2;
Query OK, 1 row affected (0.01 sec)
root@localhost : test【04:31:58】4 SQL->commit;
Query OK, 0 rows affected (0.01 sec)
root@localhost : test【04:32:01】5 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 3 | c |
+----+----------+
2 rows in set (0.00 sec)
###恢复数据之前需要保证数据目录是空的状态
#关闭数据库并删除数据文件
[root@xag57 /]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@xag57 /]# cd /usr/local/mysql
[root@xag57 mysql]# mv data/ data_bak/
[root@xag57 mysql]# mkdir data
#通過備份還原被刪除的數據(id=2)
#准备(prepare)一个完全备份: --apply-log ( /data/pxb/2019-03-26_16-18-18/ 为备份目录,执行之后 xtrabackup_checkpoints 文件中的 backup_type = full-prepared )
[root@xag57 pxb]# innobackupex --apply-log /data/pxb/2019-03-26_16-18-18/
#执行恢复操作
[root@xag57 pxb]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/pxb/2019-03-26_16-18-18/
#更改 data/ 目录权限并启动mysql:
[root@MYSQL ~]# chown -Rf mysql:mysql /usr/local/mysql/data
[root@xag57 mysql]# service mysqld start
Starting MySQL. SUCCESS!
檢查恢復情況
[root@xag57 mysql]# mysql -uroot -p123456
root@localhost : (none)【04:45:31】1 SQL->use test
root@localhost : test【04:45:38】2 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+----------+
3 rows in set (0.00 sec)
增量備份及恢復
#创建增量備份存放目录
[root@xag57 pxb]# mkdir -pv /data/pxb/inc
#我们以之前做的全备为基准,在其基础上做增量备份:
#新建一张表,并插入数据作为增量
root@localhost : test【05:03:26】4 SQL->insert into t2 values (1,'aaa'),(2,'bbb'),(3,'ccc'),(26,'zzz');
commit;
root@localhost : test【05:04:41】7 SQL->select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 26 | zzz |
+------+------+
6.1. 增量备份1:( 以全备为基准:/data/pxb/2019-03-26_16-18-18/ )
[root@xag57 pxb]# innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=123456 --socket=/usr/local/mysql/mysql.sock --incremental /data/pxb/inc --incremental-basedir=/data/pxb/2019-03-26_16-18-18/ --parallel=2
[root@xag57 pxb]# ls
2019-03-26_15-59-08 2019-03-26_16-18-18 inc
[root@xag57 pxb]# cd inc
[root@xag57 inc]# ls
2019-03-26_17-08-01
[root@xag57 inc]# cd 2019-03-26_17-08-01/
[root@xag57 2019-03-26_17-08-01]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2564162
to_lsn = 2572324
last_lsn = 2572333
compact = 0
recover_binlog_info = 0
6.2. 再往 tb2 里插入数据:
root@localhost : test【05:10:42】9 SQL->insert into t2 values (201,'aaa'),(202,'bbb'),(203,'ccc'),(226,'zzz');
root@localhost : test【05:10:49】10 SQL->commit;
root@localhost : test【05:10:51】11 SQL->select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 26 | zzz |
| 201 | aaa |
| 202 | bbb |
| 203 | ccc |
| 226 | zzz |
+------+------+
6.3. 增量备份2:( 以增量1为基准:/data/pxb/inc/2019-03-26_17-08-01/ )
[root@xag57 inc]# innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=123456 --socket=/usr/local/mysql/mysql.sock --incremental /data/pxb/inc --incremental-basedir=/data/pxb/inc/2019-03-26_17-08-01/ --parallel=2
[root@xag57 inc]# ll
total 0
drwxr-x--- 6 root root 261 Mar 26 17:08 2019-03-26_17-08-01
drwxr-x--- 6 root root 261 Mar 26 17:15 2019-03-26_17-15-26
[root@xag57 inc]# cd 2019-03-26_17-15-26/
[root@xag57 2019-03-26_17-15-26]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2572324
to_lsn = 2575129
last_lsn = 2575138
compact = 0
recover_binlog_info = 0
7.1. 增量备份的恢复之準備
#增量备份的恢复需要有3个步骤
1、恢复完全备份
2、恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数,到最后一次增量备份要去掉--redo-only)
3、对整体的完全备份进行恢复,回滚未提交的数据
##准备一个全备##
[root@xag57 pxb]# innobackupex --apply-log --redo-only /data/pxb/2019-03-26_16-18-18/
##将增量1应用到完全备份##
[root@xag57 pxb]# innobackupex --apply-log --redo-only /data/pxb/2019-03-26_16-18-18/ --incremental-dir=/data/pxb/inc/2019-03-26_17-08-01/
##将增量2应用到完全备份,注意不加 --redo-only 参数了##
[root@xag57 pxb]# innobackupex --apply-log /data/pxb/2019-03-26_16-18-18/ --incremental-dir=/data/pxb/inc/2019-03-26_17-15-26/
##把所有合在一起的完全备份整体进行一次apply操作,回滚未提交的数据##
[root@xag57 pxb]# innobackupex --apply-log /data/pxb/2019-03-26_16-18-18/
7.2. 增量备份恢復之测试
root@localhost : test【05:11:25】12 SQL->drop table t2;
###恢复数据之前需要保证数据目录是空的状态
#关闭数据库并删除数据文件
[root@xag57 /]# service mysqld stop
[root@xag57 /]# cd /usr/local/mysql
[root@xag57 mysql]# mv data/ data_bak2/
[root@xag57 mysql]# mkdir data
#執行恢復操作
[root@xag57 mysql]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/pxb/2019-03-26_16-18-18/
#更改 data/ 目录权限并启动mysql:
[root@MYSQL ~]# chown -Rf mysql:mysql /usr/local/mysql/data
[root@xag57 mysql]# service mysqld start
[root@xag57 mysql]# mysql -uroot -p123456
root@localhost : (none)【05:52:52】1 SQL->use test
root@localhost : test【05:52:57】2 SQL->select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 26 | zzz |
| 201 | aaa |
| 202 | bbb |
| 203 | ccc |
| 226 | zzz |
+------+------+
8 rows in set (0.01 sec)