{"id":3086,"date":"2019-05-21T09:18:04","date_gmt":"2019-05-21T08:18:04","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=3086"},"modified":"2019-09-03T07:36:16","modified_gmt":"2019-09-03T06:36:16","slug":"mysql-innodb-cluster-howto-2-validate-an-instance","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2019\/05\/21\/mysql-innodb-cluster-howto-2-validate-an-instance\/","title":{"rendered":"MySQL InnoDB Cluster – HowTo #2 – Validate an instance"},"content":{"rendered":"\n
\"Sakila<\/figure><\/div>\n\n\n\n

How do I\u2026 Validate an instance for MySQL InnoDB Cluster usage?<\/h2>\n\n\n\n

Short answer<\/h3>\n\n\n\n

Use:<\/p>\n\n\n\n

checkInstanceConfiguration<\/a>()<\/p>\n\n\n\n\n\n

Long answer…<\/h3>\n\n\n\n

In this article I assuming you already know what is MySQL Group Replication<\/a> & MySQL InnoDB Cluster<\/a>.
Additionally you can read this tutorial<\/a> and this article<\/a> from my colleague lefred<\/a> or this one on Windows Platform<\/a> from my colleague Ivan<\/a>. <\/p>\n\n\n\n

During the cluster creation process or when you want to add a node to a running cluster, the chosen MySQL instance must be valid for an InnoDB Cluster<\/strong> usage.
That is, be compliant with Group Replication <\/a>r<\/a>equirements<\/a>.<\/p>\n\n\n\n

MySQL Shell<\/a> provide a simple and easy way to check if your instance is valid: checkInstanceConfiguration<\/a>()<\/p>\n\n\n\n

I’m using MySQL Shell<\/a> 8.0.16<\/strong>:<\/p>\n\n\n\n

$ mysqlsh\nMySQL Shell 8.0.16\n\nCopyright (c) 2016, 2019, Oracle and\/or its affiliates. All rights reserved.\nOracle is a registered trademark of Oracle Corporation and\/or its affiliates.\nOther names may be trademarks of their respective owners.\n\nType '\\help' or '\\?' for help; '\\quit' to exit.\n\nMySQL JS> <\/code><\/pre>\n\n\n\n
In this scenario my cluster is not created yet. However the logic would have been the same for adding a node to a running cluster.<\/p>\n\n\n\n\n\n
Ask for help<\/h3>\n\n\n\n
The built-in help is simply awesome!<\/p>\n\n\n\n
MySQL JS> dba.help('checkInstanceConfiguration')\nNAME\n      checkInstanceConfiguration - Validates an instance for MySQL InnoDB\n                                   Cluster usage.\n\nSYNTAX\n      dba.checkInstanceConfiguration(instance[, options])\n\nWHERE\n      instance: An instance definition.\n      options: Data for the operation.\n\nRETURNS\n       A descriptive text of the operation result.\n\nDESCRIPTION\n      This function reviews the instance configuration to identify if it is\n      valid for usage with group replication. Use this to check for possible\n      configuration issues on MySQL instances before creating a cluster with\n      them or adding them to an existing cluster.\n\n      The instance definition is the connection data for the instance.\n\n      For additional information on connection data use \\? connection.\n\n      Only TCP\/IP connections are allowed for this function.\n\n      The options dictionary may contain the following options:\n\n      - mycnfPath: Optional path to the MySQL configuration file for the\n        instance. Alias for verifyMyCnf\n      - verifyMyCnf: Optional path to the MySQL configuration file for the\n        instance. If this option is given, the configuration file will be\n        verified for the expected option values, in addition to the global\n        MySQL system variables.\n      - password: The password to get connected to the instance.\n      - interactive: boolean value used to disable the wizards in the command\n        execution, i.e. prompts are not provided to the user and confirmation\n        prompts are not shown.\n\n      The connection password may be contained on the instance definition,\n      however, it can be overwritten if it is specified on the options.\n\n      The returned descriptive text of the operation result indicates whether\n      the instance is valid for InnoDB Cluster usage or not. If not, a table\n      containing the following information is presented:\n\n      - Variable: the invalid configuration variable.\n      - Current Value: the current value for the invalid configuration\n        variable.\n      - Required Value: the required value for the configuration variable.\n      - Note: the action to be taken.\n\n      The note can be one of the following:\n\n      - Update the config file and update or restart the server variable.\n      - Update the config file and restart the server.\n      - Update the config file.\n      - Update the server variable.\n      - Restart the server.\n\nEXCEPTIONS\n      ArgumentError in the following scenarios:\n\n      - If the instance parameter is empty.\n      - If the instance definition is invalid.\n      - If the instance definition is a connection dictionary but empty.\n\n      RuntimeError in the following scenarios:\n\n      - If the instance accounts are invalid.\n      - If the instance is offline.\n      - If the instance is already part of a Replication Group.\n      - If the instance is already part of an InnoDB Cluster.\n      - If the given the instance cannot be used for Group Replication.<\/code><\/pre>\n\n\n\n\n\n
Check Instance Configuration<\/h3>\n\n\n\n
In order to check a MySQL instance I must connect to that instance, either by connecting to that instance with MySQL Shell or by providing the connection data to the function:<\/p>\n\n\n\n
MySQL JS> dba.checkInstanceConfiguration('root@172.20.0.11')\nValidating MySQL instance at 172.20.0.11:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node1\n\nChecking whether existing tables comply with Group Replication requirements...\nWARNING: The following tables do not have a Primary Key or equivalent column: \ntest.squares, test.people, test.animal\n\nGroup Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY or PRIMARY KEY Equivalent.\n\nChecking instance configuration...\n\nSome configuration options need to be fixed:\n+--------------------------+---------------+----------------+--------------------------------------------------+\n| Variable                 | Current Value | Required Value | Note                                             |\n+--------------------------+---------------+----------------+--------------------------------------------------+\n| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |\n| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |\n| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |\n| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |\n+--------------------------+---------------+----------------+--------------------------------------------------+\n\nSome variables need to be changed, but cannot be done dynamically on the server.\nPlease use the dba.configureInstance() command to repair these issues.\n\n{\n    \"config_errors\": [\n        {\n            \"action\": \"server_update\", \n            \"current\": \"CRC32\", \n            \"option\": \"binlog_checksum\", \n            \"required\": \"NONE\"\n        }, \n        {\n            \"action\": \"restart\", \n            \"current\": \"OFF\", \n            \"option\": \"enforce_gtid_consistency\", \n            \"required\": \"ON\"\n        }, \n        {\n            \"action\": \"restart\", \n            \"current\": \"OFF\", \n            \"option\": \"gtid_mode\", \n            \"required\": \"ON\"\n        }, \n        {\n            \"action\": \"restart\", \n            \"current\": \"1\", \n            \"option\": \"server_id\", \n            \"required\": \"<unique ID>\"\n        }\n    ], \n    \"status\": \"error\"\n}<\/code><\/pre>\n\n\n\n
The output depends on the instance current status.
In my case 3 tables do not meet the requirements because of lack of Primary key (or non-null unique key).
Also I need to set correctly 4 variables and I must restart the MySQL instance because of 3 of them.<\/p>\n\n\n\n\n\n
Automation<\/h3>\n\n\n\n
It is not always convenient (or recommended) to do these kind of task manually. 
MySQL Shell is built in regards to DevOps usage<\/a> : <\/p>\n\n\n\n
$ mysqlsh -e \"dba.checkInstanceConfiguration('root@172.20.0.12')\"\nValidating MySQL instance at 172.20.0.12:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node2\n\nChecking whether existing tables comply with Group Replication requirements...\nNo incompatible tables detected\n\nChecking instance configuration...\n\nSome configuration options need to be fixed:\n+--------------------------+---------------+----------------+--------------------------------------------------+\n| Variable                 | Current Value | Required Value | Note                                             |\n+--------------------------+---------------+----------------+--------------------------------------------------+\n| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |\n| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |\n| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |\n| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |\n+--------------------------+---------------+----------------+--------------------------------------------------+\n\nSome variables need to be changed, but cannot be done dynamically on the server.\nPlease use the dba.configureInstance() command to repair these issues.<\/code><\/pre>\n\n\n\n
Or even more practical<\/strong>:<\/p>\n\n\n\n
$ mysqlsh -- dba checkInstanceConfiguration --user=root --host=172.20.0.13\nValidating MySQL instance at 172.20.0.13:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node3\n\nChecking whether existing tables comply with Group Replication requirements...\nNo incompatible tables detected\n\nChecking instance configuration...\n\nSome configuration options need to be fixed:\n+--------------------------+---------------+----------------+--------------------------------------------------+\n| Variable                 | Current Value | Required Value | Note                                             |\n+--------------------------+---------------+----------------+--------------------------------------------------+\n| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |\n| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |\n| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |\n| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |\n+--------------------------+---------------+----------------+--------------------------------------------------+\n\nSome variables need to be changed, but cannot be done dynamically on the server.\nPlease use the dba.configureInstance() command to repair these issues.\n\n{\n    \"config_errors\": [\n        {\n            \"action\": \"server_update\", \n            \"current\": \"CRC32\", \n            \"option\": \"binlog_checksum\", \n            \"required\": \"NONE\"\n        }, \n        {\n            \"action\": \"restart\", \n            \"current\": \"OFF\", \n            \"option\": \"enforce_gtid_consistency\", \n            \"required\": \"ON\"\n        }, \n        {\n            \"action\": \"restart\", \n            \"current\": \"OFF\", \n            \"option\": \"gtid_mode\", \n            \"required\": \"ON\"\n        }, \n        {\n            \"action\": \"restart\", \n            \"current\": \"1\", \n            \"option\": \"server_id\", \n            \"required\": \"<unique ID>\"\n        }\n    ], \n    \"status\": \"error\"\n}<\/code><\/pre>\n\n\n\n
An other option is to create a script and pass it to MySQL Shell.
A very simple (and naive) example could be:<\/p>\n\n\n\n
$ cat \/tmp\/servers.js\ndba.checkInstanceConfiguration('root@172.20.0.11');\ndba.checkInstanceConfiguration('root@172.20.0.12');\ndba.checkInstanceConfiguration('root@172.20.0.13');<\/code><\/pre>\n\n\n\n
then process the file:<\/p>\n\n\n\n
$ mysqlsh  -f \/tmp\/servers.js\nValidating MySQL instance at 172.20.0.11:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node1\n\nChecking whether existing tables comply with Group Replication requirements...\nNo incompatible tables detected\n\nChecking instance configuration...\nInstance configuration is compatible with InnoDB cluster\n\nThe instance '172.20.0.11:3306' is valid for InnoDB cluster usage.\n\nValidating MySQL instance at 172.20.0.12:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node2\n\nChecking whether existing tables comply with Group Replication requirements...\nNo incompatible tables detected\n\nChecking instance configuration...\nInstance configuration is compatible with InnoDB cluster\n\nThe instance '172.20.0.12:3306' is valid for InnoDB cluster usage.\n\nValidating MySQL instance at 172.20.0.13:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node3\n\nChecking whether existing tables comply with Group Replication requirements...\nNo incompatible tables detected\n\nChecking instance configuration...\nInstance configuration is compatible with InnoDB cluster\n\nThe instance '172.20.0.13:3306' is valid for InnoDB cluster usage.<\/code><\/pre>\n\n\n\n
In the previous scenario all the MySQL instances was set properly before the check.

<\/p>\n\n\n\n
Note that all that has been done previously in Javascript<\/strong> can also be done in Python<\/strong> :<\/p>\n\n\n\n
MySQL 172.20.0.11:33060+ JS> \\py\nSwitching to Python mode...\n\nMySQL 172.20.0.11:33060+ Py> dba.check_instance_configuration()\nValidating MySQL instance at 172.20.0.11:3306 for use in an InnoDB cluster...\n\nThis instance reports its own address as mysql_node1\n\nChecking whether existing tables comply with Group Replication requirements...\nNo incompatible tables detected\n\nChecking instance configuration...\nInstance configuration is compatible with InnoDB cluster\n\nThe instance '172.20.0.11:3306' is valid for InnoDB cluster usage.\n\n{\n    \"status\": \"ok\"\n}<\/code><\/pre>\n\n\n\n
$ mysqlsh root@172.20.0.11 --py -f check_servers.py\n...<\/code><\/pre>\n\n\n\n\n\n
To summarize<\/h2>\n\n\n\n
Q: How do I validate an instance for MySQL InnoDB Cluster usage?

A: Use check_instance_configuration<\/strong>()<\/strong><\/em> <\/p>\n\n\n\n\n\n
References<\/h2>\n\n\n\n