MariaDB 10 example my.cnf

How to install mariadb10:

1) mkdir /var/tmp/mysql && chown mysql:mysql /var/tmp/mysql
2) add "none /var/tmp/mysql tmpfs size=2G,noexec 0 0" to fstab and mount it
3) touch /var/log/mysqld-master-slow.log && chown mysql:mysql /var/log/mysqld-master-slow.log
4) remove default dataset: rm -rf /var/lib/mysql/*
5) edit appropriate config (see bellow)
6) create appropriate dataset by invoking "mysql_install_db"
7) start mariadb and make initial setting by invoking "mysql_secure_installation"

[server]


[mysqld]
user                            =   mysql
character-set-server            =   utf8
collation-server                =   utf8_czech_ci

datadir                         =   /var/lib/mysql            #<< EDIT
tmpdir                          =   /var/tmp/mysql            #<< EDIT (ramdisk)
port                            =   3306
log-error                       =   /var/log/mysqld.log
slow-query-log-file             =   /var/log/mysqld-master-slow.log

query_cache_size                =   512M                      #<< EDIT
query_cache_limit               =   2M

key_buffer_size                 =   512M                      #<< EDIT (sum of all indexes preferably)
key_cache_segments              =   16

max_allowed_packet              =   16M
skip_name_resolve

sort_buffer_size                =   2M
read_buffer_size                =   256K
join_buffer_size                =   8M
read_rnd_buffer_size            =   16M


net_buffer_length               =   16K
tmp_table_size                  =   64M
max_heap_table_size             =   64M

myisam_sort_buffer_size         =   256M                     # Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.
ft_min_word_len                 =   3

thread_stack                    =   1M
max_connections                 =   2500                     #<< EDIT

table_open_cache                =   16384                    #<< EDIT
table_definition_cache          =   16384                    #<< EDIT

thread_pool_max_threads          =   1000
thread_pool_size                =   8
thread_pool_idle_timeout        =   60
thread_handling                 =   pool-of-threads

wait_timeout                    =   300
connect_timeout                 =   20

myisam-recover-options          =   BACKUP,FORCE

symbolic-links                  =   0
log-slow-rate-limit             =   1
slow-query-log                  =   1
long-query-time                 =   10


innodb_data_file_path           =   ibdata1:64M:autoextend
innodb_buffer_pool_size         =   2G                       #<< EDIT: 70% of RAM (dedicated VM)
innodb_buffer_pool_instances    =   2                        #<< EDIT: add 1 for every 1GB of pool_size
innodb_file_per_table           =   ON
innodb_log_file_size            =   64M
innodb_log_files_in_group       =   2
innodb_file_format              =   Barracuda

innodb_log_buffer_size          =   8M

innodb_flush_log_at_trx_commit  =   1             #<< see: https://mariadb.com/kb/en/mariadb/binlog-group-commit-and-innodb_flush_log_at_trx_commit/
innodb_force_recovery           =   0

aria_pagecache_buffer_size      =   256M
aria_repair_threads             =   4

server-id                       =   1                        #<< EDIT
gtid_domain_id                  =   1                        #<< EDIT - mandatory with multimaster repl.
gtid_strict_mode                =   ON
report-host                     =   host.domain.cz           #<< EDIT

log-bin                         =   host-domain-binlog       #<< EDIT
sync_binlog                     =   1                        #<< EDIT: see flush_log_at_trx_commit
binlog_format                   =   mixed
expire_logs_days                =   10                       #<< EDIT
max_binlog_size                 =   1G                       
log-warnings



[embedded]
[mariadb]
[mariadb-10.0]