高效MySQL数据库备份策略详解

时间：11-08 现代故事提交错误

本篇文章给大家谈谈高效MySQL数据库备份策略详解，以及对应的知识点，文章可能有点长，但是希望大家可以阅读完，增长自己的知识，最重要的是希望对各位有所帮助，可以解决了您的问题，不要忘了收藏本站喔。

1.快速可靠地完成备份

2. 备份期间不间断的事务处理

3.节省磁盘空间和网络带宽

4.自动验证备份文件

5、快速恢复，保证在线运行时间的持久性

percona-xtrabackup软件包包含两个工具，一个是xtrabackup，另一个是innobackupex。 innobackupex 是由per 封装的。备份innodb表时会自动调用xtraback工具，所以实际备份InnoDB表的是xtrabackup，xtrabackup这个工具只能备份innodb表。这是专门为innodb开发的热备份工具。对于myisam等其他引擎中的表，由innobackupex负责备份。如果是全备份加增量的方案，那么每个增量innobackupex工具都会对非innodb表进行全备份，并且会请求读锁。

xtrabackup在备份innodb表的时候，不再是简单的复制文件，而是通过innodb存储引擎层的新旧LSN（日志序列号）来标识这个数据页是否需要备份。

xtraback工具完美支持innodb引擎的真正热备份。备份数据中的数据文件和事务日志文件往往由于innodb缓存等因素与事务日志中的数据不一致。因此，在进行数据恢复时，需要重做事务日志中已提交的事务，并撤消未提交的事务。这就是做数据恢复时需要做的准备工作，即prepare。

2）Xtrabackup备份原理

1.InnoDB备份原理

InnoDB内部维护着一个重做日志文件，我们也可以称之为事务日志文件。事务日志存储InnoDB表数据的每条记录修改。当InnoDB启动时，InnoDB检查数据文件和事务日志并执行两个步骤：将已提交的事务日志应用（前滚）到数据文件，并回滚已修改但未提交的数据。

备份过程

Xtrabackup启动时会记住日志

序列号（LSN），并复制所有数据文件。复制过程需要一些时间，因此如果在此期间修改数据文件，数据库将处于不同的时间点。这时，xtrabackup会运行一个后台进程来监视事务日志，并从事务日志中复制最新的修改。 Xtrabackup必须继续做这个操作，因为事务日志会被轮转和重复写入，并且事务日志可以被重用。因此，自从xtrabackup启动以来，它就不断地在事务日志中记录每个数据文件的修改。

准备过程

以上就是xtrabackup的备份过程。接下来是准备过程。在此过程中，xtrabackup使用之前复制的事务日志对每个数据文件进行灾难恢复（就像MySQL刚启动时所做的那样）。当这个过程完成后，数据库就可以恢复了。

2.MyISAM的备份原理

上述过程是在xtrabackup编译好的二进制程序中实现的。 innobackupex 程序允许我们备份MyISAM 表和frm 文件，增加了便利性和功能性。 Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件，然后执行FLUSH TABLES WITH READ LOCK以防止新的写入并将MyISAM表数据刷新到硬盘，然后复制MyISAM数据文件，最后释放锁。

备份的MyISAM和InnoDB表最终将保持一致。准备过程完成后，InnoDB表数据已前滚到整个备份结束的点，而不是回滚到xtrabackup首次启动时的点。该时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，因此myisam表数据和InnoDB表数据是同步的。与Oracle类似，InnoDB的prepare过程可以称为recover，myisam的数据复制过程可以称为restore。

Xtrabackup 和innobackupex 这两个工具都提供了许多前面未提及的功能。说明书对每个功能都有详细的介绍。简单介绍一下，这些工具提供了流式备份、增量备份等，通过复制数据文件、复制日志文件、向数据文件提交日志（前滚）等方式实现各种复合备份方式。

3）、安装

1.使用yum方式安装

配置仓库

百胜安装http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm-y

检查仓库

百胜清单| grep 佩科纳

安装包

百胜安装percona-xtrabackup-24

2.使用rpm包安装

下载rpm包

wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

安装：

yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

3.安装后的目录结构

4）通讯方式

