使用XtraBackup 备份MySQL数据库

共计 16283 个字符，预计需要花费 41 分钟才能阅读完成。

本次测试使用 XtraBackup 备份 MySQL 数据库

版本：XtraBackup2.4.5+MySQL5.7.16

下载地址:https://www.percona.com/downloads/XtraBackup/

1、安装 XtraBackup

本次为了方便，使用解压版本进行安装，直接解压就可以用了。

使用过程中可能会遇到缺少 perl 依赖包的问题，我的方法是直接操作把镜像包里的 perl 都安装了，yum install -y perl*

2、简介

XtraBackup 主要包括两个备份工具 xtrabackup 和 innobackupex。其中 innobackupex 对 xtrabackup 进行了封装。本次只介绍 innobackupex 备份 MySQL

3、全量备的操作方法

1)创建备份

# 创建存储备份数据的目录
[root@rhel7 ~]# mkdir /mysqlbackup
# 开始备份
[root@rhel7 ~]# innobackupex –user=root –password=123456 /mysqlbackup
161213 12:27:13 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

161213 12:27:13 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘root’ (using password: YES).
Failed to connect to MySQL server: DBI connect(‘;mysql_read_default_group=xtrabackup’,’root’,…) failed: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) at – line 1314.
161213 12:27:13 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null)
Using server version 5.7.16
innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: uses posix_fadvise().
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
161213 12:27:13 >> log scanned up to (2671643)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
161213 12:27:13 [01] Copying ./ibdata1 to /mysqlbackup/2016-12-13_12-27-13/ibdata1
161213 12:27:14 >> log scanned up to (2671643)
161213 12:27:15 [01] …done
161213 12:27:15 >> log scanned up to (2671643)
161213 12:27:15 [01] Copying ./mysql/plugin.ibd to /mysqlbackup/2016-12-13_12-27-13/mysql/plugin.ibd
161213 12:27:15 [01] …done
……
161213 12:27:16 [01] Copying ./wl/zx.ibd to /mysqlbackup/2016-12-13_12-27-13/wl/zx.ibd
161213 12:27:16 [01] …done
161213 12:27:16 >> log scanned up to (2671643)
161213 12:27:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES…
161213 12:27:16 Executing FLUSH TABLES WITH READ LOCK…
161213 12:27:16 Starting to backup non-InnoDB tables and files
161213 12:27:16 [01] Copying ./mysql/db.opt to /mysqlbackup/2016-12-13_12-27-13/mysql/db.opt
161213 12:27:16 [01] …done
……
161213 12:27:18 [01] Copying ./wl/wl.frm to /mysqlbackup/2016-12-13_12-27-13/wl/wl.frm
161213 12:27:18 [01] …done
161213 12:27:18 [01] Copying ./wl/zx.frm to /mysqlbackup/2016-12-13_12-27-13/wl/zx.frm
161213 12:27:18 [01] …done
161213 12:27:18 Finished backing up non-InnoDB tables and files
161213 12:27:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘2671634’
xtrabackup: Stopping log copying thread.
.161213 12:27:18 >> log scanned up to (2671643)

161213 12:27:18 Executing UNLOCK TABLES
161213 12:27:18 All tables unlocked
161213 12:27:18 [00] Copying ib_buffer_pool to /mysqlbackup/2016-12-13_12-27-13/ib_buffer_pool
161213 12:27:18 [00] …done
161213 12:27:18 Backup created in directory ‘/mysqlbackup/2016-12-13_12-27-13’
161213 12:27:18 [00] Writing backup-my.cnf
161213 12:27:18 [00] …done
161213 12:27:18 [00] Writing xtrabackup_info
161213 12:27:18 [00] …done
xtrabackup: Transaction log of lsn (2671634) to (2671643) was copied.
161213 12:27:19 completed OK!
# 在指定的目录下生成了一个时间目录
[root@rhel7 ~]# ls -l /mysqlbackup/
total 4
drwxr-x—. 7 root root 4096 Dec 13 12:27 2016-12-13_12-27-13
[root@rhel7 ~]# du -sm /mysqlbackup/*
90 /mysqlbackup/2016-12-13_12-27-13

注意：innobackupex 会去读取 my.cnf 文件中的 [mysqld] 和[xtrabackup]部分，也可以使用 –defaults-file 指定参数文件。

2)恢复全备的数据

备份的数据不能直接使用，需要先做 prepare 操作(可以理解为应用日志的过程，commit 的事务提交，未 commit 的事务回滚)

# 使用 –apply-log 应用日志，
[root@rhel7 ~]# innobackupex –apply-log /mysqlbackup/2016-12-13_12-27-13/
161213 12:36:24 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints “completed OK!”.

innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: cd to /mysqlbackup/2016-12-13_12-27-13/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2671634)
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
InnoDB: PUNCH HOLE support not available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2671634
InnoDB: Doing recovery: scanned up to log sequence number 2671643 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 2671643 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 2671643

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 2671671
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
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: PUNCH HOLE support not available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2671671
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2672140
InnoDB: Doing recovery: scanned up to log sequence number 2672149 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 2672149 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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: Waiting for purge to start
InnoDB: 5.7.13 started; log sequence number 2672149
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 2672168
161213 12:36:30 completed OK!
# 查看 prepare 后数据目录的大小
[root@rhel7 ~]# ls -l /mysqlbackup/
total 4
drwxr-x—. 7 root root 4096 Dec 13 12:36 2016-12-13_12-27-13
[root@rhel7 ~]# du -sm /mysqlbackup/*
206 /mysqlbackup/2016-12-13_12-27-13

prepare 完成后恢复数据到原 MySQL 的数据目录

注意：MySQL 的数据目录必须为空，MySQL 服务也必须停止，否则恢复会报错(除非恢复部分备份)

# 创建一个测试库 zx
[root@rhel7 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| wl |
+——————–+
6 rows in set (0.10 sec)

mysql> create database zx;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
# 停止 MySQL 数据库并创建新的 data 目录
[root@rhel7 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@rhel7 ~]# cd $MYSQL_HOME
[root@rhel7 mysql]# ls
bin COPYING data docs include lib man mysql-test README share support-files
[root@rhel7 mysql]# mv data data_bak
[root@rhel7 mysql]# mkdir data
# 恢复数据
[root@rhel7 mysql]# innobackupex –copy-back /mysqlbackup/2016-12-13_12-27-13/
161213 13:03:50 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints “completed OK!”.

innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
161213 13:03:50 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0
161213 13:03:50 [01] …done
161213 13:03:51 [01] Copying ib_logfile1 to /usr/local/mysql/data/ib_logfile1
161213 13:03:51 [01] …done
161213 13:03:52 [01] Copying ibdata1 to /usr/local/mysql/data/ibdata1
161213 13:03:54 [01] …done
161213 13:03:55 [01] Copying ./mysql/plugin.ibd to /usr/local/mysql/data/mysql/plugin.ibd
161213 13:03:55 [01] …done
161213 13:03:55 [01] Copying ./mysql/servers.ibd to /usr/local/mysql/data/mysql/servers.ibd
161213 13:03:55 [01] …done
161213 13:03:55 [01] Copying ./mysql/help_topic.ibd to /usr/local/mysql/data/mysql/help_topic.ibd
161213 13:03:55 [01] …done
161213 13:03:55 [01] Copying ./mysql/help_category.ibd to /usr/local/mysql/data/mysql/help_category.ibd
161213 13:03:55 [01] …done
…….
161213 13:03:57 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1
161213 13:03:57 [01] …done
161213 13:03:57 completed OK!
# 修改 data 目录下的权限
[root@rhel7 mysql]# chown mysql:mysql -R data
# 启动 MySQL 数据库查看恢复是否成功
[root@rhel7 mysql]# service mysqld start
Starting MySQL. SUCCESS!
[root@rhel7 mysql]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| wl |
+——————–+
6 rows in set (0.00 sec)
# 数据恢复成功，测试数据库 zx 不见了。

4. 增量备份的操作步骤

1)创建备份，增量备份需要有一个全量备份做基础，所以先做一个全量备份再做增量备份

# 创建全备
[root@rhel7 mysql]# innobackupex –user=root –password=123456 /mysqlbackup/
[root@rhel7 mysql]# ls -l /mysqlbackup/
total 4
drwxr-x—. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48
# 删除一个测试库
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| wl |
| zx |
+——————–+
7 rows in set (0.00 sec)

mysql> drop database zx;
Query OK, 0 rows affected (0.07 sec)
# 创建增量备份(基于全备)
[root@rhel7 mysql]# innobackupex –user=root –password=123456 –incremental /mysqlbackup/ –incremental-basedir=/mysqlbackup/2016-12-13_13-10-48/
……
[root@rhel7 mysql]# ls -l /mysqlbackup/
total 8
drwxr-x—. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48
drwxr-x—. 7 root root 4096 Dec 13 13:16 2016-12-13_13-16-17
[root@rhel7 mysql]# du -sm /mysqlbackup/*
90 /mysqlbackup/2016-12-13_13-10-48
3 /mysqlbackup/2016-12-13_13-16-17
# 删除一个测试库
mysql> drop database wl;
Query OK, 0 rows affected (0.07 sec)
# 创建增量备份(基于上次增量备份)
[root@rhel7 mysql]# innobackupex –user=root –password=123456 –incremental /mysqlbackup/ –incremental-basedir=/mysqlbackup/2016-12-13_13-16-17/
……
[root@rhel7 mysql]# ls -l /mysqlbackup/
total 12
drwxr-x—. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48
drwxr-x—. 7 root root 4096 Dec 13 13:16 2016-12-13_13-16-17
drwxr-x—. 6 root root 4096 Dec 13 13:18 2016-12-13_13-18-30
[root@rhel7 mysql]# du -sm /mysqlbackup/*
90 /mysqlbackup/2016-12-13_13-10-48
3 /mysqlbackup/2016-12-13_13-16-17
4 /mysqlbackup/2016-12-13_13-18-30

