What’s New in MySQL Shell 8.0.22

MySQL Shell 8.0.22 has just been released and is now available for download.

In addition to bug fixes and minor changes described in the Release Notes, some more significant enhancements were also included.

Dump and Load Utilities

importTable: Custom Data Transformations

The importTable utility now supports applying arbitrary data transformations to imported data. Arbitrary SQL expressions can be specified in the decodeColumns option, which is evaluated for each loaded row by the MySQL server.

This allows for conversion, normalization and/or renormalization of data when importing or migrating tables and the implementation of simple Extract-Transform-Load workflows as MySQL Shell scripts.

exportTable

A new exportTable utility was added as a companion to  importTable. It can be used to export row data from individual tables in one of several different formats, including CSV, TSV, JSON etc.  As with importTable, data can be stored in local files as well as OCI Object Storage buckets.

dumpTables

A new dumpTables utility was added for dumping of individual tables, along with their DDL. Most options supported in dumpInstance and dumpSchemas are also supported in dumpTables. Additionally, it’s possible to load dumps created by dumpTables into a schema of a different name.

Improved Chunking During Dumping and Loading

Bugs that prevented dump and/or load to work in some corner cases were fixed.
In particular, handling of tables with key distributions that produced
excessively large chunks was improved.

Transaction Set (GTID_EXECUTED) Handling

Support for updating GTID_EXECUTED. Dumps created in MySQL Shell 8.0.21 were already storing the value of GTID_EXECUTED when available. In version 8.0.22, a new updateGtidSet option was added.

User Filtering

New includeUsers and excludeUsers options were added to both dumpInstance and loadDump, allowing fine grained control of copied user accounts.

Dumping from MySQL 5.6

It is now possible to dump from MySQL 5.6 and load these dumps into MySQL 5.7 or 8.0. However, dumping of user accounts is not supported when dumping from MySQL 5.6.

Consistent Dumping without FLUSH TABLES WITH READ LOCK

The Shell performs parallel dumping using multiple threads with separate
transactions. FLUSH TABLES WITH READ LOCK is executed to synchronize transactions,
so that all threads can work on the same consistent view of the world. The lock is released immediately after the transactions are started, allowing applications to continue updating the database normally during the dump.

However, execution of that statement is often restricted by lack of privileges and not possible for users in managed cloud services (such as RDS) producing the following error:

It was still possible to perform consistent dumps (e.g. using a single thread or dumping from a read-only replica with the consistent option off). But in 8.0.22, dumpInstance was improved to allow parallel consistent dumps without FTWRL. If FTWRL fails because of  missing grants, dump will automatically fallback to synchronizing transactions by locking tables with the LOCK TABLES ... READ statement.

Support for Complex Dump Workflows using Pre-Authenticated Requests for OCI Object Storage

The OCI Object Storage supports authentication through Pre-Authenticated Requests (PAR), which is an alternative to API Signing Key based authentication. The Dump and Load utilities now support both. PARs enable use cases where the entities producing and loading the dump are not the same and do not have access to the same API Keys or Tenancies, without compromising on security and privacy of the data. Furthermore, PAR support was carefully designed to be convenient and easy to use.

MySQL InnoDB Cluster

As in the MySQL Server, deprecated terminology in replication related features was updated, while keeping backwards compatibility wherever necessary.

You can read about the general change at the MySQL Terminology Updates blog post.

Several bug fixes and minor improvements were done in the AdminAPI for InnoDB clusters. You can read the complete list in the Release Notes.

 

Other Changes

Improved Python Plugin Support

New decorators were added to make it easier to register extension objects and functions in Python.

To register a new extension object simply use the @plugin decorator as follows:

The @plugin decorator will create the extension object and register it for you using the doc string to register the built in help data on the shell.

To register a function into a pre-existing object use the @plugin_function decorator as follows:

The @plugin_function decorator will register the myFunction member into the
myPlugin object using the function definition to gather the parameter names and
types as well as the doc strings for the built in help in the Shell.

Also

  • A new --pym command line option was added, equivalent to the -m option available in the standard python interpreter, allowing Python modules to be called directly from the command line.

 

 

Leave a Reply