两个工具之间的交互和协调是通过控制文件的创建和删除来实现的。主要文件有：

xtrabackup_suspended_1

xtrabackup_suspended_2

xtrabackup_log_copied

作为示例，我们来看看xtrabackup_suspended_2 在备份过程中如何协调两个工具进程。

innobackupex启动xtrabackup进程后，会通过等待文件xtrabackup_suspended_2创建来等待xtrabackup完成InnoDB文件的备份；

xtrabackup准备好InnoDB数据后，会在指定目录下创建这个文件，然后等待innobackupex删除该文件；

innobackupex检测到文件xtrabackup_suspended_2创建后，继续；

innobackupex完成非InnoDB表的备份后，会删除xtrabackup_suspended_2文件，从而通知xtrabackup可以继续，然后等待xtrabackup_log_copied创建；

xtrabackup检测到xtrabackup_suspended_2文件被删除后，即可继续。

5）备份过程

innobackupex启动后，首先会fork一个进程，启动xtrabackup进程，然后等待xtrabackup备份ibd数据文件；

xtrabackup备份InnoDB相关数据时，有两种线程。一种是重做复制线程，负责复制重做文件。另一个是ibd复制线程，负责复制ibd文件。在ibd 复制线程之前只有一个重做复制线程。在ibd 线程结束后开始并结束。 xtrabackup进程开始执行后，首先启动redo复制线程，从最新的checkpoint点开始依次复制redo log；然后启动ibd数据复制线程。在xtrabackup复制ibd过程中，innobackupex进程一直处于等待状态（等待文件创建）。

xtrabackup完成复制idb后，通知innobackupex（通过创建文件）并进入等待模式（redo线程仍然继续复制）；

innobackupex收到xtrabackup通知后，执行FLUSH TABLES WITH READ LOCK（FTWRL）获取一致性点，然后开始备份非InnoDB文件（包括frm、MYD、MYI、CSV、opt、par等）。在复制非InnoDB文件的过程中，由于数据库处于全局只读状态，如果备份业务主数据库，必须特别小心。如果非InnoDB表（主要是MyISAM）较多，整个数据库的只读时间就会较长。必须评估这种影响。

当innobackupex复制完所有非InnoDB表文件后，通知xtrabackup（通过删除文件），同时进入等待（等待创建另一个文件）；

xtrabackup收到innobackupex非InnoDB备份完成的通知后，停止redo复制线程，然后通知innobackupex重做日志复制完成（通过创建文件）；

innobackupex收到重做备份完成通知后，开始解锁，执行UNLOCK TABLES；

最后，innobackupex和xtrabackup进程各自完成收尾工作，如释放资源、写入备份元数据信息等。innobackupex等待xtrabackup子进程完成后退出。

上述文件复制是通过备份过程直接通过操作系统读取的所有数据文件。它们仅在执行SQL命令时与数据库进行交互，基本不影响数据库的操作。备份非InnoDB时，会有一个只读期。（如果没有MyISAM表，只读时间大约是几秒）。备份InnoDB数据文件时，对数据库完全没有影响，是真正的热备份。

InnoDB和非InnoDB文件的备份都是通过复制文件来完成的，但实现方法不同。前者以页粒度完成（xtrabackup），后者通过cp或tar命令（innobackupex）完成。 xtrabackup读取每个页面创建时都会验证校验和值，以确保数据块一致。 Innobackupex在cp MyISAM文件时已经flush（FTWRL）了，磁盘上的文件也已经完整，所以最终备份集中的数据文件全部写入完整。

6）增量备份

PXB支持增量备份，但只能对InnoDB做增量备份。每个InnoDB 页都有一个LSN 号。 LSN 全局递增。当页面改变时，当前的LSN号将被记录。页面中的LSN越大，则该页面中的LSN号也越大。这意味着当前页面较新（最近更新）。每次备份都会记录当前备份的LSN（在xtrabackup_checkpoints文件中）。增量备份仅复制LSN大于上次备份的页面，并跳过LSN小于上次备份的页面。每个ibd文件的最终备份都是增量的。增量文件。

MyISAM没有增量机制。每个增量备份都是完整的副本。

增量备份过程与全量备份相同，只是ibd文件副本不同。

