MySQL shell工具介绍

0    209    1

👉 本文共约9759个字,系统预计阅读时间或需37分钟。

MySQL Shell介绍

什么是 MySQL Shell

MySQL Shell 是 MySQL 的一个高级客户端和代码编辑器,是第二代 MySQL 客户端。第一代 MySQL 客户端即我们常用的 mysql。

相比于 mysql,MySQL Shell 不仅支持 SQL,还具有以下关键特性:

  1. 支持 Python 和 JavaScript 两种语言模式。基于此,我们可以很容易地进行一些脚本开发工作。
  2. 支持 AdminAPI。AdminAPI 可用来管理 InnoDB Cluster、InnoDB ClusterSet 和 InnoDB ReplicaSet。
  3. 支持 X DevAPI。X DevAPI 可对文档( Document )和表( Table )进行 CRUD(Create,Read,Update,Delete)操作。

除此之外,MySQL Shell 还内置了很多实用工具,包括:

  • checkForServerUpgrade:检测目标实例能否升级到指定版本。

  • dumpInstance:备份实例。

  • dumpSchemas:备份指定库。

  • dumpTables:备份指定表。

  • loadDump:恢复通过上面三个工具生成的备份。

  • exportTable:将指定的表导出到文本文件中。只支持单表,效果同 SELECT INTO OUTFILE 一样。

  • importTable:将指定文本的数据导入到表中。

    在线上,如果我们有个大文件需要导入,建议使用这个工具。它会将单个文件进行拆分,然后多线程并行执行 LOAD DATA LOCAL INFILE 操作。不仅提升了导入速度,还规避了大事务的问题。

  • importJson:将 JSON 格式的数据导入到 MySQL 中,譬如将 MongoDB 中通过 mongoexport 导出的数据导入到 MySQL 中。

MySQL Shell 的安装

MySQL Shell 下载地址:https://dev.mysql.com/downloads/shell/

支持各种系统的安装,同 MySQL 一样,提供了多个版本的下载。这里使用 Linux 二进制版本( Linux - Generic )。

MySQL shell模块对象

MySQL shell 中模块对象众多,使用也比较复杂,我当前关注的是 dba、AdminAPI、mysqlx、X DevAPI、mysql 这几个模块、API,足够我用来创建管理 InnoDB Cluster、ReplicaSet、文档型数据就行,下面分别介绍。

全局对象

Shell 启动时,可以使用以下模块和对象:

  • dba:用于InnoDB Cluster、ReplicaSet 管理;
  • mysql:支持使用经典 MySQL 协议连接到 MySQL 服务器,允许执行 SQL;
  • mysqlx:用于通过 MySQL X DevAPI 处理 X 协议会话;
  • os:允许访问允许与操作系统交互的功能;
  • session:代表当前打开的MySQL会话;
  • shell:允许访问通用功能和属性;
  • sys:允许访问系统特定的参数;
  • util:对诸如升级检查器和JSON导入之类的各种工具进行了分组。

AdminAPI

简单的说 AdminAPI 提供管理 InnoDB cluster、InnoDB ReplicaSet 的功能,包含 dba、Cluster、ReplicaSet 对象。使用 \help 命令查看如何使用:

就像帮助信息里说的,可以分别再用 \help xxx 方式查看更具体的帮助文档:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

如果要看以上每个对象、类的命令列表,可以用通配符,比如列出所有 dba 对象的命令:

如果要更详细的某个命令的帮助手册,则可以 \help 后接具体的命令:

可以看出 dba 对象命令可以创建 Innodb Cluster、ReplicaSet。

创建后,dba.getCluster() 和 dba.getReplicaSet() 获取管理对象,然后 Cluster、ReplicaSet 对象提供具体的管理 Innodb Cluster、ReplicaSet 的命令,可以用 \help 命令简单查看:

X DevAPI

X DevAPI 是 mysqlx 模块上包含的函数和类的集合,当 mysql shell 启动时,该模块会自动加载。要使用X DevAPI,必须:

  1. MySQL Server 启用 X Protocol
  2. 连接到 X Protocol 定义的端口

X DevAPI 的简化了将 MySQL 作为文档型数据库存取数据的使用,功能有:

  1. 与启用X协议的MySQL服务器建立会话
  2. 管理 schema
  3. 管理集合
  4. 对集合(文档型数据)和表(关系型数据)的CRUD操作

