Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.1Mb
PDF (RPM) - 34.1Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 200.2Kb
Man Pages (Zip) - 305.4Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Server System Variables

6.1.4 Server System Variables

The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions.

There are several ways to see the names and values of system variables:

  • To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:

    mysqld --verbose --help
    
  • To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:

    mysqld --no-defaults --verbose --help
    
  • To see the current values used by a running server, use the SHOW VARIABLES statement.

This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.7 releases.

The following table lists all available system variables.

Table 6.2 System Variable Summary

NameCmd-LineOption FileSystem VarVar ScopeDynamic
audit_log_buffer_sizeYesYesYesGlobalNo
audit_log_connection_policyYesYesYesGlobalYes
audit_log_current_session  YesBothNo
audit_log_exclude_accountsYesYesYesGlobalYes
audit_log_fileYesYesYesGlobalNo
audit_log_filter_id  YesBothNo
audit_log_flush  YesGlobalYes
audit_log_formatYesYesYesGlobalNo
audit_log_include_accountsYesYesYesGlobalYes
audit_log_policyYesYesYesGlobalNo
audit_log_rotate_on_sizeYesYesYesGlobalYes
audit_log_statement_policyYesYesYesGlobalYes
audit_log_strategyYesYesYesGlobalNo
auto_generate_certsYesYesYesGlobalNo
auto_increment_increment  YesBothYes
auto_increment_offset  YesBothYes
autocommitYesYesYesBothYes
automatic_sp_privileges  YesGlobalYes
avoid_temporal_upgradeYesYesYesGlobalYes
back_log  YesGlobalNo
basedirYesYesYesGlobalNo
big-tablesYesYes  Yes
- Variable: big_tables  YesBothYes
bind-addressYesYes  No
- Variable: bind_address  YesGlobalNo
binlog_cache_sizeYesYesYesGlobalYes
binlog_checksum  YesGlobalYes
binlog_direct_non_transactional_updatesYesYesYesBothYes
binlog_error_actionYesYesYesBothYes
binlog-formatYesYes  Yes
- Variable: binlog_format  YesBothYes
binlog_group_commit_sync_delayYesYesYesGlobalYes
binlog_group_commit_sync_no_delay_countYesYesYesGlobalYes
binlog_gtid_simple_recoveryYesYesYesGlobalNo
binlog_max_flush_queue_time  YesGlobalYes
binlog_order_commits  YesGlobalYes
binlog_row_imageYesYesYesBothYes
binlog_rows_query_log_events  YesBothYes
binlog_stmt_cache_sizeYesYesYesGlobalYes
binlogging_impossible_modeYesYesYesBothYes
block_encryption_modeYesYesYesBothYes
bulk_insert_buffer_sizeYesYesYesBothYes
character_set_client  YesBothYes
character_set_connection  YesBothYes
character_set_database[a]  YesBothYes
character-set-filesystemYesYes  Yes
- Variable: character_set_filesystem  YesBothYes
character_set_results  YesBothYes
character-set-serverYesYes  Yes
- Variable: character_set_server  YesBothYes
character_set_system  YesGlobalNo
character-sets-dirYesYes  No
- Variable: character_sets_dir  YesGlobalNo
check_proxy_usersYesYesYesGlobalYes
collation_connection  YesBothYes
collation_database[b]  YesBothYes
collation-serverYesYes  Yes
- Variable: collation_server  YesBothYes
completion_typeYesYesYesBothYes
concurrent_insertYesYesYesGlobalYes
connect_timeoutYesYesYesGlobalYes
core_file  YesGlobalNo
daemon_memcached_enable_binlogYesYesYesGlobalNo
daemon_memcached_engine_lib_nameYesYesYesGlobalNo
daemon_memcached_engine_lib_pathYesYesYesGlobalNo
daemon_memcached_optionYesYesYesGlobalNo
daemon_memcached_r_batch_sizeYesYesYesGlobalNo
daemon_memcached_w_batch_sizeYesYesYesGlobalNo
datadirYesYesYesGlobalNo
date_format  YesGlobalNo
datetime_format  YesGlobalNo
debugYesYesYesBothYes
debug_sync  YesSessionYes
default_authentication_pluginYesYesYesGlobalNo
default_password_lifetimeYesYesYesGlobalYes
default-storage-engineYesYes  Yes
- Variable: default_storage_engine  YesBothYes
default_tmp_storage_engineYesYesYesBothYes
default_week_formatYesYesYesBothYes
delay-key-writeYesYes  Yes
- Variable: delay_key_write  YesGlobalYes
delayed_insert_limitYesYesYesGlobalYes
delayed_insert_timeoutYesYesYesGlobalYes
delayed_queue_sizeYesYesYesGlobalYes
disabled_storage_enginesYesYesYesGlobalNo
disconnect_on_expired_passwordYesYesYesSessionNo
div_precision_incrementYesYesYesBothYes
end_markers_in_json  YesBothYes
enforce-gtid-consistencyYesYesYesGlobalVaries
enforce_gtid_consistencyYesYesYesGlobalVaries
eq_range_index_dive_limit  YesBothYes
error_count  YesSessionNo
event-schedulerYesYes  Yes
- Variable: event_scheduler  YesGlobalYes
executed_gtids_compression_period  YesGlobalYes
expire_logs_daysYesYesYesGlobalYes
explicit_defaults_for_timestampYesYesYesBothNo
external_user  YesSessionNo
flushYesYesYesGlobalYes
flush_timeYesYesYesGlobalYes
foreign_key_checks  YesBothYes
ft_boolean_syntaxYesYesYesGlobalYes
ft_max_word_lenYesYesYesGlobalNo
ft_min_word_lenYesYesYesGlobalNo
ft_query_expansion_limitYesYesYesGlobalNo
ft_stopword_fileYesYesYesGlobalNo
general-logYesYes  Yes
- Variable: general_log  YesGlobalYes
general_log_fileYesYesYesGlobalYes
group_concat_max_lenYesYesYesBothYes
gtid_executed  YesVariesNo
gtid_executed_compression_period  YesGlobalYes
gtid-modeYesYes  Varies
- Variable: gtid_mode  YesGlobalVaries
gtid_mode  YesGlobalVaries
gtid_next  YesSessionYes
gtid_owned  YesBothNo
gtid_purged  YesGlobalYes
have_compress  YesGlobalNo
have_crypt  YesGlobalNo
have_dynamic_loading  YesGlobalNo
have_geometry  YesGlobalNo
have_openssl  YesGlobalNo
have_profiling  YesGlobalNo
have_query_cache  YesGlobalNo
have_rtree_keys  YesGlobalNo
have_ssl  YesGlobalNo
have_statement_timeout  YesGlobalNo
have_symlink  YesGlobalNo
host_cache_size  YesGlobalYes
hostname  YesGlobalNo
identity  YesSessionYes
ignore-builtin-innodbYesYes  No
- Variable: ignore_builtin_innodb  YesGlobalNo
ignore_db_dirs  YesGlobalNo
init_connectYesYesYesGlobalYes
init-fileYesYes  No
- Variable: init_file  YesGlobalNo
init_slaveYesYesYesGlobalYes
innodb_adaptive_flushingYesYesYesGlobalYes
innodb_adaptive_flushing_lwmYesYesYesGlobalYes
innodb_adaptive_hash_indexYesYesYesGlobalYes
innodb_adaptive_hash_index_partsYesYesYesGlobalNo
innodb_adaptive_max_sleep_delayYesYesYesGlobalYes
innodb_additional_mem_pool_sizeYesYesYesGlobalNo
innodb_api_bk_commit_intervalYesYesYesGlobalYes
innodb_api_disable_rowlockYesYesYesGlobalNo
innodb_api_enable_binlogYesYesYesGlobalNo
innodb_api_enable_mdlYesYesYesGlobalNo
innodb_api_trx_levelYesYesYesGlobalYes
innodb_autoextend_incrementYesYesYesGlobalYes
innodb_autoinc_lock_modeYesYesYesGlobalNo
innodb_background_drop_list_emptyYesYesYesGlobalYes
innodb_buffer_pool_chunk_sizeYesYesYesGlobalNo
innodb_buffer_pool_dump_at_shutdownYesYesYesGlobalYes
innodb_buffer_pool_dump_nowYesYesYesGlobalYes
innodb_buffer_pool_dump_pctYesYesYesGlobalYes
innodb_buffer_pool_filenameYesYesYesGlobalYes
innodb_buffer_pool_instancesYesYesYesGlobalNo
innodb_buffer_pool_load_abortYesYesYesGlobalYes
innodb_buffer_pool_load_at_startupYesYesYesGlobalNo
innodb_buffer_pool_load_nowYesYesYesGlobalYes
innodb_buffer_pool_sizeYesYesYesGlobalVaries
innodb_change_buffer_max_sizeYesYesYesGlobalYes
innodb_change_bufferingYesYesYesGlobalYes
innodb_change_buffering_debugYesYesYesGlobalYes
innodb_checksum_algorithmYesYesYesGlobalYes
innodb_checksumsYesYesYesGlobalNo
innodb_cmp_per_index_enabledYesYesYesGlobalYes
innodb_commit_concurrencyYesYesYesGlobalYes
innodb_compress_debugYesYesYesGlobalYes
innodb_compression_failure_threshold_pctYesYesYesGlobalYes
innodb_compression_levelYesYesYesGlobalYes
innodb_compression_pad_pct_maxYesYesYesGlobalYes
innodb_concurrency_ticketsYesYesYesGlobalYes
innodb_create_intrinsicYesYesYesSessionYes
innodb_data_file_pathYesYesYesGlobalNo
innodb_data_home_dirYesYesYesGlobalNo
innodb_default_row_formatYesYesYesGlobalYes
innodb_disable_resize_buffer_pool_debugYesYesYesGlobalYes
innodb_disable_sort_file_cacheYesYesYesGlobalYes
innodb_doublewriteYesYesYesGlobalNo
innodb_fast_shutdownYesYesYesGlobalYes
innodb_fil_make_page_dirty_debugYesYesYesGlobalYes
innodb_file_formatYesYesYesGlobalYes
innodb_file_format_checkYesYesYesGlobalNo
innodb_file_format_maxYesYesYesGlobalYes
innodb_file_per_tableYesYesYesGlobalYes
innodb_fill_factorYesYesYesGlobalYes
innodb_flush_log_at_timeout  YesGlobalYes
innodb_flush_log_at_trx_commitYesYesYesGlobalYes
innodb_flush_methodYesYesYesGlobalNo
innodb_flush_neighborsYesYesYesGlobalYes
innodb_flush_syncYesYesYesGlobalYes
innodb_flushing_avg_loopsYesYesYesGlobalYes
innodb_force_load_corruptedYesYesYesGlobalNo
innodb_force_recoveryYesYesYesGlobalNo
innodb_ft_aux_tableYesYesYesGlobalYes
innodb_ft_cache_sizeYesYesYesGlobalNo
innodb_ft_enable_diag_printYesYesYesGlobalYes
innodb_ft_enable_stopwordYesYesYesGlobalYes
innodb_ft_max_token_sizeYesYesYesGlobalNo
innodb_ft_min_token_sizeYesYesYesGlobalNo
innodb_ft_num_word_optimizeYesYesYesGlobalYes
innodb_ft_result_cache_limitYesYesYesGlobalYes
innodb_ft_server_stopword_tableYesYesYesGlobalYes
innodb_ft_sort_pll_degreeYesYesYesGlobalNo
innodb_ft_total_cache_sizeYesYesYesGlobalNo
innodb_ft_user_stopword_tableYesYesYesBothYes
innodb_io_capacityYesYesYesGlobalYes
innodb_io_capacity_maxYesYesYesGlobalYes
innodb_large_prefixYesYesYesGlobalYes
innodb_limit_optimistic_insert_debugYesYesYesGlobalYes
innodb_lock_wait_timeoutYesYesYesBothYes
innodb_locks_unsafe_for_binlogYesYesYesGlobalNo
innodb_log_buffer_sizeYesYesYesGlobalNo
innodb_log_checksum_algorithmYesYesYesGlobalYes
innodb_log_checksumsYesYesYesGlobalYes
innodb_log_compressed_pagesYesYesYesGlobalYes
innodb_log_file_sizeYesYesYesGlobalNo
innodb_log_files_in_groupYesYesYesGlobalNo
innodb_log_group_home_dirYesYesYesGlobalNo
innodb_log_write_ahead_sizeYesYesYesGlobalYes
innodb_lru_scan_depthYesYesYesGlobalYes
innodb_max_dirty_pages_pctYesYesYesGlobalYes
innodb_max_dirty_pages_pct_lwmYesYesYesGlobalYes
innodb_max_purge_lagYesYesYesGlobalYes
innodb_max_purge_lag_delayYesYesYesGlobalYes
innodb_max_undo_log_sizeYesYesYesGlobalYes
innodb_merge_threshold_set_all_debugYesYesYesGlobalYes
innodb_monitor_disableYesYesYesGlobalYes
innodb_monitor_enableYesYesYesGlobalYes
innodb_monitor_resetYesYesYesGlobalYes
innodb_monitor_reset_allYesYesYesGlobalYes
innodb_numa_interleaveYesYesYesGlobalNo
innodb_old_blocks_pctYesYesYesGlobalYes
innodb_old_blocks_timeYesYesYesGlobalYes
innodb_online_alter_log_max_sizeYesYesYesGlobalYes
innodb_open_filesYesYesYesGlobalNo
innodb_optimize_fulltext_onlyYesYesYesGlobalYes
innodb_optimize_point_storageYesYesYesSessionYes
innodb_page_cleanersYesYesYesGlobalNo
innodb_page_sizeYesYesYesGlobalNo
innodb_print_all_deadlocksYesYesYesGlobalYes
innodb_purge_batch_sizeYesYesYesGlobalYes
innodb_purge_rseg_truncate_frequencyYesYesYesGlobalYes
innodb_purge_threadsYesYesYesGlobalNo
innodb_random_read_aheadYesYesYesGlobalYes
innodb_read_ahead_thresholdYesYesYesGlobalYes
innodb_read_io_threadsYesYesYesGlobalNo
innodb_read_onlyYesYesYesGlobalNo
innodb_replication_delayYesYesYesGlobalYes
innodb_rollback_on_timeoutYesYesYesGlobalNo
innodb_rollback_segmentsYesYesYesGlobalYes
innodb_saved_page_number_debugYesYesYesGlobalYes
innodb_sort_buffer_sizeYesYesYesGlobalNo
innodb_spin_wait_delayYesYesYesGlobalYes
innodb_stats_auto_recalcYesYesYesGlobalYes
innodb_stats_methodYesYesYesGlobalYes
innodb_stats_on_metadataYesYesYesGlobalYes
innodb_stats_persistentYesYesYesGlobalYes
innodb_stats_persistent_sample_pagesYesYesYesGlobalYes
innodb_stats_sample_pagesYesYesYesGlobalYes
innodb_stats_transient_sample_pagesYesYesYesGlobalYes
innodb_status_outputYesYesYesGlobalYes
innodb_status_output_locksYesYesYesGlobalYes
innodb_strict_modeYesYesYesBothYes
innodb_support_xaYesYesYesBothYes
innodb_sync_array_sizeYesYesYesGlobalNo
innodb_sync_debugYesYesYesGlobalNo
innodb_sync_spin_loopsYesYesYesGlobalYes
innodb_table_locksYesYesYesBothYes
innodb_temp_data_file_pathYesYesYesGlobalNo
innodb_thread_concurrencyYesYesYesGlobalYes
innodb_thread_sleep_delayYesYesYesGlobalYes
innodb_tmpdirYesYesYesBothYes
innodb_trx_purge_view_update_only_debugYesYesYesGlobalYes
innodb_trx_rseg_n_slots_debugYesYesYesGlobalYes
innodb_undo_directoryYesYesYesGlobalNo
innodb_undo_log_truncateYesYesYesGlobalYes
innodb_undo_logsYesYesYesGlobalYes
innodb_undo_tablespacesYesYesYesGlobalNo
innodb_use_native_aioYesYesYesGlobalNo
innodb_use_sys_mallocYesYesYesGlobalNo
innodb_version  YesGlobalNo
innodb_write_io_threadsYesYesYesGlobalNo
insert_id  YesSessionYes
interactive_timeoutYesYesYesBothYes
internal_tmp_disk_storage_engineYesYesYesGlobalYes
join_buffer_sizeYesYesYesBothYes
keep_files_on_createYesYesYesBothYes
key_buffer_sizeYesYesYesGlobalYes
key_cache_age_thresholdYesYesYesGlobalYes
key_cache_block_sizeYesYesYesGlobalYes
key_cache_division_limitYesYesYesGlobalYes
keyring_file_dataYesYesYesGlobalYes
keyring_okv_conf_dirYesYesYesGlobalYes
languageYesYesYesGlobalNo
large_files_support  YesGlobalNo
large_page_size  YesGlobalNo
large-pagesYesYes  No
- Variable: large_pages  YesGlobalNo
last_insert_id  YesSessionYes
lc-messagesYesYes  Yes
- Variable: lc_messages  YesBothYes
lc-messages-dirYesYes  No
- Variable: lc_messages_dir  YesGlobalNo
lc_time_names  YesBothYes
license  YesGlobalNo
local_infile  YesGlobalYes
lock_wait_timeoutYesYesYesBothYes
locked_in_memory  YesGlobalNo
log_backward_compatible_user_definitionsYesYesYesGlobalYes
log-binYesYesYesGlobalNo
log_bin  YesGlobalNo
log_bin_basename  YesGlobalNo
log_bin_index  YesGlobalNo
log-bin-trust-function-creatorsYesYes  Yes
- Variable: log_bin_trust_function_creators  YesGlobalYes
log-bin-use-v1-row-eventsYesYes  No
- Variable: log_bin_use_v1_row_events  YesGlobalNo
log_bin_use_v1_row_eventsYesYesYesGlobalNo
log_builtin_as_identified_by_passwordYesYesYesGlobalYes
log-errorYesYes  No
- Variable: log_error  YesGlobalNo
log_error_verbosityYesYesYesGlobalYes
log-outputYesYes  Yes
- Variable: log_output  YesGlobalYes
log-queries-not-using-indexesYesYes  Yes
- Variable: log_queries_not_using_indexes  YesGlobalYes
log-slave-updatesYesYes  No
- Variable: log_slave_updates  YesGlobalNo
log_slave_updatesYesYesYesGlobalNo
log_slow_admin_statements  YesGlobalYes
log_slow_slave_statements  YesGlobalYes
log_statements_unsafe_for_binlog  YesGlobalYes
log_syslogYesYesYesGlobalYes
log_syslog_facilityYesYesYesGlobalYes
log_syslog_include_pidYesYesYesGlobalYes
log_syslog_tagYesYesYesGlobalYes
log_throttle_queries_not_using_indexes  YesGlobalYes
log_timestampsYesYesYesGlobalYes
log-warningsYesYes  Yes
- Variable: log_warnings  YesGlobalYes
long_query_timeYesYesYesBothYes
low-priority-updatesYesYes  Yes
- Variable: low_priority_updates  YesBothYes
lower_case_file_system  YesGlobalNo
lower_case_table_namesYesYesYesGlobalNo
master_info_repositoryYesYesYesGlobalYes
master_verify_checksum  YesGlobalYes
max_allowed_packetYesYesYesBothYes
max_binlog_cache_sizeYesYesYesGlobalYes
max_binlog_sizeYesYesYesGlobalYes
max_binlog_stmt_cache_sizeYesYesYesGlobalYes
max_connect_errorsYesYesYesGlobalYes
max_connectionsYesYesYesGlobalYes
max_delayed_threadsYesYesYesBothYes
max_digest_lengthYesYesYesGlobalNo
max_error_countYesYesYesBothYes
max_execution_timeYesYesYesBothYes
max_heap_table_sizeYesYesYesBothYes
max_insert_delayed_threads  YesBothYes
max_join_sizeYesYesYesBothYes
max_length_for_sort_dataYesYesYesBothYes
max_points_in_geometryYesYesYesGlobalYes
max_prepared_stmt_countYesYesYesGlobalYes
max_relay_log_sizeYesYesYesGlobalYes
max_seeks_for_keyYesYesYesBothYes
max_sort_lengthYesYesYesBothYes
max_sp_recursion_depthYesYesYesBothYes
max_statement_time  YesBothYes
max_tmp_tables  YesBothYes
max_user_connectionsYesYesYesBothYes
max_write_lock_countYesYesYesGlobalYes
mecab_rc_fileYesYesYesGlobalNo
metadata_locks_cache_size  YesGlobalNo
metadata_locks_hash_instances  YesGlobalNo
min-examined-row-limitYesYesYesBothYes
multi_range_countYesYesYesBothYes
myisam_data_pointer_sizeYesYesYesGlobalYes
myisam_max_sort_file_sizeYesYesYesGlobalYes
myisam_mmap_sizeYesYesYesGlobalNo
myisam_recover_options  YesGlobalNo
myisam_repair_threadsYesYesYesBothYes
myisam_sort_buffer_sizeYesYesYesBothYes
myisam_stats_methodYesYesYesBothYes
myisam_use_mmapYesYesYesGlobalYes
mysql_firewall_modeYesYesYesGlobalYes
mysql_firewall_traceYesYesYesGlobalYes
mysql_native_password_proxy_usersYesYesYesGlobalYes
mysqlx_connect_timeoutYesYesYesGlobalYes
mysqlx_max_connectionsYesYesYesGlobalYes
mysqlx_portYesYesYesGlobalYes
named_pipe  YesGlobalNo
ndb-allow-copying-alter-tableYesYesYesBothYes
ndb_autoincrement_prefetch_szYesYesYesBothYes
ndb-batch-sizeYesYesYesGlobalNo
ndb-blob-read-batch-bytesYesYesYesBothYes
ndb-blob-write-batch-bytesYesYesYesBothYes
ndb_cache_check_timeYesYesYesGlobalYes
ndb_clear_apply_statusYes YesGlobalYes
ndb-cluster-connection-poolYesYesYesGlobalNo
ndb-cluster-connection-pool-nodeidsYesYesYesGlobalNo
ndb_data_node_neighbourYesYesYesGlobalYes
ndb-deferred-constraintsYesYes  Yes
- Variable: ndb_deferred_constraints  YesBothYes
ndb_deferred_constraintsYesYesYesBothYes
ndb-distributionYesYes  Yes
- Variable: ndb_distribution  YesGlobalYes
ndb_distributionYesYesYesGlobalYes
ndb_eventbuffer_free_percentYesYesYesGlobalYes
ndb_eventbuffer_max_allocYesYesYesGlobalYes
ndb_extra_loggingYesYesYesGlobalYes
ndb_force_sendYesYesYesBothYes
ndb_fully_replicatedYesYesYesBothYes
ndb_index_stat_enableYesYesYesBothYes
ndb_index_stat_optionYesYesYesBothYes
ndb_join_pushdown  YesBothYes
ndb-log-apply-statusYesYes  No
- Variable: ndb_log_apply_status  YesGlobalNo
ndb_log_apply_statusYesYesYesGlobalNo
ndb_log_binYes YesBothYes
ndb_log_binlog_indexYes YesGlobalYes
ndb-log-empty-epochsYesYesYesGlobalYes
ndb_log_empty_epochsYesYesYesGlobalYes
ndb-log-exclusive-readsYesYes  Yes
- Variable: ndb_log_exclusive_reads  YesBothYes
ndb_log_exclusive_readsYesYesYesBothYes
ndb-log-origYesYes  No
- Variable: ndb_log_orig  YesGlobalNo
ndb_log_origYesYesYesGlobalNo
ndb-log-transaction-idYesYes  No
- Variable: ndb_log_transaction_id  YesGlobalNo
ndb_log_transaction_id  YesGlobalNo
ndb_log_updated_onlyYesYesYesGlobalYes
ndb_optimization_delay  YesGlobalYes
ndb_optimized_node_selectionYesYesYesGlobalNo
ndb_read_backupYesYesYesGlobalYes
ndb_recv_thread_cpu_mask  YesGlobalYes
ndb_report_thresh_binlog_epoch_slipYesYesYesGlobalYes
ndb_report_thresh_binlog_mem_usageYesYesYesGlobalYes
ndb_show_foreign_key_mock_tablesYesYesYesGlobalYes
ndb_slave_conflict_roleYesYesYesGlobalYes
Ndb_slave_max_replicated_epoch  YesGlobalNo
ndb_table_no_logging  YesSessionYes
ndb_table_temporary  YesSessionYes
ndb_use_copying_alter_table  YesBothNo
ndb_use_exact_count  YesBothYes
ndb_use_transactionsYesYesYesBothYes
ndb_version  YesGlobalNo
ndb_version_string  YesGlobalNo
ndb-wait-connectedYesYesYesGlobalNo
ndb-wait-setupYesYesYesGlobalNo
ndbinfo_database  YesGlobalNo
ndbinfo_max_bytesYes YesBothYes
ndbinfo_max_rowsYes YesBothYes
ndbinfo_offline  YesGlobalYes
ndbinfo_show_hiddenYes YesBothYes
ndbinfo_table_prefixYes YesBothYes
ndbinfo_version  YesGlobalNo
net_buffer_lengthYesYesYesBothYes
net_read_timeoutYesYesYesBothYes
net_retry_countYesYesYesBothYes
net_write_timeoutYesYesYesBothYes
newYesYesYesBothYes
ngram_token_sizeYesYesYesGlobalNo
offline_modeYesYesYesGlobalYes
oldYesYesYesGlobalNo
old-alter-tableYesYes  Yes
- Variable: old_alter_table  YesBothYes
old_passwords  YesBothYes
open-files-limitYesYes  No
- Variable: open_files_limit  YesGlobalNo
optimizer_prune_levelYesYesYesBothYes
optimizer_search_depthYesYesYesBothYes
optimizer_switchYesYesYesBothYes
optimizer_trace  YesBothYes
optimizer_trace_features  YesBothYes
optimizer_trace_limit  YesBothYes
optimizer_trace_max_mem_size  YesBothYes
optimizer_trace_offset  YesBothYes
parser_max_mem_sizeYesYesYesBothYes
performance_schemaYesYesYesGlobalNo
performance_schema_accounts_sizeYesYesYesGlobalNo
performance_schema_digests_sizeYesYesYesGlobalNo
performance_schema_events_stages_history_long_sizeYesYesYesGlobalNo
performance_schema_events_stages_history_sizeYesYesYesGlobalNo
performance_schema_events_statements_history_long_sizeYesYesYesGlobalNo
performance_schema_events_statements_history_sizeYesYesYesGlobalNo
performance_schema_events_transactions_history_long_sizeYesYesYesGlobalNo
performance_schema_events_transactions_history_sizeYesYesYesGlobalNo
performance_schema_events_waits_history_long_sizeYesYesYesGlobalNo
performance_schema_events_waits_history_sizeYesYesYesGlobalNo
performance_schema_hosts_sizeYesYesYesGlobalNo
performance_schema_max_cond_classesYesYesYesGlobalNo
performance_schema_max_cond_instancesYesYesYesGlobalNo
performance_schema_max_digest_lengthYesYesYesGlobalNo
performance_schema_max_file_classesYesYesYesGlobalNo
performance_schema_max_file_handlesYesYesYesGlobalNo
performance_schema_max_file_instancesYesYesYesGlobalNo
performance_schema_max_index_statYesYesYesGlobalNo
performance_schema_max_memory_classesYesYesYesGlobalNo
performance_schema_max_metadata_locksYesYesYesGlobalNo
performance_schema_max_mutex_classesYesYesYesGlobalNo
performance_schema_max_mutex_instancesYesYesYesGlobalNo
performance_schema_max_prepared_statements_instancesYesYesYesGlobalNo
performance_schema_max_program_instancesYesYesYesGlobalNo
performance_schema_max_rwlock_classesYesYesYesGlobalNo
performance_schema_max_rwlock_instancesYesYesYesGlobalNo
performance_schema_max_socket_classesYesYesYesGlobalNo
performance_schema_max_socket_instancesYesYesYesGlobalNo
performance_schema_max_sql_text_lengthYesYesYesGlobalNo
performance_schema_max_stage_classesYesYesYesGlobalNo
performance_schema_max_statement_classesYesYesYesGlobalNo
performance_schema_max_statement_stackYesYesYesGlobalNo
performance_schema_max_table_handlesYesYesYesGlobalNo
performance_schema_max_table_instancesYesYesYesGlobalNo
performance_schema_max_table_lock_statYesYesYesGlobalNo
performance_schema_max_thread_classesYesYesYesGlobalNo
performance_schema_max_thread_instancesYesYesYesGlobalNo
performance_schema_session_connect_attrs_sizeYesYesYesGlobalNo
performance_schema_setup_actors_sizeYesYesYesGlobalNo
performance_schema_setup_objects_sizeYesYesYesGlobalNo
performance_schema_users_sizeYesYesYesGlobalNo
pid-fileYesYes  No
- Variable: pid_file  YesGlobalNo
plugin_dirYesYesYesGlobalNo
portYesYesYesGlobalNo
preload_buffer_sizeYesYesYesBothYes
profiling  YesBothYes
profiling_history_sizeYesYesYesBothYes
protocol_version  YesGlobalNo
proxy_user  YesSessionNo
pseudo_slave_mode  YesSessionYes
pseudo_thread_id  YesSessionYes
query_alloc_block_sizeYesYesYesBothYes
query_cache_limitYesYesYesGlobalYes
query_cache_min_res_unitYesYesYesGlobalYes
query_cache_sizeYesYesYesGlobalYes
query_cache_typeYesYesYesBothYes
query_cache_wlock_invalidateYesYesYesBothYes
query_prealloc_sizeYesYesYesBothYes
rand_seed1  YesSessionYes
rand_seed2  YesSessionYes
range_alloc_block_sizeYesYesYesBothYes
range_optimizer_max_mem_sizeYesYesYesBothYes
rbr_exec_mode  YesSessionYes
read_buffer_sizeYesYesYesBothYes
read_onlyYesYesYesGlobalYes
read_rnd_buffer_sizeYesYesYesBothYes
relay-logYesYes  No
- Variable: relay_log  YesGlobalNo
relay_log_basename  YesGlobalNo
relay-log-indexYesYes  No
- Variable: relay_log_index  YesGlobalNo
relay_log_indexYesYesYesGlobalNo
relay_log_info_fileYesYesYesGlobalNo
relay_log_info_repository  YesGlobalYes
relay_log_purgeYesYesYesGlobalYes
relay_log_recoveryYesYesYesGlobalNo
relay_log_space_limitYesYesYesGlobalNo
report-hostYesYes  No
- Variable: report_host  YesGlobalNo
report-passwordYesYes  No
- Variable: report_password  YesGlobalNo
report-portYesYes  No
- Variable: report_port  YesGlobalNo
report-userYesYes  No
- Variable: report_user  YesGlobalNo
require_secure_transportYesYesYesGlobalYes
rewriter_enabled  YesGlobalYes
rewriter_verbose  YesGlobalYes
rpl_semi_sync_master_enabled  YesGlobalYes
rpl_semi_sync_master_timeout  YesGlobalYes
rpl_semi_sync_master_trace_level  YesGlobalYes
rpl_semi_sync_master_wait_for_slave_count  YesGlobalYes
rpl_semi_sync_master_wait_no_slave  YesGlobalYes
rpl_semi_sync_master_wait_point  YesGlobalYes
rpl_semi_sync_slave_enabled  YesGlobalYes
rpl_semi_sync_slave_trace_level  YesGlobalYes
rpl_stop_slave_timeoutYesYesYesGlobalYes
secure-authYesYes  Yes
- Variable: secure_auth  YesGlobalYes
secure-file-privYesYes  No
- Variable: secure_file_priv  YesGlobalNo
server-idYesYes  Yes
- Variable: server_id  YesGlobalYes
server-id-bitsYesYes  No
- Variable: server_id_bits  YesGlobalNo
server_id_bitsYesYesYesGlobalNo
server_uuid  YesGlobalNo
session_track_gtidsYesYesYesBothYes
session_track_schemaYesYesYesBothYes
session_track_state_changeYesYesYesBothYes
session_track_system_variablesYesYesYesBothYes
sha256_password_auto_generate_rsa_keysYesYesYesGlobalNo
sha256_password_private_key_path  YesGlobalNo
sha256_password_proxy_usersYesYesYesGlobalYes
sha256_password_public_key_path  YesGlobalNo
shared_memoryYesYesYesGlobalNo
shared_memory_base_nameYesYesYesGlobalNo
show_compatibility_56YesYesYesGlobalYes
show_old_temporalsYesYesYesBothYes
simplified_binlog_gtid_recoveryYesYesYesGlobalNo
skip_external_lockingYesYesYesGlobalNo
skip-name-resolveYesYes  No
- Variable: skip_name_resolve  YesGlobalNo
skip-networkingYesYes  No
- Variable: skip_networking  YesGlobalNo
skip-show-databaseYesYes  No
- Variable: skip_show_database  YesGlobalNo
slave_allow_batchingYesYesYesGlobalYes
slave_checkpoint_groupYesYesYesGlobalYes
slave_checkpoint_periodYesYesYesGlobalYes
slave_compressed_protocolYesYesYesGlobalYes
slave_exec_modeYesYesYesGlobalYes
slave-load-tmpdirYesYes  No
- Variable: slave_load_tmpdir  YesGlobalNo
slave_max_allowed_packet  YesGlobalYes
slave-net-timeoutYesYes  Yes
- Variable: slave_net_timeout  YesGlobalYes
slave_parallel_type  YesGlobalYes
slave_parallel_workersYes YesGlobalYes
slave_pending_jobs_size_max  YesGlobalYes
slave_preserve_commit_orderYes YesGlobalYes
slave_rows_search_algorithms  YesGlobalYes
slave-skip-errorsYesYes  No
- Variable: slave_skip_errors  YesGlobalNo
slave_sql_verify_checksum  YesGlobalYes
slave_transaction_retriesYesYesYesGlobalYes
slave_type_conversionsYesYesYesGlobalNo
slow_launch_timeYesYesYesGlobalYes
slow-query-logYesYes  Yes
- Variable: slow_query_log  YesGlobalYes
slow_query_log_fileYesYesYesGlobalYes
socketYesYesYesGlobalNo
sort_buffer_sizeYesYesYesBothYes
sql_auto_is_null  YesBothYes
sql_big_selects  YesBothYes
sql_buffer_result  YesBothYes
sql_log_bin  YesSessionYes
sql_log_off  YesBothYes
sql-modeYesYes  Yes
- Variable: sql_mode  YesBothYes
sql_notes  YesBothYes
sql_quote_show_create  YesBothYes
sql_safe_updates  YesBothYes
sql_select_limit  YesBothYes
sql_slave_skip_counter  YesGlobalYes
sql_warnings  YesBothYes
ssl-caYesYes  No
- Variable: ssl_ca  YesGlobalNo
ssl-capathYesYes  No
- Variable: ssl_capath  YesGlobalNo
ssl-certYesYes  No
- Variable: ssl_cert  YesGlobalNo
ssl-cipherYesYes  No
- Variable: ssl_cipher  YesGlobalNo
ssl-crlYesYes  No
- Variable: ssl_crl  YesGlobalNo
ssl-crlpathYesYes  No
- Variable: ssl_crlpath  YesGlobalNo
ssl-keyYesYes  No
- Variable: ssl_key  YesGlobalNo
storage_engine  YesBothYes
stored_program_cacheYesYesYesGlobalYes
super_read_onlyYesYesYesGlobalYes
sync_binlogYesYesYesGlobalYes
sync_frmYesYesYesGlobalYes
sync_master_infoYesYesYesGlobalYes
sync_relay_logYesYesYesGlobalYes
sync_relay_log_infoYesYesYesGlobalYes
system_time_zone  YesGlobalNo
table_definition_cache  YesGlobalYes
table_open_cache  YesGlobalYes
table_open_cache_instances  YesGlobalNo
thread_cache_sizeYesYesYesGlobalYes
thread_concurrencyYesYesYesGlobalNo
thread_handlingYesYesYesGlobalNo
thread_stackYesYesYesGlobalNo
time_format  YesGlobalNo
time_zone  YesBothYes
timed_mutexesYesYesYesGlobalYes
timestamp  YesSessionYes
tls_versionYesYesYesGlobalNo
tmp_table_sizeYesYesYesBothYes
tmpdirYesYesYesGlobalNo
transaction_alloc_block_sizeYesYesYesBothYes
transaction_allow_batching  YesSessionYes
transaction_prealloc_sizeYesYesYesBothYes
transaction_write_set_extractionYes YesBothYes
tx_isolation  YesBothYes
tx_read_only  YesBothYes
unique_checks  YesBothYes
updatable_views_with_limitYesYesYesBothYes
validate_password_dictionary_file  YesGlobalVaries
validate_password_length  YesGlobalYes
validate_password_mixed_case_count  YesGlobalYes
validate_password_number_count  YesGlobalYes
validate_password_policy  YesGlobalYes
validate_password_special_char_count  YesGlobalYes
validate_user_plugins  YesGlobalNo
version  YesGlobalNo
version_comment  YesGlobalNo
version_compile_machine  YesGlobalNo
version_compile_os  YesGlobalNo
version_tokens_sessionYesYesYesBothYes
version_tokens_session_numberYesYesYesBothNo
wait_timeoutYesYesYesBothYes
warning_count  YesSessionNo

