MySQL Shell 8.0  / Customizing MySQL Shell  /  Configuring MySQL Shell Options

13.4 Configuring MySQL Shell Options

You can configure MySQL Shell to match your preferences, for example to start up to a certain programming language or to provide output in a particular format. Configuration options can be set for the current session only, or options can be set permanently by persisting changes to the MySQL Shell configuration file. Online help for all options is provided. You can configure options using the MySQL Shell\option command, which is available in all MySQL Shell modes for querying and changing configuration options. Alternatively in JavaScript and Python modes, use theshell.options object.

Valid Configuration Options

The following configuration options can be set using either the\option command orshell.options scripting interface:

optionNameDefaultValueTypeEffect
autocomplete.nameCachetruebooleanEnable database name caching for autocompletion.
batchContinueOnErrorfalseboolean (READ ONLY)In SQL batch mode, force processing to continue if an error is found. Set totrue by adding--force on the command line. SeeAppendix A,MySQL Shell Command Reference.
connectTimeout10float greater than 0The time in seconds to wait before the connection of any session not using AdminAPI times out.
credentialStore.excludeFiltersemptyarrayAn array of URLs for which automatic password storage is disabled, supports glob characters* and?.
credentialStore.helperDepends on platformstringName of the credential helper used to fetch or store passwords. A special valuedefault is supported to use the platform's default helper. The special value>disabled< disables the credential store.
credentialStore.savePasswordspromptstringControls automatic password storage, supported values:always,prompt ornever.
dba.connectTimeout5float greater than 0The time in seconds to wait before the connection of any session using AdminAPI times out.

dba.connectivityChecks

true

boolean

Defines if connectivity checks are performed forcluster.addInstance(),clusterSet.createReplicaCluster(), andreplicaSet.addInstance(), using the defined SSL configuration.

If an SSL error occurs, the command stops and an error is returned.

dba.gtidWaitTimeout60integer greater than 0The time in seconds to wait for GTID transactions to be applied, when required by AdminAPI operations (seeSection 7.9, “Modifying or Dissolving an InnoDB Cluster”).
logFilePath to the MySQL Shell log file.stringDisplays the path to the MySQL Shell log file. (Read-only) This value can only be changed from the command line, using the--log-file=path/to/logfile.log option. SeeAppendix A,MySQL Shell Command Reference.
dba.logSql0integer ranging from 0 to 2(Deprecated in MySQL Shell 8.0.30. UselogSql instead.) Log SQL statements that are executed by AdminAPI operations (seeChapter 12,MySQL Shell Logging and Debug).
dba.restartWaitTimeout60integer greater than 0The time in seconds to wait for transactions to be applied during a recovery operation. Use to configure a longer timeout when a joining instance has to recover a large amount of data. SeeSection 7.4.6, “Using MySQL Clone with InnoDB Cluster”).
defaultCompressfalsebooleanRequest compression for information sent between the client and the server in every global session. Affects classic MySQL protocol connections only (seeSection 4.3.7, “Using Compressed Connections”).
defaultModeNonestring (sql, js or py)The mode to use when MySQL Shell is started (SQL, JavaScript or Python).
devapi.dbObjectHandlestruebooleanEnable table and collection name handles for the X DevAPIdb object.
history.autoSavefalsebooleanSave (true) or clear (false) entries in the MySQL Shell code history when you exit the application (seeSection 5.5, “Code History”).
history.maxSize1000integerThe maximum number of entries to store in the MySQL Shell code history.
history.sql.ignorePattern*SELECT*:SHOW*stringStrings that match these patterns are not added to the MySQL Shell code history.
history.sql.syslogfalsebooleanSend interactive SQL statements to the operating system’s system logging facility (seeSection 12.3, “System Logging for User SQL Statements”).
interactivetrueboolean (READ ONLY)Enable interactive mode.
logLevelinfointeger ranging from 1 to 8 or any of none, internal, error, warning, info, debug, debug2, debug3, respectivelySet a logging level for the application log (seeChapter 12,MySQL Shell Logging and Debug).
logSqlerrorstring (off, error, on, all)Log SQL statements that are executed by MySQL Shell operations (seeSection 12.4, “MySQL Shell SQL Logging”). This log option does not log user SQL statements executed interactively from the SQL mode, only SQL statements executed by MySQL Shell operations. Logs all SQL statements except those defined in thelogSql.ignorePatternUnsafe andlogSql.ignorePattern options.

  • off: no SQL statements are logged.

  • error: only SQL statements with error messages are logged when an error occurs.

  • on: logs all SQL statements except those defined in thelogSql.ignorePatternUnsafe andlogSql.ignorePattern options.

  • all: logs all SQL statements except those defined inlogSql.ignorePatternUnsafe.

