为了有用，备份必须定期安排。在 MySQL 中，可以使用多种工具进行完整备份（某一时间点的数据快照）。例如，MySQL Enterprise Backup 可以对整个实例执行物理备份，优化以最小化开销并在备份 InnoDB 数据文件时避免中断；mysqldump 提供在线逻辑备份。本讨论使用 mysqldump。To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in time) can be done in MySQL with several tools. For example, MySQL Enterprise Backup can perform a physical backup of an entire instance, with optimizations to minimize overhead and avoid disruption when backing up InnoDB data files; mysqldump provides online logical backup. This discussion uses mysqldump.

假设我们在周日下午 1 点在负载较低时，使用以下命令对所有数据库中的所有InnoDB表进行完整备份：Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low:

$> mysqldump --all-databases --source-data --single-transaction > backup_sunday_1_PM.sql

mysqldump生成的.sql文件包含一组 SQL INSERT语句，可用于在以后重新加载转储的表。The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

此备份操作在转储开始时会对所有表获取全局读锁（使用 FLUSH TABLES WITH READ LOCK）。一旦获取了此锁，就会读取二进制日志坐标并释放锁。如果在执行 FLUSH 语句时有长时间运行的更新语句，备份操作可能会暂停，直到这些语句执行完毕。之后，转储将变为无锁，并且不会干扰表的读取和写入操作。This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

之前假设要备份的表是 InnoDB 表，因此 --single-transaction 使用一致性读，并保证 mysqldump 查看到的数据不会改变。（其他客户端对 InnoDB 表的更改，mysqldump 进程将不可见。）如果备份操作包括非事务型表，则一致性要求它们在备份期间不发生变化。例如，对于 mysql 数据库中的 MyISAM 表，备份期间不得对 MySQL 帐户进行任何管理操作。It was assumed earlier that the tables to back up are InnoDB tables, so --single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change. (Changes made by other clients to InnoDB tables are not seen by the mysqldump process.) If the backup operation includes nontransactional tables, consistency requires that they do not change during the backup. For example, for the MyISAM tables in the mysql database, there must be no administrative changes to MySQL accounts during the backup.

完整备份是必要的，但创建它们并不总是方便。它们会生成较大的备份文件，并且需要时间来生成。从优化的角度来看，它们并不理想，因为每次后续的完整备份都会包含所有数据，即使是自上一次完整备份以来未发生变化的部分。更有效的方法是先进行初始完整备份，然后进行增量备份。增量备份较小，生成所需时间也更短。权衡之处在于，在恢复时，你不能仅仅通过重新加载完整备份来恢复数据，你还必须处理增量备份以恢复增量更改。Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.

为了进行增量备份，我们需要保存增量的更改。在 MySQL 中，这些更改以二进制日志的形式表示，因此 MySQL 服务器应始终以 --log-bin 选项启动以启用该日志。启用二进制日志后，服务器在更新数据的同时会将每个数据更改写入文件。查看已运行数天的 MySQL 服务器的数据目录，我们会发现这些 MySQL 二进制日志文件：To make incremental backups, we need to save the incremental changes. In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the --log-bin option to enable that log. With binary logging enabled, the server writes each data change into a file while it updates data. Looking at the data directory of a MySQL server that has been running for some days, we find these MySQL binary log files:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

每次重启时，MySQL 服务器都会使用序列中的下一个编号创建一个新的二进制日志文件。在服务器运行时，你也可以通过执行 FLUSH LOGS SQL 语句或使用 mysqladmin flush-logs 命令手动关闭当前二进制日志文件并开始一个新的日志文件。mysqldump 也有一个刷新日志的选项。数据目录中的 .index 文件包含该目录下所有 MySQL 二进制日志的列表。Each time it restarts, the MySQL server creates a new binary log file using the next number in the sequence. While the server is running, you can also tell it to close the current binary log file and begin a new one manually by issuing a FLUSH LOGS SQL statement or with a mysqladmin flush-logs command. mysqldump also has an option to flush the logs. The .index file in the data directory contains the list of all MySQL binary logs in the directory.

MySQL 二进制日志对于恢复非常重要，因为它们构成了增量备份的集合。如果你在进行完整备份时确保刷新日志，那么之后创建的二进制日志文件将包含自备份以来的所有数据更改。让我们稍微修改之前的 mysqldump 命令，以便在进行完整备份时刷新 MySQL 二进制日志，并使导出文件包含新的当前二进制日志的名称：The MySQL binary logs are important for recovery because they form the set of incremental backups. If you make sure to flush the logs when you make your full backup, the binary log files created afterward contain all the data changes made since the backup. Let's modify the previous mysqldump command a bit so that it flushes the MySQL binary logs at the moment of the full backup, and so that the dump file contains the name of the new current binary log:

$> mysqldump --single-transaction --flush-logs --source-data=2 \
         --all-databases > backup_sunday_1_PM.sql

执行此命令后，数据目录中会包含一个新的二进制日志文件，gbichot2-bin.000007，因为--flush-logs选项会导致服务器刷新其日志。--source-data选项会导致mysqldump将二进制日志信息写入其输出，因此生成的.sql转储文件将包含这些行：After executing this command, the data directory contains a new binary log file, gbichot2-bin.000007, because the --flush-logs option causes the server to flush its logs. The --source-data option causes mysqldump to write binary log information to its output, so the resulting .sql dump file includes these lines:

-- Position to start replication or point-in-time recovery from
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='gbichot2-bin.000007',SOURCE_LOG_POS=4;

因为mysqldump命令进行了完整备份，所以这些行意味着两件事：Because the mysqldump command made a full backup, those lines mean two things:

在周一下午 1 点，我们可以通过刷新日志来创建增量备份，以开始一个新的二进制日志文件。例如，执行一个 mysqladmin flush-logs 命令会创建 gbichot2-bin.000008。所有在周日下午 1 点的完整备份和周一下午 1 点之间的更改都记录在 gbichot2-bin.000007 中。这个增量备份很重要，因此最好将其复制到安全的地方。（例如，备份到磁带或 DVD，或复制到另一台机器。）在周二下午 1 点，再次执行一个 mysqladmin flush-logs 命令。所有在周一下午 1 点和周二下午 1 点之间的更改都记录在 gbichot2-bin.000008 中（也应该复制到安全的地方）。On Monday at 1 p.m., we can create an incremental backup by flushing the logs to begin a new binary log file. For example, executing a mysqladmin flush-logs command creates gbichot2-bin.000008. All changes between the Sunday 1 p.m. full backup and Monday 1 p.m. are written in gbichot2-bin.000007. This incremental backup is important, so it is a good idea to copy it to a safe place. (For example, back it up on tape or DVD, or copy it to another machine.) On Tuesday at 1 p.m., execute another mysqladmin flush-logs command. All changes between Monday 1 p.m. and Tuesday 1 p.m. are written in gbichot2-bin.000008 (which also should be copied somewhere safe).

MySQL 二进制日志会占用磁盘空间。为了释放空间，需要不时清理它们。一种方法是删除不再需要的二进制日志，例如在我们进行完整备份时：The MySQL binary logs take up disk space. To free up space, purge them from time to time. One way to do this is by deleting the binary logs that are no longer needed, such as when we make a full backup:

$> mysqldump --single-transaction --flush-logs --source-data=2 \
         --all-databases --delete-source-logs > backup_sunday_1_PM.sql