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.