September 2013 Blog Posts

MySql服务端系统变量大全(V5.6)

MitchellChu 2013-09-14 其他技术

5.6服务端的(来自官方文档)

Name Cmd-Line Option file System Var Var Scope Dynamic
audit_log_buffer_size     Yes Global No
audit_log_file     Yes Global No
audit_log_flush     Yes Global Yes
audit_log_policy     Yes Global Yes
audit_log_rotate_on_size     Yes Global Yes
audit_log_strategy     Yes Global No
auto_increment_increment     Yes Both Yes
auto_increment_offset     Yes Both Yes
autocommit Yes Yes Yes Both Yes
automatic_sp_privileges     Yes Global Yes
back_log     Yes Global No
basedir Yes Yes Yes Global No
big-tables Yes Yes     Yes
- Variable: big_tables     Yes Both Yes
bind-address Yes Yes     No
- Variable: bind_address     Yes Global No
binlog_cache_size Yes Yes Yes Global Yes
binlog_checksum     Yes Global Yes
binlog_direct_non_transactional_updates Yes Yes Yes Both Yes
binlog-format Yes Yes     Yes
- Variable: binlog_format     Yes Both Yes
binlog_max_flush_queue_time     Yes Global Yes
binlog_order_commits     Yes Global Yes
binlog_row_image Yes Yes Yes Both Yes
binlog_rows_query_log_events     Yes Both Yes
binlog_stmt_cache_size Yes Yes Yes Global Yes
bulk_insert_buffer_size Yes Yes Yes Both Yes
character_set_client     Yes Both Yes
character_set_connection     Yes Both Yes
character_set_database[a]     Yes Both Yes
character-set-filesystem Yes Yes     Yes
- Variable: character_set_filesystem     Yes Both Yes
character_set_results     Yes Both Yes
character-set-server Yes Yes     Yes
- Variable: character_set_server     Yes Both Yes
character_set_system     Yes Global No
character-sets-dir Yes Yes     No
- Variable: character_sets_dir     Yes Global No
collation_connection     Yes Both Yes
collation_database[b]     Yes Both Yes
collation-server Yes Yes     Yes
- Variable: collation_server     Yes Both Yes
completion_type Yes Yes Yes Both Yes
concurrent_insert Yes Yes Yes Global Yes
connect_timeout Yes Yes Yes Global Yes
core_file     Yes Global No
daemon_memcached_enable_binlog Yes Yes Yes Global No
daemon_memcached_engine_lib_name Yes Yes Yes Global No
daemon_memcached_engine_lib_path Yes Yes Yes Global No
daemon_memcached_option Yes Yes Yes Global No
daemon_memcached_r_batch_size Yes Yes Yes Global No
daemon_memcached_w_batch_size Yes Yes Yes Global No
datadir Yes Yes Yes Global No
date_format     Yes Global No
datetime_format     Yes Global No
debug Yes Yes Yes Both Yes
debug_sync     Yes Session Yes
default-storage-engine Yes Yes     Yes
- Variable: default_storage_engine     Yes Both Yes
default_tmp_storage_engine Yes Yes Yes Both Yes
default_week_format Yes Yes Yes Both Yes
delay-key-write Yes Yes     Yes
- Variable: delay_key_write     Yes Global Yes
delayed_insert_limit Yes Yes Yes Global Yes
delayed_insert_timeout Yes Yes Yes Global Yes
delayed_queue_size Yes Yes Yes Global Yes
disable_gtid_unsafe_statements Yes Yes Yes Global No
disable-gtid-unsafe-statements Yes Yes Yes Global No
disconnect_on_expired_password Yes Yes Yes Session No
div_precision_increment Yes Yes Yes Both Yes
end_markers_in_json     Yes Both Yes
enforce_gtid_consistency Yes Yes Yes Global No
enforce-gtid-consistency Yes Yes Yes Global No
engine-condition-pushdown Yes Yes     Yes
- Variable: engine_condition_pushdown     Yes Both Yes
eq_range_index_dive_limit     Yes Both Yes
error_count     Yes Session No
event-scheduler Yes Yes     Yes
- Variable: event_scheduler     Yes Global Yes
expire_logs_days Yes Yes Yes Global Yes
explicit_defaults_for_timestamp Yes Yes Yes Session No
external_user     Yes Session No
flush Yes Yes Yes Global Yes
flush_time Yes Yes Yes Global Yes
foreign_key_checks     Yes Both Yes
ft_boolean_syntax Yes Yes Yes Global Yes
ft_max_word_len Yes Yes Yes Global No
ft_min_word_len Yes Yes Yes Global No
ft_query_expansion_limit Yes Yes Yes Global No
ft_stopword_file Yes Yes Yes Global No
general-log Yes Yes     Yes
- Variable: general_log     Yes Global Yes
general_log_file Yes Yes Yes Global Yes
group_concat_max_len Yes Yes Yes Both Yes
gtid_done     Yes Both No
gtid_executed     Yes Both No
gtid_lost     Yes Global No
gtid_mode     Yes Global No
gtid-mode Yes Yes     No
- Variable: gtid_mode     Yes Global No
gtid_next     Yes Session Yes
gtid_owned     Yes Both No
gtid_purged     Yes Global Yes
have_compress     Yes Global No
have_crypt     Yes Global No
have_csv     Yes Global No
have_dynamic_loading     Yes Global No
have_geometry     Yes Global No
have_innodb     Yes Global No
have_ndbcluster     Yes Global No
have_openssl     Yes Global No
have_partitioning     Yes Global No
have_profiling     Yes Global No
have_query_cache     Yes Global No
have_rtree_keys     Yes Global No
have_ssl     Yes Global No
have_symlink     Yes Global No
host_cache_size     Yes Global Yes
hostname     Yes Global No
identity     Yes Session Yes
ignore-builtin-innodb Yes Yes     No
- Variable: ignore_builtin_innodb     Yes Global No
ignore_db_dirs     Yes Global No
init_connect Yes Yes Yes Global Yes
init-file Yes Yes     No
- Variable: init_file     Yes Global No
init_slave Yes Yes Yes Global Yes
innodb_adaptive_flushing Yes Yes Yes Global Yes
innodb_adaptive_flushing_lwm Yes Yes Yes Global Yes
innodb_adaptive_hash_index Yes Yes Yes Global Yes
innodb_adaptive_max_sleep_delay Yes Yes Yes Global Yes
innodb_additional_mem_pool_size Yes Yes Yes Global No
innodb_api_bk_commit_interval Yes Yes Yes Global Yes
innodb_api_disable_rowlock Yes Yes Yes Global No
innodb_api_enable_binlog Yes Yes Yes Global No
innodb_api_enable_mdl Yes Yes Yes Global No
innodb_api_trx_level Yes Yes Yes Global Yes
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_autoinc_lock_mode Yes Yes Yes Global No
innodb_buffer_pool_dump_at_shutdown Yes Yes Yes Global Yes
innodb_buffer_pool_dump_now Yes Yes Yes Global Yes
innodb_buffer_pool_filename Yes Yes Yes Global Yes
innodb_buffer_pool_instances Yes Yes Yes Global No
innodb_buffer_pool_load_abort Yes Yes Yes Global Yes
innodb_buffer_pool_load_at_startup Yes Yes Yes Global No
innodb_buffer_pool_load_now Yes Yes Yes Global Yes
innodb_buffer_pool_size Yes Yes Yes Global No
innodb_change_buffer_max_size Yes Yes Yes Global Yes
innodb_change_buffering Yes Yes Yes Global Yes
innodb_checksum_algorithm Yes Yes Yes Global Yes
innodb_checksums Yes Yes Yes Global No
innodb_cmp_per_index_enabled Yes Yes Yes Global Yes
innodb_commit_concurrency Yes Yes Yes Global Yes
innodb_compression_failure_threshold_pct Yes Yes Yes Global Yes
innodb_compression_level Yes Yes Yes Global Yes
innodb_compression_pad_pct_max Yes Yes Yes Global Yes
innodb_concurrency_tickets Yes Yes Yes Global Yes
innodb_data_file_path Yes Yes Yes Global No
innodb_data_home_dir Yes Yes Yes Global No
innodb_disable_sort_file_cache Yes Yes Yes Global Yes
innodb_doublewrite Yes Yes Yes Global No
innodb_fast_shutdown Yes Yes Yes Global Yes
innodb_file_format Yes Yes Yes Global Yes
innodb_file_format_check Yes Yes Yes Global No
innodb_file_format_max Yes Yes Yes Global Yes
innodb_file_per_table Yes Yes Yes Global Yes
innodb_flush_log_at_timeout     Yes Global Yes
innodb_flush_log_at_trx_commit Yes Yes Yes Global Yes
innodb_flush_method Yes Yes Yes Global No
innodb_flush_neighbors Yes Yes Yes Global Yes
innodb_flushing_avg_loops Yes Yes Yes Global Yes
innodb_force_load_corrupted Yes Yes Yes Global No
innodb_force_recovery Yes Yes Yes Global No
innodb_ft_aux_table Yes Yes Yes Global Yes
innodb_ft_cache_size Yes Yes Yes Global No
innodb_ft_enable_diag_print Yes Yes Yes Global Yes
innodb_ft_enable_stopword Yes Yes Yes Global Yes
innodb_ft_max_token_size Yes Yes Yes Global No
innodb_ft_min_token_size Yes Yes Yes Global No
innodb_ft_num_word_optimize Yes Yes Yes Global Yes
innodb_ft_server_stopword_table Yes Yes Yes Global Yes
innodb_ft_sort_pll_degree Yes Yes Yes Global No
innodb_ft_user_stopword_table Yes Yes Yes Both Yes
innodb_io_capacity Yes Yes Yes Global Yes
innodb_io_capacity_max Yes Yes Yes Global Yes
innodb_large_prefix Yes Yes Yes Global Yes
innodb_lock_wait_timeout Yes Yes Yes Both Yes
innodb_locks_unsafe_for_binlog Yes Yes Yes Global No
innodb_log_buffer_size Yes Yes Yes Global No
innodb_log_compressed_pages Yes Yes Yes Global Yes
innodb_log_file_size Yes Yes Yes Global No
innodb_log_files_in_group Yes Yes Yes Global No
innodb_log_group_home_dir Yes Yes Yes Global No
innodb_lru_scan_depth Yes Yes Yes Global Yes
innodb_max_dirty_pages_pct Yes Yes Yes Global Yes
innodb_max_dirty_pages_pct_lwm Yes Yes Yes Global Yes
innodb_max_purge_lag Yes Yes Yes Global Yes
innodb_max_purge_lag_delay Yes Yes Yes Global Yes
innodb_mirrored_log_groups Yes Yes Yes Global No
innodb_monitor_disable Yes Yes Yes Global Yes
innodb_monitor_enable Yes Yes Yes Global Yes
innodb_monitor_reset Yes Yes Yes Global Yes
innodb_monitor_reset_all Yes Yes Yes Global Yes
innodb_old_blocks_pct Yes Yes Yes Global Yes
innodb_old_blocks_time Yes Yes Yes Global Yes
innodb_online_alter_log_max_size Yes Yes Yes Global Yes
innodb_open_files Yes Yes Yes Global No
innodb_optimize_fulltext_only Yes Yes Yes Global Yes
innodb_page_size Yes Yes Yes Global No
innodb_print_all_deadlocks Yes Yes Yes Global Yes
innodb_purge_batch_size Yes Yes Yes Global Yes
innodb_purge_threads Yes Yes Yes Global No
innodb_random_read_ahead Yes Yes Yes Global Yes
innodb_read_ahead_threshold Yes Yes Yes Global Yes
innodb_read_io_threads Yes Yes Yes Global No
innodb_read_only Yes Yes Yes Global No
innodb_replication_delay Yes Yes Yes Global Yes
innodb_rollback_on_timeout Yes Yes Yes Global No
innodb_rollback_segments Yes Yes Yes Global Yes
innodb_sort_buffer_size Yes Yes Yes Global No
innodb_spin_wait_delay Yes Yes Yes Global Yes
innodb_stats_auto_recalc Yes Yes Yes Global Yes
innodb_stats_method Yes Yes Yes Global Yes
innodb_stats_on_metadata Yes Yes Yes Global Yes
innodb_stats_persistent Yes Yes Yes Global Yes
innodb_stats_persistent_sample_pages Yes Yes Yes Global Yes
innodb_stats_sample_pages Yes Yes Yes Global Yes
innodb_stats_transient_sample_pages Yes Yes Yes Global Yes
innodb_strict_mode Yes Yes Yes Both Yes
innodb_support_xa Yes Yes Yes Both Yes
innodb_sync_array_size Yes Yes Yes Global No
innodb_sync_spin_loops Yes Yes Yes Global Yes
innodb_table_locks Yes Yes Yes Both Yes
innodb_thread_concurrency Yes Yes Yes Global Yes
innodb_thread_sleep_delay Yes Yes Yes Global Yes
innodb_undo_directory Yes Yes Yes Global No
innodb_undo_logs Yes Yes Yes Global Yes
innodb_undo_tablespaces Yes Yes Yes Global No
innodb_use_native_aio Yes Yes Yes Global No
innodb_use_sys_malloc Yes Yes Yes Global No
innodb_version     Yes Global No
innodb_write_io_threads Yes Yes Yes Global No
insert_id     Yes Session Yes
interactive_timeout Yes Yes Yes Both Yes
join_buffer_size Yes Yes Yes Both Yes
keep_files_on_create Yes Yes Yes Both Yes
key_buffer_size Yes Yes Yes Global Yes
key_cache_age_threshold Yes Yes Yes Global Yes
key_cache_block_size Yes Yes Yes Global Yes
key_cache_division_limit Yes Yes Yes Global Yes
language Yes Yes Yes Global No
large_files_support     Yes Global No
large_page_size     Yes Global No
large-pages Yes Yes     No
- Variable: large_pages     Yes Global No
last_insert_id     Yes Session Yes
lc-messages Yes Yes     Yes
- Variable: lc_messages     Yes Both Yes
lc-messages-dir Yes Yes     No
- Variable: lc_messages_dir     Yes Global No
lc_time_names     Yes Both Yes
license     Yes Global No
local_infile     Yes Global Yes
lock_wait_timeout Yes Yes Yes Both Yes
locked_in_memory     Yes Global No
log Yes Yes Yes Global Yes
log_bin     Yes Global No
log-bin Yes Yes Yes Global No
log_bin_basename     Yes Global No
log_bin_index     Yes Global No
log_bin_use_v1_row_events Yes Yes Yes Global No
log-bin-use-v1-row-events Yes Yes     No
- Variable: log_bin_use_v1_row_events     Yes Global No
log-error Yes Yes     No
- Variable: log_error     Yes Global No
log-output Yes Yes     Yes
- Variable: log_output     Yes Global Yes
log-queries-not-using-indexes Yes Yes     Yes
- Variable: log_queries_not_using_indexes     Yes Global Yes
log-slave-updates Yes Yes     No
- Variable: log_slave_updates     Yes Global No
log_slave_updates Yes Yes Yes Global No
log_slow_admin_statements     Yes Global Yes
log-slow-queries Yes Yes     Yes
- Variable: log_slow_queries     Yes Global Yes
log_slow_slave_statements     Yes Global Yes
log_throttle_queries_not_using_indexes     Yes Global Yes
log-warnings Yes Yes     Yes
- Variable: log_warnings     Yes Global Yes
long_query_time Yes Yes Yes Both Yes
low-priority-updates Yes Yes     Yes
- Variable: low_priority_updates     Yes Both Yes
lower_case_file_system     Yes Global No
lower_case_table_names Yes Yes Yes Global No
master_info_repository     Yes Global Yes
master_verify_checksum     Yes Global Yes
max_allowed_packet Yes Yes Yes Global Yes
max_binlog_cache_size Yes Yes Yes Global Yes
max_binlog_size Yes Yes Yes Global Yes
max_binlog_stmt_cache_size Yes Yes Yes Global Yes
max_connect_errors Yes Yes Yes Global Yes
max_connections Yes Yes Yes Global Yes
max_delayed_threads Yes Yes Yes Both Yes
max_error_count Yes Yes Yes Both Yes
max_heap_table_size Yes Yes Yes Both Yes
max_insert_delayed_threads     Yes Both Yes
max_join_size Yes Yes Yes Both Yes
max_length_for_sort_data Yes Yes Yes Both Yes
max_prepared_stmt_count Yes Yes Yes Global Yes
max_relay_log_size Yes Yes Yes Global Yes
max_seeks_for_key Yes Yes Yes Both Yes
max_sort_length Yes Yes Yes Both Yes
max_sp_recursion_depth Yes Yes Yes Both Yes
max_user_connections Yes Yes Yes Both Yes
max_write_lock_count Yes Yes Yes Global Yes
memlock Yes Yes Yes Global No
metadata_locks_cache_size     Yes Global No
metadata_locks_hash_instances     Yes Global No
min-examined-row-limit Yes Yes Yes Both Yes
myisam_data_pointer_size Yes Yes Yes Global Yes
myisam_max_sort_file_size Yes Yes Yes Global Yes
myisam_mmap_size Yes Yes Yes Global No
myisam_recover_options     Yes Global No
myisam_repair_threads Yes Yes Yes Both Yes
myisam_sort_buffer_size Yes Yes Yes Both Yes
myisam_stats_method Yes Yes Yes Both Yes
myisam_use_mmap Yes Yes Yes Global Yes
named_pipe     Yes Global No
ndb_autoincrement_prefetch_sz Yes Yes Yes Both Yes
ndb-batch-size Yes Yes Yes Global No
ndb-blob-read-batch-bytes Yes Yes Yes Both Yes
ndb-blob-write-batch-bytes Yes Yes Yes Both Yes
ndb_cache_check_time Yes Yes Yes Global Yes
ndb-cluster-connection-pool Yes Yes Yes Global No
ndb-deferred-constraints Yes Yes     Yes
- Variable: ndb_deferred_constraints     Yes Both Yes
ndb_deferred_constraints Yes Yes Yes Both Yes
ndb_distribution Yes Yes Yes Global Yes
ndb-distribution Yes Yes     Yes
- Variable: ndb_distribution     Yes Global Yes
ndb_extra_logging Yes Yes Yes Global Yes
ndb_force_send Yes Yes Yes Both Yes
ndb_join_pushdown     Yes Global No
ndb-log-apply-status Yes Yes     No
- Variable: ndb_log_apply_status     Yes Global No
ndb_log_bin Yes   Yes Both Yes
ndb_log_binlog_index Yes   Yes Global Yes
ndb_log_empty_epochs Yes Yes Yes Global Yes
ndb-log-empty-epochs Yes Yes Yes Global Yes
ndb-log-orig Yes Yes     No
- Variable: ndb_log_orig     Yes Global No
ndb-log-transaction-id Yes Yes     No
- Variable: ndb_log_transaction_id     Yes Global No
ndb_log_transaction_id     Yes Global No
ndb-log-update-as-write Yes Yes Yes Global Yes
ndb_log_updated_only Yes Yes Yes Global Yes
ndb_optimization_delay     Yes Global Yes
ndb_recv_thread_cpu_mask     Yes Global Yes
ndb_table_no_logging     Yes Session Yes
ndb_table_temporary     Yes Session Yes
ndb_use_copying_alter_table     Yes Both No
ndb_use_exact_count     Yes Both Yes
ndb_use_transactions Yes Yes Yes Both Yes
ndb-wait-connected Yes Yes Yes Global No
ndb-wait-setup Yes Yes Yes Global No
ndbinfo_database     Yes Global No
ndbinfo_max_bytes Yes   Yes Both Yes
ndbinfo_max_rows Yes   Yes Both Yes
ndbinfo_show_hidden Yes   Yes Both Yes
ndbinfo_table_prefix Yes   Yes Both Yes
ndbinfo_version     Yes Global No
net_buffer_length Yes Yes Yes Both Yes
net_read_timeout Yes Yes Yes Both Yes
net_retry_count Yes Yes Yes Both Yes
net_write_timeout Yes Yes Yes Both Yes
new Yes Yes Yes Both Yes
old Yes Yes Yes Global No
old-alter-table Yes Yes     Yes
- Variable: old_alter_table     Yes Both Yes
old_passwords     Yes Both Yes
open-files-limit Yes Yes     No
- Variable: open_files_limit     Yes Global No
optimizer_join_cache_level Yes Yes Yes Both Yes
optimizer_prune_level Yes Yes Yes Both Yes
optimizer_search_depth Yes Yes Yes Both Yes
optimizer_switch Yes Yes Yes Both Yes
optimizer_trace     Yes Both Yes
optimizer_trace_features     Yes Both Yes
optimizer_trace_limit     Yes Both Yes
optimizer_trace_max_mem_size     Yes Both Yes
optimizer_trace_offset     Yes Both Yes
performance_schema Yes Yes Yes Global No
performance_schema_accounts_size Yes Yes Yes Global No
performance_schema_digests_size Yes Yes Yes Global No
performance_schema_events_stages_history_long_size Yes Yes Yes Global No
performance_schema_events_stages_history_size Yes Yes Yes Global No
performance_schema_events_statements_history_long_size Yes Yes Yes Global No
performance_schema_events_statements_history_size Yes Yes Yes Global No
performance_schema_events_waits_history_long_size Yes Yes Yes Global No
performance_schema_events_waits_history_size Yes Yes Yes Global No
performance_schema_hosts_size Yes Yes Yes Global No
performance_schema_max_cond_classes Yes Yes Yes Global No
performance_schema_max_cond_instances Yes Yes Yes Global No
performance_schema_max_file_classes Yes Yes Yes Global No
performance_schema_max_file_handles Yes Yes Yes Global No
performance_schema_max_file_instances Yes Yes Yes Global No
performance_schema_max_mutex_classes Yes Yes Yes Global No
performance_schema_max_mutex_instances Yes Yes Yes Global No
performance_schema_max_rwlock_classes Yes Yes Yes Global No
performance_schema_max_rwlock_instances Yes Yes Yes Global No
performance_schema_max_socket_classes Yes Yes Yes Global No
performance_schema_max_socket_instances Yes Yes Yes Global No
performance_schema_max_stage_classes Yes Yes Yes Global No
performance_schema_max_statement_classes Yes Yes Yes Global No
performance_schema_max_table_handles Yes Yes Yes Global No
performance_schema_max_table_instances Yes Yes Yes Global No
performance_schema_max_thread_classes Yes Yes Yes Global No
performance_schema_max_thread_instances Yes Yes Yes Global No
performance_schema_session_connect_attrs_size Yes Yes Yes Global No
performance_schema_setup_actors_size Yes Yes Yes Global No
performance_schema_setup_objects_size Yes Yes Yes Global No
performance_schema_users_size Yes Yes Yes Global No
pid-file Yes Yes     No
- Variable: pid_file     Yes Global No
plugin_dir Yes Yes Yes Global No
port Yes Yes Yes Global No
preload_buffer_size Yes Yes Yes Both Yes
profiling     Yes Both Yes
profiling_history_size Yes Yes Yes Both Yes
protocol_version     Yes Global No
proxy_user     Yes Session No
pseudo_slave_mode     Yes Session Yes
pseudo_thread_id     Yes Session Yes
query_alloc_block_size Yes Yes Yes Both Yes
query_cache_limit Yes Yes Yes Global Yes
query_cache_min_res_unit Yes Yes Yes Global Yes
query_cache_size Yes Yes Yes Global Yes
query_cache_type Yes Yes Yes Both Yes
query_cache_wlock_invalidate Yes Yes Yes Both Yes
query_prealloc_size Yes Yes Yes Both Yes
rand_seed1     Yes Session Yes
rand_seed2     Yes Session Yes
range_alloc_block_size Yes Yes Yes Both Yes
read_buffer_size Yes Yes Yes Both Yes
read_only Yes Yes Yes Global Yes
read_rnd_buffer_size Yes Yes Yes Both Yes
relay-log Yes Yes     No
- Variable: relay_log     Yes Global No
relay_log_basename     Yes Global No
relay-log-index Yes Yes     No
- Variable: relay_log_index     Yes Global No
relay_log_index Yes Yes Yes Global No
relay_log_info_file Yes Yes Yes Global No
relay_log_info_repository     Yes Global Yes
relay_log_purge Yes Yes Yes Global Yes
relay_log_recovery Yes Yes Yes Global Yes
relay_log_space_limit Yes Yes Yes Global No
report-host Yes Yes     No
- Variable: report_host     Yes Global No
report-password Yes Yes     No
- Variable: report_password     Yes Global No
report-port Yes Yes     No
- Variable: report_port     Yes Global No
report-user Yes Yes     No
- Variable: report_user     Yes Global No
rpl_semi_sync_master_enabled     Yes Global Yes
rpl_semi_sync_master_timeout     Yes Global Yes
rpl_semi_sync_master_trace_level     Yes Global Yes
rpl_semi_sync_master_wait_no_slave     Yes Global Yes
rpl_semi_sync_slave_enabled     Yes Global Yes
rpl_semi_sync_slave_trace_level     Yes Global Yes
rpl_stop_slave_timeout Yes Yes Yes Global Yes
secure-auth Yes Yes     Yes
- Variable: secure_auth     Yes Global Yes
secure-file-priv Yes Yes     No
- Variable: secure_file_priv     Yes Global No
server-id Yes Yes     Yes
- Variable: server_id     Yes Global Yes
server-id-bits Yes Yes     No
- Variable: server_id_bits     Yes Global No
server_uuid     Yes Global No
sha256_password_private_key_path     Yes Global No
sha256_password_public_key_path     Yes Global No
shared_memory     Yes Global No
shared_memory_base_name     Yes Global No
skip_external_locking Yes Yes Yes Global No
skip-name-resolve Yes Yes     No
- Variable: skip_name_resolve     Yes Global No
skip-networking Yes Yes     No
- Variable: skip_networking     Yes Global No
skip-show-database Yes Yes     No
- Variable: skip_show_database     Yes Global No
slave_allow_batching Yes Yes Yes Global Yes
slave_checkpoint_group Yes Yes Yes Global Yes
slave_checkpoint_period Yes Yes Yes Global Yes
slave_compressed_protocol Yes Yes Yes Global Yes
slave_exec_mode Yes Yes Yes Global Yes
slave-load-tmpdir Yes Yes     No
- Variable: slave_load_tmpdir     Yes Global No
slave_max_allowed_packet     Yes Global Yes
slave-net-timeout Yes Yes     Yes
- Variable: slave_net_timeout     Yes Global Yes
slave_parallel_workers     Yes Global Yes
slave_pending_jobs_size_max     Yes Global Yes
slave_rows_search_algorithms     Yes Global Yes
slave-skip-errors Yes Yes     No
- Variable: slave_skip_errors     Yes Global No
slave_sql_verify_checksum     Yes Global Yes
slave_transaction_retries Yes Yes Yes Global Yes
slave_type_conversions Yes Yes Yes Global No
slow_launch_time Yes Yes Yes Global Yes
slow-query-log Yes Yes     Yes
- Variable: slow_query_log     Yes Global Yes
slow_query_log_file Yes Yes Yes Global Yes
socket Yes Yes Yes Global No
sort_buffer_size Yes Yes Yes Both Yes
sql_auto_is_null     Yes Both Yes
sql_big_selects     Yes Both Yes
sql_big_tables     Yes Both Yes
sql_buffer_result     Yes Both Yes
sql_log_bin     Yes Both Yes
sql_log_off     Yes Both Yes
sql_low_priority_updates     Yes Both Yes
sql_max_join_size     Yes Both Yes
sql-mode Yes Yes     Yes
- Variable: sql_mode     Yes Both Yes
sql_notes     Yes Both Yes
sql_quote_show_create     Yes Both Yes
sql_safe_updates     Yes Both Yes
sql_select_limit     Yes Both Yes
sql_slave_skip_counter     Yes Global Yes
sql_warnings     Yes Both Yes
ssl-ca Yes Yes     No
- Variable: ssl_ca     Yes Global No
ssl-capath Yes Yes     No
- Variable: ssl_capath     Yes Global No
ssl-cert Yes Yes     No
- Variable: ssl_cert     Yes Global No
ssl-cipher Yes Yes     No
- Variable: ssl_cipher     Yes Global No
ssl-crl Yes Yes     No
- Variable: ssl_crl     Yes Global No
ssl-crlpath Yes Yes     No
- Variable: ssl_crlpath     Yes Global No
ssl-key Yes Yes     No
- Variable: ssl_key     Yes Global No
storage_engine     Yes Both Yes
stored_program_cache Yes Yes Yes Global Yes
sync_binlog Yes Yes Yes Global Yes
sync_frm Yes Yes Yes Global Yes
sync_master_info Yes Yes Yes Global Yes
sync_relay_log Yes Yes Yes Global Yes
sync_relay_log_info Yes Yes Yes Global Yes
system_time_zone     Yes Global No
table_definition_cache     Yes Global Yes
table_open_cache     Yes Global Yes
table_open_cache_instances     Yes Global No
thread_cache_size Yes Yes Yes Global Yes
thread_concurrency Yes Yes Yes Global No
thread_handling Yes Yes Yes Global No
thread_pool_algorithm Yes Yes Yes Global No
thread_pool_high_priority_connection Yes Yes Yes Both Yes
thread_pool_max_unused_threads Yes Yes Yes Global Yes
thread_pool_prio_kickup_timer Yes Yes Yes Both Yes
thread_pool_size Yes Yes Yes Global No
thread_pool_stall_limit Yes Yes Yes Global Yes
thread_stack Yes Yes Yes Global No
time_format     Yes Global No
time_zone     Yes Both Yes
timed_mutexes Yes Yes Yes Global Yes
timestamp     Yes Session Yes
tmp_table_size Yes Yes Yes Both Yes
tmpdir Yes Yes Yes Global No
transaction_alloc_block_size Yes Yes Yes Both Yes
transaction_allow_batching     Yes Session Yes
transaction_prealloc_size Yes Yes Yes Both Yes
tx_isolation     Yes Both Yes
tx_read_only     Yes Both Yes
unique_checks     Yes Both Yes
updatable_views_with_limit Yes Yes Yes Both Yes
validate_password_dictionary_file     Yes Global No
validate_password_length     Yes Global Yes
validate_password_mixed_case_count     Yes Global Yes
validate_password_number_count     Yes Global Yes
validate_password_policy     Yes Global Yes
validate_password_special_char_count     Yes Global Yes
validate_user_plugins     Yes Global No
version     Yes Global No
version_comment     Yes Global No
version_compile_machine     Yes Global No
version_compile_os     Yes Global No
wait_timeout Yes Yes Yes Both Yes
warning_count     Yes Session No