7）恢复过程

如果你查看恢复的备份集的日志，你会发现和mysqld启动时非常相似。事实上，备份集的恢复类似于mysqld崩溃后进行崩溃恢复。

恢复的目的是将备份集中的数据恢复到一致点。所谓一致性，是指原始数据库中各个引擎数据在某一时间点的状态。例如，MyISAM 中的数据对应的是15:00 时间点，而InnoDB 中的数据对应的是15:20，这种状态下的数据是不一致的。 PXB备份集对应的一致性点是备份时FTWRL的时间点，恢复的数据对应的是原数据库在FTWRL时的状态。

因为备份时FTWRL之后数据库是只读的，非InnoDB数据在持有全局读锁的情况下进行复制，所以非InnoDB数据本身对应的是FTWRL时间点； InnoDB的ibd文件复制是在FTWRL之前完成的。不同复制的ibd文件的最后更新时间点是不同的。该状态下的ibd文件不能直接使用，而是从备份中不断复制重做日志，最后的重做日志点是持有后面获得的FTWRL时，所以重做应用后最终的ibd数据时间点也与FTWRL一致。

因此，恢复过程只涉及InnoDB文件的恢复，非InnoDB数据不会被移动。备份恢复完成后，可以将数据文件复制到相应的目录下，然后通过mysqld启动。

8) 备份实例

1、全量备份

[root@linux-node2 备份]#xtrabackup --backup

--target-dir=/data/backups/$(日期+%F) -uroot -p123456

170427 10:46:41version_check 连接到MySQL 服务器，DSN"dbi:mysql:mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock"as "root"(使用密码： YES)。

170427 10:46:41version_check 连接到MySQL 服务器

170427 10:46:41version_check 正在对服务器执行版本检查.

170427 10:46:41version_check 完成。

170427 10:46:41 连接到MySQL服务器host: localhost，user: root，password: set，port: 3306，socket:/usr/local/mysql/data/mysqld.sock

使用服务器版本5.6.35-log

xtrabackup 版本2.4.7 基于MySQLserver 5.7.13 Linux (x86_64)（修订版id: 6f7a799）

xtrabackup: 使用posix_fadvise()。

xtrabackup: cd 到/usr/local/mysql/data

xtrabackup: 请求的打开文件限制65535，设置为1024000

xtrabackup: 使用以下InnoDBconfiguration:

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=536870912

InnoDB: 池数量： 1

170427 10:46:41 日志扫描最多(1743474)

xtrabackup: 生成表空间列表

InnoDB: 为mysql/slave_master_info 分配的表空间ID 15，旧最大值为0

170427 10:46:41 [01] 复制./ibdata1 到/data/backups/2017-04-27/ibdata1

170427 10:46:41 [01].完成

170427 10:46:41 [01]复制./mysql/slave_master_info.ibd到/data/backups/2017-04-27/mysql/slave_master_info.ibd

170427 10:46:41 [01].完成

170427 10:46:41 [01]复制./mysql/slave_relay_log_info.ibd到/data/backups/2017-04-27/mysql/slave_relay_log_info.ibd

170427 10:46:41 [01].完成

…………

170427 10:46:42 [01] 复制./test/db.opt 到/data/backups/2017-04-27/test/db.opt

170427 10:46:42 [01].完成

170427 10:46:42 完成备份非InnoDB 表和文件

170427 10:46:42 [00] 写入xtrabackup_binlog_info

170427 10:46:42 [00].完成

170427 10:46:42 正在执行FLUSHNO_WRITE_TO_BINLOG 引擎日志.

xtrabackup: 最新检查点（增量）: "1743474"

xtrabackup: 正在停止日志复制线程。

.170427 10:46:42 日志扫描至(1743474)

170427 10:46:43 执行解锁表

170427 10:46:43 所有表已解锁

170427 10:46:43 在目录"/data/backups/2017-04-27/"中创建备份

MySQL binlog位置：文件名"mysql-bin.000004"，位置"191"，最后更改的GTID"0e9896a7-14f7-11e7-a0e6-000c2900551e:1-3"

170427 10:46:43 [00] 写入backup-my.cnf

170427 10:46:43 [00].完成