使用 \help 可以查看:

mysql模块

mysql 模块支持使用经典 MySQL 协议连接到 MySQL 服务器,允许执行 SQL(切换到 SQL 模式)。

比如可以用 help 查看 SQL 命令使用方式:

切换到 SQL 模式,可以执行所有 SQL 命令:

MySQL Shell基本使用

MySQL shell工具介绍

常用命令

命令描述
shell.connect('root@172.16.9.51:3306')连接到实例
var cluster = dba.getCluster()定义集群信息
cluster.status();查看集群状态

配置命令

命令描述
dba.getCluster();查看集群名
cluster.status();查看集群状态
dba.checkInstanceConfiguration('root@172.16.9.52:3306')检查节点状态是否正常
dba.configureLocalInstance();检查/etc/my.cnf是否正常
var cluster = dba.createCluster('myCluster');创建一个名为myCluster的集群
dba.createCluster('st');创建一个名字为st的集群
cluster.addInstance('root@oratest52:3306');集群添加节点

通用命令

命令别名/快捷方式描述
\help\h or ?打印有关MySQL Shell的帮助,或搜索联机帮助。
\quit\q or \exit退出MySQL Shell。
\在SQL模式下,开始多行模式。输入空行时缓存并执行代码。
\status\s显示当前的MySQL Shell状态。
\js将执行模式切换为JavaScript。
\py将执行模式切换为Python。
\sql将执行模式切换为SQL。
\connect\c连接到MySQL服务器。
\reconnect重新连接到同一个MySQL服务器。
\use\u指定要使用的架构。
\source.使用活动语言执行脚本文件。
\warnings\W显示语句生成的任何警告。
\nowarnings\w不要显示语句生成的任何警告。
\history查看和编辑命令行历史记录。
\rehash手动更新自动完成名称缓存。
\option查询和更改MySQL Shell配置选项。
\show使用提供的选项和参数运行指定的报告。
\watch使用提供的选项和参数运行指定的报告,并定期刷新结果。

MySQL Shell Dump & Load 的使用

mysqldump 和 mydumper 是我们常用的两个逻辑备份工具。

无论是 mysqldump 还是 mydumper 都是将备份数据通过 INSERT 的方式写入到备份文件中。

恢复时,myloader( mydumper 中的恢复工具 ) 是多线程导入,且一个 INSERT 语句中包含多条记录,多个 INSERT 操作批量提交。基本上,凡是我们能想到的,有助于提升导入速度的,myloader 都会使用或有选项提供。

单就恢复速度而言,可以说,myloader 就是逻辑恢复工具的天花板。

既然如此,还有办法能继续提升逻辑恢复工具的恢复速度么?毕竟,备份的恢复速度直接影响着灾难发生时数据库服务的 RTO。

答案,有!

这个就是官方在 MySQL Shell 8.0.21 中推出的 Dump & Load 工具,该工具是官方出品,比 mydumper 更快的逻辑备份工具。

与 myloader 不一样的是,MySQL Shell Load 是通过 LOAD DATA LOCAL INFILE 命令来导入数据的。

而 LOAD DATA 操作,按照官方文档的说法,比 INSERT 操作快 20 倍。

util.dumpInstance(outputUrl[, options])

备份实例。其中,outputUrl 是备份目录,不能为空。options 是可指定的选项。

首先,看一个简单的示例。

命令中的 /data/backup/full 是备份目录,目标必须为空,compression: "none" 指的是不压缩,这里设置为不压缩主要是为了方便查看数据文件的内容。线上使用建议开启压缩

接下来我们看看备份目录中的内容。

