1. 摘要 数据作为一家公司的重要资产,其重要程度不言而喻。数据库为数据提供存取服务,担任着重要的角色,如果因数据误删、服务器故障、病毒入侵等原因导致数据丢失或服务不可用,会对公司造成重大损失,所以数据库备份是数据系统中最为重要的一环。
MySQL备份按照类型分为逻辑备份、物理备份、快照备份,本文将通过图文方式对常见的逻辑备份工具的一致性备份流程进行说明,来进一步了解逻辑备份的整个流程是怎么实现的。
具体的信息可以看: 图解MySQL逻辑备份的实现流程。
2. 概念 逻辑备份是数据库对象级的备份,其将数据库里的对象通过SQL查询出来并转储到文件中,包含了用于创建转储对象(数据库,表,触发器、自定义函数、存储过程等)的CREATE语句,和用于将数据加载到表中的INSERT语句。
一致性备份是指在某个时间点,导出的数据与导出的备份文件信息匹配,如果导出了多张表的数据,这些不同表之间的数据都是同一个时间点的数据,MySQL可以通过全局锁(FTWRL,锁表备份)和事务(single-transaction,一致性快照)实现。
锁表备份在Server层实现,备份期间该实例只能进行SELECT操作;事务的一致性快照备份在引擎层实现,支持MVCC引擎表(InnoDB)的备份,期间实例可以对任何表进行DML操作,DDL操作需要根据具体情况分析,本文会对该情况(一致性快照备份)进行说明。
常见的逻辑备份工具:MySQL官方的mysqldump、mysqlpump、mysqlshell的dump方法和第三方开源的mydumper。
3. 工具说明
3.1 mysqldump
- 说明
- 备份命令
mysqldump -udump_user -p -P3306 -h127.0.0.1 --master-data=https://www.it610.com/article/2 --single-transaction --default-character-set=utf8 --all-databases --triggers --routines --events> all.sql
- 备份流程
文章图片
流程说明:
1. 连接数据库,设置当前变量,刷脏页并加一个全局读锁,此刻数据库实例只能SELECT,不能执行其他任何类型的操作(会影响到业务),再设置事务隔离级别和开启一致性快照,并获取BINLOG和GITD信息,此时所有的支持事务的表(INNODB)数据均来自同一时间点。最后再释放全局读锁,此刻数据库实例可以执行任何操作(正常情况下,加全局读锁和释放锁的时间很短)。
2. 获取备份对象的元数据信息并单线程导出表「SLEECT *」。导出表分3种情况:已经导出完成、还未导出和正在导出:
-
- 对于已导出的表,可以做DDL操作(使用SAVEPOINT提前释放导出表的metadata lock);
- 对还未导出的表,INNODB表的DDL操作,能否执行成功取决于DDL的操作方式:no-rebuild方式的DDL执行成功,rebuild方式的DDL执行失败(Table definition has changed),MyISAM引擎的表都能执行成功;
- 对正在导出的表,DDL会出现MDL,此时对该表后续的查询都会出现MDL,导致业务不可用(时间根据备份时长决定),直到该表导出完成。
4. 获取当前的GTID信息,所有对象的导出均转储到一个文件,完成备份。
3.2 mysqlpump
- 说明
mysqlpump的备份是基于表并行的,对于每张表的导出只能是单个线程的,如果一张表非常大,大部分的时间都是消耗在这个表的备份上,并行备份的效果可能就不明显。
- 备份命令
- mysqlpump -udump_user -p -P3306 -h127.0.0.1 --set-gtid-purged=on --default-parallelism=2 --single-transaction --default-character-set=utf8 --exclude-databases=mysql,sys,information_schema,performance_schema > all.sql
- 备份流程
文章图片
流程说明:
1. 多线程连接数据库,设置当前变量,刷脏页并加一个全局读锁,此刻数据库实例只能SELECT,不能执行任何类型的操作(会影响业务),再设置事务隔离级别和开启一致性快照读并获取GITD 信息,此时所有的支持事务的表(INNODB)数据均来自同一时间点。最后再释放全局读锁,此刻数据库实例可以执行任何操作(正常情况下,加全局读锁和释放锁的时间很短)。
2. 获取除表外的其他对象:自定义函数、存储过程、VIEW等。
3. 获取备份对象的元数据信息并多线程导出表「SLEECT col1,col2,...」。导出表分3种情况:已经导出完成、还未导出和正在导出:
-
- 对于已导出的表,不能做DDL操作(不支持SAVEPOINT);
- 对还未导出的表,INNODB表的DDL操作,能否执行成功取决于DDL的操作方式:no-rebuild方式的DDL执行成功,rebuild方式的DDL执行失败(Table definition has changed),MyISAM引擎的表都能执行成功,但如果表结构先于DDL导出,再导出数据,则在还原的时候会报异常(表结构和导出的数据不一致);
- 对正在导出的表,DDL会出现MDL,此时对该表后续的查询都会出现MDL,导致业务不可用(时间根据备份时长决定),直到该表导出完成。
3.3 mydumper
- 说明
- 备份命令
mydumper -u dump_user -p -h 127.0.0.1 -P 3306 --use-savepoints --trx-consistency-only -r 100000 -t 2 -G -R -E -B sbtest -o /data/backup/
--trx-consistency-only:如果不加,则FTWRL的锁在备份完成之后释放。加了会在获取到一致性快照读之后释放(UNLOCK TABLES)。
--rows:-r,分片导出的行数。
--use-savepoints 和 --rows互斥。
- 备份流程
文章图片
流程说明:
1. 连接数据库,设置当前变量,刷脏页并加一个全局读锁,此刻数据库实例只能SELECT,不能执行任何类型的操作(会影响业务),再获取BINLOG和GITD 信息并设置事务隔离级别和开启一致性快照,此时所有的支持事务的表(INNODB)数据均来自同一时间点。最后再释放全局读锁,此刻数据库实例可以执行任何操作(正常情况,加全局读锁和释放锁的时间很短)。
2. 获取备份对象的元数据信息并多线程导出表「SLEECT *」。导出表分3种情况:已经导出完成、还未导出和正在导出:
-
- 对于已导出的表,可以做DDL操作(使用SAVEPOINT提前释放导出表的metadata lock,如果使用分片导出,SAVEPOINT将不可用);
- 对还未导出的表,INNODB表的DDL操作,能否执行成功取决于DDL的操作方式:no-rebuild方式的DDL执行成功,rebuild方式的DDL执行失败(Table definition has changed),MyISAM引擎的表都能执行成功;
- 对正在导出的表,DDL会出现MDL,此时对该表后续的查询都会出现MDL,导致业务不可用(时间根据备份时长决定),直到该表导出完成。
4. 所有对象的导出均转储到多个文件(将表数据分块导出成多个数据文件),完成备份。
3.4 mysqlshell
- 说明
- 备份命令
util.dumpSchemas(['sbtest'],'/data/backup',{"threads":1,"consistent":true})
- 备份流程
文章图片
流程说明:
1. 连接数据库,设置当前变量,刷脏页并加一个全局读锁,此刻数据库实例只能SELECT,不能执行任何类型的操作(会影响业务),再获取BINLOG、GITD和备份对象的元数据信息,接着设置事务隔离级别和启动一致性快照,此时所有的支持事务的表(INNODB)数据均来自同一时间点。最后再释放全局读锁,此刻数据库实例可以执行任何操作(正常情况,加全局读锁和释放锁的时间很短)。
2. 获取备份对象:自定义函数、存储过程、VIEW等。
3. 多线程导出表「SLEECT col1,col2,...」。导出表分3种情况:已经导出完成、还未导出和正在导出:
- 对于已导出的表,不能做DDL操作(不支持SAVEPOINT);
- 对还未导出的表,需要区分有没有PK或UK:
- 没有PK和UK,还要根据DDL的操作方式:no-rebuild方式的DDL执行成功,rebuild方式的DDL执行失败(Table definition has changed),MyISAM引擎的表都能执行成功;
- 有PK或UK,和正在导出的表情况一样,原因是在获取分片信息时需要查询表的最大最小值。
- 对正在导出的表,DDL会出现MDL,此时对该表后续的查询都会出现MDL,导致业务不可用(时间根据备份时长决定),直到该表导出完成。
3.5 小结从上面各逻辑备份的流程中看到,在一致性备份下,所有表的DML操作不影响(除FTWRL短暂的时刻外),而DDL操作都存在一定的风险。所以在逻辑备份期间,要尽量避免DDL操作。
各逻辑备份工具的参数可以看官方文档,对比各工具之后,如表所示:
文章图片
推荐使用的逻辑备份,需要具备的条件:支持一致性备份、行级别的分片多线程导出、导出到多个文件(恢复快),延迟创建索引和savepoint能力。从上面表中看到,mydumper和mysqlshell中的dump方法能满足较多的条件。
4. 总结 【mysql|图文结合,完美解释MySQL逻辑备份的实现流程】希望通过阅读本文,能让大家更清晰的了解逻辑备份的整个实现流程,在选择合适的逻辑备份时有帮助。
原文链接:
https://www.cnblogs.com/zhoujinyi/p/16350709.html
推荐阅读
- Java小案例|Jdbc数据库的连接工具类
- CMU 15-445 数据库课程第三课文字版 - 存储1
- 达蒙数据库语法
- 达蒙数据库迁移
- MYSQL随笔十五 mysql库
- Harmony OSArkUIDemo关系数据库基本使用
- 聊聊集群管理员和数据库管理员的区别
- 进阶学习|进阶学习之优化MySQL
- 安装MySQL