MySQL: Cannot set MySQL server for InnoDB Cluster with dba.configureLocalInstance()

Question

I am trying to setup a Production Deployment of InnoDB Cluster (https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-production-deployment.html) by following the steps outlined in the documentation. I am using MySQL 5.7.25

My MySQL server starts successfully and I run (from the MySQL Shell)

dba.checkInstanceConfiguration('root@localhost:3306')

which prints

    Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as ip-10-0-3-184.ap-south-1.compute.internal
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency         | OFF           | ON             | Update the config file and restart the server    |
| gtid_mode                        | OFF           | ON             | Update the config file and restart the server    |
| log_bin                          | <not set>     | <no value>     | Update the config file                           |
| log_bin                          | OFF           | ON             | Update read-only variable and restart the server |
| log_slave_updates                | OFF           | ON             | Update the config file and restart the server    |
| master_info_repository           | FILE          | TABLE          | Update the config file and restart the server    |
| relay_log_info_repository        | FILE          | TABLE          | Update the config file and restart the server    |
| server_id                        | 0             | <unique ID>    | Update the config file and restart the server    |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update the config file and restart the server    |
+----------------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server: an option file is required.
Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "config_update+restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        }, 
        {
            "action": "config_update+restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        }, 
        {
            "action": "config_update", 
            "current": "<not set>", 
            "option": "log_bin", 
            "required": "<no value>"
        }, 
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "log_bin", 
            "required": "ON"
        }, 
        {
            "action": "config_update+restart", 
            "current": "OFF", 
            "option": "log_slave_updates", 
            "required": "ON"
        }, 
        {
            "action": "config_update+restart", 
            "current": "FILE", 
            "option": "master_info_repository", 
            "required": "TABLE"
        }, 
        {
            "action": "config_update+restart", 
            "current": "FILE", 
            "option": "relay_log_info_repository", 
            "required": "TABLE"
        }, 
        {
            "action": "config_update+restart", 
            "current": "0", 
            "option": "server_id", 
            "required": "<unique ID>"
        }, 
        {
            "action": "config_update+restart", 
            "current": "OFF", 
            "option": "transaction_write_set_extraction", 
            "required": "XXHASH64"
        }
    ], 
    "status": "error"

(So far so good)

After this I try running dba.configureLocalInstance() but I get the following error message:

Dba.configureLocalInstance: Dba.configureLocalInstance: An open session is required to perform this operation. (RuntimeError)

I am not sure what this means but to try to get around it I run dba.configureLocalInstance('root@localhost:3306') which seems to work:

Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as ip-10-0-3-184.ap-south-1.compute.internal
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

WARNING: User 'root' can only connect from localhost.
If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 3

Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency         | OFF           | ON             | Update the config file and restart the server    |
| gtid_mode                        | OFF           | ON             | Update the config file and restart the server    |
| log_bin                          | <not set>     | <no value>     | Update the config file                           |
| log_bin                          | OFF           | ON             | Update read-only variable and restart the server |
| log_slave_updates                | OFF           | ON             | Update the config file and restart the server    |
| master_info_repository           | FILE          | TABLE          | Update the config file and restart the server    |
| relay_log_info_repository        | FILE          | TABLE          | Update the config file and restart the server    |
| server_id                        | 0             | <unique ID>    | Update the config file and restart the server    |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update the config file and restart the server    |
+----------------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server: an option file is required.

Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [y/N]: y
Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...
The instance 'localhost:3306' was configured for InnoDB cluster usage.
MySQL server needs to be restarted for configuration changes to take effect.

But then after I restart MySQL server, I get the exact same output for dba.checkInstanceConfiguration('root@localhost:3306')...

I have also noticed that /etc/my.cnf has not been modified from it's default from the initial installation. In case it was a permissions issues I changed the owner of /etc/my.cnf to be owned my the user "mysql" but the result is the same...

Anybody that can shed some light onto it? Any help is appreciated.

Answer 1

I have been able to get past it by manually modifying my /etc/my.cnf file to include the necessary values to the variables mentioned in the output of dba.checkInstanceConfiguration('root@localhost:3306')