Home / Questions / 56041
Accepted
JackNavaRow
Asked: 2020-03-18 05:47:50 +0800 CST 2020-03-18 05:47:50 +0800 CST

在 MySQL 中执行增量或差异备份

  • 772

我有一个不断增长的数据库，所以我必须非常注意这些记录。

我在 Internet 上查找，Oracle 公司（自 2010 年以来拥有 MySQL）提供了一种付费产品MySQL Enterprise Backup，以使用一个命令执行这种类型的备份，mysqlbackups.

使用示例：

mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image

使用此工具，可以执行增量备份和执行差异备份。

我的问题基本上是：如何使用基本的 MySQL 命令执行增量或差异备份？

mysql

3 Answers

  1. Best Answer

    如何使用基本的 MySQL 命令执行增量或差异备份？

    可以使用 MySQL 提供的工具进行增量备份，例如mysqldump，稍后使用diff仅存储差异等工具。

    问题是它mysqldump对于商业用途有几个问题：

    • 由于获取数据的方式，备份过程会使 CPU 和内存过载。向服务器发起不同的 SQL 查询，服务器必须处理结果以将它们发送到mysqldump. 同时，它mysqldump必须收集该信息并将其保存为 SQL 格式，以便以后能够重现。

    • 备份的大小比磁盘上的数据大得多。对于具有二进制数据（blob）的表，它们以十六进制转储。

    • 压缩的后续或同时处理（使用管道）将增加备份时间或处理器负载和恢复时间，因为必须执行解压缩过程。

    • 与直接从磁盘读取数据相比，备份持续时间非常长。

    • 与直接写入磁盘相比，还原速度非常慢。

    • 尽管该工具提供了诸如--single-transaction在数据库级别进行一致备份等选项，但它并不健壮 (*)，并且如果在大量转储期间事务日志填满，它可能会阻止对服务器上所有表和数据库的访问。直到备份的记录数交易完成。

    • 诸如--lock-tables在转储期间阻止使用表等选项，导致对这些表的查询被阻止。

    • 由于每个 SQL 查询的解释和验证以及插入每条记录时对键的验证，恢复过程使服务器的 CPU、磁盘和内存过载，插入的记录越多（尽管可以通过使用类似--disable-keys) 的选项来减少影响。

    (*)从文档中：

    在--single-transaction转储过程中，为确保转储文件有效（正确的表内容和二进制日志坐标），其他连接不应使用以下语句：ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. 一致读取与这些语句不隔离，因此在要转储的表上使用它们可能会导致SELECTmysqldump 执行的检索表内容以获取不正确的内容或失败。

    在备份期间使用ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE可能会得到不正确的结果或导致失败。

    建议的解决方案

    如果你可以使用其他工具（完全免费，不像 MySQL），我推荐使用 Percona 开发的备份工具XtraBackup：

    Percona XtraBackup 是一个免费、开源、完整的在线备份解决方案，适用于所有版本的 Percona Server for MySQL、MySQL® 和 MariaDB®。Percona XtraBackup 的下载量超过 2,100,000 次，可在事务系统上执行在线非阻塞、紧密压缩、高度安全的备份，以便应用程序在计划的维护时段内保持完全可用。

    在西班牙语中：

    Percona XtraBackup 是一个免费、开源、完整的备份解决方案，适用于所有版本的 Percona Server、MySQL® 和 MariaDB®。Percona XtraBackup 的下载量超过 2,100,000 次，可为事务系统执行高度安全、高度压缩、无阻塞的在线备份，因此应用程序在计划的维护时段内保持完全可用。

    或者，同样的，在备份过程中数据库性能略有下降，但是没有锁表，或者干扰了服务器的正常工作。

    在有关增量备份的文档中，您可以看到一个使用示例：

    要进行完整备份，只需运行：

    xtrabackup --backup --target-dir=/data/backups/base

    要从前一个执行增量备份：

    xtrabackup --backup --target-dir=/data/backups/incremental1 \
    --incremental-basedir=/data/backups/base

    运作

    该过程在产品的网站上有详细说明。

    它制作磁盘文件的二进制副本，最大限度地减少对服务器的影响，分析事务日志以标记确切的起始点（LSN日志序列号），因此能够提供完整的备份一致，同时更新日志后台数据。

    差异备份通过仅复制存储在事务日志中的差异来工作，从而减少磁盘访问和备份时间。

    唯一的缺点是 MyISAM 表不能增量保存，因为它们不是事务性的，因此它们的完整副本是通过执行表锁定来制作的。

    现在没有令人信服的理由在 MyISAM 中维护表，所以这应该不是问题。

    恢复备份会强制恢复将表中的信息更新至事务日志（崩溃恢复），因此恢复还将导致在完整服务器基础级别（而不是在数据库级别作为“在交易中”模式会做）。

    此外，由于恢复是在不同的目录中完成的（然后需要将其移动到生产目录），因此 MySQL 服务器可以在不同的端口上运行以访问恢复的数据。

    测试它们或访问它们以对某些已删除数据进行特定访问或进行选择性导出或恢复可能很有用。

    缺点

    如果表未优化（它们有“要清除的残留物”、修改或删除的记录，这些记录会留下旧值占用未使用的空间），则残留物将被复制到完整备份（但不会复制到增量备份） )，因此建议对OPTIMIZE TABLE那些更大的表执行 a 或在完整备份之前倾向于累积更大的残留物。

    通过 LVM 备份

    另一种执行一致备份的方法是通过LVM 快照。

    所有 MySQL 内容都应存储在同一逻辑卷上，以使该解决方案适用（事务日志、复制日志（如果有）、表空间等）。

    在恢复过程中，应强制对备份时未完成的所有事务进行崩溃恢复。rollback

    该过程将按如下方式进行：

    # Creamos un LV para el snapshot
