mysql的my.cnf优化

[mysql]
default-character-set=utf8mb4
[mysqld]
socket=/mysql3306/mysql3306.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

port = 3306
#extra_port=13306
basedir = /usr/local/mysql57
datadir = /mysql3306/data
pid-file = /mysql3306/mysql.pid
user = mysql
server-id =1343306
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
#rpl_semi_sync_slave_enabled=1
relay_log_purge=0
read_only=0
super_read_only=0
slave-skip-errors=1396
binlog_row_image=full
# parallel replication
binlog_group_commit_sync_delay=10
binlog_group_commit_sync_no_delay_count=1000
master_info_repository='TABLE'
#
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 10
relay_log_info_repository='TABLE'
#

log_slave_updates=1
lower_case_table_names = 1
character-set-server=utf8mb4
skip-name-resolve
skip-external-locking
back_log = 500
max_connections = 2000
max_connect_errors = 2000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 64M

key_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 16M
sort_buffer_size = 32M
join_buffer_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
query_cache_size = 16M
query_cache_limit = 16M
thread_cache_size = 64

log_bin = /mysql3306/binlog/mysql-bin
binlog_format = row
binlog_cache_size = 16M
sync_binlog = 1
max_binlog_cache_size = 1000M
max_binlog_size = 1G
expire_logs_days = 10

log_error = /mysql3306/log/mysql-error.log
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file = //mysql3306/log/mysql-slow.log
relay_log=/mysql3306/binlog/slave-relay-bin
relay_log_index=slave-relay-bin.index

default_storage_engine = InnoDB
innodb_buffer_pool_size = 2G
innodb_file_per_table = 1
innodb_data_home_dir = /mysql3306/data
innodb_data_file_path = ibdata1:500M;ibdata2:1G:autoextend
innodb_log_group_home_dir = /mysql3306
innodb_log_file_size = 500M
innodb_log_buffer_size = 96M
innodb_flush_log_at_trx_commit = 1
innodb_print_all_deadlocks = 1

skip-slave-start=1
gtid-mode=on
enforce-gtid-consistency=true
slave-parallel-workers=8
sql_mode='NO_ENGINE_SUBSTITUTION'
log_timestamps=SYSTEM

转载于:https://www.cnblogs.com/sunshinea121/p/10649209.html