MySQL optimal configuration template (5.6&5.7-turn)

[Client]user = Rootpassword = 1111aaa_[mysql]prompt = [\\[email protected]\\p][\\d]>\\_no-auto-rehash[mysqld _safe]malloc-lib=tcmalloc[mysqldump]single-transaction[mysqld]# Basic Settings #user = Mysqlsql_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=utf8mb4datadir=/mysql_data/datatransaction_isolation = Read-committedexplicit_defaults_for_timestamp = 1max_allowed_packet = 64mevent_scheduler = # connection #interactive_ Timeout = 1800wait_timeout = 1800lock_wait_timeout = 1800skip_name_resolve = 1max_connections = 1024max_user_connections = 256max_connect_errors = 1000000# Table Cache Performance Settingstable_open_cache = 4096table_definition_cache = 4096ta Ble_open_cache_instances = 64# session memory Settings #read_buffer_size = 16mread_rnd_buffer_size = 32msort_buffer_size = 32mtmp_table_size = 64mjoin_buffer_size = 128mthread_cache_size = 64# LoG settings #log_error = Error.loglog_bin = binloglog_error_verbosity = 2general_log_file = General.logslow_query_log = 1SL Ow_query_log_file = slow.loglog_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 = # InnoDB Settings #innodb_page_size = 16384innodb_buffer_ Pool_size = 160ginnodb_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_directinnodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 0innodb_ log_file_size = 16ginnodb_log_files_in_group = 2innodb_log_buffer_size = 64minnodb_purge_threads = 4innodb_large_ prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1inNodb_strict_mode = 1innodb_sort_buffer_size = 128minnodb_write_io_threads = 16innodb_read_io_threads = Innodb_file_ per_table = 1innodb_stats_persistent_sample_pages = 64innodb_autoinc_lock_mode = 2innodb_online_alter_log_max_size= 1ginnodb_open_files=4096# replication Settings #master_info_repository = Tablerelay_log_info_repository = tablesync_ Binlog = 1gtid_mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = Rowbinlog_rows_query_log_events = 1relay_log = Relay.logrelay_log_recovery = 1slave_skip_errors = Ddl_exist_errorsslave-rows-search-algorithms = ' INDEX_ Scan,hash_scan ' # Semi sync replication 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 = password Plugin #validate_password_policy = Strongvalidate-password = Force_plus_ permanent# Perforamnce_schema settingsperformance-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 Settingsmetadata_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 = 2Ginnodb_ purge_rseg_truncate_frequency = 128# new replication settings #slave-parallel-type = Logical_ Clockslave-parallel-workers = 16slave_preserve_commit_order = 1slave_transaction_retries = 128# Other Change settings # Binlog_gtid_simple_recovery = 1log_timestamps = systemshow_compatibility_56 = on# Group Replication settingsplugin-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 for group replication# binlog_checksum = N One # only For group replicationloose_group_replication = Force_plus_permanentloose_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

MySQL optimal configuration template (5.6&5.7-turn)