1.server启动相关
2.日志相关(log)
3.session&timeout相关
4.复制相关(Replication)
5.InnoDB引擎相关
6.Metrics相关

1.Server启动相关

port= 3306
socket= /tmp/mysql3306.sock
datadir = /data/mysql/mysql3306/data
tmpdir=/data/mysql/mysql3306/tmp
log-bin = /data/mysql/mysql3306/logs/mysql-bin
server_id=3306
mysqlx_port=33060
admin_port=33062
admin_address='127.0.0.1'
create_admin_listener_thread=on
max_connections=500
max_user_connections=400
max_connect_errors=300
mysqlx_max_connections=300

2.日志相关(log)

#binlog
log-bin = /data/mysql/mysql3306/logs/mysql-bin
binlog_format = row
log_slave_updates
gtid_mode=on
enforce_gtid_consistency=on
binlog_cache_size = 1M
max_binlog_size = 512M
binlog_rows_query_log_events=on

sync_binlog = 1
binlog_group_commit_sync_delay=1000
binlog_group_commit_sync_no_delay_count=10
binlog_order_commits=off
expire_logs_days =5
#slow log
log_long_query_time=0.5
log_slave_updates=on
slow_query_log_file=slow.log
#error log
log_error=error.log
log_error_verbosity=3

3.1Session级别参数

sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64-128
thread_stack =192K
tmp_table_size = 96M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

3.2timeout参数

interactive_timeout=300
wait_timeout=300
innodb_rollback_on_timeout=on
slave_net_timeout=30
rpl_stop_slave_timeout=180
lock_wait_timeout=300

4.1复制相关的配置

#relay_log
relay_log=relay-bin
relay_log_info_file=relay-bin.index
relay_log_info_repository=table
relay_log_purge=on
sync_relay_log=10000
sync_relay_log_info=10000

4.2sql_thread优化

relay_log_recovery=ON
slave_preserve_commit_order=OFF
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
transaction_write_set_extraction='XXHASH64'
binlog_transaction_dependency_tracking='wrieiset'
binlog_transaction_dependency_history_size=25000

5.InnoDB配置相关

innodb_buffer_pool_size =内存的50-75%
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
innodb_log_file_size =1024M
innodb_log_files_in_group = 4
innodb_max_dirty_pages_pct = 90
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_print_all_deadlocks=1