lvcreate -L1G -n mysqlbackup -s mysqldatos

    在哪里：

    • -L1G允许原始分区在满之前修改 1 GB。如果数据库活动很高，则可能需要更大的快照。

    • -n mysqlbackup将命名mysqlbackup此逻辑卷。

    • -s mysqldatos表示它将是逻辑卷的快照mysqldatos。

    要挂载快照并访问它以进行存储或计算增量备份：

    # Averiguamos el nombre del dispositivo donde se ha creado el volumen lógico
lvdisplay | egrep "LV Path*mysqlbackup$"
# Creamos el punto de montaje (si no existe)
[ -d /mnt/mysqlbackup ] || mkdir /mnt/mysqlbackup
# Montamos el snapshot
mount /dev/mapper/vg-mysqlbackup /mnt/ops/mysqlbackup
# [hacemos el backup del tipo que sea]
# ...
# Desmontamos y borramos el snapshot
umount /mnt/ops/mysqlbackup
lvremove /dev/mapper/vg-mysqlbackup

    从快照管理完整和增量备份的工具可以是duplicity，它支持大文件的增量备份，只存储以前备份中修改的部分。

    • 13

  2. 前段时间我在找学校，我的一个项目就是这样。

    使用 Git 增量备份数据库

    备份数据库的一种非常有趣的方法（因为它很简单而且非常强大）是使用 Git。该过程很简单，并且基于执行数据库转储，以便表的每一行都是一个独立的插入，这样在每次提交中我们将只保存与最后状态相关的差异（删除和插入，例如更新） )。

    在 MySQL 的特定情况下，我们最初会这样做：

    $ mkdir mydatabase && cd mydatabase
$ mysqldump --skip-extended-insert > data.sql
$ git init
$ git add data.sql
$ git commit -m "Initial dump of mydatabase"

    从那时起，我们可以使用如下简单的脚本自动执行该过程：

    $ cat /usr/bin/git_backup_database!/bin/sh
BACKUP_DIRECTORY=$1
DATABASE=$2

