The server maintains many system variables that indicate how it is configured. All of them have default values. They can be set at server startup using options on the command line or in option files. Most of them can be set at runtime using the SET statement.
Beginning with MySQL 4.0.3, the mysqld server maintains two kinds of variables. Global variables affect the overall operation of the server. Session variables affect its operation for inpidual client connections.
When the server starts, it initializes all global variables to their default values. These defaults can be changed by options specified in option files or on the command line. After the server starts, those global variables that are dynamic can be changed by connecting to the server and issuing a SET GLOBAL <em class="replaceable"><code>var_name statement. To change a global variable, you must have the SUPER privilege.
The server also maintains a set of session variables for each client that connects. The client's session variables are initialized at connect time using the current values of the corresponding global variables. For those session variables that are dynamic, the client can change them by issuing a SET SESSION <em class="replaceable"><code>var_name statement. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.
A change to a global variable is visible to any client that accesses that global variable. However, it affects the corresponding session variable that is initialized from the global variable only for clients that connect after the change. It does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).
When setting a variable using a startup option, variable values can be given with a suffix of K, M, or G to indicate kilobytes, megabytes, or gigabytes, respectively. For example, the following command starts the server with a key buffer size of 16 megabytes:
mysqld --key_buffer_size=16M<br>
Before MySQL 4.0, use this syntax instead:
mysqld --set-variable=key_buffer_size=16M<br>
The lettercase of suffix letters does not matter; 16M and 16m are equivalent.
At runtime, use the SET statement to set system variables. In this context, suffix letters cannot be used, but the value can take the form of an expression:
mysql> SET sort_buffer_size = 10 * 1024 * 1024;<br>
To specify explicitly whether to set the global or session variable, use the GLOBAL or SESSION options:
mysql> SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;<br>mysql> SET SESSION sort_buffer_size = 10 * 1024 * 1024;<br>
Without either option, the statement sets the session variable.
The variables that can be set at runtime are listed in Section 5.3.3.1, “Dynamic System Variables”.
If you want to restrict the maximum value to which a system variable can be set with the SET statement, you can specify this maximum by using an option of the form --maximum-<em class="replaceable"><code>var_name at server startup. For example, to prevent the value of query_cache_size from being increased to more than 32MB at runtime, use the option --maximum-query_cache_size=32M. This feature is available as of MySQL 4.0.2.
You can view system variables and their values by using the SHOW VARIABLES statement. See Section 9.4, “System Variables” for more information.
mysql> SHOW VARIABLES;<br>+---------------------------------+-------------------------------------------------------------+<br>| Variable_name | Value |<br>+---------------------------------+-------------------------------------------------------------+<br>| auto_increment_increment | 1 |<br>| auto_increment_offset | 1 |<br>| back_log | 50 |<br>| basedir | /usr/local/mysql |<br>| bdb_cache_size | 8388600 |<br>| bdb_home | /usr/local/mysql |<br>| bdb_log_buffer_size | 131072 |<br>| bdb_logdir | |<br>| bdb_max_lock | 10000 |<br>| bdb_shared_data | OFF |<br>| bdb_tmpdir | /tmp/ |<br>| binlog_cache_size | 32768 |<br>| bulk_insert_buffer_size | 8388608 |<br>| character_set_client | latin1 |<br>| character_set_connection | latin1 |<br>| character_set_database | latin1 |<br>| character_set_results | latin1 |<br>| character_set_server | latin1 |<br>| character_set_system | utf8 |<br>| character_sets_dir | /usr/local/mysql/share/charsets/ |<br>| collation_connection | latin1_swedish_ci |<br>| collation_database | latin1_swedish_ci |<br>| collation_server | latin1_swedish_ci |<br>| concurrent_insert | 1 |<br>| connect_timeout | 5 |<br>| datadir | /usr/local/mysql/data/ |<br>| date_format | %Y-%m-%d |<br>| datetime_format | %Y-%m-%d %H:%i:%s |<br>| default_week_format | 0 |<br>| delay_key_write | ON |<br>| delayed_insert_limit | 100 |<br>| delayed_insert_timeout | 300 |<br>| delayed_queue_size | 1000 |<br>| expire_logs_days | 0 |<br>| flush | OFF |<br>| flush_time | 1800 |<br>| ft_boolean_syntax | + ->| ft_max_word_len | 84 |<br>| ft_min_word_len | 4 |<br>| ft_query_expansion_limit | 20 |<br>| ft_stopword_file | (built-in) |<br>| group_concat_max_len | 1024 |<br>| have_archive | NO |<br>| have_bdb | YES |<br>| have_compress | YES |<br>| have_crypt | NO |<br>| have_csv | NO |<br>| have_example_engine | NO |<br>| have_geometry | YES |<br>| have_innodb | YES |<br>| have_isam | NO |<br>| have_ndbcluster | NO |<br>| have_openssl | YES |<br>| have_query_cache | YES |<br>| have_raid | NO |<br>| have_rtree_keys | YES |<br>| have_symlink | YES |<br>| init_connect | |<br>| init_file | |<br>| init_slave | |<br>| innodb_additional_mem_pool_size | 2097152 |<br>| innodb_autoextend_increment | 8 |<br>| innodb_buffer_pool_awe_mem_mb | 0 |<br>| innodb_buffer_pool_size | 8388608 |<br>| innodb_data_file_path | ibdata1:10M:autoextend |<br>| innodb_data_home_dir | |<br>| innodb_fast_shutdown | 1 |<br>| innodb_file_io_threads | 4 |<br>| innodb_file_per_table | OFF |<br>| innodb_locks_unsafe_for_binlog | OFF |<br>| innodb_flush_log_at_trx_commit | 1 |<br>| innodb_flush_method | |<br>| innodb_force_recovery | 0 |<br>| innodb_lock_wait_timeout | 50 |<br>| innodb_log_arch_dir | |<br>| innodb_log_archive | OFF |<br>| innodb_log_buffer_size | 1048576 |<br>| innodb_log_file_size | 10485760 |<br>| innodb_log_files_in_group | 2 |<br>| innodb_log_group_home_dir | ./ |<br>| innodb_max_dirty_pages_pct | 90 |<br>| innodb_max_purge_lag | 0 |<br>| innodb_mirrored_log_groups | 1 |<br>| innodb_open_files | 300 |<br>| innodb_table_locks | ON |<br>| innodb_thread_concurrency | 8 |<br>| interactive_timeout | 28800 |<br>| join_buffer_size | 131072 |<br>| key_buffer_size | 4194304 |<br>| key_cache_age_threshold | 300 |<br>| key_cache_block_size | 1024 |<br>| key_cache_pision_limit | 100 |<br>| language | /usr/local/mysql/share/english/ |<br>| large_files_support | ON |<br>| license | GPL |<br>| local_infile | ON |<br>| log | OFF |<br>| log_bin | OFF |<br>| log_error | ./gigan.err |<br>| log_slave_updates | OFF |<br>| log_slow_queries | OFF |<br>| log_update | OFF |<br>| log_warnings | 1 |<br>| long_query_time | 10 |<br>| low_priority_updates | OFF |<br>| lower_case_file_system | OFF |<br>| lower_case_table_names | 1 |<br>| max_allowed_packet | 1048576 |<br>| max_binlog_cache_size | 4294967295 |<br>| max_binlog_size | 1073741824 |<br>| max_connect_errors | 10 |<br>| max_connections | 100 |<br>| max_delayed_threads | 20 |<br>| max_error_count | 64 |<br>| max_heap_table_size | 16777216 |<br>| max_insert_delayed_threads | 20 |<br>| max_join_size | 4294967295 |<br>| max_length_for_sort_data | 1024 |<br>| max_relay_log_size | 0 |<br>| max_seeks_for_key | 4294967295 |<br>| max_sort_length | 1024 |<br>| max_tmp_tables | 32 |<br>| max_user_connections | 0 |<br>| max_write_lock_count | 4294967295 |<br>| myisam_data_pointer_size | 4 |<br>| myisam_max_sort_file_size | 107374182400 |<br>| myisam_recover_options | OFF |<br>| myisam_repair_threads | 1 |<br>| myisam_sort_buffer_size | 8388608 |<br>| named_pipe | OFF |<br>| net_buffer_length | 16384 |<br>| net_read_timeout | 30 |<br>| net_retry_count | 10 |<br>| net_write_timeout | 60 |<br>| new | OFF |<br>| old_passwords | OFF |<br>| open_files_limit | 622 |<br>| optimizer_prune_level | 1 |<br>| optimizer_search_depth | 62 |<br>| pid_file | /usr/local/mysql/gigan.pid |<br>| port | 3306 |<br>| preload_buffer_size | 32768 |<br>| protocol_version | 10 |<br>| query_alloc_block_size | 8192 |<br>| query_cache_limit | 1048576 |<br>| query_cache_min_res_unit | 4096 |<br>| query_cache_size | 0 |<br>| query_cache_type | ON |<br>| query_cache_wlock_invalidate | OFF |<br>| query_prealloc_size | 8192 |<br>| range_alloc_block_size | 2048 |<br>| read_buffer_size | 61440 |<br>| read_only | OFF |<br>| read_rnd_buffer_size | 258048 |<br>| relay_log_purge | ON |<br>| rpl_recovery_rank | 0 |<br>| secure_auth | OFF |<br>| shared_memory | OFF |<br>| shared_memory_base_name | MYSQL |<br>| server_id | 0 |<br>| skip_external_locking | ON |<br>| skip_networking | OFF |<br>| skip_show_database | OFF |<br>| slave_net_timeout | 3600 |<br>| slow_launch_time | 2 |<br>| socket | /tmp/mysql.sock |<br>| sort_buffer_size | 217080 |<br>| sql_mode | |<br>| storage_engine | MyISAM |<br>| sync_binlog | 0 |<br>| sync_frm | ON |<br>| system_time_zone | E. Australia Standard Time |<br>| table_cache | 256 |<br>| table_type | MyISAM |<br>| thread_cache_size | 0 |<br>| thread_stack | 196608 |<br>| time_format | %H:%i:%s |<br>| time_zone | SYSTEM |<br>| tmp_table_size | 5242880 |<br>| tmpdir | /tmp/ |<br>| transaction_alloc_block_size | 8192 |<br>| transaction_prealloc_size | 4096 |<br>| tx_isolation | REPEATABLE-READ |<br>| updatable_views_with_limit | YES |<br>| version | 5.0.2-alpha-max |<br>| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (December 1, 2004) |<br>| version_comment | Source distribution |<br>| wait_timeout | 28800 |<br>+---------------------------------+-------------------------------------------------------------+
