Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.8Kb
Man Pages (Zip) - 329.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  InnoDB Cluster  /  Working with a Production Deployment

20.5 Working with a Production Deployment

When working in a production environment, the MySQL Server instances are running on hosts as part of a network rather than on your local machine as described in previous sections.

The following diagram illustrates the scenario you work with in the following section:

Figure 20.2 Production Deployment

Production Deployment

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). To give the user your_user the privileges needed to administer InnoDB cluster issue:

GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO your_user@'%' WITH GRANT OPTION;
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO your_user@'%' WITH GRANT OPTION;
GRANT SELECT ON performance_schema.* TO your_user@'%' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO your_user@'%' WITH GRANT OPTION;

If only read operations are needed (such as for monitoring purposes), an account with more restricted privileges may be used. To give the user your_user the privileges needed to monitor InnoDB cluster issue:

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO your_user@'%';
GRANT SELECT ON performance_schema.global_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_members TO your_user@'%';

When working with a production deployment it is a good idea to activate verbose logging for MySQL Shell initially. This is helpful in finding and resolving any issues that may arise when you are preparing the server to work as part of InnoDB cluster. To start MySQL Shell with a verbose logging level type:

shell> mysqlsh --log-level=DEBUG3

The log file is located in ~/.mysqlsh/mysqlsh.log for Unix-based systems. On Microsoft Windows systems it is located in %APPDATA%\MySQL\mysqlsh\mysqlsh.log. See Section 18.5, “MySQL Shell Application Log”.

Checking Instance State

The cluster.checkInstanceState() function can be used for the following purposes:

  1. To validate if an instance can be added to the cluster.

  2. The instance is consistent with the seed instances, meaning that it has not executed any transactions which the cluster has not, and can be recovered to the same state as the rest of the cluster.

Checking Instance Configuration

Before creating a cluster from remote instances you need to check that the servers are suitably configured. This can be done using the dba.checkInstanceConfiguration() function. For detailed help on this function you can type dba.help('checkInstanceConfiguration').

The dba.checkInstanceConfiguration() function checks if the server instances are valid for InnoDB cluster usage.

The following demonstrates this:

mysql-js> dba.checkInstanceConfiguration('user@139.59.177.10:3306')

Please provide the password for 'user@139.59.177.10:3306':
Validating instance...

The instance '139.59.177.10:3306' is not valid for Cluster usage.

The following issues were encountered:

- Some configuration options need to be fixed.

+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable or restart the server |
| enforce_gtid_consistency         | OFF           | ON             | Restart the server                               |
| gtid_mode                        | OFF           | ON             | Restart the server                               |
| log_bin                          | 0             | 1              | Restart the server                               |
| log_slave_updates                | 0             | ON             | Restart the server                               |
| master_info_repository           | FILE          | TABLE          | Restart the server                               |
| relay_log_info_repository        | FILE          | TABLE          | Restart the server                               |
| transaction_write_set_extraction | OFF           | XXHASH64       | Restart the server                               |
+----------------------------------+---------------+----------------+--------------------------------------------------+


Please fix these issues , restart the server and try again.

{
  "config_errors": [
    {
      "action": "server_update",
      "current": "CRC32",
      "option": "binlog_checksum",
      "required": "NONE"
    },
    {
      "action": "restart",
      "current": "OFF",
      "option": "enforce_gtid_consistency",
      "required": "ON"
    },
    {
      "action": "restart",
      "current": "OFF",
      "option": "gtid_mode",
      "required": "ON"
    },
    {
      "action": "restart",
      "current": "0",
      "option": "log_bin",
      "required": "1"
    },
    {
      "action": "restart",
      "current": "0",
      "option": "log_slave_updates",
      "required": "ON"
    },
    {
      "action": "restart",
      "current": "FILE",
      "option": "master_info_repository",
      "required": "TABLE"
    },
    {
      "action": "restart",
      "current": "FILE",
      "option": "relay_log_info_repository",
      "required": "TABLE"
    },
    {
      "action": "restart",
      "current": "OFF",
      "option": "transaction_write_set_extraction",
      "required": "XXHASH64"
    }
  ],
  "errors": [],
  "restart_required": true,
  "status": "error"
}
mysql-js>

The report shows the configuration changes required for that instance before it can be added to the cluster.

Configuring the Instance

Once the configuration issues have been identified you can reconfigure your server instance manually. Alternatively, if you can run MySQL Shell directly on the same machine where the instance of MySQL is running, log in to the server and run MySQL Shell on the server. On the server to be configured run:

shell> mysqlsh --log-level=DEBUG3 --uri=root@localhost

The function you use to configure a server for InnoDB cluster use is dba.configureLocalInstance(). This function runs provisioning scripts for you that modify the MySQL server's configuration file.

The dba.configureLocalInstance() function can only configure servers connected to locally. If you try to run dba.configureLocalInstance() remotely you get the following error:

