【MySQL技术专题「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）

检索binlog文件内容

按照位置截取binlog内容

按照位置截取 binlog 内容的优点是可以实现精确的截取，但是在选择位置时可能会花费一些时间。例如，如果要截取 "backup-bin.000009" 文件从位置 365 到位置 465 的日志，可以使用以下命令：

mysqlbinlog backup-bin.000009 --start-position=365 --stop-position=465 -r pos.sql

注意要点

• 起始位置必须存在于 binlog 中，而结束位置点可以不存在。
• 如果指定了起始位置而没有指定结束位置，则会截取从起始位置到文件结尾的所有 binlog 日志：

mysqlbinlog backup-bin.000009 --start-position=365 -r pos.sql

• 如果指定了结束位置而没有指定起始位置，则会截取从文件开头到指定结束位置的所有 binlog 日志：

mysqlbinlog backup-bin.000009 --stop-position=465 -r pos.sql

在这里，位置点指的是 mysqlbinlog 解析文件时每一行开头的 "#at 数字" 标识的数据。通过指定起始位置和结束位置，可以根据具体需求来截取所需要的 binlog 内容。

优化后的内容如下：

按照位置截取 binlog 内容的优点是可以实现精确的截取，但是在选择位置时可能会花费一些时间。例如，如果要截取 "dadong-bin.000009" 文件从位置 365 到位置 465 的日志，可以使用以下命令：

[root@DB02 ~]# mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=465 -r pos.sql

案例分析

以下是一些示例，演示了如何根据不同的条件来截取部分 binlog：

按位置截取 binlog

mysqlbinlog backup-bin.000009 --start-position=365 --stop-position=456 -r pos.sql
mysqlbinlog backup-bin.000005 --start-position=2265 --stop-position=2552 -r pos.sql
mysqlbinlog backup-bin.000009 --start-position=365 --stop-position=456 -r pos.sql
mysqlbinlog backup-bin.000009 --start-position=365 -r pos.sql
mysqlbinlog backup-bin.000009 --stop-position=456 -r pos.sql

按时间截取 binlog

mysqlbinlog backup-bin.000009 --start-datetime='2022-10-16 17:14:15' --stop-datetime='2022-10-16 17:15:15' -r time.sql
mysqlbinlog backup-bin.000009 --start-datetime='2022-10-16 17:14:15'  -r time.sql
mysqlbinlog backup-bin.000009  --stop-datetime='2022-10-16 17:15:15' -r time.sql

注意：

• 使用上述命令时，替换 backup-bin.000009 中的文件名为实际的文件名。
• 对于按位置截取，可以指定起始位置和/或结束位置来截取指定的 binlog 内容。
• 对于按时间截取，可以指定起始时间和/或结束时间来截取在指定时间范围内的 binlog 内容。

根据具体需求，在 --start-position、--stop-position、--start-datetime 和 --stop-datetime 参数中填入正确的值，以实现所需的截取操作。

mysqlbinlog -F 是一个用于解析和显示 MySQL 二进制日志的命令。-F 参数用于指定二进制日志文件的格式。

根据MySQL文档，-F 参数可以有以下几个选项：

• --base64-output=value：设置输出格式为Base64编码的二进制日志。
• --hexdump：以十六进制格式显示二进制日志内容。
• --skip-gtids：忽略GTID (全局事务标识符)。

请根据您的具体需求选择适当的 -F 参数，并将其附加到 mysqlbinlog 命令后面使用。

MySQLbinlog命令常用参数：

通过binlog恢复数据

执行增量恢复

在新的MySQL实例上执行增量恢复操作。假设要从位置"binlog_file"，偏移量"binlog_position"开始进行恢复，执行以下命令：

 mysqlbinlog --start-position=binlog_position binlog_file | mysql -h hostname -u username -p

