11.7.1 collectDiagnostics Utility

The debugging and diagnostics utility util.debug.collectDiagnostics() enables you to collect diagnostic data on your MySQL server.

• About the Utility

• Requirements and Restrictions

• Running the Utility

• Options for Collecting Diagnostics

About the Utility

The diagnostic report is generated as a zip file to either the local directory or a specified path.

util.debug.collectDiagnostics() enables you to collect raw diagnostic data from standalone servers, members of replication topologies, InnoDB Clusters, and MySQL HeatWave Service DB Systems.

The utility generates files in both TSV and YAML format.

Requirements and Restrictions

The following requirements apply to exports using the diagnostics collection utility:

• MySQL 5.7 or later is required.

• The utility must be run as root.

Running the Utility

The diagnostics utility has the following signature:

util.debug.collectDiagnostics("path/",{options})

• "path": you can specify a path, filename, or path and filename.

  If a filename is not provided, the file is written to the specified location and the filename mysql-diagnostics-YYYYMMDD-HHMMSS.zip is used.

  If a filename is provided without a path, the file is written to the current directory.

• options: dictionary of options that can be omitted if empty. See Options for Collecting Diagnostics for the available options.

  If options are not defined, the utility generates a default set of diagnostics. Each option adds one or more reports to the output.

The following example, run on April 6th, 2022, at 10:02:06AM, generates a default set of diagnostics, mysql-diagnostics-20220406-100206.zip, in the C:/Temp/ directory:

util.debug.collectDiagnostics("C:/Temp/")

The following example generates a default set of diagnostics in a file named myDiagnostics.zip in the C:/Temp/ directory:

util.debug.collectDiagnostics("C:/Temp/myDiagnostics.zip")

The trailing forward slash is required to define a path. If you omit it, the utility creates a file named Temp.zip in the named path. C:/Temp.zip for example. It is not possible to overwrite an existing file.

Host information (host_info) is collected from the localhost, only. It is not possible to collect host information from a remote host. If your MySQL server is running on a remote host, only the MySQL server information is collected.

On Microsoft Windows platforms, host information is collected using the MSInfo utility. This spawns an additional progress dialog while the utility is running.

Options for Collecting Diagnostics

allMembers: [true|false]

Default false. If set to true, generates diagnostics for all members of a managed topology, such as InnoDB Cluster, and pings each member of the topology. Each diagnostic is prefixed with a number, 1 (one) for the server MySQL Shell is connected to, and incremented for each member detected.

Ping results (ping.txt) are generated only if Shell is connected to a member of the topology on the localhost. It is not possible to request ping results from a remote host.

allMembers: true does not create additional reports. All collected data is included in the default reports.

innodbMutex: [true|false]

Default false. If set to true, collects the output of SHOW ENGINE INNODB MUTEX.

This option generates the following additional report:

• innodb_mutex: lists the output of SHOW ENGINE INNODB MUTEX.

Note

This option can impact performance.

schemaStats: [true|false]

Default false. If set to true, collects schema size statistics.

This option generates the following additional report:

• schema_object_overview: lists the contents of the schema_object_overview view.

• top_biggest_tables: lists the largest tables on the connected server and the slow performance indicators.

slowQueries: [true|false]

Default false. If set to true, collects slow query information from the The Slow Query Log.

This option requires you to enable slow_log on the target server and configure its output to TABLE.

This option generates the following additional report:

• slow_log: lists the contents of the mysql.slow_log table.

ignoreErrors: [true|false]

Default false. If set to true, ignores any errors generated by the queries used to generate the diagnostic reports.

customSql: array

One or more SQL statements to run.

For example:

            {"customSql": ["statement1", "statement2", "statement3"]}

customShell: array

One or more shell (DOS, BOURNE, and so on) commands to run.

For example:

            {"customShell": ["command1", "command2", "command3"]}

Important

These commands run with the privileges of the user running MySQL Shell and should be used with caution.