MySQL Shell utilities for dumping and loading data
MySQL Shell provides some dump utilities working at instance, schema and table level: util.dumpInstance(), util.dumpSchemas(), util.dumpTables() and the counterpart dump loading utility util.loadDump()
The relevant reference docs can be found at https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html
It comes with its own package. It can be installed directly on Windows, MacOS and Linux downloading from the web site (https://dev.mysql.com/downloads/shell/).
The data dump can be saved to an Oracle Cloud bucket or to a set of local files (using outputUrl: FILE://</path/to/dump/dir>). The dump loading can import into a MySQL Database Service DB System (Oracle Cloud) or a MySQL Server instance.
The main strengths (not provided by mysqldump) are:
- compatibility checks and modifications (apparently mostly from/to Oracle Cloud and thus not particularly useful for DBOD users at this stage)
- parallel dumping with multiple threads (in chunks)
- file compression (in chunks)
Parameters to control parallelism/chunking:
- threads: default 4
- chunking: default true
- bytesPerChunk: default "64M"
Parameters to control compression:
- compression: "zstd|gzip|none", default "zstd"
Some interesting benchmarks related with leveraging compression and parallel execution to compare MySQL Shell with some other tools can be consulted at [1]
The dump utility has a dryRun: parameter that can be specified:
...with your chosen set of dump options to show information about what actions would be performed, what items would be dumped and compatibility issues.
The dump consists of both:
- DDL files specifying the schema structure,
- and tab-separated .tsv files containing the data.
You can specify to dump DDL files or data files ONLY and load them separately.
There are also exclude parameters very useful when the majority of objects need to be included in the dump but some are not.
Mysql shell works only on MySQL >= 5.7 destinations. It is normally better to use the latest MySQL Shell version (currently 8.0.24) to benefit from bug fixing and best functionality.
Parameters are passed with a JSON document:
util.dumpInstance(outputUrl[, options])
util.dumpSchemas(schemas, outputUrl[, options])
util.dumpTables(schema, tables, outputUrl[, options])
You can choose whether or not to lock the instance for backup during the dump depending on the data consistency required using the 'consistent' parameter which by default is true and thus has some implications:
Enable (true) or disable (false) consistent data dumps by locking the instance for backup during the dump. When true the utility sets a global read lock using the FLUSH TABLES WITH READ LOCK [2] statement (if the user ID used to run the utility has the RELOAD privilege), or a series of table locks using LOCK TABLES statements (if the user ID does not have the RELOAD privilege but does have LOCK TABLES). The transaction for each thread is started using the statements SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ and START TRANSACTION WITH CONSISTENT SNAPSHOT. When all threads have started their transactions, the instance is locked for backup (as described in LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements) and the global read lock is released.
It is worth noting that by default, all the timestamps in the dump are converted to UTC (which is useful for moving data with multiple TZ or between servers with different TZ).
It is also worth mentioning that:
- MySQL Shell can execute SQL, JavaScript or Python code [3]:
shell> mysqlsh --sql < code.sql
or
shell> mysqlsh < code.js
or
shell> mysqlsh --py < code.py
- MySQL Shell is CL integrated which can provide interesting and useful ways to invoke functions [4], just as an example:
mysqlsh -- util dump-instance my-dump --threads=8
- MySQL Shell can be used for scripting:
#!/usr/local/mysql-shell/bin/mysqlsh --file
print("Hello World\n");
[1] Interesting benchmarks and comparisons with other tools
[2] Interesting information about FLUSH TABLES WITH READ LOCK implications