其中,

  • @.done.json:会记录备份的结束时间,备份集的大小。备份结束时生成。

  • @.json:会记录备份的一些元数据信息,包括备份时的一致性位置点信息:binlogFile,binlogPosition 和 gtidExecuted,这些信息可用来建立复制。

  • @.sql,@.post.sql:这两个文件只有一些注释信息。不过在通过 util.loadDump 导入数据时,我们可以通过这两个文件自定义一些 SQL。其中,@.sql 是数据导入前执行,@.post.sql 是数据导入后执行。

  • sbtest.json:记录 sbtest 中已经备份的表、视图、定时器、函数和存储过程。

  • *.tsv:数据文件。我们看看数据文件的内容。

    TSV 格式,每一行储存一条记录,字段与字段之间用制表符(\t)分隔。

  • sbtest@sbtest1.json:记录了表相关的一些元数据信息,如列名,字段之间的分隔符(fieldsTerminatedBy)等。

  • sbtest@sbtest1.sql:sbtest.sbtest1 的建表语句。

  • sbtest.sql:建库语句。如果这个库中存在存储过程、函数、定时器,也是写到这个文件中。

  • @.users.sql:创建账号及授权语句。默认不会备份 mysql.session,mysql.session,mysql.sys 这三个内部账号。

若mysqlsh在Windows平台,如下:

util.dumpSchemas(schemas, outputUrl[, options])

备份指定库的数据。

用法同 util.dumpInstance 类似。其中,第一个参数必须为数组,即使只需备份一个库,如,

支持的配置大部分与 util.dumpInstance 相同。

从 MySQL Shell 8.0.28 开始,可直接使用 util.dumpInstance 中的 includeSchemas 选项进行指定库的备份。

示例:

util.dumpTables(schema, tables, outputUrl[, options])

备份指定表的数据。

用法同 util.dumpInstance 类似。其中,第二个参数必须为数组,如,

支持的配置大部分与 util.dumpInstance 相同。

从 MySQL Shell 8.0.28 开始,可直接使用 util.dumpInstance 中的 includeTables 选项进行指定表的备份。

util.loadDump(url[, options])

导入通过 dump 命令生成的备份集。如,

命令中的 /data/backup/full 是备份目录,loadUsers: true 是导入账号,默认不会导入

util.dumpInstance 的关键特性

util.dumpInstance 的关键特性如下:

  1. 多线程备份。并发线程数由 threads 决定,默认是 4。
  2. 支持单表 chunk 级别的并行备份,前提是表上存在主键或唯一索引。
  3. 默认是压缩备份。
  4. 支持备份限速。可通过 maxRate 限制单个线程的数据读取速率。

util.loadDump 的关键特性

util.loadDump 的关键特性如下:

  1. 多线程恢复。并发线程数由 threads 决定,默认是 4。

  2. 支持断点续传功能。

    在导入的过程中,会在备份目录生成一个进度文件,用于记录导入过程中的进度信息。

    文件名由 progressFile 指定,默认是 load-progress..progress。

    导入时,如果备份目录中存在 progressFile,默认会从上次完成的地方继续执行。如果要从头开始执行,需将 resetProgress 设置为 true。

  3. 支持延迟创建二级索引。

  4. 支持边备份,边导入。

  5. 通过 LOAD DATA LOCAL INFILE 命令来导入数据。

  6. 如果单个文件过大,util.loadDump 在导入时会自动进行切割,以避免产生大事务。

util.dumpInstance 的备份流程

util.dumpInstance 的备份流程如下图所示。

MySQL shell工具介绍

不难看出,util.dumpInstance 的备份流程与 mysqldump 大致相同,不同的地方主要体现在以下两点:

  1. util.dumpInstance 会加备份锁。备份锁可用来阻塞备份过程中的 DDL。
  2. util.dumpInstance 是并行备份,相对于 mysqldump 的单线程备份,备份效率更高。

util.dumpInstance 的参数解析

util.dumpInstance 的参数可分为如下几类:

过滤相关

以下是过滤相关的选项。

  • excludeSchemas:忽略某些库的备份,多个库之间用逗号隔开,如,

  • includeSchemas:指定某些库的备份。

  • excludeTables:忽略某些表的备份,表必须是 schema.table 的格式,多个表之间用逗号隔开,如,

  • includeTables:指定某些表的备份。

  • events:是否备份定时器,默认为 true。

  • excludeEvents:忽略某些定时器的备份。

  • includeEvents:指定某些定时器的备份。

  • routines:是否备份函数和存储过程,默认为 true。

  • excludeRoutines:忽略某些函数和存储过程的备份。

  • includeRoutines:指定某些函数和存储过程的备份。

  • users:是否备份账号信息,默认为 true。

  • excludeUsers:忽略某些账号的备份,可指定多个账号。

  • includeUsers:指定某些账号的备份,可指定多个账号。

  • triggers:是否备份触发器,默认为 true。

  • excludeTriggers:忽略某些触发器的备份。

  • includeTriggers:指定某些触发器的备份。

  • ddlOnly:是否只备份表结构,默认为 false。

  • dataOnly:是否只备份数据,默认为 false。

