MySQLMySQL相关MySQL好难啊

MySQL 备份精编版

2019-03-08  本文已影响221人  运维开发_西瓜甜

一、备份概述

为何要备份

备份的目的

备份的种类

逻辑备份方法具有以下特征：

在线与离线备份

在MySQL服务器运行时进行联机备份，以便可以从服务器获取数据库信息。服务器停止时会发生脱机备份。这种区别也可以描述为“ 热 ”与 “ 冷 ”备份; 一个“ 温暖 ”的备份是一个在服务器保持运行，但锁定，以防止当你从外部访问数据库文件修改数据。

二、MySQL 逻辑备份 mysqldump

逻辑备份特点

在日常工作中，我们会使用 mysqldump 命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。

特点
  1. 自动记录position位置。
show master  status\G;
  1. 可用性，一致性

锁表机制

用法
mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql

/*查看帮助*/
mysqldump --help

日常用法

备份所有库

// 先配置用户名和密码
shell> vi ~/.mysql_user
[mysqldump]
user=root
password=123

shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

备份指定的多个库

// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数
// --defaults-file=~/.mysql_user -hip
shell> mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql

备份指定库的指定几个表

shell> mysqldump db1 t1 t3 t7 > dump.sql

备份时不锁表

备份时希望转储和刷新日志到恰好在同一时刻发生，适用于 InnoDB 引擎

shell> mysqldump  --all-databases --single-transaction --flush-logs > `date +%FT%H_%M_%S`dump_all.sql

--flush-logs 在开始备份数据之前刷新MySQL服务器日志文件。
此选项需要 RELOAD权限。如果将此选项与选项结合使用 --all-databases，则会为每个转储的数据库刷新日志。会锁表。

--single-transaction 是针对 InnoDB 引擎的表，不锁表，也称热备。

其他参数

优势

mysqldump的优势：

  1. 可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。
  2. 不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
  3. 不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复。
恢复
shell> mysql < dump.sql

或者，在mysql中，使用 source命令：

mysql> source dump.sql

如果文件是不包含CREATE DATABASE和 USE语句的单数据库转储 ，请首先创建数据库（如有必要）：

shell> mysqladmin create db1

然后在加载转储文件时指定数据库名称：

shell> mysql db1 < dump.sql

或者，在mysql中创建数据库，将其选为默认数据库，然后加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql>source dump.sql

Example

shell> mysql --defaults-file=~/.mysql_user < /backup/2016-12-08-04-mysql-all.sql

三、MySQL 物理备份： Innobackupex 和 xtrabackup（热备）

Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本上InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupex 。

原来的版本

（1）xtrabackup 只能备份InnoDB和XtraDB两种数据引擎的数据表，而不能备份MyISAM数据表

（2）innobackupex 则封装了 xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁。

新版本的变化

如果你安装了2.3之前版本的xtrabackup，那么在备份的过程中，你可能会用到两个常用的备份工具。

安装2.3版本之前的XtraBackup后，我们会得到两个主要的备份工具：

xtrabackup是一个C程序。

innobackupex是一个perl脚本，它对xtrabackup这个C程序进行了封装，在备份innodb表时，此脚本会调用xtrabackup这个C程序。

如果使用xtrabackup这个C程序进行备份，则只能备份innodb和xtradb的表，不能备份myisam表。

如果使用innobackupex进行备份，则可以备份innodb或xtradb的表，同时也能够备份myisam表。

所以，一般在使用XtraBackup备份工具进行数据备份时，通常会选择使用innobackupex命令进行备份。

那么问题来了。

xtrabackup是一个C程序，innobackupex是一个perl脚本，当它们作为两个进程运行时，总是没有特别完美的方式让它们进行通讯，当它们作为一个整体进行工作时就不太尽如人意，如此情况，就导致了一些bug的出现，于是，官方决定使用C重写innobackupex，将它与xtrabackup这个C程序完美的整合在一起。这个想法在2.3版本的XtraBackup中实现。

