MySQL :: MySQL 5.0 Reference Manual :: 16.4.1 Replication Features and Issues

Posted by: Lava Kafle

  • mysqld --verbose --help
  • To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:
    mysqld --no-defaults --verbose --help
  • To see the current values used by a running server, use the SHOW VARIABLES statement.

This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.1 releases. For historical information concerning their implementation, please see http://dev.mysql.com/doc/refman/5.0/en/, and http://dev.mysql.com/doc/refman/4.1/en/.

The following table lists all available system variables:

Table 5.2. System Variable Summary

NameCmd-LineOption fileSystem VarVar ScopeDynamic
auto_increment_incrementYesYesYesBothYes
auto_increment_offsetYesYesYesBothYes
autocommitYesSessionYes
automatic_sp_privilegesYesGlobalYes
back_logYesYesYesGlobalNo
basedirYesYesYesGlobalNo
big-tablesYesYesYes
- Variable: big_tablesYesSessionYes
binlog_cache_sizeYesYesYesGlobalYes
binlog_direct_non_transactional_updatesYesYesYesBothYes
binlog-formatYesYesYes
- Variable: binlog_formatYesBothYes
bulk_insert_buffer_sizeYesYesYesBothYes
character_set_clientYesBothYes
character_set_connectionYesBothYes
character_set_database[a]YesBothYes
character-set-filesystemYesYesYes
- Variable: character_set_filesystemYesBothYes
character_set_resultsYesBothYes
character-set-serverYesYesYes
- Variable: character_set_serverYesBothYes
character_set_systemYesGlobalNo
character-sets-dirYesYesNo
- Variable: character_sets_dirYesGlobalNo
collation_connectionYesBothYes
collation_database[b]YesBothYes
collation-serverYesYesYes
- Variable: collation_serverYesBothYes
completion_typeYesYesYesBothYes
concurrent_insertYesYesYesGlobalYes
connect_timeoutYesYesYesGlobalYes
datadirYesYesYesGlobalNo
date_formatYesBothNo
datetime_formatYesYesYesBothNo
debugYesYesYesBothYes
debug_syncYesBothYes
default-storage-engineYesYesYesBothYes
default_week_formatYesYesYesBothYes
delay-key-writeYesYesYes
- Variable: delay_key_writeYesGlobalYes
delayed_insert_limitYesYesYesGlobalYes
delayed_insert_timeoutYesYesYesGlobalYes
delayed_queue_sizeYesYesYesGlobalYes
div_precision_incrementYesYesYesBothYes
engine-condition-pushdownYesYesYes
- Variable: engine_condition_pushdownYesBothYes
error_countYesSessionNo
event-schedulerYesYesYes
- Variable: event_schedulerYesGlobalYes
expire_logs_daysYesYesYesGlobalYes
flushYesYesYesGlobalYes
flush_timeYesYesYesGlobalYes
foreign_key_checksYesSessionYes
ft_boolean_syntaxYesYesYesGlobalYes
ft_max_word_lenYesYesYesGlobalNo
ft_min_word_lenYesYesYesGlobalNo
ft_query_expansion_limitYesYesYesGlobalNo
ft_stopword_fileYesYesYesGlobalNo
general-logYesYesYes
- Variable: general_logYesGlobalYes
general_log_fileYesYesYesGlobalYes
group_concat_max_lenYesYesYesBothYes
have_archiveYesGlobalNo
have_blackhole_engineYesGlobalNo
have_community_featuresYesGlobalNo
have_compressYesGlobalNo
have_cryptYesGlobalNo
have_csvYesGlobalNo
have_dynamic_loadingYesGlobalNo
have_example_engineYesGlobalNo
have_federated_engineYesGlobalNo
have_geometryYesGlobalNo
have_innodbYesGlobalNo
have_isamYesGlobalNo
have_merge_engineYesGlobalNo
have_ndbclusterYesGlobalNo
have_opensslYesGlobalNo
have_partitioningYesGlobalNo
have_query_cacheYesGlobalNo
have_raidYesGlobalNo
have_row_based_replicationYesGlobalNo
have_rtree_keysYesGlobalNo
have_sslYesGlobalNo
have_symlinkYesGlobalNo
hostnameYesGlobalNo
identityYesSessionYes
ignore_builtin_innodbYesYesYesGlobalNo
init_connectYesYesYesGlobalYes
init-fileYesYesNo
- Variable: init_fileYesGlobalNo
init_slaveYesYesYesGlobalYes
innodb_adaptive_flushingYesYesYesGlobalYes
innodb_adaptive_hash_indexYesYesYesGlobalNo
innodb_additional_mem_pool_sizeYesYesYesGlobalNo
innodb_autoextend_incrementYesYesYesGlobalYes
innodb_autoinc_lock_modeYesYesYesGlobalNo
innodb_buffer_pool_awe_mem_mbYesYesYesGlobalNo
innodb_buffer_pool_sizeYesYesYesGlobalNo
innodb_change_bufferingYesYesYesGlobalYes
innodb_checksumsYesYesYesGlobalNo
innodb_commit_concurrencyYesYesYesGlobalYes
innodb_concurrency_ticketsYesYesYesGlobalYes
innodb_data_file_pathYesYesYesGlobalNo
innodb_data_home_dirYesYesYesGlobalNo
innodb_doublewriteYesYesYesGlobalNo
innodb_fast_shutdownYesYesYesGlobalYes
innodb_file_formatYesYesYesGlobalYes
innodb_file_format_checkYesYesYesGlobalYes
innodb_file_io_threadsYesYesYesGlobalNo
innodb_file_per_tableYesYesYesGlobalNo
innodb_flush_log_at_trx_commitYesYesYesGlobalYes
innodb_flush_methodYesYesYesGlobalNo
innodb_force_recoveryYesYesYesGlobalNo
innodb_io_capacityYesYesYesGlobalNo
innodb_lock_wait_timeoutYesYesYesBothYes
innodb_locks_unsafe_for_binlogYesYesYesGlobalNo
innodb_log_arch_dirYesYesYesGlobalNo
innodb_log_archiveYesYesYesGlobalNo
innodb_log_buffer_sizeYesYesYesGlobalNo
innodb_log_file_sizeYesYesYesGlobalNo
innodb_log_files_in_groupYesYesYesGlobalNo
innodb_log_group_home_dirYesYesYesGlobalNo
innodb_max_dirty_pages_pctYesYesYesGlobalYes
innodb_max_purge_lagYesYesYesGlobalYes
innodb_mirrored_log_groupsYesYesYesGlobalNo
innodb_old_blocks_pctYesYesYesGlobalYes
innodb_old_blocks_timeYesYesYesGlobalYes
innodb_open_filesYesYesYesGlobalNo
innodb_read_ahead_thresholdYesYesYesGlobalYes
innodb_read_io_threadsYesYesYesGlobalNo
innodb_replication_delayYesYesYesGlobalYes
innodb_rollback_on_timeoutYesYesYesGlobalNo
innodb_spin_wait_delayYesYesYesGlobalYes
innodb_stats_on_metadataYesYesYesGlobalYes
innodb_stats_sample_pagesYesYesYesGlobalYes
innodb_strict_modeYesYesYesBothYes
innodb_support_xaYesYesYesBothYes
innodb_sync_spin_loopsYesYesYesGlobalYes
innodb_table_locksYesYesYesBothYes
innodb_thread_concurrencyYesYesYesGlobalYes
innodb_thread_sleep_delayYesYesYesGlobalYes
innodb_use_legacy_cardinality_algorithmYesYesYesGlobalYes
innodb_use_sys_mallocYesYesYesGlobalNo
innodb_versionYesGlobalNo
innodb_write_io_threadsYesYesYesGlobalNo
insert_idYesSessionYes
interactive_timeoutYesYesYesBothYes
join_buffer_sizeYesYesYesBothYes
keep_files_on_createYesYesYesBothYes
key_buffer_sizeYesYesYesGlobalYes
key_cache_age_thresholdYesYesYesGlobalYes
key_cache_block_sizeYesYesYesGlobalYes
key_cache_division_limitYesYesYesGlobalYes
languageYesYesYesGlobalNo
large_files_supportYesGlobalNo
large_page_sizeYesGlobalNo
large-pagesYesYesNo
- Variable: large_pagesYesGlobalNo
last_insert_idYesSessionYes
lc_time_namesYesBothYes
licenseYesGlobalNo
local_infileYesGlobalYes
locked_in_memoryYesGlobalNo
logYesYesYesGlobalYes
log_binYesGlobalNo
log-binYesYesYesGlobalNo
log-bin-trust-function-creatorsYesYesYes
- Variable: log_bin_trust_function_creatorsYesGlobalYes
log-bin-trust-routine-creatorsYesYesYes
- Variable: log_bin_trust_routine_creatorsYesGlobalYes
log-errorYesYesNo
- Variable: log_errorYesGlobalNo
log-outputYesYesYes
- Variable: log_outputYesGlobalYes
log-queries-not-using-indexesYesYesYes
- Variable: log_queries_not_using_indexesYesGlobalYes
log-slave-updatesYesYesNo
- Variable: log_slave_updatesYesGlobalNo
log-slow-queriesYesYesYes
- Variable: log_slow_queriesYesGlobalYes
log-warningsYesYesYes
- Variable: log_warningsYesBothYes
long_query_timeYesYesYesBothYes
low-priority-updatesYesYesYes
- Variable: low_priority_updatesYesBothYes
lower_case_file_systemYesYesYesGlobalNo
lower_case_table_namesYesYesYesGlobalNo
master-bindYesYesYesNo
max_allowed_packetYesYesYesGlobalYes
max_binlog_cache_sizeYesYesYesGlobalYes
max_binlog_sizeYesYesYesGlobalYes
max_connect_errorsYesYesYesGlobalYes
max_connectionsYesYesYesGlobalYes
max_delayed_threadsYesYesYesBothYes
max_error_countYesYesYesBothYes
max_heap_table_sizeYesYesYesBothYes
max_insert_delayed_threadsYesBothYes
max_join_sizeYesYesYesBothYes
max_length_for_sort_dataYesYesYesBothYes
max_prepared_stmt_countYesYesYesGlobalYes
max_relay_log_sizeYesYesYesGlobalYes
max_seeks_for_keyYesYesYesBothYes
max_sort_lengthYesYesYesBothYes
max_sp_recursion_depthYesYesYesBothYes
max_tmp_tablesYesYesYesBothYes
max_user_connectionsYesYesYesBothYes
max_write_lock_countYesYesYesGlobalYes
memlockYesYesYesGlobalNo
min-examined-row-limitYesYesYesBothYes
multi_range_countYesYesYesBothYes
myisam_data_pointer_sizeYesYesYesGlobalYes
myisam_max_sort_file_sizeYesYesYesGlobalYes
myisam_mmap_sizeYesYesYesGlobalNo
myisam_recover_optionsYesGlobalNo
myisam_repair_threadsYesYesYesBothYes
myisam_sort_buffer_sizeYesYesYesBothYes
myisam_stats_methodYesYesYesBothYes
myisam_use_mmapYesYesYesGlobalYes
named_pipeYesGlobalNo
ndb_autoincrement_prefetch_szYesYesYesBothYes
ndb-batch-sizeYesYesYesGlobalNo
ndb_cache_check_timeYesYesYesGlobalYes
ndb_extra_loggingYesYesYesGlobalYes
ndb_force_sendYesYesYesBothYes
ndb_log_binYesYesBothYes
ndb_log_binlog_indexYesYesGlobalYes
ndb_log_empty_epochsYesYesYesGlobalYes
ndb_log_origYesGlobalNo
ndb-log-update-as-writeYesYesYesGlobalYes
ndb_log_updated_onlyYesYesYesGlobalYes
ndb_optimization_delayYesGlobalYes
ndb_table_no_loggingYesSessionYes
ndb_table_temporaryYesSessionYes
ndb_use_copying_alter_tableYesBothNo
ndb_use_exact_countYesBothYes
ndb_use_transactionsYesYesYesBothYes
ndb_wait_connectedYesYesYesNo
ndb-wait-setupYesYesYesNo
ndbinfo_databaseYesNo
ndbinfo_max_bytesYesYesBothYes
ndbinfo_max_rowsYesYesBothYes
ndbinfo_show_hiddenYesYesBothYes
ndbinfo_table_prefixYesYesBothYes
ndbinfo_versionYesGlobalNo
net_buffer_lengthYesYesYesBothYes
net_read_timeoutYesYesYesBothYes
net_retry_countYesYesYesBothYes
net_write_timeoutYesYesYesBothYes
newYesYesYesBothYes
oldYesYesYesGlobalNo
old-alter-tableYesYesYes
- Variable: old_alter_tableYesBothYes
old-passwordsYesYesYes
- Variable: old_passwordsYesBothYes
open-files-limitYesYesNo
- Variable: open_files_limitYesGlobalNo
optimizer_prune_levelYesYesYesBothYes
optimizer_search_depthYesYesYesBothYes
optimizer_switchYesYesYesBothYes
partitionYesYesNo
- Variable: have_partitioningYesGlobalNo
pid-fileYesYesNo
- Variable: pid_fileYesGlobalNo
plugin_dirYesYesYesGlobalNo
portYesYesYesGlobalNo
preload_buffer_sizeYesYesYesBothYes
prepared_stmt_countYesGlobalNo
profilingYesSessionYes
profiling_history_sizeYesBothYes
protocol_versionYesGlobalNo
pseudo_thread_idYesSessionYes
query_alloc_block_sizeYesYesYesBothYes
query_cache_limitYesYesYesGlobalYes
query_cache_min_res_unitYesYesYesGlobalYes
query_cache_sizeYesYesYesGlobalYes
query_cache_typeYesYesYesBothYes
query_cache_wlock_invalidateYesYesYesBothYes
query_prealloc_sizeYesYesYesBothYes
rand_seed1YesSessionYes
rand_seed2YesSessionYes
range_alloc_block_sizeYesYesYesBothYes
read_buffer_sizeYesYesYesBothYes
read_onlyYesYesYesGlobalYes
read_rnd_buffer_sizeYesYesYesBothYes
relay_log_purgeYesYesYesGlobalYes
relay_log_space_limitYesYesYesGlobalNo
report-hostYesYesNo
- Variable: report_hostYesGlobalNo
report-passwordYesYesNo
- Variable: report_passwordYesGlobalNo
report-portYesYesNo
- Variable: report_portYesGlobalNo
report-userYesYesNo
- Variable: report_userYesGlobalNo
rpl_recovery_rankYesGlobalYes
safe-show-databaseYesYesYesGlobalYes
secure-authYesYesYes
- Variable: secure_authYesGlobalYes
secure-file-privYesYesNo
- Variable: secure_file_privYesGlobalNo
server-idYesYesYes
- Variable: server_idYesGlobalYes
shared_memoryYesGlobalNo
shared_memory_base_nameYesGlobalNo
skip-external-lockingYesYesNo
- Variable: skip_external_lockingYesGlobalNo
skip-name-resolveYesYesYesGlobalNo
skip-networkingYesYesNo
- Variable: skip_networkingYesGlobalNo
skip-show-databaseYesYesNo
- Variable: skip_show_databaseYesGlobalNo
slave-allow-batchingYesYesYes
- Variable: slave_allow_batchingYesGlobalYes
slave_compressed_protocolYesYesYesGlobalYes
slave_exec_modeYesGlobalYes
slave-load-tmpdirYesYesNo
- Variable: slave_load_tmpdirYesGlobalNo
slave-net-timeoutYesYesYes
- Variable: slave_net_timeoutYesGlobalYes
slave-skip-errorsYesYesNo
- Variable: slave_skip_errorsYesGlobalNo
slave_transaction_retriesYesYesYesGlobalYes
slave_type_conversionsYesYesYesGlobalNo
slow_launch_timeYesYesYesGlobalYes
slow-query-logYesYesYes
- Variable: slow_query_logYesGlobalYes
slow_query_log_fileYesYesYesGlobalYes
socketYesYesYesGlobalNo
sort_buffer_sizeYesYesYesBothYes
sql_auto_is_nullYesSessionYes
sql_big_selectsYesBothYes
sql_big_tablesYesSessionYes
sql_buffer_resultYesSessionYes
sql_log_binYesSessionYes
sql_log_offYesSessionYes
sql_log_updateYesSessionYes
sql_low_priority_updatesYesBothYes
sql_max_join_sizeYesBothYes
sql-modeYesYesYes
- Variable: sql_modeYesBothYes
sql_notesYesSessionYes
sql_quote_show_createYesSessionYes
sql_safe_updatesYesSessionYes
sql_select_limitYesBothYes
sql_slave_skip_counterYesGlobalYes
sql_warningsYesSessionYes
ssl-caYesYesNo
- Variable: ssl_caYesGlobalNo
ssl-capathYesYesNo
- Variable: ssl_capathYesGlobalNo
ssl-certYesYesNo
- Variable: ssl_certYesGlobalNo
ssl-cipherYesYesNo
- Variable: ssl_cipherYesGlobalNo
ssl-keyYesYesNo
- Variable: ssl_keyYesGlobalNo
storage_engineYesBothYes
sync_binlogYesYesYesGlobalYes
sync_frmYesYesYesGlobalYes
system_time_zoneYesGlobalNo
table_cacheYesYesYesGlobalYes
table_definition_cacheYesYesYesGlobalYes
table_lock_wait_timeoutYesYesYesGlobalYes
table_open_cacheYesYesYesGlobalYes
table_typeYesBothYes
thread_cache_sizeYesYesYesGlobalYes
thread_concurrencyYesYesYesGlobalNo
thread_handlingYesYesYesGlobalNo
thread_stackYesYesYesGlobalNo
time_formatYesYesYesBothNo
time_zoneYesYesYesBothYes
timed_mutexesYesYesYesGlobalYes
timestampYesSessionYes
tmp_table_sizeYesYesYesBothYes
tmpdirYesYesYesGlobalNo
transaction_alloc_block_sizeYesYesYesBothYes
transaction_allow_batchingYesSessionYes
transaction_prealloc_sizeYesYesYesBothYes
tx_isolationYesBothYes
unique_checksYesSessionYes
updatable_views_with_limitYesYesYesBothYes
versionYesYesYesGlobalNo
version_commentYesGlobalNo
version_compile_machineYesGlobalNo
version_compile_osYesGlobalNo
wait_timeoutYesYesYesBothYes
warning_countYesSessionNo

