MySQL备份与恢复

最新推荐文章于 2023-06-20 16:08:39 发布

以梦为马不负韶华

最新推荐文章于 2023-06-20 16:08:39 发布

阅读量248

点赞数

分类专栏：运维文章标签： mysql 存储数据

本文链接：https://blog.csdn.net/beanewself/article/details/78576406

版权

运维专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一简介

备份：即存储数据副本；
恢复：把副本应用到线上系统，仅能恢复至备份操作时刻的数据状态，恢复时可以结合二进制日志进行时间点恢复：

二备份类型

1 根据备份的数据集的范围分为完全备份和部分备份
完全备份：整个数据集；
部分备份：数据集的一部分，比如部分表；
2 全量备份、增量备份、差异备份：
全量备份：即完全备份
增量备份：仅备份自上一次完全备份或增量备份以来变量的那部数据；
差异备份：仅备份自上一次完全备份以来变量的那部数据；
3 物理备份、逻辑备份：
物理备份：复制数据文件进行的备份；
逻辑备份：从数据库导出数据另存在一个或多个文件中；
4 根据数据服务是否在线：
热备：读写操作均可进行的状态下所做的备份；
温备：可读但不可写状态下进行的备份；
冷备：读写操作均不可进行的状态下所做的备份；

三备份工具

1 mysqldump

（1）简介：

逻辑备份工具：基于mysql客户端协议
完全备份、部分备份；
支持对InnoDB存储引擎进行热备或温备，对MyISAM存储引擎进行温备；

