Skip to content

Instantly share code, notes, and snippets.

@kien-truong

kien-truong/gist:02b5e9b33899805a2fda533d22455eac

Created August 5, 2019 03:30
Download ZIP

Revisions

  1. kien-truong created this gist Aug 5, 2019.
    109 changes: 109 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,109 @@
    [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