mysql-js> dba.configureLocalInstance('user@139.59.177.10:3306')

Dba.configureLocalInstance: This function only works with local instances (RuntimeError)

If MySQL Shell is started locally, then output will be similar to:

mysql-js> dba.configureLocalInstance('root@localhost:3306')

Please provide the password for 'root@localhost:3306':

Please specify the path to the MySQL configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf
Validating instance...

The configuration has been updated but it is required to restart the server.
{
  "config_errors": [
    {
      "action": "restart",
      "current": "OFF",
      "option": "enforce_gtid_consistency",
      "required": "ON"
    },
    {
      "action": "restart",
      "current": "OFF",
      "option": "gtid_mode",
      "required": "ON"
      },
    {
      "action": "restart",
      "current": "0",
      "option": "log_bin",
      "required": "1"
    },
    {
      "action": "restart",
      "current": "0",
      "option": "log_slave_updates",
      "required": "ON"
    },
    {
      "action": "restart",
      "current": "FILE",
      "option": "master_info_repository",
      "required": "TABLE"
    },
    {
      "action": "restart",
      "current": "FILE",
      "option": "relay_log_info_repository",
      "required": "TABLE"
    },
    {
      "action": "restart",
      "current": "OFF",
      "option": "transaction_write_set_extraction",
      "required": "XXHASH64"
    }
  ],
  "errors": [],
  "restart_required": true,
  "status": "error"
}
mysql-js>

As with dba.checkInstanceConfiguration(), the configuration requirements are identified, but this time the entered configuration file is modified. For the changes to take effect you need to restart the MySQL Server. For example:

shell> sudo service mysql restart
Note

If dba.configureLocalInstance() is used on a instance that is already a member of a cluster, then its Group Replication configuration information is persisted to the server configuration file and a call to rejoinInstance() is not required in that case. When restarted, the instance is automatically joined to the cluster. This is illustrated in the following example:

shell.connect({host: 'localhost', port: 3333, user: 'root', password: 'somePwd'});

var cluster = dba.createCluster('devCluster');

// Here, configureLocalInstance makes sure the instance is configured for Group Replication
dba.configureLocalInstance('localhost:3334', {password:'somePwd', mycnfPath:'some path'})
cluster.addInstance('localhost:3334', {password:'somePwd'})

dba.configureLocalInstance('localhost:3335', {password:'somePwd', mycnfPath:'some path'})
cluster.addInstance('localhost:3335', {password:'somePwd'})

// A restart here, would require using rejoin to put the instance back into the cluster
dba.killSandboxInstance(3335);
dba.startSandboxInstance(3335);
cluster.rejoinInstance('localhost:3335', {password:'somePwd'})

// Calling configureLocalInstance again, since the instance is already part of the cluster
// It will persist the Group Replication server variables
dba.configureLocalInstance('localhost:3335', {password:'somePwd', mycnfPath:'some path'})

// On a new restart, the instance automatically joins the Cluster (no need to rejoinInstance)
dba.killSandboxInstance(3335);
dba.startSandboxInstance(3335);

Once the server has restarted, you can use MySQL Shell again to check the configuration:

mysql-js> dba.checkInstanceConfiguration('root@localhost:3306')

Please provide the password for 'root@localhost:3306':
Validating instance...

The instance 'localhost:3306' is valid for Cluster usage
{
  "status": "ok"
}
mysql-js>

Creating the Cluster

Log in to the remote instance and use MySQL Shell to configure the instance automatically and ensure the configuration changes are persisted.

shell> mysqlsh --uri user@139.59.177.10:3306

Creating a Session to 'user@139.59.177.10:3306'
Enter password: *********
Classic Session successfully established. No default schema selected.

Now create the cluster:

mysql-js> var cluster = dba.createCluster('devCluster');

      A new InnoDB cluster will be created on instance 'user@139.59.177.10:3306'.

      Creating InnoDB cluster 'devCluster' on 'user@139.59.177.10:3306'...
      Adding Seed Instance...

      Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
      At least 3 instances are needed for the cluster to be able to withstand up to
      one server failure.

First, check the instance configuration:

mysql-js> dba.checkInstanceConfiguration('user@139.59.177.10:3306')
  Please provide the password for 'user@139.59.177.10:3306':
  Validating instance...

  The instance '139.59.177.10:3306' is valid for Cluster usage
  {
    "status": "ok"
  }

You can also check the instance state:

mysql-js> cluster.checkInstanceState('user@139.59.177.10:3306')
  Please provide the password for 'user@139.59.177.10:3306':
  Analyzing the instance replication state...

  The instance '139.59.177.10:3306' is valid for the cluster.
  The instance is fully recoverable.

  {
    "reason": "recoverable",
    "state": "ok"
  }

Check the cluster status:

mysql-js> cluster.status()
  {
    "clusterName": "devCluster",
    "defaultReplicaSet": {
      "name": "default",
      "status": "Cluster is NOT tolerant to any failures.",
      "topology": {}
    }
  }