170427 10:46:43 [00] 写入xtrabackup_info

170427 10:46:43 [00].完成

xtrabackup: lsn (1743474) 的事务日志已复制到(1743474)。

170427 10:46:43 Completed OK!#表示备份成功。

[root@linux-node2 备份]#

备份成功后，备份目录结构如下：

2.增量备份

[root@linux-node2 备份]# xtrabackup--backup --target-dir=/data/backups/increment/$(date +%F-%H-%M-%S)--incremental-basedir=/data /备份/2017-04-27/-uroot -p123456

170427 14:41:49version_check 连接到MySQL 服务器，DSN"dbi:mysql:mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock"as "root"(使用密码： YES)。

170427 14:41:49version_check 连接到MySQL 服务器

170427 14:41:49version_check 正在对服务器执行版本检查.

170427 14:41:49version_check 完成。

170427 14:41:49 连接到MySQL服务器host: localhost，user: root，password: set，port: 3306，socket:/usr/local/mysql/data/mysqld.sock

使用服务器版本5.6.35-log

xtrabackup 版本2.4.7 基于MySQLserver 5.7.13 Linux (x86_64)（修订版id: 6f7a799）

已启用从1743474 开始的增量备份。

xtrabackup: 使用posix_fadvise()。

xtrabackup: cd 到/usr/local/mysql/data

xtrabackup: 请求的打开文件限制65535，设置为1024000

xtrabackup: 使用以下InnoDBconfiguration:

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=536870912

InnoDB: 池数量： 1

170427 14:41:49 日志扫描最多(1752524)

xtrabackup: 生成表空间列表

InnoDB: 为mysql/slave_master_info 分配的表空间ID 15，旧最大值为0

xtrabackup: 使用全量扫描进行增量备份

170427 14:41:49 [01] 复制./ibdata1 到/data/backups/increment/2017-04-27-14-41-49/ibdata1.delta

170427 14:41:49 [01].完成

170427 14:41:49 [01]复制./mysql/slave_master_info.ibd到/data/backups/increment/2017-04-27-14-41-49/mysql/slave_master_info.ibd.delta

170427 14:41:49 [01].完成

170427 14:41:49 [01]复制./mysql/slave_relay_log_info.ibd到/data/backups/increment/2017-04-27-14-41-49/mysql/slave_relay_log_info.ibd.delta

…………

170427 14:41:50 [01] 复制./incrememtal1/t1.frm 到/data/backups/increment/2017-04-27-14-41-49/incrememtal1/t1.frm

170427 14:41:50 [01].完成

170427 14:41:50 已完成备份非InnoDB 表和文件

170427 14:41:50 [00] 写入xtrabackup_binlog_info

170427 14:41:50 [00].完成

170427 14:41:50 执行

