作为MySQL方向从业人员，也多次使用mysqldump这个工具了，但是前几天被人问到mysqldump的备份原理，发现自己是不太清楚的，这几天搬家有点事，趁着周末有时间，便阅读书籍和查阅资料了解了下，以下是学习的部分。

mysqldump是MySQL官方自带的一个备份工具，它的优缺点如下:

优点:

mysqldump基于逻辑备份，将数据以生成SQL语句的形式保存起来，适合于单库，单表数据迁移以及备份恢复，也便于在不同数据库平台之间做数据迁移。

缺点:

mysqldump备份是单线程的，当备份数据量大时，对非事务表长期锁表会对业务造成影响，另外备份数据恢复时间也较长，还可能会导致内存中热点数据刷出问题。

mysqldump的备份流程具体可以拆分为以下七个步骤:

mysqldump备份过程解读:

1.flush tables与flush tables with read lock之间的一些概念：

flush tables会强制关闭所有正在使用的表，并刷新查询缓存

以下情况，flush tables会被阻塞。

1).如果执行flush tables操作前使用lock tables对某表加了表锁，则flush tables命令会被阻塞。

2).如果执行flush tables操作前备份数据库实例上执行DDL语句，则flush tables命令会被阻塞。

3).如果执行flush tables操作前备份数据库实例上有DML大事务执行，则flush tables命令会被阻塞。

flush tables with read lock会关闭所有打开的表，并使用全局读锁锁定整个实例下的所有表。该语句获取的是一个全局读锁，而不是表锁，因此表现与lock tables和unlock tables不同。

以下情况，flush tables with read lock会被阻塞。

1).如果执行flush tables with read lock操作前使用lock tables对某表加了表锁，则flush tables with read lock命令会被阻塞。

2).如果执行flush tables with read lock操作前备份数据库实例上执行DDL语句，则flush tables with read lock命令会被阻塞。

3).如果执行flush tables with read lock操作前备份数据库实例上有DML大事务执行，则flush tables with read lock命令会被阻塞。

lock tables和unlock tables与事务混搭时会相互影响的场景:

以下情况，lock tables和unlock tables与事务混搭时会相互影响:

1).在一个会话中，如果有表使用lock tables命令加锁，当开启事务后该表表锁会被释放，类似执行了unlock tables命令。

2).如果在一个事务中使用了lock tables命令加锁，会导致该事务隐式提交。

3).在事务内使用unlock tables命令无效。

可参考以下表格:

2.为什么要修改隔离级别:

一致性事务快照只支持RR隔离级别。

3.with consistent snapshot子句的作用?

with consistent snapshot子句要求必须在RR隔离级别下才会自动启用，作用相当于START TRANSACTION + SELECT语句，目的是为了在开启事务的同一时刻往MVCC的read view中加入这个事务，使得后续其他事务的DML不会影响该事务的查询结果，即一致性读。如果不使用with consistent子句，在使用START TRANSACTION

语句显示开启事务后，在执行SELECT语句之前有其他的事务发起了DML操作，就会导致该事务查询该表的时候读取的事务与事务开始时间不一致。

4.为什么要设置日志点?

1).将事务回滚到指定的保存点的位置，而不终止事务。

2).释放在设置保存点之后事务持有的MDL锁。

5.每张表的备份过程是怎样的?

每一个表的备份过程如下:

1).先查找出表结构的定义语句

2).对表执行SELECT *语句获取全表的数据。

3).使用表结构中的字段信息和所获取的表数据来生成INSERT语句。

6.mysqldump备份过程中热点数据被刷出缓冲池的问题?

使用mysqldump备份表数据的时候会先执行SELECT语句查询全表数据，在执行SELECT语句的过程中MySQL会不断地把查询到的数据缓存到内存中，然后把备份数据发送给mysqldump客户端，客户端接收到备份数据后再写入到磁盘备份文件中，对数据量大的数据进行备份时可能会导致内存问题和热点数据被刷出InnoDB缓冲池问题，可以使用--quick(或者--opt,它启用--quick)选项来避免热点数据被刷出缓冲池的问题，当使用--quick选项时，查询到的数据不在内存中缓存，而是一边查询一边发送给mysqldump客户端。然后mysqldump客户端再写入磁盘备份文件。

7.将最新版本的mysqldump备份的文件加载到旧版本的实例中?

如果要将最新版本的mysqldump备份的文件加载到旧版本的实例中，需要使用--skip-opt代替--opt选项，或者加上--extented-insert选项。

mysqldump工具的一些参数:

mysqldump工具的参数很多，一一记忆意义不大，结合平常的工作和前人文章，给出一些需要注意的参数。

--force， -f:忽略所有错误，及时在表备份期间发生SQL错误也会继续执行，如果希望忽略特定的错误，可以使用--ignore-error选项。

--default-character-set=charset_name：指定默认字符集，默认使用UTF-8，建议加上，不然可能会出现中文乱码问题，之前我就遇到过。

--master-data：如果备份时要生成整个备份文件的检查点，可以使用该选项，在做主从复制时是一种有效的参考，有1和2两个选项，1为执行，2为不执行。不执行时生成CHANGE MASTER TO语句中会带注释。

--tz-utc：使timestamp数据类型列从不同时区的服务器中导出并重新加载。

--opt:是--add-drop-table,--add-locks,--create-options,disable-keys, --extend-insert,--lock-tables,--quick，--set-charset组合的缩写，提供快速的导出操作，默认启用该选项，如果禁用使用--skip-opt关闭。

--quick：对于备份大表有很好效果，强制mysqldump从服务器一次查询一行数据而不是整张表，mysqldump每次查询都需要先将数据保存在本地缓冲区中，启用该选项后一次查询一行数据就可以尽量不使用缓冲区，但是如果内存够大的话，可以关闭该选项以加快备份速度。

--single-transaction：将事务隔离模式设置为REPEATABLE READ，并在备份数据前向服务器发送SQL语句START TRANSACTION显示开启一个事务快照，在事务快照内进行备份，使得备份的数据与获取事务快照时的数据一致，且不会阻塞任何应用访问服务器，在事务备份期间，不要有alter,create,drop等ddl操作，因为会破坏一致性状态，导致备份失败。

其他一些知识点:

1).MySQL5.7.8及之前，mysqldump默认不备份information_schema,performance_schema,sys这三个系统库，可以使用--databases选项明确选定，对于information_schema和performance_schema，需要使用--skip--lock-tables选项跳过加表锁，否则拒绝执行。

2).启用了GTID的数据库使用mysqldump备份后，无法恢复到未启用GTID的数据库中。

3).mysqldump备份mysql时只备份general_log和slow_query表的建表语句，不备份数据。

4).mysqldump是单线程，在备份过程中非事务表长期锁表对业务造成影响。

5).在使用mysqldump备份时，会话时域的设置会影响具有时域敏感性的时间值的显示，包括NOW(),CURDATE()函数和使用timestamp类型存储的字段，需要指定--skip-tz-utc选项，否则可能会导致备份数据异常，建议使用datetime,与时区无关且存储范围更大。

ITPub博客

使用mysqldump需要知道的一些知识点。

最新文章