my.cnf

################################################################
# Name: my.cnf
#
# Description:
# MySQL config file 
#
# History:
#
# edited by moti for 2.16
# 26.9.17 baruch@brillix.co.il add notes and fixed variables 
# 26.9.17 baruch@brillix.co.il add bin log location  
################################################################


[mysql]
prompt="\R:\m:\\s[\d](\l)>"
reconnect
show-warnings
ssl-ca = ca.pem
ssl-cert = client-cert.pem
ssl-key = client-key.pem

#no-locking

# MySQL angel/watchdog process options
[mysqld_safe]
core-file-size=1G
malloc-lib=/usr/lib64/libjemalloc.so
nice=19

[mysqld]
user=mysql
## only for manual install
#basedir=/usr/bin/mysql
datadir=/u01/data/mysql/1
ignore-db-dir=lost+found
tmpdir = /u01/temp
#bind-address =
open-files-limit=20000
max-connections=2000
#storage engine options
archive=OFF
blackhole=OFF
federated=OFF
#partition=OFF
default-storage-engine=innodb
default-tmp-storage-engine=innodb
internal-tmp-disk-storage-engine=innodb
disabled-storage-engines=myisam

memlock=ON
key_buffer_size=32M
table_open_cache=8000
table-open-cache-instances = 16
back-log=1500
query_cache_type=0
join_buffer_size=32K
sort_buffer_size=32K
#max-allowed-packet =
#tmp-table-size =
#max-heap-table-size =
skip-temp-pool

# binary log and replication options
#log-bin=/u01/data/mysql/1/mysql01-mysql-1
binlog-row-image=MINIMAL
binlog-rows-query-log-events=ON
log-bin-trust-function-creators=TRUE
gtid-mode=ON
enforce-gtid-consistency=ON
expire-logs-days=30
max-binlog-size=1G
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = ON
log-slave-updates = ON
slave-exec-mode = IDEMPOTENT
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave-preserve-commit-order = ON
slave-rows-search-algorithms = INDEX_SCAN,HASH_SCAN
slave-type-conversions = ALL_NON_LOSSY
sync-master-info = 1000
sync-relay-log = 1000

# character set, collation, and i18n options
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

# crash handling and debugging options
core-file

# security options
default-password-lifetime=0
#des-key-file=
#ssl-ca=ca.pem
#ssl-cert=server-cert.pem
#ssl-key=server-key.pem
#secure_file_priv=/u01/data/mysql
#ssl-crl =
#ssl-cipher =

# date and time handling options
default-time-zone=SYSTEM
explicit-defaults-for-timestamp=ON
# optimizer related options
#eq-range-index-dive-limit =
#optimizer-prune-level =
#optimizer-search-depth =
#range-optimizer-max-mem-size =

# scheduler options
event-scheduler=ON

# logging options
#log-error-verbosity =
# Performance Schema options
performance-schema-consumer-events-transactions-current=ON
performance-schema-consumer-events-transactions-history=ON

#early-plugin-load=keyring_file.so
#keyring_file_data=$data_dir/keyring_dir/keyring
# Enterprise features
#plugin-load=audit_log.so;thread_pool.so;authentication_pam.so;auth_socket.so
#audit-log=FORCE_PLUS_PERMANENT
#audit_log_rotate_on_size=1024M
#audit_log_statement_policy=ERRORS
thread-pool-size=64
# session tracking
#session-track-gtids = OWN_GTID
#session-track-schema = ON
#session-track-state-change = ON
#session-track-transaction-info = ON

# InnoDB options
innodb-autoinc-lock-mode=2
innodb_flush_log_at_trx_commit=1
innodb-flush-method=O_DIRECT_NO_FSYNC
innodb-log-group-home-dir=/u01/data/mysql/1/
innodb-log-file-size=1G
innodb-log-files-in-group=6

innodb-log-write-ahead-size=4096
innodb-open-files=2048
#innodb_additional_mem_pool_size=20M
innodb-log-buffer-size=64M
innodb-monitor-enable=%
innodb-max-dirty-pages-pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb-print-all-deadlocks=ON
innodb-undo-log-truncate=ON
#innodb-undo-tablespaces=4
#innodb-undo-logs=64

server-id=1
innodb-buffer-pool-size=5G
innodb-buffer-pool-instances=8
innodb_flush_neighbors=0
innodb-io-capacity=200
innodb-io-capacity-max=400