MySQL :: MySQL Shell 8.0

MySQL Document Store

In MySQL 8.0 a new stack of components is introduced, turning MySQL to a competitive Document Store solution.

The MySQL Document Store takes advantage of existing capabilities including:

Efficient and safe data management from the InnoDB storage engine
ACID compliance and Transactional Operations
High Availability with Group Replication and InnoDB cluster

One of the most attractive components introduced in MySQL 8.0 is a development API (DevAPI) that enables you to create JSON document based applications through a fluent API.

The MySQL Shell fully supports this new DevAPI in two flavors:

In addition to the standard DevAPI, the MySQL Shell includes extra features to make interactive development and exploration easier: automatic result display, automatic CRUD and SQL execution and online help.

For more information about the DevAPI please take a look at the X DevAPI User Guide, as well as the shell Quick Start Guides for JavaScript and Python.

Automatic Result Display

When a CRUD or SQL operation is executed, a Result object is returned with information about the operation that completed.

For example a Collection.Find operation returns a DocResult object that can be used to get the documents returned by the Find operation. Smilarly a Table.select operation will return a RowResult object containing table records matching the specified criteria.

Normally, one would have to iterate these result objects to print their contents; but when using the MySQL Shell in interactive mode, result objects returned by DevAPI operations are automatically displayed.

mysql-js> db.Country.select().limit(1).execute()
+------+-------+---------+-------+
| Code | Name  | Capital | Code2 |
+------+-------+---------+-------+
| ABW  | Aruba |     129 | AW    |
+------+-------+---------+-------+
1 row in set (0.0018 sec)

mysql-js> db.CountryInfo.find().limit(1).execute()
[
    {
        "GNP": 828, 
        "IndepYear": null, 
        "Name": "Aruba", 
        "_id": "ABW", 
        "demographics": {
            "LifeExpectancy": 78.4000015258789, 
            "Population": 103000
        }, 
        "geography": {
            "Continent": "North America", 
            "Region": "Caribbean", 
            "SurfaceArea": 193
        }, 
        "government": {
            "GovernmentForm": "Nonmetropolitan Territory of The Netherlands", 
            "HeadOfState": "Beatrix"
        }
    }
]
1 document in set (0.0007 sec)

mysql-js>

mysql-js> db.Country.select().limit(1).execute()

+------+-------+---------+-------+

+------+-------+---------+-------+

| ABW | Aruba | 129 | AW |

+------+-------+---------+-------+

1 row in set (0.0018 sec)

mysql-js> db.CountryInfo.find().limit(1).execute()

