Mysql官方文档翻译系列14.18--MySql备份与恢复

原文链接：
(https://dev.mysql.com/doc/refman/5.7/en/innodb-backup-recovery.html)

The key to safe database management is making regular backups. Depending on your data volume, number of MySQL servers, and database workload, you can use these backup techniques, alone or in combination: hot backup with MySQL Enterprise Backup; cold backup by copying files while the MySQL server is shut down; logical backup with mysqldump for smaller data volumes or to record the structure of schema objects. Hot and cold backups are physical backups that copy actual data files, which can be used directly by the mysqld server for faster restore.

数据库安全管理的关键是进行常规备份。根据你的数据量，数据库服务器的个数以及数据库的工作负荷，你能使用单独或者组合使用以下这些备份技术：
Mysql企业版热备份；通过当Mysql服务器关闭时拷贝文件进行的冷备份；针对小数据量或者记录记录数据库模式结构而采用mysqldump进行逻辑备份。
热备和冷备都是拷贝通过拷贝实际的数据文件进行物理备份，这些文件能够直接被mysqld服务器使用以进行快速恢复。

> 热备份

The mysqlbackup command, part of the MySQL Enterprise Backup component, lets you back up a running MySQL instance, including InnoDB tables, with minimal disruption to operations while producing a consistent snapshot of the database. When mysqlbackup is copying InnoDB tables, reads and writes to InnoDB tables can continue. MySQL Enterprise Backup can also create compressed backup files, and back up subsets of tables and databases. In conjunction with the MySQL binary log, users can perform point-in-time recovery. MySQL Enterprise Backup is part of the MySQL Enterprise subscription. For more details, see Section 29.2, “MySQL Enterprise Backup Overview”.

mysqlbackup命令是MySQL企业版备份模块的一部分，可以让你备份一个运行中的MySQL实例，包括InnoDB表，而且在生成一个一致性的数据库快照时能够最少地中断业务操作。
当mysqlbackup拷贝InnoDB表时，对InnoDB的读和写操作能够继续进行。Mysql企业版备份功能同样可以创建压缩的备份文件，备份表和数据库的子集。协同Mysql二进制日志，用户能够进行时间点级别的恢复。
Mysql企业版备份功能时Mysql企业版的一部分。更多细节参考MySQL Enterprise Backup Overview.

> 冷备份

If you can shut down the MySQL server, you can make a physical backup that consists of all files used by InnoDB to manage its tables. Use the following procedure:
Perform a slow shutdown of the MySQL server and make sure that it stops without errors.
Copy all InnoDB data files (ibdata files and .ibd files) into a safe place.
Copy all the .frm files for InnoDB tables to a safe place.
Copy all InnoDB log files (ib_logfile files) to a safe place.
Copy your my.cnf configuration file or files to a safe place.

如果你关闭Mysql服务器，你能将Innodb管理表过程用到的所有文件进行物理备份。进行如下步骤：

• 正常关闭mysql服务器，确保没有错误.
• 拷贝所有innodb数据文件（ibdata和.ibd文件）到一个安全的地方.
• 拷贝所有innodb表用到的.frm文件到一个安全的地方。
• 拷贝所有innodb的日志文件（ib_logfile）到一个安全的地方。
• 拷贝你的my.cnf配置文件到一个安全的地方。

> 用mysqldump进行逻辑备份

In addition to physical backups, it is recommended that you regularly create logical backups by dumping your tables using mysqldump. A binary file might be corrupted without you noticing it. Dumped tables are stored into text files that are human-readable, so spotting table corruption becomes easier. Also, because the format is simpler, the chance for serious data corruption is smaller. mysqldump also has a --single-transaction option for making a consistent snapshot without locking out other clients. See Section 7.3.1, “Establishing a Backup Policy”.
Replication works with InnoDB tables, so you can use MySQL replication capabilities to keep a copy of your database at database sites requiring high availability. See Section 14.19, “InnoDB and MySQL Replication”.

除了物理备份，也推荐你定期地通过mysqldump来转储表文件来创建逻辑备份。二进制文件稍不留神就会崩溃。转储的表被存储进人类刻度的文本文件中，因此找出表中的崩溃点更容易。而且，因为格式简单，严重的数据损坏的几率更小。
mysqldump有--single-transaction选项来使得导出一致性快照时不锁定其他客户端。参考章节7.31.1，Establishing a Backup Policy
Innodb表在主备复制中可以工作，因此你可以使用mysql复制特性来保存一个数据库的备份来保证高可用。参考章节14.19，“InnoDB and MySQL Replication”

转载于:https://www.cnblogs.com/grey-wolf/p/7493681.html