mysql-参数模板_MySQL 5.6&5.7参数模板

[client]

user=root

password=1111aaA_

[mysql]

prompt= [\\u@\\p][\\d]>\\_

no-auto-rehash

[mysqld_safe]

malloc-lib=tcmalloc

[mysqldump]

single-transaction

[mysqld]

# basic settings #

user=mysql

sql_mode= "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"autocommit= 1server-id = 8888character_set_server=utf8mb4

datadir=/mysql_data/data

transaction_isolation= READ-COMMITTED

explicit_defaults_for_timestamp= 1max_allowed_packet=64M

event_scheduler= 1# connection #

interactive_timeout= 1800wait_timeout= 1800lock_wait_timeout= 1800skip_name_resolve= 1max_connections= 1024max_user_connections= 256max_connect_errors= 1000000# table cache performance settings

table_open_cache= 4096table_definition_cache= 4096table_open_cache_instances= 64# session memory settings #

read_buffer_size=16M

read_rnd_buffer_size=32M

sort_buffer_size=32M

tmp_table_size=64M

join_buffer_size=128M

thread_cache_size= 64# log settings #

log_error=error.log

log_bin=binlog

log_error_verbosity= 2general_log_file=general.log

slow_query_log= 1slow_query_log_file=slow.log

log_queries_not_using_indexes= 1log_slow_admin_statements= 1log_slow_slave_statements= 1log_throttle_queries_not_using_indexes= 10expire_logs_days= 90long_query_time= 2min_examined_row_limit= 100log-bin-trust-function-creators = 1log-slave-updates = 1# innodb settings #

innodb_page_size= 16384innodb_buffer_pool_size=160G

innodb_buffer_pool_instances= 16innodb_buffer_pool_load_at_startup= 1innodb_buffer_pool_dump_at_shutdown= 1innodb_lru_scan_depth= 4096innodb_lock_wait_timeout= 5innodb_io_capacity= 10000innodb_io_capacity_max= 20000innodb_flush_method=O_DIRECT

innodb_undo_logs= 128innodb_undo_tablespaces= 3innodb_flush_neighbors= 0innodb_log_file_size=16G

innodb_log_files_in_group= 2innodb_log_buffer_size=64M

innodb_purge_threads= 4innodb_large_prefix= 1innodb_thread_concurrency= 64innodb_print_all_deadlocks= 1innodb_strict_mode= 1innodb_sort_buffer_size=128M

innodb_write_io_threads= 16innodb_read_io_threads= 16innodb_file_per_table= 1innodb_stats_persistent_sample_pages= 64innodb_autoinc_lock_mode= 2innodb_online_alter_log_max_size=1G

innodb_open_files=4096# replication settings #

master_info_repository=TABLE

relay_log_info_repository=TABLE

sync_binlog= 1gtid_mode=on

enforce_gtid_consistency= 1log_slave_updates

binlog_format=ROW

binlog_rows_query_log_events= 1relay_log=relay.log

relay_log_recovery= 1slave_skip_errors=ddl_exist_errors

slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'# semisyncreplication settings #

plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so"loose_rpl_semi_sync_master_enabled= 1loose_rpl_semi_sync_master_timeout= 3000loose_rpl_semi_sync_slave_enabled= 1# password plugin #

validate_password_policy=STRONG

validate-password =FORCE_PLUS_PERMANENT

# perforamnce_schema settings

performance-schema-instrument='memory/%=COUNTED'performance_schema_digests_size= 40000performance_schema_max_table_instances= 40000performance_schema_max_sql_text_length= 4096performance_schema_max_digest_length= 4096[mysqld-5.6]

# metalock performance settings

metadata_locks_hash_instances= 64[mysqld-5.7]

# new innodb settings #

loose_innodb_numa_interleave= 1innodb_buffer_pool_dump_pct= 40innodb_page_cleaners= 16innodb_undo_log_truncate= 1innodb_max_undo_log_size=2G

innodb_purge_rseg_truncate_frequency= 128# new replication settings #

slave-parallel-type =LOGICAL_CLOCK

slave-parallel-workers = 16slave_preserve_commit_order= 1slave_transaction_retries= 128# other change settings #

binlog_gtid_simple_recovery= 1log_timestamps=system

show_compatibility_56=on

# group replication settings

plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so"transaction-write-set-extraction =XXHASH64

# report_host= 127.0.0.1 # optional forgroup replication

# binlog_checksum= NONE # only forgroup replication

loose_group_replication=FORCE_PLUS_PERMANENT

loose_group_replication_group_name= "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose_group_replication_compression_threshold= 100loose_group_replication_flow_control_mode= 0loose_group_replication_single_primary_mode= 0loose_group_replication_enforce_update_everywhere_checks= 1loose_group_replication_transaction_size_limit= 10485760loose_group_replication_unreachable_majority_timeout= 120loose_group_replication_start_on_boot= 0