MySQL Configuration File Variable Tutorial-Part 1

Today I am going to explain the MySQL server configuration file variable. MySQL Server (mysqld) is the main program that does most of the work in a MySQL installation. When MySQL Server starts it uses the configuration file. The first thing you need to know is where MySQL gets configuration information: from command-line arguments and settings in its configuration file. On Unix-like systems, the configuration file is generally located at /etc/my.cnf or /etc/mysql/my.cnf. On windows system the configuration file generally located at mysql/bin/my.ini or if you have xampp server than it will be at xampp/mysql/bin/my.ini; If you use your operating system’s start-up scripts, this is typically the only place you’ll specify configuration settings. If you start MySQL manually, as you might do when you’re running a test installation, you can also specify settings on the command line. Below is the list of configuration variables:

Name	Variable  Scope	Dynamic
abort-slave-event-count
allow-suspicious-udfs
ansi
auto_increment_increment	Both	Yes
auto_increment_offset	Both	Yes
back_log	Global	No
backup_history_log	Global	Yes
backup_history_log_file	Global	Yes
backup_progress_log	Global	Yes
backup_progress_log_file	Global	Yes
backupdir	Global	Yes
basedir	Global	No
big-tables	Session	Yes
bind-address
binlog_cache_size	Global	Yes
binlog-do-db
binlog-format	Both	Yes
binlog-ignore-db
binlog-row-event-max-size
bootstrap
bulk_insert_buffer_size	Both	Yes
character-set-client-handshake
character-set-filesystem	Both	Yes
character-set-server	Both	Yes
character-sets-dir	Global	No
chroot
collation-server	Both	Yes
completion_type	Both	Yes
concurrent_insert	Global	Yes
connect_timeout	Global	Yes
console
core-file
datadir	Global	No
datetime_format	Both	Yes
debug	Both	Yes
debug-sync-timeout
default-storage-engine
default-table-type
default-time-zone
default_week_format	Both	Yes
delay-key-write	Global	Yes
delayed_insert_limit	Global	Yes
delayed_insert_timeout	Global	Yes
delayed_queue_size	Global	Yes
des-key-file
disconnect-slave-event-count
div_precision_increment	Both	Yes
enable-locking
enable-named-pipe
enable-pstack
engine-condition-pushdown	Both	Yes
event-scheduler	Global	Yes
exit-info
expire_logs_days	Global	Yes
external-locking
falcon
falcon_checkpoint_schedule	Global	Yes
falcon_checksums	Global	Yes
falcon_consistent_read	Both	Yes
falcon_debug_mask	Global	Yes
falcon_debug_server	Global	No
falcon_disable_fsync	Global	Yes
falcon_gopher_threads	Global	No
falcon_index_chill_threshold	Global	Yes
falcon_initial_allocation	Global	Yes
falcon_io_threads	Global	Yes
falcon_large_blob_threshold	Global	No
falcon_lock_wait_timeout	Global	Yes
falcon_max_transaction_backlog	Global	Yes
falcon_page_cache_size	Global	No
falcon_page_size	Global	No
falcon_record_chill_threshold	Global	Yes
falcon_record_memory_max	Global	Yes
falcon_record_scavenge_floor	Global	Yes
falcon_record_scavenge_threshold	Global	Yes
falcon_scavenge_schedule	Global	No
falcon_serial_log_buffers	Global	No
falcon_serial_log_dir	Global	No
falcon_serial_log_priority	Global	Yes
falcon_support_xa	Global	No
falcon_use_deferred_index_hash	Global	No
falcon_use_sectorcache	Global	No
falcon_use_supernodes	Global	No
flush	Global	Yes
flush_time	Global	Yes
ft_boolean_syntax	Global	Yes
ft_max_word_len	Global	No
ft_min_word_len	Global	No
ft_query_expansion_limit	Global	No
ft_stopword_file	Global	No
gdb
general-log	Global	Yes
general_log_file	Global	Yes
group_concat_max_len	Both	Yes
help
init_connect	Global	Yes
init-file	Global	No
init_slave	Global	Yes
innodb
innodb_adaptive_hash_index	Global	No
innodb_additional_mem_pool_size	Global	No
innodb_autoextend_increment	Global	Yes
innodb_autoinc_lock_mode	Global	No
innodb_buffer_pool_size	Global	No
innodb_checksums	Global	No
innodb_commit_concurrency	Global	Yes
innodb_concurrency_tickets	Global	Yes
innodb_data_file_path	Global	No
innodb_data_home_dir	Global	No
innodb_doublewrite	Global	No
innodb_fast_shutdown	Global	Yes
innodb_file_io_threads	Global	No
innodb_file_per_table	Global	No
innodb_flush_log_at_trx_commit	Global	Yes
innodb_flush_method	Global	No
innodb_force_recovery	Global	No
innodb_lock_wait_timeout	Global	No
innodb_locks_unsafe_for_binlog	Global	No
innodb_log_buffer_size	Global	No
innodb_log_file_size	Global	No
innodb_log_files_in_group	Global	No
innodb_log_group_home_dir	Global	No
innodb_max_dirty_pages_pct	Global	Yes
innodb_max_purge_lag	Global	Yes
innodb_mirrored_log_groups	Global	No
innodb_open_files	Global	No
innodb_rollback_on_timeout	Global	No
innodb_stats_on_metadata	Global	Yes
innodb_status_file
innodb_support_xa	Both	Yes
innodb_sync_spin_loops	Global	Yes
innodb_table_locks	Both	Yes
innodb_thread_concurrency	Global	Yes
innodb_thread_sleep_delay	Global	Yes
interactive_timeout	Both	Yes
join_buffer_size	Both	Yes
join_cache_level	Both	Yes
keep_files_on_create	Both	Yes
key_buffer_size	Global	Yes
key_cache_age_threshold	Global	Yes
key_cache_block_size	Global	Yes
key_cache_division_limit	Global	Yes
language	Global	No
large-pages	Global	No
local-infile
log	Global	Yes
log-backup-output	Global	Yes
log-bin	Global	No
log-bin-index
log-bin-trust-function-creators	Global	Yes
log-error	Global	No
log-isam
log-output	Global	Yes
log-queries-not-using-indexes	Global	Yes
log-short-format
log-slave-updates	Global	No
log-slow-admin-statements
log-slow-queries	Global	Yes
log-slow-slave-statements
log-tc
log-tc-size
log-warnings	Both	Yes
long_query_time	Both	Yes
low-priority-updates	Both	Yes
lower_case_file_system	Global	No
lower_case_table_names	Global	No
maria
maria-block-size		No
maria-checkpoint-interval	Global	Yes
maria-force-start-after-recovery-failures
maria-log-dir-path
maria-log-file-size	Global	Yes
maria-log-purge-type	Global	Yes
maria-max-sort-file-size	Global	Yes
maria-page-checksum	Global	Yes
maria-pagecache-age-threshold	Global	Yes
maria-pagecache-buffer-size	Global	No
maria-pagecache-division-limit	Global	Yes
maria-recover	Global	Yes
maria-repair-threads	Both	Yes
maria-sort-buffer-size	Both	Yes
maria-stats-method	Both	Yes
maria-sync-log-dir	Global	Yes
master-bind		No
master-info-file
master-retry-count
max_allowed_packet	Both	Yes
max_binlog_cache_size	Global	Yes
max-binlog-dump-events
max_binlog_size	Global	Yes
max_connect_errors	Global	Yes
max_connections	Global	Yes
max_delayed_threads	Both	Yes
max_error_count	Both	Yes
max_heap_table_size	Both	Yes
max_join_size	Both	Yes
max_length_for_sort_data	Both	Yes
max_prepared_stmt_count	Global	Yes
max_relay_log_size	Global	Yes
max_seeks_for_key	Both	Yes
max_sort_length	Both	Yes
max_sp_recursion_depth	Both	Yes
max_tmp_tables	Both	Yes
max_user_connections	Both	Yes
max_write_lock_count	Global	Yes
memlock	Global	No
min-examined-row-limit	Both	Yes
myisam-block-size
myisam_data_pointer_size	Global	Yes
myisam_max_sort_file_size	Global	Yes
myisam-recover
myisam_repair_threads	Both	Yes
myisam_sort_buffer_size	Both	Yes
myisam_stats_method	Both	Yes
myisam_use_mmap	Global	Yes
ndb_autoincrement_prefetch_sz	Both	Yes
ndb_index_stat_cache_entries
ndb_index_stat_enable
ndb_index_stat_update_freq
ndb_report_thresh_binlog_epoch_slip
ndb_report_thresh_binlog_mem_usage
ndb_use_transactions	Both	Yes
net_buffer_length	Both	Yes
net_read_timeout	Both	Yes
net_retry_count	Both	Yes
net_write_timeout	Both	Yes
new	Both	Yes
old	Global	No
old-alter-table	Both	Yes
old-passwords	Both	Yes
old-style-user-limits
one-thread
open-files-limit	Global	No
optimizer_prune_level	Both	Yes
optimizer_search_depth	Both	Yes
optimizer_switch	Both	Yes
pid-file	Global	No
plugin_dir	Global	No
plugin-load
port	Global	No
port-open-timeout
preload_buffer_size	Both	Yes
query_alloc_block_size	Both	Yes
query_cache_limit	Global	Yes
query_cache_min_res_unit	Global	Yes
query_cache_size	Global	Yes
query_cache_type	Both	Yes
query_cache_wlock_invalidate	Both	Yes
query_prealloc_size	Both	Yes
range_alloc_block_size	Both	Yes
read_buffer_size	Both	Yes
read_only	Global	Yes
read_rnd_buffer_size	Both	Yes
relay-log
relay-log-index
relay-log-info-file
relay_log_purge	Global	Yes
relay_log_recovery	Global	Yes
relay_log_space_limit	Global	No
replicate-do-db
replicate-do-table
replicate-ignore-db
replicate-ignore-table
replicate-rewrite-db
replicate-same-server-id
replicate-wild-do-table
replicate-wild-ignore-table
report-host	Global	No
report-password	Global	No
report-port	Global	No
report-user	Global	No
safe-mode
safe-show-database	Global	Yes
safe-user-create
safemalloc-mem-limit
secure-auth	Global	Yes
secure-backup-file-priv	Global	No
secure-file-priv	Global	No
server-id	Global	Yes
show-slave-auth-info
skip-character-set-client-handshake
skip-concurrent-insert
skip-external-locking	Global	No
skip-falcon
skip-grant-tables
skip-host-cache
skip-innodb
skip-innodb-checksums
skip-locking
skip-name-resolve
skip-ndbcluster
skip-networking	Global	No
skip-new
skip-safemalloc
skip-show-database	Global	No
skip-slave-start
skip-ssl
skip-stack-trace
skip-symlink
skip-thread-priority
slave_compressed_protocol	Global	Yes
slave-load-tmpdir	Global	No
slave-net-timeout	Global	Yes
slave-skip-errors	Global	No
slave_transaction_retries	Global	Yes
slow_launch_time	Global	Yes
slow-query-log	Global	Yes
slow_query_log_file	Global	Yes
socket	Global	No
sort_buffer_size	Both	Yes
sporadic-binlog-dump-fail
sql-mode	Both	Yes
ssl
ssl-ca	Global	No
ssl-capath	Global	No
ssl-cert	Global	No
ssl-cipher	Global	No
ssl-key	Global	No
ssl-verify-server-cert
standalone
symbolic-links
sync-binlog	Global	Yes
sync-frm	Global	Yes
sync-master-info	Global	Yes
sync-relay-log	Global	Yes
sync-relay-log-info	Global	Yes
sysdate-is-now
table_definition_cache	Global	Yes
table_lock_wait_timeout	Global	Yes
table_open_cache	Global	Yes
tc-heuristic-recover
temp-pool
thread_cache_size	Global	Yes
thread_concurrency	Global	No
thread_handling	Global	No
thread_pool_size	Global	No
thread_stack	Global	No
time_format	Both	Yes
time_zone	Both	Yes
timed_mutexes	Global	Yes
tmp_table_size	Both	Yes
tmpdir	Global	No
transaction_alloc_block_size	Both	Yes
transaction-isolation
transaction_prealloc_size	Both	Yes
updatable_views_with_limit	Both	Yes
use-symbolic-links
user
verbose
version	Global	No
wait_timeout	Both	Yes