并行备份相关

  • chunking:是否开启 chunk 级别的并行备份功能,默认为 true。
  • bytesPerChunk:每个 chunk 文件的大小,默认 64M。
  • threads:并发线程数,默认为 4。

OCI(甲骨文云)相关

  • ocimds:是否检查备份集与 MySQL Database Service(甲骨文云的 MySQL 云服务,简称 MDS )的兼容性,默认为 false,不检查。如果设置为 true,会输出所有的不兼容项及解决方法。不兼容项可通过下面的 compatibility 来解决。

  • compatibility:如果要将备份数据导入到 MDS 中,为了保证与后者的兼容性,可在导出的过程中进行相应地调整。具体来说:

    1. create_invisible_pks:对于没有主键的表,会创建一个隐藏主键:my_row_id BIGINT UNSIGNED AUTO_INCREMENT INVISIBLE PRIMARY KEY。隐藏列是 MySQL 8.0.23 引入的。
    2. force_innodb:将表的引擎强制设置为 InnoDB。
    3. ignore_missing_pks:忽略主键缺失导致的错误,与 create_invisible_pks 互斥,不能同时指定。
    4. skip_invalid_accounts:忽略没有密码,或者使用了 MDS 不支持的认证插件的账号。
    5. strip_definers:去掉视图、存储过程、函数、定时器、触发器中的 DEFINER=account 子句。
    6. strip_restricted_grants:去掉 MDS 中不允许 GRANT 的权限。
    7. strip_tablespaces:去掉建表语句中的 TABLESPACE=xxx 子句。
  • osBucketName,osNamespace,ociConfigFile,ociProfile,ociParManifest,ociParExpireTime:OCI 对象存储相关。

其它选项

  • tzUtc:是否设置 TIME_ZONE = '+00:00',默认为 true。
  • consistent:是否开启一致性备份,默认为 true。若设置为 false,则不会加全局读锁,也不会开启事务的一致性快照。
  • dryRun:试运行。此时只会打印备份信息,不会执行备份操作。
  • maxRate:限制单个线程的数据读取速率,单位 byte,默认为 0,不限制。
  • showProgress:是否打印进度信息,如果是 TTY 设备(命令行终端),则为 true,反之,则为 false。
  • defaultCharacterSet:字符集,默认为 utf8mb4。
  • compression:备份文件的压缩算法,默认为 zstd。也可设置为 gzip 或 none(不压缩)。

util.loadDump 的参数解析

util.loadDump 的参数可分为如下几类:

过滤相关

  • excludeEvents:忽略某些定时器的导入。
  • excludeRoutines:忽略某些函数和存储过程的导入。
  • excludeSchemas:忽略某些库的导入。
  • excludeTables:忽略某些表的导入。
  • excludeTriggers:忽略某些触发器的导入。
  • excludeUsers:忽略某些账号的导入。
  • includeEvents:导入指定定时器。
  • includeRoutines:导入指定函数和存储过程。
  • includeSchemas:导入指定库。
  • includeTables:导入指定表。
  • includeTriggers:导入指定触发器。
  • includeUsers:导入指定账号。
  • loadData:是否导入数据,默认为 true。
  • loadDdl:是否导入 DDL 语句,默认为 true。
  • loadUsers:是否导入账号,默认为 false。注意,即使将 loadUsers 设置为 true,也不会导入当前正在执行导入操作的用户
  • ignoreExistingObjects:是否忽略已经存在的对象,默认为 off。

并行导入相关

  • backgroundThreads:获取元数据和 DDL 文件内容的线程数。备份集如果存储在本地,backgroundThreads 默认和 threads 一致。
  • threads:并发线程数,默认为 4。
  • maxBytesPerTransaction:指定单个 LOAD DATA 操作可加载的最大字节数。默认与 bytesPerChunk 一致。这个参数可用来规避大事务

断点续传相关

  • progressFile:在导入的过程中,会在备份目录生成一个 progressFile,用于记录加载过程中的进度信息,这个进度信息可用来实现断点续传功能。默认为 load-progress..progress。
  • resetProgress:如果备份目录中存在 progressFile,默认会从上次完成的地方继续执行。如果要从头开始执行,需将 resetProgress 设置为 true。该参数默认为 off。

