I got tired of going all over the place to get information about mysqld variables, so I decided to make a summary of most of them (for version 5.0.27 linux). (Due to lack of horizontal space, I had to make two tables — the second one containing a short description of the variable.)
Corrections, additions, and amplifications are welcome. Enjoy!
Table 1: MySQL Variables
| Variable Name | Variable Type | SET OFFLINE ONLY | SET GLOBAL | SET SESSION |
| auto_increment_increment | numeric | OFFLINE | ||
| auto_increment_offset | numeric | OFFLINE | ||
| autocommit | boolean | SESSION | ||
| automatic_sp_privileges | boolean | GLOBAL | ||
| back_log | numeric | OFFLINE | ||
| basedir | string | OFFLINE | ||
| bdb_cache_size | numeric | OFFLINE | ||
| bdb_home | string | OFFLINE | ||
| bdb_log_buffer_size | numeric | OFFLINE | ||
| bdb_logdir | string | OFFLINE | ||
| bdb_max_lock | numeric | OFFLINE | ||
| bdb_shared_data | boolean | OFFLINE | ||
| bdb_tmpdir | string | OFFLINE | ||
| big_tables | boolean | SESSION | ||
| binlog_cache_size | numeric | GLOBAL | ||
| bulk_insert_buffer_size | numeric | GLOBAL | SESSION | |
| character_set_client | string | GLOBAL | SESSION | |
| character_set_connection | string | GLOBAL | SESSION | |
| character_set_database | string | OFFLINE | ||
| character_set_filesystem | string | GLOBAL | SESSION | |
| character_set_results | string | GLOBAL | SESSION | |
| character_set_server | string | GLOBAL | SESSION | |
| character_set_system | string | OFFLINE | ||
| character_sets_dir | string | OFFLINE | ||
| collation_connection | string | GLOBAL | SESSION | |
| collation_database | string | OFFLINE | ||
| collation_server | string | GLOBAL | SESSION | |
| completion_type | numeric | GLOBAL | SESSION | |
| concurrent_insert | numeric | GLOBAL | ||
| connect_timeout | numeric | GLOBAL | ||
| datadir | string | OFFLINE | ||
| date_format | — | OFFLINE | ||
| datetime_format | — | OFFLINE | ||
| default_week_format | numeric | GLOBAL | SESSION | |
| delay_key_write | OFF | ON | ALL | GLOBAL | ||
| delayed_insert_limit | numeric | GLOBAL | ||
| delayed_insert_timeout | numeric | GLOBAL | ||
| delayed_queue_size | numeric | GLOBAL | ||
| div_precision_increment | numeric | GLOBAL | SESSION | |
| engine_condition_pushdown | boolean | GLOBAL | SESSION | |
| error_count | numeric | SESSION | ||
| expire_logs_days | numeric | GLOBAL | ||
| flush | boolean | GLOBAL | ||
| flush_time | numeric | GLOBAL | ||
| foreign_key_checks | boolean | SESSION | ||
| ft_boolean_syntax | string | GLOBAL | ||
| ft_max_word_len | numeric | OFFLINE | ||
| ft_min_word_len | numeric | OFFLINE | ||
| ft_query_expansion_limit | numeric | OFFLINE | ||
| ft_stopword_file | string | OFFLINE | ||
| group_concat_max_len | numeric | GLOBAL | SESSION | |
| have_archive | boolean | OFFLINE | ||
| have_bdb | boolean | OFFLINE | ||
| have_blackhole_engine | boolean | OFFLINE | ||
| have_compress | boolean | OFFLINE | ||
| have_crypt | boolean | OFFLINE | ||
| have_csv | boolean | OFFLINE | ||
| have_dynamic_loading | boolean | OFFLINE | ||
| have_example_engine | boolean | OFFLINE | ||
| have_federated_engine | boolean | OFFLINE | ||
| have_geometry | boolean | OFFLINE | ||
| have_innodb | boolean | OFFLINE | ||
| have_isam | boolean | OFFLINE | ||
| have_merge_engine | boolean | OFFLINE | ||
| have_ndbcluster | boolean | OFFLINE | ||
| have_openssl | boolean | OFFLINE | ||
| have_query_cache | boolean | OFFLINE | ||
| have_raid | boolean | OFFLINE | ||
| have_rtree_keys | boolean | OFFLINE | ||
| have_symlink | boolean | OFFLINE | ||
| init_connect | string | OFFLINE | ||
| init_file | string | OFFLINE | ||
| init_slave | string | OFFLINE | ||
| innodb_additional_mem_pool_size | numeric | OFFLINE | ||
| innodb_autoextend_increment | numeric | GLOBAL | ||
| innodb_buffer_pool_awe_mem_mb | numeric | OFFLINE | ||
| innodb_buffer_pool_size | numeric | OFFLINE | ||
| innodb_checksums | boolean | OFFLINE | ||
| innodb_commit_concurrency | numeric | GLOBAL | ||
| innodb_concurrency_tickets | numeric | GLOBAL | ||
| innodb_data_file_path | string | OFFLINE | ||
| innodb_data_home_dir | string | OFFLINE | ||
| innodb_doublewrite | boolean | OFFLINE | ||
| innodb_fast_shutdown | 0,1 or 2 | OFFLINE | ||
| innodb_file_io_threads | numeric | OFFLINE | ||
| innodb_file_per_table | boolean | OFFLINE | ||
| innodb_flush_log_at_trx_commit | 0,1 or 2 | OFFLINE | ||
| innodb_flush_method | Enum | OFFLINE | ||
| innodb_force_recovery | 1 to 6 | OFFLINE | ||
| innodb_lock_wait_timeout | numeric | OFFLINE | ||
| innodb_locks_unsafe_for_binlog | boolean | OFFLINE | ||
| innodb_log_arch_dir | string | OFFLINE | ||
| innodb_log_archive | — | OFFLINE | ||
| innodb_log_buffer_size | numeric | OFFLINE | ||
| innodb_log_file_size | numeric | OFFLINE | ||
| innodb_log_files_in_group | numeric | OFFLINE | ||
| innodb_log_group_home_dir | string | OFFLINE | ||
| innodb_max_dirty_pages_pct | numeric | GLOBAL | ||
| innodb_max_purge_lag | numeric | GLOBAL | ||
| innodb_mirrored_log_groups | numeric | OFFLINE | ||
| innodb_open_files | numeric | OFFLINE | ||
| innodb_support_xa | boolean | GLOBAL | SESSION | |
| innodb_sync_spin_loops | numeric | GLOBAL | ||
| innodb_table_locks | boolean | GLOBAL | SESSION | |
| innodb_thread_concurrency | numeric | GLOBAL | ||
| innodb_thread_sleep_delay | numeric | GLOBAL | ||
| interactive_timeout | numeric | GLOBAL | SESSION | |
| join_buffer_size | numeric | GLOBAL | SESSION | |
| key_buffer_size | numeric | GLOBAL | ||
| key_cache_age_threshold | numeric | OFFLINE | ||
| key_cache_block_size | numeric | OFFLINE | ||
| key_cache_division_limit | 1 to 100 | OFFLINE | ||
| language | string | OFFLINE | ||
| large_files_support | boolean | OFFLINE | ||
| large_page_size | numeric | OFFLINE | ||
| large_pages | boolean | OFFLINE | ||
| lc_time_names | string | GLOBAL | SESSION | |
| license | string | OFFLINE | ||
| local_infile | boolean | GLOBAL | ||
| locked_in_memory | boolean | OFFLINE | ||
| log | boolean | OFFLINE | ||
| log_bin | boolean | OFFLINE | ||
| log_bin_trust_function_creators | boolean | GLOBAL | ||
| log_error | string | OFFLINE | ||
| log_queries_not_using_indexes | boolean | GLOBAL | ||
| log_slave_updates | boolean | OFFLINE | ||
| log_slow_queries | boolean | OFFLINE | ||
| log_warnings | numeric | GLOBAL | ||
| long_query_time | numeric | GLOBAL | SESSION | |
| low_priority_updates | boolean | GLOBAL | SESSION | |
| lower_case_file_system | boolean | OFFLINE | ||
| lower_case_table_names | numeric | OFFLINE | ||
| max_allowed_packet | numeric | GLOBAL | SESSION | |
| max_binlog_cache_size | numeric | GLOBAL | ||
| max_binlog_size | numeric | GLOBAL | ||
| max_connect_errors | numeric | GLOBAL | ||
| max_connections | numeric | GLOBAL | ||
| max_delayed_threads | numeric | GLOBAL | ||
| max_error_count | numeric | GLOBAL | SESSION | |
| max_heap_table_size | numeric | GLOBAL | SESSION | |
| max_insert_delayed_threads | numeric | GLOBAL | ||
| max_join_size | numeric | GLOBAL | SESSION | |
| max_length_for_sort_data | numeric | OFFLINE | ||
| max_prepared_stmt_count | numeric | GLOBAL | ||
| max_relay_log_size | numeric | GLOBAL | ||
| max_seeks_for_key | numeric | GLOBAL | SESSION | |
| max_sort_length | numeric | GLOBAL | SESSION | |
| max_sp_recursion_depth | numeric | OFFLINE | ||
| max_tmp_tables | numeric | GLOBAL | SESSION | |
| max_user_connections | numeric | GLOBAL | ||
| max_write_lock_count | numeric | GLOBAL | ||
| multi_range_count | numeric | GLOBAL | SESSION | |
| myisam_data_pointer_size | numeric | GLOBAL | ||
| myisam_max_sort_file_size | numeric | GLOBAL | SESSION | |
| myisam_recover_options | boolean | OFFLINE | ||
| myisam_repair_threads | numeric | GLOBAL | SESSION | |
| myisam_sort_buffer_size | numeric | GLOBAL | SESSION | |
| myisam_stats_method | enum | GLOBAL | SESSION | |
| net_buffer_length | numeric | GLOBAL | SESSION | |
| net_read_timeout | numeric | GLOBAL | SESSION | |
| net_retry_count | numeric | GLOBAL | SESSION | |
| net_write_timeout | numeric | GLOBAL | SESSION | |
| new | boolean | OFFLINE | ||
| old_passwords | boolean | GLOBAL | SESSION | |
| open_files_limit | numeric | OFFLINE | ||
| optimizer_prune_level | numeric | GLOBAL | SESSION | |
| optimizer_search_depth | numeric | GLOBAL | SESSION | |
| pid_file | string | OFFLINE | ||
| port | numeric | OFFLINE | ||
| preload_buffer_size | numeric | GLOBAL | SESSION | |
| prepared_stmt_count | numeric | OFFLINE | ||
| protocol_version | numeric | OFFLINE | ||
| query_alloc_block_size | numeric | GLOBAL | SESSION | |
| query_cache_limit | numeric | GLOBAL | ||
| query_cache_min_res_unit | numeric | OFFLINE | ||
| query_cache_size | numeric | GLOBAL | ||
| query_cache_type | enumeration | GLOBAL | SESSION | |
| query_cache_wlock_invalidate | boolean | GLOBAL | SESSION | |
| query_prealloc_size | numeric | GLOBAL | SESSION | |
| range_alloc_block_size | numeric | GLOBAL | SESSION | |
| read_buffer_size | numeric | GLOBAL | SESSION | |
| read_only | numeric | GLOBAL | ||
| read_rnd_buffer_size | numeric | GLOBAL | SESSION | |
| relay_log_purge | boolean | OFFLINE | ||
| relay_log_space_limit | numeric | OFFLINE | ||
| rpl_recovery_rank | numeric | GLOBAL | ||
| secure_auth | boolean | GLOBAL | ||
| server_id | numeric | GLOBAL | ||
| skip_external_locking | boolean | OFFLINE | ||
| skip_networking | boolean | OFFLINE | ||
| skip_show_database | boolean | OFFLINE | ||
| slave_compressed_protocol | boolean | GLOBAL | ||
| slave_load_tmpdir | string | OFFLINE | ||
| slave_net_timeout | numeric | GLOBAL | ||
| slave_skip_errors | boolean | OFFLINE | ||
| slave_transaction_retries | numeric | GLOBAL | ||
| slow_launch_time | numeric | GLOBAL | ||
| socket | string | OFFLINE | ||
| sort_buffer_size | numeric | GLOBAL | SESSION | |
| sql_auto_is_null | boolean | SESSION | ||
| sql_big_selects | boolean | SESSION | ||
| sql_big_tables | boolean | SESSION | ||
| sql_buffer_result | boolean | SESSION | ||
| sql_log_bin | boolean | SESSION | ||
| sql_log_off | boolean | SESSION | ||
| sql_log_update | boolean | SESSION | ||
| sql_low_priority_updates | boolean | GLOBAL | SESSION | |
| sql_max_join_size | numeric | GLOBAL | SESSION | |
| sql_mode | enumeration | GLOBAL | SESSION | |
| sql_notes | boolean | SESSION | ||
| sql_quote_show_create | boolean | SESSION | ||
| sql_safe_updates | boolean | SESSION | ||
| sql_select_limit | numeric | SESSION | ||
| sql_slave_skip_counter | numeric | GLOBAL | ||
| sql_warnings | boolean | SESSION | ||
| ssl_ca | string | OFFLINE | ||
| ssl_capath | string | OFFLINE | ||
| ssl_cert | string | OFFLINE | ||
| ssl_cipher | string | OFFLINE | ||
| ssl_key | string | OFFLINE | ||
| storage_engine | enumeration | GLOBAL | SESSION | |
| sync_binlog | numeric | GLOBAL | ||
| sync_frm | boolean | GLOBAL | ||
| system_time_zone | string | OFFLINE | ||
| table_cache | numeric | GLOBAL | ||
| table_lock_wait_timeout | numeric | OFFLINE | ||
| table_type | enumeration | GLOBAL | SESSION | |
| thread_cache_size | numeric | GLOBAL | ||
| thread_stack | numeric | OFFLINE | ||
| time_format | — | OFFLINE | ||
| time_zone | string | GLOBAL | SESSION | |
| timed_mutexes | boolean | OFFLINE | ||
| tmp_table_size | enumeration | GLOBAL | SESSION | |
| tmpdir | string | OFFLINE | ||
| transaction_alloc_block_size | numeric | GLOBAL | SESSION | |
| transaction_prealloc_size | numeric | GLOBAL | SESSION | |
| tx_isolation | enumeration | GLOBAL | SESSION | |
| updatable_views_with_limit | enumeration | GLOBAL | SESSION | |
| version | string | OFFLINE | ||
| version_bdb | string | OFFLINE | ||
| version_comment | string | OFFLINE | ||
| version_compile_machine | string | OFFLINE | ||
| version_compile_os | numeric | OFFLINE | ||
| wait_timeout | numeric | GLOBAL | SESSION |
Table 2: The comment column
| Variable Name | COMMENT |
| auto_increment_increment | Controls the interval between successive column values |
| auto_increment_offset | Determines the starting point for the AUTO_INCREMENT column value |
| autocommit | Statement forms a single transaction on its own |
| automatic_sp_privileges | Server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine |
| back_log | Number of outstanding connection requests MySQL can have |
| basedir | MySQL installation base directory |
| bdb_cache_size | Size of the buffer allocated for caching indexes and rows for BDB tables |
| bdb_home | Base directory for BDB tables |
| bdb_log_buffer_size | Size of the buffer allocated for caching indexes and rows for BDB tables |
| bdb_logdir | Directory where the BDB storage engine writes its log files |
| bdb_max_lock | Maximum number of locks that can be active for a BDB table |
| bdb_shared_data | Start Berkeley DB in multi-process mode |
| bdb_tmpdir | BDB temporary file directory |
| big_tables | Exactly the same as using SQL_BIG_TABLES for all queries |
| binlog_cache_size | Size of the cache to hold the SQL statements for the binary log during a transaction |
| bulk_insert_buffer_size | Limits the size of the cache tree in bytes per thread |
| character_set_client | Character set for statements that arrive from the client |
| character_set_connection | Character set used for literals that do not have a character set introducer and for number-to-string conversion |
| character_set_database | Character set used by the default database |
| character_set_filesystem | Used to interpret string literals that refer to filenames |
| character_set_results | Character set used for returning query results to the client |
| character_set_server | Server’s default character set |
| character_set_system | Character set used by the server for storing identifiers |
| character_sets_dir | Directory where character sets are installed |
| collation_connection | Collation of the connection character set |
| collation_database | Collation used by the default database |
| collation_server | Server’s default collation |
| completion_type | Transaction completion type |
| concurrent_insert | Allows INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file |
| connect_timeout | Number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake |
| datadir | MySQL data directory |
| date_format | Not implemented |
| datetime_format | Not implemented |
| default_week_format | Default mode value to use for the WEEK() function |
| delay_key_write | ONLY MyISAM. If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed. |
| delayed_insert_limit | After inserting <delayed_insert_limit> delayed rows, the INSERT DELAYED handler thread checks whether there are any SELECT statements pending. If so, it allows them to execute before continuing to insert delayed rows. |
| delayed_insert_timeout | How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating |
| delayed_queue_size | Per-table limit on the number of rows to queue when handling INSERT DELAYED statements |
| div_precision_increment | Number of digits of precision by which to increase the result of division operations performed with the / operator |
| engine_condition_pushdown | Applies to NDB |
| error_count | Read-only variable |
| expire_logs_days | Number of days for automatic binary log removal |
| flush | If ON, the server flushes (synchronizes) all changes to disk after each SQL statement. |
| flush_time | All tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk |
| foreign_key_checks | Do not check for foreign key problems |
| ft_boolean_syntax | List of operators supported by boolean full-text searches performed using IN BOOLEAN MODE |
| ft_max_word_len | Maximum length of the word to be included in a FULLTEXT index |
| ft_min_word_len | Minimum length of the word to be included in a FULLTEXT index |
| ft_query_expansion_limit | Number of top matches to use for full-text searches performed using WITH QUERY EXPANSION |
| ft_stopword_file | File from which to read the list of stopwords for full-text searches |
| group_concat_max_len | Maximum allowed result length for the GROUP_CONCAT() function |
| have_archive | YES if mysqld supports ARCHIVE tables, NO if not. |
| have_bdb | YES if mysqld supports BDB tables. DISABLED if –skip-bdb is used |
| have_blackhole_engine | YES if mysqld supports BLACKHOLE tables, NO if not |
| have_compress | YES if the zlib compression library is available to the server, NO if not. If not, the COMPRESS() and UNCOMPRESS() functions cannot be used |
| have_crypt | YES if the crypt() system call is available to the server, NO if not. If not, the ENCRYPT() function cannot be used |
| have_csv | YES if mysqld supports CSV tables, NO if not |
| have_dynamic_loading | YES if mysqld can dynamically load data |
| have_example_engine | YES if mysqld supports EXAMPLE tables, NO if not |
| have_federated_engine | YES if mysqld supports FEDERATED tables, NO if not |
| have_geometry | YES if the server supports spatial data types, NO if not |
| have_innodb | YES if mysqld supports InnoDB tables. DISABLED if –skip-innodb is used |
| have_isam | For backward compatibility. Always NO because ISAM tables are no longer supported. |
| have_merge_engine | YES if mysqld supports MERGE tables. DISABLED if –skip-merge is used |
| have_ndbcluster | YES if mysqld supports NDB Cluster tables. DISABLED if –skip-ndbcluster is used |
| have_openssl | YES if mysqld supports SSL connections, NO if not |
| have_query_cache | YES if mysqld supports the query cache, NO if not |
| have_raid | For backward compatibility. Always NO because RAID tables are no longer supported |
| have_rtree_keys | YES if RTREE indexes are available, NO if not |
| have_symlink | YES if symbolic link support is enabled, NO if not |
| init_connect | String to be executed by the server for each client that connects |
| init_file | Name of the file specified with the –init-file option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts |
| init_slave | String to be executed by a slave server each time the SQL thread starts |
| innodb_additional_mem_pool_size | Size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures |
| innodb_autoextend_increment | Increment size (in MB) for extending the size of an auto-extending tablespace when it becomes full |
| innodb_buffer_pool_awe_mem_mb | Size of the buffer pool (in MB), if it is placed in the AWE memory. Relevant only in 32-bit Windows |
| innodb_buffer_pool_size | Size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables |
| innodb_checksums | InnoDB can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files |
| innodb_commit_concurrency | Number of threads that can commit at the same time. A value of 0 disables concurrency control. |
| innodb_concurrency_tickets | Number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable |
| innodb_data_file_path | Paths to individual data files and their sizes |
| innodb_data_home_dir | Common part of the directory path for all InnoDB data files |
| innodb_doublewrite | InnoDB stores all data twice, first to the doublewrite buffer, and then to the actual data files |
| innodb_fast_shutdown | Full purge and an insert buffer merge before a shutdown OR NOT |
| innodb_file_io_threads | Number of file I/O threads in InnoDB |
| innodb_file_per_table | If this variable is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the shared tablespace |
| innodb_flush_log_at_trx_commit | See documentation |
| innodb_flush_method | See documentation |
| innodb_force_recovery | Crash recovery mode |
| innodb_lock_wait_timeout | Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back |
| innodb_locks_unsafe_for_binlog | Controls next-key locking in InnoDB searches and index scans |
| innodb_log_arch_dir | Path to arch dir |
| innodb_log_archive | Present for historical reasons, but unused |
| innodb_log_buffer_size | Size in bytes of the buffer that InnoDB uses to write to the log files on disk |
| innodb_log_file_size | Size in bytes of each log file in a log group |
| innodb_log_files_in_group | Number of log files in the log group |
| innodb_log_group_home_dir | Directory path to the InnoDB log files |
| innodb_max_dirty_pages_pct | Main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value |
| innodb_max_purge_lag | Controls how to delay INSERT, UPDATE and DELETE operations when the purge operations are lagging |
| innodb_mirrored_log_groups | Number of identical copies of log groups to keep for the database |
| innodb_open_files | Relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time |
| innodb_support_xa | Enables InnoDB support for two-phase commit in XA transactions |
| innodb_sync_spin_loops | Number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended |
| innodb_table_locks | If 1, means that LOCK TABLES causes InnoDB to lock a table internally if AUTOCOMMIT=0 |
| innodb_thread_concurrency | InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to this limit |
| innodb_thread_sleep_delay | How long InnoDB threads sleep before joining the InnoDB queue, in microseconds |
| interactive_timeout | Number of seconds the server waits for activity on an interactive connection before closing it |
| join_buffer_size | Size of the buffer used for joins that do not use indexes and thus perform full table scans |
| key_buffer_size | Size of the buffer used for index blocks (also known as the key cache) |
| key_cache_age_threshold | Controls the demotion of buffers from the hot sub-chain of a key cache to the warm sub-chain |
| key_cache_block_size | Size in bytes of blocks in the key cache |
| key_cache_division_limit | Division point between the hot and warm sub-chains of the key cache buffer chain. The value is the percentage of the buffer chain to use for the warm sub-chain |
| language | Language used for error messages |
| large_files_support | Whether mysqld was compiled with options for large file support |
| large_page_size | |
| large_pages | Whether large page support is enabled |
| lc_time_names | Specifies the locale that controls the language used to display day and month names and abbreviations |
| license | Type of license the server has |
| local_infile | Whether LOCAL is supported for LOAD DATA INFILE statements |
| locked_in_memory | Whether mysqld was locked in memory with –memlock |
| log | Whether logging of all statements to the general query log is enabled |
| log_bin | Whether the binary log is enabled |
| log_bin_trust_function_creators | Applies when binary logging is enabled. Controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. |
| log_error | Location of the error log |
| log_queries_not_using_indexes | Whether queries that do not use indexes are logged to the slow query log |
| log_slave_updates | Whether updates received by a slave server from a master server should be logged to the slave’s own binary log |
| log_slow_queries | Whether slow queries should be logged |
| log_warnings | Whether to produce additional warning messages |
| long_query_time | If a query takes longer than this many seconds, the query is logged to the slow query log file |
| low_priority_updates | If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table |
| lower_case_file_system | Describes the case sensitivity of filenames on the filesystem where the data directory is located |
| lower_case_table_names | If set to 1, table names are stored in lowercase on disk and table name comparisons are not case sensitive. If set to 2 table names are stored as given but compared in lowercase |
| max_allowed_packet | Maximum size of one packet or any generated/intermediate string |
| max_binlog_cache_size | If a multiple-statement transaction requires more than this many bytes of memory, the server generates a multi-statement transaction requiring more than ‘max_binlog_cache_size’ bytes of storage error |
| max_binlog_size | If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one) |
| max_connect_errors | If there are more than this number of interrupted connections from a host, that host is blocked from further connections |
| max_connections | Number of simultaneous client connections allowed |
| max_delayed_threads | Do not start more than this number of threads to handle INSERT DELAYED statements |
| max_error_count | Maximum number of error, warning, and note messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements |
| max_heap_table_size | Sets the maximum size to which MEMORY tables are allowed to grow |
| max_insert_delayed_threads | Synonym for max_delayed_threads |
| max_join_size | Do not allow SELECT statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks |
| max_length_for_sort_data | Cutoff on the size of index values that determines which filesort algorithm to use |
| max_prepared_stmt_count | Limits the total number of prepared statements in the server |
| max_relay_log_size | If a write by a replication slave to its relay log causes the current log file size to exceed the value of this variable, the slave rotates the relay logs (closes the current file and opens the next one) |
| max_seeks_for_key | Limit the assumed maximum number of seeks when looking up rows based on a key |
| max_sort_length | Number of bytes to use when sorting BLOB or TEXT values |
| max_sp_recursion_depth | Number of times that a stored procedure may call itself |
| max_tmp_tables | Maximum number of temporary tables a client can keep open at the same time |
| max_user_connections | Maximum number of simultaneous connections allowed to any given MySQL account |
| max_write_lock_count | After this many write locks, allow some pending read lock requests to be processed in between |
| multi_range_count | Maximum number of ranges to send to a table handler at once during range selects |
| myisam_data_pointer_size | Default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified |
| myisam_max_sort_file_size | Maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE) |
| myisam_recover_options | Value of the –myisam-recover option |
| myisam_repair_threads | If greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process |
| myisam_sort_buffer_size | Size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE |
| myisam_stats_method | How the server treats NULL values when collecting statistics about the distribution of index values for MyISAM tables |
| net_buffer_length | Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed |
| net_read_timeout | Number of seconds to wait for more data from a connection before aborting the read |
| net_retry_count | If a read on a communication port is interrupted, retry this many times before giving up |
| net_write_timeout | Number of seconds to wait for a block to be written to a connection before aborting the write |
| new | Retained for backward compatibility |
| old_passwords | Whether the server should use pre-4.1-style passwords for MySQL user accounts |
| open_files_limit | Number of files that the operating system allows mysqld to open |
| optimizer_prune_level | Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space |
| optimizer_search_depth | Maximum depth of search performed by the query optimizer |
| pid_file | Pathname of the process ID (PID) file |
| port | Number of the port on which the server listens for TCP/IP connections |
| preload_buffer_size | Size of the buffer that is allocated when preloading indexes |
| prepared_stmt_count | Current number of prepared statements |
| protocol_version | Version of the client/server protocol used by the MySQL server |
| query_alloc_block_size | Allocation size of memory blocks that are allocated for objects created during statement parsing and execution |
| query_cache_limit | Don’t cache results that are larger than this number of bytes |
| query_cache_min_res_unit | Minimum size (in bytes) for blocks allocated by the query cache |
| query_cache_size | Amount of memory allocated for caching query results |
| query_cache_type | Sets the query cache type |
| query_cache_wlock_invalidate | Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table. This forces other clients that attempt to access the table to wait while the lock is in effect. |
| query_prealloc_size | Size of the persistent buffer used for statement parsing and execution |
| range_alloc_block_size | Size of blocks allocated when doing range optimization |
| read_buffer_size | Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans |
| read_only | When this variable is set to ON, the server allows no updates except from users that have the SUPER privilege, or (on a slave server) from updates performed by slave threads |
| read_rnd_buffer_size | When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks |
| relay_log_purge | Disables or enables automatic purging of relay log files as soon as they are no longer needed |
| relay_log_space_limit | Places an upper limit on the total size in bytes of all relay logs on the slave |
| rpl_recovery_rank | Variable is unused |
| secure_auth | Blocks connections from all accounts that have passwords stored in the old (pre-4.1) format |
| server_id | Used for replication to enable master and slave servers to identify themselves uniquely |
| skip_external_locking | This is OFF if mysqld uses external locking, ON if external locking is disabled |
| skip_networking | This is ON if the server allows only local (non-TCP/IP) connections |
| skip_show_database | Prevents users from using the SHOW DATABASES statement if they do not have the SHOW DATABASES privilege |
| slave_compressed_protocol | Whether to use compression of the slave/master protocol if both the slave and the master support it |
| slave_load_tmpdir | Name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements |
| slave_net_timeout | Number of seconds to wait for more data from a master/slave connection before aborting the read |
| slave_skip_errors | Replication errors that the slave should skip (ignore) |
| slave_transaction_retries | If a replication slave SQL thread fails to execute a transaction, it automatically retries <slave_transaction_retries> times before stopping with an error |
| slow_launch_time | If creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable. |
| socket | On Unix platforms, this variable is the name of the socket file that is used for local client connections |
| sort_buffer_size | Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. |
| sql_auto_is_null | If set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column. |
| sql_big_selects | If set to 0, MySQL aborts SELECT statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of max_join_size). |
| sql_big_tables | Renamed BIG_TABLES |
| sql_buffer_result | If set to 1, SQL_BUFFER_RESULT forces results from SELECT statements to be put into temporary tables. |
| sql_log_bin | If set to 0, no logging is done to the binary log for the client. The client must have the SUPER privilege to set this option. |
| sql_log_off | If set to 1, no logging is done to the general query log for this client. The client must have the SUPER privilege to set this option. |
| sql_log_update | Deprecated, and is mapped to SQL_LOG_BIN |
| sql_low_priority_updates | Renamed low_priority_updates |
| sql_max_join_size | Renamed max_join_size |
| sql_mode | Current server SQL mode |
| sql_notes | If set to 1 (the default), warnings of Note level are recorded. If set to 0, Note warnings are suppressed |
| sql_quote_show_create | If set to 1 (the default), the server quotes identifiers for SHOW CREATE TABLE and SHOW CREATE DATABASE statements. If set to 0, quoting is disabled |
| sql_safe_updates | If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause |
| sql_select_limit | Maximum number of rows to return from SELECT statements |
| sql_slave_skip_counter | Number of events from the master that a slave server should skip |
| sql_warnings | Controls whether single-row INSERT statements produce an information string if warnings occur |
| ssl_ca | Path to a file with a list of trusted SSL CAs |
| ssl_capath | Path to a directory that contains trusted SSL CA certificates in PEM format |
| ssl_cert | Name of the SSL certificate file to use for establishing a secure connection |
| ssl_cipher | List of allowable ciphers to use for SSL encryption |
| ssl_key | Name of the SSL key file to use for establishing a secure connection |
| storage_engine | Default storage engine (table type) |
| sync_binlog | If the value is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log. |
| sync_frm | If set to 1, when any non-temporary table is created its .frm file is synchronized to disk (using fdatasync()) |
| system_time_zone | Server system time zone |
| table_cache | Number of open tables for all threads |
| table_lock_wait_timeout | Specifies a wait timeout for table-level locks, in seconds |
| table_type | Synonym for storage_engine |
| thread_cache_size | How many threads the server should cache for reuse |
| thread_stack | Stack size for each thread |
| time_format | Not implemented |
| time_zone | Current time zone. Used to initialize the time zone for each client that connects. |
| timed_mutexes | Controls whether InnoDB mutexes are timed |
| tmp_table_size | Maximum size of in-memory temporary tables |
| tmpdir | Directory used for temporary files and temporary tables |
| transaction_alloc_block_size | Amount in bytes by which to increase a per-transaction memory pool that needs memory |
| transaction_prealloc_size | See documentation |
| tx_isolation | Default transaction isolation level |
| updatable_views_with_limit | Controls whether updates to a view can be made when the view does not contain all columns of the primary key defined in the underlying table, if the update statement contains a LIMIT clause |
| version | Version number for the server |
| version_bdb | BDB storage engine version |
| version_comment | Allows a comment to be specified when building MySQL |
| version_compile_machine | Type of machine or architecture on which MySQL was built |
| version_compile_os | Type of operating system on which MySQL was built |
| wait_timeout | Number of seconds the server waits for activity on a non-interactive connection before closing it |
4 Comments. Leave new
Hi,
Nice tables, but we’ve had summary tables of this information in the MySQL online documentation since September 06 for 5.0/5.1 and have just added the 4.1 table:
https://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html
https://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html
https://dev.mysql.com/doc/refman/4.1/en/mysqld-option-tables.html
Have you considered putting these into a DB install script to have them available in the information schema? It can prove awful handy in the DBMS itself. I’d be happy to put them into a script to put into docs if there was interest.
Very nice! I haven’t seen that before on the MySQL Online docs, and I do browse them often enough! Hopefully more people will find the links from here.
–Raj.
Hi,
Yes funnily enough we have been discussing the potential for putting this information into the SQL help tables that the documentation team also produce.
I’ll keep you posted on when that happens.
MC