4.2.3.1.2 Dynamic System Variables - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss system and other variables, then begin to look at general security issues. The second of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

TABLE OF CONTENTS:

By: Sams Publishing

Poor Best

Rating:

/ 3

June 01, 2006

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Beginning with MySQL 4.0.3, many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also select their values using SELECT. See Section 4.2.3.1, "System Variables."

The following table shows the full list of all dynamic system variables. The last column indicates for each variable whether GLOBAL or SESSION (or both) apply.

Variable Name	Value Type	Type
`autocommit`	boolean	`SESSION`
`big_tables`	boolean	`SESSION`
`binlog_cache_size`	numeric	`GLOBAL`
`bulk_insert_buffer_size`	numeric	`GLOBAL \| SESSION`
`character_set_client`	string	`GLOBAL \| SESSION`
`character_set_connection`	string	`GLOBAL \| SESSION`
`character_set_results`	string	`GLOBAL \| SESSION`
`character_set_server`	string	`GLOBAL \| SESSION`
`collation_connection`	string	`GLOBAL \| SESSION`
`collation_server`	string	`GLOBAL \| SESSION`
`concurrent_insert`	boolean	`GLOBAL`
`connect_timeout`	numeric	`GLOBAL`
`convert_character_set`	string	`GLOBAL \| SESSION`
`default_week_format`	numeric	`GLOBAL \| SESSION`
`delay_key_write`	`OFF \| ON \| ALL`	`GLOBAL`
`delayed_insert_limit`	numeric	`GLOBAL`
`delayed_insert_timeout`	numeric	`GLOBAL`
`delayed_queue_size`	numeric	`GLOBAL`
`error_count`	numeric	`SESSION`
`flush`	boolean	`GLOBAL`
`flush_time`	numeric	`GLOBAL`
`foreign_key_checks`	boolean	`SESSION`
`ft_boolean_syntax`	numeric	`GLOBAL`
`group_concat_max_len`	numeric	`GLOBAL \| SESSION`
`identity`	numeric	`SESSION`
`insert_id`	boolean	`SESSION`
`interactive_timeout`	numeric	`GLOBAL \| SESSION`
`join_buffer_size`	numeric	`GLOBAL \| SESSION`
`key_buffer_size`	numeric	`GLOBAL`
`last_insert_id`	numeric	`SESSION`
`local_infile`	boolean	`GLOBAL`
`log_warnings`	boolean	`GLOBAL`
`long_query_time`	numeric	`GLOBAL \| SESSION`
`low_priority_updates`	boolean	`GLOBAL \| SESSION`
`max_allowed_packet`	numeric	`GLOBAL \| SESSION`
`max_binlog_cache_size`	numeric	`GLOBAL`
`max_binlog_size`	numeric	`GLOBAL`
`max_connect_errors`	numeric	`GLOBAL`
`max_connections`	numeric	`GLOBAL`
`max_delayed_threads`	numeric	`GLOBAL`
`max_error_count`	numeric	`GLOBAL \| SESSION`
`max_heap_table_size`	numeric	`GLOBAL \| SESSION`
`max_insert_delayed_threads`	numeric	`GLOBAL`
`max_join_size`	numeric	`GLOBAL \| SESSION`
`max_relay_log_size`	numeric	`GLOBAL`
`max_seeks_for_key`	numeric	`GLOBAL \| SESSION`
`max_sort_length`	numeric	`GLOBAL \| SESSION`
`max_tmp_tables`	numeric	`GLOBAL`
`max_user_connections`	numeric	`GLOBAL`
`max_write_lock_count`	numeric	`GLOBAL`
`myisam_max_extra_sort_file_size`	numeric	`GLOBAL \| SESSION`
`myisam_max_sort_file_size`	numeric	`GLOBAL \| SESSION`
`myisam_repair_threads`	numeric	`GLOBAL \| SESSION`
`myisam_sort_buffer_size`	numeric	`GLOBAL \| SESSION`
`net_buffer_length`	numeric	`GLOBAL \| SESSION`
`net_read_timeout`	numeric	`GLOBAL \| SESSION`
`net_retry_count`	numeric	`GLOBAL \| SESSION`
`net_write_timeout`	numeric	`GLOBAL \| SESSION`
`query_alloc_block_size`	numeric	`GLOBAL \| SESSION`
`query_cache_limit`	numeric	`GLOBAL`
`query_cache_size`	numeric	`GLOBAL`
`query_cache_type`	enumeration	`GLOBAL \| SESSION`
`query_cache_wlock_invalidate`	boolean	`GLOBAL \| SESSION`
`query_prealloc_size`	numeric	`GLOBAL \| SESSION`
`range_alloc_block_size`	numeric	`GLOBAL \| SESSION`
`read_buffer_size`	numeric	`GLOBAL \| SESSION`
`read_only`	numeric	`GLOBAL`
`read_rnd_buffer_size`	numeric	`GLOBAL \| SESSION`
`rpl_recovery_rank`	numeric	`GLOBAL`
`safe_show_database`	boolean	`GLOBAL`
`server_id`	numeric	`GLOBAL`
`slave_compressed_protocol`	boolean	`GLOBAL`
`slave_net_timeout`	numeric	`GLOBAL`
`slow_launch_time`	numeric	`GLOBAL`
`sort_buffer_size`	numeric	`GLOBAL \| SESSION`
`sql_auto_is_null`	boolean	`SESSION`
`sql_big_selects`	boolean	`SESSION`
`sql_big_tables`	boolean	`SESSION`
`sql_buffer_result`	boolean	`SESSION`
`sql_log_bin`	boolean	`SESSION`
`sql_log_off`	boolean	`SESSION`
`sql_log_update`	boolean	`SESSION`
`sql_low_priority_updates`	boolean	`GLOBAL \| SESSION`
`sql_max_join_size`	numeric	`GLOBAL \| SESSION`
`sql_quote_show_create`	boolean	`SESSION`
`sql_safe_updates`	boolean	`SESSION`
`sql_select_limit`	numeric	`SESSION`
`sql_slave_skip_counter`	numeric	`GLOBAL`
`sql_warnings`	boolean	`SESSION`
`storage_engine`	enumeration	`GLOBAL \| SESSION`
`table_cache`	numeric	`GLOBAL`
`table_type`	enumeration	`GLOBAL \| SESSION`
`thread_cache_size`	numeric	`GLOBAL`
`timestamp`	boolean	`SESSION`
`tmp_table_size`	enumeration	`GLOBAL \| SESSION`
`transaction_alloc_block_size`	numeric	`GLOBAL \| SESSION`
`transaction_prealloc_size`	numeric	`GLOBAL \| SESSION`
`tx_isolation`	enumeration	`GLOBAL \| SESSION`
`unique_checks`	boolean	`SESSION`
`wait_timeout`	numeric	`GLOBAL \| SESSION`
`warning_count`	numeric	`SESSION`

Variables that are marked as "string" take a string value. Variables that are marked as "numeric" take a numeric value. Variables that are marked as "boolean" can be set to 0, 1, ON or OFF. Variables that are marked as "enumeration" normally should be set to one of the available values for the variable, but can also be set to the number that corresponds to the desired enumeration value. For enumeration-valued system variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1.

Next: 4.2.4 Server Status Variables >>