首页 > 代码库 > 一个命令查看mysql的所有配置(原创)

一个命令查看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的所有配置(原创)