The Power of MySQL Shell: A Guide to Create an InnoDB Cluster

Introduction

The efficiency and reliability of database management systems are paramount. This blog post is your step-by-step guide to mastering the creation of an InnoDB Cluster from scratch using MySQL Shell. Let’s dive into the detailed steps.

Prerequisites for Setting Up an InnoDB Cluster

Laying the Groundwork: What You Need Before You Begin

Before creating an InnoDB Cluster, it’s essential to ensure specific prerequisites are in place. These foundational steps are critical for a smooth and successful setup:

1. MySQL Shell Installation: The MySQL Shell must be installed and should be the same version as your MySQL instance. Installing it locally is unnecessary, but the server with MySQL Shell must have access to the database servers.
2. Network Configuration: Ensure that all servers are on a network that allows communication between them. This step is vital for the cluster to operate correctly.
3. Security Considerations: Proper security settings and firewall configurations are imperative. These measures safeguard your cluster from unauthorized access and potential vulnerabilities.
4. Unused MySQL Database Instance: The database instance you plan to use for the cluster should not currently be used for other purposes. This helps avoid conflicts and potential data integrity issues.

Step-by-Step Guide

Installation and User Permissions

1. MySQL Installation: Install MySQL on the server that will be part of the cluster.
2. Ensuring User Permissions: After the installation, it’s crucial to verify that the user has all the necessary permissions to create and manage the cluster. If permissions are lacking, you’ll encounter an error message. To check and confirm the user permissions, use the following command in MySQL Shell:
   
   MySQL localhost:8031 ssl JS > dba.checkInstanceConfiguration()

   1	MySQL localhost:8031 ssl JS > dba.checkInstanceConfiguration()

   
   If there are permission issues, it will provide a detailed error message and the required privileges. These privileges include a range of permissions from CLONE_ADMIN to SYSTEM_VARIABLES_ADMIN:
   
   Validating local MySQL instance listening at port 8031 for use in an InnoDB cluster... ERROR: The account 'cluster_admin'@'%' is missing privileges required to manage an InnoDB cluster: GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'cluster_admin'@'%' WITH GRANT OPTION; For more information, see the online documentation. Dba.checkInstanceConfiguration: The account 'cluster_admin'@'%' is missing privileges required to manage an InnoDB cluster. (RuntimeError)

   1 2 3 4 5 6 7 8 9

   Validating local MySQL instance listening at port 8031 for use in an InnoDB cluster... ERROR: The account 'cluster_admin'@'%' is missing privileges required to manage an InnoDB cluster: GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'cluster_admin'@'%' WITH GRANT OPTION; For more information, see the online documentation. Dba.checkInstanceConfiguration: The account 'cluster_admin'@'%' is missing privileges required to manage an InnoDB cluster. (RuntimeError)

3. Verifying MySQL Instance Parameters: To ensure that your MySQL instance is ready for cluster initiation, use the dba.checkInstanceConfiguration() command in MySQL Shell:
   
   MySQL localhost:8031 ssl JS > dba.checkInstanceConfiguration() Validating local MySQL instance listening at port 8031 for use in an InnoDB cluster... This instance reports its own address as single-8031:8031 Checking whether existing tables comply with Group Replication requirements... No incompatible tables detected Checking instance configuration... NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +----------------------------------------+---------------+----------------+--------------------------------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | | server_id | 1 | <unique ID> | Update read-only variable and restart the server | +----------------------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. NOTE: Please use the dba.configureInstance() command to repair these issues. { "config_errors": [ { "action": "server_update", "current": "COMMIT_ORDER", "option": "binlog_transaction_dependency_tracking", "required": "WRITESET" }, { "action": "server_update+restart", "current": "OFF", "option": "enforce_gtid_consistency", "required": "ON" }, { "action": "server_update+restart", "current": "OFF", "option": "gtid_mode", "required": "ON" }, { "action": "server_update+restart", "current": "1", "option": "server_id", "required": "<unique ID>" } ], "status": "error" }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

   MySQL  localhost:8031 ssl  JS > dba.checkInstanceConfiguration()   Validating local MySQL instance listening at port 8031 for use in an InnoDB cluster... This instance reports its own address as single-8031:8031 Checking whether existing tables comply with Group Replication requirements...   No incompatible tables detected    Checking instance configuration...   NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+--------------------------------------------------+ | Variable                               | Current Value | Required Value | Note                                             | +----------------------------------------+---------------+----------------+--------------------------------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable                       | | enforce_gtid_consistency               | OFF           | ON             | Update read-only variable and restart the server | | gtid_mode                              | OFF           | ON             | Update read-only variable and restart the server | | server_id                              | 1             | <unique ID>    | Update read-only variable and restart the server | +----------------------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server.   NOTE: Please use the dba.configureInstance() command to repair these issues.   {    "config_errors": [        {            "action": "server_update",            "current": "COMMIT_ORDER",            "option": "binlog_transaction_dependency_tracking",            "required": "WRITESET"        },        {            "action": "server_update+restart",            "current": "OFF",            "option": "enforce_gtid_consistency",            "required": "ON"        },        {            "action": "server_update+restart",            "current": "OFF",            "option": "gtid_mode",            "required": "ON"        },        {            "action": "server_update+restart",            "current": "1",            "option": "server_id",            "required": "<unique ID>"        }    ],    "status": "error" }

