How to Configure my.cnf for MySQL Server Settings

To configure MySQL server settings, edit the my.cnf file located typically in /etc/mysql/ or /etc/. This file uses sections like [mysqld] to set options such as port, data directory, and buffer sizes. After editing, restart the MySQL service to apply changes.

Syntax

The my.cnf file is divided into sections, each starting with a section header in square brackets like [mysqld]. Below each header, you write key-value pairs to set options.

• Section headers: Define which part of MySQL the settings apply to (e.g., [client], [mysqld]).
• Options: Written as option_name=value, these control server behavior.

ini

[mysqld]
option_name=value
another_option=value

[client]
option_name=value

Example

This example shows a simple my.cnf configuration that sets the MySQL server port, data directory, and enables the slow query log.

ini

[mysqld]
port=3306
datadir=/var/lib/mysql
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

Output

MySQL server will listen on port 3306, store data in /var/lib/mysql, and log slow queries taking longer than 2 seconds to /var/log/mysql/mysql-slow.log.

Common Pitfalls

Common mistakes when configuring my.cnf include:

• Using incorrect file paths for datadir or log files, causing MySQL to fail starting.
• Syntax errors like missing equal signs or spaces around =.
• Editing the wrong my.cnf file if multiple exist on the system.
• Not restarting MySQL after changes, so new settings don’t apply.

ini

[mysqld]
port 3306  # Wrong: missing '=' sign

# Correct:
port=3306

Quick Reference

Option	Description	Example
port	Sets the TCP port MySQL listens on	port=3306
datadir	Directory where MySQL stores data files	datadir=/var/lib/mysql
socket	Unix socket file for local connections	socket=/var/run/mysqld/mysqld.sock
slow_query_log	Enable slow query logging (1=on, 0=off)	slow_query_log=1
slow_query_log_file	File path for slow query log	slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time	Threshold in seconds for slow queries	long_query_time=2

Key Takeaways

Edit the correct my.cnf file and use proper section headers like [mysqld] for server settings.

Write options as key=value pairs without spaces around the equal sign.

Always restart MySQL after changing my.cnf to apply new settings.

Check file paths carefully to avoid startup errors.

Use the quick reference table to remember common options and their usage.