一个命令查看mysql的所有配置（原创）

在mysql的命令提示符下，执行下面一句话：

mysql> show global variables

得到：

上表的文本内容：

"Variable_name"
"Value"
"auto_increment_increment"
"1"
"auto_increment_offset"
"1"
"autocommit"
"ON"
"automatic_sp_privileges"
"ON"
"back_log"
"50"
"basedir"
"C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\"
"big_tables"
"OFF"
"binlog_cache_size"
"32768"
"binlog_direct_non_transactional_updates"
"OFF"
"binlog_format"
"STATEMENT"
"bulk_insert_buffer_size"
"8388608"
"character_set_client"
"utf8"
"character_set_connection"
"utf8"
"character_set_database"
"utf8"
"character_set_filesystem"
"binary"
"character_set_results"
"utf8"
"character_set_server"
"utf8"
"character_set_system"
"utf8"
"character_sets_dir"
"C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\share\\charsets\\"
"collation_connection"
"utf8_general_ci"
"collation_database"
"utf8_general_ci"
"collation_server"
"utf8_general_ci"
"completion_type"
"0"
"concurrent_insert"
"1"
"connect_timeout"
"10"
"datadir"
"D:\\ProgramData\\MySQL\\MySQL Server 5.1\\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"
"div_precision_increment"
"4"
"engine_condition_pushdown"
"ON"
"error_count"
"0"
"event_scheduler"
"OFF"
"expire_logs_days"
"0"
"flush"
"OFF"
"flush_time"
"1800"
"foreign_key_checks"
"ON"
"ft_boolean_syntax"
"+ -><()~*:""""&|"
"ft_max_word_len"
"84"
"ft_min_word_len"
"4"
"ft_query_expansion_limit"
"20"
"ft_stopword_file"
"(built-in)"
"general_log"
"ON"
"general_log_file"
"D:\\ProgramData\\MySQL\\MySQL Server 5.1\\Data\\WIN-2736PK059N6.log"
"group_concat_max_len"
"102400"
"have_community_features"
"YES"
"have_compress"
"YES"
"have_crypt"
"NO"
"have_csv"
"YES"
"have_dynamic_loading"
"YES"
"have_geometry"
"YES"
"have_innodb"
"YES"
"have_ndbcluster"
"NO"
"have_openssl"
"DISABLED"
"have_partitioning"
"YES"
"have_query_cache"
"YES"
"have_rtree_keys"
"YES"
"have_ssl"
"DISABLED"
"have_symlink"
"YES"
"hostname"
"WIN-2736PK059N6"
"identity"
"0"
"ignore_builtin_innodb"
"OFF"
"init_connect"
"SET NAMES utf8"
"init_file"
""
"init_slave"
""
"innodb_adaptive_hash_index"
"ON"
"innodb_additional_mem_pool_size"
"8388608"
"innodb_autoextend_increment"
"8"
"innodb_autoinc_lock_mode"
"1"
"innodb_buffer_pool_size"
"378535936"
"innodb_checksums"
"ON"
"innodb_commit_concurrency"
"0"
"innodb_concurrency_tickets"
"500"
"innodb_data_file_path"
"ibdata1:10M:autoextend"
"innodb_data_home_dir"
""
"innodb_doublewrite"
"ON"
"innodb_fast_shutdown"
"1"
"innodb_file_io_threads"
"4"
"innodb_file_per_table"
"OFF"
"innodb_flush_log_at_trx_commit"
"1"
"innodb_flush_method"
""
"innodb_force_recovery"
"0"
"innodb_lock_wait_timeout"
"50"
"innodb_locks_unsafe_for_binlog"
"OFF"
"innodb_log_buffer_size"
"4194304"
"innodb_log_file_size"
"76546048"
"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_rollback_on_timeout"
"OFF"
"innodb_stats_on_metadata"
"ON"
"innodb_support_xa"
"ON"
"innodb_sync_spin_loops"
"20"
"innodb_table_locks"
"ON"
"innodb_thread_concurrency"
"8"
"innodb_thread_sleep_delay"
"10000"
"innodb_use_legacy_cardinality_algorithm"
"ON"
"insert_id"
"0"
"interactive_timeout"
"288000"
"join_buffer_size"
"131072"
"keep_files_on_create"
"OFF"
"key_buffer_size"
"195035136"
"key_cache_age_threshold"
"300"
"key_cache_block_size"
"1024"
"key_cache_division_limit"
"100"
"language"
"C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\share\\english\\"
"large_files_support"
"ON"
"large_page_size"
"0"
"large_pages"
"OFF"
"last_insert_id"
"0"
"lc_time_names"
"en_US"
"license"
"GPL"
"local_infile"
"ON"
"log"
"ON"
"log_bin"
"OFF"
"log_bin_trust_function_creators"
"OFF"
"log_bin_trust_routine_creators"
"OFF"
"log_error"
"D:\\ProgramData\\MySQL\\MySQL Server 5.1\\Data\\WIN-2736PK059N6.err"
"log_output"
"FILE"
"log_queries_not_using_indexes"
"OFF"
"log_slave_updates"
"OFF"
"log_slow_queries"
"ON"
"log_warnings"
"1"
"long_query_time"
"3.000000"
"low_priority_updates"
"OFF"
"lower_case_file_system"
"ON"
"lower_case_table_names"
"1"
"max_allowed_packet"
"524288000"
"max_binlog_cache_size"
"4294963200"
"max_binlog_size"
"1073741824"
"max_connect_errors"
"10"
"max_connections"
"1510"
"max_delayed_threads"
"20"
"max_error_count"
"64"
"max_heap_table_size"
"16777216"
"max_insert_delayed_threads"
"20"
"max_join_size"
"18446744073709551615"
"max_length_for_sort_data"
"1024"
"max_prepared_stmt_count"
"16382"
"max_relay_log_size"
"0"
"max_seeks_for_key"
"4294967295"
"max_sort_length"
"1024"
"max_sp_recursion_depth"
"0"
"max_tmp_tables"
"32"
"max_user_connections"
"0"
"max_write_lock_count"
"4294967295"
"min_examined_row_limit"
"0"
"multi_range_count"
"256"
"myisam_data_pointer_size"
"6"
"myisam_max_sort_file_size"
"107374182400"
"myisam_mmap_size"
"4294967295"
"myisam_recover_options"
"OFF"
"myisam_repair_threads"
"1"
"myisam_sort_buffer_size"
"23068672"
"myisam_stats_method"
"nulls_unequal"
"myisam_use_mmap"
"OFF"
"named_pipe"
"OFF"
"net_buffer_length"
"16384"
"net_read_timeout"
"30"
"net_retry_count"
"10"
"net_write_timeout"
"60"
"new"
"OFF"
"old"
"OFF"
"old_alter_table"
"OFF"
"old_passwords"
"OFF"
"open_files_limit"
"2048"
"optimizer_prune_level"
"1"
"optimizer_search_depth"
"62"
"optimizer_switch"
"index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on"
"pid_file"
"D:\\ProgramData\\MySQL\\MySQL Server 5.1\\Data\\WIN-2736PK059N6.pid"
"plugin_dir"
"C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\lib/plugin"
"port"
"3306"
"preload_buffer_size"
"32768"
"profiling"
"OFF"
"profiling_history_size"
"15"
"protocol_version"
"10"
"pseudo_thread_id"
"0"
"query_alloc_block_size"
"8192"
"query_cache_limit"
"1048576"
"query_cache_min_res_unit"
"4096"
"query_cache_size"
"127926272"
"query_cache_type"
"ON"
"query_cache_wlock_invalidate"
"OFF"
"query_prealloc_size"
"8192"
"rand_seed1"
""
"rand_seed2"
""
"range_alloc_block_size"
"4096"
"read_buffer_size"
"65536"
"read_only"
"OFF"
"read_rnd_buffer_size"
"262144"
"relay_log"
""
"relay_log_index"
""
"relay_log_info_file"
"relay-log.info"
"relay_log_purge"
"ON"
"relay_log_space_limit"
"0"
"report_host"
""
"report_password"
""
"report_port"
"3306"
"report_user"
""
"rpl_recovery_rank"
"0"
"secure_auth"
"OFF"
"secure_file_priv"
""
"server_id"
"0"
"shared_memory"
"OFF"
"shared_memory_base_name"
"MYSQL"
"skip_external_locking"
"ON"
"skip_name_resolve"
"ON"
"skip_networking"
"OFF"
"skip_show_database"
"OFF"
"slave_compressed_protocol"
"OFF"
"slave_exec_mode"
"STRICT"
"slave_load_tmpdir"
"C:\\Windows\\TEMP"
"slave_net_timeout"
"3600"
"slave_skip_errors"
"OFF"
"slave_transaction_retries"
"10"
"slow_launch_time"
"2"
"slow_query_log"
"ON"
"slow_query_log_file"
"C:\\Program Files (x86)\\MySQL\\MySQL Server 5.1\\mysqllog/slowquery.log"
"socket"
"MySQL"
"sort_buffer_size"
"262144"
"sql_auto_is_null"
"ON"
"sql_big_selects"
"ON"
"sql_big_tables"
"OFF"
"sql_buffer_result"
"OFF"
"sql_log_bin"
"ON"
"sql_log_off"
"OFF"
"sql_log_update"
"ON"
"sql_low_priority_updates"
"OFF"
"sql_max_join_size"
"18446744073709551615"
"sql_mode"
"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
"sql_notes"
"ON"
"sql_quote_show_create"
"ON"
"sql_safe_updates"
"OFF"
"sql_select_limit"
"18446744073709551615"
"sql_slave_skip_counter"
""
"sql_warnings"
"OFF"
"ssl_ca"
""
"ssl_capath"
""
"ssl_cert"
""
"ssl_cipher"
""
"ssl_key"
""
"storage_engine"
"InnoDB"
"sync_binlog"
"0"
"sync_frm"
"ON"
"system_time_zone"
""
"table_definition_cache"
"256"
"table_lock_wait_timeout"
"50"
"table_open_cache"
"264"
"table_type"
"InnoDB"
"thread_cache_size"
"64"
"thread_handling"
"one-thread-per-connection"
"thread_stack"
"196608"
"time_format"
"%H:%i:%s"
"time_zone"
"SYSTEM"
"timed_mutexes"
"OFF"
"timestamp"
"1484874972"
"tmp_table_size"
"23068672"
"tmpdir"
"C:\\Windows\\TEMP"
"transaction_alloc_block_size"
"8192"
"transaction_prealloc_size"
"4096"
"tx_isolation"
"REPEATABLE-READ"
"unique_checks"
"ON"
"updatable_views_with_limit"
"YES"
"version"
"5.1.53-community-log"
"version_comment"
"MySQL Community Server (GPL)"
"version_compile_machine"
"ia32"
"version_compile_os"
"Win32"
"wait_timeout"
"288000"
"warning_count"
"1"

如果需要在众多的全局变量中，找到某一个变量的值的办法是在上边的命令后面使用 “LIKE %需要的特征值%”模糊查找包含特征值的全局配置。

例如：

1.mysql查看数据库的数据存放目录命令：

>mysql> show global variables like "%datadir%";

一个命令查看mysql的所有配置（原创）

标签：stop   dap   shared   flush   tput   share   repair   sql   rem