• 替换以下参数：
  - binlog_file：要恢复的二进制日志文件名。
  - binlog_position：要恢复的二进制日志文件的起始位置。
  - hostname：数据库服务器的主机名。
  - username：具有创建和修改表结构权限的MySQL用户。

• 验证数据恢复：

  • 在新的MySQL实例上查询和验证恢复的数据是否正确。

xtrabackup安装部署

xtrabackup

Percona为MySQL、PostgreSQL和MongoDB提供免费的开源软件。有关具体细节，请访问各个产品页面，在那里您还可以找到我们所有主要开源软件产品的当前文档。

官方网站：https://www.percona.com/downloads

下载和安装

普通下载安装

下载 RPM 安装包

运行以下命令下载 Percona XtraBackup 的 RPM 安装包：

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.3/binary/redhat/6/x86_64/percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

安装依赖

运行以下命令安装 Percona XtraBackup 的依赖项：

yum install -y perl-DBD-MySQL per-DBI perl-Time-HiRes libaio*

安装 RPM 包

运行以下命令安装 Percona XtraBackup 的 RPM 包：

rpm -ivh percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

可能会出现以下错误：

warning: percona-xtrabackup-2.3.3-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature,key ID cd2efd2a: NOKEY
error: Faild dependencies:
lidev.so4()(64bit) is needed by percona-xtrabackup-2.3.3-1.el6.x86_64

安装 libev.so()(64bit)

访问以下地址：rpmfind.net/linux/RPM/i… ，搜索 libev.so.4()(64bit), 下载相应的文件。

然后运行以下命令安装 libev.so()(64bit)：

rpm -ivh libev-4.04-2.e16.x86_64.rpm

安装 XtraBackup

最后运行以下命令完成安装：

rpm -ivh percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

安装过程会显示如下信息：

Preparing...             ########################################### [100%] 
1:percona-xtrabackup     ########################################### [100%] 

以下是优化后的内容：安装方法二：

使用 yum 安装

安装 Percona 源

运行以下命令安装 Percona 源：

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

使用 yum 安装 Percona XtraBackup

运行以下命令使用 yum 安装 Percona XtraBackup：

yum -y install percona-xtrabackup

Xtrabackup常用参数：

备份与恢复

全量备份

在这里我们采用的是innodb数据库引擎进行处理和操作。在这里我们使用的是innobackupex工具。

innobackupex

innobackupex 是一个备份和恢复 InnoDB 存储引擎的工具，是 Percona XtraBackup 工具包的一部分。它可以用于创建完整的物理备份，并支持增量备份和恢复操作。

下面是 innobackupex 命令的一些常用参数：

• --user=USER：指定备份用户，如果未指定，默认使用当前系统用户。
• --password=PASSWD：指定备份用户密码。
• --host=HOST：指定备份的主机，可以是远程数据库服务器。
• --port=PORT：指定数据库端口。
• --defaults-file=FILE：指定 MySQL 配置文件。
• --databases=DBLIST：指定要备份的数据库列表，多个数据库之间以逗号分隔。
• --incremental：执行增量备份。
• --incremental-basedir=DIR：指定上一次增量备份的目录路径，用于增量备份操作。
• --apply-log：对备份进行日志应用，用于回滚操作或增量备份合并操作。
• --copy-back：将备份数据复制回原始位置。

环境准备

• 完全备份目录：/data/backup/full。
• 每次成功完成命令操作时，日志结尾处会打印标志【completed OK!】，用于区分完全备份和增量备份。

备份操作

innobackupex --user=root --password /data/backup/full

介绍说明

上述命令用于在 /data/backup/full 目录下生成一个名为【yyyy-MM-dd_HH-mm-ss】的文件夹。

然而，此备份通常不能用于恢复，因为备份中可能存在尚未提交的事务或已提交的事务但尚未同步至数据文件的事务，导致数据文件处于不一致状态。

因此，我们需要执行一些操作来回滚未提交的事务并将已提交的事务同步至数据文件，以确保数据文件处于一致状态。
执行以下命令：

