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. 为了有用，必须定期安排备份。MySQL中可以使用多种工具完成完整备份（某个时间点的数据快照）。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. 例如，MySQL Enterprise Backup可以对整个实例进行物理备份，并进行优化以最大限度地减少开销，避免在备份InnoDB数据文件时中断；mysqldump提供在线逻辑备份。This discussion uses mysqldump.本讨论使用mysqldump。

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:假设我们在周日下午1点负载较低时使用以下命令对所有数据库中的所有InnoDB表进行完整备份：

shell> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

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.mysqldump生成的结果.sql文件包含一组SQL INSERT语句，可用于稍后重新加载转储的表。

This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). 此备份操作在转储开始时获取所有表的全局读取锁（使用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. 如果发出FLUSH语句时正在运行长更新语句，则备份操作可能会暂停，直到这些语句完成。After that, the dump becomes lock-free and does not disturb reads and writes on the tables.之后，转储将无锁，不会干扰表上的读取和写入。

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. 之前假设要备份的表是InnoDB表，因此--single-transaction使用一致的读取，并保证mysqldump看到的数据不会改变。(Changes made by other clients to InnoDB tables are not seen by the mysqldump process.) （mysqldump进程看不到其他客户端对InnoDB表所做的更改。）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.例如，对于mysql数据库中的MyISAM表，在备份过程中不得对mysql帐户进行管理更改。

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.您还必须处理增量备份以恢复增量更改。

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. 要进行增量备份，我们需要保存增量更改。在MySQL中，这些更改在二进制日志中表示，因此MySQL服务器应始终使用--log-bin选项启动以启用该日志。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:启用二进制日志记录后，服务器在更新数据的同时将每个数据更改写入文件。查看运行了几天的MySQL服务器的数据目录，我们发现了以下MySQL二进制日志文件：

-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

Each time it restarts, the MySQL server creates a new binary log file using the next number in the sequence. 每次重新启动时，MySQL服务器都会使用序列中的下一个数字创建一个新的二进制日志文件。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. 当服务器运行时，您还可以告诉它关闭当前的二进制日志文件，并通过发出FLUSH LOGS SQL语句或mysqladmin FLUSH LOGS命令手动开始一个新的日志文件。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.mysqldump还可以选择刷新日志。数据目录中的.index文件包含该目录中所有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. MySQL二进制日志对于恢复很重要，因为它们构成了一组增量备份。如果在进行完整备份时确保刷新日志，则之后创建的二进制日志文件将包含自备份以来所做的所有数据更改。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命令，使其在完全备份时刷新MySQL二进制日志，并使转储文件包含新的当前二进制日志的名称：

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases > backup_sunday_1_PM.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. 执行此命令后，数据目录包含一个新的二进制日志文件gbichot2-bin.000007，因为--flush-logs选项会导致服务器刷新其日志。The --master-data option causes mysqldump to write binary log information to its output, so the resulting .sql dump file includes these lines:--master-data选项使mysqldump将二进制日志信息写入其输出，因此生成的.sql转储文件包括以下行：

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

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

On Monday at 1 p.m., we can create an incremental backup by flushing the logs to begin a new binary log file. 周一下午1点，我们可以通过刷新日志来创建增量备份，以开始一个新的二进制日志文件。For example, executing a mysqladmin flush-logs command creates gbichot2-bin.000008. 例如，执行mysqladmin flush-logs命令会创建gbichot2-bin.000008。All changes between the Sunday 1 p.m. full backup and Monday 1 p.m. are written in gbichot2-bin.000007. 周日下午1点完整备份和周一下午1点之间的所有更改都以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.) （例如，将其备份到磁带或DVD上，或复制到另一台机器上。）On Tuesday at 1 p.m., execute another mysqladmin flush-logs command. 周二下午1点，执行另一个mysqladmin flush-logs命令。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).周一下午1点至周二下午1点之间的所有更改都以gbichot2-bin.000008编写（也应复制到安全的地方）。

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:MySQL二进制日志占用磁盘空间。要释放空间，请不时地清除它们。一种方法是删除不再需要的二进制日志，例如当我们进行完整备份时：

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