【MySQL】MySQL备份恢复工具之XtraBackup

XtraBackup概述

XtraBackup是Percona提供的一款热备工具，除开源、免费外，XtraBackup提供了以下优点：

• 备份集高效、完整、可用；
• 备份任务过程中不会阻塞事务；
• 节省磁盘空间，使用更好的压缩降低网络带宽；
• 备份集自动验证机制；
• 恢复更快；

XtraBackup安装

1、安装依赖包；

[root@strong ~]# yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel vim-common;

2、下载XtraBackup压缩包；

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

3、解压缩；

[root@db1 local]# tar zxvf /u01/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
[root@db1 local]# chown -R  mysql:mysql percona-xtrabackup-2.4.9-Linux-x86_64
[root@db1 local]# ll percona-xtrabackup-2.4.9-Linux-x86_64/bin/
total 212008
lrwxrwxrwx. 1 mysql mysql        10 Nov 23 17:20 innobackupex -> xtrabackup
-rwxr-xr-x. 1 mysql mysql   5357661 Nov 23 17:06 xbcloud
-rwxr-xr-x. 1 mysql mysql      3020 Nov 23 17:06 xbcloud_osenv
-rwxr-xr-x. 1 mysql mysql   5270021 Nov 23 17:06 xbcrypt
-rwxr-xr-x. 1 mysql mysql   5344286 Nov 23 17:06 xbstream
-rwxr-xr-x. 1 mysql mysql 201111701 Nov 23 17:18 xtrabackup
[root@db1 local]# 
--将XtraBackup程序目录加入PATH路径
[root@db1 ~]# echo "export PATH=/usr/local/percona-xtrabackup-2.4.9-Linux-x86_64/bin:\$PATH" >> .bash_profile 
[root@db1 ~]# source .bash_profile 

4、创建XtraBackup用户；

mysql> create user 'xtrabk'@'localhost' identified by 'xtrabk';
Query OK, 0 rows affected (0.28 sec)

mysql> grant reload,lock tables,process,replication client on *.* to 'xtrabk'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> 

XtraBackup备份

完全备份

1、执行备份；

[root@db1 ~]# xtrabackup --backup -u xtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/
180316 10:56:14  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'xtrabk'  (using password: YES).
180316 10:56:14  version_check Connected to MySQL server
180316 10:56:14  version_check Executing a version check against the server...
180316 10:56:14  version_check Done.
180316 10:56:14 Connecting to MySQL server host: localhost, user: xtrabk, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.21-log
--xtrabackup版本信息
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: uses posix_fadvise().
--首先找到数据文件路径，收集Innodb引擎信息
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180316 10:56:14 >> log scanned up to (5550616)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 21 for sys/sys_config, old maximum was 0
180316 10:56:15 >> log scanned up to (5550616)
--开始复制文件
180316 10:56:16 [01] Copying ./ibdata1 to /u01/hotback/ibdata1
180316 10:56:16 >> log scanned up to (5550616)
180316 10:56:17 >> log scanned up to (5550616)
180316 10:56:18 [01]        ...done
180316 10:56:18 [01] Copying ./sys/sys_config.ibd to /u01/hotback/sys/sys_config.ibd
180316 10:56:18 [01]        ...done
180316 10:56:18 [01] Copying ./test/t_mm.ibd to /u01/hotback/test/t_mm.ibd
180316 10:56:18 [01]        ...done
180316 10:56:18 >> log scanned up to (5550616)
180316