Let us look at the Trove configuration groups. These technically are some configurations that can be applied on one or more instances. It is to be noted that at any given point in time, only one configuration group can be active.

The steps in using the configuration groups are:

The configuration is synchronized across the instances. This means that, once the configuration is created and attached, modifying the configuration will modify it on all the instances that it is associated with. This makes it extremely useful when we want different configurations for different groups of instances (think about the configuration of prod/dev).

Trove already ships with a validation-rules.json file in its templates directory (the same directory where we found the base configuration template).

Each parameter will have the following properties (please note that this is a JSON string and hence the curly braces are delimiters):

There are several of these and you can see that the instructions for the configuration and whether the instance needs to be rebooted are mentioned in the JSON file. This file, with its default values, is more than enough for most cases, but we could modify the parameter values in certain cases (for example, innodb_log_buffer_size, where we can tweak the min/max value as per your standards). This is merely defining configuration parameters in a file and these values do not take effect until the next step is completed.

We can check the configuration parameters that are currently defined by using the command trove configuration-parameter-list --datastore <Datastore name> <datstore version>, so we will execute.

trove configuration-parameter-list --datastore mysql 5.6

This is expected as we have not loaded validation-rules.json yet.

If we try attaching a configuration to a datastore, whose configuration parameters have not been initialized, we will get an error stating that the configuration groups is not supported for that particular datastore.

Now, we will upload the validation-rules.json file by using the command trove-manage db_load_datastore_config_parameters mysql 5.6

/opt/stack/trove/trove/templates/mysql/validation-rules.json

The db_load_datastore_config_parameters command takes the datastore name, datastore version, and file path as inputs and uploads the configuration template. Once the command succeeds, we can execute the parameter list one more time and we shall see the following output:

Now, we can move on to the next step.

Now, we need to create a configuration patch that we need to apply to an instance or a group of instances. This is done by using the configuration-create command.

The command takes the configuration parameters as a JSON string (key-value pair) and separated by commas.

trove configuration-create test-configuration 
   --datastore mysql --datastore_version 5.6 
  '{ "max_connections":200, "max_user_connections":200 }' 
  --description "Testing Configuration Group"

The previous output shows the configuration that was created. We can now apply this configuration to the instance of our choice.

We can apply the configuration by using the configuration-attach command, which has the format trove configuration-attach <instance name / id> <configuration id>; we will use the instance mysql_gui and the ID of test-configuration.

trove configuration-attach mysql_gui 26a1d629-df68-42bc-826b-684af8f70e64

This command provides no output if successful, but we would have applied our recently created configuration to the mysql_gui instance. We can apply the configuration to as many instances of the same datastore type and version as we please.

We can also use trove update <instance_name> --configuration <config id> in order to attach the configuration to the instance.

If the configuration needs a restart, the changes will not be effective immediately and we will see the status RESTART_REQUIRED in the trove list command.

We can choose to apply the configuration and restart the instance later, by using the command trove restart <instance_id / name>. In our case, this will be

trove restart mysql_gui.

This will restart the MySQL instance and disconnect any active connections to the system. It is recommended that this be done only during the change window for a production instance.

Verification

In order to verify that the configuration has indeed been applied, we can log in to the instance using the MySQL command line.

mysql –udbuser –pdbpass –h 10.1.10.2

Once logged in, execute the following command:

show global variables like '%max_connections%';

This should show that the max connections have been set to 200 from the default 400. We can also use the command format select @@global.<variable name>.

The override.config.template file from the templates folder is used for creating the overridden configuration file and is placed in the mysql/conf.d folder on the instance.

Tip

The file can be seen on the instance only after logging in to the instance using SSH or VNC Console. This is not a required step; however, for the purposes of understanding the working, we may want to do it.

A screenshot showing the overridden config file is shown on the instance.

Hence, the configuration on an instance is persisted across instance reboots.

If we want to check the contents of the configuration, we will use the trove configuration-show command with the configuration ID, which can be retrieved by the trove configuration-list command.

So, in our case, we will execute the trove configuration-list command and note down the ID for the configuration we are trying to retrieve (in our case, 26a1d629-df68-42bc-826b-684af8f70e64).

We will then execute the trove configuration-show 26a1d629-df68-42bc-826b-684af8f70e64 command to see the values.

