MariaDB configuration file

my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server
# Location: /etc/my.cnf or /etc/mysql/my.cnf
# This template is intended to work with MySQL/MariaDB 5.5 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


[mysql]

no_auto_rehash
max_allowed_packet             = 16M
prompt                         = '\u@\h [\d]> '
default_character_set          = utf8                                # Possibly this setting is correct for most recent Linux systems


[mysqldump]

max_allowed_packet             = 16M


[mysqld_safe]

open_files_limit               = 8192                                # You possibly have to adapt your O/S settings as well
user                           = mysql
log-error                      = hostname_error.log


[mysqld]

# Connection and Thread variables

port                           = 3306
socket                         = /var/run/mysqld/mysql.sock
datadir                        = /mnt/smt/database/mysql
tmpdir                         = /tmp
max_allowed_packet             = 16M
default_storage_engine         = InnoDB
max_connections                = 505                                 # Values < 1000 are typically good
max_user_connections           = 500                                 # Limit one specific user/application
thread_cache_size              = 505                                 # Up to 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 be too big for many small sorts
tmp_table_size                 = 32M                                 # Make sure your temporary results do NOT contain BLOB/TEXT attributes

read_buffer_size               = 128k                                # Resist to change this parameter if you do not know what you are doing
read_rnd_buffer_size           = 256k                                # Resist to change this parameter if you do not know what you are doing
join_buffer_size               = 128k                                # Resist to change this parameter if you do not know what you are doing


# Other buffers and caches

table_definition_cache         = 400                                 # As big as many tables you have
table_open_cache               = 512                                 # connections x tables/connection (~2)


# MySQL error log

log_error                      = /var/log/mysql/mysql-error.log
log_warnings                   = 2


# Slow Query Log

slow_query_log_file            = /var/log/mysql/mysql-slow.log
slow_query_log                 = 0
log_queries_not_using_indexes  = 0
long_query_time                = 1
min_examined_row_limit         = 100


# General Query Log

general_log_file               = /var/log/mysql/mysql-query.log
general_log                    = 0


# Binary logging and Replication

server_id                      = 42
log_bin                        = hostname_binlog                     # Locate outside of datadir
binlog_cache_size              = 1M
binlog_stmt_cache_size         = 1M
max_binlog_size                = 128M                                # Make bigger for high 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 will survive easter holidays
binlog_format                  = ROW                                 # Use MIXED if you experience some troubles

# MyISAM variables

key_buffer_size                = 8M                                  # Set to 25 - 33 % of RAM if you still use MyISAM
myisam_recover_options         = 'BACKUP,FORCE'


# MEMORY variables

max_heap_table_size            = 64M                                 # Should be greater or equal to tmp_table_size


# InnoDB variables

innodb_strict_mode             = ON
innodb_file_format_check       = 1
innodb_buffer_pool_size        = 512M                                # Go up to 80% of your available RAM
innodb_file_per_table          = 1                                   # Is the recommended way nowadays
innodb_flush_log_at_trx_commit = 0                                   # 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


#
# include all files from the config directory
#
!includedir /etc/my.cnf.d