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 individual 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
statement. To change a
global variable, you must have the var_nameSUPER
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
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.
var_name
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
Before MySQL 4.0, use this syntax instead:
mysqld --set-variable=key_buffer_size=16M
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;
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;
mysql> SET SESSION sort_buffer_size = 10 * 1024 * 1024;
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- at
server startup. For example, to prevent the value of
var_namequery_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;转载保留：http://www.qqread.com/mysql/f261127052.html进入讨论组讨论。
+---------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| back_log | 50 |
| basedir | /usr/local/mysql |
| bdb_cache_size | 8388600 |
| bdb_home | /usr/local/mysql |
| bdb_log_buffer_size | 131072 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/data/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 1800 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | YES |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 2097152 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 10485760 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 4194304 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql/share/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| log | OFF |
| log_bin | OFF |
| log_error | ./gigan.err |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_sort_file_size | 107374182400 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| named_pipe | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 622 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysql/gigan.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 61440 |
| read_only | OFF |
| read_rnd_buffer_size | 258048 |
| relay_log_purge | ON |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| shared_memory | OFF |
| shared_memory_base_name | MYSQL |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 217080 |
| sql_mode | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | E. Australia Standard Time |
| table_cache | 256 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 5242880 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.2-alpha-max |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (December 1, 2004) |
| version_comment | Source distribution |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------+
【深 度 阅 读】 相 关 文 章
相关专题
- 专访MySQL CEO：MySQL与企业级应用 (0次浏览)
- 深入了解MySQL系统日常管理 (0次浏览)
- 如何保护MySQL重要数据 (0次浏览)
- 浅析MySQL数据库常用管理 (0次浏览)
- 缓存PHP页面代码 可减轻CPU和MYSQL负担 (0次浏览)
- 浅析Tomcat5配置Mysql JDBC数据库连接池 (0次浏览)
- MySQL数据库敏感数据安全保护六大措施 (0次浏览)
- apache+php+mysql+serv_u迷你傻瓜安装程序 (0次浏览)
- Oracle,SQL Server,MySQL的自增变量设置 (0次浏览)
- Win2003下APACHE+PHP5+MYSQL4+PHPMYADMIN的简 (0次浏览)