[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

[b] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

For additional system variable information, see these sections:

Note

Some of the following variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not.

Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.

Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.

Some system variables take file name values. Unless otherwise specified, the default file location is the data directory if the value is a relative path name. To specify the location explicitly, use an absolute path name. Suppose that the data directory is /var/mysql/data. If a file-valued variable is given as a relative path name, it will be located under /var/mysql/data. If the value is an absolute path name, its location is as given by the path name.

  • autocommit

    The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. By default, client connections begin with autocommit set to 1. If you change autocommit mode from 0 to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 12.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

  • automatic_sp_privileges
    Variable Nameautomatic_sp_privileges
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values
    Typeboolean
    DefaultTRUE

    When this variable has a value of 1 (the default), the 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. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.

    The creator of a routine is the account used to execute the CREATE statement for it. This might not be the same as the account named as the DEFINER in the routine definition.

    See also Section 19.2.2, “Stored Routines and MySQL Privileges”.

  • back_log
    Command-Line Format--back_log=#
    Config-File Formatback_log
    Option Sets VariableYes, back_log
    Variable Nameback_log
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values
    Typenumeric
    Default50
    Range1-65535

    The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time.

    In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix listen() system call should have more details. Check your OS documentation for the maximum value for this variable. back_log cannot be set higher than your operating system limit.

  • basedir
    Command-Line Format--basedir=name
    Config-File Formatbasedir
    Option Sets VariableYes, basedir
    Variable Namebasedir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values
    Typefilename

    The MySQL installation base directory. This variable can be set with the --basedir option. Relative path names for other variables usually are resolved relative to the base directory.

  • big_tables

    If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The table tbl_name is full does not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable, because in-memory tables are automatically converted to disk-based tables as required.

    Note

SLEEP()

via MySQL :: MySQL 5.0 Reference Manual :: 16.4.1 Replication Features and Issues.

MySQL :: MySQL 5.0 Reference Manual :: 16.4.1 Replication Features and Issues was last modified: May 18th, 2010 by Lava Kafle

Post Your Comments:

Your email address will not be published. Required fields are marked *