6.13 Executing SQL on Topologies

The execute() function enables you to execute SQL queries and statements across all, or a selection of, the members of a topology.

Important

This function must be used with care. For example, altering the value of a required system variable on multiple members could leave your managed topology in an unusable state. Also, this function acquires AdminAPI locks, which means some AdminAPI commands, such as addInstance() are blocked while this function is running.

      topologyType.execute(cmd, instances, options)

• topologyType: represents the Cluster, ClusterSet, or ReplicaSet object.

• cmd: a single MySQL query or statement.

• instances: a keyword or list of addresses where the command is run.

  • all/a: all reachable instances, including Read Replicas, in Clusters and ClusterSets.

  • primary/p: one of the following:

    • the primary instance in a single-primary cluster

    • all primary instances in a multi-primary cluster

    • the primary instance of the primary cluster of a ClusterSet

    • the primary instance in a ReplicaSet

  • secondaries/s:

    • the secondary instances of a single primary cluster, only. Does not include Read Replicas.

    • the secondary instances of all clusters in a ClusterSet.

    • the secondary instances of a ReplicaSet.

  • read-replicas/rr: the Read Replica instances of a Cluster or all Clusters in a ClusterSet.

  The following example shows how to execute SELECT VERSION() against specific cluster members:

  cluster.execute("SELECT VERSION();", ["host:4100", "host:4200"])

• options:

  • exclude: specify instances to exclude. This can be any keyword except all, or a list of addresses.

    The following example shows how to retrieve the values of the variables report_host and report_port from all members except Read Replicas:

    cluster.execute("SHOW VARIABLES WHERE VARIABLE_NAME IN ('REPORT_HOST', 'REPORT_PORT');", "a", {exclude: "rr"})

  • timeout: specifies a number of seconds after which the current statement is canceled. Timeout is only used for SELECT statements and locks originating from LOCK TABLE statements.

  • dryRun: if true, simulates running the command. The command is never run against the target instances. Sessions are established to the target instances, ensuring they can be contacted.

The result is returned as JSON.

A simple SQL statement, SHOW DATABASES, executed on the Cluster primary, returns the following:

cluster.execute("show databases;", "primary")
Statement will be executed at: 'IPAddress:4100'

Executing statement on instances (press Ctrl+C to cancel)... finished.
[
    {
        "executionTime": 0.0006606,
        "instance": {
            "address": "IPAddress:4100",
            "version": "9.0.0"
        },
        "output": [
            {
                "columnNames": [
                    "Database"
                ],
                "rows": [
                    [
                        "information_schema"
                    ],
                    [
                        "mysql"
                    ],
                    [
                        "mysql_innodb_cluster_metadata"
                    ],
                    [
                        "performance_schema"
                    ],
                    [
                        "sys"
                    ]
                ]
            }
        ]
    }
]