官方手册解释

xtrabackup
一个已编译的C二进制文件，它提供了使用MyISAM，InnoDB和XtraDB表备份整个MySQL数据库实例的功能

而我们安装的就是2.4版本，此时，innobackupex的功能已经完全整合到了xtrabackup中，innobackupex不再是perl脚本了，但是，为了兼容之前用户的使用习惯，官方保留了innobackupex，它作为一个软连接，指向了xtrabackup，也就是说，在2.4版本中，不管我们使用innobackupex命令，还是xtrabackup命令，其实使用的都是这个xtrabackupC程序。虽然在实现上有所不同，但是在工作原理上，与之前的版本并没有什么不同。

下面我们用新的命令xtrabachup使用。

首先我们先来简单的了解一下xtrabackup 是怎么工作的。xtrabackup 基于innodb的crash-recovery（实例恢复）功能，先copy innodb的物理文件（这个时候数据的一致性是无法满足的），然后进行基于redo log进行恢复，达到数据的一致性。

第一个安装方式： 使用 YUM 方式安装

地址
https://www.percona.com/downloads/XtraBackup/LATEST/

  1. 确保安装 EPEL 源
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
  1. 安装 libev
    为了成功安装Percona XtraBackup libev包需要先安装。
yum  install -y libev
  1. 安装Percona存储库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
  1. 测试存储库的可用性
shell> yum list | grep percona

应该输出如下信息

...
percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64
...
  1. 安装软件
shell> yum install percona-xtrabackup-24

  1. 验证安装


    image.png

第一种安装方式：下载对应版本的软件包，在本地安装

点击 下载页面，选择对应版本后进行下载

image.png

示例：

下载 2.4.4版本

wget https://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

注意：像这样手动安装软件包时，您需要确保解决所有依赖项并自行安装缺少的软件包。

卸载
yum remove percona-xtrabackup

80 版本安装方法

注意：
这个版本只支持 MySQL8.0的数据进行备份，不支持 MySQL8.0版本之前的数据进行备份。

# 安装仓库文件
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 启用仓库
percona-release enable-only tools release

#安装软件
yum install percona-xtrabackup-80

报错解决

来自 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona 的无效 GPG 密钥：No key found in given key data

或者

源 "CentOS 7 - Percona" 的 GPG 密钥已安装，但是不适用于此软件包。请检查
源的公钥 URL 是否配置正确。


 失败的软件包是：Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64
 GPG  密钥配置为：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

修改仓库文件 percona-release.repo 不使用密钥认证

image.png

再次安装会看到如下报错信息


Transaction check error:
  file /etc/my.cnf from install of Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64 conflicts with file from package mysql-community-server-5.7.25-1.el7.x86_64

错误概要
-------------

需要安装如下软件

yum install -y   mysql-community-libs-compat

注意：
这个软件的源是 mysql57-community

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 文件内容如下：

点我传送门

  1. 再执行安装命令
yum install percona-xtrabackup-24
日常操作

条件：

  1. 在 MySQL 服务器本地安装 Xtrbackup 并执行相关操作。
  2. 给执行备份到用户进行相应的授权。

配置选项

配置选项可以在sh命令行中直接使用，也可以在 my.cnf 文件中配置

# my.cnf 文件的配置
[xtrabackup]
target_dir = /data/backups/mysql/   # 备份数据放置的位置

假如是编译安装的 mysql ，需要在配置文件my.cnf 中指定 socket 文件的路径。

[xtrabackup]
socket = /tmp/mysql.sock

1. 全备

下面的命令均假设没有在 my.cnf 中配置任何关于 xtrabackup 的选项

要执行备份需要指定 备份数据放置的位置，就是目录，假如目录不存在，则会自动创建；==注意这个目录不会被递归创建，仅仅会创建最后一级目录；==假如存在，就会直接开始备份，并且不会覆盖原来的数据。

  1. 开始备份
