This is really useful for me because I work with dozens of different database servers. The first thing I do is run this command and paste it into the servers /etc/my.cnf file. That way I will always know the original value and it just makes life much easier.
$ mysql -NBe 'SHOW VARIABLES' | sed 's,\t,^=,' | column -ts^ | tr "\n" '@' | eval $(echo "sed '" "s,@\("{a..z}"\),\n\n\1,;" "'") | tr '@' "\n" | sed 's,^,# ,g'
Run this ( setup your ~/.my.cnf file first ) in bash and it will save the output in ~/mysql-variables.log, suitable for instantly pasting in the main server conf file with vim.
{ echo -e "# MYSQL VARIABLES {{{1\n##\n# MYSQL `mysql -V|sed 's,^.*\(V.*\)\, for.*,\1,'` - By: `logname`@`hostname -f` on `date +%c`\n##"; for l in {a..z}; do echo '#'; mysql -NBe "SHOW GLOBAL VARIABLES LIKE '${l}%'" | sed 's,\t,^= ,' | column -ts^ | tr "\n" '@' | eval $(echo "sed '" "s,@\("{a..u}{a..z}"\),\n\n\1,;" "'") | eval $(echo "sed '" "s,@\(innodb_"{a..z}{a..z}"\),\n\n\1,;" "'") | tr '@' "\n" | sed 's,^,# ,g'; done; echo -e "#\n##\n# MYSQL VARIABLES }}}1"; } | tee ~/mysql-variables.log
Newlines before each group!
# MYSQL VARIABLES {{{1
##
# MYSQL Ver 15.1 Distrib 5.5.31-MariaDB - By: srot@host.askapacherack.com on Wed Jul 10 20:53:49 2013
##
#
# aria_block_size = 8192
#
# aria_checkpoint_interval = 30
{ echo -e "# MYSQL VARIABLES @ `hostname -f` ON `date +%c` {{{1\n#######"; mysql -NBe 'SHOW VARIABLES' | sed 's,\t,^=,' | column -ts^ | tr "\n" '@' | eval $(echo "sed '" "s,@\("{a..z}"\),\n\n\1,;" "'") | tr '@' "\n" | sed 's,^,# ,g'; echo -e "######\n# MYSQL VARIABLES }}}1"; }
mysql -NBe 'SHOW VARIABLES'sed 's,\t,^=,'column -ts^tr "\n" '@'eval $(echo "sed '" "s,@\("{a..z}"\),\n\n\1,;" "'")tr '@' "\n"sed 's,^,# ,g'# mysql INFORMATION_SCHEMA -tBe "SELECT * FROM GLOBAL_VARIABLES"
+--------------------------+-------------------------------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------+-------------------------------------------------------+ | CHARACTER_SETS_DIR | /usr/share/mysql/charsets/ | | CHARACTER_SET_RESULTS | utf8 | | CHARACTER_SET_CONNECTION | utf8 | | COLLATION_DATABASE | utf8_general_ci | | CHARACTER_SET_SERVER | utf8 | | COLLATION_SERVER | utf8_general_ci | | CHARACTER_SET_SYSTEM | utf8 | | CHARACTER_SET_FILESYSTEM | binary | | CHARACTER_SET_DATABASE | utf8 | | CHARACTER_SET_CLIENT | utf8 | | INIT_CONNECT | SET collation_connection = utf8_general_ci,NAMES utf8 | | COLLATION_CONNECTION | utf8_general_ci | +--------------------------+-------------------------------------------------------+
# mysql -tBe "SHOW VARIABLES"
+--------------------------+-------------------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | | init_connect | SET collation_connection = utf8_general_ci,NAMES utf8 | +--------------------------+-------------------------------------------------------+
Paste this into your servers /etc/my.cnf file.
# auto_increment_increment = 1 # # # auto_increment_offset = 1 # autocommit = ON # automatic_sp_privileges = ON # # # back_log = 100 # basedir = /usr # big_tables = OFF # binlog_cache_size = 32768 # binlog_direct_non_transactional_updates = OFF # binlog_format = STATEMENT # binlog_stmt_cache_size = 32768 # bulk_insert_buffer_size = 8388608 # # # character_set_client = latin1 # character_set_connection = latin1 # character_set_database = utf8 # character_set_filesystem = binary # character_set_results = latin1 # character_set_server = utf8 # character_set_system = utf8 # character_sets_dir = /usr/share/mysql/charsets/ # collation_connection = latin1_swedish_ci # collation_database = utf8_general_ci # collation_server = utf8_general_ci # completion_type = NO_CHAIN # concurrent_insert = AUTO # connect_timeout = 10 # # # datadir = /var/lib/mysql/ # date_format = %Y-%m-%d # datetime_format = %Y-%m-%d %H:%i:%s # default_storage_engine = InnoDB # 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 = 7 # external_user = # # # flush = OFF # flush_time = 0 # 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 = OFF # general_log_file = /var/lib/mysqllogs/general-log # group_concat_max_len = 1024 # # # have_compress = YES # have_crypt = YES # have_csv = YES # have_dynamic_loading = YES # have_geometry = YES # have_innodb = YES # have_ndbcluster = NO # have_openssl = DISABLED # have_partitioning = YES # have_profiling = YES # have_query_cache = YES # have_rtree_keys = YES # have_ssl = DISABLED # have_symlink = YES # hostname = killerdbees.ouch.node5.net # # # identity = 0 # ignore_builtin_innodb = OFF # init_connect = SET collation_connection = utf8_general_ci,NAMES utf8 # init_file = # init_slave = # innodb_adaptive_flushing = ON # innodb_adaptive_hash_index = ON # innodb_additional_mem_pool_size = 8388608 # innodb_autoextend_increment = 8 # innodb_autoinc_lock_mode = 1 # innodb_buffer_pool_instances = 1 # innodb_buffer_pool_size = 157286400 # innodb_change_buffering = all # 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_format = Antelope # innodb_file_format_check = ON # innodb_file_format_max = Antelope # innodb_file_per_table = OFF # innodb_flush_log_at_trx_commit = 2 # innodb_flush_method = # innodb_force_load_corrupted = OFF # innodb_force_recovery = 0 # innodb_io_capacity = 200 # innodb_large_prefix = OFF # innodb_lock_wait_timeout = 50 # innodb_locks_unsafe_for_binlog = OFF # innodb_log_buffer_size = 8388608 # innodb_log_file_size = 5242880 # innodb_log_files_in_group = 2 # innodb_log_group_home_dir = ./ # innodb_max_dirty_pages_pct = 75 # innodb_max_purge_lag = 0 # innodb_mirrored_log_groups = 1 # innodb_old_blocks_pct = 37 # innodb_old_blocks_time = 0 # innodb_open_files = 300 # innodb_print_all_deadlocks = OFF # innodb_purge_batch_size = 20 # innodb_purge_threads = 0 # innodb_random_read_ahead = OFF # innodb_read_ahead_threshold = 56 # innodb_read_io_threads = 4 # innodb_replication_delay = 0 # innodb_rollback_on_timeout = OFF # innodb_rollback_segments = 128 # innodb_spin_wait_delay = 6 # innodb_stats_method = nulls_equal # innodb_stats_on_metadata = ON # innodb_stats_sample_pages = 8 # innodb_strict_mode = OFF # innodb_support_xa = ON # innodb_sync_spin_loops = 30 # innodb_table_locks = ON # innodb_thread_concurrency = 0 # innodb_thread_sleep_delay = 10000 # innodb_use_native_aio = ON # innodb_use_sys_malloc = ON # innodb_version = 5.5.30 # innodb_write_io_threads = 4 # insert_id = 0 # interactive_timeout = 36000 # # # join_buffer_size = 1048576 # # # keep_files_on_create = OFF # key_buffer_size = 134217728 # key_cache_age_threshold = 300 # key_cache_block_size = 1024 # key_cache_division_limit = 100 # # # large_files_support = ON # large_page_size = 0 # large_pages = OFF # last_insert_id = 0 # lc_messages = en_US # lc_messages_dir = /usr/share/mysql/ # lc_time_names = en_US # license = GPL # local_infile = ON # lock_wait_timeout = 31536000 # locked_in_memory = OFF # log = OFF # log_bin = ON # log_bin_trust_function_creators = OFF # log_error = /var/log/mysqld.log # log_output = FILE # log_queries_not_using_indexes = OFF # log_slave_updates = OFF # log_slow_queries = ON # log_warnings = 1 # long_query_time = 5.000000 # low_priority_updates = OFF # lower_case_file_system = OFF # lower_case_table_names = 0 # # # max_allowed_packet = 536870912 # max_binlog_cache_size = 18446744073709547520 # max_binlog_size = 1073741824 # max_binlog_stmt_cache_size = 18446744073709547520 # max_connect_errors = 10000 # max_connections = 500 # max_delayed_threads = 20 # max_error_count = 64 # max_heap_table_size = 157286400 # max_insert_delayed_threads = 20 # max_join_size = 18446744073709551615 # max_length_for_sort_data = 1024 # max_long_data_size = 536870912 # max_prepared_stmt_count = 16382 # max_relay_log_size = 0 # max_seeks_for_key = 18446744073709551615 # max_sort_length = 1024 # max_sp_recursion_depth = 0 # max_tmp_tables = 32 # max_user_connections = 0 # max_write_lock_count = 18446744073709551615 # metadata_locks_cache_size = 1024 # min_examined_row_limit = 0 # multi_range_count = 256 # myisam_data_pointer_size = 6 # myisam_max_sort_file_size = 9223372036853727232 # myisam_mmap_size = 18446744073709551615 # myisam_recover_options = OFF # myisam_repair_threads = 1 # myisam_sort_buffer_size = 12582912 # myisam_stats_method = nulls_unequal # myisam_use_mmap = 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 = 40000 # optimizer_prune_level = 1 # optimizer_search_depth = 62 # # # performance_schema = OFF # performance_schema_events_waits_history_long_size = 10000 # performance_schema_events_waits_history_size = 10 # performance_schema_max_cond_classes = 80 # performance_schema_max_cond_instances = 1000 # performance_schema_max_file_classes = 50 # performance_schema_max_file_handles = 32768 # performance_schema_max_file_instances = 10000 # performance_schema_max_mutex_classes = 200 # performance_schema_max_mutex_instances = 1000000 # performance_schema_max_rwlock_classes = 30 # performance_schema_max_rwlock_instances = 1000000 # performance_schema_max_table_handles = 100000 # performance_schema_max_table_instances = 50000 # performance_schema_max_thread_classes = 50 # performance_schema_max_thread_instances = 1000 # pid_file = /var/run/mysqld/mysqld.pid # plugin_dir = /usr/lib64/mysql/plugin/ # port = 3306 # preload_buffer_size = 32768 # profiling = OFF # profiling_history_size = 15 # protocol_version = 10 # proxy_user = # pseudo_slave_mode = OFF # pseudo_thread_id = 40992 # # # query_alloc_block_size = 8192 # query_cache_limit = 1048576 # query_cache_min_res_unit = 4096 # query_cache_size = 2097152 # query_cache_type = ON # query_cache_wlock_invalidate = OFF # query_prealloc_size = 8192 # # # rand_seed1 = 0 # rand_seed2 = 0 # range_alloc_block_size = 4096 # read_buffer_size = 4194304 # read_only = OFF # read_rnd_buffer_size = 5242880 # relay_log = # relay_log_index = # relay_log_info_file = relay-log.info # relay_log_purge = ON # relay_log_recovery = OFF # 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 = 224 # 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 = /var/lib/mysqltmp # slave_max_allowed_packet = 1073741824 # slave_net_timeout = 3600 # slave_skip_errors = OFF # slave_transaction_retries = 10 # slave_type_conversions = # slow_launch_time = 2 # slow_query_log = ON # slow_query_log_file = /var/lib/mysqllogs/slow-log # socket = /var/lib/mysql/mysql.sock # sort_buffer_size = 5242880 # sql_auto_is_null = OFF # sql_big_selects = ON # sql_big_tables = OFF # sql_buffer_result = OFF # sql_log_bin = ON # sql_log_off = OFF # sql_low_priority_updates = OFF # sql_max_join_size = 18446744073709551615 # sql_mode = NO_ENGINE_SUBSTITUTION # sql_notes = ON # sql_quote_show_create = ON # sql_safe_updates = OFF # sql_select_limit = 18446744073709551615 # sql_slave_skip_counter = 0 # sql_warnings = OFF # ssl_ca = # ssl_capath = # ssl_cert = # ssl_cipher = # ssl_key = # storage_engine = InnoDB # stored_program_cache = 256 # sync_binlog = 0 # sync_frm = ON # sync_master_info = 0 # sync_relay_log = 0 # sync_relay_log_info = 0 # system_time_zone = EDT # # # table_definition_cache = 1200 # table_open_cache = 1000 # thread_cache_size = 150 # thread_concurrency = 10 # thread_handling = one-thread-per-connection # thread_stack = 262144 # time_format = %H:%i:%s # time_zone = SYSTEM # timed_mutexes = OFF # timestamp = 1365185527 # tmp_table_size = 157286400 # tmpdir = /var/lib/mysqltmp # transaction_alloc_block_size = 8192 # transaction_prealloc_size = 4096 # tx_isolation = REPEATABLE-READ # # # unique_checks = ON # updatable_views_with_limit = YES # # # version = 5.5.30-log # version_comment = Distributed by The IUS Community Project # version_compile_machine = x86_64 # version_compile_os = Linux # # # wait_timeout = 36000 # warning_count = 0
Just a listing of the ones being used on my killerdbees server.