关于mysqldump:99的人都不知道的mysqldump的备份原理

简介

mysqldump 程序是官网逻辑备份工具，生成一组能够导入数据库中以重现原始数据库中的数据对 象和表数据的 SQL 语句，mysqldump 命令还能够生成 CSV，或其余分隔文本或 XML 格局的输入文件。

mysqldump 客户端工具执行备份表至多须要的权限：备份表的 SELECT 权限，导出视图的 SHOW VIEW，导出触发器的 TRIGGER，以及在不应用 –single-transaction 选项时须要进行锁表操作的 LOCK TABLES，更多的选项可能就须要更多的其余权限。

如果要从新加载备份文件，则您必须具备执行这个文件中所有语句须要的权限，例如 create 相应的对象语句就代表须要相应对象的 create 权限，如果有执行 alter 批改表构造的 DDL 语句，则还须要有 alter 权限。

留神：

• 如果您有蕴含生成列 (generated columns，即列在定义表构造时是定义的一个函数表达式，在插入值的时候忽略这个列，在 MySQL 外部通过表定义的表达式进行计算得来这个列的值) 的表，请应用 MySQL 5.7.9 或更高版本提供的 mysqldump 命令来进行备份，晚期版本中提供的 mysqldump 命令对生成列无奈正确解析定义语句(BUG #20769542），您能够应用 INFORMATION_SCHEMA.COLUMNS 表来查问并辨认出具备生成列的表。
• 应用 PowerShell 在 Windows 上应用输入重定向进行的备份会创立一个 UTF-16 编码的备份文件，如：mysqldump [options]> dump.sql，然而无奈应用 UTF-16 作为连贯字符集，所以这个备份文件将无奈正确被从新加载到数据库中，因而，须要应用 WIN 下的专用选项 –result-fifile 指定备份文件路径，而不是应用输入重定向，如：mysqldump [options] –result-fifile = dump.sql 应用 mysqldump 来备份的办法通常有三种，一种是备份单表，或者一组指定的库表，一种是一组或一个数据库下的所有表，一种是整个数据库实例，如：mysqldump [options] db_name [tbl_name …]、mysqldump [options] –databases db_name …、mysqldump [options] –all- databases。要备份整个数据库下的所有表，不要在 db_name 之后带任何表名，能够应用 – databases 指定库名备份整个数据库下的所有表或应用 –all-databases 选项备份整个实例

原理

依据备份参数的不同，具体的备份过程略有差别：首先，执行 flflush tables 命令把内存中的表构造改变同步到磁盘上（把表敞开之后再从新关上），而后执行 flflush tables with read lock 加一个全局 S 锁，为后边获取一个一致性备份做筹备，接着执行 set session transaction isolation level repeatable read 批改隔离级别为 RR, 在这个隔离级别下，对于事务引擎 innodb 能够实现一致性非锁定读, 即能够拿到一个一致性快照，而后执行 start transaction with consistent snapshot 命令开始一个一致性快照事务，这里在整个备份过程中是一个大事务。接着执行 show master status 获取到 binlog fifilename 和 position，这个地位就是以后备份数据的 binlog 地位，对于事务引擎，获取的就是快照数据的 binlog 地位。获取到 binlog 地位之后，执行 unlock tables 解锁事务表，如果是非事务表，在整个备份过程中始终锁着，直到备份实现才开释。在这个步骤之后，开始循环每库，每表这样去备份，依据备份选项，如果有触发器、事件、存储过程和函数等选项，也会进行备份。

在一个库中的表备份开始之前会自定义一个保留点，每个表是从一个点开始的，所以每一个表备份完之后会回滚到这个保留点，这样在有问题的时候就方便进行回滚。一个库下所有表备份完之后就会开释这个保留点。每个表的备份是先查出表构造，而后 select * 出所有的数据而后生成 insert 语句。

性能和可扩展性注意事项

mysqldump 的长处在于是逻辑备份，能够间接关上备份文件进行查看、编辑，以确保导入的备份文件符合要求，但毛病是备份速度较慢，并不适宜备份大表，因为他在备份时生成的是 sql 语句，而在导入的时候是把这些 sql 语句一一执行，比方：DDL 建表，DML 插入数据，而后再 DDL 创立索引等，设计到大量数据的操作都比拟耗费 IO 资源。

大表的备份能够应用官网提供的 mysqlbackup 物理备份工具（MySQL Enterprise Backup 产品的 mysqlbackup 命令），也能够应用 percona 公司的 percona xtrabackup 备份工具（开源，收费的物理备份工具），两者备份的性能和原理都差不多。

mysqldump 备份表数据时是先执行 select，而 MySQL 会把 select 的数据保留到内存中，而后再把备份数据发送给 mysqldump 客户端，mysqldump 接管到备份数据再写到磁盘备份文件中，如果您应用它来备份大表，则内存可能是一个问题，另外还有可能导致热数据被冲刷的问题。能够应用 – quick 选项（或 –opt，它启用 –quick）来防止一次性 select 整个表数据到内存再保留到磁盘备份文件中，而是不在内存中缓存备份数据，边读取表数据边发送备份数据给 mysqldump 客户端，mysqldump 客户端再写入磁盘备份文件中，-opt 选项是默认启用的，因而要启用内存缓冲（先把 表数据查问进去保留在内存中再写到磁盘备份文件），则须要应用 –skip-quick 来跳过疾速备份。

如果您应用最新版本的 mysqldump 备份的文件须要加载到旧版本的实例中，须要应用 –skip-opt 选

项代替 –opt，或者加上 –extended-insert 选项。

目前市面上各种备份的工具档次不穷，我也用过不少，最近在用的是沃趣 QFusion，不仅仅是备份数据，监控，还原，样样都行，仅供参考。