Hottest Guide
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 |
If you enjoyed this article, please consider sharing it!
Very nice article. i reqest u tell how to get this variable useful
my mobile no.9889303587
Hiya from Argentina! I have found your webpage on msn. Great content! Melissa M. Clayton x
This post is beyond awesome. I am always wondering what to do and what not to do so I will follow some of these tips.