本节总结了一些进行备份的一般方法。
使用 MySQL Enterprise Backup 进行热备份
MySQL Enterprise Edition 的客户可以使用
MySQL Enterprise Backup产品对
整个实例或选定的数据库、表或两者进行物理备份。该产品包括
增量备份和
压缩备份的功能。备份物理数据库文件使恢复比命令等逻辑技术快得多mysqldump
。InnoDB
使用
热备份机制复制表。(理想情况下,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
'
. 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.
file_name
' FROM
tbl_name
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:
From a client program, execute
FLUSH TABLES WITH READ LOCK
.From another shell, execute
mount vxfs snapshot
.从第一个客户端,执行
UNLOCK TABLES
.从快照复制文件。
卸载快照。
其他文件系统(例如 LVM 或 ZFS)中可能也有类似的快照功能。