As we can see, the configuration sets max_user_connections and max_connections to a certain value.

In order to check which instances are associated with this configuration, we will execute trove configuration-instances <configuration id>.

This shows that the configuration is only applied to a single instance (mysql_gui) at the moment.

Once the configuration is applied to an instance or a bunch of instances, we can patch the configuration and it will be applied to all the instances attached to the configuration.

In order to effectively understand the use of patching, we will also attach the configuration to our second guest instance (test12) using the command trove configuration-attach test12 26a1d629-df68-42bc-826b-684af8f70e64. If you don't already have a second instance, you can launch it.

Once this is done, we can ensure that both instances show up in the output of the trove configuration-instances command output.

The patching of the configuration is done by using the command format:

trove configuration-patch <configuration-id> <JSON for the patch>.

In our case, we will drop max_user_connections to 150 rather than 300:

trove configuration-patch 26a1d629-df68-42bc-826b-684af8f70e64 
'{ "max_user_connections": 150 }'

We can verify that the configuration was patched by looking at the output of the trove configuration-show <configuration-id> command. We can also verify by using a similar method that we used to verify in the Applying configuration section: mysql -udbuser -pdbpass -h 10.1.10.2 .

-e "select @@global.max_user_connections;"

This should give you an output of 150, rather than the previous 200.

We should be able to create a new database user and password for the second instance as we did not specify it during the create time.

trove database-create test12 testdb2
trove user-create test12 dbuser2 dbpass2
trove user-grant-access test12 dbuser2 testdb2

The preceding creates a database (testdb2) and user (dbuser2) with the password (dbpass2) and grants access to the newly created database. Check on the second instance (Test12) mysql -udbuser2 -pdbpass2 -h10.1.10.3

-e "select @@global.max_user_connections;"

We will notice that this also has the same configuration.

If you remember, we had changed the default configuration port to 3307 before spinning the test12 instance; hence, the port needs to be specified.

The difference between patching and updating is that the update command should replace the entire contents of the configuration and not just update the values in the configuration. The command format is as following:

trove configuration-update <config id> '<new JSON>'

trove configuration-update 26a1d629-df68-42bc-826b-684af8f70e64 
'{ "wait_timeout": 300 }'

We verify it with the trove configuration-show command.

This will update the configuration; however, please remember that due to the bug, even the older configuration will still exist.

This is effectively the bug; once the bug resolves, this will not exist.

In order to ensure that this doesn't happen, we should detach the configuration and reattach it, so that the older configuration doesn't exist.

The configuration can be removed from the two instance commands:

This restores the configuration to the default configuration. Please note that this is the configuration in my.cnf (which was generated from the default configuration when the instance was spun up and may be different from the current default configuration).

This command, as you can see, has no output.

If a restart is required for the service, you need to execute trove restart <instance name/id> before you can reapply a new configuration.

Verification

We can execute the same commands that we did to verify that the configuration took effect. We will also see that the file has been removed from the guest instance.

Please notice that the 20-user-001-common.cnf file no longer exists.

We can only add the configuration parameters that are listed in the output of the trove configuration-parameter-list command. This is populated from the validation-rules.json file that we imported earlier.

Let's take a use case: in a company X, in the dev/test environment, the MySQL database instances run on port 3308 and production instances run on the default port 3306, but port is not a valid configuration parameter and if we try to create a configuration, we will get an error like ERROR: The configuration parameter port is not supported for this datastore: MySQL 5.6. (HTTP 422.). We will need to modify validation-rules.json. So in this case, we will add the JSON (I have added somewhere in between. There is no dependency on the placement of this).

So, the configuration is port, which is an integer value and can be from 1025 to 65535 as they are non-privileged ports. We will need to restart the MySQL instance when we are changing the port, so restart_required is set to true.

Once the validation-rules.json file is modified, we will upload it using trove-manage db_load_datastore_config_parameters and then follow the process from creating a configuration and applying it.

Modifying the port is not supported by Trove; however, it can be performed as shown earlier. If we have to modify the port, please remember to change the port for the network security group or the database will be inaccessible. Different datastores may have different configuration strategies, but most of them implement the default file configuration. The databases that allow configuration management during the time of writing are MySQL, MariaDB, Percona, Percona XtraDB, and Redis. Other database support for configuration changes is planned in the near future.