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，不仅仅是备份数据，监控，还原，样样都行，仅供参考。irds.cn