第14章 mysql备份

mysqlbackup客户端是用于所有备份和恢复操作的易于使用的工具。在备份操作期间， mysqlbackup备份：

  • 所有 InnoDB 表和索引，包括：

    • InnoDB系统表空间，默认情况下包含所有 InnoDB 表。

    • 使用 InnoDB file-per-table 设置生成的任何单独的数据文件。每个都包含一个表及其关联的索引。每个数据文件都可以使用原始的 Antelope或新的 Barracuda文件格式。

  • 所有 MyISAM 表和索引。

  • 由其他存储引擎管理的表。

  • MySQL数据目录下的其他文件，如 .frm记录各表结构的文件。

  • 服务器数据目录下数据库子目录中的任何其他文件。

除了创建备份之外，mysqlbackup 还可以打包和解包备份数据，将备份操作期间发生的 InnoDB 表的任何更改应用到备份数据，并将数据、索引和日志文件恢复到它们的原始位置或其他位置.

以下是使用mysqlbackup 启动备份操作的一些示例命令 ： 

# Information about data files can be retrieved through the database connection.
# Specify connection options on the command line.
mysqlbackup --user=dba --password --port=3306 \
  --with-timestamp --backup-dir=/export/backups \
  backup

# Or we can include the above options in the configuration file
# under the [mysqlbackup] section, and just specify the configuration file
# and the 'backup' operation.
mysqlbackup --defaults-file=/usr/local/mysql/my.cnf backup

# Or we can specify the configuration file as above, but
# override some of those options on the command line.
mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \
  --compress --user=backupadmin --password --port=18080 \
  backup

您指定的--user和--password 用于连接到 MySQL 服务器。此 MySQL 用户必须在 MySQL 服务器中具有某些权限，如第 4.1.2 节“将 MySQL 权限授予备份管理员”中所述。

该--with-timestamp选项将备份放置在您在上面指定的目录下创建的子目录中。备份子目录的名称由备份运行的日期和时钟时间组成。

有关其他命令行选项的含义，请参阅 第 16 章，mysqlbackup命令行选项。有关配置文件的信息，请参阅第 17 章，配置文件和参数。

确保运行 mysqlbackup的用户或 cron 作业有权将文件从 MySQL 数据库目录复制到备份目录。

Make sure that your connection timeouts are long enough so that the mysqlbackup command can keep the connection to the server open for the duration of the backup run. mysqlbackup pings the server after copying each database to keep the connection alive.

Important

  • Although mysqlbackup backs up InnoDB tables without interrupting database use, the final stage that copies non-InnoDB files (such as MyISAM tables and .frm files) temporarily puts the database into a read-only state, using the statement FLUSH TABLES WITH READ LOCK. For best backup performance and minimal impact on database processing:

    1. Do not run long SELECT queries or other SQL statements at the time of the backup run.

    2. Keep your MyISAM tables relatively small and primarily for read-only or read-mostly work.

    Then the locked phase at the end of a mysqlbackup run is short (maybe a few seconds), and does not disturb the normal processing of mysqld much. If the preceding conditions are not met in your database application, use the --only-innodb option to back up only InnoDB tables, or use the --no-locking option to back up non-InnoDB files. Note that MyISAM, .frm, and other files copied under the --no-locking setting cannot be guaranteed to be consistent, if they are updated during this final phase of the backup.

  • For a large database, a backup run might take a long time. Always check that the mysqlbackup command has been completed successfully by verifying that mysqlbackup has returned the exit code 0, or by observing that mysqlbackup has printed the text “mysqlbackup completed OK!”.

  • mysqlbackup is not the same as the former “MySQL Backup” open source project from the MySQL 6.0 source tree. The MySQL Enterprise Backup product supersedes the MySQL Backup initiative.

  • 在没有涉及表的 DDL 操作运行期间安排备份。有关与 DDL 操作并行创建备份的限制，请参阅 附录 B，MySQL Enterprise Backup的限制。