Implementing a MySQL Configuration Comparison Script: Key Considerations and Common Pitfalls

Introduction

When operating MySQL in daily maintenance—whether setting up replication, upgrading versions, migrating data, or performing routine inspections—you often need to compare MySQL configuration values. Although tools like pt-config-diff exist, custom scripts are sometimes required for specialized scenarios.

1. Obtaining Runtime Values

The first step is to fetch the current value of a variable. For example, the following command retrieves the transaction_isolation setting:

[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

You can also query the performance_schema.global_variables table directly:

[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

When scripting, avoid plain‑text passwords by using the --login-path=root option or creating a dedicated low‑privilege user.

2. Parsing Configuration Files

Beyond runtime values, you must extract the values defined in configuration files. Two files are relevant:

my.cnf (specified with --defaults-file )

mysqld-auto.cnf (persisted variables in MySQL 8.0)

Using --defaults-file simplifies locating the exact configuration file via process information and prevents interference from system defaults such as /etc/my.cnf .

When parsing my.cnf , consider:

Variables may appear under different sections ( [mysql] , [mysqld] ); filter for the [mysqld] block.

Multiple definitions of the same variable should resolve to the last occurrence.

Formatting variations require normalisation: case conversion, comment removal, whitespace trimming, handling of underscores vs. hyphens, and recognising both _ and - forms.

Example of extracting key/value pairs from mysqld-auto.cnf using jq and awk :

[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

[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

After gathering runtime and file‑based values, comparison must handle several nuances:

Boolean equivalents: treat 1 / ON as true and 0 / OFF as false.

Units for buffer sizes (e.g., 1024 , 1024K , 1024M ) need conversion to a common base.

Legacy terminology: map slave / master to source / replica where appropriate.

Normalize case for both keys and values before comparison.

4. Summary

Implementing a reliable MySQL configuration comparison script requires careful extraction of runtime values, thorough parsing of my.cnf and mysqld-auto.cnf , and robust comparison logic that accounts for formatting, units, boolean representations, and evolving terminology. Continuous refinement will improve accuracy and maintainability.