shell> xtrabackup --backup --user=root --password='123'  --target-dir=/backups/full

# 备份完成后，可以看到备份时的LSN号，当下次进行增量备份时，xtrabackup就只备份大于此号的page即可。
  1. 查看备份文件
[root@mysql-master ~]# ls -lh /backups/full
总用量 13M
-rw-r----- 1 root root  487 8月  18 09:44 backup-my.cnf
-rw-r----- 1 root root  293 8月  18 09:44 ib_buffer_pool
-rw-r----- 1 root root  12M 8月  18 09:44 ibdata1
drwxr-x--- 2 root root 4.0K 8月  18 09:44 mysql
drwxr-x--- 2 root root   88 8月  18 09:44 one_db
drwxr-x--- 2 root root 8.0K 8月  18 09:44 performance_schema
drwxr-x--- 2 root root   58 8月  18 09:44 shark_db
drwxr-x--- 2 root root 8.0K 8月  18 09:44 sys
-rw-r----- 1 root root  115 8月  18 09:44 xtrabackup_checkpoints
-rw-r----- 1 root root  446 8月  18 09:44 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月  18 09:44 xtrabackup_logfile

进入目录后，可以看到一些目录，这些目录与我们数据库的名称相同，没错，这些就是各个数据库的数据文件备份目录。

还有一个innodb的共享表空间文件，ibdata1，注意，如果想要使用xtrabackup备份众多数据库中的某一个，那么必须保证在创建这个数据库时，已经开启了innodb_file_per_table 参数，否则将无法单独备份数据库服务器中的某一个数据库。

除了刚才描述的这些数据文件，xtrabackup还为我们生成了一些文件，我们来看看这些文件都有什么用（不同版本的xtrabackup生成的文件可能不同）。

  1. backup-my.cnf
    此文件中包含了my.cnf中的一些设置信息，但是，并不是my.cnf中的所有信息都会包含在此文件中，此文件中只包含了备份时需要的信息。

  2. xtrabackup_binlog_info
    需要开启二进制日志
    此文件中记录了备份开始时二进制日志文件的"位置(position)"

  3. xtrabackup_checkpoints
    此文件中记录此次备份属于那种类型的备份，是全量还是增量，备份时起始的LSN号码，结束的LSN号码等信息。

  4. xtrabackup_info
    本次备份的概要信息，此文件中的信息还是比较全面的。

  5. xtrabackup_logfile
    记录了备份过程中的日志，在对数据进行prepare时需要通过日志将数据还原成一致的可用的数据。

  1. 准备恢复的数据

使用 xtrabackup --backup 选项进行备份后，并不能直接使用，首先需要准备它以便还原它。

如果您尝试使用这些数据文件启动 InnoDB，它将检测损坏并自行崩溃，以防止您在损坏的数据上运行。

因为备份出的数据是不一致的，我们需要将同时备份出的事务日志应用到备份中，才能得到一份完整、一致、可用的数据，xtrabackup称这一步操作为prepare，直译过来就是"准备"。

xtrabackup --prepare 步骤使文件在同一时刻内的数据完全一致

shell> xtrabackup --prepare --target-dir=/backups/full

如果你要备份的数据量巨大，那么备份时长会变长，期间备份的事务日志容量有可能会很大。那么，我们可以使用--use-memory选项，加速准备工作的完成，在不指定内存大小的情况下，准备工作默认会占用100MB的内存，如果服务器有一定的空闲内存，那么我们可以让xtrabackup使用指定大小的内存完成准备工作，以提升准备工作完成的速度，示例语句如下。

shell> xtrabackup --prepare --use-memory=512M --target-dir=/backups/full

==准备备份时不建议中断xtrabackup进程，因为这可能会导致数据文件损坏，备份将无法使用。如果准备过程中断，则无法保证备份有效性。==

