my.cnf configuration templates

gistfile1.txt

[mysql]

# CLIENT #
port                           = {{mysql_port}}
socket                         = {{home_dir}}/mysql.sock
host                           = 127.0.0.1

[mysqld]

# SERVER #
port                           = {{mysql_port}}
socket                         = {{home_dir}}/mysql.sock

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = {{home_dir}}/mysql.sock
pid-file                       = {{home_dir}}/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = {{home_dir}}

# BINARY LOGGING #
log-bin                        = {{home_dir}}/mysql-bin
log-bin-compress               = 1
log-slave-updates              = 1
expire-logs-days               = 1
sync-binlog                    = 0
binlog-format                  = ROW

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 600
max-user-connections           = {{ mysql_max_user_connections }}
thread-cache-size              = 50
thread-handling                = pool-of-threads
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = {{ (innodb_buffer_size / 8) | round(0, 'ceil') | int }}G
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table          = 1
innodb-buffer-pool-size        = {{ innodb_buffer_size }}G
{% if innodb_buffer_size < 64 %}
innodb-buffer-pool-instances   = {{ innodb_buffer_size }}
{% else %}
innodb-buffer-pool-instances   = 64
{% endif %}
innodb-autoinc-lock-mode       = 2
innodb-stats-on-metadata       = 0
{% if innodb_undo_tablespaces | default(0) > 0 %}
innodb-undo-tablespaces        = {{ innodb_undo_tablespaces }}
innodb-undo-log-truncate       = 1
innodb-max-undo-log-size       = 1G
{% endif %}

# REPLICATION
server-id                      = {{ server_id }}
log-basename                   = {{ log_basename }}

# Galera
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=5G;gcs.fc_limit=400;gcs.fc_factor=0.8"
wsrep_slave_threads=80
wsrep_sst_method=mariabackup
wsrep_sst_auth={{ mariabackup_user }}:{{ mariabackup_password }}
wsrep_node_address={{ ansible_host }}
wsrep_cluster_address=gcomm://{% for h in groups['mysql'] -%}{{ hostvars[h]['ansible_host'] }}{% if not loop.last -%},{%- endif %}{%- endfor %}


# Default character set and collation for new database
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

# LOGGING #
log-error                      = {{home_dir}}/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = {{home_dir}}/mysql-slow.log

# Enable performance schema
performance-schema             = ON

# Skip name resolve
skip-name-resolve              = ON

{% if proxy_protocol_enabled %}
# Enable proxy protocol support
proxy-protocol-networks        = localhost,{% for h in groups['haproxy'] -%}{{ hostvars[h]['ansible_host'] }}{% if not loop.last -%},{%- endif %}{%- endfor %}
{% endif %}

# Enable event scheduler
event-scheduler                = 1