OCI 相关

osBucketName,osNamespace,ociConfigFile,ociProfile。

二级索引相关

  • deferTableIndexes:是否延迟(数据加载完毕后)创建二级索引。可设置:off(不延迟),fulltext(只延迟创建全文索引,默认值),all(延迟创建所有索引)。
  • loadIndexes:与 deferTableIndexes 一起使用,用来决定数据加载完毕后,最后的二级索引是否创建,默认为 true。

其它选项

  • analyzeTables:表加载完毕后,是否执行 ANALYZE TABLE 操作。默认是 off(不执行),也可设置为 on 或 histogram(只对有直方图信息的表执行)。
  • characterSet:字符集,无需显式设置,默认会从备份集中获取。
  • createInvisiblePKs:是否创建隐式主键,默认从备份集中获取。这个与备份时是否指定了 create_invisible_pks 有关,若指定了则为 true,反之为 false。
  • dryRun:试运行。
  • ignoreVersion:忽略 MySQL 的版本检测。默认情况下,要求备份实例和导入实例的大版本一致。
  • schema:将表导入到指定 schema 中,适用于通过 util.dumpTables 创建的备份。
  • showMetadata:导入时是否打印一致性备份时的位置点信息。
  • showProgress:是否打印进度信息。
  • skipBinlog:是否设置 sql_log_bin=0 ,默认 false。这一点与 mysqldump、mydumper 不同,后面这两个工具默认会禁用 Binlog
  • updateGtidSet:更新 GTID_PURGED。可设置:off(不更新,默认值), replace(替代目标实例的 GTID_PURGED), append(追加)。
  • waitDumpTimeout:util.loadDump 可导入当前正在备份的备份集。处理完所有文件后,如果备份还没有结束(具体来说,是备份集中没有生成 @.done.json),util.loadDump 会报错退出,可指定 waitDumpTimeout 等待一段时间,单位秒。

MySQL Shell Dump & Load 的注意事项

  1. 表上存在主键或唯一索引才能进行 chunk 级别的并行备份。字段的数据类型不限。不像 mydumper,分片键只能是整数类型。
  2. 对于不能进行并行备份的表,目前会备份到一个文件中。如果该文件过大,不用担心大事务的问题,util.loadDump 在导入时会自动进行切割。
  3. util.dumpInstance 只能保证 InnoDB 表的备份一致性。
  4. 默认不会备份 information_schema,mysql,ndbinfo,performance_schema,sys。
  5. 备份实例支持 MySQL 5.6 及以上版本,导入实例支持 MySQL 5.7 及以上版本。
  6. 备份的过程中,会将 BLOB 等非文本安全的列转换为 Base64,由此会导致转换后的数据大小超过原数据。导入时,注意 max_allowed_packet 的限制。
  7. 导入之前,需将目标实例的 local_infile 设置为 ON。
  8. 该工具属于客户端工具,生成的文件在客户端。
  9. 导出的时候,导出路径下不能有文件。

在使用时注意:

  1. 通过 dumpInstance,dumpSchemas,dumpTables 生成的备份只能通过 loadDump 来恢复。
  2. 通过 exportTable 生成的备份只能通过 importTable 来恢复。

参考

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

[1] Instance Dump Utility, Schema Dump Utility, and Table Dump Utility: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

[2] MySQL Shell Dump & Load part 1: Demo!: https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-1-demo/

[3] MySQL Shell Dump & Load part 2: Benchmarks: https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-2-benchmarks/

[4] MySQL Shell Dump & Load part 3: Load Dump: https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-3-load-dump/

[5] MySQL Shell Dump & Load part 4: Dump Instance & Schemas: https://dev.mysql.com/blog-archive/mysql-shell-8-0-21-speeding-up-the-dump-process/

[6] Backup/Restore Performance Conclusion: mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup: https://www.percona.com/blog/backup-restore-performance-conclusion-mysqldump-vs-mysql-shell-utilities-vs-mydumper-vs-mysqlpump-vs-xtrabackup/

[7] Optimizing INSERT Statements: https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html

https://zhuanlan.zhihu.com/p/522228513

    头像

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    4 × 2 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部