logSql.ignorePattern*SELECT*:*SHOW*stringColon-separated list of glob pattern to filter out of SQL statements logged bylogSql (seeSection 12.4, “MySQL Shell SQL Logging”).
logSql.ignorePatternUnsafe*IDENTIFIED*:*PASSWORD*stringColon-separated list of statement patterns to filter out of SQL statements logged bylogSql (seeSection 12.4, “MySQL Shell SQL Logging”).
mysqlPluginDirMySQL Shell's MySQL plugin directory. That is, thelib/mysql/plugins directory of your MySQL Shell installation on Linux platforms, andlib\mysql\plugins on Windows platforms.stringSet a persistent path to a plugin directory.
oci.configFileThe default location for your platform.stringSet a persistent path to an OCI CLI config file.
oci.profileDEFAULTstringSpecify which profile to use in the OCI CLI config file.
pagerNonestringUse the specified external pager tool to display text and results. Command-line arguments for the tool can be added (seeSection 4.6, “Using a Pager”).
passwordsFromStdinfalsebooleanRead passwords fromstdin instead of terminal.
resultFormattablestring (table, tabbed, vertical, json | json/pretty, ndjson | json/raw, json/array)The default output format for printing result sets (seeSection 5.7, “Output Formats”).
sandboxDirDepends on platformstringThe sandbox directory. On Windows, the default isC:\Users\MyUser\MySQL\mysql-sandboxes, and on Unix systems, the default is$HOME/mysql-sandboxes.
showColumnTypeInfofalsebooleanIn SQL mode, display column metadata for result sets.
showWarningstruebooleanIn SQL mode, automatically display SQL warnings if any.
ssh.bufferSize10240integer greater than 0The buffer size in bytes for data transfer through an SSH tunnel (seeSection 4.3.6, “Using an SSH Tunnel”).
ssh.configFileemptystringThe path to a custom SSH configuration file that replaces the standard SSH configuration file~/.ssh/config as the default for SSH tunneling (seeSection 4.3.6, “Using an SSH Tunnel”).
useWizardstrueboolean(Read-only) Indicates if interactive prompting and wizards are enabled by default
verbose0integer ranging from 0 to 4Enable verbose output to the console and set a level of detail (seeChapter 12,MySQL Shell Logging and Debug).
Note

String values are case-sensitive.

Options listed as“READ ONLY” cannot be modified.

TheoutputFormat option is now deprecated. UseresultFormat instead.

Using the\option Command

The MySQL Shell\option command enables you to query and change configuration options in all modes, enabling configuration from SQL mode in addition to JavaScript and Python modes.

The command is used as follows:

  • \option -h, --help [filter] - print help for options matchingfilter.

  • \option -l, --list [--show-origin] - list all the options.--show-origin augments the list with information about how the value was last changed, possible values are:

    • Command line

    • Compiled default

    • Configuration file

    • Environment variable

    • User defined

  • \optionoption_name - print the current value of the option.

  • \option [--persist]option_namevalue orname=value - set the value of the option and if--persist is specified save it to the configuration file.

  • \option --unset [--persist] <option_name> - reset option's value to default and if--persist is specified, removes the option from the MySQL Shell configuration file.

Note

The value ofoption_name andfilter are case-sensitive.

SeeValid Configuration Options for a list of possible values foroption_name.

Using theshell.options Configuration Interface

Theshell.options object is available in JavaScript and Python mode to change MySQL Shell option values. You can use specific methods to configure the options, or key-value pairs as follows:

MySQL JS > shell.options['history.autoSave']=1

In addition to the key-value pair interface, the following methods are available:

  • shell.options[optionName]: lists the current value of the option.

  • shell.options.set(optionName,value): sets theoptionName tovalue for this session, the change is not saved to the configuration file.

  • shell.options.setPersist(optionName,value): sets theoptionName tovalue for this session, and saves the change to the configuration file. In Python mode, the method isshell.options.set_persist.

  • shell.options.unset(optionName): resets theoptionName to the default value for this session, the change is not saved to the configuration file.

  • shell.options.unsetPersist(optionName): resets theoptionName to the default value for this session, and saves the change to the configuration file. In Python mode, the method isshell.options.unset_persist.

Option names are treated as strings, and as such should be surrounded by' characters. SeeValid Configuration Options for a list of possible values foroptionName.

Use the commands to configure MySQL Shell options as follows:

MySQL JS > shell.options.set('history.maxSize', 5000)MySQL JS > shell.options.setPersist('useWizards', 'true')MySQL JS > shell.options.setPersist('history.autoSave', 1)

Return options to their default values as follows:

MySQL JS > shell.options.unset('history.maxSize')MySQL JS > shell.options.unsetPersist('useWizards')

Configuration File

The MySQL Shell configuration file stores the values of the option to ensure they are persisted across sessions. Values are read at startup and when you use the persist feature, settings are saved to the configuration file.

The location of the configuration file is the user configuration path and the file is namedoptions.json. Assuming that the default user configuration path has not been overridden by defining the environment variableMYSQLSH_USER_CONFIG_HOME, the path to the configuration file is:

  • on Windows%APPDATA%\MySQL\mysqlsh

  • on Unix~/.mysqlsh where~ represents the user's home directory.

The configuration file is created the first time you customize a configuration option. This file is internally maintained by MySQL Shell and should not be edited manually. If an unrecognized option or an option with an incorrect value is found in the configuration file on startup, MySQL Shell exits with an error.