[root@ ~]# cat /etc/my.cnf
#*************************
#Created By:    hotpu.cn
#*************************
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
#mysqladmin=
#user=root
#password=
log=/data/mysql/multi.log

[mysqld_safe]
user=mysql
open-files-limit=65535

[mysql]
no-auto-rehash
prompt='\\u@\\h:\\d \\v \\r:\\m:\\s> '
default-character-set=utf8mb4
show-warnings
#socket=/data/mysql/mysqldata3306/sock/mysql.sock

[mysqldump]
default-character-set=utf8mb4
max-allowed-packet=2G
net-buffer-length=32K

[mysqld]
#***********************************common parameters******************************
basedir=/usr/local/mysql

skip-federated
skip-blackhole
skip-name-resolve
loose-validate-password=off
loose-mysqlx=off

performance-schema=1

event-scheduler=ON
default-storage-engine=InnoDB

lower-case-table-names=1

explicit-defaults-for-timestamp
sql-mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

optimizer-switch='index_merge_intersection=off,loosescan=off,batched_key_access=on'
max-seeks-for-key=10000

user=mysql

group-concat-max-len=1048576

back-log=1000
max-connections=8400
max-user-connections=8200
thread-cache-size=128
max-connect-errors=99999
wait-timeout=172800
interactive-timeout=172800
net-read-timeout=30
net-write-timeout=60
net-buffer-length=32K
max-allowed-packet=64M

max-heap-table-size=1G
tmp-table-size=2M

sort-buffer-size=8M
max-length-for-sort-data=16k
join-buffer-size=4M   #bka
read-rnd-buffer-size=8M  #mrr
open-files-limit=65535
table-open-cache=10240
table-open-cache-instances=16

loose-query-cache-type=0
loose-query-cache-size=0
#query-cache-limit=1M

#lock-wait-timeout=300


#*******************************  Logs related settings ***************************
#general-log
loose-log-warnings=2
loose-log-error-verbosity=3
loose-log-timestamps=system
long-query-time=0.3
slow-query-log
#log-queries-not-using-indexes
log-slow-slave-statements
log-slow-admin-statements

binlog-cache-size=256K
max-binlog-size=512M

binlog-format=MIXED
#binlog-checksum=CRC32
expire-logs-days=1

#*******************************  Replication related settings **********************

#skip-slave-start
#slave-skip-errors=
slave-type-conversions='ALL_NON_LOSSY'
loose-rpl-semi-sync-master-enabled=1
loose-rpl-semi-sync-slave-enabled=1
loose-rpl-semi-sync-master-wait-no-slave=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
#slave-parallel-workers=4
#loose-slave-parallel-type=LOGICAL_CLOCK
#loose-binlog-group-commit-sync-delay=1   #5.7
#loose-binlog-group-commit-sync-no-delay-count=10   #5.7
#loose-binlog-max-flush-queue-time=1  #5.6
log-slave-updates=1
log-bin-trust-function-creators=1
auto-increment-increment=1
auto-increment-offset=1
allow-suspicious-udfs
loose-innodb-support-xa=1
sysdate-is-now


#******************************* MyISAM Specific options ****************************
key-buffer-size=8M
bulk-insert-buffer-size=16M
myisam-sort-buffer-size=64M
myisam-max-sort-file-size=10G
myisam-repair-threads=1
myisam-recover-options=default
read-buffer-size=1M


#***************************** INNODB Specific options ****************************
loose-innodb-use-sys-malloc=1

innodb-buffer-pool-instances=8
innodb-max-dirty-pages-pct=40
innodb-sort-buffer-size=16M
large-pages

innodb-autoextend-increment=128
innodb-file-per-table
innodb-open-files=7168
loose-innodb-file-format=Barracuda
loose-innodb-file-format-check=1
#innodb-checksum-algorithm=CRC32

innodb-fast-shutdown=1

innodb-log-buffer-size=64M
innodb-log-files-in-group=2
innodb-adaptive-flushing-lwm=30

innodb-read-io-threads=16
innodb-write-io-threads=8
innodb-flush-method=O_DIRECT
innodb-flush-neighbors=0
innodb-lru-scan-depth=1024
innodb-change-buffering=all
innodb-purge-threads=1

innodb-commit-concurrency=16
innodb-thread-concurrency=64
innodb-concurrency-tickets=1024
innodb-autoinc-lock-mode=1

innodb-stats-on-metadata=0
innodb-lock-wait-timeout=300
innodb-rollback-on-timeout=1
innodb-print-all-deadlocks=1

[mysqld3309]
pid-file=/data/mysql/mysqldata3309/sock/mysql.pid
port=3309
socket=/data/mysql/mysqldata3309/sock/mysql.sock
datadir=/data/mysql/mysqldata3309/mydata
tmpdir=/data/mysql/mysqldata3309/tmpdir
slave-load-tmpdir=/data/mysql/mysqldata3309/tmpdir

general-log-file=/data/mysql/mysqldata3309/log/general.log
log-error=/data/mysql/mysqldata3309/log/error.log
slow-query-log-file=/data/mysql/mysqldata3309/log/slow-query.log

server-id=02033309
log-bin-index=/data/mysql/mysqldata3309/binlog/mysql-bin.index
log-bin=/data/mysql/mysqldata3309/binlog/mysql-bin
sync-binlog=0

relay-log-index=/data/mysql/mysqldata3309/relaylog/mysql-relay-bin.index
relay-log=/data/mysql/mysqldata3309/relaylog/mysql-relay-bin

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci


innodb-buffer-pool-size=48G
innodb-data-home-dir=/data/mysql/mysqldata3309/innodb_ts
innodb-data-file-path=ibdata1:1024M:autoextend
innodb-log-group-home-dir=/data/mysql/mysqldata3309/innodb_log
innodb-log-file-size=16G
innodb-flush-log-at-trx-commit=0
innodb-io-capacity=800

innodb-force-recovery=0


gtid-mode=ON
enforce-gtid-consistency