MySQL Shell 8.0  /  MySQL Shell Utilities  /  Debugging and Diagnostics Utility

11.7 Debugging and Diagnostics Utility

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

util.debug.collectDiagnostics() is introduced in MySQL Shell 8.0.29.

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 Database 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 must define 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 only a filename is provided, 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. See Default Diagnostic Collection for information on the various reports generated. 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")
Note

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.

Options for Collecting Diagnostics

allMembers: [true|false]

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

Ping details (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 details from a remote host.

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

Note

All members of a managed topology, such as InnoDB Cluster, are scanned by default. To deactivate this scan, and only scan the MySQL server to which MySQL Shell is connected, you must explictly set this option to False.

innodbMutex: [true|false]

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

This option generates the following additional report:

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 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.

Default Diagnostic Collection

The following files are generated in the default diagnostic collection:

If you are connected to a managed topology such as InnoDB Cluster, the topology is detected automatically and diagnostics are generated for that topology.

  • Diagnostic information is collected from the tables of the mysql_innodb_cluster_metadata schema:

    • mysql_innodb_cluster_metadata.async_cluster_members

    • mysql_innodb_cluster_metadata.async_cluster_views

    • mysql_innodb_cluster_metadata.clusters

    • mysql_innodb_cluster_metadata.clusterset_members

    • mysql_innodb_cluster_metadata.clusterset_views

    • mysql_innodb_cluster_metadata.clustersets

    • mysql_innodb_cluster_metadata.instances

    • mysql_innodb_cluster_metadata.router_rest_accounts

    • mysql_innodb_cluster_metadata.routers

    • mysql_innodb_cluster_metadata.schema_version