Top Stories

Lava Kafle

HIKE

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

  • 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

Name Cmd-Line Option file System Var Var Scope Dynamic
auto_increment_increment Yes Yes Yes Both Yes
auto_increment_offset Yes Yes Yes Both Yes
autocommit Yes Session Yes
automatic_sp_privileges Yes Global Yes
back_log Yes Yes Yes Global No
basedir Yes Yes Yes Global No
big-tables Yes Yes Yes
Variable: big_tables Yes Session Yes
binlog_cache_size Yes Yes Yes Global Yes
binlog_direct_non_transactional_updates Yes Yes Yes Both Yes
binlog-format Yes Yes Yes
Variable: binlog_format Yes Both Yes
bulk_insert_buffer_size Yes Yes Yes Both Yes
character_set_client Yes Both Yes
character_set_connection Yes Both Yes
character_set_database[a] Yes Both Yes
character-set-filesystem Yes Yes Yes
Variable: character_set_filesystem Yes Both Yes
character_set_results Yes Both Yes
character-set-server Yes Yes Yes
Variable: character_set_server Yes Both Yes
character_set_system Yes Global No
character-sets-dir Yes Yes No
Variable: character_sets_dir Yes Global No
collation_connection Yes Both Yes
collation_database[b] Yes Both Yes
collation-server Yes Yes Yes
Variable: collation_server Yes Both Yes
completion_type Yes Yes Yes Both Yes
concurrent_insert Yes Yes Yes Global Yes
connect_timeout Yes Yes Yes Global Yes
datadir Yes Yes Yes Global No
date_format Yes Both No
datetime_format Yes Yes Yes Both No
debug Yes Yes Yes Both Yes
debug_sync Yes Both Yes
default-storage-engine Yes Yes Yes Both Yes
default_week_format Yes Yes Yes Both Yes
delay-key-write Yes Yes Yes
Variable: delay_key_write Yes Global Yes
delayed_insert_limit Yes Yes Yes Global Yes
delayed_insert_timeout Yes Yes Yes Global Yes
delayed_queue_size Yes Yes Yes Global Yes
div_precision_increment Yes Yes Yes Both Yes
engine-condition-pushdown Yes Yes Yes
Variable: engine_condition_pushdown Yes Both Yes
error_count Yes Session No
event-scheduler Yes Yes Yes
Variable: event_scheduler Yes Global Yes
expire_logs_days Yes Yes Yes Global Yes
flush Yes Yes Yes Global Yes
flush_time Yes Yes Yes Global Yes
foreign_key_checks Yes Session Yes
ft_boolean_syntax Yes Yes Yes Global Yes
ft_max_word_len Yes Yes Yes Global No
ft_min_word_len Yes Yes Yes Global No
ft_query_expansion_limit Yes Yes Yes Global No
ft_stopword_file Yes Yes Yes Global No
general-log Yes Yes Yes
Variable: general_log Yes Global Yes
general_log_file Yes Yes Yes Global Yes
group_concat_max_len Yes Yes Yes Both Yes
have_archive Yes Global No
have_blackhole_engine Yes Global No
have_community_features Yes Global No
have_compress Yes Global No
have_crypt Yes Global No
have_csv Yes Global No
have_dynamic_loading Yes Global No
have_example_engine Yes Global No
have_federated_engine Yes Global No
have_geometry Yes Global No
have_innodb Yes Global No
have_isam Yes Global No
have_merge_engine Yes Global No
have_ndbcluster Yes Global No
have_openssl Yes Global No
have_partitioning Yes Global No
have_query_cache Yes Global No
have_raid Yes Global No
have_row_based_replication Yes Global No
have_rtree_keys Yes Global No
have_ssl Yes Global No
have_symlink Yes Global No
hostname Yes Global No
identity Yes Session Yes
ignore_builtin_innodb Yes Yes Yes Global No
init_connect Yes Yes Yes Global Yes
init-file Yes Yes No
Variable: init_file Yes Global No
init_slave Yes Yes Yes Global Yes
innodb_adaptive_flushing Yes Yes Yes Global Yes
innodb_adaptive_hash_index Yes Yes Yes Global No
innodb_additional_mem_pool_size Yes Yes Yes Global No
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_autoinc_lock_mode Yes Yes Yes Global No
innodb_buffer_pool_awe_mem_mb Yes Yes Yes Global No
innodb_buffer_pool_size Yes Yes Yes Global No
innodb_change_buffering Yes Yes Yes Global Yes
innodb_checksums Yes Yes Yes Global No
innodb_commit_concurrency Yes Yes Yes Global Yes
innodb_concurrency_tickets Yes Yes Yes Global Yes
innodb_data_file_path Yes Yes Yes Global No
innodb_data_home_dir Yes Yes Yes Global No
innodb_doublewrite Yes Yes Yes Global No
innodb_fast_shutdown Yes Yes Yes Global Yes
innodb_file_format Yes Yes Yes Global Yes
innodb_file_format_check Yes Yes Yes Global Yes
innodb_file_io_threads Yes Yes Yes Global No
innodb_file_per_table Yes Yes Yes Global No
innodb_flush_log_at_trx_commit Yes Yes Yes Global Yes
innodb_flush_method Yes Yes Yes Global No
innodb_force_recovery Yes Yes Yes Global No
innodb_io_capacity Yes Yes Yes Global No
innodb_lock_wait_timeout Yes Yes Yes Both Yes
innodb_locks_unsafe_for_binlog Yes Yes Yes Global No
innodb_log_arch_dir Yes Yes Yes Global No
innodb_log_archive Yes Yes Yes Global No
innodb_log_buffer_size Yes Yes Yes Global No
innodb_log_file_size Yes Yes Yes Global No
innodb_log_files_in_group Yes Yes Yes Global No
innodb_log_group_home_dir Yes Yes Yes Global No
innodb_max_dirty_pages_pct Yes Yes Yes Global Yes
innodb_max_purge_lag Yes Yes Yes Global Yes
innodb_mirrored_log_groups Yes Yes Yes Global No
innodb_old_blocks_pct Yes Yes Yes Global Yes
innodb_old_blocks_time Yes Yes Yes Global Yes
innodb_open_files Yes Yes Yes Global No
innodb_read_ahead_threshold Yes Yes Yes Global Yes
innodb_read_io_threads Yes Yes Yes Global No
innodb_replication_delay Yes Yes Yes Global Yes
innodb_rollback_on_timeout Yes Yes Yes Global No
innodb_spin_wait_delay Yes Yes Yes Global Yes
innodb_stats_on_metadata Yes Yes Yes Global Yes
innodb_stats_sample_pages Yes Yes Yes Global Yes
innodb_strict_mode Yes Yes Yes Both Yes
innodb_support_xa Yes Yes Yes Both Yes
innodb_sync_spin_loops Yes Yes Yes Global Yes
innodb_table_locks Yes Yes Yes Both Yes
innodb_thread_concurrency Yes Yes Yes Global Yes
innodb_thread_sleep_delay Yes Yes Yes Global Yes
innodb_use_legacy_cardinality_algorithm Yes Yes Yes Global Yes
innodb_use_sys_malloc Yes Yes Yes Global No
innodb_version Yes Global No
innodb_write_io_threads Yes Yes Yes Global No
insert_id Yes Session Yes
interactive_timeout Yes Yes Yes Both Yes
join_buffer_size Yes Yes Yes Both Yes
keep_files_on_create Yes Yes Yes Both Yes
key_buffer_size Yes Yes Yes Global Yes
key_cache_age_threshold Yes Yes Yes Global Yes
key_cache_block_size Yes Yes Yes Global Yes
key_cache_division_limit Yes Yes Yes Global Yes
language Yes Yes Yes Global No
large_files_support Yes Global No
large_page_size Yes Global No
large-pages Yes Yes No
Variable: large_pages Yes Global No
last_insert_id Yes Session Yes
lc_time_names Yes Both Yes
license Yes Global No
local_infile Yes Global Yes
locked_in_memory Yes Global No
log Yes Yes Yes Global Yes
log_bin Yes Global No
log-bin Yes Yes Yes Global No
log-bin-trust-function-creators Yes Yes Yes
Variable: log_bin_trust_function_creators Yes Global Yes
log-bin-trust-routine-creators Yes Yes Yes
Variable: log_bin_trust_routine_creators Yes Global Yes
log-error Yes Yes No
Variable: log_error Yes Global No
log-output Yes Yes Yes
Variable: log_output Yes Global Yes
log-queries-not-using-indexes Yes Yes Yes
Variable: log_queries_not_using_indexes Yes Global Yes
log-slave-updates Yes Yes No
Variable: log_slave_updates Yes Global No
log-slow-queries Yes Yes Yes
Variable: log_slow_queries Yes Global Yes
log-warnings Yes Yes Yes
Variable: log_warnings Yes Both Yes
long_query_time Yes Yes Yes Both Yes
low-priority-updates Yes Yes Yes
Variable: low_priority_updates Yes Both Yes
lower_case_file_system Yes Yes Yes Global No
lower_case_table_names Yes Yes Yes Global No
master-bind Yes Yes Yes No
max_allowed_packet Yes Yes Yes Global Yes
max_binlog_cache_size Yes Yes Yes Global Yes
max_binlog_size Yes Yes Yes Global Yes
max_connect_errors Yes Yes Yes Global Yes
max_connections Yes Yes Yes Global Yes
max_delayed_threads Yes Yes Yes Both Yes
max_error_count Yes Yes Yes Both Yes
max_heap_table_size Yes Yes Yes Both Yes
max_insert_delayed_threads Yes Both Yes
max_join_size Yes Yes Yes Both Yes
max_length_for_sort_data Yes Yes Yes Both Yes
max_prepared_stmt_count Yes Yes Yes Global Yes
max_relay_log_size Yes Yes Yes Global Yes
max_seeks_for_key Yes Yes Yes Both Yes
max_sort_length Yes Yes Yes Both Yes
max_sp_recursion_depth Yes Yes Yes Both Yes
max_tmp_tables Yes Yes Yes Both Yes
max_user_connections Yes Yes Yes Both Yes
max_write_lock_count Yes Yes Yes Global Yes
memlock Yes Yes Yes Global No
min-examined-row-limit Yes Yes Yes Both Yes
multi_range_count Yes Yes Yes Both Yes
myisam_data_pointer_size Yes Yes Yes Global Yes
myisam_max_sort_file_size Yes Yes Yes Global Yes
myisam_mmap_size Yes Yes Yes Global No
myisam_recover_options Yes Global No
myisam_repair_threads Yes Yes Yes Both Yes
myisam_sort_buffer_size Yes Yes Yes Both Yes
myisam_stats_method Yes Yes Yes Both Yes
myisam_use_mmap Yes Yes Yes Global Yes
named_pipe Yes Global No
ndb_autoincrement_prefetch_sz Yes Yes Yes Both Yes
ndb-batch-size Yes Yes Yes Global No
ndb_cache_check_time Yes Yes Yes Global Yes
ndb_extra_logging Yes Yes Yes Global Yes
ndb_force_send Yes Yes Yes Both Yes
ndb_log_bin Yes Yes Both Yes
ndb_log_binlog_index Yes Yes Global Yes
ndb_log_empty_epochs Yes Yes Yes Global Yes
ndb_log_orig Yes Global No
ndb-log-update-as-write Yes Yes Yes Global Yes
ndb_log_updated_only Yes Yes Yes Global Yes
ndb_optimization_delay Yes Global Yes
ndb_table_no_logging Yes Session Yes
ndb_table_temporary Yes Session Yes
ndb_use_copying_alter_table Yes Both No
ndb_use_exact_count Yes Both Yes
ndb_use_transactions Yes Yes Yes Both Yes
ndb_wait_connected Yes Yes Yes No
ndb-wait-setup Yes Yes Yes No
ndbinfo_database Yes No
ndbinfo_max_bytes Yes Yes Both Yes
ndbinfo_max_rows Yes Yes Both Yes
ndbinfo_show_hidden Yes Yes Both Yes
ndbinfo_table_prefix Yes Yes Both Yes
ndbinfo_version Yes Global No
net_buffer_length Yes Yes Yes Both Yes
net_read_timeout Yes Yes Yes Both Yes
net_retry_count Yes Yes Yes Both Yes
net_write_timeout Yes Yes Yes Both Yes
new Yes Yes Yes Both Yes
old Yes Yes Yes Global No
old-alter-table Yes Yes Yes
Variable: old_alter_table Yes Both Yes
old-passwords Yes Yes Yes
Variable: old_passwords Yes Both Yes
open-files-limit Yes Yes No
Variable: open_files_limit Yes Global No
optimizer_prune_level Yes Yes Yes Both Yes
optimizer_search_depth Yes Yes Yes Both Yes
optimizer_switch Yes Yes Yes Both Yes
partition Yes Yes No
Variable: have_partitioning Yes Global No
pid-file Yes Yes No
Variable: pid_file Yes Global No
plugin_dir Yes Yes Yes Global No
port Yes Yes Yes Global No
preload_buffer_size Yes Yes Yes Both Yes
prepared_stmt_count Yes Global No
profiling Yes Session Yes
profiling_history_size Yes Both Yes
protocol_version Yes Global No
pseudo_thread_id Yes Session Yes
query_alloc_block_size Yes Yes Yes Both Yes
query_cache_limit Yes Yes Yes Global Yes
query_cache_min_res_unit Yes Yes Yes Global Yes
query_cache_size Yes Yes Yes Global Yes
query_cache_type Yes Yes Yes Both Yes
query_cache_wlock_invalidate Yes Yes Yes Both Yes
query_prealloc_size Yes Yes Yes Both Yes
rand_seed1 Yes Session Yes
rand_seed2 Yes Session Yes
range_alloc_block_size Yes Yes Yes Both Yes
read_buffer_size Yes Yes Yes Both Yes
read_only Yes Yes Yes Global Yes
read_rnd_buffer_size Yes Yes Yes Both Yes
relay_log_purge Yes Yes Yes Global Yes
relay_log_space_limit Yes Yes Yes Global No
report-host Yes Yes No
Variable: report_host Yes Global No
report-password Yes Yes No
Variable: report_password Yes Global No
report-port Yes Yes No
Variable: report_port Yes Global No
report-user Yes Yes No
Variable: report_user Yes Global No
rpl_recovery_rank Yes Global Yes
safe-show-database Yes Yes Yes Global Yes
secure-auth Yes Yes Yes
Variable: secure_auth Yes Global Yes
secure-file-priv Yes Yes No
Variable: secure_file_priv Yes Global No
server-id Yes Yes Yes
Variable: server_id Yes Global Yes
shared_memory Yes Global No
shared_memory_base_name Yes Global No
skip-external-locking Yes Yes No
Variable: skip_external_locking Yes Global No
skip-name-resolve Yes Yes Yes Global No
skip-networking Yes Yes No
Variable: skip_networking Yes Global No
skip-show-database Yes Yes No
Variable: skip_show_database Yes Global No
slave-allow-batching Yes Yes Yes
Variable: slave_allow_batching Yes Global Yes
slave_compressed_protocol Yes Yes Yes Global Yes
slave_exec_mode Yes Global Yes
slave-load-tmpdir Yes Yes No
Variable: slave_load_tmpdir Yes Global No
slave-net-timeout Yes Yes Yes
Variable: slave_net_timeout Yes Global Yes
slave-skip-errors Yes Yes No
Variable: slave_skip_errors Yes Global No
slave_transaction_retries Yes Yes Yes Global Yes
slave_type_conversions Yes Yes Yes Global No
slow_launch_time Yes Yes Yes Global Yes
slow-query-log Yes Yes Yes
Variable: slow_query_log Yes Global Yes
slow_query_log_file Yes Yes Yes Global Yes
socket Yes Yes Yes Global No
sort_buffer_size Yes Yes Yes Both Yes
sql_auto_is_null Yes Session Yes
sql_big_selects Yes Both Yes
sql_big_tables Yes Session Yes
sql_buffer_result Yes Session Yes
sql_log_bin Yes Session Yes
sql_log_off Yes Session Yes
sql_log_update Yes Session Yes
sql_low_priority_updates Yes Both Yes
sql_max_join_size Yes Both Yes
sql-mode Yes Yes Yes
Variable: sql_mode Yes Both Yes
sql_notes Yes Session Yes
sql_quote_show_create Yes Session Yes
sql_safe_updates Yes Session Yes
sql_select_limit Yes Both Yes
sql_slave_skip_counter Yes Global Yes
sql_warnings Yes Session Yes
ssl-ca Yes Yes No
Variable: ssl_ca Yes Global No
ssl-capath Yes Yes No
Variable: ssl_capath Yes Global No
ssl-cert Yes Yes No
Variable: ssl_cert Yes Global No
ssl-cipher Yes Yes No
Variable: ssl_cipher Yes Global No
ssl-key Yes Yes No
Variable: ssl_key Yes Global No
storage_engine Yes Both Yes
sync_binlog Yes Yes Yes Global Yes
sync_frm Yes Yes Yes Global Yes
system_time_zone Yes Global No
table_cache Yes Yes Yes Global Yes
table_definition_cache Yes Yes Yes Global Yes
table_lock_wait_timeout Yes Yes Yes Global Yes
table_open_cache Yes Yes Yes Global Yes
table_type Yes Both Yes
thread_cache_size Yes Yes Yes Global Yes
thread_concurrency Yes Yes Yes Global No
thread_handling Yes Yes Yes Global No
thread_stack Yes Yes Yes Global No
time_format Yes Yes Yes Both No
time_zone Yes Yes Yes Both Yes
timed_mutexes Yes Yes Yes Global Yes
timestamp Yes Session Yes
tmp_table_size Yes Yes Yes Both Yes
tmpdir Yes Yes Yes Global No
transaction_alloc_block_size Yes Yes Yes Both Yes
transaction_allow_batching Yes Session Yes
transaction_prealloc_size Yes Yes Yes Both Yes
tx_isolation Yes Both Yes
unique_checks Yes Session Yes
updatable_views_with_limit Yes Yes Yes Both Yes
version Yes Yes Yes Global No
version_comment Yes Global No
version_compile_machine Yes Global No
version_compile_os Yes Global No
wait_timeout Yes Yes Yes Both Yes
warning_count Yes Session No

[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 Name automatic_sp_privileges
    Variable Scope Global
    Dynamic Variable Yes
    Permitted Values
    Type boolean
    Default TRUE

    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 Format back_log
    Option Sets Variable Yes, back_log
    Variable Name back_log
    Variable Scope Global
    Dynamic Variable No
    Permitted Values
    Type numeric
    Default 50
    Range 1-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 Format basedir
    Option Sets Variable Yes, basedir
    Variable Name basedir
    Variable Scope Global
    Dynamic Variable No
    Permitted Values
    Type filename

    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.

Leave a Reply

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