cd $BACKUP_DIRECTORY &&
mysqldump --skip-extended-insert $DATABASE > data.sql &&
git commit -am "Updating dump `date +%Y_%m_%d__%H_%M`"`

    根据您的数据库的查询量，您将它以某种频率或其他频率放入 cron 中会很有趣。此外，建议运行 $ git gc 来优化存储库。例如，一天两次，一周一次的维护：

    0 0 * * * /usr/bin/git_backup_database /path/to/mydatabase mydatabase
0 12 * * * /usr/bin/git_backup_database  /path/to/mydatabase mydatabase
0 1 * * 1 cd /path/to/mydatabase && git gc

    此外，在另一台计算机上，没有人阻止您执行一个$ git clone ssh://equipo:path/to/mydatabase操作并在闪存中拥有数据库的所有历史记录（嗯，这是相对的，它将占据自己的......）甚至安排 $ git pull 进行多次备份在不同的机器上。简而言之，无限的选择打开了。

    • 8

  3. 正如我们在评论中提到的，我认为您有一个直到现在还没有考虑过的选项，即让平台正常工作，而不是寻找更“手动”的解决方案。

    您的问题是它增长了很多BBDD，我知道您不想做的是backup不断阻止BBDD.

    我可以为您提供 3 种解决方案，具体取决于您在平台级别的需要或可以负担的费用，我推荐其中一种，在这种情况下，我将它们按应用程序的复杂性/成本顺序排列。

    MySQL主/从

    在这种情况下，我们拥有的是一master de MySQL台slave机器。这样，虽然你可以继续写他master，但他slave有一个完整的副本BBDD，你只能阅读，但你可以随意阻止，你可以无耻地粉碎，因为他会得到updates他们作为他走了。我可以

    安装它比较简单，我给你一个教程，教你如何安装一个master/slave de MySQL

    即使你会一点英文，听从命令就足够了。我在生产中的一个非常大的应用程序中拥有它，没有问题。

    它的好处是它可以用两台机器组装，因此平台的成本非常小，您可以随意扩展它。读取次数非常多，写入次数高达master. 同样，如果崩溃slave没有任何反应，如果崩溃master，您可以更改slave并master保存应用程序。

    mysql-集群

    是版本的一个新特性5.7，我只能用一个测试项目来测试，所以我不能给你一个非常好的反馈，我只能告诉你，它似乎已经测试了很多，我的意见看过的都挺优惠的。

    在这里你有安装 a 的教程cluster de MySQL，它不像 a 那样简单，master/slave但它给你提供了比它更多的东西。例如，它必须automatic sharding允许您大大增加写入次数。如果您不知道它是什么，他们会在StackOverflow 上用英文sharding评论它，阅读一定会对您有所帮助。

    厨房集群

    就是其中cluster之一master/master/master，你可以同时从三台服务器读写，我觉得这对你的需要来说太多了，但是如果你认为应用程序可以增加很多大小，这是一个非常好的选项。

    问题是必须至少有 3 台服务器cluster才能足够一致，因为所谓的必须存在quorum。读取次数不会像在 a 中那样增加，cluster de MySQL因为它没有，automatic sharding但与master/slave服务器不同的是，它可以毫无问题地关闭，因为它quorum可以继续存在。

    这里有CentOS7 安装galera和Ubuntu 16.04 安装galera 的方法，因为不知道你用的是什么系统，就留给你们两个吧。我总是喜欢CentOS或喜欢Debian与服务器相关的一切。

    Galera和之间的区别MySQL Cluster

    之间的差异Galera和集群Mysql是相当多的，我建议你阅读这个幻灯片，因为如果我们把它放在这里，答案几乎是无限的，然后每个人的意见都会进入哪个更好或更坏。

    我认为有了这个，我可以让您了解平台级别的选项，而不必拉一些更“奇怪”的东西，例如将 agit放到文件中MySQL

    如果您需要更多信息，请发表评论，我会尽我所能帮助您。

    • 4