MySQL's configuration system follows a specific hierarchy when applying settings. The server checks these locations in order:

1. Command-line parameters (highest priority)
2. /etc/my.cnf
3. /etc/mysql/my.cnf
4. ~/.my.cnf
5. --defaults-extra-file specified files
6. Built-in defaults (lowest priority)

To verify which configuration files MySQL is actually reading, execute:

mysql --help | grep "Default options" -A 1

For a running server, check with:

SHOW VARIABLES LIKE '%config%';
SHOW VARIABLES LIKE '%collation%';
SHOW VARIABLES LIKE '%dir%';

The most frequent issues causing configuration problems include:

• Incorrect file permissions (should be readable by mysql user)
• Malformed INI syntax (unclosed quotes, missing section headers)
• Conflicting settings from multiple config files
• Using deprecated parameter names

Example of proper section headers:

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Create a test configuration file and verify it's being processed:

# Create test config
echo "[mysqld]" > /tmp/test.cnf
echo "des-key-file=/tmp/test_keys" >> /tmp/test.cnf

# Start MySQL with explicit config
mysqld_safe --defaults-file=/tmp/test.cnf --verbose --help

When troubleshooting startup problems, use these techniques:

# Check error log (location varies by installation)
tail -f /var/log/mysql/error.log

# Run in foreground with debug output
mysqld --defaults-file=/etc/my.cnf --console

# Verify privilege inheritance
namei -l /etc/my.cnf

For critical production systems, consider these best practices:

# Systemd override example (create /etc/systemd/system/mysql.service.d/override.conf)
[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --daemonize --pid-file=/var/run/mysqld/mysqld.pid

When file-based configuration proves problematic, consider:

# Runtime configuration using SET GLOBAL
SET GLOBAL collation_server = 'utf8_unicode_ci';

# Persistent configuration without config files
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

# Using --init-command for specific settings
mysqld_safe --init-command="SET GLOBAL max_connections=200"

After making changes, always verify:

# Check if variables changed
SELECT * FROM performance_schema.variables_by_thread 
WHERE VARIABLE_NAME IN ('collation_server','character_set_server');

# Verify file-based parameters
SHOW VARIABLES WHERE Variable_name LIKE '%dir' 
OR Variable_name LIKE '%file%';

---

MySQL follows a specific order when loading configuration files. The standard search path is:

/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

To verify which configuration files MySQL is actually using, run:

mysql --help | grep "Default options"

Or for a running server:

mysqladmin variables | grep -E 'basedir|datadir|version'

Debian systems often use an alternative configuration structure:

/etc/mysql/my.cnf (main file)
/etc/mysql/conf.d/ (additional configurations)
/etc/mysql/mysql.conf.d/ (server-specific settings)

Here's how to test where your settings might be getting overridden:

mysqld --verbose --help | grep -A1 "Default options"

The issue might stem from how your startup script invokes MySQL. Check for:

# Problematic line that might override configs
$bindir/mysqld_safe --defaults-file="/etc/my.cnf" --datadir="$datadir" ...

Better practice would be:

$bindir/mysqld_safe --defaults-extra-file=/etc/mysql/my.cnf &

Create a test configuration file to isolate the issue:

[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

Then test with:

mysqld --defaults-file=/path/to/test.cnf --verbose --help

For modern Debian installations using systemd:

systemctl edit mysql.service

Add override configuration:

[Service]
ExecStartPre=/bin/sleep 10
Environment="MYSQLD_OPTS=--defaults-extra-file=/etc/mysql/my.cnf"

Enable detailed logging to track configuration loading:

[mysqld_safe]
log-error=/var/log/mysql/mysql-error.log
debug=d,info,error,query,general,where:O,/tmp/mysqld.trace

Ensure proper permissions (especially when using AppArmor/SELinux):

chmod 644 /etc/mysql/my.cnf
chown root:root /etc/mysql/my.cnf

After making changes, verify the running configuration:

mysql -e "SHOW VARIABLES LIKE 'collation%';"
mysql -e "SHOW VARIABLES LIKE 'character%';"