MySQL 8.0 参考手册  / 第 7 章备份与恢复  /  7.2 数据库备份方式

7.2 数据库备份方式

本节总结了一些进行备份的一般方法。

使用 MySQL Enterprise Backup 进行热备份

MySQL Enterprise Edition 的客户可以使用 MySQL Enterprise Backup产品对 整个实例或选定的数据库、表或两者进行物理备份。该产品包括 增量备份和 压缩备份的功能。备份物理数据库文件使恢复比命令等逻辑技术快得多mysqldumpInnoDB使用 热备份机制复制表。(理想情况下,InnoDB表应该代表数据的绝大部分。)使用热备份复制来自其他存储引擎的表机制。有关 MySQL Enterprise Backup 产品的概述,请参阅第 28.2 节,“MySQL Enterprise Backup 概述”

使用 mysqldump 进行备份

mysqldump程序可以进行备份 。它可以备份各种表。(参见 第 7.4 节,“使用 mysqldump 进行备份”。)

对于表,可以使用mysqldump的选项 InnoDB执行不锁定表的在线备份 。请参阅第 7.3.1 节,“建立备份策略”--single-transaction

通过复制表文件进行备份

对于使用自己的文件表示每个表的存储引擎,可以通过复制这些文件来备份表。例如, MyISAM表存储为文件,因此很容易通过复制文件( 、 和 files )来*.frm进行 *.MYD备份*.MYI。要获得一致的备份,请停止服务器或锁定并刷新相关表:

FLUSH TABLES tbl_list WITH READ LOCK;

你只需要一个读锁;这使其他客户端可以在您复制数据库目录中的文件时继续查询表。需要刷新以确保在开始备份之前将所有活动索引页写入磁盘。请参阅第 13.3.5 节,“LOCK TABLES 和 UNLOCK TABLES 语句”第 13.7.6.3 节,“FLUSH 语句”

只要服务器不更新任何内容,您也可以通过复制所有表文件来简单地创建二进制备份。(但请注意,如果您的数据库包含表,则表文件复制方法不起作用InnoDB。此外,即使服务器没有主动更新数据,InnoDB 修改后的数据可能仍缓存在内存中,而没有刷新到磁盘。)

制作带分隔符的文本文件备份

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. See Section 13.2.9, “SELECT Statement”. This method works for any kind of data file, but saves only table data, not the table structure.

Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option. See Section 7.4.3, “Dumping Data in Delimited-Text Format with mysqldump”.

To reload a delimited-text data file, use LOAD DATA or mysqlimport.

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see Section 5.4.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 7.5, “Point-in-Time (Incremental) Recovery”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS or mysqldump --flush-logs. See Section 4.5.4, “mysqldump — A Database Backup Program”.

Making Backups Using Replicas

If you have performance problems with your source server while making backups, one strategy that can help is to set up replication and perform backups on the replica rather than on the source. See Section 16.3.1, “Using Replication for Backups”.

If you are backing up a replica server, you should back up its source info and relay log info repositories (see Section 16.2.4, “Relay Log and Replication Metadata Repositories”) when you back up the replica's databases, regardless of the backup method you choose. These information files are always needed to resume replication after you restore the replica's data. If your replica is replicating LOAD DATA statements, you should also back up any SQL_LOAD-* files that exist in the directory that the replica uses for this purpose. The replica needs these files to resume replication of any interrupted LOAD DATA operations. The location of this directory is the value of the slave_load_tmpdir system variable. If the server was not started with that variable set, the directory location is the value of the tmpdir system variable.

Recovering Corrupt Tables

If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, see Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. 从第一个客户端,执行 UNLOCK TABLES.

  4. 从快照复制文件。

  5. 卸载快照。

其他文件系统(例如 LVM 或 ZFS)中可能也有类似的快照功能。