4. Applying Necessary Parameters: Apply the necessary parameters for your cluster manually or using the dba.configureInstance() command. This step configures your MySQL instance for InnoDB Cluster use and may require server updates or restarts.
   
   MySQL localhost:8031 ssl JS > dba.configureInstance() Configuring local MySQL instance listening at port 8031 for use in an InnoDB cluster... This instance reports its own address as single-8031:8031 applierWorkerThreads will be set to the default value of 4. NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +----------------------------------------+---------------+----------------+--------------------------------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | | server_id | 1 | <unique ID> | Update read-only variable and restart the server | +----------------------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. Do you want to perform the required configuration changes? [y/n]: Y Do you want to restart the instance after configuring it? [y/n]: Y Configuring instance... The instance 'single-8031:8031' was configured to be used in an InnoDB cluster. Restarting MySQL... NOTE: MySQL server at single-8031:8031 was restarted.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

   MySQL  localhost:8031 ssl  JS > dba.configureInstance()   Configuring local MySQL instance listening at port 8031 for use in an InnoDB cluster... This instance reports its own address as single-8031:8031     applierWorkerThreads will be set to the default value of 4.   NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+--------------------------------------------------+ | Variable                               | Current Value | Required Value | Note                                             | +----------------------------------------+---------------+----------------+--------------------------------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable                       | | enforce_gtid_consistency               | OFF           | ON             | Update read-only variable and restart the server | | gtid_mode                              | OFF           | ON             | Update read-only variable and restart the server | | server_id                              | 1             | <unique ID>    | Update read-only variable and restart the server | +----------------------------------------+---------------+----------------+--------------------------------------------------+   Some variables need to be changed, but cannot be done dynamically on the server.   Do you want to perform the required configuration changes? [y/n]: Y   Do you want to restart the instance after configuring it? [y/n]: Y   Configuring instance...   The instance 'single-8031:8031' was configured to be used in an InnoDB cluster.   Restarting MySQL...   NOTE: MySQL server at single-8031:8031 was restarted.

5.  Creating the Cluster: Create the cluster using the dba.createCluster() command. This step involves adding the seed instance and ensuring the configuration is suitable for the cluster.
   
   MySQL localhost:8031 ssl JS > dba.createCluster('myCluster', {'localAddress':'localhost:38031'}) A new InnoDB cluster will be created on instance 'localhost:8031'. Validating instance configuration at localhost:8031... This instance reports its own address as single-8031:8031 Instance configuration is suitable. Creating InnoDB cluster 'myCluster' on 'single-8031:8031'... 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. <Cluster:myCluster>

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

   MySQL  localhost:8031 ssl  JS > dba.createCluster('myCluster', {'localAddress':'localhost:38031'})   A new InnoDB cluster will be created on instance 'localhost:8031'. Validating instance configuration at localhost:8031...    This instance reports its own address as single-8031:8031    Instance configuration is suitable.   Creating InnoDB cluster 'myCluster' on 'single-8031:8031'...    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.   <Cluster:myCluster>

6. Cluster Validation: Use the command to confirm the cluster’s creation and check its status. This step verifies that your cluster is operational and ready for use.
   
   MySQL localhost:8031 ssl JS > cluster = dba.getCluster() <Cluster:myCluster> MySQL localhost:8031 ssl JS > cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "single-8031:8031", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "single-8031:8031": { "address": "single-8031:8031", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.31" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "single-8031:8031" }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

   MySQL  localhost:8031 ssl  JS >  cluster = dba.getCluster()   <Cluster:myCluster>   MySQL  localhost:8031 ssl  JS > cluster.status() {     "clusterName": "myCluster",     "defaultReplicaSet": {         "name": "default",         "primary": "single-8031:8031",         "ssl": "REQUIRED",         "status": "OK_NO_TOLERANCE",         "statusText": "Cluster is NOT tolerant to any failures.",         "topology": {             "single-8031:8031": {                 "address": "single-8031:8031",                 "memberRole": "PRIMARY",                 "mode": "R/W",                 "readReplicas": {},                 "replicationLag": null,                 "role": "HA",                 "status": "ONLINE",                 "version": "8.0.31"             }         },         "topologyMode": "Single-Primary"     },     "groupInformationSourceMember": "single-8031:8031" }

Conclusion

Creating an InnoDB cluster becomes more simplified and fluid using the MySQL shell. Following the detailed instructions in this blog post ensures that the process is carried out safely and efficiently.

Additional Resources

For further reading and detailed documentation, visit:

• Configuring Cluster Instance Ports
• Setting Up InnoDB Cluster and MySQL Router

---

This blog post consolidates the documentation into an easily digestible format, covering all the essential steps and information needed for creating an InnoDB Cluster using MySQL Shell. Let me know if you need any further adjustments or additional sections!