MySQL Shell Dump how to deal with array arguments in non-interactive mode

As you know, the best way to perform logical dump for MySQL is to use MySQL Shell Dump & Load utilities. This is the most powerful option as it can dump and load in parallel (it also include many options to migrate to MDS very easily and supports OCI Object Store too).

One of the main question I receive related to MySQL Shell utility is related to the use of MySQL Shell in non-interactive mode with parameters requiring arrays.

What does that mean ?

For example if you want to dump a MySQL instance but you want to exclude some tables, you have an option called excludeSchemas and it expect an array of strings with the list of schemas you want to exclude in the dumb.

In interactive mode, this is how we use it:

JS> util.dumpInstance("/tmp/dump", {excludeSchemas: 
    ["mysql_innodb_cluster_metadata", "fred_test"], 
    threads: 8, showProgress: true})

However this notation is not supported in the command line:

$ mysqlsh root@localhost -- util dump-instance /tmp/dump \
  --excludeSchemas=["mysql_innodb_cluster_metadata", "fred_test"] \
  --threads=8 --showProgress

This will return an error like this one:

ERROR: Util.dumpInstance: Invalid number of arguments, expected 1 to 2 but got 3

And if we try to do the same with only one schema to exclude, the error will be different:

$ mysqlsh root@localhost -- util dump-instance /tmp/dump \
  --excludeSchemas=["mysql_innodb_cluster_metadata"] \
  --threads=8 --showProgress

And the error is:

ERROR: Util.dumpInstance: Option 'excludeSchemas' is expected to be of type Array, but is String

So how could we achieve such dump only in command line without having to use MySQL Shell interactively ?

The answer is easy, we need to use a script (in JavaScript or Python) like this:

$ mysqlsh --py root@localhost < dump.py

or

$ mysqlsh --js root@localhost < dump.js

I really recommend you to always specify the mode (--js or --py) even if JS is usually the default.

How do these scripts look like ?

dump.py:

util.dump_instance("/tmp/dump", 
  {"excludeSchemas": ["mysql_innodb_cluster_metadata",
  "fred_test"], "threads": 8, "showProgress": True})

dump.js:

util.dumpInstance("/tmp/dump", 
     {excludeSchemas: ["mysql_innodb_cluster_metadata", "fred_test"],
     threads: 8, showProgress: true})

I hope that if you encountered such problem, now you know how to workaround it !