ing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (forincremental): "1752524" xtrabackup: Stopping log copying thread. .170427 14:41:50 >>log scanned up to(1752524) 170427 14:41:50 Executing UNLOCK TABLES 170427 14:41:50 All tables unlocked 170427 14:41:50 Backup created in directory"/data/backups/increment/2017-04-27-14-41-49/" MySQL binlog position: filename"mysql-bin.000004", position "525", GTID of the last change"0e9896a7-14f7-11e7-a0e6-000c2900551e:1-5" 170427 14:41:50 [00] Writing backup-my.cnf 170427 14:41:50 [00]...done 170427 14:41:50 [00] Writing xtrabackup_info 170427 14:41:50 [00]...done xtrabackup: Transaction log of lsn (1752524)to (1752524) was copied. 170427 14:41:50 completed OK! 备份成功后的目录结构：查看xtrabackup_checkpoints文件，显示备份是从lsn 1743474到lsn 1752524，通过查看上一次全量备份目录的xtrabackup_checkpoints文件显示，最后一个lsn是1743474。 [root@linux-node22017-04-27-14-41-49]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 1743474 to_lsn = 1752524 last_lsn = 1752524 compact = 0 recover_binlog_info = 0 [root@linux-node2 2017-04-27-14-41-49]# 3、再次增量备份（基于上一次增量备份） [root@linux-node2 increment]# xtrabackup--backup --target-dir=/data/backups/increment/$(date +%F-%H-%M-%S)--incremental-basedir=/data/backups/increment/2017-04-27-14-41-49/ -uroot-p123456 170427 15:39:26version_check Connecting to MySQL server withDSN "dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock"as "root"(using password: YES). 170427 15:39:26version_check Connected to MySQL server 170427 15:39:26version_check Executing a version checkagainst the server... 170427 15:39:26version_check Done. 170427 15:39:26 Connecting to MySQL serverhost: localhost, user: root, password: set, port: 3306, socket:/usr/local/mysql/data/mysqld.sock Using server version 5.6.35-log xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799) incremental backup from 1752524 is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /usr/local/mysql/data xtrabackup: open files limit requested 65535,set to 1024000 xtrabackup: using the following InnoDBconfiguration: 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 = 536870912 InnoDB: Number of pools: 1 170427 15:39:26 >>log scanned up to(1758973) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 15 formysql/slave_master_info, old maximum was 0 xtrabackup: using the full scan forincremental backup 170427 15:39:26 [01] Copying ./ibdata1 to/data/backups/increment/2017-04-27-15-39-25/ibdata1.delta 170427 15:39:26 [01]...done …… 170427 15:39:26 [01] Copying./incrememtal2/t1.ibd to/data/backups/increment/2017-04-27-15-39-25/incrememtal2/t1.ibd.delta 170427 15:39:26 [01]...done 170427 15:39:27 >>log scanned up to(1758973) 170427 15:39:27 Executing FLUSHNO_WRITE_TO_BINLOG TABLES... 170427 15:39:27 Executing FLUSH TABLES WITHREAD LOCK... 170427 15:39:27 Starting to backup non-InnoDBtables and files 170427 15:39:27 [01] Copying./mysql/servers.frm to/data/backups/increment/2017-04-27-15-39-25/mysql/servers.frm 170427 15:39:27 [01]...done …… 170427 15:39:27 [01] Copying./incrememtal2/t1.frm to /data/backups/increment/2017-04-27-15-39-25/incrememtal2/t1.frm 170427 15:39:27 [01]...done 170427 15:39:27 Finished backing upnon-InnoDB tables and files 170427 15:39:27 [00] Writingxtrabackup_binlog_info 170427 15:39:27 [00]...done 170427 15:39:27 Executing FLUSH NO_WRITE_TO_BINLOGENGINE LOGS... xtrabackup: The latest check point (forincremental): "1758973" xtrabackup: Stopping log copying thread. .170427 15:39:27 >>log scanned up to(1758973) 170427 15:39:27 Executing UNLOCK TABLES 170427 15:39:27 All tables unlocked 170427 15:39:27 Backup created in directory"/data/backups/increment/2017-04-27-15-39-25/" MySQL binlog position: filename"mysql-bin.000004", position "859", GTID of the last change"0e9896a7-14f7-11e7-a0e6-000c2900551e:1-7" 170427 15:39:27 [00] Writing backup-my.cnf 170427 15:39:27 [00]...done 170427 15:39:27 [00] Writing xtrabackup_info 170427 15:39:27 [00]...done xtrabackup: Transaction log of lsn (1758973)to (1758973) was copied. 170427 15:39:27 completed OK! 查看备份完成后的目录结构： 4、准备恢复（preparing a backup）-基于全量备份在在使用备份文件恢复数据之前，你需要对备份的数据进行整理。具体原因在前面的内容已经介绍过，我们先看使用完全备份恢复 [root@linux-node2 2017-04-27-15-39-25]#xtrabackup --prepare --target-dir=/data/backups/2017-04-27/ xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799) xtrabackup: cd to /data/backups/2017-04-27/ xtrabackup: This target seems to be notprepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected:size=8388608, start_lsn=(1743474) xtrabackup: using the following InnoDBconfiguration 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 InnoDBconfiguration 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 forrecovery. xtrabackup: Using 104857600 bytes for bufferpool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomicbuiltins InnoDB: Uses event mutexes InnoDB: GCC builtin __atomic_thread_fence()is used for memory barrier InnoDB: Compressed tables use zlib 1.2.7 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size= 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of bufferpool InnoDB: page_cleaner coordinator priority:-20 InnoDB: Highest supported file format isBarracuda. InnoDB: The log sequence number 1732944 in thesystem tablespace does not match the log sequence number 1743474 in theib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Doing recovery: scanned up to logsequence number 1743474 (0%) InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002 InnoDB: Creating shared tablespace fortemporary tables InnoDB: Setting file "./ibtmp1" size to 12MB. Physically writing the file full; Please wait ... InnoDB: File "./ibtmp1" size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) areactive. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number1743474 InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002 xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 1743493 InnoDB: Number of pools: 1 xtrabackup: using the following InnoDBconfiguration 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 = 536870912 InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomicbuiltins InnoDB: Uses event mutexes InnoDB: GCC builtin __atomic_thread_fence()is used for memory barrier InnoDB: Compressed tables use zlib 1.2.7 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size= 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of bufferpool InnoDB: page_cleaner coordinator priority:-20 InnoDB: Setting log file ./ib_logfile101 sizeto 512 MB InnoDB: Progress in MB: 100200 300 400 500 InnoDB: Setting log file ./ib_logfile1 sizeto 512 MB InnoDB: Progress in MB: 100200 300 400 500

