MySql - configuration files

Configuration Files (my.cnf / my.ini) in MySQL

The MySQL server relies heavily on configuration files to define its behavior during startup and runtime. These configuration files, typically named my.cnf on Linux/Unix systems or my.ini on Windows, are essential for customizing MySQL's behavior, performance, security, logging, and more. Understanding how to configure and tune these files can significantly enhance MySQL’s reliability and efficiency. In this guide, we explore the structure, location, key options, and best practices for managing MySQL configuration files.

Purpose of MySQL Configuration Files

Configuration files in MySQL define system-wide parameters that determine:

• Where MySQL stores data files and logs
• How much memory the server uses
• Networking and connection limits
• Authentication mechanisms
• Replication and binlog settings
• SQL mode and other operational options

These settings are read during MySQL startup and can be overridden using command-line flags or environment variables, but configuration files are the most reliable and maintainable method.

File Names and Locations

Default Configuration File Paths

MySQL reads configuration files in the following order (the exact paths may vary based on OS and installation method):

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

On Windows, the default file is usually:

C:\ProgramData\MySQL\MySQL Server X.Y\my.ini

Verifying Which File is Used

You can verify the active configuration file using the following command:

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

Structure of my.cnf / my.ini

The configuration files are divided into sections, each beginning with a header in square brackets. Each section is meant for a different MySQL component such as the server, client, or replication utility.

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[client]
user=root
password=secret
port=3306
socket=/var/lib/mysql/mysql.sock

Common Sections

• [mysqld]: Settings for the MySQL server daemon.
• [client]: Settings for the MySQL client program.
• [mysql]: Used by the `mysql` CLI tool.
• [mysqld_safe]: Options used when starting MySQL via `mysqld_safe`.
• [mysqldump]: Options specific to the `mysqldump` utility.

Key Configuration Categories

1. Basic Server Settings

[mysqld]
port = 3306
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip-name-resolve

Explanation:

• port: Port used by MySQL server.
• user: System user to run the MySQL daemon.
• datadir: Location of MySQL data files.
• socket: UNIX socket file path.
• skip-name-resolve: Disables DNS resolution to speed up connections.

2. Networking Options

[mysqld]
bind-address = 127.0.0.1
max_connections = 200
wait_timeout = 600

• bind-address: Defines which IP addresses MySQL will listen to.
• max_connections: Sets the maximum allowed client connections.
• wait_timeout: Disconnects idle clients after a set time (in seconds).

3. InnoDB Storage Engine Settings

[mysqld]
default-storage-engine = InnoDB
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1

• innodb_buffer_pool_size: Memory allocated for InnoDB buffer pool (should be 60-80% of RAM).
• innodb_log_file_size: Size of redo logs; affects recovery time.
• innodb_flush_log_at_trx_commit: Controls durability and performance (1 for safest, 2 or 0 for speed).

4. Logging Options

[mysqld]
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

These options help in performance monitoring and troubleshooting.

5. Binary Logging for Replication

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

Essential for replication and point-in-time recovery.

6. Security Settings

[mysqld]
local-infile = 0
secure-file-priv = /var/lib/mysql-files

local-infile = 0 disables `LOAD DATA LOCAL INFILE` to prevent unauthorized data import. secure-file-priv restricts file operations to a secure directory.

Configuring Client Settings

[client]
user = root
password = mysecurepassword
host = 127.0.0.1

These are default values used when the client tools (`mysql`, `mysqldump`, etc.) connect to the server.

Configuration Best Practices

1. Use Separate Files for Custom Settings

Instead of modifying the system-wide /etc/my.cnf, place custom settings in /etc/mysql/conf.d/custom.cnf or /etc/my.cnf.d/ depending on your OS. MySQL includes all files in these directories at startup.

2. Commenting and Documentation

Always comment on any custom setting to make it easier for others (or yourself) to understand.

# Increase the timeout to avoid disconnections
wait_timeout = 600

3. Version Control for Configuration Files

Maintain configuration files in a version-controlled repository (e.g., Git) to track changes over time.

4. Always Test Changes

Use the mysqld --help --verbose command to validate configuration syntax before restarting the server.

Dynamic vs Static Configuration

Some MySQL variables can be changed dynamically at runtime using SQL, while others require a restart.

Example of Dynamic Change

SET GLOBAL max_connections = 300;

To make the change persistent, it should also be added to the configuration file:

[mysqld]
max_connections = 300

Example Configuration File

[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
bind-address = 127.0.0.1
max_connections = 300
wait_timeout = 600
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2G
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_queries.log
long_query_time = 2
secure-file-priv = /var/lib/mysql-files

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

Restarting MySQL After Configuration Changes

After making changes to the configuration file, restart the MySQL service:

sudo systemctl restart mysql

or on older systems:

sudo service mysql restart

Common Troubleshooting Tips

1. MySQL Won’t Start After Configuration Change

Check the error log for syntax errors:

cat /var/log/mysql/error.log

2. Confirm Applied Settings

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Compare runtime values with the values in your configuration file to ensure they were applied.

MySQL 8.0 Configuration Enhancements

MySQL 8 includes more advanced configuration options:

• persisted_globals_load: Load persisted variables at startup
• SET PERSIST: Save global variables to disk for automatic loading

SET PERSIST max_connections = 500;

This automatically updates the file mysqld-auto.cnf.

MySQL configuration files (my.cnf / my.ini) are critical for tailoring the server to meet your specific application needs. Proper configuration can drastically improve performance, reliability, and security. By understanding the available options, organizing the configuration properly, and following best practices, DBAs and developers can ensure that their MySQL instances run efficiently and remain stable in production environments. Whether adjusting buffer sizes, configuring logging, setting connection limits, or tuning replication, the configuration file is the central place for consistent and reliable control over MySQL server behavior.