MySQL my.cnf Configuration Parameters Reference

This article presents a detailed reference of MySQL server configuration options (my.cnf), explaining each setting and its impact on performance, stability, and replication.

Client section

[client]
port = 3306

mysqld section (core server settings)

# Default storage engine
default-storage-engine=INNODB
# GROUP_CONCAT length
group_concat_max_len =99999
# Server port
port = 3306

# Socket file

socket = /usr/local/mysql/mysql.sock

# PID file location

pid-file = /usr/local/mysql/mysqld.pid

# Data directory

datadir = /home/data/mysql/data

# Open files limit

open_files_limit = 10240

# SQL mode

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Skip external locking to avoid performance loss on single‑server setups

skip-external-locking

# Skip DNS reverse lookup

skip-name-resolve

# Disable TIMESTAMP default value

explicit_defaults_for_timestamp

# Ensure server character set is not affected by client

skip-character-set-client-handshake

# Initial connection character set

init-connect='SET NAMES utf8'

# Server default character set

character-set-server=utf8

# Query cache settings (type, size, limit)

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# Connection timeout

connect_timeout = 20

# Slave network timeout

slave_net_timeout = 30

# Log slave updates and server ID for replication

log-slave-updates=1
replicate-same-server-id=0
server_id=10112879101

# Binary logging (required for master servers and point‑in‑time recovery)

log-bin =/home/data/mysql/binlog/mysql-bin.log
relay-log=mysql-relay-bin

# Repository settings for crash‑safe binary logs

master-info-repository=TABLE
relay-log-info-repository=TABLE

# Binlog ignore/do databases

binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-do-db=business_db
binlog-do-db=user_db
binlog-do-db=plocc_system

# Binlog retention and size

expire-logs-days=15
max_binlog_size = 1073741824
sync_binlog = 1000

# Replication filters

replicate-do-db=business_db
replicate-do-db=user_db
replicate-do-db=plocc_system

# Event scheduler

event_scheduler=1

# Connection handling (back_log, max_connections, max_user_connection, max_connect_errors)

back_log = 500
max_connections = 6000
max_user_connection = 3000
max_connect_errors = 6000

# Table cache settings

table_cache = 614
table_open_cache = 2048

# Maximum packet size

max_allowed_packet = 64M

# Binlog cache size

binlog_cache_size = 1M

# Memory table size limits

max_heap_table_size = 256M

# Sort and join buffers (per‑connection)

sort_buffer_size = 8M
join_buffer_size = 8M

# Thread cache and concurrency

thread_cache_size = 128
thread_concurrency = 8

# Thread stack size

thread_stack = 192K

# Transaction isolation level

transaction_isolation = READ-COMMITTED

# Temporary table size limits

tmp_table_size = 256M

# Binary log format

binlog_format=mixed

# Slow query log settings

slow_query_log
log_output = FILE
long_query_time = 0.5
slow_query_log_file=/usr/local/mysql/mysqld_slow.log

# Full‑text index minimum word length

ft_min_word_len = 4

# Full‑text index rebuild required after changing the above

This reference can be used by DBAs to adjust MySQL settings for optimal performance, reliability, and replication behavior.