【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.gz3、解压缩；
[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 10:56:19 [01] Copying ./test/t_emp.ibd to /u01/hotback/test/t_emp.ibd
180316 10:56:19 [01] ...done
180316 10:56:19 [01] Copying ./mysql/slave_worker_info.ibd to /u01/hotback/mysql/slave_worker_info.ibd
180316 10:56:19 [01] ...done
180316 10:56:19 [01] Copying ./mysql/time_zone_transition.ibd to /u01/hotback/mysql/time_zone_transition.ibd
.............................中间过程省略..........................
180316 10:56:19 >> log scanned up to (5550616)
180316 10:56:20 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180316 10:56:20 >> log scanned up to (5550616)
180316 10:56:21 Executing FLUSH TABLES WITH READ LOCK...
180316 10:56:21 Starting to backup non-InnoDB tables and files
180316 10:56:21 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /u01/hotback/performance_schema/events_stages_summary_by_user_by_event_name.frm
180316 10:56:21 [01] ...done
180316 10:56:21 [01] Copying ./performance_schema/threads.frm to /u01/hotback/performance_schema/threads.frm
180316 10:56:21 [01] ...done
180316 10:56:21 [01] Copying ./performance_schema/metadata_locks.frm to /u01/hotback/performance_schema/metadata_locks.frm
180316 10:56:21 [01] ...done
.............................中间过程省略..........................
180316 10:56:24 Finished backing up non-InnoDB tables and files
180316 10:56:24 [00] Writing /u01/hotback/xtrabackup_binlog_info
180316 10:56:24 [00] ...done
180316 10:56:24 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '5550607'
xtrabackup: Stopping log copying thread.
.180316 10:56:24 >> log scanned up to (5550616)
180316 10:56:24 Executing UNLOCK TABLES
180316 10:56:24 All tables unlocked
180316 10:56:24 [00] Copying ib_buffer_pool to /u01/hotback/ib_buffer_pool
180316 10:56:25 [00] ...done
180316 10:56:25 Backup created in directory '/u01/hotback/'
MySQL binlog position: filename 'mysql-bin.000007', position '12110'
180316 10:56:25 [00] Writing /u01/hotback/backup-my.cnf
180316 10:56:25 [00] ...done
180316 10:56:25 [00] Writing /u01/hotback/xtrabackup_info
180316 10:56:25 [00] ...done
--备份过程结束
xtrabackup: Transaction log of lsn (5550607) to (5550616) was copied.
180316 10:56:25 completed OK!2、查看备份文件；
[root@db1 ~]# ls -lh /u01/hotback/
total 13M
-rw-r-----. 1 root root 426 Mar 16 10:56 backup-my.cnf
-rw-r-----. 1 root root 1.1K Mar 16 10:56 ib_buffer_pool
-rw-r-----. 1 root root 12M Mar 16 10:56 ibdata1
drwxr-x---. 2 root root 4.0K Mar 16 10:56 mysql
drwxr-x---. 2 root root 4.0K Mar 16 10:56 performance_schema
drwxr-x---. 2 root root 12K Mar 16 10:56 sys
drwxr-x---. 2 root root 4.0K Mar 16 10:56 test
-rw-r-----. 1 root root 23 Mar 16 10:56 xtrabackup_binlog_info
-rw-r-----. 1 root root 113 Mar 16 10:56 xtrabackup_checkpoints
-rw-r-----. 1 root root 508 Mar 16 10:56 xtrabackup_info
-rw-r-----. 1 root root 2.5K Mar 16 10:56 xtrabackup_logfile
[root@db1 ~]# 3、准备备份集；
备份后的数据并不处于一致性状态，为了可以用来恢复，需要使用--prepare准备备份集。
[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/hotback/
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /u01/hotback/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5550607)
xtrabackup: using the following InnoDB configuration for recovery:
.............................中间过程省略..........................
InnoDB: Shutdown completed; log sequence number 5551144
180316 11:49:33 completed OK!
[root@db1 ~]# 注：如果想让该全备作为增量备份的基础，需要使用--apply-log-only选项准备备份集。
增量备份
增量备份是基于完全备份，故进行增量备份前，须进行一次全备。增量备份之所以可行是因为InnoDB页包含了日志序列号（也称为LSN），LSN是整个数据库的系统版本号，每一页的LSN记录着最近它的变化。
1、创建全备份；
[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/base
--XtraBackup将备份结束时的LSN写到xtrabackup_checkpoints文件中
[root@db1 ~]# more /u01/hotback/base/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 5551689
last_lsn = 5551698
compact = 0
recover_binlog_info = 0
[root@db1 ~]# 2、插入一些数据；
mysql> insert into t_emp (name,sex) values('Incr1',1),('Test Incr1',0);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> 3、进行第一次增量备份；
[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/incr1 --incremental-basedir=/u01/hotback/base/
[root@db1 ~]# more /u01/hotback/incr1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5551689 --该LSN是全备的to_lsn
to_lsn = 5552148
last_lsn = 5552157
compact = 0
recover_binlog_info = 0
[root@db1 ~]# 4、创建一些测试数据；
mysql> create table t_myisam(id int,name varchar(100),cdate datetime default now())engine=myisam;
Query OK, 0 rows affected (0.23 sec)
mysql> insert into t_myisam(id,name) values(1,'Alen'),(2,'MyISAM');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t_emp(name,sex)values('Test Incr2',0),('Incr2',1);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 05、进行第二次增量备份；
[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --target-dir=/u01/hotback/incr2 --incremental-basedir=/u01/hotback/incr1/
[root@db1 ~]# more /u01/hotback/incr
incr1/ incr2/
[root@db1 ~]# more /u01/hotback/incr2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5552148 --该LSN是第一次增备的to_lsn
to_lsn = 5552578
last_lsn = 5552587
compact = 0
recover_binlog_info = 0
[root@db1 ~]# 6、准备全备备份集；
[root@db1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/u01/hotback/base/
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /u01/hotback/base/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5551689)
.............................中间过程省略..........................
InnoDB: Shutdown completed; log sequence number 5551707
InnoDB: Number of pools: 1
180316 13:38:27 completed OK!7、准备第一次增量备份集；
[root@db1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/u01/hotback/base --incremental-dir=/u01/hotback/incr1
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
incremental backup from 5551689 is enabled.
xtrabackup: cd to /u01/hotback/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5552148)
.............................中间过程省略..........................
180316 13:46:38 [00] Copying /u01/hotback/incr1//xtrabackup_info to ./xtrabackup_info
180316 13:46:38 [00] ...done
180316 13:46:38 completed OK!8、准备第二次增量备份集；
[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/hotback/base --incremental-dir=/u01/hotback/incr2
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
incremental backup from 5552148 is enabled.
xtrabackup: cd to /u01/hotback/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5552578)
xtrabackup: using the following InnoDB configuration for recovery:
.............................中间过程省略..........................
InnoDB: 5.7.13 started; log sequence number 5552661
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: page_cleaner: 1000ms intended loop took 15159ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 5552680
180316 13:53:40 completed OK!
[root@db1 ~]# 注：xtrabackup --apply-log-only应被用在除最后一个增量的其他所有增量备份准备备份集中，即使用在最后一个，备份仍然处于一致性状态，但是这种情况下服务器会执行回滚。
压缩备份
1、执行压缩备份；
[root@db1 ~]# xtrabackup --backup -uxtrabk -pxtrabk --socket=/tmp/mysql.sock --compress --target-dir=/u01/compress_backup/
.............................中间过程省略..........................
180316 14:55:07 Executing UNLOCK TABLES
180316 14:55:07 All tables unlocked
180316 14:55:07 [00] Compressing ib_buffer_pool to /u01/compress_backup/ib_buffer_pool.qp
180316 14:55:07 [00] ...done
180316 14:55:07 Backup created in directory '/u01/compress_backup/'
MySQL binlog position: filename 'mysql-bin.000009', position '464'
180316 14:55:07 [00] Compressing /u01/compress_backup/backup-my.cnf.qp
180316 14:55:07 [00] ...done
180316 14:55:07 [00] Compressing /u01/compress_backup/xtrabackup_info.qp
180316 14:55:07 [00] ...done
xtrabackup: Transaction log of lsn (5553187) to (5553196) was copied.
180316 14:55:07 completed OK!注：如果想提高压缩的速度，可以使用--compress-threads选项。
2、查看压缩后的文件；
[root@db1 ~]# ls -lh /u01/compress_backup/
total 552K
-rw-r-----. 1 root root 416 Mar 16 14:55 backup-my.cnf.qp
-rw-r-----. 1 root root 792 Mar 16 14:55 ib_buffer_pool.qp
-rw-r-----. 1 root root 501K Mar 16 14:55 ibdata1.qp
drwxr-x---. 2 root root 4.0K Mar 16 14:55 mysql
drwxr-x---. 2 root root 4.0K Mar 16 14:55 performance_schema
drwxr-x---. 2 root root 12K Mar 16 14:55 sys
drwxr-x---. 2 root root 4.0K Mar 16 14:55 test
-rw-r-----. 1 root root 108 Mar 16 14:55 xtrabackup_binlog_info.qp
-rw-r-----. 1 root root 113 Mar 16 14:55 xtrabackup_checkpoints
-rw-r-----. 1 root root 501 Mar 16 14:55 xtrabackup_info.qp
-rw-r-----. 1 root root 294 Mar 16 14:55 xtrabackup_logfile.qp3、准备备份集；
--解压备份集
[root@db1 ~]# xtrabackup --decompress --target-dir=/u01/compress_backup/
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
180316 15:27:52 [01] decompressing ./xtrabackup_info.qp
180316 15:27:53 [01] decompressing ./backup-my.cnf.qp
180316 15:27:53 [01] decompressing ./ib_buffer_pool.qp
180316 15:27:53 [01] decompressing ./performance_schema/events_transactions_summary_by_user_by_event_name.frm.qp
.............................中间过程省略..........................
180316 15:28:13 completed OK!
--准备备份集
[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/compress_backup/
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /u01/compress_backup/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(5553187)
.............................中间过程省略..........................
InnoDB: 5.7.13 started; log sequence number 5553685
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: page_cleaner: 1000ms intended loop took 5152ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 5553704
180316 15:34:26 completed OK!注：使用 --decompress压缩的备份集在准备备份之前需要解压，解压工具是qpress。解压后的原文件不会被删除，可以使用--remove-original选项清除。
XtraBackup恢复
全备恢复
1、全备进行恢复前需要使用--prepare选项准备备份集；
[root@db1 ~]# xtrabackup --prepare --target-dir=/u01/hotback/
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /u01/hotback/
--如果已经准备过备份集，会提示已经prepared
xtrabackup: This target seems to be already prepared.
InnoDB: Number of pools: 1
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.2、执行全备恢复，数据库需要处于关机状态；
[root@db1 ~]# mysqladmin -uroot -p shutdown;
Enter password:
[root@db1 ~]# 3、还原备份之前，datadir目录必须是空的；
[root@db1 ~]# mv /usr/local/mysql/data /usr/local/mysql/data_bak
[root@db1 ~]# mkdir /usr/local/mysql/data4、执行还原全备；
[root@db1 ~]# xtrabackup --copy-back --target-dir=/u01/hotback/
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
180316 12:10:19 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0
180316 12:10:22 [01] ...done
180316 12:10:22 [01] Copying ib_logfile1 to /usr/local/mysql/data/ib_logfile1
.............................中间过程省略..........................
180316 12:10:27 [01] ...done
180316 12:10:27 completed OK!
注：也可以使用下面方式实现
[root@db1 ~]# rsync -avrP /u01/hotback/ /usr/local/mysql/data/5、修改data目录权限并启动MySQL服务；
[root@db1 ~]# chown -R mysql:mysql /usr/local/mysql/data
[root@db1 ~]# service mysql.server start
Starting MySQL.Logging to '/usr/local/mysql/data/db1.err'.
.... SUCCESS!
[root@db1 ~]#6、查看数据的恢复状态；
[root@db1 ~]# mysql -uroot -p test -e 'select *from t_emp';
Enter password:
+----+--------+---------------------+------+
| id | name | cdate | sex |
+----+--------+---------------------+------+
| 1 | 张三 | 2018-03-13 18:53:44 | 1 |
| 3 | 李四 | 2018-03-13 18:53:44 | 1 |
| 5 | Alen | 2018-03-13 17:37:34 | 1 |
| 7 | Lucy | 2018-03-13 17:39:45 | 0 |
| 9 | Hanmei | 2018-03-14 17:19:07 | 0 |
| 11 | 李磊 | 2018-03-14 17:19:07 | 1 |
| 13 | James | 2018-03-14 17:19:07 | 1 |
| 15 | 王五 | 2018-03-14 22:13:26 | 1 |
| 17 | Kim | 2018-03-14 22:13:26 | 1 |
| 19 | Eile | 2018-03-14 22:13:26 | 0 |
+----+--------+---------------------+------+
[root@db1 ~]# 7、通过分析Binlog，使数据库恢复到当前状态；
[root@db1 ~]# mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000007 --start-position=12260 | mysql -uroot -p test
Enter password:
[root@db1 ~]# mysql -uroot -p test -e 'select *from t_emp';
Enter password:
+----+-------------+---------------------+------+
| id | name | cdate | sex |
+----+-------------+---------------------+------+
| 1 | 张三 | 2018-03-13 18:53:44 | 1 |
| 3 | 李四 | 2018-03-13 18:53:44 | 1 |
| 5 | Alen | 2018-03-13 17:37:34 | 1 |
| 7 | Lucy | 2018-03-13 17:39:45 | 0 |
| 9 | Hanmei | 2018-03-14 17:19:07 | 0 |
| 11 | 李磊 | 2018-03-14 17:19:07 | 1 |
| 13 | James | 2018-03-14 17:19:07 | 1 |
| 15 | 王五 | 2018-03-14 22:13:26 | 1 |
| 17 | Kim | 2018-03-14 22:13:26 | 1 |
| 19 | Eile | 2018-03-14 22:13:26 | 0 |
| 21 | Full Backup | 2018-03-16 12:04:07 | 1 |
| 23 | Full Test | 2018-03-16 12:04:07 | 0 |
+----+-------------+---------------------+------+
[root@db1 ~]# 至此，全库恢复完成。
增量恢复
1、增量备份后插入几条记录作为当前环境；
mysql> insert into t_emp(name,sex)values('Test Recover',1),('Rec Incr',0);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select count(1) from t_emp;
+----------+
| count(1) |
+----------+
| 18 |
+----------+
1 row in set (0.04 sec)
mysql> 2、执行完增量备份准备备份集后进行恢复；
[root@db1 ~]# xtrabackup --copy-back --target-dir=/u01/hotback/base
xtrabackup version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
180316 14:25:52 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0
180316 14:25:52 [01] ...done
.............................中间过程省略..........................
180316 14:25:55 [01] Copying ./ib_buffer_pool to /usr/local/mysql/data/ib_buffer_pool
180316 14:25:55 [01] ...done
180316 14:25:55 completed OK!3、通过分析Binlog，使数据库恢复到当前状态；
[root@db1 ~]# mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000008 --start-position=1846 | mysql -uroot -p
Enter password:
[root@db1 ~]# mysql -uroot -p test -e 'select count(1) from t_emp';
Enter password:
+----------+
| count(1) |
+----------+
| 18 |
+----------+
[root@db1 ~]# 至此，增量恢复完成。
压缩恢复
和全备恢复过程一样，在此不做演示。