innobackupex --user=root --password --defaults-file=/data/mysql/my.cnf --apply-log /data/backup/full/yyyy-MM-dd_HH-mm-ss

其中参数 --apply-log 用于启动恢复过程。

恢复操作

关闭数据库，备份原数据，创建新的数据目录

停止MySQL服务

[root@mysql]# /data/3306/mysql stop

移动原数据目录至备份目录

[root@mysql]# mv /data/3306/data/ /data/3306/data_bak

创建新的数据目录

[root@mysql]# mkdir /data/3306/data

注意：恢复全备份必须恢复到一个空目录中，否则会出现错误。

执行innobackupex恢复命令

[root@mysql]# innobackupex --defaults-file=/data/3306/my.cnf --user=xxx--password=xxxx--copy-back /data/backup/full/yyyy-MM-dd_HH-mm-ss

对新目录进行授权，此操作需要在innobackupex恢复命令后执行

chmod -R mysql.mysql /data/mysql/data

重启服务，并检查数据是否成功恢复。

[root@mysql]# /data/3306/mysql start

启动MySQL服务...

[root@mysql]# ps -ef|grep 3306

增量备份

环境准备

环境备份目录说明：

• 全量备份目录：/data/backup/full
• 增量备份目录1：/data/backup/inc1
• 增量备份目录2：/data/backup/inc2

以下是备份命令示例：

全量备份：

innobackupex --defaults-file=/data/3306/my.cnf --user=root --password=123456 /data/backup/full

第一次增量备份

innobackupex --defaults-file=/data/3306/my.cnf --user=root --password=123456 --incremental /data/backup/inc1 --incremental-basedir=/data/backup/full/yyyy-MM-dd_HH-mm-ss

其中，--incremental-basedir 参数指定了完全备份所在的目录

全量备份和增量备份相互结合，可以实现尽可能保证数据的一致性和完整性。

注意事项

• innobackupex命令执行后，在 /data/backup 目录下会创建一个以时间命名的新目录，用于存放所有增量备份数据。

• 如果进行多次增量备份，请确保每次增量备份的 --incremental-basedir 参数指向上一次增量备份的目录。

• 增量备份仅适用于 InnoDB 或 XtraDB 表，对于 MyISAM 表，执行增量备份实际上是进行完全备份的操作。

第二次增量备份

innobackupex  --defaults-file=/data/3306/my.cnf  --user=root --password=123456 --incremental /data/backup/inc2  --incremental-basedir=/data/backup/inc1/yyyy-MM-dd_HH-mm-ss

如果需要恢复的话需要先执行如下操作

innobackupex --apply-log --redo-only /data/backup/full/yyyy-MM-dd_HH-mm-ss
innobackupex --apply-log --redo-only /data/backup/full/yyyy-MM-dd_HH-mm-ss --incremental-dir=/data/backup/inc1/yyyy-MM-dd_HH-mm-ss

再次重申一下：如果存在多次增量备份的话，就多次执行如下命令。此处执行针对的是第二次增量备份

执行恢复命令

innobackupex --defaults-file=/data/3306/my.cnf --user=root --password=123456 --copy-back  /data/backup/full/yyyy-MM-dd_HH-mm-ss

指定使用 --databases 参数可以备份指定的数据库。

案例分析

示例1：

innobackupex --default-file=/data/3306/my.cnf --user-root --password-123456 --databases="backup" /data/backup/

示例2：

innobackupex --default-file=/data/3306/my.cnf --user=root --password=12345 --databases="backup" /data/backup/

指定使用 --stream 参数进行备份，并将备份数据压缩成压缩包。

示例：

innobackupex --default-file=/data/3306/my.cnf --user=root --password=123456 --stream=tar /backup/full/ | gzip > /backup/full/back_$(date +%F).tar.gz

请注意，以上示例中的参数和路径应根据实际情况进行调整。