This guide outlines essential MySQL configuration settings aimed at optimizing server performance, security, and replication management. The configurations detailed here are suitable for a broad range of applications, from web applications to more data-intensive services.
long_query_time to execute..ibd file.tmp_table_size is for temporary tables, while max_heap_table_size applies to memory tables.[mysqld]
symbolic-links=0
skip-name-resolve
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysql-slow-query.log
long_query_time=1
# Performance Tuning
max_connections=2000
innodb_buffer_pool_size=12G
innodb_log_file_size=1G
innodb_file_per_table=ON
thread_cache_size=100
query_cache_size=0
wait_timeout=60
max_allowed_packet=16M
tmp_table_size=256M
max_heap_table_size=256M
# Logging and Replication
log_bin=mysql-bin
server-id=1
binlog_format=mixed
MySQL allows the inclusion of other configuration files using the !includedir directive. This approach enables modular configuration management.
!includedir /etc/my.cnf.d
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# This group is read by the server
#
[mysqld]
# Basic Settings
symbolic-links=0
skip-name-resolve
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysql-slow-query.log
long_query_time=1
# Performance Tuning
max_connections=2000 # Adjusted based on a more typical workload
innodb_buffer_pool_size=12G # 75% of RAM, monitor and adjust as needed
innodb_log_file_size=1G # Adjust for high-write environments
innodb_file_per_table=ON
thread_cache_size=100 # Start with this and adjust based on threads_created status
query_cache_size=0 # Consider disabling query cache for modern MariaDB versions
wait_timeout=60
max_allowed_packet=16M # Adjust based on your needs
tmp_table_size=256M # Adjust based on your workload
max_heap_table_size=256M # Make sure it matches tmp_table_size
# Logging and Replication (if applicable)
# These are basic settings, customize based on your replication setup
log_bin=mysql-bin
server-id=1
binlog_format=mixed
!includedir /etc/my.cnf.d