（2）用法:

            mysqldump [OPTIONS] database [tables]   # 备份单库，可以只备份其中的一部分表（部分备份）；
            mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  # 备份多库；
            mysqldump [OPTIONS] --all-databases [OPTIONS] # 备份所有库；
            [OPTIONS]：
            -x, --lock-all-tables：锁定所有库的所有表，读锁；
            -l, --lock-tables：锁定指定库所有表；
            --single-transaction：创建一个事务，基于此快照执行备份；
            -R, --routines：备份指定库的存储过程和存储函数；
            --triggers：备份指定库的触发器；
            -E, --events：备份时间驱动器；
             --master-data[=#]
                1：记录为CHANGE MASTER TO语句，此语句不被注释；
                2：记录为CHANGE MASTER TO语句，此语句被注释；

            --flush-logs：锁定表完成后，即进行日志刷新操作；

利用mysqldump做全量备份，在全量备份周期内（即未到下一次全量备份）变化的数据可以通过二进制日志来记录和恢复，但是怎么知道从二进制日志的哪个位置开始重放呢？备份时加上–master-data参数，该参数可以将备份那一时刻二进制日志处在哪个日志文件的哪个位置记录到备份下来的sql脚本中，格式为CHANGE MASTER TO=位置（最好设置–master-data=2，即注释掉该语句，否则恢复时该语句也被执行了），加上–flush-logs参数表示备份的那一刻启用一个新的文件记录二进制日志。恢复时先用备份下来的sql脚本恢复全量备份的那部分数据，然后再用该二进制日志恢复在全量备份周期内的变化的数据。

2 Xtrabackup

（1）简介

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

备份过程快速、可靠；
备份过程不会打断正在执行的事务；
能够基于压缩等功能节约磁盘空间和流量；
自动实现备份检验；
还原速度快；

（2）安装

其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得。

（3）备份的实现

使用xtrabackup进行备份时，服务一般处于运行状态
使用xtrabackup进行恢复时，服务必须处于关闭状态
若为热备，则在备份的一瞬间，有些事务可能正处于已提交但未同步到文件的状态，有些事务可能处于未提交状态。进行恢复时，若恢复所用的备份不是最后一次备份的数据，即后面还有增量备份或者进行时间点还原的二进制日志，那么恢复时要加上–apply-log、–redo-only选项，只将处于已提交但未同步到文件状态的事务提交，对于处于未提交状态的事务则不做处理，因为后面还有增量备份或者进行时间点还原的二进制日志，在增量备份期间或二进制日志中可能会对处于未提交状态的事务进行提交，所以若擅自回滚，可能会导致后面的增量备份或者进行时间点还原的二进制日志中得到一个不完整的事务；若恢复所用的备份为最后一次备份的数据，即后面没有增量备份或者进行时间点还原的二进制日志，那么恢复时只需加上–apply-log选项，将处于已提交但未同步到文件状态的事务提交，将处于未提交状态的事务回滚，因为后面没有增量备份或者进行时间点还原的二进制日志，不可能会对处于未提交状态的事务进行提交了，所以为了保证事务的ACID特性，将处于未提交状态的事务回滚。

1）完全备份

 innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/

2）准备(prepare)一个完全备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：

 innobackupex --apply-log  /path/to/BACKUP-DIR

如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407 09:01:40 innobackupex: completed OK!

在实现“准备”的过程中，innobackupex通常还可以使用–use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

3）从一个完全备份中恢复数据

注意：恢复不用启动MySQL

innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

innobackupex --copy-back  /path/to/BACKUP-DIR

如果执行正确，其输出信息的最后几行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in ‘/backup/2012-04-07_08-17-03’
innobackupex: back to original InnoDB log directory ‘/mydata/data’
innobackupex: Finished copying back files.

120407 09:36:10 innobackupex: completed OK!

请确保如上信息的最行一行出现“innobackupex: completed OK!”。

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：

chown -R  mysql:mysql  /mydata/data/

4）使用innobackupex进行增量备份

每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

要实现第一次增量备份，可以使用下面的命令进行：

 innobackupex --incremental /backup --incremental-basedir=BASEDIR

其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：
(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
(2)基于所有的备份将未提交的事务进行“回滚”。

于是，操作就变成了：

 innobackupex --apply-log --redo-only BASE-DIR

接着执行：

 innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二个增量：

 innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；

最后一个增量命令完成后，要在全量备份上做“回滚”操作：

 innobackupex --apply-log BASE-DIR

下面将使用Xtrabackup进行一次全量备份、两次增量备份，然后利用该备份进行恢复，并利用二进制日志进行时间点还原：
在主机名为localhost的节点进行备份，然后在主机名为node2的节点进行数据恢复。
这里写图片描述

–user定义备份的账户
–password定义备份时的密码
–host定义备份的目标主机
如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

图中表示root用户对本机数据库做备份，备份后的文件保存在/mydata/backups目录下，未加其他参数，默认备份所有库，默认进行全量备份
这里写图片描述
出现completed OK表示备份完成，备份范围（日志序列号）从0到2021722

备份后在生成了一个以当前时间为文件名的文件夹

这里写图片描述

这里写图片描述
自全量备份后进行了插入和查询操作

–incremental进行增量或差异备份
–incremental-basedir指明基于哪次全量或增量进行备份
root用户基于上次全量备份进行备份，可以看做是增量备份，也可以看做是差异备份
这里写图片描述
备份范围（日志序列号）到2022161

备份后在生成了一个以当前时间为文件名的文件夹,进入第一次增量备份文件夹：使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。
(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；

(5)backup-my.cnf —— 备份命令用到的配置选项信息；
这里写图片描述
该文件记录了当前备份的属性：
类型为增量备份
备份范围（日志序列号）从2021722到2022161，即从全量备份的结束位置开始进行增量备份。

这里写图片描述
自第一次增量备份后进行了删除和查询操作

root用户基于上次增量备份进行备份，是增量备份

这里写图片描述

这里写图片描述
自第二次增量备份后进行了更新和查询操作，此时假设服务器崩溃，即未来得及做第三次增量备份

进入第二次增量备份文件夹，该文件记录了本次备份时二进制日志所处的文件及位置，可以利用此后记录的二进制日志进行时间点恢复，即利用这部分二进制日志恢复第二次增量备份后的数据
这里写图片描述

这里写图片描述

这里写图片描述
mysqlbinlog读二进制日志的工具
-j 指明从二进制日志的哪个位置开始读
将二进制日志重定向为sql脚本

node2
模拟一个未进行任何初始化（服务不要启动，也不要初始化，数据目录下应没有任何内容）的数据库
这里写图片描述
因为后面还有增量备份或者进行时间点还原的二进制日志，在增量备份期间或二进制日志中可能会对处于未提交状态的事务进行提交，所以加上–apply-log、–redo-only选项，只将处于已提交但未同步到文件状态的事务提交，对于处于未提交状态的事务则不做处理
表示对当前文件夹（即全量备份生成的文件夹）进行操作：将处于已提交但未同步到文件状态的事务提交
这里写图片描述

这里写图片描述
因为后面还有增量备份或者进行时间点还原的二进制日志，在增量备份期间或二进制日志中可能会对处于未提交状态的事务进行提交，所以加上–apply-log、–redo-only选项，只将处于已提交但未同步到文件状态的事务提交，对于处于未提交状态的事务则不做处理
–incremental-basedir指明增量备份文件夹，必须使用绝对路径？
对第一次增量备份文件夹进行操作：只将处于已提交但未同步到文件状态的事务提交。然后将第一次增量备份合并到当前文件夹（即全量备份生成的文件夹）
这里写图片描述

这里写图片描述
因为后面还有增量备份或者进行时间点还原的二进制日志，在增量备份期间或二进制日志中可能会对处于未提交状态的事务进行提交，所以加上–apply-log、–redo-only选项，只将处于已提交但未同步到文件状态的事务提交，对于处于未提交状态的事务则不做处理
–incremental-basedir指明增量备份文件夹
对第二次增量备份文件夹进行操作：只将处于已提交但未同步到文件状态的事务提交。然后将第二次增量备份合并到当前文件夹（即全量备份生成的文件夹）
这里写图片描述

这里写图片描述
后面还有进行时间点还原的二进制日志，在二进制日志中可能会对处于未提交状态的事务进行提交，所以不应该加上–apply-log、–redo-only选项，只将处于已提交但未同步到文件状态的事务提交，对于处于未提交状态的事务则不做处理才对吗？若做了回滚，在第二次增量备份后，可能又对处于未提交状态的事务进行提交了，即记入到二进制日志中了，那拿这个二进制日志恢复的时候，不就无法得到一个完整的事务了？本实验中在第二次增量备份时，没有处于未提交状态的事务，所以对本实验来说结果无影响？
这里写图片描述