MySQL 8.0 Shell Utilities – How can you use them for good ?

Two important MySQL 8.0 Shell Utilities we use regularly are the upgrade checker utility and  JSON import utilityThe upgrade checker utility simplifies the pre-upgrade compatibility audit (whether MySQL server instances are ready for upgrade), We have blogged about MySQL upgrade checker utility here . The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7. From MySQL Shell 8.0.16, the upgrade checker utility can check the configuration file (my.cnf or my.ini) for the server instance. The utility checks for any system variables that are defined in the configuration file but have been removed in the target MySQL Server release, and also for any system variables that are not defined in the configuration file and will have a different default value in the target MySQL Server release. The upgrade checker utility can generate its output in text format, which is the default, or in JSON format. MySQL Shell’s JSON import utility (first introduced in MySQL Shell 8.0.13) to import JSON documents from a file (or FIFO special file) or standard input to a MySQL Server collection or relational table.  To access the utilities from within MySQL Shell, use the util global object, which provides the following functions:

  • checkForServerUpgrade() – An upgrade checker utility that enables you to verify whether MySQL server instances are ready for upgrade.
  • importJSON() – A JSON import utility that enables you to import JSON documents to a MySQL Server collection or table.

MySQL Upgrade Checker Utility

The following command checks the MySQL server at URI root@11.147.56.14:3306 for upgrade to the first MySQL Server 8.0 GA status release (8.0.11). The user password and the configuration file path are supplied as part of the options dictionary, and the output is returned in the default text format:

mysqlsh> util.checkForServerUpgrade('root@11.147.56.14:3306', {"password":"UpgradeMySQL@1867", "targetVersion":"8.0.11", "configPath":"/home/mysql/my.cnf"})

The following command checks the same MySQL server for upgrade to the MySQL Server release number that matches the current MySQL Shell release number (the default), and returns JSON output for further processing:

mysqlsh> util.checkForServerUpgrade('user@11.147.56.14:3306', {"password":"UpgradeMySQL@1867", "outputFormat":"JSON", "configPath":"/home/mysql/my.cnf"})

From MySQL 8.0.13, you can start the upgrade checker utility from the command line using the mysqlsh command interface:

mysqlsh -- util checkForServerUpgrade root@11.147.56.14:3306 --target-version=8.0.15 --output-format=JSON --config-path=/home/mysql/my.cnf

Want help to use MySQL upgrade checker ? Issue util.help:

mysqlsh> util.help("checkForServerUpgrade")

JSON Import Utility

MySQL Shell 8.0.13 introduced MySQL Shell’s JSON import utility, From MySQL Shell 8.0.14, the import utility can process BSON (binary JSON) data types that are represented in JSON documents. The data types used in BSON documents are not all natively supported by JSON, but can be represented using extensions to the JSON format. The import utility can process documents that use JSON extensions to represent BSON data types, convert them to an identical or compatible MySQL representation, and import the data value using that representation. The resulting converted data values can be used in expressions and indexes, and manipulated by SQL statements and X DevAPI functions. The JSON import utility requires an existing X Protocol connection to the server. The utility cannot operate over a classic MySQL protocol connection.

The following examples import the JSON documents in the file /tmp/customers.json to the customer_master collection in the business database:

mysql-js> util.importJson("/JSON/customers.json", {schema: "business", collection: "customer_master"})
mysql-py> util.import_json("/JSON/customers.json", {"schema": "business", "collection": "customer_master"})

The following example has no options specified, so the dictionary is omitted. billing is the active schema for the MySQL Shell session. The utility therefore imports the JSON documents in the file /tmp/billing.json to a collection named billing in the billing database:

mysql-js> \use billing
mysql-js> util.importJson("/billing/billing.json")

Conclusion

Our consultants sometimes spend several weeks (onsite & remote) to define the technical scope of Major upgrades between the versions to avoid unpleased experiences in the future, This task is very expensive for customer and exhaustive for the consultants as they have to spend weeks on MySQL version dependency duedeligenoe , Thanks to MySQL Shell “Upgrade Checker Utility” . ” JSON import utility” was much awaited feature in MySQL, we can now seamlessly do JSON data loading activity more efficiently in a shortest duration.

About MinervaDB Corporation 88 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server, PostgreSQL and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 250 customers worldwide
UA-155183614-1