2)恢复增量备份

同全量备份一样恢复里也需要 prepare

# 全量备份的 prepare –redo-only 表示只提交 commit 的事务
[root@rhel7 mysql]# innobackupex –apply-log –redo-only /mysqlbackup/2016-12-13_13-10-48/
# 第一次增量备份 prepare
[root@rhel7 mysql]# innobackupex –apply-log –redo-only /mysqlbackup/2016-12-13_13-10-48/ –incremental-dir=/mysqlbackup/2016-12-13_13-16-17/
# 最后一次增量备份 prepare 不再需要 redo-only 参数
[root@rhel7 mysql]# innobackupex –apply-log /mysqlbackup/2016-12-13_13-10-48/ –incremental-dir=/mysqlbackup/2016-12-13_13-18-30/
# 停止 MySQL 服务创建新的 data 目录
[root@rhel7 mysql]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@rhel7 mysql]# rm -rf data
[root@rhel7 mysql]# mkdir data
# 恢复数据
[root@rhel7 mysql]# innobackupex –copy-back /mysqlbackup/2016-12-13_13-10-48/
# 修改 data 目录权限
[root@rhel7 mysql]# chown mysql:mysql -R data
# 启动 MySQL 服务并验证
[root@rhel7 mysql]# service mysqld start
Starting MySQL… SUCCESS!
[root@rhel7 mysql]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| wl |
+——————–+
6 rows in set (0.00 sec)

mysql> show tables in wl;
Empty set (0.00 sec)
# 测试库 zx 没有了，wl 库还在，但是库里的表已经没有了。

前边只是提供了简单的恢复方法，如果数据库备份之后运行了一段时间，数据库宕机了，如果只恢复备份的数据那就会丢失一部分数据。那这部分数据要怎么恢复呢。答案就是应用 binlog。

在启用 binlog 的数据库中，备份完成后日志中会输出关于 binlog 的信息，生成的备份目录中也会有一个 xtrabackup_binlog_info 文件保存备份时的 binlog 位置。

161214 10:24:34 Executing UNLOCK TABLES
161214 10:24:34 All tables unlocked
161214 10:24:34 [00] Copying ib_buffer_pool to /mysqlbackup//2016-12-14_10-24-27/ib_buffer_pool
161214 10:24:34 [00] …done
161214 10:24:34 Backup created in directory ‘/mysqlbackup//2016-12-14_10-24-27’
#binlog 位置
MySQL binlog position: filename ‘mysql-bin.000001’, position ‘154’
161214 10:24:34 [00] Writing backup-my.cnf
161214 10:24:34 [00] …done
161214 10:24:34 [00] Writing xtrabackup_info
161214 10:24:34 [00] …done
xtrabackup: Transaction log of lsn (2677865) to (2677874) was copied.
161214 10:24:34 completed OK!

[root@rhel7 2016-12-14_10-24-27]# ls -l
total 77876
-rw-r—–. 1 root root 425 Dec 14 10:24 backup-my.cnf
-rw-r—–. 1 root root 307 Dec 14 10:24 ib_buffer_pool
-rw-r—–. 1 root root 79691776 Dec 14 10:24 ibdata1
drwxr-x—. 2 root root 4096 Dec 14 10:24 mysql
drwxr-x—. 2 root root 8192 Dec 14 10:24 performance_schema
drwxr-x—. 2 root root 8192 Dec 14 10:24 sys
drwxr-x—. 2 root root 87 Dec 14 10:24 test
-rw-r—–. 1 root root 21 Dec 14 10:24 xtrabackup_binlog_info
-rw-r—–. 1 root root 113 Dec 14 10:24 xtrabackup_checkpoints
-rw-r—–. 1 root root 469 Dec 14 10:24 xtrabackup_info
-rw-r—–. 1 root root 2560 Dec 14 10:24 xtrabackup_logfile
# 记录 binlog 位置的文件
[root@rhel7 2016-12-14_10-24-27]# cat xtrabackup_binlog_info
mysql-bin.000001 154