InnoDB: Renaming log file ./ib_logfile101 to./ib_logfile0 InnoDB: New log files created, LSN=1743493 InnoDB: Highest supported file format isBarracuda. InnoDB: Log scan progressed past thecheckpoint lsn 1743884 InnoDB: Doing recovery: scanned up to logsequence number 1743893 (0%) InnoDB: Doing recovery: scanned up to logsequence number 1743893 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002 InnoDB: Removed temporary tablespace datafile: "ibtmp1" InnoDB: Creating shared tablespace fortemporary tables InnoDB: Setting file "./ibtmp1" size to 12MB. Physically writing the file full; Please wait ... InnoDB: File "./ibtmp1" size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) areactive. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number1743893 xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: page_cleaner: 1000ms intended looptook 4725ms. The settings might not be optimal. (flushed=0 and evicted=0,during the time.) InnoDB: Shutdown completed; log sequencenumber 1743912 170427 15:52:06 completed OK! 如果已经进行过数据整理，再次运行会有如下提示信息： xtrabackup: This target seems to be alreadyprepared. xtrabackup: notice: xtrabackup_logfile wasalready used to "--prepare". 5、恢复备份（基于全量）首先我们需要所整理完数据后的备份目录内的文件复制到mysql的数据目录，复制的方法很多，可以使用xtrabackup –copy-back选项，或者—move-back选项，当然我们也可以使用rsync或者cp等命令。（注意在操作前应该先停止mysql服务，然后清空数据目录） [root@linux-node2 mysql]# mysqladmin -uroot-p123456 shutdown Warning: Using a password on the command lineinterface can be insecure. [root@linux-node2 mysql]# 170427 16:11:27mysqld_safe mysqld from pid file /usr/local/mysql/data/db01.pid ended [1]+Done/usr/local/mysql/bin/mysqld_safe(wd: ~) (wd now: /usr/local/mysql) [root@linux-node2 mysql]# mv /usr/local/mysql/data/tmp/ 完成后使用xtraback恢复数据 [root@linux-node2 mysql]# xtrabackup--copy-back --target-dir=/data/backups/2017-04-27/ xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799) 170427 16:13:43 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0 170427 16:13:46 [01]...done 170427 16:13:46 [01] Copying ib_logfile1 to/usr/local/mysql/data/ib_logfile1 170427 16:13:48 [01]...done ………… 170427 16:13:49 [01]...done 170427 16:13:49 [01] Copying ./xtrabackup_infoto /usr/local/mysql/data/xtrabackup_info 170427 16:13:49 [01]...done 170427 16:13:49 [01] Copying./xtrabackup_binlog_pos_innodb to/usr/local/mysql/data/xtrabackup_binlog_pos_innodb 170427 16:13:49 [01]...done 170427 16:13:49 [01] Copying ./ibtmp1 to/usr/local/mysql/data/ibtmp1 170427 16:13:49 [01]...done 170427 16:13:49 completed OK! 对恢复后的文件授权： [root@linux-node2 mysql]# chown -R mysql.mysql /usr/local/mysql/data/ 最后重启启动mysql服务 [root@linux-node2data]# Logging to "/usr/local/mysql/data/mysql-error.log". 17042716:15:08 mysqld_safe Starting mysqld daemon with databases from/usr/local/mysql/data [root@linux-node2data]# lsof -i :3306 COMMANDPIDUSERFDTYPE DEVICE SIZE/OFF NODE NAME mysqld4116 mysql13uIPv6260840t0TCP *:mysql (LISTEN) [root@linux-node2data]# mysql -uroot -p Enterpassword: Welcome tothe MySQL monitor.Commands end with ;or g. Your MySQLconnection id is 1 Serverversion: 5.6.35-log MySQL Community Server (GPL) Copyright(c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle isa 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 | |test| +--------------------+ 4 rows inset (0.00 sec) 6、基于增量备份进行恢复我们现在的备份情况如下： /data/backups/2017-04-27全备 [root@linux-node2 backups]# cat 2017-04-27/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1780797 last_lsn = 1780797 compact = 0 recover_binlog_info = 0 /data/backups/increment/2017-04-27-16-51-43第一次增备 [root@linux-node2 backups]# catincrement/2017-04-27-16-51-43/xtrabackup_checkpoints backup_type = incremental from_lsn = 1780797 to_lsn = 1788921 last_lsn = 1788921 compact = 0 recover_binlog_info = 0 /data/backups/increment/2017-04-27-16-52-18第二次增备 [root@linux-node2 backups]# catincrement/2017-04-27-16-52-18/xtrabackup_checkpoints backup_type = incremental from_lsn = 1788921 to_lsn = 1796758 last_lsn = 1796758 compact = 0 recover_binlog_info = 0 恢复过程如下：使用全备进行恢复准备操作， [root@linux-node2 backups]# xtrabackup--prepare --apply-log-only --target-dir=/data/backups/2017-04-27/ xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799) xtrabackup: cd to /data/backups/2017-04-27/ xtrabackup: This target seems to be notprepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected:size=8388608, start_lsn=(1780797) xtrabackup: using the following InnoDBconfiguration 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 InnoDBconfiguration 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 forrecovery. xtrabackup: Using 104857600 bytes for bufferpool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomicbuiltins InnoDB: Uses event mutexes InnoDB: GCC builtin __atomic_thread_fence()is used for memory barrier InnoDB: Compressed tables use zlib 1.2.7 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size= 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of bufferpool InnoDB: page_cleaner coordinator priority:-20 InnoDB: Highest supported file format isBarracuda. InnoDB: The log sequence number 1761000 inthe system tablespace does not match the log sequence number 1780797 in theib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Doing recovery: scanned up to logsequence number 1780797 (0%) InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002 InnoDB: xtrabackup: Last MySQL binlog fileposition 1169, file name master-bin.000002 xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 1780806 InnoDB: Number of pools: 1 170427 16:55:24 completed OK! 合并第一次增备 [root@linux-node2 backups]# xtrabackup--prepare --apply-log-only --target-dir=/data/backups/2017-04-27/--incremental-dir=/data/backups/increment/2017-04-27-16-51-43/ 合并第二次增备： [root@linux-node2 backups]# xtrabackup--prepare --target-dir=/data/backups/2017-04-27/ --incremental-dir=/data/backups/increment/2017-04-27-16-52-18/ 合并完成后，使用上面第五步的方法恢复数据库。 9）压缩备份（compressed backup） XtraBackup支持压缩备份 1、创建Compressed Backups [root@linux-node2 backups]# xtrabackup--backup --compress --target-dir=/data/backups/compressed/$(date +%F) -uroot-p123456 170427 17:28:03version_check Connecting to MySQL server withDSN "dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/data/mysqld.sock"as "root"(using password: YES). 170427 17:28:03version_check Connected to MySQL server 170427 17:28:03version_check Executing a version checkagainst the server... 170427 17:28:03version_check Done. 170427 17:28:03 Connecting to MySQL serverhost: localhost, user: root, password: set, port: 3306, socket:/usr/local/mysql/data/mysqld.sock Using server version 5.6.35-log xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /usr/local/mysql/data xtrabackup: open files limit requested 65535,set to 1024000 xtrabackup: using the following InnoDBconfiguration: 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 = 536870912 InnoDB: Number of pools: 1 170427 17:28:03 >>log scanned up to(1796758) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 15 formysql/slave_master_info, old maximum was 0 170427 17:28:04 [01] Compressing ./ibdata1 to/data/backups/compressed/2017-04-27/ibdata1.qp 170427 17:28:04 [01]...done ………… 170427 17:28:05 [01] Compressing./inc2/t1.frm to /data/backups/compressed/2017-04-27/inc2/t1.frm.qp 170427 17:28:05 [01]...done 170427 17:28:05 Finished backing upnon-InnoDB tables and files 170427 17:28:05 [00] Compressingxtrabackup_binlog_info 170427 17:28:05 [00]...done 170427 17:28:05 Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (forincremental): "1796758" xtrabackup: Stopping log copying thread. .170427 17:28:05 >>log scanned up to(1796758) 170427 17:28:05 Executing UNLOCK TABLES 170427 17:28:05 All tables unlocked 170427 17:28:05 Backup created in directory"/data/backups/compressed/2017-04-27/" MySQL binlog position: filename"mysql-bin.000005", position "1761", GTID of the last change "0e9896a7-14f7-11e7-a0e6-000c2900551e:1-17" 170427 17:28:05 [00] Compressingbackup-my.cnf 170427 17:28:05 [00]...done 170427 17:28:05 [00] Compressingxtrabackup_info 170427 17:28:05 [00]...done xtrabackup: Transaction log of lsn (1796758)to (1796758) was copied. 170427 17:28:05 completed OK! 备份完成后目录结构如下 [root@linux-node2 backups]# llcompressed/2017-04-27/ total 248 -rw-r----- 1 root root400 Apr 27 17:28 backup-my.cnf.qp -rw-r----- 1 root root 223722 Apr 27 17:28ibdata1.qp drwxr-x--- 2 root root54 Apr 27 17:28 inc1 drwxr-x--- 2 root root54 Apr 27 17:28 inc2 drwxr-x--- 2 root root54 Apr 27 17:28 incrememtal1 drwxr-x--- 2 root root54 Apr 27 17:28 incrememtal2 drwxr-x--- 2 root root4096 Apr 27 17:28 mysql drwxr-x--- 2 root root4096 Apr 27 17:28 performance_schema drwxr-x--- 2 root root22 Apr 27 17:28 test -rw-r----- 1 root root152 Apr 27 17:28 xtrabackup_binlog_info.qp -rw-r----- 1 root root113 Apr 27 17:28 xtrabackup_checkpoints -rw-r----- 1 root root560 Apr 27 17:28 xtrabackup_info.qp -rw-r----- 1 root root381 Apr 27 17:28 xtrabackup_logfile.qp [root@linux-node2 backups]# 2、恢复准备preparing（数据整理）准备整理数据前需要先解压备份数据，使用—decompress选项，如果出现如下错误，请安装qpress，可以使用yum安装。 [root@linux-node2 backups]# xtrabackup--decompress --target-dir=/data/backups/compressed/2017-04-27/ xtrabackup version 2.4.7 based on MySQLserver 5.7.13 Linux (x86_64) (revision id: 6f7a799) 170427 17:34:54 [01] decompressing./xtrabackup_logfile.qp sh: qpress: command not found cat: write error: Broken pipe Error: thread 0 failed. [root@linux-node2 backups]# xtrabackup --decompress--target-dir=/data/backups/compressed/2017-04-27/ xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64)(revision id: 6f7a799) 170427 17:35:45 [01] decompressing ./xtrabackup_logfile.qp 170427 17:35:45 [01] decompressing ./ibdata1.qp ………… 170427 17:35:46 [01] decompressing ./xtrabackup_binlog_info.qp 170427 17:35:46 [01] decompressing ./backup-my.cnf.qp 170427 17:35:46 [01] decompressing ./xtrabackup_info.qp 170427 17:35:46 completed OK!

关于高效MySQL数据库备份策略详解到此分享完毕，希望能帮助到您。