[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

[b] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.


For additional system variable information, see these sections:

Note

Some of the following variable descriptions refer to enabling or disabling a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. Boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case sensitive), as well as 1 and 0. See Section 5.2.5, “Program Option Modifiers”.

Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.

Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.

Some system variables take file name values. Unless otherwise specified, the default file location is the data directory if the value is a relative path name. To specify the location explicitly, use an absolute path name. Suppose that the data directory is /var/mysql/data. If a file-valued variable is given as a relative path name, it will be located under /var/mysql/data. If the value is an absolute path name, its location is as given by the path name.

  • autocommit

    Command-Line Format--autocommit[=#]
    System VariableNameautocommit
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 14.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

    By default, client connections begin with autocommit set to 1. To cause clients to begin with a default of 0, set the global autocommit value by starting the server with the --autocommit=0 option. To set the variable using an option file, include these lines:

    [mysqld]
    autocommit=0
    
  • automatic_sp_privileges

    System VariableNameautomatic_sp_privileges
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultTRUE

    When this variable has a value of 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.

    The creator of a routine is the account used to execute the CREATE statement for it. This might not be the same as the account named as the DEFINER in the routine definition.

    See also Section 21.2.2, “Stored Routines and MySQL Privileges”.

  • auto_generate_certs

    Introduced5.7.5
    Command-Line Format--auto_generate_certs[={OFF|ON}]
    System VariableNameauto_generate_certs
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    This variable is available if the server was compiled using OpenSSL (see Section 7.4.1, “OpenSSL Versus yaSSL”). It controls whether the server autogenerates SSL key and certificate files in the data directory, if they do not already exist.

    At startup, the server automatically generates server-side and client-side SSL certificate and key files in the data directory if the auto_generate_certs system variable is enabled, no SSL options other than --ssl are specified, and the server-side SSL files are missing from the data directory. These files enable secure client connections using SSL; see Section 7.4.4, “Configuring MySQL to Use Secure Connections”.

    For more information about SSL file autogeneration, including file names and characteristics, see Section 7.4.6.1, “Creating SSL and RSA Certificates and Keys using MySQL”

    The sha256_password_auto_generate_rsa_keys system variable is related but controls autogeneration of RSA key-pair files needed for secure password exchange using RSA over unencypted connections.

  • avoid_temporal_upgrade

    Introduced5.7.6
    Deprecated5.7.6
    Command-Line Format--avoid_temporal_upgrade={OFF|ON}
    System VariableNameavoid_temporal_upgrade
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable controls whether ALTER TABLE implicitly upgrades temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). Upgrading such columns requires a table rebuild, which prevents any use of fast alterations that might otherwise apply to the operation to be performed.

    This variable is disabled by default. Enabling it causes ALTER TABLE not to rebuild temporal columns and thereby be able to take advantage of possible fast alterations.

    This variable is deprecated and will be removed in a future MySQL release.

  • back_log

    System VariableNameback_log
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default-1 (autosized)
    Min Value1
    Max Value65535

    The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time.

    In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix listen() system call should have more details. Check your OS documentation for the maximum value for this variable. back_log cannot be set higher than your operating system limit.

    The default value is based on the following formula, capped to a limit of 900:

    50 + (max_connections / 5)
    
  • basedir

    Command-Line Format--basedir=dir_name
    System VariableNamebasedir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The MySQL installation base directory. This variable can be set with the --basedir option. Relative path names for other variables usually are resolved relative to the base directory.

  • big_tables

    Command-Line Format--big-tables
    System VariableNamebig_tables
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The table tbl_name is full does not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable, because in-memory tables are automatically converted to disk-based tables as required.

  • bind_address

    Command-Line Format--bind-address=addr
    System VariableNamebind_address
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default*

    The value of the --bind-address option.

    This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer visible within the embedded server.

  • block_encryption_mode

    Introduced5.7.4
    Command-Line Format--block_encryption_mode=#
    System VariableNameblock_encryption_mode
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaultaes-128-ecb

    This variable controls the block encryption mode for block-based algorithms such as AES. It affects encryption for AES_ENCRYPT() and AES_DECRYPT().

    block_encryption_mode takes a value in aes-keylen-mode format, where keylen is the key length in bits and mode is the encryption mode. The value is not case sensitive. Permitted keylen values are 128, 192, and 256. Permitted encryption modes depend on whether MySQL was compiled using OpenSSL or yaSSL:

    • For OpenSSL, permitted mode values are: ECB, CBC, CFB1, CFB8, CFB128, OFB

    • For yaSSL, permitted mode values are: ECB, CBC

    For example, this statement causes the AES encryption functions to use a key length of 256 bits and the CBC mode:

    SET block_encryption_mode = 'aes-256-cbc';
    

    An error occurs for attempts to set block_encryption_mode to a value containing an unsupported key length or a mode that the SSL library does not support.

  • bulk_insert_buffer_size

    Command-Line Format--bulk_insert_buffer_size=#
    System VariableNamebulk_insert_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default8388608
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default8388608
    Min Value0
    Max Value18446744073709551615

    MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

  • character_set_client

    System VariableNamecharacter_set_client
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server. (Many clients support a --default-character-set option to enable this character set to be specified explicitly. See also Section 11.1.5, “Connection Character Sets and Collations”.) The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests:

    • The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.

    • The client requests a character set not known to the server. For example, a Japanese-enabled client requests sjis when connecting to a server not configured with sjis support.

    • mysqld was started with the --skip-character-set-client-handshake option, which causes it to ignore client character set configuration. This reproduces MySQL 4.0 behavior and is useful should you wish to upgrade the server without upgrading all the clients.

    ucs2, utf16, utf16le, and utf32 cannot be used as a client character set, which means that they also do not work for SET NAMES or SET CHARACTER SET.

  • character_set_connection

    System VariableNamecharacter_set_connection
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    The character set used for literals that do not have a character set introducer and for number-to-string conversion.

  • character_set_database

    System VariableNamecharacter_set_database
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    FootnoteThis option is dynamic, but only the server should set this information. You should not set the value of this variable manually.
    Permitted ValuesTypestring

    The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

    The global character_set_database and collation_database system variables are deprecated as of MySQL 5.7.6 and will be removed in a future version of MySQL.

    Assigning a value to the session character_set_database and collation_database system variables is deprecated as of MySQL 5.7.6 and assignments produce a warning. The session variables will become read only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database.

  • character_set_filesystem

    Command-Line Format--character-set-filesystem=name
    System VariableNamecharacter_set_filesystem
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaultbinary

    The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8'.

  • character_set_results

    System VariableNamecharacter_set_results
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    The character set used for returning query results such as result sets or error messages to the client.

  • character_set_server

    Command-Line Format--character-set-server
    System VariableNamecharacter_set_server
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaultlatin1

    The server's default character set.

  • character_set_system

    System VariableNamecharacter_set_system
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Defaultutf8

    The character set used by the server for storing identifiers. The value is always utf8.

  • character_sets_dir

    Command-Line Format--character-sets-dir=dir_name
    System VariableNamecharacter_sets_dir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The directory where character sets are installed.

  • check_proxy_users

    Introduced5.7.7
    Command-Line Format--check_proxy_users=[={OFF|ON}]
    System VariableNamecheck_proxy_users
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable controls whether the server performs proxy user mapping for authentication plugins that request it. With check_proxy_users enabled, it may also be necessary to enable plugin-specific system variables to take advantage of server proxy user mapping support:

    For information about user proxying, see Section 7.3.9, “Proxy Users”.

    This variable was added in MySQL 5.7.7. Before 5.7.7, proxy user mapping is available only for plugins that implement it for themselves.

  • collation_connection

    System VariableNamecollation_connection
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    The collation of the connection character set.

  • collation_database

    System VariableNamecollation_database
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    FootnoteThis option is dynamic, but only the server should set this information. You should not set the value of this variable manually.
    Permitted ValuesTypestring

    The collation used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as collation_server.

    The global character_set_database and collation_database system variables are deprecated as of MySQL 5.7.6 and will be removed in a future version of MySQL.

    Assigning a value to the session character_set_database and collation_database system variables is deprecated as of MySQL 5.7.6 and assignments produce a warning. The session variables will become read only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database.

  • collation_server

    Command-Line Format--collation-server
    System VariableNamecollation_server
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaultlatin1_swedish_ci

    The server's default collation.

  • completion_type

    Command-Line Format--completion_type=#
    System VariableNamecompletion_type
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultNO_CHAIN
    Valid ValuesNO_CHAIN
    CHAIN
    RELEASE
    0
    1
    2

    The transaction completion type. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.

    ValueDescription
    NO_CHAIN (or 0)COMMIT and ROLLBACK are unaffected. This is the default value.
    CHAIN (or 1)COMMIT and ROLLBACK are equivalent to COMMIT AND CHAIN and ROLLBACK AND CHAIN, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)
    RELEASE (or 2)COMMIT and ROLLBACK are equivalent to COMMIT RELEASE and ROLLBACK RELEASE, respectively. (The server disconnects after terminating the transaction.)

    completion_type affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK. It does not apply to implicit commits resulting from execution of the statements listed in Section 14.3.3, “Statements That Cause an Implicit Commit”. It also does not apply for XA COMMIT, XA ROLLBACK, or when autocommit=1.

  • concurrent_insert

    Command-Line Format--concurrent_insert[=#]
    System VariableNameconcurrent_insert
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultAUTO
    Valid ValuesNEVER
    AUTO
    ALWAYS
    0
    1
    2

    If AUTO (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you start mysqld with --skip-new, this variable is set to NEVER.

    This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.

    ValueDescription
    NEVER (or 0)Disables concurrent inserts
    AUTO (or 1)(Default) Enables concurrent insert for MyISAM tables that do not have holes
    ALWAYS (or 2)Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

    See also Section 9.11.3, “Concurrent Inserts”.

  • connect_timeout

    Command-Line Format--connect_timeout=#
    System VariableNameconnect_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default10
    Min Value2
    Max Value31536000

    The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds.

    Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.

  • core_file

    System VariableNamecore_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether to write a core file if the server crashes. This variable is set by the --core-file option.

  • datadir

    Command-Line Format--datadir=dir_name
    System VariableNamedatadir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The MySQL data directory. This variable can be set with the --datadir option.

  • date_format

    This variable is unused. It is deprecated and will be removed in a future MySQL release.

  • datetime_format

    This variable is unused. It is deprecated and will be removed in a future MySQL release.

  • debug

    Command-Line Format--debug[=debug_options]
    System VariableNamedebug
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (Unix)Typestring
    Defaultd:t:i:o,/tmp/mysqld.trace
    Permitted Values (Windows)Typestring
    Defaultd:t:i:O,\mysqld.trace

    This variable indicates the current debugging settings. It is available only for servers built with debugging support. The initial value comes from the value of instances of the --debug option given at server startup. The global and session values may be set at runtime; the SUPER privilege is required, even for the session value.

    Assigning a value that begins with + or - cause the value to added to or subtracted from the current value:

    mysql> SET debug = 'T';
    mysql> SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | T       |
    +---------+
    
    mysql> SET debug = '+P';
    mysql> SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | P:T     |
    +---------+
    
    mysql> SET debug = '-P';
    mysql> SELECT @@debug;
    +---------+
    | @@debug |
    +---------+
    | T       |
    +---------+
    

    For more information, see Section 26.5.3, “The DBUG Package”.

  • debug_sync

    System VariableNamedebug_sync
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypestring

    This variable is the user interface to the Debug Sync facility. Use of Debug Sync requires that MySQL be configured with the -DENABLE_DEBUG_SYNC=1 CMake option (see Section 2.9.4, “MySQL Source-Configuration Options”). If Debug Sync is not compiled in, this system variable is not available.

    The global variable value is read only and indicates whether the facility is enabled. By default, Debug Sync is disabled and the value of debug_sync is OFF. If the server is started with --debug-sync-timeout=N, where N is a timeout value greater than 0, Debug Sync is enabled and the value of debug_sync is ON - current signal followed by the signal name. Also, N becomes the default timeout for individual synchronization points.

    The session value can be read by any user and will have the same value as the global variable. The session value can be set by users that have the SUPER privilege to control synchronization points.

    For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.

  • default_authentication_plugin

    Introduced5.7.2
    Command-Line Format--default-authentication-plugin=plugin_name
    System VariableNamedefault_authentication_plugin
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeenumeration
    Defaultmysql_native_password
    Valid Valuesmysql_native_password
    sha256_password

    The default authentication plugin. Permitted values are mysql_native_password (use MySQL native passwords; this is the default) and sha256_password (use SHA-256 passwords). For more information about these plugins, see Section 7.5.1.1, “The Native Authentication Plugin”, and Section 7.5.1.4, “The SHA-256 Authentication Plugin”.

    Note

    If you use this variable to change the default authentication plugin to a value other than mysql_native_password, clients older than MySQL 5.5.6 will no longer be able to connect because they will not understand the resulting change to the authentication protocol.

    The value of default_authentication_plugin affects these aspects of server operation:

    • It determines which authentication plugin the server assigns to new accounts created by CREATE USER and GRANT statements that do not name a plugin explicitly with an IDENTIFIED WITH clause.

    • It sets the old_passwords system variable at startup to the value that is consistent with the password hashing method required by the default plugin. The old_passwords value affects hashing of passwords specified in the IDENTIFIED BY clause of CREATE USER and GRANT, and passwords specified as the argument to the PASSWORD() function.

    • For an account created with either of the following statements, the server associates the account with the default authentication plugin and assigns the account the given password, hashed according to the value of old_passwords.

      CREATE USER ... IDENTIFIED BY 'cleartext password';
      GRANT ...  IDENTIFIED BY 'cleartext password';
      
    • For an account created with either of the following statements, the statement fails if the password hash is not encrypted using the hash format required by the default authentication plugin. Otherwise, the server associates the account with the default authentication plugin and assigns the account the given password hash.

      CREATE USER ... IDENTIFIED BY PASSWORD 'encrypted password';
      GRANT ...  IDENTIFIED BY PASSWORD 'encrypted password';
      

    This variable was added in MySQL 5.7.2. Earlier in MySQL 5.7, use the --default-authentication-plugin command-line option instead, which is used the same way at server startup, but cannot be accessed at runtime.

  • default_password_lifetime

    Introduced5.7.4
    Command-Line Format--default_password_lifetime=#
    System VariableNamedefault_password_lifetime
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.10)Typeinteger
    Default360
    Min Value0
    Max Value65535
    Permitted Values (>= 5.7.11)Typeinteger
    Default0
    Min Value0
    Max Value65535

    This variable defines the global automatic password expiration policy. It applies to accounts that use MySQL built-in authentication methods (accounts that use an authentication plugin of mysql_native_password, mysql_old_password, or sha256_password).

    The default default_password_lifetime value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

    The global password expiration policy can be overridden as desired for individual accounts using the ALTER USER statement. See Section 7.3.6, “Password Expiration Policy”.

    Note

    From MySQL 5.7.4 to 5.7.10, the default default_password_lifetime value is 360 (passwords must be changed approximately once per year). For those versions, be aware that, if you make no changes to the default_password_lifetime variable or to individual user accounts, all user passwords will expire after 360 days, and all user accounts will start running in restricted mode when this happens. Clients (which are effectively users) connecting to the server will then get an error indicating that the password must be changed: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

    However, this is easy to miss for clients that automatically connect to the server, such as connections made from scripts. To avoid having such clients suddenly stop working due to a password expiring, make sure to change the password expiration settings for those clients, like this:

    ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER

    Alternatively, set the default_password_lifetime variable to 0, thus disabling automatic password expiration for all users.

  • default_storage_engine

    Command-Line Format--default-storage-engine=name
    System VariableNamedefault_storage_engine
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultInnoDB

    The default storage engine. This variable sets the storage engine for permanent tables only. To set the storage engine for TEMPORARY tables, set the default_tmp_storage_engine system variable.

    To see which storage engines are available and enabled, use the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table.

    default_storage_engine should be used in preference to storage_engine, which is deprecated and was removed in MySQL 5.7.5.

    If you disable the default storage engine at server startup, you must set the default engine for both permanent and TEMPORARY tables to a different engine or the server will not start.

  • default_tmp_storage_engine

    Command-Line Format--default_tmp_storage_engine=name
    System VariableNamedefault_tmp_storage_engine
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultInnoDB

    The default storage engine for TEMPORARY tables (created with CREATE TEMPORARY TABLE). To set the storage engine for permanent tables, set the default_storage_engine system variable. Also see the discussion of that variable regarding possible values.

    If you disable the default storage engine at server startup, you must set the default engine for both permanent and TEMPORARY tables to a different engine or the server will not start.

  • default_week_format

    Command-Line Format--default_week_format=#
    System VariableNamedefault_week_format
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value7

    The default mode value to use for the WEEK() function. See Section 13.7, “Date and Time Functions”.

  • delay_key_write

    Command-Line Format--delay-key-write[=name]
    System VariableNamedelay_key_write
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultON
    Valid ValuesON
    OFF
    ALL

    This option applies only to MyISAM tables. It can have one of the following values to affect handling of the DELAY_KEY_WRITE table option that can be used in CREATE TABLE statements.

    OptionDescription
    OFFDELAY_KEY_WRITE is ignored.
    ONMySQL honors any DELAY_KEY_WRITE option specified in CREATE TABLE statements. This is the default value.
    ALLAll new opened tables are treated as if they were created with the DELAY_KEY_WRITE option enabled.

    If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the --myisam-recover-options option (for example, --myisam-recover-options=BACKUP,FORCE). See Section 6.1.3, “Server Command Options”, and Section 16.2.1, “MyISAM Startup Options”.

    Warning

    If you enable external locking with --external-locking, there is no protection against index corruption for tables that use delayed key writes.

  • delayed_insert_limit

    Deprecated5.6.7
    Command-Line Format--delayed_insert_limit=#
    System VariableNamedelayed_insert_limit
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default100
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default100
    Min Value1
    Max Value18446744073709551615

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • delayed_insert_timeout

    Deprecated5.6.7
    Command-Line Format--delayed_insert_timeout=#
    System VariableNamedelayed_insert_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default300

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • delayed_queue_size

    Deprecated5.6.7
    Command-Line Format--delayed_queue_size=#
    System VariableNamedelayed_queue_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default1000
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default1000
    Min Value1
    Max Value18446744073709551615

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • disabled_storage_engines

    Introduced5.7.8
    Command-Line Format--disabled_storage_engines=engine[,engine]...
    System VariableNamedisabled_storage_engines
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Defaultempty string

    This variable indicates which storage engines cannot be used to create tables or tablespaces. For example, to prevent new MyISAM or FEDERATED tables from being created, start the server with these lines in the server option file:

    [mysqld]
    disabled_storage_engines="MyISAM,FEDERATED"
    

    By default, disabled_storage_engines is empty (no engines disabled), but it can be set to a comma-separated list of one or more engines (not case sensitive). Any engine named in the value cannot be used to create tables or tablespaces with CREATE TABLE or CREATE TABLESPACE, and cannot be used with ALTER TABLE ... ENGINE or ALTER TABLESPACE ... ENGINE to change the storage engine of existing tables or tablespaces. Attempts to do so result in an ER_DISABLED_STORAGE_ENGINE error.

    disabled_storage_engines does not restrict other DDL statements for existing tables, such as CREATE INDEX, TRUNCATE TABLE, ANALYZE TABLE, DROP TABLE, or DROP TABLESPACE. This permits a smooth transition so that existing tables or tablespaces that use a disabled engine can be migrated to a permitted engine by means such as ALTER TABLE ... ENGINE permitted_engine.

    It is permitted to set the default_storage_engine or default_tmp_storage_engine system variable to a storage engine that is disabled. This could cause applications to behave erratically or fail, although that might be a useful technique in a development environment for identifying applications that use disabled engines, so that they can be modified.

    disabled_storage_engines is disabled and has no effect if the server is started with any of these options: --bootstrap, --initialize, --initialize-insecure, --skip-grant-tables.

  • disconnect_on_expired_password

    Introduced5.7.1
    Command-Line Format--disconnect_on_expired_password[=#]
    System VariableNamedisconnect_on_expired_password
    Variable ScopeSession
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    This variable controls how the server handles clients with expired passwords:

    For more information about the interaction of client and server settings relating to expired-password handling, see Section 7.3.7, “Password Expiration and Sandbox Mode”.

  • div_precision_increment

    Command-Line Format--div_precision_increment=#
    System VariableNamediv_precision_increment
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default4
    Min Value0
    Max Value30

    This variable indicates the number of digits by which to increase the scale of the result of division operations performed with the / operator. The default value is 4. The minimum and maximum values are 0 and 30, respectively. The following example illustrates the effect of increasing the default value.

    mysql> SELECT 1/7;
    +--------+
    | 1/7    |
    +--------+
    | 0.1429 |
    +--------+
    mysql> SET div_precision_increment = 12;
    mysql> SELECT 1/7;
    +----------------+
    | 1/7            |
    +----------------+
    | 0.142857142857 |
    +----------------+
    
  • end_markers_in_json

    System VariableNameend_markers_in_json
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether optimizer JSON output should add end markers.

  • eq_range_index_dive_limit

    System VariableNameeq_range_index_dive_limit
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (<= 5.7.3)Typeinteger
    Default10
    Min Value0
    Max Value4294967295
    Permitted Values (>= 5.7.4)Typeinteger
    Default200
    Min Value0
    Max Value4294967295

    This variable indicates the number of equality ranges in an equality comparison condition when the optimizer should switch from using index dives to index statistics in estimating the number of qualifying rows. It applies to evaluation of expressions that have either of these equivalent forms, where the optimizer uses a nonunique index to look up col_name values:

    col_name IN(val1, ..., valN)
    col_name = val1 OR ... OR col_name = valN
    

    In both cases, the expression contains N equality ranges. The optimizer can make row estimates using index dives or index statistics. If eq_range_index_dive_limit is greater than 0, the optimizer uses existing index statistics instead of index dives if there are eq_range_index_dive_limit or more equality ranges. Thus, to permit use of index dives for up to N equality ranges, set eq_range_index_dive_limit to N + 1. To disable use of index statistics and always use index dives regardless of N, set eq_range_index_dive_limit to 0.

    For more information, see Section 9.2.1.3.3, “Equality Range Optimization of Many-Valued Comparisons”.

    To update table index statistics for best estimates, use ANALYZE TABLE.

  • error_count

    The number of errors that resulted from the last statement that generated messages. This variable is read only. See Section 14.7.5.17, “SHOW ERRORS Syntax”.

  • event_scheduler

    Command-Line Format--event-scheduler[=value]
    System VariableNameevent_scheduler
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultOFF
    Valid ValuesON
    OFF
    DISABLED

    This variable indicates the status of the Event Scheduler; possible values are ON, OFF, and DISABLED, with the default being OFF. This variable and its effects on the Event Scheduler's operation are discussed in greater detail in the Overview section of the Events chapter.

  • expire_logs_days

    Command-Line Format--expire_logs_days=#
    System VariableNameexpire_logs_days
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value99

    The number of days for automatic binary log file removal. The default is 0, which means no automatic removal. Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 6.4, “MySQL Server Logs”.

    To remove binary log files manually, use the PURGE BINARY LOGS statement. See Section 14.4.1.1, “PURGE BINARY LOGS Syntax”.

  • explicit_defaults_for_timestamp

    Deprecated5.6.6
    Command-Line Format--explicit_defaults_for_timestamp=#
    System VariableNameexplicit_defaults_for_timestamp
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE

    In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types:

    • TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

    • The first TIMESTAMP column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

    • TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the zero timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

    Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup:

    [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
    Please use --explicit_defaults_for_timestamp server option (see
    documentation for more details).
    

    As indicated by the warning, to turn off the nonstandard behaviors, enable the explicit_defaults_for_timestamp system variable at server startup. With this variable enabled, the server handles TIMESTAMP as follows instead:

    • TIMESTAMP columns not explicitly declared as NOT NULL permit NULL values. Setting such a column to NULL sets it to NULL, not the current timestamp.

    • No TIMESTAMP column is assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes must be explicitly specified.

    • TIMESTAMP columns declared as NOT NULL and without an explicit DEFAULT clause are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME.

    Note

    explicit_defaults_for_timestamp is itself deprecated because its only purpose is to permit control over now-deprecated TIMESTAMP behaviors that will be removed in a future MySQL release. When that removal occurs, explicit_defaults_for_timestamp will have no purpose and will be removed as well.

  • external_user

    System VariableNameexternal_user
    Variable ScopeSession
    Dynamic VariableNo
    Permitted ValuesTypestring

    The external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this variable is NULL. See Section 7.3.9, “Proxy Users”.

  • flush

    Command-Line Format--flush
    System VariableNameflush
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    If ON, the server flushes (synchronizes) all changes to disk after each SQL statement. Normally, MySQL does a write of all changes to disk only after each SQL statement and lets the operating system handle the synchronizing to disk. See Section B.5.3.3, “What to Do If MySQL Keeps Crashing”. This variable is set to ON if you start mysqld with the --flush option.

  • flush_time

    Command-Line Format--flush_time=#
    System VariableNameflush_time
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Permitted Values (Windows)Typeinteger
    Default0
    Min Value0

    If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. This option is best used only on systems with minimal resources.

  • foreign_key_checks

    If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, foreign key constraints are ignored, with a couple of exceptions. When re-creating a table that was dropped, an error is returned if the table definition does not conform to the foreign key constraints referencing the table. Likewise, an ALTER TABLE operation returns an error if a foreign key definition is incorrectly formed. For more information, see Section 14.1.18.3, “Using FOREIGN KEY Constraints”.

    Setting this variable has the same effect on NDB tables as it does for InnoDB tables. Typically you leave this setting enabled during normal operation, to enforce referential integrity. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships. See Section 15.6.6, “InnoDB and FOREIGN KEY Constraints”.

    Setting foreign_key_checks to 0 also affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables.

    Note

    Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks=0 will not be verified for consistency.

    As of MySQL 5.7.5, dropping an index required by a foreign key constraint is not permitted, even with foreign_key_checks=0. The foreign key constraint must be removed before dropping the index (Bug #70260).

  • ft_boolean_syntax

    Command-Line Format--ft_boolean_syntax=name
    System VariableNameft_boolean_syntax
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    Default+ -><()~*:""&|

    The list of operators supported by boolean full-text searches performed using IN BOOLEAN MODE. See Section 13.9.2, “Boolean Full-Text Searches”.

    The default variable value is '+ -><()~*:""&|'. The rules for changing the value are as follows:

    • Operator function is determined by position within the string.

    • The replacement value must be 14 characters.

    • Each character must be an ASCII nonalphanumeric character.

    • Either the first or second character must be a space.

    • No duplicates are permitted except the phrase quoting operators in positions 11 and 12. These two characters are not required to be the same, but they are the only two that may be.

    • Positions 10, 13, and 14 (which by default are set to :, &, and |) are reserved for future extensions.

  • ft_max_word_len

    Command-Line Format--ft_max_word_len=#
    System VariableNameft_max_word_len
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Min Value10

    The maximum length of the word to be included in a MyISAM FULLTEXT index.

    Note

    FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK.

  • ft_min_word_len

    Command-Line Format--ft_min_word_len=#
    System VariableNameft_min_word_len
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default4
    Min Value1

    The minimum length of the word to be included in a MyISAM FULLTEXT index.

    Note

    FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK.

  • ft_query_expansion_limit

    Command-Line Format--ft_query_expansion_limit=#
    System VariableNameft_query_expansion_limit
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default20
    Min Value0
    Max Value1000

    The number of top matches to use for full-text searches performed using WITH QUERY EXPANSION.

  • ft_stopword_file

    Command-Line Format--ft_stopword_file=file_name
    System VariableNameft_stopword_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The file from which to read the list of stopwords for full-text searches on MyISAM tables. The server looks for the file in the data directory unless an absolute path name is given to specify a different directory. All the words from the file are used; comments are not honored. By default, a built-in list of stopwords is used (as defined in the storage/myisam/ft_static.c file). Setting this variable to the empty string ('') disables stopword filtering. See also Section 13.9.4, “Full-Text Stopwords”.

    Note

    FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable or the contents of the stopword file. Use REPAIR TABLE tbl_name QUICK.

  • general_log

    Command-Line Format--general-log
    System VariableNamegeneral_log
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether the general query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log. The default value depends on whether the --general_log option is given. The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

  • general_log_file

    Command-Line Format--general-log-file=file_name
    System VariableNamegeneral_log_file
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypefile name
    Defaulthost_name.log

    The name of the general query log file. The default value is host_name.log, but the initial value can be changed with the --general_log_file option.

  • group_concat_max_len

    Command-Line Format--group_concat_max_len=#
    System VariableNamegroup_concat_max_len
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default1024
    Min Value4
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default1024
    Min Value4
    Max Value18446744073709551615

    The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.

  • have_compress

    YES if the zlib compression library is available to the server, NO if not. If not, the COMPRESS() and UNCOMPRESS() functions cannot be used.

  • have_crypt

    YES if the crypt() system call is available to the server, NO if not. If not, the ENCRYPT() function cannot be used.

  • have_dynamic_loading

    YES if mysqld supports dynamic loading of plugins, NO if not. If the value is NO, you cannot use options such as --plugin-load to load plugins at server startup, or the INSTALL PLUGIN statement to load plugins at runtime.

  • have_geometry

    YES if the server supports spatial data types, NO if not.

  • have_openssl

    This variable is an alias for have_ssl.

  • have_profiling

    YES if statement profiling capability is present, NO if not. If present, the profiling system variable controls whether this capability is enabled or disabled. See Section 14.7.5.31, “SHOW PROFILES Syntax”.

    This variable is deprecated and will be removed in a future MySQL release.

  • have_query_cache

    YES if mysqld supports the query cache, NO if not.

  • have_rtree_keys

    YES if RTREE indexes are available, NO if not. (These are used for spatial indexes in MyISAM tables.)

  • have_ssl

    YES if mysqld supports SSL connections, NO if not. DISABLED indicates that the server was compiled with SSL support, but was not started with the appropriate --ssl-xxx options. For more information, see Section 7.4.2, “Building MySQL with Support for Secure Connections”.

  • have_statement_timeout

    Introduced5.7.4
    System VariableNamehave_statement_timeout
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean

    Whether the statement execution timeout feature is available (see Statement Execution Time Optimizer Hints). The value can be NO if the background thread used by this feature could not be initialized.

  • have_symlink

    YES if symbolic link support is enabled, NO if not. This is required on Unix for support of the DATA DIRECTORY and INDEX DIRECTORY table options. If the server is started with the --skip-symbolic-links option, the value is DISABLED.

    This variable has no meaning on Windows.

  • host_cache_size

    System VariableNamehost_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default-1 (autosized)
    Min Value0
    Max Value65536

    The size of the internal host cache (see Section 9.12.6.2, “DNS Lookup Optimization and the Host Cache”). Setting the size to 0 disables the host cache. Changing the cache size at runtime implicitly causes a FLUSH HOSTS operation to clear the host cache and truncate the host_cache table.

    The default value is 128, plus 1 for a value of max_connections up to 500, plus 1 for every increment of 20 over 500 in the max_connections value, capped to a limit of 2000.

    Use of --skip-host-cache is similar to setting the host_cache_size system variable to 0, but host_cache_size is more flexible because it can also be used to resize, enable, or disable the host cache at runtime, not just at server startup.

    If you start the server with --skip-host-cache, that does not prevent changes to the value of host_cache_size, but such changes have no effect and the cache is not re-enabled even if host_cache_size is set larger than 0.

  • hostname

    System VariableNamehostname
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The server sets this variable to the server host name at startup.

  • identity

    This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity.

  • ignore_db_dirs

    System VariableNameignore_db_dirs
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    A comma-separated list of names that are not considered as database directories in the data directory. The value is set from any instances of --ignore-db-dir given at server startup.

    As of MySQL 5.7.11, --ignore-db-dir can be used at data directory initialization time with mysqld --initialize to specify directories that the server should ignore for purposes of assessing whether an existing data directory is considered empty. See Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld”.

  • init_connect

    Command-Line Format--init-connect=name
    System VariableNameinit_connect
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled. For older servers (before MySQL 5.5.8), there is no global autocommit system variable to specify that autocommit should be disabled by default, but as a workaround init_connect can be used to achieve the same effect:

    SET GLOBAL init_connect='SET autocommit=0';
    

    The init_connect variable can also be set on the command line or in an option file. To set the variable as just shown using an option file, include these lines:

    [mysqld]
    init_connect='SET autocommit=0'
    

    The content of init_connect is not executed for users that have the SUPER privilege. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the SUPER privilege enables them to open a connection and fix the init_connect value.

    The server discards any result sets produced by statements in the value of of init_connect.

  • init_file

    Command-Line Format--init-file=file_name
    System VariableNameinit_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The name of the file specified with the --init-file option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts. Each statement must be on a single line and should not include comments. For more information, see the description of --init-file.

  • innodb_xxx

    InnoDB system variables are listed in Section 15.12, “InnoDB Startup Options and System Variables”. These variables control many aspects of storage, memory use, and I/O patterns for InnoDB tables, and are especially important now that InnoDB is the default storage engine.

  • insert_id

    The value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

  • interactive_timeout

    Command-Line Format--interactive_timeout=#
    System VariableNameinteractive_timeout
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default28800
    Min Value1

    The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

  • internal_tmp_disk_storage_engine

    Introduced5.7.5
    Command-Line Format--internal_tmp_disk_storage_engine=#
    System VariableNameinternal_tmp_disk_storage_engine
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (5.7.5)Typeenumeration
    DefaultMYISAM
    Valid ValuesMYISAM
    INNODB
    Permitted Values (>= 5.7.6)Typeenumeration
    DefaultINNODB
    Valid ValuesMYISAM
    INNODB

    The storage engine for on-disk internal temporary tables (see Section 9.4.4, “Internal Temporary Table Use in MySQL”). Permitted values are MYISAM and INNODB.

    This variable was added in MySQL 5.7.5 with a default of MYISAM. In MySQL 5.7.6, the default value was changed to INNODB. With this change, the optimizer uses the InnoDB storage engine by default for on-disk internal temporary tables.

    Under internal_tmp_disk_storage_engine=INNODB, queries that generate temporary tables that exceed InnoDB row or column limits will return Row size too large or Too many columns errors. The workaround is to set internal_tmp_disk_storage_engine to MYISAM.

  • join_buffer_size

    Command-Line Format--join_buffer_size=#
    System VariableNamejoin_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (Windows)Typeinteger
    Default262144
    Min Value128
    Max Value4294967295
    Permitted Values (Other, 32-bit platforms)Typeinteger
    Default262144
    Min Value128
    Max Value4294967295
    Permitted Values (Other, 64-bit platforms)Typeinteger
    Default262144
    Min Value128
    Max Value18446744073709547520

    The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

    Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.

    When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

    The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).

    For additional information about join buffering, see Section 9.2.1.10, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 9.2.1.14, “Block Nested-Loop and Batched Key Access Joins”.

  • keep_files_on_create

    Command-Line Format--keep_files_on_create=#
    System VariableNamekeep_files_on_create
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is created in the database directory. By default, if MyISAM finds an existing .MYD file in this case, it overwrites it. The same applies to .MYI files for tables created with no INDEX DIRECTORY option. To suppress this behavior, set the keep_files_on_create variable to ON (1), in which case MyISAM will not overwrite existing files and returns an error instead. The default value is OFF (0).

    If a MyISAM table is created with a DATA DIRECTORY or INDEX DIRECTORY option and an existing .MYD or .MYI file is found, MyISAM always returns an error. It will not overwrite a file in the specified directory.

  • key_buffer_size

    Command-Line Format--key_buffer_size=#
    System VariableNamekey_buffer_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default8388608
    Min Value8
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default8388608
    Min Value8
    Max ValueOS_PER_PROCESS_LIMIT

    Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

    The maximum permissible setting for key_buffer_size is 4GB−1 on 32-bit platforms. Larger values are permitted for 64-bit platforms. The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.

    You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.

    For even more speed when writing many rows at the same time, use LOCK TABLES. See Section 9.2.2.1, “Speed of INSERT Statements”.

    You can check the performance of the key buffer by issuing a SHOW STATUS statement and examining the Key_read_requests, Key_reads, Key_write_requests, and Key_writes status variables. (See Section 14.7.5, “SHOW Syntax”.) The Key_reads/Key_read_requests ratio should normally be less than 0.01. The Key_writes/Key_write_requests ratio is usually near 1 if you are using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time or if you are using the DELAY_KEY_WRITE table option.

    The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable:

    1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)
    

    This value is an approximation because some space in the key buffer is allocated internally for administrative structures. Factors that influence the amount of overhead for these structures include block size and pointer size. As block size increases, the percentage of the key buffer lost to overhead tends to decrease. Larger blocks results in a smaller number of read operations (because more keys are obtained per read), but conversely an increase in reads of keys that are not examined (if not all keys in a block are relevant to a query).

    It is possible to create multiple MyISAM key caches. The size limit of 4GB applies to each cache individually, not as a group. See Section 9.10.2, “The MyISAM Key Cache”.

  • key_cache_age_threshold

    Command-Line Format--key_cache_age_threshold=#
    System VariableNamekey_cache_age_threshold
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default300
    Min Value100
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default300
    Min Value100
    Max Value18446744073709551615

    This value controls the demotion of buffers from the hot sublist of a key cache to the warm sublist. Lower values cause demotion to happen more quickly. The minimum value is 100. The default value is 300. See Section 9.10.2, “The MyISAM Key Cache”.

  • key_cache_block_size

    Command-Line Format--key_cache_block_size=#
    System VariableNamekey_cache_block_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1024
    Min Value512
    Max Value16384

    The size in bytes of blocks in the key cache. The default value is 1024. See Section 9.10.2, “The MyISAM Key Cache”.

  • key_cache_division_limit

    Command-Line Format--key_cache_division_limit=#
    System VariableNamekey_cache_division_limit
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default100
    Min Value1
    Max Value100

    The division point between the hot and warm sublists of the key cache buffer list. The value is the percentage of the buffer list to use for the warm sublist. Permissible values range from 1 to 100. The default value is 100. See Section 9.10.2, “The MyISAM Key Cache”.

  • keyring_file_data

    Introduced5.7.11
    Command-Line Format--keyring_file_data=file_name
    System VariableNamekeyring_file_data
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypefile name
    Defaultplatform specific

    The path name of the data file used for secure data storage by the keyring_file plugin (see Section 7.5.3.2, “Configuring the keyring_file File-Based Plugin”). The file location should be in a directory considered for use only by the keyring_file plugin. For example, do not locate the file under the data directory.

    Keyring operations are transactional: The keyring_file plugin uses a backup file during write operations to ensure that it can roll back to the original file if an operation fails. The backup file has the same name as the value of the keyring_file_data system variable with an extension of .backup.

    Do not use the same keyring_file data file for multiple MySQL instances. Each instance should have its own unique data file.

    The default file name is keyring, located in a directory that is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table. To specify the default directory for the file explicitly if you are building from source, use the INSTALL_MYSQLKEYRINGDIR CMake option.

    INSTALL_LAYOUT ValueDefault keyring_file_data Value
    DEB, RPM, SLES, SVR4/var/lib/mysql-keyring/keyring
    Otherwisekeyring/keyring under the CMAKE_INSTALL_PREFIX value

    If the value assigned to keyring_file_data specifies a file that does not exist, the keyring_file plugin attempts to create it during plugin initialization. If necessary, the plugin also creates the directory in which the file is located.

    If you create the directory manually, it should have a restrictive mode and be accessible only to the account used to run the server. For example, on Unix and Unix-like systems, to use /usr/local/mysql/mysql-keyring/keyring, the following commands (executed as root) create the directory and set its mode and ownership:

    shell> cd /usr/local/mysql
    shell> mkdir mysql-keyring
    shell> chmod 750 mysql-keyring
    shell> chown mysql mysql-keyring
    shell> chgrp mysql mysql-keyring
    

    If the keyring_file plugin cannot create or access the file, it writes an error message to the error log. If an attempted runtime assignment to keyring_file_data results in an error, the variable value remains unchanged.

    Important

    Once the keyring_file plugin has created the keyring_file plugin data file and started to use it, it is important not to remove the file. For example, InnoDB uses the file to store the master key used to decrypt the data in tables that use tablespace encryption; see Section 15.5.10, “InnoDB Tablespace Encryption”. Loss of the file will cause data in such tables to become inaccessible. (It is permissible to rename or move the file, as long as you change the value of keyring_file_data to match.) It is recommended that you create a separate backup of the keyring file immediately after you create the first encrypted table and before and after master key rotation.

  • keyring_okv_conf_dir

    Introduced5.7.12
    Command-Line Format--keyring_okv_conf_dir=dir_name
    System VariableNamekeyring_okv_conf_dir
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypedirectory name
    Defaultempty string

    The path name of the directory that stores configuration information used by the keyring_okv plugin (see Section 7.5.3.3, “Configuring the keyring_okv Oracle Key Vault Plugin”). The location should be a directory considered for use only by the keyring_okv plugin. For example, do not locate the directory under the data directory.

    The default keyring_okv_conf_dir value is empty. For the keyring_okv plugin to be able to access Oracle Key Vault, the value must be set to a directory that contains Oracle Key Vault configuration and SSL materials. For instructions on setting up this directory, see Section 7.5.3.3, “Configuring the keyring_okv Oracle Key Vault Plugin”.

    The directory should have a restrictive mode and be accessible only to the account used to run the server. For example, on Unix and Unix-like systems, to use /usr/local/mysql/mysql-keyring-okv, the following commands (executed as root) create the directory and set its mode and ownership:

    shell> cd /usr/local/mysql
    shell> mkdir mysql-keyring-okv
    shell> chmod 750 mysql-keyring-okv
    shell> chown mysql mysql-keyring-okv
    shell> chgrp mysql mysql-keyring-okv
    

    If the value assigned to keyring_okv_conf_dir specifies a directory that does not exist, or that does not contain configuration information that enables a connection to Oracle Key Vault to be established, keyring_okv writes an error message to the error log. If an attempted runtime assignment to keyring_okv_conf_dir results in an error, the variable value and keyring operation remain unchanged.

  • large_files_support

    System VariableNamelarge_files_support
    Variable ScopeGlobal
    Dynamic VariableNo

    Whether mysqld was compiled with options for large file support.

  • large_pages

    Command-Line Format--large-pages
    System VariableNamelarge_pages
    Variable ScopeGlobal
    Dynamic VariableNo
    Platform SpecificLinux
    Permitted Values (Linux)Typeboolean
    DefaultFALSE

    Whether large page support is enabled (via the --large-pages option). See Section 9.12.5.2, “Enabling Large Page Support”.

  • large_page_size

    System VariableNamelarge_page_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (Linux)Typeinteger
    Default0

    If large page support is enabled, this shows the size of memory pages. Large memory pages are supported only on Linux; on other platforms, the value of this variable is always 0. See Section 9.12.5.2, “Enabling Large Page Support”.

  • last_insert_id

    The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function.

  • lc_messages

    Command-Line Format--lc-messages=name
    System VariableNamelc_messages
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaulten_US

    The locale to use for error messages. The default is en_US. The server converts the argument to a language name and combines it with the value of lc_messages_dir to produce the location for the error message file. See Section 11.2, “Setting the Error Message Language”.

  • lc_messages_dir

    Command-Line Format--lc-messages-dir=dir_name
    System VariableNamelc_messages_dir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The directory where error messages are located. The server uses the value together with the value of lc_messages to produce the location for the error message file. See Section 11.2, “Setting the Error Message Language”.

  • lc_time_names

    System VariableNamelc_time_names
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    This variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. Locale names are POSIX-style values such as 'ja_JP' or 'pt_BR'. The default value is 'en_US' regardless of your system's locale setting. For further information, see Section 11.7, “MySQL Server Locale Support”.

  • license

    System VariableNamelicense
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    DefaultGPL

    The type of license the server has.

  • local_infile

    System VariableNamelocal_infile
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Defaulttrue

    Whether LOCAL is supported for LOAD DATA INFILE statements. If this variable is disabled, clients cannot use LOCAL in LOAD DATA statements. While the default for this variable is true, whether LOAD DATA INFILE LOCAL is actually permitted depends on how MySQL was compiled, as well as a number of settings on both the server and the client; see Section 7.1.6, “Security Issues with LOAD DATA LOCAL”, for details.

  • lock_wait_timeout

    Command-Line Format--lock_wait_timeout=#
    System VariableNamelock_wait_timeout
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default31536000
    Min Value1
    Max Value31536000

    This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

    This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.

    This timeout does not apply to implicit accesses to system tables in the mysql database, such as grant tables modified by GRANT or REVOKE statements or table logging statements. The timeout does apply to system tables accessed directly, such as with SELECT or UPDATE.

    The timeout value applies separately for each metadata lock attempt. A given statement can require more than one lock, so it is possible for the statement to block for longer than the lock_wait_timeout value before reporting a timeout error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is reported.

    lock_wait_timeout does not apply to delayed inserts, which always execute with a timeout of 1 year. This is done to avoid unnecessary timeouts because a session that issues a delayed insert receives no notification of delayed insert timeouts.

  • locked_in_memory

    System VariableNamelocked_in_memory
    Variable ScopeGlobal
    Dynamic VariableNo

    Whether mysqld was locked in memory with --memlock.

  • log_backward_compatible_user_definitions

    Introduced5.7.6
    Removed5.7.9
    Command-Line Format--log_backward_compatible_user_definitions[={OFF|ON}]
    System VariableNamelog_backward_compatible_user_definitions
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether to log the user_specification part of CREATE USER, ALTER USER, and GRANT statements in backward-compatible (pre-5.7.6) fashion:

    • By default, this variable is disabled. The server writes user specifications as user IDENTIFIED WITH auth_plugin AS 'hash_string'.

    • When enabled, the server writes user specifications as user IDENTIFIED BY PASSWORD 'hash_string'. Enabling this variable ensures better compatibility for cross-version replication.

    This variable was removed in MySQL 5.7.9 and replaced by log_builtin_as_identified_by_password.

  • log_bin_trust_function_creators

    Command-Line Format--log-bin-trust-function-creators
    System VariableNamelog_bin_trust_function_creators
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultFALSE

    This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 21.7, “Binary Logging of Stored Programs”.

  • log_builtin_as_identified_by_password

    Introduced5.7.9
    Command-Line Format--log_builtin_as_identified_by_password[={OFF|ON}]
    System VariableNamelog_builtin_as_identified_by_password
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable affects binary logging of user-management statements. If enabled, binary logging for CREATE USER statements involving built-in authentication plugins rewrites the statements to include an IDENTIFIED BY PASSWORD clause, and SET PASSWORD statements are logged as SET PASSWORD statements, rather than being rewritten to ALTER USER statements.

    This variable was added in MySQL 5.7.9. It replaces the log_backward_compatible_user_definitions variable.

  • log_error

    Command-Line Format--log-error[=file_name]
    System VariableNamelog_error
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The location of the error log, or stderr if the server is writing error message to the standard error output. See Section 6.4.2, “The Error Log”.

  • log_error_verbosity

    Introduced5.7.2
    Command-Line Format--log_error_verbosity=#
    System VariableNamelog_error_verbosity
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default3
    Min Value1
    Max Value3

    This variable controls verbosity of the server in writing error, warning, and note messages to the error log. The following table shows the permitted values. The default is 3.

    Verbosity ValueMessage Types Logged
    1Errors only
    2Errors and warnings
    3Errors, warnings, and notes

    log_error_verbosity was added in MySQL 5.7.2. It is preferred over, and should be used instead of, the older log_warnings system variable. See the description of log_warnings for information about how that variable relates to log_error_verbosity. In particular, assigning a value to log_warnings assigns a value to log_error_verbosity and vice versa.

  • log_output

    Command-Line Format--log-output=name
    System VariableNamelog_output
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeset
    DefaultFILE
    Valid ValuesTABLE
    FILE
    NONE

    The destination for general query log and slow query log output. The value can be a comma-separated list of one or more of the words TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files). The default value is FILE. NONE, if present, takes precedence over any other specifiers. If the value is NONE log entries are not written even if the logs are enabled. If the logs are not enabled, no logging occurs even if the value of log_output is not NONE. For more information, see Section 6.4.1, “Selecting General Query and Slow Query Log Output Destinations”.

  • log_queries_not_using_indexes

    Command-Line Format--log-queries-not-using-indexes
    System VariableNamelog_queries_not_using_indexes
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether queries that do not use indexes are logged to the slow query log. See Section 6.4.5, “The Slow Query Log”.

  • log_syslog

    Introduced5.7.5
    Command-Line Format--log_syslog[={0|1}]
    System VariableNamelog_syslog
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (Unix)Typeboolean
    DefaultOFF
    Permitted Values (Windows)Typeboolean
    DefaultON

    Whether to write error log output to syslog (on Unix and Unix-like systems) or Event Log (on Windows). The default value is platform specific:

    • On Unix and Unix-like systems, syslog output is disabled by default.

    • On Windows, Event Log output is enabled by default, which is consistent with older MySQL versions.

    Regardless of the default, log_syslog can be set explicitly to control output on any supported platform.

    syslog output control is orthogonal to sending error output to a file or (on Windows) to the console. Error output can be directed to the latter destination in addition to or instead of syslog as desired.

  • log_syslog_facility

    Introduced5.7.5
    Command-Line Format--log_syslog_facility=value
    System VariableNamelog_syslog_facility
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaultdaemon

    The facility for error log output written to syslog (what type of program is sending the message). This variable has no effect unless the log_syslog system variable is enabled.

    The permitted values can vary per operating system; consult your system syslog documentation.

    This variable does not exist on Windows.

  • log_syslog_include_pid

    Introduced5.7.5
    Command-Line Format--log_syslog_include_pid[={0|1}]
    System VariableNamelog_syslog_include_pid
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Whether to include the server process ID in each line of error log output written to syslog. This variable has no effect unless the log_syslog system variable is enabled.

    This variable does not exist on Windows.

  • log_syslog_tag

    Introduced5.7.5
    Command-Line Format--log_syslog_tag=value
    System VariableNamelog_syslog_tag
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaultempty string

    The tag to be added to the server identifier in error log output written to syslog. This variable has no effect unless the log_syslog system variable is enabled.

    By default, the server identifier is mysqld with no tag. If a tag of tag_val is specified, it is appended to the server identifier with a leading hyphen, resulting in an identifier of mysqld-tag_val.

    On Windows, to use a tag that does not already exist, the server must be run from an account with Administrator privileges, to permit creation of a registry entry for the tag. Elevated privileges are not required if the tag already exists.

  • log_timestamps

    Introduced5.7.2
    Command-Line Format--log_timestamps=#
    System VariableNamelog_timestamps
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultUTC
    Valid ValuesUTC
    SYSTEM

    This variable controls the timestamp time zone of error log messages, and of general query log and slow query log messages written to files. It does not affect the time zone of general query log and slow query log messages written to tables (mysql.general_log, mysql.slow_log). Rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.

    Permitted log_timestamps values are UTC (the default) and SYSTEM (local system time zone).

    Timestamps are written using ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value of Z signifying Zulu time (UTC) or ±hh:mm (an offset from UTC).

    This variable was added in MySQL 5.7.2. Before 5.7.2, timestamps in log messages were written using the local system time zone by default, not UTC. If you want the previous log message time zone default, set log_timestamps=SYSTEM.

  • log_throttle_queries_not_using_indexes

    System VariableNamelog_throttle_queries_not_using_indexes
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0

    If log_queries_not_using_indexes is enabled, the log_throttle_queries_not_using_indexes variable limits the number of such queries per minute that can be written to the slow query log. A value of 0 (the default) means no limit. For more information, see Section 6.4.5, “The Slow Query Log”.

  • log_slow_admin_statements

    Introduced5.7.1
    System VariableNamelog_slow_admin_statements
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Include slow administrative statements in the statements written to the slow query log. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

  • log_warnings

    Deprecated5.7.2
    Command-Line Format--log-warnings[=#]
    System VariableNamelog_warnings
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms, <= 5.7.1)Typeinteger
    Default1
    Min Value0
    Max Value4294967295
    Permitted Values (32-bit platforms, >= 5.7.2)Typeinteger
    Default2
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, <= 5.7.1)Typeinteger
    Default1
    Min Value0
    Max Value18446744073709551615
    Permitted Values (64-bit platforms, >= 5.7.2)Typeinteger
    Default2
    Min Value0
    Max Value18446744073709551615

    Whether to produce additional warning messages to the error log. This variable is enabled by default (the default is 1 before MySQL 5.7.2, 2 as of 5.7.2). To disable it, set it to 0. The server logs messages about statements that are unsafe for statement-based logging if the value is greater than 0. Aborted connections and access-denied errors for new connection attempts are logged if the value is greater than 1. See Section B.5.2.11, “Communication Errors and Aborted Connections”.

    Enabling this option by setting it greater than 0 is recommended, if you use replication, to get more information about what is happening, such as messages about network failures and reconnections. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.

    If a slave server is started with log_warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.

    As of MySQL 5.7.2, information items previously governed by log_warnings are governed by log_error_verbosity, which is preferred over, and should be used instead of, the older log_warnings system variable. (The log_warnings system variable and --log-warnings command-line option are deprecated and will be removed in a future MySQL release.)

    Assigning a value to log_warnings assigns a value to log_error_verbosity and vice versa. The variables are related as follows:

    As of MySQL 5.7.2, the default log level is controlled by log_error_verbosity, which has a default of 3. In addition, the default for log_warnings changes from 1 to 2, which corresponds to log_error_verbosity=3. To achieve a logging level similar to the previous default, set log_error_verbosity=2.

    In MySQL 5.7.2 and higher, use of log_warnings is still permitted but maps onto use of log_error_verbosity as follows:

  • long_query_time

    Command-Line Format--long_query_time=#
    System VariableNamelong_query_time
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default10
    Min Value0

    If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored. See Section 6.4.5, “The Slow Query Log”.

  • low_priority_updates

    Command-Line Format--low-priority-updates
    System VariableNamelow_priority_updates
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultFALSE

    If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

  • lower_case_file_system

    System VariableNamelower_case_file_system
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean

    This variable describes the case sensitivity of file names on the file system where the data directory is located. OFF means file names are case sensitive, ON means they are not case sensitive. This variable is read only because it reflects a file system attribute and setting it would have no effect on the file system.

  • lower_case_table_names

    Command-Line Format--lower_case_table_names[=#]
    System VariableNamelower_case_table_names
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value2

    If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 10.2.2, “Identifier Case Sensitivity”.

    On Windows the default value is 1. On OS X, the default value is 2.

    You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or OS X). It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name letter case. With MyISAM, accessing table names using different letter cases could cause index corruption.

    As of MySQL 5.7.9, an error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.

    If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

    The setting of this variable in MySQL 5.7 affects the behavior of replication filtering options with regard to case sensitivity. (Bug #51639) See Section 18.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information.

  • max_allowed_packet

    Command-Line Format--max_allowed_packet=#
    System VariableNamemax_allowed_packet
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default4194304
    Min Value1024
    Max Value1073741824

    The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB.

    The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

    You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

    When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

    The session value of this variable is read only. The client can receive up to as many bytes as the session value. However, the server will not send to the client more bytes than the current global max_allowed_packet value. (The global value could be less than the session value if the global value is changed after the client connects.)

  • max_connect_errors

    Command-Line Format--max_connect_errors=#
    System VariableNamemax_connect_errors
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default100
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default100
    Min Value1
    Max Value18446744073709551615

    If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. The default is 100.

  • max_connections

    Command-Line Format--max_connections=#
    System VariableNamemax_connections
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default151
    Min Value1
    Max Value100000

    The maximum permitted number of simultaneous client connections. By default, this is 151. See Section B.5.2.7, “Too many connections”, for more information.

    Increasing this value increases the number of file descriptors that mysqld requires. If the required number of descriptors are not available, the server reduces the value of max_connections. See Section 9.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.

    Connections refused because the max_connections limit is reached increment the Connection_errors_max_connections status variable.

  • max_delayed_threads

    Deprecated5.6.7
    Command-Line Format--max_delayed_threads=#
    System VariableNamemax_delayed_threads
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default20
    Min Value0
    Max Value16384

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • max_digest_length

    Introduced5.7.6
    Command-Line Format--max_digest_length=#
    System VariableNamemax_digest_length
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1024
    Min Value0
    Max Value1048576

    The maximum number of bytes available for computing statement digests (see Section 23.7, “Performance Schema Statement Digests”). When this amount of space is used for computing the digest for a statement, no further tokens from the parsed statement are collected or figure into the digest value. Statements differing only after that many bytes of parsed statement tokens produce the same digest and are aggregated for digest statistics.

    Decreasing the max_digest_length value reduces memory use but causes the digest value of more statements to become indistinguishable if they differ only at the end. Increasing the value permits longer statements to be distinguished but increases memory use, particularly for workloads that involve large numbers of simultaneous sessions (max_digest_length bytes are allocated per session).

    This variable was added in MySQL 5.7.6. Until 5.7.8, this variable applies to Performance Schema and to other server functions that use digests, such as query rewrite plugins. As of 5.7.8, it no longer applies to Performance Schema; instead, use performance_schema_max_digest_length.

  • max_error_count

    Command-Line Format--max_error_count=#
    System VariableNamemax_error_count
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default64
    Min Value0
    Max Value65535

    The maximum number of error, warning, and note messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements. This is the same as the number of condition areas in the diagnostics area, and thus the number of conditions that can be inspected by GET DIAGNOSTICS.

  • max_execution_time

    Introduced5.7.8
    Command-Line Format--max_execution_time=#
    System VariableNamemax_execution_time
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0

    The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.

    max_execution_time applies as follows:

    • The global max_execution_time value provides the default for the session value for new connections. The session value applies to SELECT executions executed within the session that include no MAX_EXECUTION_TIME(N) optimizer hint or for which N is 0.

    • max_execution_time applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

    • max_execution_time is ignored for SELECT statements in stored programs.

    This variable was added in MySQL 5.7.8. Previously, it was named max_statement_time.

  • max_heap_table_size

    Command-Line Format--max_heap_table_size=#
    System VariableNamemax_heap_table_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default16777216
    Min Value16384
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default16777216
    Min Value16384
    Max Value1844674407370954752

    This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

    This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. See Section 9.4.4, “Internal Temporary Table Use in MySQL”.

    max_heap_table_size is not replicated. See Section 18.4.1.23, “Replication and MEMORY Tables”, and Section 18.4.1.38, “Replication and Variables”, for more information.

  • max_insert_delayed_threads

    Deprecated5.6.7
    System VariableNamemax_insert_delayed_threads
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger

    This variable is a synonym for max_delayed_threads.

    This system variable is deprecated (because DELAYED inserts are not supported), and will be removed in a future release.

  • max_join_size

    Command-Line Format--max_join_size=#
    System VariableNamemax_join_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default18446744073709551615
    Min Value1
    Max Value18446744073709551615

    Do not permit statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks. By setting this value, you can catch statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows.

    Setting this variable to a value other than DEFAULT resets the value of sql_big_selects to 0. If you set the sql_big_selects value again, the max_join_size variable is ignored.

    If a query result is in the query cache, no result size check is performed, because the result has previously been computed and it does not burden the server to send it to the client.

  • max_length_for_sort_data

    Command-Line Format--max_length_for_sort_data=#
    System VariableNamemax_length_for_sort_data
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1024
    Min Value4
    Max Value8388608

    The cutoff on the size of index values that determines which filesort algorithm to use. See Section 9.2.1.15, “ORDER BY Optimization”.

  • max_points_in_geometry

    Introduced5.7.8
    Command-Line Format--max_points_in_geometry=integer
    System VariableNamemax_points_in_geometry
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default65536
    Min Value3
    Max Value1048576

    The maximum value of the points_per_circle argument to the ST_Buffer_Strategy() function.

  • max_prepared_stmt_count

    Command-Line Format--max_prepared_stmt_count=#
    System VariableNamemax_prepared_stmt_count
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default16382
    Min Value0
    Max Value1048576

    This variable limits the total number of prepared statements in the server. It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. The default value is 16,382. The permissible range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements.

  • max_relay_log_size

    Command-Line Format--max_relay_log_size=#
    System VariableNamemax_relay_log_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value1073741824

    If a write by a replication slave to its relay log causes the current log file size to exceed the value of this variable, the slave rotates the relay logs (closes the current file and opens the next one). If max_relay_log_size is 0, the server uses max_binlog_size for both the binary log and the relay log. If max_relay_log_size is greater than 0, it constrains the size of the relay log, which enables you to have different sizes for the two logs. You must set max_relay_log_size to between 4096 bytes and 1GB (inclusive), or to 0. The default value is 0. See Section 18.2.2, “Replication Implementation Details”.

  • max_seeks_for_key

    Command-Line Format--max_seeks_for_key=#
    System VariableNamemax_seeks_for_key
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default4294967295
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default18446744073709551615
    Min Value1
    Max Value18446744073709551615

    Limit the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required when searching for matching rows in a table by scanning an index, regardless of the actual cardinality of the index (see Section 14.7.5.22, “SHOW INDEX Syntax”). By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans.

  • max_sort_length

    Command-Line Format--max_sort_length=#
    System VariableNamemax_sort_length
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1024
    Min Value4
    Max Value8388608

    The number of bytes to use when sorting data values. The server uses only the first max_sort_length bytes of each value and ignores the rest. Consequently, values that differ only after the first max_sort_length bytes compare as equal for GROUP BY, ORDER BY, and DISTINCT operations.

    Increasing the value of max_sort_length may require increasing the value of sort_buffer_size as well. For details, see Section 9.2.1.15, “ORDER BY Optimization”

  • max_sp_recursion_depth

    Command-Line Format--max_sp_recursion_depth[=#]
    System VariableNamemax_sp_recursion_depth
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Max Value255

    The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255.

    Stored procedure recursion increases the demand on thread stack space. If you increase the value of max_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value of thread_stack at server startup.

  • max_statement_time

    Introduced5.7.4
    Removed5.7.8
    System VariableNamemax_statement_time
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0

    The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.

    max_statement_time applies as follows:

    • The global max_statement_time value provides the default for the session value for new connections. The session value applies to SELECT statements executed within the session that include no MAX_STATEMENT_TIME = N option or for which N is 0.

    • max_statement_time applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

    • max_statement_time is ignored for SELECT statements in stored programs.

    This variable was added in MySQL 5.7.4 and renamed to max_execution_time in MySQL 5.7.8.

  • max_tmp_tables

    This variable is unused. It is deprecated and will be removed in a future MySQL release.

  • max_user_connections

    Command-Line Format--max_user_connections=#
    System VariableNamemax_user_connections
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value4294967295

    The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means no limit.

    This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:

    • If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session max_user_connections value is set to that limit.

    • Otherwise, the session max_user_connections value is set to the global value.

    Account resource limits are specified using the CREATE USER or ALTER USER statement. See Section 7.3.4, “Setting Account Resource Limits”.

  • max_write_lock_count

    Command-Line Format--max_write_lock_count=#
    System VariableNamemax_write_lock_count
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default4294967295
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default18446744073709551615
    Min Value1
    Max Value18446744073709551615

    After this many write locks, permit some pending read lock requests to be processed in between.

  • mecab_rc_file

    Introduced5.7.6
    Command-Line Format--mecab_rc_file
    System VariableNamemecab_rc_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The mecab_rc_file option is used when setting up the MeCab full-text parser.

    The mecab_rc_file option defines the path to the mecabrc configuration file, which is the configuration file for MeCab. The option is read-only and can only be set at startup. The mecabrc configuration file is required to initialize MeCab.

    For information about the MeCab full-text parser, see Section 13.9.9, “MeCab Full-Text Parser Plugin”.

    For information about options that can be specified in the MeCab mecabrc configuration file, refer to the MeCab Documentation on the Google Developers site.

  • metadata_locks_cache_size

    Deprecated5.7.4
    System VariableNamemetadata_locks_cache_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1024
    Min Value1
    Max Value1048576

    The size of the metadata locks cache. The server uses this cache to avoid creation and destruction of synchronization objects. This is particularly helpful on systems where such operations are expensive, such as Windows XP.

    In MySQL 5.7.4, metadata locking implementation changes make this variable unnecessary, so it is deprecated and will be removed in a future MySQL release.

  • metadata_locks_hash_instances

    Deprecated5.7.4
    System VariableNamemetadata_locks_hash_instances
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default8
    Min Value1
    Max Value1024

    The set of metadata locks can be partitioned into separate hashes to permit connections accessing different objects to use different locking hashes and reduce contention. The metadata_locks_hash_instances system variable specifies the number of hashes (default 8).

    In MySQL 5.7.4, metadata locking implementation changes make this variable unnecessary, so it is deprecated and will be removed in a future MySQL release.

  • min_examined_row_limit

    Command-Line Format--min-examined-row-limit=#
    System VariableNamemin_examined_row_limit
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default0
    Min Value0
    Max Value18446744073709551615

    Queries that examine fewer than this number of rows are not logged to the slow query log.

  • multi_range_count

    Deprecated5.6.7
    Command-Line Format--multi_range_count=#
    System VariableNamemulti_range_count
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default256
    Min Value1
    Max Value4294967295

    This variable has no effect. It is deprecated and will be removed in a future MySQL release.

  • myisam_data_pointer_size

    Command-Line Format--myisam_data_pointer_size=#
    System VariableNamemyisam_data_pointer_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default6
    Min Value2
    Max Value7

    The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified. This variable cannot be less than 2 or larger than 7. The default value is 6. See Section B.5.2.12, “The table is full”.

  • myisam_max_sort_file_size

    Command-Line Format--myisam_max_sort_file_size=#
    System VariableNamemyisam_max_sort_file_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default2147483648
    Permitted Values (64-bit platforms)Typeinteger
    Default9223372036854775807

    The maximum size of the temporary file that MySQL is permitted to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

    If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. The space must be available in the file system containing the directory where the original index file is located.

  • myisam_mmap_size

    Command-Line Format--myisam_mmap_size=#
    System VariableNamemyisam_mmap_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (32-bit platforms)Typeinteger
    Default4294967295
    Min Value7
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default18446744073709551615
    Min Value7
    Max Value18446744073709551615

    The maximum amount of memory to use for memory mapping compressed MyISAM files. If many compressed MyISAM tables are used, the value can be decreased to reduce the likelihood of memory-swapping problems.

  • myisam_recover_options

    System VariableNamemyisam_recover_options
    Variable ScopeGlobal
    Dynamic VariableNo

    The value of the --myisam-recover-options option. See Section 6.1.3, “Server Command Options”.

  • myisam_repair_threads

    Command-Line Format--myisam_repair_threads=#
    System VariableNamemyisam_repair_threads
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default1
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default1
    Min Value1
    Max Value18446744073709551615

    If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1.

    Note

    Multi-threaded repair is still beta-quality code.

  • myisam_sort_buffer_size

    Command-Line Format--myisam_sort_buffer_size=#
    System VariableNamemyisam_sort_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (Windows, 32-bit platforms)Typeinteger
    Default8388608
    Min Value4096
    Max Value4294967295
    Permitted Values (Windows, 64-bit platforms)Typeinteger
    Default8388608
    Min Value4096
    Max Value18446744073709551615
    Permitted Values (Other, 32-bit platforms)Typeinteger
    Default8388608
    Min Value4096
    Max Value4294967295
    Permitted Values (Other, 64-bit platforms)Typeinteger
    Default8388608
    Min Value4096
    Max Value18446744073709551615

    The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

    The maximum permissible setting for myisam_sort_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms.

  • myisam_stats_method

    Command-Line Format--myisam_stats_method=name
    System VariableNamemyisam_stats_method
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Defaultnulls_unequal
    Valid Valuesnulls_equal
    nulls_unequal
    nulls_ignored

    How the server treats NULL values when collecting statistics about the distribution of index values for MyISAM tables. This variable has three possible values, nulls_equal, nulls_unequal, and nulls_ignored. For nulls_equal, all NULL index values are considered equal and form a single value group that has a size equal to the number of NULL values. For nulls_unequal, NULL values are considered unequal, and each NULL forms a distinct value group of size 1. For nulls_ignored, NULL values are ignored.

    The method that is used for generating table statistics influences how the optimizer chooses indexes for query execution, as described in Section 9.3.7, “InnoDB and MyISAM Index Statistics Collection”.

  • myisam_use_mmap

    Command-Line Format--myisam_use_mmap
    System VariableNamemyisam_use_mmap
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Use memory mapping for reading and writing MyISAM tables.

  • mysql_native_password_proxy_users

    Introduced5.7.7
    Command-Line Format--mysql_native_password_proxy_users=[={OFF|ON}]
    System VariableNamemysql_native_password_proxy_users
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable controls whether the mysql_native_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled. For information about user proxying, see Section 7.3.9, “Proxy Users”.

    This variable was added in MySQL 5.7.7. Before 5.7.7, mysql_native_password does not support proxy users.

  • named_pipe

    System VariableNamenamed_pipe
    Variable ScopeGlobal
    Dynamic VariableNo
    Platform SpecificWindows
    Permitted Values (Windows)Typeboolean
    DefaultOFF

    (Windows only.) Indicates whether the server supports connections over named pipes.

  • net_buffer_length

    Command-Line Format--net_buffer_length=#
    System VariableNamenet_buffer_length
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default16384
    Min Value1024
    Max Value1048576

    Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.

    This variable should not normally be changed, but if you have very little memory, you can set it to the expected length of statements sent by clients. If statements exceed this length, the connection buffer is automatically enlarged. The maximum value to which net_buffer_length can be set is 1MB.

    The session value of this variable is read only.

  • net_read_timeout

    Command-Line Format--net_read_timeout=#
    System VariableNamenet_read_timeout
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default30
    Min Value1

    The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.

  • net_retry_count

    Command-Line Format--net_retry_count=#
    System VariableNamenet_retry_count
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default10
    Min Value1
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default10
    Min Value1
    Max Value18446744073709551615

    If a read or write on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.

  • net_write_timeout

    Command-Line Format--net_write_timeout=#
    System VariableNamenet_write_timeout
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default60
    Min Value1

    The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

  • new

    Command-Line Format--new
    System VariableNamenew
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Disabled byskip-new
    Permitted ValuesTypeboolean
    DefaultFALSE

    This variable was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility. Its value is always OFF.

    In MySQL Cluster, setting this variable to ON makes it possible to employ partitioning types other than KEY or LINEAR KEY with NDB tables. This feature is experimental only, and not supported in production. For additional information, see User-defined partitioning and the NDB storage engine (MySQL Cluster).

  • ngram_token_size

    Introduced5.7.6
    Command-Line Format--ngram_token_size
    System VariableNamengram_token_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default2
    Min Value1
    Max Value10

    Defines the n-gram token size for the n-gram full-text parser. The ngram_token_size option is read-only and can only be modified at startup. The default value is 2 (bigram). The maximum value is 10.

    For more information about how to configure this variable, see Section 13.9.8, “ngram Full-Text Parser”.

  • offline_mode

    Introduced5.7.5
    Command-Line Format--offline_mode=val
    System VariableNameoffline_mode
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether the server is in offline mode, which has these characteristics:

    • Connected client users who do not have the SUPER privilege are disconnected on the next request, with an appropriate error. Disconnection includes terminating running statements and releasing locks. Such clients also cannot initiate new connections, and receive an appropriate error.

    • Connected client users who have the SUPER privilege are not disconnected, and can initiate new connections to manage the server.

    • Replication slave threads are permitted to keep applying data to the server.

    Only users who have the SUPER privilege can control offline mode. To put a server in offline mode, change the value of the offline_mode system variable from OFF to ON. To resume normal operations, change offline_mode from ON to OFF. In offline mode, clients that are refused access receive an ER_SERVER_OFFLINE_MODE error.

  • old

    Command-Line Format--old
    System VariableNameold
    Variable ScopeGlobal
    Dynamic VariableNo

    old is a compatibility variable. It is disabled by default, but can be enabled at startup to revert the server to behaviors present in older versions.

    When old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17. That is, index hints with no FOR clause apply only to how indexes are used for row retrieval and not to resolution of ORDER BY or GROUP BY clauses. (See Section 9.9.4, “Index Hints”.) Take care about enabling this in a replication setup. With statement-based binary logging, having different modes for the master and slaves might lead to replication errors.

  • old_alter_table

    Command-Line Format--old-alter-table
    System VariableNameold_alter_table
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When this variable is enabled, the server does not use the optimized method of processing an ALTER TABLE operation. It reverts to using a temporary table, copying over the data, and then renaming the temporary table to the original, as used by MySQL 5.0 and earlier. For more information on the operation of ALTER TABLE, see Section 14.1.8, “ALTER TABLE Syntax”.

  • old_passwords

    Deprecated5.7.6
    System VariableNameold_passwords
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (<= 5.7.4)Typeenumeration
    Default0
    Valid Values0
    1
    2
    Permitted Values (>= 5.7.5)Typeenumeration
    Default0
    Valid Values0
    2
    Note

    This system variable is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

    This variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause.

    The following table shows the permitted values of old_passwords, the password hashing method for each value, and which authentication plugins use passwords hashed with each method.

    ValuePassword Hashing MethodAssociated Authentication Plugin
    0MySQL 4.1 native hashingmysql_native_password
    1Pre-4.1 (old) hashingmysql_old_password
    2SHA-256 hashingsha256_password
    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5. Consequently, old_passwords=1, which causes PASSWORD() to generate pre-4.1 password hashes, is not permitted as of 5.7.5. For account upgrade instructions, see Section 7.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

    If old_passwords=1, PASSWORD(str) returns the same value as OLD_PASSWORD(str). The latter function is not affected by the value of old_passwords.

    If you set old_passwords=2, follow the instructions for using the sha256_password plugin at Section 7.5.1.4, “The SHA-256 Authentication Plugin”.

    The server sets the global old_passwords value during startup to be consistent with the password hashing method required by the default authentication plugin. The default plugin is mysql_native_password unless the default_authentication_plugin system variable is set otherwise.

    As of MySQL 5.7.1, when a client successfully connects to the server, the server sets the session old_passwords value appropriately for the account authentication method. For example, if the account uses the sha256_password authentication plugin, the server sets old_passwords=2.

    For additional information about authentication plugins and hashing formats, see Section 7.3.8, “Pluggable Authentication”, and Section 7.1.2.4, “Password Hashing in MySQL”.

  • open_files_limit

    Command-Line Format--open-files-limit=#
    System VariableNameopen_files_limit
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default5000, with possible adjustment
    Min Value0
    Max Valueplatform dependent

    The number of files that the operating system permits mysqld to open. The value of this variable at runtime is the real value permitted by the system and might be different from the value you specify at server startup. The value is 0 on systems where MySQL cannot change the number of open files.

    The effective open_files_limit value is based on the value specified at system startup (if any) and the values of max_connections and table_open_cache, using these formulas:

    1) 10 + max_connections + (table_open_cache * 2)
    2) max_connections * 5
    3) open_files_limit value specified at startup, 5000 if none
    

    The server attempts to obtain the number of file descriptors using the maximum of those three values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit.

  • optimizer_prune_level

    Command-Line Format--optimizer_prune_level[=#]
    System VariableNameoptimizer_prune_level
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default1

    Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.

  • optimizer_search_depth

    Command-Line Format--optimizer_search_depth[=#]
    System VariableNameoptimizer_search_depth
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default62
    Min Value0
    Max Value62

    The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value.

  • optimizer_switch

    Command-Line Format--optimizer_switch=value
    System VariableNameoptimizer_switch
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (<= 5.7.4)Typeset
    Valid Valuesbatched_key_access={on|off}
    block_nested_loop={on|off}
    engine_condition_pushdown={on|off}
    firstmatch={on|off}
    index_condition_pushdown={on|off}
    index_merge={on|off}
    index_merge_intersection={on|off}
    index_merge_sort_union={on|off}
    index_merge_union={on|off}
    loosescan={on|off}
    materialization={on|off}
    mrr={on|off}
    mrr_cost_based={on|off}
    semijoin={on|off}
    subquery_materialization_cost_based={on|off}
    use_index_extensions={on|off}
    Permitted Values (5.7.5)Typeset
    Valid Valuesbatched_key_access={on|off}
    block_nested_loop={on|off}
    condition_fanout_filter={on|off}
    engine_condition_pushdown={on|off}
    firstmatch={on|off}
    index_condition_pushdown={on|off}
    index_merge={on|off}
    index_merge_intersection={on|off}
    index_merge_sort_union={on|off}
    index_merge_union={on|off}
    loosescan={on|off}
    materialization={on|off}
    mrr={on|off}
    mrr_cost_based={on|off}
    semijoin={on|off}
    subquery_materialization_cost_based={on|off}
    use_index_extensions={on|off}
    Permitted Values (>= 5.7.6, <= 5.7.7)Typeset
    Valid Valuesbatched_key_access={on|off}
    block_nested_loop={on|off}
    condition_fanout_filter={on|off}
    derived_merge={on|off}
    engine_condition_pushdown={on|off}
    firstmatch={on|off}
    index_condition_pushdown={on|off}
    index_merge={on|off}
    index_merge_intersection={on|off}
    index_merge_sort_union={on|off}
    index_merge_union={on|off}
    loosescan={on|off}
    materialization={on|off}
    mrr={on|off}
    mrr_cost_based={on|off}
    semijoin={on|off}
    subquery_materialization_cost_based={on|off}
    use_index_extensions={on|off}
    Permitted Values (>= 5.7.8)Typeset
    Valid Valuesbatched_key_access={on|off}
    block_nested_loop={on|off}
    condition_fanout_filter={on|off}
    derived_merge={on|off}
    duplicateweedout={on|off}
    engine_condition_pushdown={on|off}
    firstmatch={on|off}
    index_condition_pushdown={on|off}
    index_merge={on|off}
    index_merge_intersection={on|off}
    index_merge_sort_union={on|off}
    index_merge_union={on|off}
    loosescan={on|off}
    materialization={on|off}
    mrr={on|off}
    mrr_cost_based={on|off}
    semijoin={on|off}
    subquery_materialization_cost_based={on|off}
    use_index_extensions={on|off}

    The optimizer_switch system variable enables control over optimizer behavior. The value of this variable is a set of flags, each of which has a value of on or off to indicate whether the corresponding optimizer behavior is enabled or disabled. This variable has global and session values and can be changed at runtime. The global default can be set at server startup.

    To see the current set of optimizer flags, select the variable value:

    mysql> SELECT @@optimizer_switch\G
    *************************** 1. row ***************************
    @@optimizer_switch: index_merge=on,index_merge_union=on,
                        index_merge_sort_union=on,
                        index_merge_intersection=on,
                        engine_condition_pushdown=on,
                        index_condition_pushdown=on,
                        mrr=on,mrr_cost_based=on,
                        block_nested_loop=on,batched_key_access=off,
                        materialization=on,semijoin=on,loosescan=on,
                        firstmatch=on,duplicateweedout=on,
                        subquery_materialization_cost_based=on,
                        use_index_extensions=on,
                        condition_fanout_filter=on,derived_merge=on
    

    For more information about the syntax of this variable and the optimizer behaviors that it controls, see Section 9.9.2, “Controlling Switchable Optimizations”.

  • optimizer_trace

    System VariableNameoptimizer_trace
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    This variable controls optimizer tracing. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_features

    System VariableNameoptimizer_trace_features
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    This variable enables or disables selected optimizer tracing features. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_limit

    System VariableNameoptimizer_trace_limit
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1

    The maximum number of optimizer traces to display. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_max_mem_size

    System VariableNameoptimizer_trace_max_mem_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default16384

    The maximum cumulative size of stored optimizer traces. For details, see MySQL Internals: Tracing the Optimizer.

  • optimizer_trace_offset

    System VariableNameoptimizer_trace_offset
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default-1

    The offset of optimizer traces to display. For details, see MySQL Internals: Tracing the Optimizer.

  • performance_schema_xxx

    Performance Schema system variables are listed in Section 23.12, “Performance Schema System Variables”. These variables may be used to configure Performance Schema operation.

  • parser_max_mem_size

    Introduced5.7.12
    Command-Line Format--parser_max_mem_size=N
    System VariableNameparser_max_mem_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default4294967295
    Min Value400000
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default18446744073709551615
    Min Value400000
    Max Value18446744073709551615

    The maximum amount of memory available to the parser. The default value places no limit on memory available. The value can be reduced to protect against out-of-memory situations caused by parsing long or complex SQL statements.

  • pid_file

    Command-Line Format--pid-file=file_name
    System VariableNamepid_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The path name of the process ID (PID) file. This variable can be set with the --pid-file option.

  • plugin_dir

    Command-Line Format--plugin_dir=dir_name
    System VariableNameplugin_dir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name
    DefaultBASEDIR/lib/plugin

    The path name of the plugin directory.

    If the plugin directory is writable by the server, it may be possible for a user to write executable code to a file in the directory using SELECT ... INTO DUMPFILE. This can be prevented by making plugin_dir read only to the server or by setting --secure-file-priv to a directory where SELECT writes can be made safely.

  • port

    Command-Line Format--port=#
    System VariableNameport
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default3306
    Min Value0
    Max Value65535

    The number of the port on which the server listens for TCP/IP connections. This variable can be set with the --port option.

  • preload_buffer_size

    Command-Line Format--preload_buffer_size=#
    System VariableNamepreload_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default32768
    Min Value1024
    Max Value1073741824

    The size of the buffer that is allocated when preloading indexes.

  • profiling

    If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement profiling is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to profiling information. See Section 14.7.5.31, “SHOW PROFILES Syntax”.

    This variable is deprecated and will be removed in a future MySQL release.

  • profiling_history_size

    The number of statements for which to maintain profiling information if profiling is enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively disables profiling. See Section 14.7.5.31, “SHOW PROFILES Syntax”.

    This variable is deprecated and will be removed in a future MySQL release.

  • protocol_version

    System VariableNameprotocol_version
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger

    The version of the client/server protocol used by the MySQL server.

  • proxy_user

    System VariableNameproxy_user
    Variable ScopeSession
    Dynamic VariableNo
    Permitted ValuesTypestring

    If the current client is a proxy for another user, this variable is the proxy user account name. Otherwise, this variable is NULL. See Section 7.3.9, “Proxy Users”.

  • pseudo_slave_mode

    System VariableNamepseudo_slave_mode
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeinteger

    This variable is for internal server use.

  • pseudo_thread_id

    System VariableNamepseudo_thread_id
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeinteger

    This variable is for internal server use.

  • query_alloc_block_size

    Command-Line Format--query_alloc_block_size=#
    System VariableNamequery_alloc_block_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (>= 5.7.9)Typeinteger
    Default8192
    Min Value1024
    Max Value4294967295
    Block Size1024
    Permitted Values (32-bit platforms, <= 5.7.8)Typeinteger
    Default8192
    Min Value1024
    Max Value4294967295
    Block Size1024
    Permitted Values (64-bit platforms, <= 5.7.8)Typeinteger
    Default8192
    Min Value1024
    Max Value18446744073709551615
    Block Size1024

    The allocation size of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this parameter.

  • query_cache_limit

    Command-Line Format--query_cache_limit=#
    System VariableNamequery_cache_limit
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default1048576
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default1048576
    Min Value0
    Max Value18446744073709551615

    Do not cache results that are larger than this number of bytes. The default value is 1MB.

  • query_cache_min_res_unit

    Command-Line Format--query_cache_min_res_unit=#
    System VariableNamequery_cache_min_res_unit
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default4096
    Min Value512
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default4096
    Min Value512
    Max Value18446744073709551615

    The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB). Tuning information for this variable is given in Section 9.10.3.3, “Query Cache Configuration”.

  • query_cache_size

    Command-Line Format--query_cache_size=#
    System VariableNamequery_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default1048576
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default1048576
    Min Value0
    Max Value18446744073709551615

    The amount of memory allocated for caching query results. By default, the query cache is disabled. This is achieved using a default value of 1M, with a default for query_cache_type of 0. (To reduce overhead significantly if you set the size to 0, you should also start the server with query_cache_type=0.

    The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. query_cache_size bytes of memory are allocated even if query_cache_type is set to 0. See Section 9.10.3.3, “Query Cache Configuration”, for more information.

    The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur, as described in Section 9.10.3.3, “Query Cache Configuration”.

  • query_cache_type

    Command-Line Format--query_cache_type=#
    System VariableNamequery_cache_type
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Default0
    Valid Values0
    1
    2

    Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache. Possible values are shown in the following table.

    OptionDescription
    0 or OFFDo not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
    1 or ONCache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
    2 or DEMANDCache results only for cacheable queries that begin with SELECT SQL_CACHE.

    This variable defaults to OFF.

    If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

  • query_cache_wlock_invalidate

    Command-Line Format--query_cache_wlock_invalidate
    System VariableNamequery_cache_wlock_invalidate
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultFALSE

    Normally, when one client acquires a WRITE lock on a MyISAM table, other clients are not blocked from issuing statements that read from the table if the query results are present in the query cache. Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table. This forces other clients that attempt to access the table to wait while the lock is in effect.

  • query_prealloc_size

    Command-Line Format--query_prealloc_size=#
    System VariableNamequery_prealloc_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default8192
    Min Value8192
    Max Value4294967295
    Block Size1024
    Permitted Values (64-bit platforms)Typeinteger
    Default8192
    Min Value8192
    Max Value18446744073709551615
    Block Size1024

    The size of the persistent buffer used for statement parsing and execution. This buffer is not freed between statements. If you are running complex queries, a larger query_prealloc_size value might be helpful in improving performance, because it can reduce the need for the server to perform memory allocation during query execution operations.

  • rand_seed1

    The rand_seed1 and rand_seed2 variables exist as session variables only, and can be set but not read. The variables—but not their values—are shown in the output of SHOW VARIABLES.

    The purpose of these variables is to support replication of the RAND() function. For statements that invoke RAND(), the master passes two values to the slave, where they are used to seed the random number generator. The slave uses these values to set the session variables rand_seed1 and rand_seed2 so that RAND() on the slave generates the same value as on the master.

  • rand_seed2

    See the description for rand_seed1.

  • range_alloc_block_size

    Command-Line Format--range_alloc_block_size=#
    System VariableNamerange_alloc_block_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (>= 5.7.9)Typeinteger
    Default4096
    Min Value4096
    Max Value4294967295
    Block Size1024
    Permitted Values (32-bit platforms, <= 5.7.8)Typeinteger
    Default4096
    Min Value4096
    Max Value4294967295
    Block Size1024
    Permitted Values (64-bit platforms, <= 5.7.8)Typeinteger
    Default4096
    Min Value4096
    Max Value18446744073709551615
    Block Size1024
    Permitted Values (64-bit platforms, >= 5.7.8)Typeinteger
    Default4096
    Min Value4096
    Max Value18446744073709547520
    Block Size1024

    The size of blocks that are allocated when doing range optimization.

  • range_optimizer_max_mem_size

    Introduced5.7.9
    Command-Line Format--range_optimizer_max_mem_size=N
    System VariableNamerange_optimizer_max_mem_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (<= 5.7.11)Typeinteger
    Default1536000
    Permitted Values (>= 5.7.12)Typeinteger
    Default8388608

    The limit on memory consumption for the range optimizer. A value of 0 means no limit. If an execution plan considered by the optimizer uses the range access method but the optimizer estimates that the amount of memory needed for this method would exceed the limit, it abandons the plan and considers other plans. For more information, see Section 9.2.1.3.4, “Limiting Memory Use for Range Optimization”.

  • rbr_exec_mode

    Introduced5.7.1
    System VariableNamerbr_exec_mode
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultSTRICT
    Valid ValuesIDEMPOTENT
    STRICT

    This variable switches the server between IDEMPOTENT mode and STRICT mode. IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors. This mode is useful when replaying a row-based binary log on a server that causes conflicts with existing data. mysqlbinlog uses this mode when you set the --idempotent option by writing the following to the output:

    SET SESSION RBR_EXEC_MODE=IDEMPOTENT;
  • read_buffer_size

    Command-Line Format--read_buffer_size=#
    System VariableNameread_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default131072
    Min Value8200
    Max Value2147479552

    Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.

    This option is also used in the following context for all storage engines:

    • For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.

    • For bulk insert into partitions.

    • For caching results of nested queries.

    and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.

    The maximum permissible setting for read_buffer_size is 2GB.

    For more information about memory use during different operations, see Section 9.12.5.1, “How MySQL Uses Memory”.

  • read_only

    Command-Line Format--read_only
    System VariableNameread_only
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When the read_only system variable is enabled, the server permits no client updates except from users who have the SUPER privilege. This variable is disabled by default.

    As of MySQL 5.7.8, the server also supports a super_read_only system variable (disabled by default), which has these effects:

    Even with read_only enabled, the server permits these operations:

    Changes to read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.

    The following conditions apply to attempts to enable read_only (including implicit attempts resulting from enabling super_read_only):

    • The attempt fails and an error occurs if you have any explicit locks (acquired with LOCK TABLES) or have a pending transaction.

    • The attempt blocks while other clients hold explicit table locks or have pending transactions, until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set.

    • The attempt blocks if there are active transactions that hold metadata locks, until those transactions end.

    • read_only can be enabled while you hold a global read lock (acquired with FLUSH TABLES WITH READ LOCK) because that does not involve table locks.

  • read_rnd_buffer_size

    Command-Line Format--read_rnd_buffer_size=#
    System VariableNameread_rnd_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default262144
    Min Value1
    Max Value2147483647

    This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.

    When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. See Section 9.2.1.15, “ORDER BY Optimization”. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

    The maximum permissible setting for read_rnd_buffer_size is 2GB.

    For more information about memory use during different operations, see Section 9.12.5.1, “How MySQL Uses Memory”. For information about Multi-Range Read optimization, see Section 9.2.1.13, “Multi-Range Read Optimization”.

  • relay_log_purge

    Command-Line Format--relay_log_purge
    System VariableNamerelay_log_purge
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultTRUE

    Disables or enables automatic purging of relay log files as soon as they are not needed any more. The default value is 1 (ON).

  • relay_log_space_limit

    Command-Line Format--relay_log_space_limit=#
    System VariableNamerelay_log_space_limit
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (32-bit platforms)Typeinteger
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default0
    Min Value0
    Max Value18446744073709551615

    The maximum amount of space to use for all relay logs.

  • report_host

    Command-Line Format--report-host=host_name
    System VariableNamereport_host
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The value of the --report-host option.

  • report_password

    Command-Line Format--report-password=name
    System VariableNamereport_password
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The value of the --report-password option. Not the same as the password used for the MySQL replication user account.

  • report_port

    Command-Line Format--report-port=#
    System VariableNamereport_port
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default[slave_port]
    Min Value0
    Max Value65535

    The value of the --report-port option.

  • report_user

    Command-Line Format--report-user=name
    System VariableNamereport_user
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The value of the --report-user option. Not the same as the name for the MySQL replication user account.

  • require_secure_transport

    Introduced5.7.8
    Command-Line Format--require_secure_transport[={OFF|ON}]
    System VariableNamerequire_secure_transport
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether client connections to the server are required to use some form of secure transport. When this variable is enabled, the server permits only TCP/IP connections that use SSL, or connections that use a socket file (on Unix) or shared memory (on Windows). The server rejects nonsecure connection attempts, which fail with an ER_SECURE_TRANSPORT_REQUIRED error.

    This capability supplements per-account SSL requirements, which take precedence. For exmaple, if an account is defined with REQUIRE SSL, enabling require_secure_transport does not make it possible to use the account to connect using a Unix socket file.

    It is possible for a server to have no secure transports available. For example, a server on Windows supports no secure transports if started without specifying any SSL certificate or key files and with the shared_memory system variable disabled. Under these conditions, attempts to enable require_secure_transport at startup cause the server to write a message to the error log and exit. Attempts to enable the variable at runtime fail with an ER_NO_SECURE_TRANSPORTS_CONFIGURED error.

  • rpl_semi_sync_master_enabled

    System VariableNamerpl_semi_sync_master_enabled
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Controls whether semisynchronous replication is enabled on the master. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. The default is OFF.

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_timeout

    System VariableNamerpl_semi_sync_master_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default10000

    A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_trace_level

    System VariableNamerpl_semi_sync_master_trace_level
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default32

    The semisynchronous replication debug trace level on the master. Four levels are defined:

    • 1 = general level (for example, time function failures)

    • 16 = detail level (more verbose information)

    • 32 = net wait level (more information about network waits)

    • 64 = function level (information about function entry and exit)

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_wait_for_slave_count

    Introduced5.7.3
    System VariableNamerpl_semi_sync_master_wait_for_slave_count
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1
    Min Value1
    Max Value65535

    The number of slave acknowledgments the master must receive per transaction before proceeding. By default rpl_semi_sync_master_wait_for_slave_count is 1, meaning that semisynchronous replication proceeds after receiving a single slave acknowledgment. Performance is best for small values of this variable.

    For example, if rpl_semi_sync_master_wait_for_slave_count is 2, then 2 slaves must acknowledge receipt of the transaction before the timeout period configured by rpl_semi_sync_master_timeout for semisynchronous replication to proceed. If less slaves acknowledge receipt of the transaction during the timeout period, the master reverts to normal replication.

    Note

    This behavior also depends on rpl_semi_sync_master_wait_no_slave

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_wait_no_slave

    System VariableNamerpl_semi_sync_master_wait_no_slave
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Controls whether the master waits for the timeout period configured by rpl_semi_sync_master_timeout to expire, even if the slave count drops to less than the number of slaves configured by rpl_semi_sync_master_wait_for_slave_count during the timeout period.

    When the value of rpl_semi_sync_master_wait_no_slave is ON (the default), it is permissible for the slave count to drop to less than rpl_semi_sync_master_wait_for_slave_count during the timeout period. As long as enough slaves acknowledge the transaction before the timeout period expires, semisynchronous replication continues.

    When the value of rpl_semi_sync_master_wait_no_slave is OFF, if the slave count drops to less than the number configured in rpl_semi_sync_master_wait_for_slave_count at any time during the timeout period configured by rpl_semi_sync_master_timeout, the master reverts to normal replication.

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_wait_point

    Introduced5.7.2
    System VariableNamerpl_semi_sync_master_wait_point
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultAFTER_SYNC
    Valid ValuesAFTER_SYNC
    AFTER_COMMIT

    This variable controls the point at which a semisynchronous replication master waits for slave acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

    • AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

    • AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

    The replication characteristics of these settings differ as follows:

    • With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.

      In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date.

    • With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.

      If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.

    This variable is available only if the master-side semisynchronous replication plugin is installed.

    rpl_semi_sync_master_wait_point was added in MySQL 5.7.2. For older versions, semisynchronous master behavior is equivalent to a setting of AFTER_COMMIT.

    This change introduces a version compatibility constraint because it increments the semisynchronous interface version: Servers for MySQL 5.7.2 and up do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7.2 and up.

  • rpl_semi_sync_slave_enabled

    System VariableNamerpl_semi_sync_slave_enabled
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Controls whether semisynchronous replication is enabled on the slave. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. The default is OFF.

    This variable is available only if the slave-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_slave_trace_level

    System VariableNamerpl_semi_sync_slave_trace_level
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default32

    The semisynchronous replication debug trace level on the slave. See rpl_semi_sync_master_trace_level for the permissible values.

    This variable is available only if the slave-side semisynchronous replication plugin is installed.

  • secure_auth

    Deprecated5.7.5
    Command-Line Format--secure-auth
    System VariableNamesecure_auth
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.4)Typeboolean
    DefaultON
    Valid ValuesOFF
    ON
    Permitted Values (>= 5.7.5)Typeboolean
    DefaultON
    Valid ValuesON

    If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format.

    Enable this variable to prevent all use of passwords employing the old format (and hence insecure communication over the network).

    As of MySQL 5.7.5, this variable is deprecated and will be removed in a future MySQL release. It is always enabled and attempting to disable it produces an error. Before MySQL 5.7.5, this variable is enabled by default but can be disabled.

    Server startup fails with an error if this variable is enabled and the privilege tables are in pre-4.1 format. See Section B.5.2.4, “Client does not support authentication protocol”.

    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5. For account upgrade instructions, see Section 7.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

  • secure_file_priv

    Command-Line Format--secure-file-priv=dir_name
    System VariableNamesecure_file_priv
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.5)Typestring
    Defaultempty
    Valid Valuesempty
    dirname
    Permitted Values (>= 5.7.6)Typestring
    Defaultplatform specific
    Valid Valuesempty
    dirname
    NULL

    This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

    secure_file_priv may be set as follows:

    • If empty, the variable has no effect.

    • If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.

    • If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.

    Before MySQL 5.7.6, this variable is empty by default. As of 5.7.6, the default value is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table. To specify the default secure_file_priv value explicitly if you are building from source, use the INSTALL_SECURE_FILE_PRIVDIR CMake option.

    INSTALL_LAYOUT ValueDefault secure_file_priv Value
    STANDALONE, WINempty
    DEB, RPM, SLES, SVR4/var/lib/mysql-files
    Otherwisemysql-files under the CMAKE_INSTALL_PREFIX value

    As of MySQL 5.7.8, to set the default secure_file_priv value for the libmysqld embedded server, use the INSTALL_SECURE_FILE_PRIV_EMBEDDEDDIR CMake option. The default value for this option is NULL.

    As of MySQL 5.7.6, the server checks the value of secure_file_priv at startup and writes a warning to the error log if the value is insecure. The setting is considered insecure if secure_file_priv has an empty value, or the value is the data directory or a subdirectory of it, or a directory that is accessible by all users. If secure_file_priv is set to a nonexistent path, the server writes an error message to the error log and exits.

  • server_id

    Command-Line Format--server-id=#
    System VariableNameserver_id
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value4294967295

    The server ID, used in replication to give each master and slave a unique identity. This variable is set by the --server-id option. For each server participating in replication, you should pick a positive integer in the range from 1 to 232 − 1 to act as that server's ID.

  • session_track_gtids

    Introduced5.7.6
    Command-Line Format--session_track_gtids=[value]
    System VariableNamesession_track_gtids
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultOFF
    Valid ValuesOFF
    OWN_GTID
    ALL_GTIDS

    Controls a tracker for capturing GTIDs and returning them in the OK packet. Depending on the value of this option, at the end of executing a transaction, the GTIDs specified are captured by the tracker and appended to the OK packet. The possible sets of GTIDs to track are:

    • OFF means that no GTIDs are included in the OK packet. This is the same behavior as versions of MySQL prior to 5.7.6.

    • OWN_GTID configures the tracker to collect GTIDs generated by successfully committed read/write transactions.

    • ALL_GTIDS configures the tracker to collect all of the GTIDs in gtid_executed at the time the current transaction commits, regardless of whether the transaction is read/write or read-only.

    For information about obtaining session state-change information within client programs, see Section 25.8.7.65, “mysql_session_track_get_first()”.

  • session_track_schema

    Introduced5.7.4
    Command-Line Format--session_track_schema=#
    System VariableNamesession_track_schema
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Controls whether the server tracks changes to the default schema (database) name within the current session and makes this information available to the client when changes occur.

    If notification is enabled, any setting of the default schema is reported, even if the new schema name is the same as the old.

    For information about obtaining session state-change information within client programs, see Section 25.8.7.65, “mysql_session_track_get_first()”.

  • session_track_state_change

    Introduced5.7.4
    Command-Line Format--session_track_state_change=#
    System VariableNamesession_track_state_change
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Controls whether the server tracks changes to the state of the current session and notifies the client when state changes occur. Session state consists of these values:

    • The default schema (database)

    • Session-specific values for system variables

    • User-defined variables

    • Temporary tables

    • Prepared statements

    If the session-state tracker is enabled, any assignments to session state values are reported, even if the new values are the same as the old.

    The session_track_state_change variable controls only notification of when changes occur, not what the changes are. To receive notification for changes to the default schema name and session system variable values, use the session_track_schema and session_track_system_variables system variables.

    For information about obtaining session state-change information within client programs, see Section 25.8.7.65, “mysql_session_track_get_first()”.

  • session_track_system_variables

    Introduced5.7.4
    Command-Line Format--session_track_system_variables=#
    System VariableNamesession_track_system_variables
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    Defaulttime_zone, autocommit, character_set_client, character_set_results, character_set_connection

    Controls whether the server tracks changes to the session system variables and makes this information available to the client when changes occur. The variable value is a comma-separated list of variables for which to track changes. By default, notification is enabled for time_zone, autocommit, character_set_client, character_set_results, and character_set_connection. (The latter three variables are those affected by SET NAMES.)

    The special value * causes the server to track changes to all session variables. If given, this value must be specified by itself without specific system variable names.

    Notification occurs for all assignments to tracked session system variables, even if the new values are the same as the old.

    For information about obtaining session state-change information within client programs, see Section 25.8.7.65, “mysql_session_track_get_first()”.

  • sha256_password_auto_generate_rsa_keys

    Introduced5.7.5
    Command-Line Format--sha256_password_auto_generate_rsa_keys[={OFF|ON}]
    System VariableNamesha256_password_auto_generate_rsa_keys
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    This variable is available if the server was compiled using OpenSSL (see Section 7.4.1, “OpenSSL Versus yaSSL”). It controls whether the server autogenerates RSA private/public key-pair files in the data directory, if they do not already exist.

    At startup, the server automatically generates RSA private/public key-pair files in the data directory if the sha256_password_auto_generate_rsa_keys system variable is enabled, no RSA options are specified, and the RSA files are missing from the data directory. These files enable secure password exchange using RSA over unencrypted connections for accounts authenticated by the sha256_password plugin; see Section 7.5.1.4, “The SHA-256 Authentication Plugin”.

    For more information about RSA file autogeneration, including file names and characteristics, see Section 7.4.6.1, “Creating SSL and RSA Certificates and Keys using MySQL”

    The auto_generate_certs system variable is related but controls autogeneration of SSL certificate and key files needed for secure connections using SSL.

  • sha256_password_private_key_path

    System VariableNamesha256_password_private_key_path
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultprivate_key.pem

    This variable is available if MySQL was compiled using OpenSSL (see Section 7.4.1, “OpenSSL Versus yaSSL”). Its value is the path name of the RSA private key file for the sha256_password authentication plugin. If the file is named as a relative path, it is interpreted relative to the server data directory. The file must be in PEM format. Because this file stores a private key, its access mode should be restricted so that only the MySQL server can read it.

    For information about sha256_password, including instructions for creating the RSA key files, see Section 7.5.1.4, “The SHA-256 Authentication Plugin”.

  • sha256_password_proxy_users

    Introduced5.7.7
    Command-Line Format--sha256_password_proxy_users=[={OFF|ON}]
    System VariableNamesha256_password_proxy_users
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable controls whether the sha256_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled. For information about user proxying, see Section 7.3.9, “Proxy Users”.

    This variable was added in MySQL 5.7.7. Before 5.7.7, sha256_password does not support proxy users.

  • sha256_password_public_key_path

    System VariableNamesha256_password_public_key_path
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultpublic_key.pem

    This variable is available if MySQL was compiled using OpenSSL (see Section 7.4.1, “OpenSSL Versus yaSSL”). Its value is the path name of the RSA public key file for the sha256_password authentication plugin. If the file is named as a relative path, it is interpreted relative to the server data directory. The file must be in PEM format. Because this file stores a public key, copies can be freely distributed to client users. (Clients that explicitly specify a public key when connecting to the server using RSA password encryption must use the same public key as that used by the server.)

    For information about sha256_password, including instructions for creating the RSA key files and how clients specify the RSA public key, see Section 7.5.1.4, “The SHA-256 Authentication Plugin”.

  • shared_memory

    Command-Line Format--shared_memory[={0,1}]
    System VariableNameshared_memory
    Variable ScopeGlobal
    Dynamic VariableNo
    Platform SpecificWindows
    Permitted ValuesTypeboolean
    DefaultFALSE

    (Windows only.) Whether the server permits shared-memory connections.

  • shared_memory_base_name

    Command-Line Format--shared_memory_base_name=name
    System VariableNameshared_memory_base_name
    Variable ScopeGlobal
    Dynamic VariableNo
    Platform SpecificWindows
    Permitted ValuesTypestring
    DefaultMYSQL

    (Windows only.) The name of shared memory to use for shared-memory connections. This is useful when running multiple MySQL instances on a single physical machine. The default name is MYSQL. The name is case sensitive.

  • show_compatibility_56

    Introduced5.7.6
    Deprecated5.7.6
    Command-Line Format--show_compatibility_56[={OFF|ON}]
    System VariableNameshow_compatibility_56
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.7)Typeboolean
    DefaultON
    Permitted Values (>= 5.7.8)Typeboolean
    DefaultOFF

    The INFORMATION_SCHEMA has tables that contain system and status variable information (see Section 22.10, “The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables”, and Section 22.9, “The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables”). As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 23.9.13, “Performance Schema System Variable Tables”, and Section 23.9.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

    For advice on migrating away from the INFORMATION_SCHEMA tables to the Performance Schema tables, see Section 23.17, “Migrating to Performance Schema System and Status Variable Tables”. To assist in the migration, you can use the show_compatibility_56 system variable, which affects whether MySQL 5.6 compatibility is enabled with respect to how system and status variable information is provided by the INFORMATION_SCHEMA and Performance Schema tables, and also by the SHOW VARIABLES and SHOW STATUS statements.

    Note

    show_compatibility_56 is deprecated because its only purpose is to permit control over deprecated system and status variable information sources that will be removed in a future MySQL release. When those sources are removed, show_compatibility_56 will have no purpose and will be removed as well.

    The following discussion describes the effects of show_compatibility_56:

    For better understanding, it is strongly recommended that you also read these sections:

    Overview of show_compatibility_56 Effects

    The show_compatibility_56 system variable affects these aspects of server operation regarding system and status variables:

    • Information available from the SHOW VARIABLES and SHOW STATUS statements

    • Information available from the INFORMATION_SCHEMA tables that provide system and status variable information

    • Information available from the Performance Schema tables that provide system and status variable information

    • The effect of the FLUSH STATUS statement on status variables

    This list summarizes the effects of show_compatibility_56, with additional details given later:

    • When show_compatibility_56 is ON, compatibility with MySQL 5.6 is enabled. Older variable information sources (SHOW statements, INFORMATION_SCHEMA tables) produce the same output as in MySQL 5.6.

    • When show_compatibility_56 is OFF, compatibility with MySQL 5.6 is disabled. Selecting from the INFORMATION_SCHEMA tables produces an error because the Performance Schema tables are intended to replace them. The INFORMATION_SCHEMA tables are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.

      To obtain system and status variable information When show_compatibility_56=OFF, use the Performance Schema tables or the SHOW statements.

      Note

      When show_compatibility_56=OFF, the SHOW VARIABLES and SHOW STATUS statements display rows from the Performance Schema global_variables, session_variables, global_status, and session_status tables.

      As of MySQL 5.7.9, those tables are world readable and accessible without the SELECT privilege, which means that SELECT is not needed to use the SHOW statements, either. Before MySQL 5.7.9, the SELECT privilege is required to access those Performance Schema tables, either directly, or indirectly through the SHOW statements.

    • Several Slave_xxx status variables are available from SHOW STATUS when show_compatibility_56 is ON. When show_compatibility_56 is OFF, some of those variables are not exposed to SHOW STATUS. The information they provide is available in replication-related Performance Schema tables, as described later.

    • show_compatibility_56 has no effect on system variable access using @@ notation: @@GLOBAL.var_name, @@SESSION.var_name, @@var_name.

    • show_compatibility_56 has no effect for the embedded server, which produces 5.6-compatible output in all cases.

    The following descriptions detail the effect of setting show_compatibility_56 to ON or OFF in the contexts in which this variable applies.

    Effect of show_compatibility_56 on SHOW Statements

    SHOW GLOBAL VARIABLES statement:

    • ON: MySQL 5.6 output.

    • OFF: Output displays rows from the Performance Schema global_variables table.

    SHOW [SESSION | LOCAL] VARIABLES statement:

    • ON: MySQL 5.6 output.

    • OFF: Output displays rows from the Performance Schema session_variables table. (In MySQL 5.7.6 and 5.7.7, OFF output does not fully reflect all system variable values in effect for the current session; it includes no rows for global variables that have no session counterpart. This is corrected in MySQL 5.7.8.)

    SHOW GLOBAL STATUS statement:

    • ON: MySQL 5.6 output.

    • OFF: Output displays rows from the Performance Schema global_status table, plus the Com_xxx statement execution counters.

      OFF output includes no rows for session variables that have no global counterpart, unlike ON output.

    SHOW [SESSION | LOCAL] STATUS statement:

    • ON: MySQL 5.6 output.

    • OFF: Output displays rows from the Performance Schema session_status table, plus the Com_xxx statement execution counters. (In MySQL 5.7.6 and 5.7.7, OFF output does not fully reflect all status variable values in effect for the current session; it includes no rows for global variables that have no session counterpart. This is corrected in MySQL 5.7.8.)

    In MySQL 5.7.6 and 5.7.7, for each of the SHOW statements just described, use of a WHERE clause produces a warning when show_compatibility_56=ON and an error when show_compatibility_56=OFF. (This applies to WHERE clauses that are not optimized away. For example, WHERE 1 is trivially true, is optimized away, and thus produces no warning or error.) This behavior does not occur as of MySQL 5.7.8; WHERE is supported as before 5.7.6.

    Effect of show_compatibility_56 on INFORMATION_SCHEMA Tables

    INFORMATION_SCHEMA tables (GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, and SESSION_STATUS):

    • ON: MySQL 5.6 output, with a deprecation warning.

    • OFF: Selecting from these tables produces an error. (Before 5.7.9, selecting from these tables produces no output, with a deprecation warning.)

    Effect of show_compatibility_56 on Performance Schema Tables

    Performance Schema system variable tables:

    • OFF:

      • global_variables: Global system variables only.

      • session_variables: System variables in effect for the current session: A row for each session variable, and a row for each global variable that has no session counterpart. (In MySQL 5.7.6 and 5.7.7, the table does not fully reflect all system variable values in effect for the current session; it includes no rows for global variables that have no session counterpart. This is corrected in MySQL 5.7.8.)

      • variables_by_thread: Session system variables only, for each active session.

    • ON: Same output as for OFF. (Before 5.7.8, these tables produce no output.)

    Performance Schema status variable tables:

    • OFF:

      • global_status: Global status variables only.

      • session_status: Status variables in effect the current session: A row for each session variable, and a row for each global variable that has no session counterpart. (In MySQL 5.7.6 and 5.7.7, the table does not fully reflect all status variable values in effect for the current session; it includes no rows for global variables that have no session counterpart. This is corrected in MySQL 5.7.8.)

      • status_by_account Session status variables only, aggregated per account.

      • status_by_host: Session status variables only, aggregated per host name.

      • status_by_thread: Session status variables only, for each active session.

      • status_by_user: Session status variables only, aggregated per user name.

    • ON: Same output as for OFF. (Before 5.7.9, these tables produce no output.)

    Effect of show_compatibility_56 on Slave Status Variables

    Slave status variables:

    Effect of show_compatibility_56 on FLUSH STATUS

    FLUSH STATUS statement:

    • ON: This statement produces MySQL 5.6 behavior. It adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections.

    • OFF: This statement adds the session status from all active sessions to the global status variables, resets the status of all active sessions, and resets account, host, and user status values aggregated from disconnected sessions.

  • show_old_temporals

    Introduced5.7.6
    Deprecated5.7.6
    Command-Line Format--show_old_temporals={OFF|ON}
    System VariableNameshow_old_temporals
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether SHOW CREATE TABLE output includes comments to flag temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). This variable is disabled by default. If enabled, SHOW CREATE TABLE output looks like this:

    CREATE TABLE `mytbl` (
      `ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `dt` datetime /* 5.5 binary format */ DEFAULT NULL,
      `t` time /* 5.5 binary format */ DEFAULT NULL
    ) DEFAULT CHARSET=latin1
    

    Output for the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS table is affected similarly.

    This variable is deprecated and will be removed in a future MySQL release.

  • skip_external_locking

    Command-Line Format--skip-external-locking
    System VariableNameskip_external_locking
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    This is OFF if mysqld uses external locking (system locking), ON if external locking is disabled. This affects only MyISAM table access.

    This variable is set by the --external-locking or --skip-external-locking option. External locking is disabled by default.

    External locking affects only MyISAM table access. For more information, including conditions under which it can and cannot be used, see Section 9.11.5, “External Locking”.

  • skip_name_resolve

    Command-Line Format--skip-name-resolve
    System VariableNameskip_name_resolve
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable is set from the value of the --skip-name-resolve option. If it is OFF, mysqld resolves host names when checking client connections. If it is ON, mysqld uses only IP numbers; in this case, all Host column values in the grant tables must be IP addresses or localhost. See Section 9.12.6.2, “DNS Lookup Optimization and the Host Cache”.

  • skip_networking

    Command-Line Format--skip-networking
    System VariableNameskip_networking
    Variable ScopeGlobal
    Dynamic VariableNo

    This is ON if the server permits only local (non-TCP/IP) connections. On Unix, local connections use a Unix socket file. On Windows, local connections use a named pipe or shared memory. This variable can be set to ON with the --skip-networking option.

  • skip_show_database

    Command-Line Format--skip-show-database
    System VariableNameskip_show_database
    Variable ScopeGlobal
    Dynamic VariableNo

    This prevents people from using the SHOW DATABASES statement if they do not have the SHOW DATABASES privilege. This can improve security if you have concerns about users being able to see databases belonging to other users. Its effect depends on the SHOW DATABASES privilege: If the variable value is ON, the SHOW DATABASES statement is permitted only to users who have the SHOW DATABASES privilege, and the statement displays all database names. If the value is OFF, SHOW DATABASES is permitted to all users, but displays the names of only those databases for which the user has the SHOW DATABASES or other privilege. (Note that any global privilege is considered a privilege for the database.)

  • slow_launch_time

    Command-Line Format--slow_launch_time=#
    System VariableNameslow_launch_time
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default2

    If creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable.

  • slow_query_log

    Command-Line Format--slow-query-log
    System VariableNameslow_query_log
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether the slow query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log. The default value depends on whether the --slow_query_log option is given. The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

    Slow is determined by the value of the long_query_time variable. See Section 6.4.5, “The Slow Query Log”.

  • slow_query_log_file

    Command-Line Format--slow-query-log-file=file_name
    System VariableNameslow_query_log_file
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypefile name
    Defaulthost_name-slow.log

    The name of the slow query log file. The default value is host_name-slow.log, but the initial value can be changed with the --slow_query_log_file option.

  • socket

    Command-Line Format--socket={file_name|pipe_name}
    System VariableNamesocket
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default/tmp/mysql.sock

    On Unix platforms, this variable is the name of the socket file that is used for local client connections. The default is /tmp/mysql.sock. (For some distribution formats, the directory might be different, such as /var/lib/mysql for RPMs.)

    On Windows, this variable is the name of the named pipe that is used for local client connections. The default value is MySQL (not case sensitive).

  • sort_buffer_size

    Command-Line Format--sort_buffer_size=#
    System VariableNamesort_buffer_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (Windows)Typeinteger
    Default262144
    Min Value32768
    Max Value4294967295
    Permitted Values (Other, 32-bit platforms)Typeinteger
    Default262144
    Min Value32768
    Max Value4294967295
    Permitted Values (Other, 64-bit platforms)Typeinteger
    Default262144
    Min Value32768
    Max Value18446744073709551615

    Each session that must perform a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization. At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer. Also, increasing the value of max_sort_length may require increasing the value of sort_buffer_size. For more information, see Section 9.2.1.15, “ORDER BY Optimization”

    If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing.

    The optimizer tries to work out how much space is needed but can allocate more, up to the limit. Setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section B.5.3.5, “Where MySQL Stores Temporary Files”.

    The maximum permissible setting for sort_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).

  • sql_auto_is_null

    System VariableNamesql_auto_is_null
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default0

    If this variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:

    SELECT * FROM tbl_name WHERE auto_col IS NULL
    

    If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see Section 13.14, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.

    The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison is used by some ODBC programs, such as Access. See Obtaining Auto-Increment Values. This behavior can be disabled by setting sql_auto_is_null to 0.

    The default value of sql_auto_is_null is 0.

  • sql_big_selects

    System VariableNamesql_big_selects
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default1

    If set to 0, MySQL aborts SELECT statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of max_join_size). This is useful when an inadvisable WHERE statement has been issued. The default value for a new connection is 1, which permits all SELECT statements.

    If you set the max_join_size system variable to a value other than DEFAULT, sql_big_selects is set to 0.

  • sql_buffer_result

    System VariableNamesql_buffer_result
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default0

    If set to 1, sql_buffer_result forces results from SELECT statements to be put into temporary tables. This helps MySQL free the table locks early and can be beneficial in cases where it takes a long time to send results to the client. The default value is 0.

  • sql_log_bin

    System VariableNamesql_log_bin
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeboolean

    This variable controls whether logging to the binary log is done. The default value is 1 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the SUPER privilege to set this variable.

    Setting this variable to 0 prevents GTIDs from being assigned to transactions in the binary log. If you are using GTIDs for replication, this means that, even when binary logging is later enabled once again, the GTIDs written into the log from this point do not account for any transactions that occurred in the meantime—in effect, those transactions are lost.

    In MySQL 5.7, it is not possible to set @@session.sql_log_bin within a transaction or subquery. (Bug #53437)

  • sql_log_off

    System VariableNamesql_log_off
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default0

    This variable controls whether logging to the general query log is done. The default value is 0 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the SUPER privilege to set this option. The default value is 0.

  • sql_mode

    Command-Line Format--sql-mode=name
    System VariableNamesql_mode
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (<= 5.7.4)Typeset
    DefaultNO_ENGINE_SUBSTITUTION
    Valid ValuesALLOW_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
    Permitted Values (>= 5.7.5, <= 5.7.6)Typeset
    DefaultONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION
    Valid ValuesALLOW_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
    Permitted Values (5.7.7)Typeset
    DefaultONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
    Valid ValuesALLOW_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
    Permitted Values (>= 5.7.8)Typeset
    DefaultONLY_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
    Valid ValuesALLOW_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

    The current server SQL mode, which can be set dynamically. For details, see Section 6.1.7, “Server SQL Modes”.

    Note

    MySQL installation programs may configure the SQL mode during the installation process. For example, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file contains a line that sets the SQL mode; see Section 5.4.2, “mysql_install_db — Initialize MySQL Data Directory”.

    If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.

  • sql_notes

    If set to 1 (the default), warnings of Note level increment warning_count and the server records them. If set to 0, Note warnings do not increment warning_count and the server does not record them. mysqldump includes output to set this variable to 0 so that reloading the dump file does not produce warnings for events that do not affect the integrity of the reload operation.

  • sql_quote_show_create

    If set to 1 (the default), the server quotes identifiers for SHOW CREATE TABLE and SHOW CREATE DATABASE statements. If set to 0, quoting is disabled. This option is enabled by default so that replication works for identifiers that require quoting. See Section 14.7.5.10, “SHOW CREATE TABLE Syntax”, and Section 14.7.5.6, “SHOW CREATE DATABASE Syntax”.

  • sql_safe_updates

    If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

  • sql_select_limit

    System VariableNamesql_select_limit
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger

    The maximum number of rows to return from SELECT statements. The default value for a new connection is the maximum number of rows that the server permits per table. Typical default values are (232)−1 or (264)−1. If you have changed the limit, the default value can be restored by assigning a value of DEFAULT.

    If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of sql_select_limit.

  • sql_warnings

    This variable controls whether single-row INSERT statements produce an information string if warnings occur. The default is 0. Set the value to 1 to produce an information string.

  • ssl_ca

    Command-Line Format--ssl-ca=file_name
    System VariableNamessl_ca
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The path to a file with a list of trusted SSL CAs.

  • ssl_capath

    Command-Line Format--ssl-capath=dir_name
    System VariableNamessl_capath
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The path to a directory that contains trusted SSL CA certificates in PEM format.

  • ssl_cert

    Command-Line Format--ssl-cert=file_name
    System VariableNamessl_cert
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The name of the SSL certificate file to use for establishing a secure connection.

  • ssl_cipher

    Command-Line Format--ssl-cipher=name
    System VariableNamessl_cipher
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    A list of permissible ciphers to use for SSL encryption.

  • ssl_crl

    Command-Line Format--ssl-crl=file_name
    System VariableNamessl_crl
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The path to a file containing certificate revocation lists in PEM format. Revocation lists work for MySQL distributions compiled using OpenSSL (but not yaSSL). See Section 7.4.1, “OpenSSL Versus yaSSL”.

  • ssl_crlpath

    Command-Line Format--ssl-crlpath=dir_name
    System VariableNamessl_crlpath
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The path to a directory that contains files containing certificate revocation lists in PEM format. Revocation lists work for MySQL distributions compiled using OpenSSL (but not yaSSL). See Section 7.4.1, “OpenSSL Versus yaSSL”.

  • ssl_key

    Command-Line Format--ssl-key=file_name
    System VariableNamessl_key
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The name of the SSL key file to use for establishing a secure connection.

  • storage_engine

    Removed5.7.5
    System VariableNamestorage_engine
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultInnoDB

    This variable is deprecated and was removed in MySQL 5.7.5. Use default_storage_engine instead.

  • stored_program_cache

    Command-Line Format--stored-program-cache=#
    System VariableNamestored_program_cache
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.5)Typeinteger
    Default256
    Min Value256
    Max Value524288
    Permitted Values (>= 5.7.6)Typeinteger
    Default256
    Min Value16
    Max Value524288

    Sets a soft upper limit for the number of cached stored routines per connection. The value of this variable is specified in terms of the number of stored routines held in each of the two caches maintained by the MySQL Server for, respectively, stored procedures and stored functions.

    Whenever a stored routine is executed this cache size is checked before the first or top-level statement in the routine is parsed; if the number of routines of the same type (stored procedures or stored functions according to which is being executed) exceeds the limit specified by this variable, the corresponding cache is flushed and memory previously allocated for cached objects is freed. This allows the cache to be flushed safely, even when there are dependencies between stored routines.

  • super_read_only

    Introduced5.7.8
    Command-Line Format--super_read_only[={OFF|ON}]
    System VariableNamesuper_read_only
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    If the read_only system variable is enabled, the server permits client updates only from users who have the SUPER privilege. If the super_read_only system variable is also enabled, the server prohibits client updates even from users who have SUPER. See the description of the read_only system variable for a description of read-only mode and information about how read_only and super_read_only interact.

    Changes to super_read_only on a master server are not replicated to slave servers. The value can be set on a slave server independent of the setting on the master.

  • sync_frm

    Deprecated5.7.6
    Command-Line Format--sync-frm
    System VariableNamesync_frm
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultTRUE

    If this variable is set to 1, when any nontemporary table is created its .frm file is synchronized to disk (using fdatasync()). This is slower but safer in case of a crash. The default is 1.

    This variable is deprecated in MySQL 5.7.6 and will be removed in a future version of MySQL (when .frm files become obsolete).

  • system_time_zone

    System VariableNamesystem_time_zone
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The server system time zone. When the server begins executing, it inherits a time zone setting from the machine defaults, possibly modified by the environment of the account used for running the server or the startup script. The value is used to set system_time_zone. Typically the time zone is specified by the TZ environment variable. It also can be specified using the --timezone option of the mysqld_safe script.

    The system_time_zone variable differs from time_zone. Although they might have the same value, the latter variable is used to initialize the time zone for each client that connects. See Section 11.6, “MySQL Server Time Zone Support”.

  • table_definition_cache

    System VariableNametable_definition_cache
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default-1 (autosized)
    Min Value400
    Max Value524288

    The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum value is 400. The default value is based on the following formula, capped to a limit of 2000:

    400 + (table_open_cache / 2)
    

    For InnoDB, table_definition_cache acts as a soft limit for the number of open table instances in the InnoDB data dictionary cache. If the number of open table instances exceeds the table_definition_cache setting, the LRU mechanism begins to mark table instances for eviction and eventually removes them from the data dictionary cache. The limit helps address situations in which significant amounts of memory would be used to cache rarely used table instances until the next server restart. The number of table instances with cached metadata could be higher than the limit defined by table_definition_cache, because InnoDB system table instances and parent and child table instances with foreign key relationships are not placed on the LRU list and are not subject to eviction from memory.

    Additionally, table_definition_cache defines a soft limit for the number of InnoDB file-per-table tablespaces that can be open at one time, which is also controlled by innodb_open_files. If both table_definition_cache and innodb_open_files are set, the highest setting is used. If neither variable is set, table_definition_cache, which has a higher default value, is used. If the number of open tablespace file handles exceeds the limit defined by table_definition_cache or innodb_open_files, the LRU mechanism searches the tablespace file LRU list for files that are fully flushed and are not currently being extended. This process is performed each time a new tablespace is opened. If there are no inactive tablespaces, no tablespace files are closed.

  • table_open_cache

    System VariableNametable_open_cache
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default2000
    Min Value1
    Max Value524288

    The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See Section 6.1.6, “Server Status Variables”. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable. For more information about the table cache, see Section 9.4.3.1, “How MySQL Opens and Closes Tables”.

  • table_open_cache_instances

    System VariableNametable_open_cache_instances
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.7)Typeinteger
    Default1
    Min Value1
    Max Value64
    Permitted Values (>= 5.7.8)Typeinteger
    Default16
    Min Value1
    Max Value64

    The number of open tables cache instances. To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances . A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)

    A value of 8 or 16 is recommended on systems that routinely use 16 or more cores.

  • thread_cache_size

    Command-Line Format--thread_cache_size=#
    System VariableNamethread_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default-1 (autosized)
    Min Value0
    Max Value16384

    How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 6.1.6, “Server Status Variables”.

    The default value is based on the following formula, capped to a limit of 100:

    8 + (max_connections / 100)
    

    This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer visible within the embedded server.

  • thread_concurrency

    Deprecated5.6.1
    Removed5.7.2
    Command-Line Format--thread_concurrency=#
    System VariableNamethread_concurrency
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default10
    Min Value1
    Max Value512

    This variable is specific to Solaris 8 and earlier systems, for which mysqld invokes the thr_setconcurrency() function with the variable value. This function enables applications to give the threads system a hint about the desired number of threads that should be run at the same time. Current Solaris versions document this as having no effect.

    This variable was removed in MySQL 5.7.2.

  • thread_handling

    Command-Line Format--thread_handling=name
    System VariableNamethread_handling
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.8)Typeenumeration
    Defaultone-thread-per-connection
    Valid Valuesno-threads
    one-thread-per-connection
    Permitted Values (>= 5.7.9)Typeenumeration
    Defaultone-thread-per-connection
    Valid Valuesno-threads
    one-thread-per-connection
    dynamically-loaded

    The thread-handling model used by the server for connection threads. The permissible values are no-threads (the server uses a single thread to handle one connection) and one-thread-per-connection (the server uses one thread to handle each client connection). no-threads is useful for debugging under Linux; see Section 26.5, “Debugging and Porting MySQL”.

    This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer visible within the embedded server.

  • thread_stack

    Command-Line Format--thread_stack=#
    System VariableNamethread_stack
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (32-bit platforms)Typeinteger
    Default196608
    Min Value131072
    Max Value4294967295
    Block Size1024
    Permitted Values (64-bit platforms)Typeinteger
    Default262144
    Min Value131072
    Max Value18446744073709551615
    Block Size1024

    The stack size for each thread. The default of 192KB (256KB for 64-bit systems) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions.

  • time_format

    This variable is unused. It is deprecated and will be removed in a future MySQL release.

  • time_zone

    System VariableNametime_zone
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring

    The current time zone. This variable is used to initialize the time zone for each client that connects. By default, the initial value of this is 'SYSTEM' (which means, use the value of system_time_zone). The value can be specified explicitly at server startup with the --default-time-zone option. See Section 11.6, “MySQL Server Time Zone Support”.

  • timed_mutexes

    Deprecated5.6.20
    Removed5.7.5
    Command-Line Format--timed_mutexes
    System VariableNametimed_mutexes
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    This deprecated variable has no use and was removed in MySQL 5.7.5.

  • timestamp

    System VariableNametimestamp
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypenumeric

    Set the time for this client. This is used to get the original timestamp if you use the binary log to restore rows. timestamp_value should be a Unix epoch timestamp (a value like that returned by UNIX_TIMESTAMP(), not a value in 'YYYY-MM-DD hh:mm:ss' format) or DEFAULT.

    Setting timestamp to a constant value causes it to retain that value until it is changed again. Setting timestamp to DEFAULT causes its value to be the current date and time as of the time it is accessed.

    In MySQL 5.7, timestamp is a DOUBLE rather than BIGINT because its value includes a microseconds part.

    SET timestamp affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE(). The server can be started with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(), in which case SET timestamp affects both functions.

  • tls_version

    Introduced5.7.10
    Command-Line Format--tls_version=protocol_list
    System VariableNametls_version
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (OpenSSL)Typestring
    DefaultTLSv1,TLSv1.1,TLSv1.2
    Permitted Values (yaSSL)Typestring
    DefaultTLSv1,TLSv1.1

    The protocols permitted by the server for encrypted connections. The value is a comma-separated list containing one or more protocol names. The protocols that can be named for this variable depend on the SSL library used to compile MySQL. For details, see Section 7.4.3, “Secure Connection Protocols and Ciphers”.

  • tmp_table_size

    Command-Line Format--tmp_table_size=#
    System VariableNametmp_table_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default16777216
    Min Value1024
    Max Value18446744073709551615

    The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.

    The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. As of MySQL 5.7.5, the internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables. Prior to MySQL 5.7.5, the MyISAM storage engine is used.

    Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

    You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

    See also Section 9.4.4, “Internal Temporary Table Use in MySQL”.

  • tmpdir

    Command-Line Format--tmpdir=dir_name
    System VariableNametmpdir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The directory used for temporary files and temporary tables. This variable can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (:) on Unix and semicolon characters (;) on Windows.

    The multiple-directory feature can be used to spread the load between several physical disks. If the MySQL server is acting as a replication slave, you should not set tmpdir to point to a directory on a memory-based file system or to a directory that is cleared when the server host restarts. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails. You can set the slave's temporary directory using the slave_load_tmpdir variable. In that case, the slave will not use the general tmpdir value and you can set tmpdir to a nonpermanent location.

  • transaction_alloc_block_size

    Command-Line Format--transaction_alloc_block_size=#
    System VariableNametransaction_alloc_block_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (>= 5.7.6)Typeinteger
    Default8192
    Min Value1024
    Max Value131072
    Block Size1024
    Permitted Values (32-bit platforms, <= 5.7.5)Typeinteger
    Default8192
    Min Value1024
    Max Value4294967295
    Block Size1024
    Permitted Values (64-bit platforms, <= 5.7.5)Typeinteger
    Default8192
    Min Value1024
    Max Value18446744073709551615
    Block Size1024

    The amount in bytes by which to increase a per-transaction memory pool which needs memory. See the description of transaction_prealloc_size.

  • transaction_prealloc_size

    Command-Line Format--transaction_prealloc_size=#
    System VariableNametransaction_prealloc_size
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (>= 5.7.6)Typeinteger
    Default4096
    Min Value1024
    Max Value131072
    Block Size1024
    Permitted Values (32-bit platforms, <= 5.7.5)Typeinteger
    Default4096
    Min Value1024
    Max Value4294967295
    Block Size1024
    Permitted Values (64-bit platforms, <= 5.7.5)Typeinteger
    Default4096
    Min Value1024
    Max Value18446744073709551615
    Block Size1024

    There is a per-transaction memory pool from which various transaction-related allocations take memory. The initial size of the pool in bytes is transaction_prealloc_size. For every allocation that cannot be satisfied from the pool because it has insufficient memory available, the pool is increased by transaction_alloc_block_size bytes. When the transaction ends, the pool is truncated to transaction_prealloc_size bytes.

    By making transaction_prealloc_size sufficiently large to contain all statements within a single transaction, you can avoid many malloc() calls.

  • transaction_write_set_extraction

    Introduced5.7.6
    Command-Line Format--transaction_write_set_extraction=[value]
    System VariableNametransaction_write_set_extraction
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultOFF
    Valid ValuesOFF
    MURMUR32

    Reserved for future use.

  • tx_isolation

    System VariableNametx_isolation
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultREPEATABLE-READ
    Valid ValuesREAD-UNCOMMITTED
    READ-COMMITTED
    REPEATABLE-READ
    SERIALIZABLE

    The default transaction isolation level. Defaults to REPEATABLE-READ.

    This variable can be set directly, or indirectly using the SET TRANSACTION statement. See Section 14.3.6, “SET TRANSACTION Syntax”. If you set tx_isolation directly to an isolation level name that contains a space, the name should be enclosed within quotation marks, with the space replaced by a dash. For example:

    SET tx_isolation = 'READ-COMMITTED';
    

    Any unique prefix of a valid value may be used to set the value of this variable.

    The default transaction isolation level can also be set at startup using the --transaction-isolation server option.

  • tx_read_only

    System VariableNametx_read_only
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    The default transaction access mode. The value can be OFF (read/write, the default) or ON (read only).

    This variable can be set directly, or indirectly using the SET TRANSACTION statement. See Section 14.3.6, “SET TRANSACTION Syntax”.

    To set the default transaction access mode at startup, use the --transaction-read-only server option.

  • unique_checks

    System VariableNameunique_checks
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default1

    If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, storage engines are permitted to assume that duplicate keys are not present in input data. If you know for certain that your data does not contain uniqueness violations, you can set this to 0 to speed up large table imports to InnoDB.

    Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.

  • updatable_views_with_limit

    Command-Line Format--updatable_views_with_limit=#
    System VariableNameupdatable_views_with_limit
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default1

    This variable controls whether updates to a view can be made when the view does not contain all columns of the primary key defined in the underlying table, if the update statement contains a LIMIT clause. (Such updates often are generated by GUI tools.) An update is an UPDATE or DELETE statement. Primary key here means a PRIMARY KEY, or a UNIQUE index in which no column can contain NULL.

    The variable can have two values:

    • 1 or YES: Issue a warning only (not an error message). This is the default value.

    • 0 or NO: Prohibit the update.

  • validate_password_xxx

    The validate_password plugin implements a set of system variables having names of the form validate_password_xxx. These variables affect password testing by that plugin; see Section 7.5.2.2, “Password Validation Plugin Options and Variables”.

  • validate_user_plugins

    System VariableNamevalidate_user_plugins
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    If this variable is enabled (the default), the server checks each user account and produces a warning if conditions are found that would make the account unusable:

    • The account requires an authentication plugin that is not loaded.

    • The account requires the sha256_password authentication plugin but the server was started with neither SSL nor RSA enabled as required by this plugin.

    Enabling validate_user_plugins slows down server initialization and FLUSH PRIVILEGES. If you do not require the additional checking, you can disable this variable at startup to avoid the performance decrement.

  • version

    The version number for the server. The value might also include a suffix indicating server build or configuration information. -log indicates that one or more of the general log, slow query log, or binary log are enabled. -debug indicates that the server was built with debugging support enabled.

  • version_comment

    System VariableNameversion_comment
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The CMake configuration program has a COMPILATION_COMMENT option that permits a comment to be specified when building MySQL. This variable contains the value of that comment. See Section 2.9.4, “MySQL Source-Configuration Options”.

  • version_compile_machine

    System VariableNameversion_compile_machine
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The type of the server binary.

  • version_compile_os

    </
    System VariableNameversion_compile_os
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring