When managing MySQL servers, situations often arise where the original my.cnf configuration file gets lost or corrupted, yet the server continues running with its current settings. This creates a critical need to reconstruct the configuration file without restarting the service.

The most straightforward method is querying the running server for its current configuration:

mysql -uroot -p -e "SHOW VARIABLES;" > current_mysql_config.txt

This exports all server variables to a text file. However, the output needs filtering since it includes both default and explicitly set values.

To extract only non-default values that would typically appear in my.cnf:

mysql -uroot -p -e "SHOW VARIABLES WHERE 
    Variable_name NOT LIKE 'performance_schema%' 
    AND Variable_name NOT LIKE 'have%' 
    AND Variable_name NOT LIKE 'version%'
    AND Variable_name NOT LIKE 'ssl%'
    AND Variable_name != 'hostname'
    AND Variable_name != 'port'
    AND Value != ''" > filtered_config.txt

For a proper my.cnf format conversion, use this Perl one-liner:

mysql -uroot -p -NBe "SHOW VARIABLES" | \
perl -ne 'print "[mysqld]\n" if $. == 1; print if /^(?!version|timestamp|performance_schema)/' | \
awk '{print $1 "=" $2}' > reconstructed_my.cnf

For more comprehensive export including persisted variables:

mysqldump --defaults-file=/etc/mysql/my.cnf \
    --all-databases \
    --routines \
    --no-data \
    --flush-logs \
    --master-data=2 \
    --hex-blob \
    --triggers \
    --single-transaction \
    --set-gtid-purged=OFF \
    --no-create-info \
    --skip-triggers \
    --skip-add-drop-table \
    --skip-comments \
    --skip-disable-keys \
    --skip-extended-insert \
    --skip-lock-tables \
    --result-file=full_config_export.cnf

Before applying the reconstructed file, verify it won't cause conflicts:

mysqld --defaults-file=reconstructed_my.cnf --validate-config

When you need to recover or backup your MySQL configuration from a running server, the simplest method is using mysqld --verbose --help combined with some filtering:


# For MySQL 5.7+:
mysqld --verbose --help | grep -A 100 "Default options" > /etc/mysql/my.cnf

# For MySQL 8.0+ with more precise filtering:
mysqld --help --verbose | awk 'BEGIN {flag=0} /Default options are read from the following files/ {flag=1; next} /The following groups are read/ {flag=0} flag' > /etc/mysql/my.cnf

For more advanced cases where you need active runtime configuration (not just defaults), query the Performance Schema:

mysql -e "SELECT VARIABLE_NAME, VARIABLE_VALUE 
          FROM performance_schema.variables 
          WHERE VARIABLE_NAME NOT LIKE '%PASSWORD%' 
          AND VARIABLE_NAME NOT LIKE '%SECRET%' 
          ORDER BY VARIABLE_NAME" > current_config.txt

Here's a script that converts runtime variables to valid my.cnf format:

#!/bin/bash
mysql -NBe "SHOW VARIABLES" | awk '
BEGIN {
    print "[mysqld]"
}
!/innodb_buffer_pool_dump_at_shutdown|innodb_buffer_pool_load_at_startup/ {
    printf "%-45s = ", $1
    if ($2 ~ /^[0-9]+$/) {
        print $2
    } else {
        print "\"" $2 "\""
    }
}' > reconstructed.cnf

Important: Remove any sensitive variables like passwords before using the output.
For comprehensive configuration management:

pt-config-diff from Percona Toolkit
mysqldefconf (community tool)
mysql_config_editor (official utility)

Remember that some settings may appear differently in runtime versus config file:

# Example difference:
SHOW VARIABLES LIKE 'max_allowed_packet'; --> 67108864
# While in my.cnf it should be:
max_allowed_packet = 64M