MySQL Workbench Alternatives – ClusterControl Configuration Management

MySQL configuration management consists of two major components – MySQL configuration files and runtime configuration. Applying configuration changes on the runtime environment can be done through MySQL server clients with no privilege for session variables but SUPER privileges for global variables. Applying the same configuration changes into MySQL configuration file is also necessary to make the changes persistent across MySQL restarts, otherwise the default values will get loaded during the startup.

In this blog post, we are going to look at ClusterControl Configuration Management as an alternative to MySQL Workbench configuration management.

MySQL Workbench Configuration Management

MySQL Workbench is a graphical client for working with MySQL servers and databases for server versions 5.x and higher. It is freely available and commonly being used by SysAdmins, DBAs and developers to perform SQL development, data modelling, MySQL server administration and data migration.

You can use MySQL Workbench to perform MySQL/MariaDB configuration management on a remote MySQL server. However, there are some initial steps required to enable this feature. From MySQL Workbench, select an existing connection profile and choose Configure Remote Management. You will be presented with a step-by-step configuration wizard to help you to set up remote management for the connection profile:

At the start, a connection attempt is made to determine the server version and operating system of the target machine. This allows connection settings to be validated and allows the wizard to pick a meaningful configuration preset. If this attempt fails you can still continue to the next step, where you can customize the settings further to suit the remote server environment.

Once the remote connection configuration is complete, double clicks on the connection profile to start connecting to the MySQL instance. Then, go to the Instance -> Options File to open the configuration manager section. You should see something similar to the following screenshot:

All existing configuration variables from the configuration file are pre-loaded into this configuration manager so you can see what options have been enabled with its respective values. Configurations are categorized to a number of sections – General, logging, InnoDB, networking and so on – which really helps us focus on specific features that we want to tweak or enable.

Once you are satisfied with the changes, and before clicking “Apply”, make sure you choose the correct MySQL group section from the dropdown menu (right next to the Discard button). Once applied, you should see the configuration is applied to the MySQL server where a new line will appear (if it didn’t exist) in the MySQL configuration file.

Note that clicking on the “Apply” button will not push the corresponding change into MySQL runtime. One has to perform restart operation on the MySQL server to load the new configuration changes by going to Instance -> Startup/Shutdown. This will take a hit on your database uptime.

To see all the loaded system status and variables, go to Management -> Status and System Variables:

ClusterControl Configuration Management

ClusterControl Configuration Manager can be accessed under Manage -> Configurations. ClusterControl pulls a number of important configuration files and displays them in a tree structure. A centralized view of these files is key to efficiently understanding and troubleshooting distributed database setups. The following screenshot shows ClusterControl’s configuration file manager which listed out all related configuration files for this cluster in one single view with syntax highlighting:

As you can see from the screenshot above, ClusterControl understands MySQL “!include” parameter and will follow through all configuration files associated with it. For instance, there are two MySQL configuration files being pulled from host 192.168.0.21, /etc/my.cnf and /etc/my.cnf.d/secrets-backup.cnf. You can open multiple configuration files in another editor tab which make it easier to compare the content side-by-side. ClusterControl also pulls the last file modification information from the OS timestamp, as shown at the bottom right of the text editor.

ClusterControl eliminates the repetitiveness when changing a configuration option of a database cluster. Changing a configuration option on multiple nodes can be performed via a single interface and will be applied to the database node accordingly. When you click on “Change/Set Parameter”, you can select the database instances that you would want to change and specify the configuration group, parameter and value:

You can add a new parameter into the configuration file or modify an existing parameter. The parameter will be applied to the chosen database nodes’ runtime and into the configuration file if the option passes the variable validation process. Some variables might require a follow-up step like server restart or configuration reload, which will then be advised by ClusterControl.

All services configured by ClusterControl use a base configuration template available under /usr/share/cmon/templates on the ClusterControl node. You can directly modify the file to suit your deployment policy however, this directory will be replaced after a package upgrade. To make sure your custom configuration template files persist across upgrades, store your template files under /etc/cmon/templates directory. When ClusterControl loads up the template file for deployment, files under /etc/cmon/templates will always have higher priority over the files under /usr/share/cmon/templates. If two files having identical names exist on both directories, the one located under /etc/cmon/templates will be used.

Go to Performance -> DB Variables to check the runtime configuration for all servers in the cluster:

Notice a line highlighted in red in the screenshot above? That means the configuration is not identical in all nodes. This provides more visibility on the configuration difference among hosts in a particular database cluster.

Workbench v ClusterControl: Advantages and Disadvantages

Every product has its own set of advantages and disadvantages. For ClusterControl, since it understands cluster and topology, it’s the best configuration manager to manage multiple database nodes at once. It supports multiple MySQL vendors like MariaDB, Percona as well as all Galera Cluster variants. It also understands database load balancer configuration format for HAProxy, MariaDB MaxScale, ProxySQL and Keepalived. Since ClusterControl requires passwordless SSH configuration at the beginning of importing/deploying the cluster, configuration management requires no remote setup like Workbench and it works out-of-the-box after the hosts are managed by ClusterControl. MySQL configuration changes performed by ClusterControl will be loaded into runtime automatically (for all supported variables) as well as written into MySQL configuration files for persistence. In terms of disadvantages, ClusterControl configuration management does not come with configuration descriptions which could help us anticipate what would happen if we changed the configuration option. It does not support all platforms that MySQL can run, particularly only certain Linux distributions like CentOS, RHEL, Debian and Ubuntu.

MySQL Workbench supports remote management of many operating systems like Windows, FreeBSD, MacOS, Open Solaris and Linux. MySQL Workbench is available for free and can also be used with other MySQL vendors like Percona and MariaDB (despite not listed here, it does work with some older MariaDB versions). It also supports managing installation from the TAR bundle. It allows some customizations on configuration file path, service/stop commands and MySQL group sections naming. One of the neat features is that MySQL Workbench uses dropdown menu for fixed values, which can be a huge help in reducing the risk of misconfiguration from a user, as shown in the following screenshot:

On the downside, MySQL Workbench does not support multiple host configuration management where you have to perform the config change on every host separately. It also does not push the configuration changes into runtime, without explicit MySQL restart which can compromise the database service uptime.

The following table simplifies the significant differences taken from the all the mentioned points: