MySQL configuration file template

From: Http://www.fromdual.com/mysql-configuration-file-sample

The MySQL and MariaDB default configuration is not a very great for production use. Some of the default values in MY.CNF should is changed when your need it for business critical applications.

The following MySQL configuration file is in we opinion a good average configuration file for MySQL, Galera Cluster, Mari ADB and Percona Server. For MySQL servers more performance tuning are not need in the most cases.

Download the sample MySQL and MariaDB configuration file with wget directly from here.

#
# fromdual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server
# Location:/etc/my.cnf or/etc/mysql/my.cnf
# This template was intended to work with MySQL 5.6 and newer and MariaDB 10.0 and newer
# Get Most recent updated from here:
# Http://www.fromdual.com/mysql-configuration-file-sample
#

[Client]

Port = 3306
socket =/var/run/mysqld/mysql.sock # use Mysqld.sock on Ubuntu, conflicts with AppArmor otherwise

[MySQL]

No_auto_rehash
Max_allowed_packet = 16M
prompt = ' \[email protected]\h [\d]> ' # ' [email protected] [schema]> '
Default_character_set = UTF8 # possibly this setting are correct for most recent Linux systems

[Mysqldump]

Max_allowed_packet = 16M

[Mysqld_safe] # becomes sooner or later obsolete with SYSTEMD

Open_files_limit = 8192 # You possibly has to adapt your O/S settings as well
user = MySQL
Log-error =

[Mysqld]

# Connection and Thread variables

Port = 3306
socket =/var/run/mysqld/mysql.sock # use Mysqld.sock on Ubuntu, conflicts with AppArmor otherwise
# basedir =/usr
# DataDir =/var/lib/mysql
# tmpdir =/tmp
# innodb_tmpdir =/var/lib/mysql # mysql 5.7

Max_allowed_packet = 16M
Default_storage_engine = InnoDB
# explicit_defaults_for_timestamp = 1 # MySQL 5.6, Test carefully! This can has an impact on application.
# Disable_partition_engine_check = true # Since MySQL 5.7.17 to 5.7.20. To get rid of nasty message in error log

# character_set_server = UTF8 # If you prefer UTF8
# collation_server = Utf8_general_ci

max_connections = 505 # Values < typically good
max_user_connections = # Limit One specific user/application
thread_cache_size = 505 # max_connections makes sense

# Query Cache

Query_cache_type = 1 # Set to 0 to avoid global QC Mutex
Query_cache_size = 32M # Avoid too big (> 128M) QC because of QC Clean-up lock!

# Session variables

Sort_buffer_size = 2M # Could is too big for many small sorts
Tmp_table_size = 32M # Make sure your temporary results does not contain blob/text attributes

Read_buffer_size = 128k # Resist to the change this parameter if you don't know what is doing
Read_rnd_buffer_size = 256k # Resist to the change this parameter if you don't know what is doing
Join_buffer_size = 128k # Resist to the change this parameter if you don't know what is doing

# Other buffers and caches

Table_definition_cache = 1400 # as big as many tables you have
Table_open_cache = Connections x tables/connection (~)
table_open_cache_instances = # New Default in 5.7

# MySQL Error Log

Log_error = # log_timestamps = SYSTEM # MySQL 5.7, equivalent to old behaviour
Log_warnings = 2
# log_error_verbosity = 3 # MySQL 5.7, equivalent to log_warnings = 2
# innodb_print_all_deadlocks = 1
# wsrep_log_conflicts = 1 # for Galera only!

# Slow Query Log

Slow_query_log_file = Slow_query_log = 0
log_queries_not_using_indexes = 0
Long_query_time = 0.5
Min_examined_row_limit = 100

# General Query Log

General_log_file = General_log = 0

# Performance Schema

# Performance_schema = on # some older MariaDB releases
Performance_schema_consumer_events_statements_history_long = on # MySQL 5.6 and newer

# Binary Logging and Replication

server_id = 42
Log_bin = # master_verify_checksum = on # MySQL 5.6
Binlog_cache_size = 1M
Binlog_stmt_cache_size = 1M
Max_binlog_size = 128M # make bigger for-traffic to reduce number of files
Sync_binlog = 0 # Set to 1 or higher to reduce potential loss of binary-log data
Expire_logs_days = 5 # We'll survive Easter holidays
Binlog_format = ROW # Use MIXED If you experience some troubles
# binlog_row_image = MINIMAL # Since 5.6
# auto_increment_increment = 2 # for master/master set-ups use 2 for both nodes
# auto_increment_offset = 1 # for master/master set-ups use 1 and 2

# Slave variables

log_slave_updates = 1 # Use if slave are used for Backup and PiTR
read_only = 0 # Set to 1 to prevent writes on Slave
# super_read_only = 0 # Set to 1 to prevent writes on Slave for users with super privilege. Since 5.7
# Skip_slave_start = 1 # To avoid start of slave thread
# Relay_log = # relay_log_info_repository = table # MySQL 5.6
# master_info_repository = table # MySQL 5.6
# Slave_load_tmpdir = '/tmp '

# Crash-safe Replication Master

# binlog_checksum = CRC32 # Default
# sync_binlog = 1 # default since 5.7.6, but slow!
# innodb_support_xa = 1 # default, depracted since 5.7.10

# Crash-safe Replication Slave

# master_info_repository = TABLE
# relay_log_info_repository = TABLE
# relay_log_recovery = 1
# sync_relay_log_info = 1
# relay_log_purge = 1 # Default
# slave_sql_verify_checksum = 1 # Default

# GTID Replication

# gtid_mode = on # Master and Slave
# enforce_gtid_consistency = 1 # Master and Slave

# log_bin =%instancedir%/binlog/%uname%_%instance%_binlog # in 5.6 also on Slave
# log_slave_updates = 1 # in 5.6 also on slave

# Security variables

# local_infile = 0 # If You are security aware
# Secure_auth = 1 # If You are security aware
# Sql_mode = traditional,only_full_group_by,no_engine_substitution,strict_all_tables,no_zero_in_date,no_zero_date , Error_for_division_by_zero,no_auto_create_user # is careful changing this afterwards
# skip_name_resolve = 0 # Set to 1 if you don't trust your DNS or experience problems
# Secure_file_priv = '/tmp ' # chmod, adjust AppArmor configuration:/etc/apparmor.d/usr.sbin.mysqld

# MyISAM variables

Key_buffer_size = 8M # Set to 25-33% of RAM if your still use MyISAM
myisam_recover_options = ' Backup,force '
# disabled_storage_engines = ' myisam,memory ' # MySQL 5.7, do not during/before Mysql_upgrade, good for galera!

# MEMORY variables

Max_heap_table_size = 64M # should is greater or equal to Tmp_table_size

# InnoDB variables

Innodb_strict_mode = On
Innodb_file_format_check = 1
# Innodb_file_format = Barracuda # for dynamic and compressed InnoDB tables
Innodb_buffer_pool_size = 128M # Go to 80% of your available RAM
Innodb_buffer_pool_instances = 8 # bigger if huge InnoDB buffer pool or high concurrency

innodb_file_per_table = 1 # is the recommended-nowadays
# Innodb_flush_method = o_direct # O_direct is sometimes better for DIRECT attached storage
# innodb_write_io_threads = 8 # If You have a strong I/O system or SSD
# innodb_read_io_threads = 8 # If You have a strong I/O system or SSD
# innodb_io_capacity = If You have a strong I/O system or SSD

Innodb_flush_log_at_trx_commit = 2 # 1 for durability, 0 or 2 for performance
Innodb_log_buffer_size = 8M # bigger If Innodb_flush_log_at_trx_commit = 0
Innodb_log_file_size = 256M # bigger means more write throughput but longer recovery time

# Galera Specific MySQL parameter

# default_storage_engine = InnoDB # Galera only works with InnoDB
# innodb_flush_log_at_trx_commit = 2 # Durability is achieved by committing to the Group
# Innodb_autoinc_lock_mode = 2 # for parallel applying
# Binlog_format = row # Galera only works with RBR
# query_cache_type = 0 # Use QC with Galera only in a master/slave set-up
# query_cache_size = 0

# Wsrep parameter

# wsrep_on = on # MariaDB >= 10.1
# wsrep_provider = none # Start mysqld without Galera
# wsrep_provider =/usr/lib/galera/libgalera_smm.so # location of Galera Plugin on Ubuntu?
# wsrep_provider =/usr/lib64/galera-3/libgalera_smm.so # location of Galera Plugin on CentOS 7
# wsrep_provider_options = ' gcache.size = 1G ' # Depends on your workload, WS kept for IST

# wsrep_cluster_name = "My cool galera cluster" # same cluster name for all nodes
# wsrep_cluster_address = "gcomm://" # Old Bootstrap method
# wsrep_cluster_address = "gcomm://192.168.0.2,192.168.0.3" # Start other nodes like this

# Wsrep_node_name = "Node A" # Unique node name
# wsrep_node_address = 192.168.0.1 # Our address where replication was done
# wsrep_node_incoming_address = 10.0.0.1 # Our external interface where application comes from
# wsrep_sync_wait = 1 # If You need realy full-synchronous replication (Galera 3.6 and newer)
# wsrep_slave_threads = 4-8 per core, not more than wsrep_cert_deps_distance

# wsrep_sst_method = rsync # SST Method (initial full sync): mysqldump, rsync, Rsync_wan, Xtrabackup-v2
# Wsrep_sst_auth = sst:secret # Username/password for SST user
# wsrep_sst_receive_address = 192.168.0.1 # Our address where to receive SST

# Group Replication parameter

# default_storage_engine = InnoDB # Group Replication only works with InnoDB
# server_id =%serverid% # should is different on all 3 nodes
# log_bin =%instancedir%/binlog/%uname%_%instance%_binlog # Locate outside of DataDir, adjust AppArmor configuration:/et C/apparmor.d/local/usr.sbin.mysqld
# Binlog_format = ROW
# binlog_checksum = NONE # default!
# Gtid_mode = On
# enforce_gtid_consistency = On
# master_info_repository = TABLE
# relay_log_info_repository = TABLE
# log_slave_updates = On

# slave_parallel_workers = <n> # 1-2/core, Max. 10
# Slave_preserve_commit_order = On
# Slave_parallel_type = Logical_clock

# transaction_write_set_extraction = XXHASH64

# Loose-group_replication_group_name = "$ (uuidgen)" # must is the same on all nodes
# loose-group_replication_start_on_boot = OFF
# loose-group_replication_local_address = "192.168.0.1"
# loose-group_replication_group_seeds = "192.168.0.1,192.168.0.2,192.168.0.3" # All nodes of Cluster
# Loose-group_replication_bootstrap_group = OFF
# loose-group_replication_single_primary_mode = FALSE # = Multi-primary

MySQL configuration file template