[

{

"GNP": 828,

"IndepYear": null,

"Name": "Aruba",

"_id": "ABW",

"demographics": {

"LifeExpectancy": 78.4000015258789,

"Population": 103000

"geography": {

"Continent": "North America",

"Region": "Caribbean",

"SurfaceArea": 193

"government": {

"GovernmentForm": "Nonmetropolitan Territory of The Netherlands",

"HeadOfState": "Beatrix"

}

]

1 document in set (0.0007 sec)

mysql-js>

Automatic CRUD Execution

CRUD and SQL statements on the DevAPI are performed through function calls that can be chained to create complex operations. The operation is only actually executed when the call chain encounters a call to execute().

Similarly to the automatic result processing, when using the Shell in interactive mode, statements that evaluates to a CRUD or SQL operation are processed as if execute() was automatically added to them.

And since the execute() function returns a Result object, it will also be automatically displayed because of the automatic result display feature.

mysql-js> db.Country.select().limit(1)
+------+-------+---------+-------+
| Code | Name  | Capital | Code2 |
+------+-------+---------+-------+
| ABW  | Aruba |     129 | AW    |
+------+-------+---------+-------+
1 row in set (0.0018 sec)

mysql-js> db.CountryInfo.find().limit(1)
[
    {
        "GNP": 828, 
        "IndepYear": null, 
        "Name": "Aruba", 
        "_id": "ABW", 
        "demographics": {
            "LifeExpectancy": 78.4000015258789, 
            "Population": 103000
        }, 
        "geography": {
            "Continent": "North America", 
            "Region": "Caribbean", 
            "SurfaceArea": 193
        }, 
        "government": {
            "GovernmentForm": "Nonmetropolitan Territory of The Netherlands", 
            "HeadOfState": "Beatrix"
        }
    }
]
1 document in set (0.0004 sec)

mysql-js>

mysql-js> db.Country.select().limit(1)

+------+-------+---------+-------+

+------+-------+---------+-------+

| ABW | Aruba | 129 | AW |

+------+-------+---------+-------+

1 row in set (0.0018 sec)

mysql-js> db.CountryInfo.find().limit(1)

[

{

"GNP": 828,

"IndepYear": null,

"Name": "Aruba",

"_id": "ABW",

"demographics": {

"LifeExpectancy": 78.4000015258789,

"Population": 103000

"geography": {

"Continent": "North America",

"Region": "Caribbean",

"SurfaceArea": 193

"government": {

"GovernmentForm": "Nonmetropolitan Territory of The Netherlands",

"HeadOfState": "Beatrix"

}

]

1 document in set (0.0004 sec)

mysql-js>

Online Help

One of the most handy features available on the MySQL Shell is with no doubt the online help system, as it allows the user to get help right away about the different objects and functions available on the DevAPI.

If you have an instance of a specific shell object, information about it can be shown by executing <object>.help().

mysql-js> session.help()

Document Store functionality can be used through this object, in addition to
SQL.

This class allows performing database operations such as:

 - Schema management operations.
 - Access to relational tables.
 - Access to Document Store collections.
 - Enabling/disabling warning generation.
 - Retrieval of connection information.

The following properties are currently supported.

 - uri           Retrieves the URI for the current session.
 - defaultSchema Retrieves the Schema configured as default for the session.
 - currentSchema Retrieves the active schema on the session.


The following functions are currently supported.

 - close            Closes the session.
 - commit           Commits all the operations executed after a call to
                    startTransaction().
 - createSchema     Creates a schema on the database and returns the
                    corresponding object.
 - dropSchema       Drops the schema with the specified name.
 - getCurrentSchema Retrieves the active schema on the session.
 - getDefaultSchema Retrieves the Schema configured as default for the session.
 - getSchema        Retrieves a Schema object from the current session through
                    it's name.
 - getSchemas       Retrieves the Schemas available on the session.
 - getUri           Retrieves the URI for the current session.
 - help             Provides help about this class and it's members
 - isOpen           Returns true if session is known to be open.
 - quoteName        Escapes the passed identifier.
 - releaseSavepoint Removes a savepoint defined on a transaction.
 - rollback         Discards all the operations executed after a call to
                    startTransaction().
 - rollbackTo       Rolls back the transaction to the named savepoint without
                    terminating the transaction.
 - setCurrentSchema Sets the current schema for this session, and returns the
                    schema object for it.
 - setFetchWarnings Enables or disables warning generation.
 - setSavepoint     Creates or replaces a transaction savepoint with the given
                    name.
 - sql              Creates a SqlExecute object to allow running the received
                    SQL statement on the target MySQL Server.
 - startTransaction Starts a transaction context on the server.



mysql-js>

mysql-js> session.help()

Document Store functionality can be used through this object, in addition to

SQL.

This class allows performing database operations such as:

- Schema management operations.

- Access to relational tables.

- Access to Document Store collections.

- Enabling/disabling warning generation.

- Retrieval of connection information.

The following properties are currently supported.

- uri Retrieves the URI for the current session.

- defaultSchema Retrieves the Schema configured as default for the session.

- currentSchema Retrieves the active schema on the session.

The following functions are currently supported.

- close Closes the session.

- commit Commits all the operations executed after a call to

startTransaction().

- createSchema Creates a schema on the database and returns the

corresponding object.

- dropSchema Drops the schema with the specified name.

- getCurrentSchema Retrieves the active schema on the session.

- getDefaultSchema Retrieves the Schema configured as default for the session.

- getSchema Retrieves a Schema object from the current session through

it's name.

- getSchemas Retrieves the Schemas available on the session.

- getUri Retrieves the URI for the current session.

- help Provides help about this class and it's members

- isOpen Returns true if session is known to be open.

- quoteName Escapes the passed identifier.

- releaseSavepoint Removes a savepoint defined on a transaction.

- rollback Discards all the operations executed after a call to

startTransaction().

- rollbackTo Rolls back the transaction to the named savepoint without

terminating the transaction.

- setCurrentSchema Sets the current schema for this session, and returns the

schema object for it.

- setFetchWarnings Enables or disables warning generation.

- setSavepoint Creates or replaces a transaction savepoint with the given

name.

- sql Creates a SqlExecute object to allow running the received

SQL statement on the target MySQL Server.

- startTransaction Starts a transaction context on the server.

mysql-js>

If you want help about a specific operation of the object, simply pass its name the help(“<functionName>”) function to get details about that.

mysql-js> session.help('createSchema')

Creates a schema on the database and returns the corresponding object.

SYNTAX

  <Session>.createSchema(name)

WHERE

  name: A string value indicating the schema name.

RETURNS

  The created schema object.

mysql-js>

mysql-js> session.help('createSchema')

Creates a schema on the database and returns the corresponding object.

SYNTAX

<Session>.createSchema(name)

WHERE

name: A string value indicating the schema name.

RETURNS

The created schema object.

mysql-js>

This makes the MySQL Shell an excellent tool for fast prototyping and a convenient starting point to get familiar with the handling of Document Stores through the DevAPI.

For more details please look at:

Python Quick Start Guide and API Reference
JavaScript Quick Start Guide and API Reference

InnoDB Cluster Improvements

Taking advantage of new features introduced at MySQL 8.0, we have improved the experience of creating and administering InnoDB Clusters through the MySQL Shell Admin API by:

Enabling remote configuration of MySQL servers for use on an InnoDB Cluster.
Configuration changes on remote instances are automatically persisted.
Command line options for automatically connecting the shell session to a primary or secondary member of the cluster.
Command line option to automatically call getCluster() and assign it to a cluster global variable.
New options for better customization of an InnoDB cluster when created.

For additional information about the new InnoDB Cluster features introduced in MySQL Shell 8.0 take a look at MySQL InnoDB Cluster – What’s New in the 8.0 GA Release.

Auto-Completion

One of the main problems of scripting within the MySQL Shell was the need to have a sharp knowledge of the functions available in the different APIs. This made exploring the APIs harder, as you would need to have documentation handy to even know what functions exist.

The introduction of auto-completion solves this problem, besides the obvious improvement of reduced typing. It supports:

Built-in shell commands
SQL keywords (when in SQL mode)
Object member names (when in Python or JavaScript modes)

While typing text, hitting the TAB key will lookup for known possible completions and automatically complete the word. If multiple matches are found, hitting TAB a second time will show all matches.

Some handy usages of autocompletion include:

Getting a list of the available built-in commands.

mysql-js> \
\            \exit        \history     \nowarnings  \py          \reconnect   \source      \status      \warnings
\connect     \help        \js          \option      \quit        \rehash      \sql         \use

mysql-js> \

\ \exit \history \nowarnings \py \reconnect \source \status \warnings

\connect \help \js \option \quit \rehash \sql \use

Getting the members defined for a specific object instance.

mysql-js> session.
close()             currentSchema       getCurrentSchema()  getSchemas()        isOpen()            rollback()          setFetchWarnings()  startTransaction()
commit()            defaultSchema       getDefaultSchema()  getUri()            quoteName()         rollbackTo()        setSavepoint()      uri
createSchema()      dropSchema()        getSchema()         help()              releaseSavepoint()  setCurrentSchema()  sql()

mysql-js> session.

close() currentSchema getCurrentSchema() getSchemas() isOpen() rollback() setFetchWarnings() startTransaction()

commit() defaultSchema getDefaultSchema() getUri() quoteName() rollbackTo() setSavepoint() uri

createSchema() dropSchema() getSchema() help() releaseSavepoint() setCurrentSchema() sql()

Auto-completing a long named function by just typing the starting text.

The introduction of this feature combined with the online help available for the different APIs, enforces the fact that the MySQL Shell is the tool by excellence to work with Document Stores with the DevAPI and InnoDB Clusters with the AdminAPI.

Prompt Themes

A new mechanism to customize the shell prompts has been introduced in MySQL Shell 8.0. Information such as the shell mode (SQL, JavaScript, Python), session information (host, uri, port), active schema and others can be included in the prompt. Colors and text attributes can be used to highlight different parts of the prompt.

This customization information is self-contained in a JSON “theme” file.

The MySQL Shell 8.0 package includes some sample prompt themes that improve the information shown on the default shell prompt.

Details about the prompt themes can be found in the README.prompt file included on the Shell package.

Shell Configuration Interfaces

Shell option handling was greatly improved, making it easier to customize things like:

The format used to print results.
The way auto-completion works.
History Persistence.
Others…

The new \option shell command can now be used to view and change options, optionally persisting them so these changes survive shell restarts. The shell.options object was also extended to support this new functionality.

Command Line History Persistence

Another feature introduced on this version is the ability to persist the command line history of the MySQL Shell across Shell restarts. The command history can be navigated with familiar key strokes, like the up arrow keys for previous command and ^r or ^s to incrementally search the history.

Command line history is not persisted by default to avoid accidentally and unknowingly storing passwords in the history file, but it can be enabled with:

\option --persist<span class="Apple-converted-space"> </span>history.autoSave=1

1

\option --persist<span class="Apple-converted-space"> </span>history.autoSave=1

Other options history options can be customized with the \option command.

Others

Many other features have been introduced on this version, including:

Support for unix sockets.
Support for the new, more secure, caching_sha2_password MySQL authentication plugin.
Improved interruption handling (Ctrl+C).
Improved SSL Support (Using OpenSSL on both Commercial and Community)
Removed AdminAPI requirement for Python 2.7

Resources

For details about the MySQL Shell please take a look at the MySQL Shell User Guide.

For additional details about features introduced at this version take a look at the Release Notes.

Don’t forget to Download it and give it a try, your feedback is very welcome!

You can reach us at #shell channel in https://mysqlcommunity.slack.com/