准备备份数据完成后，应该会看到如下信息。

InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596200
180818 10:09:19 completed OK!

恢复

xtrabackup 在执行copyback时会读取数据库的my.cnf中的配置，但是如果my.cnf中没有配置datadir，那么--datadir选项必须存在，而且，datadir目录必须为空目录，其中不能存在数据，否则在执行上述命令时会报错，--copy-back选项对应的目录就是我们准备好的可用数据的目录。

为了能够正常的恢复数据，我们先确定数据库服务已经停止了，而且对应的数据目录中不存在数据，然后进行数据还原工作，删除数据目录中的文件与日志。

  1. 停止数据库的服务
  2. 清理环境
  3. 修改权限
  4. 启动数据库
shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/*

shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full

# 下面为完成后的输出结果
180818 10:59:25 [01]        ...done
180818 10:59:25 completed OK!
shell> chown  mysql.mysql -R /var/lib/mysql

或者使用 rsync 命令

shell> rsync -avrP /data/backup/ /var/lib/mysql/
shell> chown  mysql.mysql -R /var/lib/mysql

启动数据库

shell> systemctl start mysqld.service

innobackuper 命令实现

shell> innobackupex --defaults-file=/etc/my.cnf --host=192.168.1.146 --user=root --password=123 /backups/full

shell> nnobackupex --apply-log --use-memory=4G /backup/full/2018-08-17_15-53-11

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/*

shell> innobackupex --datadir=/var/lib/mysql --copy-back 2018-08-17_15-53-11

shell> chown  mysql.mysql -R /var/lib/mysql

shell> systemctl start mysqld.service

全量备份思路总结

  1. 执行备份命令
    • 指定 数据库的用户名和密码
    • 指定 备份目录，注意只可以自动创建最后一级的目录
  2. 准备备份的数据
    • 就是指： --prepare 参数, 保证数据的统一且完整性
  3. 停服务，并且把 mysql 的数据目录下的所有文件和文件夹清除。
    • /var/lib/mysql/ 此目录必须是空的
  4. 恢复数据
    • 本质上就是拷贝备份的文件到指定的 mysql 数据目录下
  6. 修改 mysql 数据目录的属主和属组为 MySQL 服务器进程启动的用户，默认是 mysql
  7. 启动服务

2. 增量备份

image.png

特点：每次备份，都对自上一次备份（注意是上一次，不是第一次）到此时备份之间有变化的文件，进行备份。所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时持续的时间长。

无论xtrabackup和innobackupex工具支持增量备份，这意味着它们可以只复制自上次备份以来发生变化的数据。

您可以在每个完整备份之间执行许多增量备份，因此您可以设置备份过程，例如每周一次完整备份和每天增量备份，或每天完整备份和每小时增量备份。

增量备份有效，因为每个InnoDB页面都包含一个日志序列号或LSN。该LSN是整个数据库系统的版本号。每个页面的LSN显示它最近的更改。

当我们做过全量备份以后会在目录下产生xtrabackup_checkpoints的文件 这里面记录了lsn和备份方式，我们可以基于这次的全量做增量的备份。

shell> cat /data/backups/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 13593159
last_lsn = 13593168
compact = 0
recover_binlog_info = 0

增量备份实际上并不将数据文件与先前备份的数据文件进行比较。事实上，如果你知道它的LSN，你可以使用 xtrabackup --incremental-lsn来执行增量备份，而不需要先前的备份。增量备份只是读取页面并将其LSN与最后一个备份的LSN进行比较。但是，您仍需要完整备份来恢复增量更改;如果没有完整备份作为基础，增量备份将毫无用处。

创建增量备份

要进行增量备份，请像往常一样以完整备份开始, 使用下面的命令创建基础的全量备份。

shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/base

现在您已拥有完整备份，以后可以根据它进行增量备份。

向数据库中添加数据，以便于测试

mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
|     99213 |
+-----------+
1 row in set (0.04 sec)

mysql> insert into shark_db.student (name,age,phone) values('xiguatian',20,13149876789);
Query OK, 1 row affected (0.00 sec)

mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
|     99214 |
+-----------+
1 row in set (0.03 sec)

使用以下命令进行增量备份：

shell> xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/inc1 --incremental-basedir=/backups/base

该/data/backups/inc1/目录现在应包含增量文件

ls -lh /data/backups/inc1/
总用量 116K
-rw-r----- 1 root root  487 8月  18 11:40 backup-my.cnf
-rw-r----- 1 root root  293 8月  18 11:40 ib_buffer_pool
-rw-r----- 1 root root  64K 8月  18 11:40 ibdata1.delta
-rw-r----- 1 root root   44 8月  18 11:40 ibdata1.meta
drwxr-x--- 2 root root 4.0K 8月  18 11:40 mysql
drwxr-x--- 2 root root  144 8月  18 11:40 one_db
drwxr-x--- 2 root root 8.0K 8月  18 11:40 performance_schema
drwxr-x--- 2 root root   88 8月  18 11:40 shark_db
drwxr-x--- 2 root root 8.0K 8月  18 11:40 sys
-rw-r----- 1 root root  120 8月  18 11:40 xtrabackup_checkpoints
-rw-r----- 1 root root  498 8月  18 11:40 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月  18 11:40 xtrabackup_logfile

这个时候去查看增量备份的xtrabackup_checkpoints,会发现同样也记录了LSN 等信息

shell> cat /backups/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13596423
to_lsn = 13596628
last_lsn = 13596637
compact = 0
recover_binlog_info = 0
// 这也意味着你可以在增量的备份上继续增量的备份。

from_lsn是备份的起始LSN，对于增量，它必须与前一个/基本备份的to_lsn（如果它是最后一个检查点）相同。

上面的情况是，to_lsn （上一个检查点LSN）和last_lsn（上次复制的LSN）之间存在差异，这意味着在备份过程中服务器上存在一些流量

模拟增加数据

mysql> insert into shark_db.student (name,age,phone) values('xiguatian3',21,13149876789);
Query OK, 1 row affected (0.01 sec)

mysql> select count(id) from shark_db.student;                                     +-----------+
| count(id) |
+-----------+
|     99215 |
+-----------+
1 row in set (0.03 sec)

现在可以使用此目录作为另一个增量备份的基础：

shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1

准备增量备份的数据

增量备份的步骤与完全备份的步骤不同。在完全备份中，执行两种类型的操作以使数据库保持一致：从日志文件中针对数据文件重播已提交的事务，并回滚未提交的事务。在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行中，并且很可能它们将在下一次增量备份中提交。您应该使用该 选项来阻止回滚阶段.

xtrabackup --apply-log-only

警告

如果不使用该 选项来阻止回滚阶段，那么增量备份将毫无用处。回滚事务后，无法应用进一步的增量备份。

要准备数据，需要从一开始就准备，现在回想一下我们都有那些备份

/backups/base
/backups/inc1
/backups/inc2
image.png

  1. 准备基础备份的数据

    shell> xtrabackup --prepare --apply-log-only \
--target-dir=/backups/base
...省略...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596441
InnoDB: Number of pools: 1
180818 11:56:55 completed OK!

注意:
即使已跳过回滚阶段，此备份实际上也可以按原样恢复。如果你恢复它并启动MySQL，InnoDB将检测到没有执行回滚阶段，它将在后台执行，因为它通常用于启动时的崩溃恢复。它会通知您数据库未正常关闭。

  1. 把第一次增量备份的数据合并到基础备份的数据中
shell> xtrabackup  --prepare  --apply-log-only  --user=root --password=123  --target-dir=/backups/base  --incremental-dir=/backups/inc1
  1. 再把第二次增量备份的数据也合并到基础备份的数据中
shell> xtrabackup --prepare --user=root --password=123  --target-dir=/backups/base  --incremental-dir=/backups/inc2

==注意： 最后一次操作不需要加 --apply-log-only 参数==

  1. 停止 MySQL 服务，并删除数据目录和日志
shell> systemctl stop mysqld
shell> rm -rf /var/lib/mysql/*
  1. 开始恢复合并后的全部数据的数据库
shell> xtrabackup --copy-back  --datadir=/var/lib/mysql  --target-dir=/backups/base/
  1. 更改数据库目录的权限并启动数据库
shell> chown mysql.mysql -R /var/lib/mysql
shell> systemctl start mysqld
启用压缩备份
  1. 压缩备份使用 --compress 
// 压缩备份
shell> xtrabackup --backup --compress  --target-dir=/backups/compressed 

// 可以同时启用 4 个线程进行压缩
shell> xtrabackup --backup --compress  --compress-threads=4    --target-dir=/backups/compressed/

  1. 准备恢复数据

    首先要解压备份文件

安装依赖的软件

shell>  wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0"http://www.quicklz.com/qpress-11-linux-x64.tar
shell> tar xvf qpress-11-linux-x64.tar
shell> cp qpress /usr/bin

使用压缩

shell> xtrabackup  --decompress   --target-dir=/backups/compressed/

// --parallel 参数可以和 --decompress 参数一起使用，可以实现同时解压多个文件

解压后就可以进行准备恢复的数据操作了

shell> xtrabackup  --prepare --target-dir=/backups/compressed/
  1. 恢复数据并更改文件权限
shell> xtrabackup --copy-back --target-dir=/backups/full
shell> chown -R mysql:mysql /var/lib/mysql
创建加密备份

要创建加密的备份需要指定至少两个选项：

  1. 产生一个加密密钥
[root@mysql-master /]# openssl rand -base64 24
2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP

// 或者创建一个密钥文件
shell> echo -n "2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP" > 
 /data/backups/keyfile

在某些情况下，文本文件可能包含CRLF，这将导致密钥大小增大，从而使其无效。建议的方法是使用以下命令创建文件：

echo -n  "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /backups/keyfile
  1. 创建加密的数据备份
xtrabackup --backup \
--target-dir=/backups \
--encrypt = AES256 \
--encrypt-key="2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP"
或者
--encrypt-key-file=/data/backups/keyfile
  1. 解密加密的备份数据
xtrabackup --decrypt=AES256 \
--encrypt-key="2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP" \ 
--target-dir=/backups/
或者
--encrypt-key-file=/backups/keyfile

剩余的操作和之前的恢复数据的操作一样

echo -n "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /backups/keyfile
部分备份

xtrabackup支持在启用innodb_file_per_table选项时进行部分备份。创建部分备份有三种方法：

使用--tables 选项

  1. 创建部分备份

第一种方法是--tables选项。选项的值是一个正则表达式，它与表单中的完全限定的表名（包括数据库名称）相匹配.

比如，仅备份数据库 shark_db中的所有表

shell> xtrabackup --backup \
--user=root \
--password=123 \
--datadir=/var/lib/mysql \
--target-dir=/backups/shark_db_all \
--tables="^shark_db[.].* | ^mysql[.].*|^sys[.].*|^per.*|information_schema[.].*"

仅备份 shark_db.student 表

shell> xtrabackup --backup \
--user=root \
--password=123 \
--datadir=/var/lib/mysql \
--target-dir=/backups/shark_db.student \
--tables="^shark_db[.]student|^mysql[.].*|sys[.].*|^per.*|informatio_schema[.].*"

// 参考默认表名
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+-----

使用 --tables-file 选项

该--tables-file选项指定一个文件，该文件可以包含多个表名，文件中每行一个表名。仅备份文件中指定的表。名称完全匹配，区分大小写，没有模式或正则表达式匹配。表格名称必须是databasename.tablename格式完全限定的。

shell> echo  "mydatabase.mytable" > /tmp/tables.txt
shell> xtrabackup --backup --tables-file=/tmp/tables.txt

使用--databases和--databases-file选项

该--databases选项接受以空格分隔的要备份的数据库和表的列表.

该--databases-file选项指定一个文件，该文件可以包含databasename[.tablename]表单中的多个数据库和表，文件中每行一个元素名称。仅备份命名的数据库和表。名称完全匹配，区分大小写，没有模式或正则表达式匹配。

这两个选项和之前提到的--tables 和 --tables-file 选项是否方式几乎一致。
所不同的是：
talbes 仅可以指定具体的表。
databases 即可以指定数据库名，也可以指定某个数据库中的表。

2.恢复部分备份

在部分备份上使用--prepare选项时，您将看到有关不存在的表的警告。这是因为这些表存在于InnoDB内的数据字典中，但相应的.ibd文件不存在。它们未被复制到备份目录中。

==这些表将从数据字典中删除，当您还原备份并启动InnoDB时，它们将不再存在，==并且不会导致任何错误或警告打印到日志文件中。

因为你之前只备份了部分备份的数据而已，自然恢复的时候不能恢复没有备份的数据。

shell> xtrabackup --prepare   --target-dir=/backups/shark_db_all
  1. 恢复备份文件

方法一：

shell> xtrabackup --copy-back \
--datadir=/var/lib/mysql \
--target-dir=/backups/shark_db_all

// 更改权限
shell> chown mysql.mysql /var/lib/mysql -R

方法二:

适用于没有对 mysql 服务器默认的数据库(msql/sys/performance_schema)备份的情况

a. 初始化数据库
shell> mysqld --initialize --user=mysql

b. 复制文件准备好的备份文件到 mysql 的数据库目录
shell> rsync -avrP /backups/shark_db_all/ /var/lib/mysql/

c. 修改相关文件权限
shell> chown mysql.mysql /var/lib/mysql -R

d. 启动数据库
shell> systemctl start mysqld

e. 找到数据库密码,并登录数据库
shell> grep password /var/log/mysqld.log
shell> mysql -uroot -p   # 回车之后，输入在上一步找到的密码

f. 修改数据库密码
mysql> alter user 'root'@'localhost' identified by '123';

在本地执行备份，并且备份到远程服务器

假如目前 MySQL 服务器是 DB1
远程用于备份到服务器是 BK1

操作步骤

1. 建立DB1 到 BK1 的信任关系

shell> ssh-copy-id  user@DK1

2. 在 DB1 中安装支持多线程压缩和压缩算法的软件

shell> yum install pigz   # 支持多线程压缩

shell>  wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0"http://www.quicklz.com/qpress-11-linux-x64.tar
shell> tar xvf qpress-11-linux-x64.tar
shell> cp qpress /usr/bin

3. 在 DB1 中执行备份命令

xtrabackup  --backup --user=mysqluser --password=password --stream=xbstream | ssh user@BK1 "cat - > /mysql_backup.tar.gz"

上面命令的意思是，把备份压缩后的文件以数据流的格式传输给 ssh ，并通过 ssh 传输给远程服务器 BK1， 备份后的文件名为 mysql_backup.tar.gz

--user 和 --password 指定的是在 MySQL 服务上经过授权的用户和密码
--stream 指定的是采用流格式进行同步压缩

恢复数据

  1. 先解压
    压缩的时候是使用下xb 格式的数据流压缩的，所以解压的时候，也需要使用 xb 工具解压文件到本地硬盘。
mkdir   -p   /data/xb
xbstream    -x    <    mysql_backup.tar.gz   -C    /data/xb
  1. 之后再安装之前的步骤恢复数据
上一篇下一篇

猜你喜欢

热点阅读