You need to add two more instances to the cluster to make it tolerant to a server failure.

Check the configuration of the next instance to add to the cluster:

mysql-js> dba.checkInstanceConfiguration('user@139.59.177.11:3306')
  Please provide the password for 'user@139.59.177.10:3306':
  Validating instance...

  The instance '139.59.177.11:3306' is valid for Cluster usage
  {
    "status": "ok"
  }

The instance can now be added into the cluster:

mysql-js> cluster.addInstance("user@139.59.177.11:3306");

  Please provide a password for 'user@139.59.177.11:3306': *****

  A new instance will be added to the InnoDB cluster. Depending on the
  amount of data on the cluster this might take from a few seconds to
  several hours.

  Adding instance 139.59.177.11:3306 to the cluster...

  The instance '139.59.177.11:3306' was successfully added to the
  cluster.

The next instance can now be added into the cluster:

mysql-js> cluster.addInstance("user@139.59.177.12:3306");

  Please provide a password for 'user@139.59.177.12:3306': *****

  A new instance will be added to the InnoDB cluster. Depending on the
  amount of data on the cluster this might take from a few seconds to
  several hours.

  Adding instance 139.59.177.12:3306 to the cluster...

  The instance '139.59.177.12:3306' was successfully added to the
  cluster.

Now recheck cluster status.

Creating a Whitelist of Servers

When using the createCluster(), addInstance(), and rejoinInstance() methods you can optionally specify a list of approved servers that belong to the cluster, referred to a whitelist. By specifying the whitelist explicitly in this way you can increase the security of your cluster because only servers in the whitelist can connect to the cluster. By default, if not specified explicitly, the whitelist is automatically set to the private network addresses that the server has network interfaces on. To configure the whitelist, specify the servers to add with the ipWhitelist option when using the method. For example:

mysql-js> c.addInstance("root:guidev!@localhost:3320", {ipWhitelist: "10.157.120.0/24, 192.168.1.110"})

This configures the instance to only accept connections from servers at addresses 10.157.120.0/24 and 192.168.1.110.

Using the ipWhitelist option configures the group_replication_ip_whitelist system variable on the instance.

Restoring a Cluster from Quorum Loss

If a instance (or instances) fail, then a cluster can lose its quorum, which is the ability to vote in a new primary. In this case you can re-establish quorum using the method cluster.forceQuorumUsingPartitionOf(), as shown in the following MySQL Shell example:

  // open session to a cluster

mysql-js> cluster = dba.getCluster("devCluster")

  // The cluster lost its quorum and its status shows
  // "status": "NO_QUORUM"

mysql-js> cluster.forceQuorumUsingPartitionOf("localhost:3310")

  Restoring replicaset 'default' from loss of quorum, by using the partition composed of [localhost:3310]

  Please provide the password for 'root@localhost:3310': ******
  Restoring the InnoDB cluster ...

  The InnoDB cluster was successfully restored using the partition from the instance 'root@localhost:3310'.

  WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset
  are removed or joined back to the group that was restored.

Rebooting a Cluster from a Major Outage

If your cluster suffers from a complete outage, you can ensure it is reconfigured correctly using dba.rebootClusterFromCompleteOutage(). An example of use is as follows:

mysql-js> shell.connect('root@localhost:3310');
mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();

This ensures the cluster is correctly reconfigured after a complete outage. It picks the instance the MySQL Shell is connected to as the new seed instance and recovers the cluster based on the existing metadata of that instance.

It is also possible to provide the cluster name as an input parameter:

mysql-js> var cluster = dba.createCluster("devCluster")
  ...
  ...
mysql-js> var cluster = dba.rebootClusterFromCompleteOutage("devCluster");

If this process fails, and the cluster metadata has become badly corrupted, you may need to drop the metadata and create the cluster again from scratch. You can drop the cluster metadata using dba.dropMetaDataSchema().

Warning

The dba.dropMetaDataSchema() method should only be used as a last resort, when it is not possible to restore the cluster. It can not be undone.

Rescanning a Cluster

If changes to the Group Replication configurations are made without using MySQL Shell you need to rescan your cluster. For example, if you create a cluster with three instances, and then without using MySQL Shell you add a new instance to that Group Replication group, the AdminAPI is not aware of that instance. The same would apply if you removed an instance from a Group Replication group without using MySQL Shell. It is necessary to rescan the cluster with cluster.rescan() in such scenarios.

After the command cluster.rescan() has been run, instances are identified that are newly discovered instances. You are prompted to add each of these newly discovered instances into your cluster as required, or you can choose to ignore them.

Nodes that no longer belong to the cluster or which are unavailable are also reported. In this case you are prompted to remove the instance, or you can later attempt to add it back into the cluster using a command such as cluster.rejoin('instancex.example.com:3340').


User Comments
Sign Up Login You must be logged in to post a comment.