How to Add a New MySQL Server Node to an InnoDB Cluster

Introduction

It is possible to expand the capacity and the availability of an InnoDB Cluster by adding more MySQL servers. For example, a 5-node InnoDB cluster can tolerate up to 2 node failures, while a 3-node InnoDB cluster can tolerate only a single failure.

Objective

This guide will navigate you through the process of integrating a new MySQL server into an existing InnoDB Cluster, leveraging the MySQL Shell to execute the requisite commands. Initially, the cluster encompasses the following servers:

• vinnie-mysql-8-01
• vinnie-mysql-8-02

The node to be introduced is:

• vinnie-mysql-8-03

Preconditions

Before the server integration, it’s important to establish the following prerequisites:

• Existing Cluster: The InnoDB Cluster is operational, with all nodes functioning optimally.
• Network Configurations: The new MySQL server is configured for seamless network integration with the existing cluster nodes.
• MySQL Versions: The new server operates on the identical MySQL version as the other cluster nodes.
• Data Backup: Data backups are conducted regularly and verified for integrity.

Adding a New Node to the InnoDB Cluster

Preparation

• MySQL Installation: Verify MySQL’s installation and proper configuration on the new server, ensuring version consistency with the cluster’s existing nodes.
• Network Configuration: Confirm the new server’s network compatibility and communication capabilities with the existing cluster nodes.

Adding the node

• Connecting to the Cluster: Establish a connection to the MySQL Shell on one of the existing cluster nodes:
  # mysqlsh MySQL JS > shell.connect('vinicius.grippa@localhost:3306') MySQL localhost:3306 ssl JS > var cluster = dba.getCluster() MySQL localhost:3306 ssl JS > cluster.status()

  1 2 3 4

  # mysqlsh MySQL JS > shell.connect('vinicius.grippa@localhost:3306') MySQL localhost:3306 ssl JS > var cluster = dba.getCluster() MySQL localhost:3306 ssl JS > cluster.status()

• Adding the New Server: Seamlessly integrate the new server into the cluster:
  MySQL localhost:3306 ssl JS > cluster.addInstance("vinicius.grippa@vinnie-mysql-8-03:3306")

  1	MySQL localhost:3306 ssl JS > cluster.addInstance("vinicius.grippa@vinnie-mysql-8-03:3306")

  
  And you should see a similar output:
  

  The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'vinnie-mysql-8-03:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'. The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'. Incremental state recovery was selected because it seems to be safely usable. Validating instance configuration at vinnie-mysql-8-03:3306... This instance reports its own address as vinnie-mysql-8-03:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'vinnie-mysql-8-03:3306'. Use the localAddress option to override. * Checking connectivity and SSL configuration... 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 to the cluster... Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background. State recovery already finished for 'vinnie-mysql-8-03:3306' The instance 'vinnie-mysql-8-03:3306' was successfully added to the cluster.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'vinnie-mysql-8-03:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.   The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.   Incremental state recovery was selected because it seems to be safely usable.   Validating instance configuration at vinnie-mysql-8-03:3306...   This instance reports its own address as vinnie-mysql-8-03:3306   Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'vinnie-mysql-8-03:3306'. Use the localAddress option to override.   * Checking connectivity and SSL configuration... 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 to the cluster...   Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background. State recovery already finished for 'vinnie-mysql-8-03:3306'   The instance 'vinnie-mysql-8-03:3306' was successfully added to the cluster.

Conclusion

Incorporating a new server into an InnoDB Cluster is a process that has been greatly facilitated by MySQL shell. The process is relatively straightforward and MySQL will handle the initial sync by itself.

Please do not hesitate to reach out if you require additional insights, modifications, or further information.

Useful Links

For further guidance, refer to the MySQL documentation on adding instances to a cluster: MySQL Documentation on Adding Instances to a Cluster.

Written by

Vinicius Grippa

Writes this blog. Mostly about databases. Boring on purpose.

More about me →