MySQL :: MySQL Shell 8.0 :: 6.1 MySQL AdminAPI

MySQL Shell 8.0 / Using MySQL AdminAPI / MySQL AdminAPI

6.1 MySQL AdminAPI

This section provides an overview of AdminAPI and what you need to know to get started.

MySQL Shell includes the AdminAPI, which is accessed through the dba global variable and its associated methods. The dba variable's methods provide operations which enable you to deploy, configure, and administer InnoDB Cluster and InnoDB ReplicaSet. For example, use the dba.createCluster() method to create an InnoDB Cluster. In addition, AdminAPI supports administration of some MySQL Router related tasks, such as creating and updating users that enable you to integrate your InnoDB Cluster and InnoDB ReplicaSet.

MySQL Shell provides two scripting language modes, JavaScript and Python, in addition to a native SQL mode. Throughout this guide MySQL Shell is used primarily in JavaScript mode. When MySQL Shell starts it is in JavaScript mode by default. Switch modes by issuing \js for JavaScript mode, and \py for Python mode. Ensure you are in JavaScript mode by issuing the \js.

Important

MySQL Shell enables you to connect to servers over a socket connection, but AdminAPI requires TCP connections to a server instance. Socket based connections are not supported in AdminAPI.

This section assumes familiarity with MySQL Shell, see MySQL Shell 8.0 (part of MySQL 8.0) for further information. MySQL Shell also provides online help for the AdminAPI. To list all available dba commands, use the dba.help() method. For online help on a specific method, use the general format object.help('methodname'). For example:

mysql-js> dba.help('getCluster')

Retrieves a cluster from the Metadata Store.

SYNTAX

  dba.getCluster([name][, options])

WHERE

  name: Parameter to specify the name of the cluster to be returned.
  options: Dictionary with additional options.

>trimmed for brevity<

In addition to this documentation, there is developer documentation for all AdminAPI methods in the MySQL Shell JavaScript API Reference or MySQL Shell Python API Reference, available from Connectors and APIs.

This section applies to using InnoDB Cluster or InnoDB ReplicaSet and consists of:

Deployment Scenarios

AdminAPI supports the following deployment scenarios:

Production deployment: if you want to use a full production environment you need to configure the required number of machines and then deploy your server instances to the machines.
Sandbox deployment: if you want to test a deployment before committing to a full production deployment, the provided sandbox feature enables you to quickly set up a test environment on your local machine. Sandbox server instances are created with the required configuration and you can experiment to become familiar with the technologies employed.

Important

A sandbox deployment is not suitable for use in a full production environment.

Installing the Components

How you install the software components required by AdminAPI depends on the type of deployment you intend to use. For a production deployment, install the components to each machine. A production deployment uses multiple remote host machines running MySQL server instances, so you need to connect to each machine using a tool such as SSH or Windows remote desktop to carry out tasks such as installing components. For a sandbox deployment, install the components to a single machine. A sandbox deployment is local to a single machine, therefore the install needs to only be done once on the local machine. The following methods of installing are available:

Downloading and installing the components using the following documentation:

MySQL Server - see Installing and Upgrading MySQL.
MySQL Shell - see Chapter 2, Installing MySQL Shell.
MySQL Router - see Installing MySQL Router.

Important

Always use the matching version of components, for example run MySQL Shell 8.0.25 to administer instances running MySQL 8.0.25 with MySQL Router 8.0.25.

Once you have installed the software required, choose to follow either Section 6.2, “MySQL InnoDB Cluster” or Section 6.3, “MySQL InnoDB ReplicaSet”.

Using Instances Running MySQL 5.7

This documentation assumes you are using MySQL instances running the latest version of MySQL 8, and MySQL Shell 8. AdminAPI also supports using instances running MySQL 5.7, but many of the features described require instances running MySQL 8. The following differences exist for instances running MySQL 5.7:

Instances running MySQL 5.7 do not support SET PERSIST, so they cannot be configured remotely, or automatically, unlike instances running MySQL 8. Instead, when configuring MySQL 5.7 instances, each time you must connect to the instance and use the dba.configureLocalInstance() operation. This operation persists the settings to the instances option file when it is available locally. See Persisting Settings.
Instances running MySQL 5.7 do not support automatic node provisioning, so before joining them to the cluster you must manually synchronize them with the other cluster instances. This means either relying on Group Replication's distributed recovery, which requires binary logs with GTIDs enabled and potentially a long wait when there is a large number of transactions to recover, or using a tool such as MySQL Enterprise Backup to manually copy the data. With the addition of the MySQL Clone plugin in version 8.0, instances can be provisioned by AdminAPI automatically. When you add a version 8.0 instance supporting MySQL Clone, AdminAPI automatically chooses the best way to bring the joining instance into synchrony with the existing instances. For example if the cluster contains a large number of transactions, MySQL Clone is used to recover the data directly, and any transactions processed by the cluster during the clone operation are then synchronized using distributed recovery. You can monitor the progress of the operation directly from MySQL Shell, no other tools are required. This makes tasks such as adding instances to expand the InnoDB Cluster and improve the chances of high availability effortless. See Section 6.2.2.2, “Using MySQL Clone with InnoDB Cluster”.
Instances running MySQL 5.7 are not compatible with InnoDB ReplicaSet. See Section 6.3, “MySQL InnoDB ReplicaSet”.
The InnoDB Cluster topology (whether it runs in single-primary or multi-primary mode) cannot be dynamically changed when using MySQL 5.7 Servers. See Changing a Cluster's Topology.
Instances running MySQL 5.7 are not compatible with the parallel replication applier. See Configuring the Parallel Replication Applier.
Instances running MySQL 5.7 do not support the autoRejoinTries and exitStateAction options, which configure how many times instances try to rejoin a cluster and what happens when an instance leaves. See Configuring Automatic Rejoin of Instances.
Instances running MySQL 5.7 do not support the consistency option. See Configuring Failover Consistency.
Instances running MySQL 5.7 do not support the expelTimeout option, which configures how long the cluster waits before expelling an instance which has lost contact with the other instances.

To use these features, please upgrade your instances to MySQL 8.

Configuring Host Name

In a production deployment, the instances which you use run on separate machines, therefore each machine must have a unique host name and be able to resolve the host names of the other machines which run server instances. If this is not the case, you can:

configure each machine to map the IP of each other machine to a host name. See your operating system documentation for details. This is the recommended solution.
set up a DNS service
configure the report_host variable in the MySQL configuration of each instance to a suitable externally reachable address

AdminAPI supports using IP addresses instead of host names. From MySQL Shell 8.0.18, AdminAPI supports IPv6 addresses if the target MySQL Server version is higher than 8.0.13. When using MySQL Shell 8.0.18 or higher, if all cluster instances are running 8.0.14 or higher then you can use an IPv6 or hostname that resolves to an IPv6 address for instance connection strings and with options such as localAddress, groupSeeds and ipAllowlist. For more information on using IPv6 see Support For IPv6 And For Mixed IPv6 And IPv4 Groups. Previous versions support IPv4 addresses only.

To verify whether the hostname of a MySQL server is correctly configured, execute the following query to see how the instance reports its own address to other servers and try to connect to that MySQL server from other hosts using the returned address:

SELECT coalesce(@@report_host, @@hostname);

Specifying Instances

One of the core concepts of using AdminAPI is understanding connections to the MySQL instances which make up your InnoDB Cluster or InnoDB ReplicaSet. The requirements for connections to the instances when administering, and for the connections between the instances themselves, are:

only TCP/IP connections are supported, using Unix sockets or named pipes is not supported. InnoDB Cluster and InnoDB ReplicaSet are intended to be used in a local area network, running over a wide area network is not recommended.
only classic MySQL protocol connections are supported, X Protocol is not supported.

Tip

Your applications can use X Protocol, this requirement is for administration operations using AdminAPI.

MySQL Shell enables you to work with various APIs, and supports specifying connections as explained in Connecting to the Server Using URI-Like Strings or Key-Value Pairs. You can specify connections using either URI-like strings, or key-value pairs. The Additional Connection parameters are not supported in AdminAPI. This documentation demonstrates AdminAPI using URI-like connection strings. For example, to connect as the user myuser to the MySQL server instance at www.example.com, on port 3306 use the connection string:

myuser@www.example.com:3306

To use this connection string with an AdminAPI operation such as dba.configureInstance(), you need to ensure the connection string is interpreted as a string, for example by surrounding the connection string with either single (') or double (") quote marks. If you are using the JavaScript implementation of AdminAPI issue:

MySQL JS > dba.configureInstance('myuser@www.example.com:3306')

Assuming you are running MySQL Shell in the default interactive mode, you are prompted for your password. AdminAPI supports MySQL Shell's Section 4.4, “Pluggable Password Store”, and once you store the password you used to connect to the instance you are no longer prompted for it.

Persisting Settings

The AdminAPI commands you use to work with an InnoDB Cluster, InnoDB ReplicaSet, and their server instances modify the configuration of the MySQL on the instance. Depending on the way MySQL Shell is connected to an instance and the version of MySQL installed on the instance, these configuration changes can be persisted to the instance automatically. Persisting settings to the instance ensures that configuration changes are retained after the instance restarts, for background information see SET PERSIST. This is essential for reliable usage, for example if settings are not persisted then an instance which has been added to a cluster does not rejoin the cluster after a restart because configuration changes are lost.

Instances which meet the following requirements support persisting configuration changes automatically:

the instance is running MySQL version 8.0.11 or later
persisted_globals_load is set to ON
the instance has not been started with the --no-defaults option

Instances which do not meet these requirements do not support persisting configuration changes automatically, and when AdminAPI operations result in changes to the instance's settings to be persisted you receive warnings such as:

WARNING: On instance 'localhost:3320' membership change cannot be persisted since MySQL version 5.7.21
does not support the SET PERSIST command (MySQL version >= 8.0.5 required). Please use the
<Dba>.configureLocalInstance command locally to persist the changes.

When AdminAPI commands are issued against the MySQL instance which MySQL Shell is currently running on, in other words the local instance, MySQL Shell persists configuration changes directly to the instance. On local instances which support persisting configuration changes automatically, configuration changes are persisted to the instance's mysqld-auto.cnf file and the configuration change does not require any further steps. On local instances which do not support persisting configuration changes automatically, you need to make the changes locally, see Configuring Instances with dba.configureLocalInstance().

When run against a remote instance, in other words an instance other than the one which MySQL Shell is currently running on, if the instance supports persisting configuration changes automatically, the AdminAPI commands persist configuration changes to the instance's mysql-auto.conf option file. If a remote instance does not support persisting configuration changes automatically, the AdminAPI commands can not automatically configure the instance's option file. This means that AdminAPI commands can read information from the instance, for example to display the current configuration, but changes to the configuration cannot be persisted to the instance's option file. In this case, you need to persist the changes locally, see Configuring Instances with dba.configureLocalInstance().

Retrieving a Handler Object

When you are working with AdminAPI, you use a handler object which represents the InnoDB Cluster or InnoDB ReplicaSet. You assign this object to a variable, and then use the operations available to monitor and administer the InnoDB Cluster or InnoDB ReplicaSet. To be able to retrieve the handler object, you establish a connection to one of the instances which belong to the InnoDB Cluster or InnoDB ReplicaSet. For example, when you create a cluster using dba.createCluster(), the operation returns a Cluster object which can be assigned to a variable. You use this object to work with the cluster, for example to add instances or check the cluster's status. If you want to retrieve a cluster again at a later date, for example after restarting MySQL Shell, use the dba.getCluster([name],[options]) function. For example:

mysql-js> var cluster1 = dba.getCluster()

Similarly, use the dba.getReplicaSet() operation to retrieve an InnoDB ReplicaSet. For example:

mysql-js> var replicaset1 = dba.getReplicaSet()

If you do not specify a name then the default object is returned. By default MySQL Shell attempts to connect to the primary instance when you retrieve a handler. Set the connectToPrimary option to configure this behavior. If connectToPrimary is true and the active global MySQL Shell session is not to a primary instance, MySQL Shell queries for the primary instance. If there is no quorum in a cluster, the operation fails. If connectToPrimary is false, the retrieved object uses the active session, in other words the same instance as the MySQL Shell's current global session. If connectToPrimary is not specified, MySQL Shell treats connectToPrimary as true, and falls back to connectToPrimary being false.

To force connecting to a secondary, establish a connection to the secondary instance and use the connectToPrimary option by issuing:

mysql-js> shell.connect(secondary_member)
mysql-js> var cluster1 = dba.getCluster(testCluster, {connectToPrimary:false})

Tip

Remember that secondary instances have super_read_only=ON, so you cannot write changes to them.

Creating User Accounts for Administration

The user account used to administer an instance does not have to be the root account, however the user needs to be assigned full read and write privileges on the metadata tables in addition to full MySQL administrator privileges (SUPER, GRANT OPTION, CREATE, DROP and so on). In this procedure the user icadmin is shown in InnoDB Cluster examples, and rsadmin in InnoDB ReplicaSet examples.

Important

The user name and password of an administrator must be the same on all instances.

In version 8.0.20 and later, use the setupAdminAccount(user) operation to create or upgrade a MySQL user account with the necessary privileges to administer an InnoDB Cluster or InnoDB ReplicaSet. To use the setupAdminAccount() operation, you must be connected as a MySQL user with privileges to create users, for example as root. The setupAdminAccount(user) operation also enables you to upgrade an existing MySQL account with the necessary privileges before a dba.upgradeMetadata() operation.

The mandatory user argument is the name of the MySQL account you want to create or upgrade to be used to administrator the account. The format of the user names accepted by the setupAdminAccount() operation follows the standard MySQL account name format, see Specifying Account Names. The user argument format is username[@host] where host is optional and if it is not provided it defaults to the % wildcard character.

For example, to create a user named icadmin to administer an InnoDB Cluster assigned to the variable myCluster, issue:

mysql-js> myCluster.setupAdminAccount('icadmin')

Missing the password for new account icadmin@%. Please provide one.
Password for new account: ********
Confirm password: ********

Creating user icadmin@%.
Setting user password.
Account icadmin@% was successfully created.

If you already have an administration user, for example created with a version prior to 8.0.20, use the update option with the setupAdminAccount() operation to upgrade the privileges of the existing user. This is relevant during an upgrade, to ensure that the administration user is compatible. For example, to upgrade the user named icadmin issue:

mysql-js> myCluster.setupAdminAccount('icadmin', {'update':1})
Updating user icadmin@%.
Account icadmin@% was successfully updated.

In versions prior to 8.0.20, the preferred method to create users for administration is using the clusterAdmin option with the dba.configureInstance() operation. The clusterAdmin option must be used with a MySQL Shell connection based on a user which has the privileges to create users with suitable privileges, in this example the root user is used. For example:

mysql-js> dba.configureInstance('root@ic-1:3306', {clusterAdmin: "'icadmin'@'ic-1%'"});

The format of the user names accepted by the setupAdminAccount() operation and the clusterAdmin option follows the standard MySQL account name format, see Specifying Account Names.

If only read operations are needed (such as for monitoring purposes), an account with more restricted privileges can be used. See Configuring Users for AdminAPI.

Verbose Logging

When working with a production deployment it can be useful to configure verbose logging for MySQL Shell. For example, the information in the log can help you to find and resolve any issues that might occur when you are preparing server instances to work as part of InnoDB Cluster. To start MySQL Shell with a verbose logging level, use the --log-level option:

shell> mysqlsh --log-level=DEBUG3

The DEBUG3 level is recommended, see --log-level for more information. When DEBUG3 is set the MySQL Shell log file contains lines such as Debug: execute_sql( ... ) which contain the SQL queries that are executed as part of each AdminAPI call. The log file generated by MySQL Shell is located in ~/.mysqlsh/mysqlsh.log for Unix-based systems; on Microsoft Windows systems it is located in %APPDATA%\MySQL\mysqlsh\mysqlsh.log. See Chapter 9, MySQL Shell Logging and Debug for more information.

In addition to enabling the MySQL Shell log level, you can configure the amount of output AdminAPI provides in MySQL Shell after issuing each command. To enable the amount of AdminAPI output, in MySQL Shell issue:

mysql-js> dba.verbose=2

This enables the maximum output from AdminAPI calls. The available levels of output are:

0 or OFF is the default. This provides minimal output and is the recommended level when not troubleshooting.
1 or ON adds verbose output from each call to the AdminAPI.
2 adds debug output to the verbose output providing full information about what each call to AdminAPI executes.

MySQL Shell can optionally log the SQL statements used by AdminAPI operations (with the exception of sandbox operations), and can also display them in the terminal as they are executed. To configure MySQL Shell to do this, see Section 9.4, “Logging AdminAPI Operations”.

Finding the Primary

When you are working with a single-primary InnoDB Cluster or an InnoDB ReplicaSet, you need to connect to the primary instance for administration tasks so that configuration changes can be written to the metadata. To find the current primary you can:

use the --redirect-primary option at MySQL Shell start up to ensure that the target server is part of an InnoDB Cluster or InnoDB ReplicaSet. If the target instance is not the primary, MySQL Shell finds the primary and connects to it.
use the shell.connectToPrimary([instance, password]) operation (added in version 8.0.20), which checks whether the target instance belongs to a cluster or ReplicaSet. If so, MySQL Shell opens a new session to the primary, sets the active global MySQL Shell session to the established session and returns it.

If an instance is not provided, the operation attempts to use the active global MySQL Shell session. If an instance is not provided and there is no active global MySQL Shell session, an exception is thrown. If the target instance does not belong to a cluster or ReplicaSet the operation fails with an error.
use the status operation, find the primary in the result, and manually connect to that instance.

Scripting AdminAPI

In addition to the interactive mode illustrated in this section, MySQL Shell supports running scripts in batch mode. This enables you to automate processes using AdminAPI with scripts written in JavaScript or Python, which can be run using MySQL Shell's --file option. For example:

shell> mysqlsh --file setup-innodb-cluster.js

Note

Any command line options specified after the script file name are passed to the script and not to MySQL Shell. You can access those options using the os.argv array in JavaScript, or the sys.argv array in Python. In both cases, the first option picked up in the array is the script name.

The contents of an example script file is shown here:

print('InnoDB Cluster sandbox set up\n');
print('==================================\n');
print('Setting up a MySQL InnoDB Cluster with 3 MySQL Server sandbox instances,\n');
print('installed in ~/mysql-sandboxes, running on ports 3310, 3320 and 3330.\n\n');

var dbPass = shell.prompt('Please enter a password for the MySQL root account: ', {type:"password"});

try {
   print('\nDeploying the sandbox instances.');
   dba.deploySandboxInstance(3310, {password: dbPass});
   print('.');
   dba.deploySandboxInstance(3320, {password: dbPass});
   print('.');
   dba.deploySandboxInstance(3330, {password: dbPass});
   print('.\nSandbox instances deployed successfully.\n\n');

   print('Setting up InnoDB Cluster...\n');
   shell.connect('root@localhost:3310', dbPass);

   var cluster = dba.createCluster("prodCluster");

   print('Adding instances to the Cluster.');
   cluster.addInstance({user: "root", host: "localhost", port: 3320, password: dbPass});
   print('.');
   cluster.addInstance({user: "root", host: "localhost", port: 3330, password: dbPass});
   print('.\nInstances successfully added to the Cluster.');

   print('\nInnoDB Cluster deployed successfully.\n');
} catch(e) {
   print('\nThe InnoDB Cluster could not be created.\n\nError: ' +
   + e.message + '\n');
}

AdminAPI is also supported by MySQL Shell's Section 5.8, “API Command Line Interface”. This enables you to easily integrate AdminAPI into your environment. For example, to check the status of an InnoDB Cluster using the sandbox instance listening on port 1234:

$ mysqlsh root@localhost:1234 -- cluster status

This maps to the equivalent command in MySQL Shell:

mysql-js> cluster.status()

PREV HOME UP NEXT