MySQL最优配置模板（ 5.6&5.7转）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166

[client] user = root  --用户 password = 1111aaA_  -- 密码   [mysql] prompt = [\\u@\\p][\\d]>\\_  -- cmd控制台前显示： node1 [localhost] {sa} (mysql)  而不是就是 mysql> no-auto-rehash  --开启命令自动补全功能   [mysqld_safe] malloc-lib=tcmalloc  --合适的内存分配算法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 = 1  --自动提交事务 server-id = 8888   --服务器ID character_set_server=utf8mb4  --服务器数据库的字符集合 datadir=/mysql_data/data   --数据目录 transaction_isolation = READ-COMMITTED  --数据库隔离级别 ，已提交读 explicit_defaults_for_timestamp = 1  -- max_allowed_packet = 64M  --最大控制其通信缓冲区的server接受的数据包大小（lob字段） event_scheduler = 1  --开启事件调度   # connection # interactive_timeout = 1800 wait_timeout = 1800 lock_wait_timeout = 1800 skip_name_resolve = 1 max_connections = 1024 max_user_connections = 256 max_connect_errors = 1000000   # table cache performance settings table_open_cache = 4096 table_definition_cache = 4096 table_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 = 2 general_log_file = general.log slow_query_log = 1  --开启慢sql日志 slow_query_log_file = slow.log --慢日志文件名 log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_throttle_queries_not_using_indexes = 10 expire_logs_days = 90  --日志过期天数 long_query_time = 2   --超过2秒的慢sql记录 min_examined_row_limit = 100 log-bin-trust-function-creators = 1 log-slave-updates = 1  --从库产生binlog日志，从库就可以做主库   # innodb settings # innodb_page_size = 16384 innodb_buffer_pool_size = 160G innodb_buffer_pool_instances = 16 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 4096 innodb_lock_wait_timeout = 5 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_flush_method = O_DIRECT innodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_flush_neighbors = 0 innodb_log_file_size = 16G innodb_log_files_in_group = 2 innodb_log_buffer_size = 64M innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 128M innodb_write_io_threads = 16 innodb_read_io_threads = 16 innodb_file_per_table = 1 innodb_stats_persistent_sample_pages = 64 innodb_autoinc_lock_mode = 2 innodb_online_alter_log_max_size=1G innodb_open_files=4096   # replication settings # master_info_repository = TABLE  --master_info存放到数据库表里 relay_log_info_repository = TABLE  --relay_log_info存放到数据库表里 sync_binlog = 1  --异步日志提交 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = ROW  --binlog使用row模式 binlog_rows_query_log_events = 1 relay_log = relay.log relay_log_recovery = 1  --relay_log出错或者中断，重新从master服务器抽取 slave_skip_errors = ddl_exist_errors slave-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 = 1 loose_rpl_semi_sync_master_timeout = 3000 loose_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 = 40000 performance_schema_max_table_instances = 40000 performance_schema_max_sql_text_length = 4096 performance_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 = 1 innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 16 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 2G innodb_purge_rseg_truncate_frequency = 128   # new replication settings # slave-parallel-type = LOGICAL_CLOCK   --开启组提交 slave-parallel-workers = 16   --组提交线程数16个 slave_preserve_commit_order = 1 slave_transaction_retries = 128 # other change settings # binlog_gtid_simple_recovery = 1 log_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 for group replication # binlog_checksum = NONE # only for group replication loose_group_replication = FORCE_PLUS_PERMANENT loose_group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose_group_replication_compression_threshold = 100 loose_group_replication_flow_control_mode = 0 loose_group_replication_single_primary_mode = 0 loose_group_replication_enforce_update_everywhere_checks = 1 loose_group_replication_transaction_size_limit = 10485760 loose_group_replication_unreachable_majority_timeout = 120 loose_group_replication_start_on_boot = 0