MySQL Configuration Comparison: A Script Implementation Guide

Introduction

In daily MySQL operations like master-slave setup, version upgrades, data migration, or regular inspection, comparing MySQL configurations is inevitable. Despite tools like pt-config-diff, custom scenarios often require self-implementation.

What are the key points and overlooked details when creating a MySQL configuration comparison script? Let’s begin.

Scenario: How to compare running values vs. configuration file values for the same instance.

1. Obtaining Running Values

To compare running values with configuration values, start by obtaining the running values.

[root@localhost ~]$ /opt/sandboxes/mysql/5.7.31/bin/mysql --login-path=root --socket=/tmp/mysql_sandbox5731.sock -se "SHOW VARIABLES LIKE 'transaction_isolation';" | awk '{print $2}'
REPEATABLE-READ

‍

Alternatively, query the performance_schema.global_variables table:

[root@localhost ~]$ /opt/sandboxes/mysql/5.7.31/bin/mysql --login-path=root --socket=/tmp/mysql_sandbox5731.sock -se "select * from performance_schema.global_variables where variable_name = 'transaction_isolation'" | awk '{print $2}'
REPEATABLE-READ

‍

As can be seen, obtaining running values is relatively straightforward. However, when executing via script, plain text passwords are often unavoidable. Here are two suggestions:

Add the --login-path=root parameter, which is more secure than plain text passwords.

Create a dedicated user with minimal privileges.

2. Parsing Configuration Files

Before explaining how to obtain configuration values, let’s consider which files might affect variables in addition to running values.

my.cnf

In recommended operation scenarios, the --defaults-file parameter is generally used to specify the configuration file. This approach has several advantages:

It facilitates the use of process information to filter the location of the configuration file when scripting.

It avoids interference from system defaults such as /etc/my.cnf and other configuration files.

mysqld-auto.cnf

In addition to the default configuration file, in MySQL 8.0, the persisted variable configuration file mysqld-auto.cnf must also be considered.

Sometimes when starting the mysqld process, certain parameters may be manually specified, which can also affect the corresponding variable values. This method of startup is generally only used for temporary maintenance and is not considered in this article.

Here are some scenarios to consider when obtaining configuration values from the my.cnf file:

1. Variables such as port and socket may be configured under multiple sections like [mysql] and [mysqld]. When obtaining these values, filter out the parts related to the [mysqld] section first.

2. The same variable may be configured multiple times in the configuration file, so after parsing, only the value of the last configuration is needed.

3.Formatting issues. In production environment configuration files, various formats may appear, so the configuration file needs to be preprocessed before filtering the corresponding key and value. Common issues include:

- Case sensitivity. Convert the content of the configuration file to all uppercase or lowercase before matching and filtering.

- Comment issues. Filter out comment lines, and also pay attention to comments in the format of port=3306 # port. When obtaining values, use awk or sed for processing.

- Space issues. Prioritize processing spaces in various positions of the configuration file to present a compact key=value format before matching and filtering.

- “_” and “-”. MySQL currently supports both formats, such as binlog_format and binlog-format. Use the . in regular expressions for fuzzy matching during matching.

- Consider variables starting with loose.

Up to this point, we have obtained the variable names and their corresponding values from the my.cnf configuration file. Next, let’s look at mysqld-auto.cnf. Below is a simplified example of the content of a mysqld-auto.cnf file:

[root@localhost data]$ cat mysqld-auto.cnf | jq
{
  "Version": 2,
  "mysql_static_variables": {
    "innodb_buffer_pool_size": {
      "Value": "1073741824",
      "Metadata": {
        "Host": "localhost",
        "User": "msandbox",
        "Timestamp": 1734599734016339
      }
    }
  },
  "mysql_dynamic_parse_early_variables": {
    "max_connections": {
      "Value": "5000",
      "Metadata": {
        "Host": "localhost",
        "User": "msandbox",
        "Timestamp": 1734599700972489
      }
    }
  }
}

‍

Two methods to extract key and value:

Using awk for Regex Matching

[root@localhost data]$ cat mysqld-auto.cnf | jq | awk '
    function clean_str(s) {
        gsub(/^ *"|"|:|,$/, "", s)
        return s
    }
    /^ *"[^"]+": *{$/ {
        k = clean_str($1)
    }
    /^ *"Value": *"[^"]*"/ {
        sub(/^ *"Value": *"/, "", $0)
        print k ":" clean_str($0)
    }
'

# Output Example
innodb_buffer_pool_size:1073741824
max_connections:5000

‍

Using jq Command

‍

[root@localhost data]$ jq -r 'to_entries[] | select(.value | type == "object") | .value | to_entries[] | select(.value | has("Value")) | .key + ":" + .value.Value' mysqld-auto.cnf

# Output Example
innodb_buffer_pool_size:1073741824
max_connections:5000

‍

3. Comparing Variable Values

When comparing obtained running values and effective configuration values, avoid simple == judgments. Consider:

Compatibility of 1/ON and 0/OFF representations. Convert ON/OFF to 1/0 before comparison.

Unit conversion for numeric buffer classes (e.g., 1024, 1024K, 1024KB, etc.).

Handle both old (slave/master) and new (source/replica) terminology.

Unify the case of key and value before comparison.

4. Summary

In implementing MySQL configuration comparison, consider multiple factors and continuously refine your code to achieve accurate results. This article offers common implementation ideas. Feel free to share other scenarios for mutual learning.