5.6+ 取消預設配置,改為極簡默認配置,需自行按需調優。
文中逐行註釋:back_log、max_connections、table_open_cache、binlog_cache_size、max_heap_table_size、read_buffer_size、read_rnd_buffer_size、sort_buffer_size、join_buffer_size、thread_cache_size、thread_concurrency、query_cache_size/limit、ft_min_word_len、default-storage-engine、transaction_isolation、tmp_table_size、log-bin/binlog_format、slow_query_log/long_query_time、server-id、key_buffer_size、bulk_insert_buffer_size、myisam_sort_buffer_size、innodb_buffer_pool_size、innodb_data_file_path、innodb_write_io_threads、innodb_thread_concurrency、innodb_flush_log_at_trx_commit、innodb_log_buffer_size、innodb_log_file_size、innodb_log_files_in_group、innodb_max_dirty_pages_pct、innodb_lock_wait_timeout 等。
[mysqld] back_log = 512 max_connections = 3000 table_open_cache = 4096 max_allowed_packet = 32M max_heap_table_size = 128M read_buffer_size = 8M read_rnd_buffer_size = 64M sort_buffer_size = 16M join_buffer_size = 16M thread_cache_size = 16 thread_concurrency = 8 query_cache_size = 128M query_cache_limit = 4M ft_min_word_len = 8 default-storage-engine = INNODB transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_cache_size = 4M binlog_format=mixed log_warnings slow_query_log long_query_time = 10 server-id = 1 key_buffer_size = 128M bulk_insert_buffer_size = 256M myisam_sort_buffer_size = 256M innodb_additional_mem_pool_size = 64M innodb_buffer_pool_size = 6G innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 open_files_limit = 10240
配置優化必須基於線上實際負載,不可盲目套用;以 SHOW STATUS、SHOW VARIABLES、SHOW ENGINE INNODB STATUS 持續觀測調整。