xtrabackup备份实操

分类 Mysql 标签 Mysql 评论:

xtrabackup备份实操

xtrabackup备份实操

准备工作

下载安装xtrabackup

[root@lyucan ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/tarball/percona-xtrabackup-2.4.5-Linux-x86_64.tar.gz

[root@lyucan ~]# tar -xf percona-xtrabackup-2.4.5-Linux-x86_64.tar.gz

[root@lyucan ~]# mv percona-xtrabackup-2.4.5-Linux-x86_64 percona-xtrabackup

#无需编译，可直接使用

创建备份用户

mysql> create user pxb@'localhost' identified by 'echo123.';Query OK, 0 rows affected (0.00 sec)mysql>  grant reload,process,lock tables,replication client on *.* to pxb@localhost;Query OK, 0 rows affected (0.00 sec)

创建备份存放目录

[root@lyucan ~]# mkdir -p /data/xtrabackup

全量备份与恢复

全量备份

[root@lyucan ~]# cd percona-xtrabackup/
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --no-timestamp /data/xtrabackup/all-20180410-bak

......

180410 23:11:42 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1475999660'
xtrabackup: Stopping log copying thread.
.180410 23:11:42 >> log scanned up to (1475999669)

180410 23:11:42 Executing UNLOCK TABLES
180410 23:11:42 All tables unlocked
180410 23:11:42 [00] Copying ib_buffer_pool to /data/xtrabackup/all-20180410-bak/ib_buffer_pool
180410 23:11:42 [00]        ...done
180410 23:11:42 Backup created in directory '/data/xtrabackup/all-20180410-bak'
MySQL binlog position: filename 'mysql-bin.000004', position '8623'
180410 23:11:42 [00] Writing backup-my.cnf
180410 23:11:42 [00]        ...done
180410 23:11:42 [00] Writing xtrabackup_info
180410 23:11:42 [00]        ...done
xtrabackup: Transaction log of lsn (1475999660) to (1475999669) was copied.
180410 23:11:42 completed OK!

查看备份文件

[root@lyucan all-20180410-bak]# cd /data/xtrabackup/all-20180410-bak/
[root@lyucan all-20180410-bak]# ll
total 77876
-rw-r-----. 1 root root      425 Apr 10 23:11 backup-my.cnf
drwxr-x---. 2 root root       49 Apr 10 23:11 dbtest
-rw-r-----. 1 root root      503 Apr 10 23:11 ib_buffer_pool
-rw-r-----. 1 root root 79691776 Apr 10 23:11 ibdata1
drwxr-x---. 2 root root     4096 Apr 10 23:11 mysql
drwxr-x---. 2 root root     8192 Apr 10 23:11 performance_schema
drwxr-x---. 2 root root     8192 Apr 10 23:11 sys
drwxr-x---. 2 root root       45 Apr 10 23:11 test
-rw-r-----. 1 root root       22 Apr 10 23:11 xtrabackup_binlog_info
-rw-r-----. 1 root root      119 Apr 10 23:11 xtrabackup_checkpoints
-rw-r-----. 1 root root      560 Apr 10 23:11 xtrabackup_info
-rw-r-----. 1 root root     2560 Apr 10 23:11 xtrabackup_logfile

xtrabackup_binlog_info 文件记录了备份完成时binlog的position；

[root@lyucan all-20180410-bak]# cat xtrabackup_binlog_info
mysql-bin.000004     8623

xtrabackup_checkpoints 记录了备份的起始lsn号和最后的lsn号

[root@lyucan all-20180410-bak]# cat xtrabackup_checkpoints
backup_type = full-backuped             ##full-backuped表示全备
from_lsn = 0                            ##全备lsn号一定是从0开始
to_lsn = 1475999660
last_lsn = 1475999669
compact = 0
recover_binlog_info = 0

xtrabackup_info 记录了备份的信息，时间，命令，版本等；

[root@lyucan all-20180410-bak]# cat xtrabackup_info
uuid = 79f03ea9-3cd1-11e8-bf94-da8255dea6c5
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=pxb --password=... --socket=/tmp/mysql.sock --no-timestamp /data/xtrabackup/all-20180410-bak
tool_version = 2.4.5
ibbackup_version = 2.4.5
server_version = 5.7.21-log
start_time = 2018-04-10 23:11:39
end_time = 2018-04-10 23:11:42
lock_time = 0
binlog_pos = filename 'mysql-bin.000004', position '8623'
innodb_from_lsn = 0
innodb_to_lsn = 1475999660
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

全量恢复

[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --apply-log  /data/xtrabackup/all-20180410-bak

......
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 8129, file name mysql-bin.000004
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 1475999765
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1475999793
180410 23:36:27 completed OK!

这一步的--apply-log的作用是将备份的事务进行前滚（redo）或回滚（undo），将已提交的前滚，未提交的回滚，达到数据的一致性；这个一致性的时间点就是备份过程中FTWRL锁表的时间点。

应用备份库

#停掉当前库
[root@lyucan xtrabackup]# service mysqld stop
Shutting down MySQL.... SUCCESS!

#将当前库重命名
[root@lyucan data]# mv /data/mysql/  /data/mysql_bak_20180410

#将备份库重命名为mysql
[root@lyucan data]# mv /data/xtrabackup/all-20180410-bak/  /data/mysql

#修改权限
[root@lyucan data]# chown mysql:mysql -R /data/mysql/

#启动数据库

[root@lyucan data]# service mysqld start
Starting MySQL.. SUCCESS!

要完全恢复数据库，还要结合二进制日志进行恢复；

增量备份与恢复

增量备份

#首先进行一次全备
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --no-timestamp /data/xtrabackup/all-20180410-bak

#进行一次增备，基于第一次的全备
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user=pxb --password=echo123. --socket=/tmp/mysql.sock --incremental --incremental-basedir=/data/xtrabackup/all-20180410-bak  /data/xtrabackup/incremental-20180411-bak

--incremental：表示是增备
--incremental-basedir：基于哪个备份进行增备

#再进行一次增备，基于第二次的增备
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user=pxb --password=echo123. --socket=/tmp/mysql.sock --incremental --incremental-basedir=/data/xtrabackup/incremental-20180411-bak  /data/xtrabackup/incremental-20180412-bak

查看三次的备份文件

# checkpoint
[root@lyucan xtrabackup]# cat /data/xtrabackup/all-20180410-bak/xtrabackup_checkpoints
backup_type = full-backuped             ##全备
from_lsn = 0
to_lsn = 1476007364
last_lsn = 1476007373
compact = 0
recover_binlog_info = 0

[root@lyucan xtrabackup]# cat /data/xtrabackup/incremental-20180411-bak/xtrabackup_checkpoints
backup_type = incremental               ##增备
from_lsn = 1476007364
to_lsn = 1476011232
last_lsn = 1476011241
compact = 0
recover_binlog_info = 0

[root@lyucan xtrabackup]# cat /data/xtrabackup/incremental-20180412-bak/xtrabackup_checkpoints
backup_type = incremental              ##增备
from_lsn = 1476011232
to_lsn = 1476015084
last_lsn = 1476015093
compact = 0
recover_binlog_info = 0

##可以看到lsn号都是连续的

# binlog_info
[root@lyucan xtrabackup]# cat /data/xtrabackup/all-20180410-bak/xtrabackup_binlog_info
mysql-bin.000005     479
[root@lyucan xtrabackup]# cat /data/xtrabackup/incremental-20180411-bak/xtrabackup_binlog_info
mysql-bin.000005     645
[root@lyucan xtrabackup]# cat /data/xtrabackup/incremental-20180412-bak/xtrabackup_binlog_info
mysql-bin.000005     811

增备恢复

##恢复第一个全备，加上--redo-only，表示只进行前滚
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --apply-log --redo-only  /data/xtrabackup/all-20180410-bak


##将20180411增量备份加到全量备份上，使用--incremental-dir= 来指定增量备份
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --apply-log --redo-only  /data/xtrabackup/all-20180410-bak  --incremental-dir=/data/xtrabackup/incremental-20180411-bak

##将20180412增量备份加到全量备份上，使用--incremental-dir= 来指定增量备份
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --apply-log --redo-only  /data/xtrabackup/all-20180410-bak  --incremental-dir=/data/xtrabackup/incremental-20180412-bak

##将所有增量备份加到全备上后，再进行全备的恢复，回归到全量备份，此时没有--redo-only；
[root@lyucan percona-xtrabackup]# ./bin/innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=echo123. --socket=/tmp/mysql.sock --apply-log  /data/xtrabackup/all-20180410-bak

应用备份库

#停掉当前库
[root@lyucan xtrabackup]# service mysqld stop
Shutting down MySQL.... SUCCESS!


#将当前库重命名
[root@lyucan data]# mv /data/mysql/  /data/mysql_bak_20180410


#将备份库重命名为mysql
[root@lyucan data]# mv /data/xtrabackup/all-20180410-bak/  /data/mysql


#修改权限
[root@lyucan data]# chown mysql:mysql -R /data/mysql/


#启动数据库
[root@lyucan data]# service mysqld start
Starting MySQL.. SUCCESS!

还是那句话，如果要进行完全数据恢复，还需要结合二进制日志进行数据恢复；
很简单，根据备份里面记录的xtrabackup_binlog_info，进行原库二进制日志的恢复；

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com