【MySQL运维】MySQL 8.0.35 配置文件模板

该模板内容根据MySQL 8.0.35进行相关优化，开启了GTID以及主从增强半同步复制功能，该版本已移除或新增的选项已经尽可能的修改，在使用前建议先熟悉每个配置项的作用，参数的说明可以参考《【MySQL运维】MySQL 5.7 配置文件模板及详细说明》，然后根据服务器与业务的实际情况进行调整。

MySQL 8.0 新增参数说明：

· admin_address：指定一个专门用于管理连接的地址，解决连接数被占满时连管理员都无法登录数据库的问题

· admin_port：配合admin_address参数使用，开启一个额外的管理员登录端口

[client]
port = 3333
socket = /tmp/mysql3333.sock

[mysql]
no-auto-rehash
prompt = "\\u@\\d \\R:\\m> "
default-character-set = utf8mb4

[mysqld]

default_authentication_plugin = mysql_native_password

### Admin ###
admin_address = 172.21.1.223
admin_port = 38333
create_admin_listener_thread = ON


### General ###
user									= mysql
port									= 3333
mysqlx 									= OFF
basedir									= /usr/local/mysql8
datadir									= /data/mysql3333/data
socket									= /tmp/mysql3333.sock
tmpdir									= /data/mysql3333/tmp/
character_set_server					= utf8mb4
collation_server                        = utf8mb4_general_ci
default-storage-engine					= InnoDB
default-time-zone						= "+8:00"
server-id								= 211223
transaction_isolation					= READ-COMMITTED
max_connect_errors						= 500
back_log								= 1024
open_files_limit						= 65535
max_allowed_packet						= 512M
init_connect							='set names utf8mb4'
group_concat_max_len					= 10240
table_open_cache						= 8192
table_definition_cache					= 8192
log_timestamps							= system
performance_schema						= ON
lower_case_table_names					= 0
tls_version								= ''
admin_tls_version						= ''
skip-name-resolve
skip-external-locking
#skip-grant-tables
explicit_defaults_for_timestamp 		= 0
sql_mode								= "NO_ENGINE_SUBSTITUTION"
#skip-character-set-client-handshake 	= 1

### Timeout ###
connect_timeout							= 20
interactive_timeout						= 3600
wait_timeout							= 3600
lock_wait_timeout						= 300
max_execution_time						= 60000
innodb_lock_wait_timeout				= 10
				
### Memory ###				
innodb_buffer_pool_size					= 4G
innodb_log_buffer_size 					= 32M
tmp_table_size         				= 64M
max_heap_table_size    					= 64M
key_buffer_size        					= 64M
max_connections        					= 1000
 read_buffer_size      					= 10M
 sort_buffer_size      					= 10M
 read_rnd_buffer_size  					= 10M
 join_buffer_size      					= 10M
 binlog_cache_size     					= 10M

### Error Log ###
log_error								= /data/mysql3333/log/error.log
log_error_suppression_list              = MY-013360


### Slow log ###
slow_query_log							= 1
slow_query_log_file 					= /data/mysql3333/log/slowlog/mysql.slow
long_query_time							= 1
log_slow_admin_statements				= 1
log_slow_replica_statements				= 1
log_queries_not_using_indexes			= 1
log_throttle_queries_not_using_indexes = 10
#min_examined_row_limit					= 100
		
### Binlog ###
sync_source_info						= 1
log_bin									= /data/mysql3333/log/binlog/mysql-bin
binlog_rows_query_log_events			= on
max_binlog_size							= 1024M
binlog_expire_logs_seconds 				= 864000

### Relay Log ###
#sync_relay_log_info 	       			= 1
sync_relay_log 							= 1
relay-log								= /data/mysql3333/log/relaylog/relay-bin
relay_log_recovery						= 1
relay-log-purge							= 1
		
### Slave ###		
skip_replica_start						= 1
replica_net_timeout						= 10 
log_replica_updates						= 1
replica_parallel_workers				= 8
replica_preserve_commit_order			= 1
transaction_write_set_extraction 		= XXHASH64
binlog_transaction_dependency_tracking  = WRITESET_SESSION
#read_only								= 1
#super_read_only						= 1

### Gtid ###
gtid_mode								= on
enforce_gtid_consistency				= 1
#binlog_gtid_simple_recovery 			= 0

### 半同步复制 ###
plugin_dir 								= /usr/local/mysql8/lib/plugin
plugin-load 							="rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
rpl_semi_sync_replica_enabled 			= ON
rpl_semi_sync_source_enabled 			= ON 
rpl_semi_sync_source_timeout 			= 5000
rpl_semi_sync_source_wait_for_replica_count = 1
rpl_semi_sync_source_wait_no_replica 	= OFF


### 双1 ###
innodb_flush_log_at_trx_commit 			= 1
sync_binlog 			   				= 1
		
### Innodb ###		
innodb_data_home_dir					= /data/mysql3333/data
innodb_data_file_path 					= ibdata1:1024M:autoextend
innodb_buffer_pool_instances 			= 16  
innodb_flush_method 		 			= O_DIRECT
innodb_file_per_table 					= 1
innodb_redo_log_capacity				= 16G
innodb_buffer_pool_load_at_startup		= 1
innodb_buffer_pool_dump_at_shutdown		= 1
innodb_read_io_threads 					= 16
innodb_write_io_threads 				= 16
innodb_purge_threads	 				= 8
innodb_io_capacity 	 					= 8000
innodb_io_capacity_max 	 				= 15000
innodb_max_dirty_pages_pct	 			= 75
innodb_flush_neighbors 	 				= 0
innodb_thread_concurrency      			= 128
innodb_strict_mode 	 					= 1
innodb_stats_on_metadata       			= 0
innodb_print_all_deadlocks 				= 1
innodb_autoinc_lock_mode				= 2
innodb_sort_buffer_size 				= 67108864
innodb_page_size 	  					= 16384
innodb_open_files						= 65535
innodb_rollback_on_timeout				= 1

[mysqldump]
quick
max_allowed_packet 						= 512M

[mysqld_safe]
open-files-limit						= 65535