[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.

官方链接:Server System Variables

MySql启用SQL trace追踪SQL语句的操作

MitchellChu 2013-09-14 其他技术

在MsSQL Server中我们要追踪SQL,只需要开启MS SQL中的(SQL Server Profiler)就可以了,而且设置非常的方便。但在MySQL中要实现类似功能需要做些处理,而且操作也比较繁琐 —— 鼠标党,被MS毁了。

要启用MySQL的,需要启用查询日志功能,各个版本操作不一,下面简要说明:

在MySql5.1.12之前

在my.cnf或者my.ini中的[mysqld]节下配置:
log = /path/for/file/live-query.log

 而在MySql 5.1.12中,你需要在上述配置文件中的[mysqld]中配置:

general_log=1

5.1.29中,包括之后版本(本文处在:5.6.11版本),你就需要再加上一句:

general_log_file = /path/for/file/live-query.log

 而这些所有的参数都是可以在线设置的,也就是说,如果你不想因为修改设置而重启MySql的话,那么在线设置应该是个更好的选择,在进入MySql的操作中:

--这里按照最新版本来设置
--查询关于log的设置
SHOW VARIABLES LIKE 'general_log%';
/×
返回结果类似:
+------------------+------------------------------------------------------------
------------------+
| Variable_name    | Value
                  |
+------------------+------------------------------------------------------------
------------------+
| general_log      | OFF
                  |
| general_log_file | dev-mc.log 
                  |
+------------------+------------------------------------------------------------
------------------+
2 rows in set (0.00 sec)

注意,general_log_file 的默认值是'机器名'.log
*/
-- 开启日志
SET GLOBAL general_log ='ON';
-- 设置日志的文件路径:本例在Windows下
SET GLOBAL general_log_file='D:/MySql/live-query.log';
-- 设置完之后,就已经生效了

 

异常处理:必须使用适当的属性或方法修改此标头

MitchellChu 2013-09-03 .NET技术

(v4.0)中,使用HttpWebRequest请求Web页面,当向HttpWebRequest的Headers的标头集合中添加Referer,Host这类HTTP标头(Header)时,会收到:System.Argument: 必须使用适当的属性或方法修改此标头。这样的报错!

这个问题的引起在官方的说法是:

通常通过 WebRequest.Headers 或 WebResponse.Headers 访问 WebHeaderCollection 类。
某些公共标头被视为受限制的,它们或者直接由 API(如 Content-Type)公开,或者受到系统保护,不能被更改。
受限制的标头是:

Accept Connection Content-Length Content-Type Date Expect Host If-Modified-Since Range Referer Transfer-Encoding User-Agent Proxy-Connection 

要解决这个问题可以用以下这些方法:

方法一: 通过HttpWebRequest/HttpWebResponse对象的相应属性来设置这些表头

HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://blog.useasp.net/");
 request.Host = "http://blog.useasp.net/";
 request.Referer = "http://blog.useasp.net/tags/.net";

 /* 你可以在这里设置其他限制的标头. 注意: Range HTTP标头是通过AddRange来添加 If-Modified-Since HTTP标头通过IfModifiedSince 属性设置 Accept由 Accept 属性设置。 Connection由 Connection 属性和 KeepAlive 属性设置。 Content-Length由 ContentLength 属性设置。 Content-Type由 ContentType 属性设置。 Expect由 Expect 属性设置。 Date由 Date属性设置,默认为系统的当前时间。 Host由系统设置为当前主机信息。 Referer由 Referer 属性设置。 Transfer-Encoding由 TransferEncoding 属性设置(SendChunked 属性必须为 true)。 User-Agent由 UserAgent 属性设置。 */

 using((HttpWebResponse)response = (HttpWebResponse)request.GetResponse()) {
 // do something here too. by Mitchell Chu
 }

要将所有的这些受限HTTP标头在写程序的时候记住,还是有点难度的,我们更希望能够自动判定那些是可以直接添加的,哪些是需要特殊处理的。在WebHeaderCollection中有个方法IsRestricted正好是解决这个问题的。因此上面的代码可以改为将所有需要的标头添加到WebHeaderCollection,在为HttpWebRequest添加标头的时候,再使用IsRestricted来确定特殊处理与否。

foreach (string key in headers.AllKeys) {
     if (!WebHeaderCollection.IsRestricted(key))
         request.Headers.Add(key, headers[key]);
     else
     {
         // do some thing, use HttpWebRequest propertiers to add restricted http header.
     }
 }

 

方法二: 继承WebHeaderCollection,通过AddWithoutValidate方法来添加表头(此方法在v4不适合,见评论)

public class InheritWebHeaders : WebHeaderCollection {
     /*     define your properties & methods here. -- by Mitchell Chu     */
     public void AddHeaderWithoutValidate(
         string name
         , string value)
     {
         base.AddWithoutValidate(name, value);
     }
 }
  // usage: HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://blog.useasp.net/"); 
InheritWebHeaders headers = new InheritWebHeaders();
 headers.AddHeaderWithoutValidate("Referrer", "http://blog.useasp.net/tag/.net");
 // other HTTP Headers request.Headers = headers;
 /// do more here...

方法三: 好吧,暂时还没有想到,如果你知道,请分享下。。。

关于博主

  一枚成分复杂的网络IT分子,属于互联网行业分类中的杂牌军。