You need to enable JavaScript to run this app.
导航

可修改参数列表(MySQL 5.7)

最近更新时间2023.10.16 18:02:07

首次发布时间2023.03.15 17:15:36

本文介绍云数据库 MySQL 版为数据库版本 MySQL 5.7 提供的可修改参数,以及参数的默认值、修改后是否需要重启和取值范围。

序号参数名参数默认值是否需要重启取值范围
1auto_increment_increment1[1-65535]
2auto_increment_offset1[1-65535]
3automatic_sp_privilegesON[ON|OFF]
4avoid_temporal_upgradeOFF[ON|OFF]
5back_log3000[1-65535]
6binlog_order_commitsON[ON|OFF]
7binlog_rows_query_log_eventsOFF[ON|OFF]
8bulk_insert_buffer_size4194304[0-4294967295]
9character_set_filesystembinary[utf8|latin1|gbk|binary]
10character_set_serverutf8[utf8|latin1|gbk|gb18030|utf8mb4]
11collation_serverutf8_general_ci[utf8_general_ci|utf8_bin|utf8_unicode_ci|utf8_icelandic_ci|utf8_latvian_ci|utf8_romanian_ci|utf8_slovenian_ci|utf8_polish_ci|utf8_estonian_ci|utf8_spanish_ci|utf8_swedish_ci|utf8_turkish_ci|utf8_czech_ci|utf8_danish_ci|utf8_lithuanian_ci|utf8_slovak_ci|utf8_spanish2_ci|utf8_roman_ci|utf8_persian_ci|utf8_esperanto_ci|utf8_hungarian_ci|utf8_sinhala_ci|utf8_german2_ci|utf8_croatian_ci|utf8_unicode_520_ci|utf8_vietnamese_ci|utf8_general_mysql500_ci|latin1_bin|latin1_danish_ci|latin1_general_ci|latin1_general_cs|latin1_german1_ci|latin1_german2_ci|latin1_spanish_ci|latin1_swedish_ci|gbk_bin|gbk_chinese_ci|gb18030_bin|gb18030_chinese_ci|gb18030_unicode_520_ci|utf8mb4_general_ci|utf8mb4_bin|utf8mb4_unicode_ci|utf8mb4_icelandic_ci|utf8mb4_latvian_ci|utf8mb4_romanian_ci|utf8mb4_slovenian_ci|utf8mb4_polish_ci|utf8mb4_estonian_ci|utf8mb4_spanish_ci|utf8mb4_swedish_ci|utf8mb4_turkish_ci|utf8mb4_czech_ci|utf8mb4_danish_ci|utf8mb4_lithuanian_ci|utf8mb4_slovak_ci|utf8mb4_spanish2_ci|utf8mb4_roman_ci|utf8mb4_persian_ci|utf8mb4_esperanto_ci|utf8mb4_hungarian_ci|utf8mb4_sinhala_ci|utf8mb4_german2_ci|utf8mb4_croatian_ci|utf8mb4_unicode_520_ci|utf8mb4_vietnamese_ci]
12concurrent_insertAUTO[NEVER|AUTO|ALWAYS]
13connect_timeout10[2-3600]
14default_storage_engineInnoDB[InnoDB|innodb]
15default_time_zone+8:00[SYSTEM|-12:00|-11:00|-10:00|-9:00|-8:00|-7:00|-6:00|-5:00|-4:00|-3:00|-2:00|-1:00|+0:00|+1:00|+2:00|+3:00|+4:00|+5:00|+5:30|+6:00|+6:30|+7:00|+8:00|+9:00|+10:00|+11:00|+12:00|+13:00]
16default_week_format0[0-7]
17delay_key_writeON[ON|OFF|ALL]
18delayed_insert_timeout300[1-3600]
19disconnect_on_expired_passwordON[ON|OFF]
20div_precision_increment4[0-30]
21end_markers_in_jsonOFF[ON|OFF]
22eq_range_index_dive_limit100[0-4294967295]
23event_schedulerOFF[ON|OFF]
24explicit_defaults_for_timestampOFF[ON|OFF]
25ft_max_word_len84[10-84]
26ft_min_word_len4[1-84]
27ft_query_expansion_limit20[0-1000]
28general_logOFF[OFF]
29group_concat_max_len1024[4-1844674407370954752]
30innodb_adaptive_hash_indexOFF[ON|OFF]
31innodb_adaptive_max_sleep_delay150000[1-1000000]
32innodb_autoextend_increment64[1-1000]
33innodb_autoinc_lock_mode2[0|1|2]
34innodb_buffer_pool_dump_at_shutdownON[ON|OFF]
35innodb_buffer_pool_dump_pct25[1-100]
36innodb_buffer_pool_instances{LEAST(DBInstanceMemory/1073741824, 8)}[1-64]
37innodb_buffer_pool_load_at_startupON[ON|OFF]
38innodb_buffer_pool_size{DBInstanceMemory*1/2}[134217728-1099511627776]
39innodb_change_buffer_max_size25[0-50]
40innodb_change_bufferingall[none|inserts|deletes|changes|purges|all]
41innodb_cmp_per_index_enabledOFF[ON|OFF]
42innodb_compression_level6[0-9]
43innodb_compression_pad_pct_max50[0-70]
44innodb_concurrency_tickets5000[1-4294967295]
45innodb_deadlock_detectON[ON|OFF]

