使用5种不同方法备份MySQL数据库

随着越来越多的数据存储在数据库中，数据备份变得越来越重要。数据库备份是一项至关重要的任务，旨在确保企业或个人不会因数据丢失而蒙受巨大损失。MySQL 是一款广受欢迎的开源关系型数据库管理系统，在企业中得到广泛应用。本文将探讨如何备份 MySQL 数据库。

MySQL数据库备份类型

根据数据库大小的不同，备份分为四种类型，适用于不同场景：

完整备份

这是大多数人使用的常见方法。它可以备份整个数据库，包括所有数据库对象，例如用户表、系统表、索引、视图和存储过程。但该方法耗时较长且占用空间较大，因此通常建议每周进行一次完整备份。

事务日志备份

事务日志是一个独立的文件，用于记录数据库的变更。备份只需复制自上次备份以来对数据库所做的更改，因此耗时极短。为增强数据库的可靠性，建议每小时甚至更频繁地备份事务日志。

差异备份

这是一种仅备份数据库部分数据的其他方法，它不使用事务日志，而是基于整个数据库的最新镜像进行备份。由于仅包含自上次完整备份以来发生变更的数据库，因此其体积小于原始的完整备份。该方法具有存储与恢复速度快的优点。建议每天执行一次差异备份。

文件备份

数据库可能由硬盘上的多个文件组成。如果数据库非常大，无法在一夜之间完成备份，则可以使用文件备份，每天晚上仅备份数据库的一部分。由于数据库通常不会大到必须使用多个文件存储，因此这种备份方式并不常见。

根据数据库的状态，可分为三种类型：

1. 冷备份

冷备份是指在数据库处于离线状态、用户无法访问时进行的数据备份。由于备份操作是在数据库离线状态下执行的，因此冷备份也常被称为离线备份。冷备份通常占用较少系统资源，且由于数据库离线期间无法写入新数据，备份过程可一次性完成全部数据的复制。当然，冷备份存在一个明显局限：备份操作执行期间，数据库无法被访问。

2. 热备份

热备份是指在数据库处于联机状态且用户可正常访问时对数据执行的备份操作。热备份通常是一种非常便捷的解决方案，因为它无需停机，对操作系统运行的干扰也极小；但热备份也存在一些弊端：如果在备份过程中被备份的数据发生了任何更改，则该备份可能与数据库中数据的最终版本不一致。

3. 逻辑备份

逻辑备份使用软件从数据库中提取数据，并将结果写入文件。

如何备份 MySQL 数据库？

根据恢复方式的不同，MySQL 备份分为逻辑备份和物理备份。逻辑备份是将数据库导出为 SQL 语句，在恢复时执行这些语句来重建数据库数据；物理备份则是以磁盘上原有的格式直接复制数据库文件。

这两种备份方式各有优缺点。一般来说，物理备份的恢复速度更快，但占用空间更大；而逻辑备份的速度较慢，但占用空间更小。

1. 使用 mysqldump

mysqldump 是 MySQL 自带的备份工具，位于 bin 目录下：/usr/local/mysql/bin/mysqldump，支持基于 InnoDB 的热备份。但由于属于逻辑备份，备份速度相对较慢，适用于备份数据量较小的场景。通过 mysqldump 全量备份 + 二进制日志（binary log），可实现时间点恢复（Point-in-Time Recovery）。

对于 MyISAM 存储引擎的表，只能使用热备份。此时应禁止数据写入操作，因此需首先添加读锁。虽然此时也可手动进入数据库执行命令添加读锁，但操作较为繁琐；更简便的方式是直接在 mysqldump 工具中启用锁定选项 —— 即 --lock-all-tables 参数，例如：mysqldump --databases test --lock-all-tables --flush-logs > /tmp/backup_test_`date +%F-%H-%M`.sql。

如果仅备份单个数据表，请在数据库名称 test 后直接添加该数据表的名称。

对于使用 InnoDB 存储引擎的表，可在不锁定数据库的情况下执行热备份。添加用于执行热备份的选项：--single-transaction，例如：mysqldump --databases test --single-transaction --flush-logs --master-data=2 > /tmp/backup_test_`date +%F-%H-%M`.sql。

附注：恢复时请记得关闭二进制日志：

mysql> 设置 sql_log_bin=0；

由于这是基于逻辑备份方法，执行 SQL 将插入数据并记录到二进制日志中。由于这是恢复操作，可关闭插入的二进制日志以缩短恢复时间。

点击了解有关 使用或不使用 mysqldump 进行 MySQL 数据库备份与恢复 的更多信息。

2. 基于LVM快照的备份

在物理备份方面，存在基于文件系统（LVM快照）的物理备份；您也可以直接使用tar等命令对整个数据库目录进行打包备份，但这些方法仅适用于冷备份。不同的存储引擎所备份的内容也不同：MyISAM自动按表级别进行备份，而InnoDB若未启用独立表空间，则只能对整个数据库进行备份。

以下是关于使用LVM快照功能进行备份的介绍。

1) 为确保安全，首先对数据库施加读锁

mysql>FLUSH TABLES WITH READ LOCK

2) 刷新二进制日志以进行时间点恢复

mysql>FLUSH LOGS

3) 然后创建快照卷

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) 进入数据库以释放读取锁

UNLOCK TABLES

5) 挂载快照卷以进行备份

mount –r /dev/myvg/data-snap /mnt/snap

6) 最后，对 /mnt/snap 目录下的文件进行打包和备份

用于恢复：

1) 关闭 mysqld

2) 然后备份二进制日志

3）恢复原始备份文件

4) 然后通过二进制日志将数据库恢复到出错的时间点（注意：在通过二进制日志进行时间点恢复时，需临时关闭二进制日志）

2010 年至 2012 年间，MySQL 数据库部署在亚马逊的云环境中。该环境提供逻辑卷管理（LVM）快照功能，操作十分便捷且速度快。借助 LVM 快照，在亚马逊 10G 网络环境下恢复数据同样极为迅速。

3. Tar 备份

为首次从数据库执行数据备份做好准备，临时锁定所有表，并打开窗口 1

mysql> 刷新表并加读锁；

查询成功，0 行受到影响（0.00 秒）

mysql> 显示主服务器状态；

+++++| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  | mysql-bin.000003 | 194554 | | |  1 row in set (0.00 sec)

此窗口无法关闭，必须保持打开状态直至 tar 包制作完成。

--Re-open a shell window, go to the data file directory tar packageopen window 2[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqlatar -zcvf mysqla1.tar.gz mysqla CompressedSwitch to window 1 and execute the unlock command

mysql> 解锁表；

Query OK, 0 rows affected (0.00 sec)mysql>

将 tar 包复制到另一台 MySQL 库服务器，覆盖数据目录，然后重启 MySQL 数据库服务。

4. Percona 提供的 xTraBackup

它支持 InnoDB 的物理热备份，支持全量备份和增量备份，速度非常快；支持因 InnoDB 存储引擎导致的不同数据库间的数据迁移；支持主从复制模式下的从库备份与恢复。为使 xTraBackup 支持更多功能扩展，可设置独立表空间并启用 innoDB_file_per_table 功能，从而实现单个数据表的备份。

xTraBackup 备份原理：

xtraBackup 基于 InnoDB 的崩溃恢复功能，通过复制 InnoDB 的数据文件来实现备份。由于备份过程中表未被锁定，因此复制出的数据处于不一致状态；在恢复时，则利用崩溃恢复机制使数据恢复一致性。

InnoDB 维护一个重做日志（也称为事务日志），其中包含对 InnoDB 数据所做的所有更改。InnoDB 启动时，会首先检查数据文件和事务日志，并执行以下步骤：

xTraBackup 在备份过程中，会逐页复制 InnoDB 数据页，而不会锁定表。同时，xTraBackup 还有另一个线程用于监控事务日志。一旦日志发生变化，该线程便会将已更改的日志页复制出去。由于事务日志文件的大小有限，当日志写满后会从开头重新开始写入，因此新数据可能会覆盖旧数据。

在准备过程中，xTraBackup 使用复制的事务日志对已备份的 InnoDB 数据文件执行崩溃恢复。

5. 使用 Vinchin 备份与恢复软件备份 MySQL 数据库

Vinchin 备份与恢复软件 提供强大功能，可保护虚拟机及物理服务器中的数据库。该软件与虚拟机级别备份协同工作，为虚拟环境用户提供关键业务数据和信息系统的双重保障。Vinchin 备份与恢复软件支持对安装在物理机和虚拟机上的 Oracle DB、MySQL、SQL Server、PostgreSQL、Postgres Pro 和 MariaDB 进行保护，并具备强大的数据库备份与恢复功能。此外，它还提供完全备份、差异备份、增量备份及事务日志备份等多种策略，供您按需定制专属备份方案。

Vinchin 备份与恢复支持对 MySQL、SQL Server 和 Oracle 进行高效的热备份，且不会影响数据库的正常运行。

Vinchin 备份与恢复的操作非常简单，只需几个简单的步骤。

1. 选择备份对象。

2. 选择备份目标位置。

3. 配置备份策略。

4. 审核并提交任务。

您可以使用为期60天的全功能免费试用版立即开始使用这一强大系统。只需点击按钮即可获取安装包。您可点击此处，进一步了解如何使用Vinchin备份与恢复软件备份MySQL。

下载免费试用版

适用于多种数据备份

* 15天全功能免费安全下载

总结

MySQL 数据库在众多行业中被广泛应用。为保障其安全性，本文为您介绍了五种不同的 MySQL 数据备份方法。您可以根据自身实际情况选择最适合的方案。还在等什么？立即为您的 MySQL 数据库执行备份吧！