This page provides a comprehensive, real-world MySQL configuration baseline for current Debian and RHEL releases with current best practices for DevOps environments.
Typical locations:
/etc/mysql/mysql.conf.d/mysqld.cnf
/etc/mysql/conf.d/*.cnf
/etc/mysql/mysql.conf.d/
Typical locations:
/etc/my.cnf
/etc/my.cnf.d/*.cnf
/usr/my.cnf
~/.my.cnf
When running MySQL in containers, configuration files are typically located at:
/etc/mysql/conf.d/*.cnf
mysqld Configuration (Production Ready)Use this as a baseline for a production service with private-network clients. Adjust values based on your hardware specifications.
[mysqld]
# Server identification
server-id = 1
report-host = mysql-primary.local
# Network
bind-address = 0.0.0.0 # Change to specific IP for security
port = 3306
skip-name-resolve = ON
# Connections
max_connections = 200
max_connect_errors = 100000
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
net_read_timeout = 30
net_write_timeout = 60
# Character set / collation
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'
# InnoDB settings (adjust based on available RAM - typically 70-80% of total RAM)
innodb_buffer_pool_size = 2G # Adjust based on your RAM
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_method = O_DIRECT
innodb_page_cleaners = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_lru_scan_depth = 1024
innodb_flush_neighbors = 0 # For SSD storage
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = OFF
innodb_print_all_deadlocks = ON
innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_load_at_startup = ON
# Performance Schema (for monitoring)
performance_schema = ON
performance_schema_consumer_events_statements_history_long = ON
# Binary logging (essential for replication and point-in-time recovery)
log-bin = mysql-bin
binlog-format = ROW
binlog-row-image = minimal
server-id = 1
expire_logs_days = 7 # Use binlog_expire_logs_seconds in newer versions
binlog_expire_logs_seconds = 604800
sync_binlog = 1
max_binlog_size = 100M
binlog_cache_size = 1M
binlog_stmt_cache_size = 1M
# Slow query logging
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes = ON
# General logging
log-error = /var/log/mysql/error.log
general_log = OFF
general_log_file = /var/log/mysql/mysql.log
# Security
local-infile = OFF
require-secure-transport = OFF # Set to ON in production with SSL
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# Query cache (disabled in MySQL 8.0+, kept here for reference)
query_cache_type = OFF
query_cache_size = 0
# Temporal settings
lower_case_table_names = 0
event_scheduler = ON
# Security and hardening
default_authentication_plugin = caching_sha2_password
disconnect_on_expired_password = ON
validate_password.policy = MEDIUM
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1
For development or staging environments where performance isn’t critical:
[mysqld]
# Network
bind-address = 127.0.0.1 # Localhost only for development
port = 3306
skip-name-resolve = ON
# Connections
max_connections = 50
# Character set / collation
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# InnoDB settings (minimal for development)
innodb_buffer_pool_size = 256M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2 # Less durable but faster for dev
innodb_flush_method = O_DIRECT
# Binary logging (enable for development if testing replication)
server-id = 1
log-bin = mysql-bin
binlog_expire_logs_seconds = 86400 # 1 day for dev
# Slow query logging
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1 # Higher threshold for dev
# General hardening
local-infile = OFF
# Security
default_authentication_plugin = caching_sha2_password
innodb_buffer_pool_size: Main data/index cache; allocate 70-80% of available RAM for dedicated MySQL serversinnodb_flush_log_at_trx_commit: 1 for full ACID compliance, 2 for better performance with slight durability riskmax_connections: Controls concurrency; each connection has memory overhead (~256KB-1MB per connection)innodb_io_capacity: Set based on your storage type (SSD: 2000+, HDD: 200-300)bind-address: Restrict MySQL to trusted interfaces only; use 127.0.0.1 for local-only accessskip-name-resolve: Avoid DNS lookups for faster/stable authentication behaviorlocal-infile=OFF: Prevents loading local files, reducing security risksdefault_authentication_plugin=caching_sha2_password: More secure authentication methodlog-bin: Enables binary logs for replication and point-in-time recoveryserver-id: Unique identifier for replication topologybinlog_expire_logs_seconds: Controls retention period for binary logssync_binlog=1: Ensures binary log sync after each transaction commitslow_query_log: Essential for identifying performance bottlenecksperformance_schema: Built-in monitoring and diagnosticslong_query_time: Threshold for logging slow queries (in seconds)Create dedicated app users with principle of least privilege:
-- Create application user with limited permissions
CREATE USER 'appuser'@'10.0.0.%' IDENTIFIED BY 'strong-password-here';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'10.0.0.%';
FLUSH PRIVILEGES;
-- Create read-only user for reporting
CREATE USER 'report_user'@'10.0.0.%' IDENTIFIED BY 'strong-password-here';
GRANT SELECT ON appdb.* TO 'report_user'@'10.0.0.%';
FLUSH PRIVILEGES;
-- Create backup user with necessary privileges
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong-password-here';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
Before applying configuration changes, validate the configuration file:
# Check configuration syntax
sudo mysqld --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --validate-config
# Or with the default config location
sudo mysqld --verbose --help | grep -A 1 "Default options"
Restart MySQL after configuration updates:
# On Debian/Ubuntu
sudo systemctl restart mysql
# On RHEL-family
sudo systemctl restart mysqld
# Check service status
sudo systemctl status mysql # Debian/Ubuntu
sudo systemctl status mysqld # RHEL-family
Validate configuration and runtime values:
# Check active configuration values
mysql -u root -p -e "SHOW VARIABLES LIKE 'bind_address';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"
# Check status variables
mysql -u root -p -e "SHOW STATUS LIKE 'Connections';"
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';"
# Check current configuration file being used
mysql -u root -p -e "SHOW VARIABLES LIKE 'config%';"
Some variables can be changed at runtime without restarting:
-- Example of dynamic configuration changes
SET GLOBAL max_connections = 250;
SET GLOBAL innodb_buffer_pool_size = 3221225472; -- 3GB in bytes
SET GLOBAL long_query_time = 1.0;
-- Note: Some changes require restart to persist after reboot
-- Use SET PERSIST for changes that should survive restarts
SET PERSIST max_connections = 250;
SET PERSIST innodb_buffer_pool_size = 3221225472;
3306 private (VPC/LAN only), never expose globally