46

innodb_flush_log_at_trx_commit

1

[0|1|2]

注意

该参数值从 1 修改为 2 时,日志文件每秒都会写入到磁盘,如果写入前系统崩溃,会导致最后 1 秒的日志丢失;如果修改为 0,会有数据丢失的风险,请谨慎操作。

47innodb_flush_methodO_DIRECT[fsync|O_DSYNC|O_DIRECT|O_DIRECT_NO_FSYNC]
48innodb_flush_neighbors0[0|1|2]
49innodb_ft_enable_diag_printOFF[ON|OFF]
50innodb_ft_enable_stopwordON[ON|OFF]
51innodb_ft_max_token_size84[10-84]
52innodb_ft_min_token_size3[0-16]
53innodb_ft_num_word_optimize2000[0-10000]
54innodb_ft_result_cache_limit2000000000[1000000-4294967295]
55innodb_ft_sort_pll_degree2[1-16]
56innodb_io_capacity5000[100-4294967295]
57innodb_io_capacity_max6000[100-4294967295]
58innodb_large_prefixON[ON|OFF]
59innodb_lock_wait_timeout50[1-1073741824]
60innodb_log_checksumsON[ON|OFF]
61innodb_log_compressed_pagesOFF[ON|OFF]
62innodb_lru_scan_depth1024[100-4294967295]
63innodb_max_dirty_pages_pct75[0-99]
64innodb_max_dirty_pages_pct_lwm0[0-99]
65innodb_max_purge_lag0[0-4294967295]
66innodb_max_purge_lag_delay0[0-10000000]
67innodb_old_blocks_pct37[5-95]
68innodb_page_cleaners{LEAST(DBInstanceMemory/1073741824, 8)}[1-64]
69innodb_print_all_deadlocksON[OFF|ON]
70innodb_purge_batch_size300[1-5000]
71innodb_purge_threads1[1-32]
72innodb_random_read_aheadOFF[ON|OFF]
73innodb_read_ahead_threshold56[0-1024]
74innodb_read_io_threads4[1-64]
75innodb_rollback_on_timeoutOFF[OFF|ON]
76innodb_spin_wait_delay6[0-4294967295]
77innodb_stats_auto_recalcON[ON|OFF]
78innodb_stats_methodnulls_equal[nulls_equal|nulls_unequal|nulls_ignored]
79innodb_stats_on_metadataOFF[ON|OFF]
80innodb_stats_persistentON[ON|OFF]
81innodb_stats_persistent_sample_pages20[0-4294967295]
82innodb_stats_sample_pages8[1-4294967296]
83innodb_stats_transient_sample_pages8[1-4294967295]
84innodb_status_output_locksOFF[ON|OFF]
85innodb_strict_modeOFF[ON|OFF]
86innodb_sync_array_size1024[1-1024]
87innodb_sync_spin_loops30[0-4294967295]
88innodb_table_locksON[ON|OFF]
89innodb_thread_concurrency0[0-1000]
90innodb_thread_sleep_delay10000[0-1000000]
91innodb_write_io_threads4[1-64]
92interactive_timeout7200[10-86400]
93key_cache_age_threshold300[100-4294967295]
94key_cache_block_size1024[512-16384]
95key_cache_division_limit100[1-100]
96lc_time_namesen_US[ja_JP|pt_BR|en_US]
97local_infileON[ON|OFF]
98lock_wait_timeout31536000[1-31536000]
99log_error_verbosity2[1-3]
100log_queries_not_using_indexesOFF[ON|OFF]
101log_slow_admin_statementsOFF[ON|OFF]
102log_throttle_queries_not_using_indexes0[0-4294967295]
103long_query_time1[0.1-31536000]
104loose_rds_create_no_pk_tableOFF[ON|OFF]
105loose_thread_pool_enabledOFF[ON|OFF]
106loose_thread_pool_oversubscribe16[2-128]
107loose_thread_pool_size{MAX(DBInstanceCpu/2, 2)}[1-64]
108lower_case_table_names1[0|1]
109max_allowed_packet1073741824[16384-1073741824]
110max_connect_errors100[0-4294967295]
111max_error_count64[0-65535]
112max_execution_time0[0-4294967295]
113max_heap_table_size67108864[16384-1844674407370954752]
114max_join_size18446744073709551615[1-18446744073709551615]
115max_length_for_sort_data1024[0-838860]
116max_points_in_geometry65536[3-1048576]
117max_prepared_stmt_count16382[0-1048576]
118max_sort_length1024[4-8388608]
119max_sp_recursion_depth0[0-255]
120max_write_lock_count102400[1-102400]
121metadata_locks_cache_size1024[1-1048576]
122min_examined_row_limit0[0-4294967295]
123myisam_sort_buffer_size262144[262144-16777216]
124mysql_native_password_proxy_usersOFF[ON|OFF]
125net_read_timeout30[1-31536000]
126net_retry_count10[1-4294967295]
127net_write_timeout60[1-31536000]
128ngram_token_size2[1-10]
129open_files_limit1048576[4000-1073741824]
130optimizer_prune_level1[0|1]
131optimizer_search_depth62[0-62]
132optimizer_trace_limit1[0-4294967295]
133optimizer_trace_max_mem_size16384[0-4294967295]
134optimizer_trace_offset-1[-1-4294967295]
135performance_schemaOFF[ON|OFF]
136preload_buffer_size32768[1024-1073741824]
137query_alloc_block_size8192[1024-16384]
138query_cache_limit1048576[1-1048576]
139query_cache_min_res_unit1024[512-18446744073709551608]
140query_cache_size0[0-10485760000]
141query_cache_typeOFF[OFF|ON|DEMAND]
142query_cache_wlock_invalidateOFF[ON|OFF]
143query_prealloc_size8192[8192-1048576]
144range_alloc_block_size4096[4096-18446744073709551615]
145range_optimizer_max_mem_size8388608[0-18446744073709551615]
146read_buffer_size{LEAST(DBInstanceMemory/1048576*128, 262144)}[8200-2147479552]
147relay_log_recoveryON[ON|OFF]
148session_track_gtidsOFF[OFF|OWN_GTID|ALL_GTIDS]
149sha256_password_proxy_usersOFF[ON|OFF]
150show_old_temporalsOFF[ON|OFF]
151slave_parallel_typeLOGICAL_CLOCK[DATABASE|LOGICAL_CLOCK]
152slave_type_conversions[空|ALL_LOSSY|ALL_NON_LOSSY|ALL_LOSSY,ALL_NON_LOSSY|ALL_SIGNED|ALL_UNSIGNED|ALL_SIGNED,ALL_UNSIGNED]
153slow_launch_time2[1-1024]
154slow_query_logON[ON|OFF]
155sort_buffer_size262144[32768-4294967295]
156sql_modeONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[*ALLOW_INVALID_DATES|ANSI_QUOTES|ERROR_FOR_DIVISION_BY_ZERO|HIGH_NOT_PRECEDENCE|IGNORE_SPACE|NO_AUTO_CREATE_USER|NO_AUTO_VALUE_ON_ZERO|NO_BACKSLASH_ESCAPES|NO_DIR_IN_CREATE|NO_ENGINE_SUBSTITUTION|NO_FIELD_OPTIONS|NO_KEY_OPTIONS|NO_TABLE_OPTIONS|NO_UNSIGNED_SUBTRACTION|NO_ZERO_DATE|NO_ZERO_IN_DATE|ONLY_FULL_GROUP_BY|PAD_CHAR_TO_FULL_LENGTH|PIPES_AS_CONCAT|REAL_AS_FLOAT|STRICT_ALL_TABLES|STRICT_TRANS_TABLES]
157sql_safe_updatesOFF[ON|OFF]
158stored_program_cache256[16-524288]

159

sync_binlog

1

[0-4294967295]

注意

该参数值从 1 修改为其他值时,会有数据丢失的风险,请谨慎操作。

160sync_master_info10000[0-4294967295]
161sync_relay_log_info10000[0-4294967295]
162table_definition_cache{LEAST(DBInstanceMemory/1073741824*512, 2048)}[400-80480]
163thread_cache_size{LEAST(DBInstanceMemory/1073741824*512, 2048)}[0-16384]
164transaction_alloc_block_size8192[1024-131072]
165transaction_isolationREAD-COMMITTED[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE]
166transaction_prealloc_size4096[1024-131072]
167wait_timeout86400[1-31536000]
168max_connections不同规格默认值不同。[100-100000]
169max_user_connections0[0|100-100000]

常见问题

如何查询云数据库 MySQL 版不支持修改的参数的设定值?

可登录数据库工作台,使用 SQL 语句 SHOW variables like '参数名'查看。