Contents

  1. About the Server System Variables
  2. Setting Server System Variables
  3. List of Server System Variables
    1. aria_block_size
    2. aria_checkpoint_interval
    3. aria_checkpoint_log_activity
    4. aria_encrypt_tables
    5. aria_force_start_after_recovery_failures
    6. aria_group_commit
    7. aria_group_commit_interval
    8. aria_log_file_size
    9. aria_log_purge_type
    10. aria_max_sort_file_size
    11. aria_page_checksum
    12. aria_pagecache_age_threshold
    13. aria_pagecache_buffer_size
    14. aria_pagecache_division_limit
    15. aria_pagecache_file_hash_size
    16. aria_recover
    17. aria_repair_threads
    18. aria_sort_buffer_size
    19. aria_stats_method
    20. aria_sync_log_dir
    21. aria_used_for_temp_tables
    22. auto_increment_increment
    23. auto_increment_offset
    24. autocommit
    25. automatic_sp_privileges
    26. back_log
    27. basedir
    28. big_tables
    29. binlog_annotate_row_events
    30. binlog_cache_size
    31. binlog_checksum
    32. binlog_commit_wait_count
    33. binlog_commit_wait_usec
    34. binlog_direct_non_transactional_updates
    35. binlog_format
    36. binlog_optimize_thread_scheduling
    37. binlog_row_image
    38. binlog_stmt_cache_size
    39. bulk_insert_buffer_size
    40. cassandra_default_thrift_host
    41. cassandra_failure_retries
    42. cassandra_insert_batch_size
    43. cassandra_multiget_batch_size
    44. cassandra_read_consistency
    45. cassandra_rnd_batch_size
    46. cassandra_write_consistency
    47. character_set_client
    48. character_set_connection
    49. character_set_database
    50. character_set_filesystem
    51. character_set_results
    52. character_set_server
    53. character_set_system
    54. character_sets_dir
    55. check_constraint_checks
    56. collation_connection
    57. collation_database
    58. collation_server
    59. completion_type
    60. concurrent_insert
    61. connect_conv_size
    62. connect_exact_info
    63. connect_indx_map
    64. connect_java_wrapper
    65. connect_json_grp_size
    66. connect_jvm_path
    67. connect_timeout
    68. connect_type_conv
    69. connect_use_tempfile
    70. connect_work_size
    71. connect_xtrace
    72. cracklib_password_check
    73. cracklib_password_check_dictionary
    74. datadir
    75. date_format
    76. datetime_format
    77. deadlock_search_depth_long
    78. deadlock_search_depth_short
    79. deadlock_timeout_long
    80. deadlock_timeout_short
    81. debug
    82. debug_no_thread_alarm
    83. debug_sync
    84. default_master_connection
    85. default_regex_flags
    86. default_storage_engine
    87. default_table_type
    88. default_tmp_storage_engine
    89. default_week_format
    90. delay_key_write
    91. delayed_insert_limit
    92. delayed_insert_timeout
    93. delayed_queue_size
    94. div_precision_increment
    95. encrypt_binlog
    96. encrypt_tmp_disk_tables
    97. encrypt_tmp_files
    98. encryption_algorithm
    99. enforce_storage_engine
    100. engine_condition_pushdown
    101. error_count
    102. event_scheduler
    103. expensive_subquery_limit
    104. expire_logs_days
    105. explicit_defaults_for_timestamp
    106. external_user
    107. extra_max_connections
    108. extra_port
    109. feedback
    110. feedback_http_proxy
    111. feedback_send_retry_wait
    112. feedback_send_timeout
    113. feedback_url
    114. feedback_user_info
    115. flush
    116. flush_time
    117. foreign_key_checks
    118. ft_boolean_syntax
    119. ft_max_word_len
    120. ft_min_word_len
    121. ft_query_expansion_limit
    122. ft_stopword_file
    123. general_log
    124. general_log_file
    125. group_concat_max_len
    126. gtid_binlog_pos
    127. gtid_binlog_state
    128. gtid_current_pos
    129. gtid_domain_id
    130. gtid_ignore_duplicates
    131. gtid_seq_no
    132. gtid_slave_pos
    133. gtid_strict_mode
    134. handlersocket_accept_balance
    135. handlersocket_address
    136. handlersocket_backlog
    137. handlersocket_epoll
    138. handlersocket_port
    139. handlersocket_port_wr
    140. handlersocket_sndbuf
    141. handlersocket_rcvbuf
    142. handlersocket_readsize
    143. handlersocket_threads
    144. handlersocket_threads_wr
    145. handlersocket_timeout
    146. handlersocket_verbose
    147. handlersocket_wrlock_timeout
    148. have_compress
    149. have_crypt
    150. have_csv
    151. have_dynamic_loading
    152. have_geometry
    153. have_innodb
    154. have_ndbcluster
    155. have_openssl
    156. have_partitioning
    157. have_profiling
    158. have_query_cache
    159. have_rtree_keys
    160. have_ssl
    161. have_symlink
    162. histogram_size
    163. histogram_type
    164. host_cache_size
    165. hostname
    166. identity
    167. ignore_builtin_innodb
    168. ignore_db_dirs
    169. in_transaction
    170. init_connect
    171. init_file
    172. init_slave
    173. innodb_adaptive_checkpoint
    174. innodb_adaptive_flushing
    175. innodb_adaptive_flushing_lwm
    176. innodb_adaptive_flushing_method
    177. innodb_adaptive_hash_index
    178. innodb_adaptive_hash_index_partitions
    179. innodb_adaptive_hash_index_parts
    180. innodb_adaptive_max_sleep_delay
    181. innodb_additional_mem_pool_size
    182. innodb_api_bk_commit_interval
    183. innodb_api_disable_rowlock
    184. innodb_api_enable_binlog
    185. innodb_api_enable_mdl
    186. innodb_api_trx_level
    187. innodb_auto_lru_dump
    188. innodb_autoextend_increment
    189. innodb_autoinc_lock_mode
    190. innodb_background_scrub_data_check_interval
    191. innodb_background_scrub_data-compressed
    192. innodb_background_scrub_data_interval
    193. innodb_background_scrub_data-uncompressed
    194. innodb_blocking_buffer_pool_restore
    195. innodb_buf_dump_status_frequency
    196. innodb_buffer_pool_chunk_size
    197. innodb_buffer_pool_dump_at_shutdown
    198. innodb_buffer_pool_dump_now
    199. innodb_buffer_pool_evict
    200. innodb_buffer_pool_filename
    201. innodb_buffer_pool_instances
    202. innodb_buffer_pool_load_abort
    203. innodb_buffer_pool_load_at_startup
    204. innodb_buffer_pool_load_now
    205. innodb_buffer_pool_populate
    206. innodb_buffer_pool_restore_at_startup
    207. innodb_buffer_pool_shm_checksum
    208. innodb_buffer_pool_shm_key
    209. innodb_buffer_pool_size
    210. innodb_change_buffer_max_size
    211. innodb_change_buffering
    212. innodb_change_buffering_debug
    213. innodb_checkpoint_age_target
    214. innodb_checksum_algorithm
    215. innodb_checksums
    216. innodb_cleaner_lsn_age_factor
    217. innodb_cmp_per_index_enabled
    218. innodb_commit_concurrency
    219. innodb_compression_algorithm
    220. innodb_compression_default
    221. innodb_compression_failure_threshold_pct
    222. innodb_compression_level
    223. innodb_compression_pad_pct_max
    224. innodb_concurrency_tickets
    225. innodb_corrupt_table_action
    226. innodb_data_file_path
    227. innodb_data_home_dir
    228. innodb_default_encryption_key_id
    229. innodb_default_page_encryption_key
    230. innodb_default_row_format
    231. innodb_defragment
    232. innodb_defragment_fill_factor
    233. innodb_defragment_fill_factor_n_recs
    234. innodb_defragment_frequency
    235. innodb_defragment_n_pages
    236. innodb_defragment_stats_accuracy
    237. innodb_dict_size_limit
    238. innodb_disable_sort_file_cache
    239. innodb_disallow_writes
    240. innodb_doublewrite
    241. innodb_doublewrite_file
    242. innodb_empty_free_list_algorithm
    243. innodb_enable_unsafe_group_commit
    244. innodb_encrypt_log
    245. innodb_encrypt_tables
    246. innodb_encryption_rotate_key_age
    247. innodb_encryption_rotation_iops
    248. innodb_encryption_threads
    249. innodb_extra_rsegments
    250. innodb_extra_undoslots
    251. innodb_fake_changes
    252. innodb_fast_checksum
    253. innodb_fast_shutdown
    254. innodb_fatal_semaphore_wait_threshold
    255. innodb_file_format
    256. innodb_file_format_check
    257. innodb_file_format_max
    258. innodb_file_per_table
    259. innodb_fill_factor
    260. innodb_flush_log_at_timeout
    261. innodb_flush_log_at_trx_commit
    262. innodb_flush_method
    263. innodb_flush_neighbor_pages
    264. innodb_flush_neighbors
    265. innodb_flush_sync
    266. innodb_flushing_avg_loops
    267. innodb_force_load_corrupted
    268. innodb_force_primary_key
    269. innodb_force_recovery
    270. innodb_foreground_preflush
    271. innodb_ft_aux_table
    272. innodb_ft_cache_size
    273. innodb_ft_enable_diag_print
    274. innodb_ft_enable_stopword
    275. innodb_ft_max_token_size
    276. innodb_ft_min_token_size
    277. innodb_ft_num_word_optimize
    278. innodb_ft_result_cache_limit
    279. innodb_ft_server_stopword_table
    280. innodb_ft_sort_pll_degree
    281. innodb_ft_total_cache_size
    282. innodb_ft_user_stopword_table
    283. innodb_ibuf_accel_rate
    284. innodb_ibuf_active_contract
    285. innodb_ibuf_max_size
    286. innodb_idle_flush_pct
    287. innodb_immediate_scrub_data-uncompressed
    288. innodb_import_table_from_xtrabackup
    289. innodb_instrument_semaphores
    290. innodb_io_capacity
    291. innodb_io_capacity_max
    292. innodb_kill_idle_transaction
    293. innodb_large_prefix
    294. innodb_lazy_drop_table
    295. innodb_lock_schedule_algorithm
    296. innodb_lock_wait_timeout
    297. innodb_locking_fake_changes
    298. innodb_locks_unsafe_for_binlog
    299. innodb_log_arch_dir
    300. innodb_log_arch_expire_sec
    301. innodb_log_archive
    302. innodb_log_block_size
    303. innodb_log_buffer_size
    304. innodb_log_checksum_algorithm
    305. innodb_log_checksums
    306. innodb_log_compressed_pages
    307. innodb_log_file_size
    308. innodb_log_files_in_group
    309. innodb_log_group_home_dir
    310. innodb_log_write_ahead_size
    311. innodb_lru_scan_depth
    312. innodb_max_bitmap_file_size
    313. innodb_max_changed_pages
    314. innodb_max_dirty_pages_pct
    315. innodb_max_dirty_pages_pct_lwm
    316. innodb_max_purge_lag
    317. innodb_max_purge_lag_delay
    318. innodb_max_undo_log_size
    319. innodb_merge_sort_block_size
    320. innodb_mirrored_log_groups
    321. innodb_monitor_disable
    322. innodb_monitor_enable
    323. innodb_monitor_reset
    324. innodb_monitor_reset_all
    325. innodb_mtflush_threads
    326. innodb_old_blocks_pct
    327. innodb_old_blocks_time
    328. innodb_online_alter_log_max_size
    329. innodb_open_files
    330. innodb_optimize_fulltext_only
    331. innodb_page_cleaners
    332. innodb_page_size
    333. innodb_prefix_index_cluster_optimization
    334. innodb_print_all_deadlocks
    335. innodb_purge_batch_size
    336. innodb_purge_rseg_truncate_frequency
    337. innodb_purge_threads
    338. innodb_random_read_ahead
    339. innodb_read_ahead
    340. innodb_read_ahead_threshold
    341. innodb_read_io_threads
    342. innodb_read_only
    343. innodb_recovery_stats
    344. innodb_recovery_update_relay_log
    345. innodb_replication_delay
    346. innodb_rollback_on_timeout
    347. innodb_rollback_segments
    348. innodb_sched_priority_cleaner
    349. innodb_scrub_log
    350. innodb_scrub_log_interval
    351. innodb_scrub_log_speed
    352. innodb_show_locks_held
    353. innodb_show_verbose_locks
    354. innodb_simulate_comp_failures
    355. innodb_sort_buffer_size
    356. innodb_spin_wait_delay
    357. innodb_stats_auto_recalc
    358. innodb_stats_auto_update
    359. innodb_stats_method
    360. innodb_stats_modified_counter
    361. innodb_stats_on_metadata
    362. innodb_stats_persistent
    363. innodb_stats_persistent_sample_pages
    364. innodb_stats_sample_pages
    365. innodb_stats_traditional
    366. innodb_stats_transient_sample_pages
    367. innodb_stats_update_need_lock
    368. innodb_status_output
    369. innodb_status_output_locks
    370. innodb_strict_mode
    371. innodb_support_xa
    372. innodb_sync_array_size
    373. innodb_sync_spin_loops
    374. innodb_table_locks
    375. innodb_temp_data_file_path
    376. innodb_thread_concurrency
    377. innodb_thread_concurrency_timer_based
    378. innodb_thread_sleep_delay
    379. innodb_tmpdir
    380. innodb_track_changed_pages
    381. innodb_undo_directory
    382. innodb_undo_logs
    383. innodb_use_atomic_writes
    384. innodb_use_fallocate
    385. innodb_use_global_flush_log_at_trx_commit
    386. innodb_use_native_aio
    387. innodb_use_purge_thread
    388. innodb_use_stacktrace
    389. innodb_use_sys_malloc
    390. innodb_use_sys_stats_table
    391. innodb_version
    392. innodb_write_io_threads
    393. insert_id
    394. interactive_timeout
    395. join_buffer_size
    396. join_buffer_space_limit
    397. join_cache_level
    398. keep_files_on_create
    399. key_buffer_size
    400. key_cache_age_threshold
    401. key_cache_block_size
    402. key_cache_division_limit
    403. key_cache_file_hash_size
    404. key_cache_segments
    405. language
    406. large_files_support
    407. large_page_size
    408. large_pages
    409. last_gtid
    410. last_insert_id
    411. lc_messages
    412. lc_messages_dir
    413. lc_time_names
    414. license
    415. local_infile
    416. lock_wait_timeout
    417. locked_in_memory
    418. log
    419. log_bin
    420. log_bin_basename
    421. log_bin_index
    422. log_bin_trust_function_creators
    423. log_error
    424. log_output
    425. log_queries_not_using_indexes
    426. log_slave_updates
    427. log_slow_admin_statements
    428. log_slow_filter
    429. log_slow_queries
    430. log_slow_rate_limit
    431. log_slow_slave_statements
    432. log_slow_verbosity
    433. log_tc_size
    434. log_warnings
    435. long_query_time
    436. low_priority_updates
    437. lower_case_file_system
    438. lower_case_table_names
    439. master_retry_count
    440. master_verify_checksum
    441. max_allowed_packet
    442. max_binlog_cache_size
    443. max_binlog_size
    444. max_binlog_stmt_cache_size
    445. max_connect_errors
    446. max_connections
    447. max_delayed_threads
    448. max_digest_length
    449. max_error_count
    450. max_heap_table_size
    451. max_insert_delayed_threads
    452. max_join_size
    453. max_length_for_sort_data
    454. max_long_data_size
    455. max_prepared_stmt_count
    456. max_recursive_iterations
    457. max_relay_log_size
    458. max_seeks_for_key
    459. max_session_mem_used
    460. max_sort_length
    461. max_sp_recursion_depth
    462. max_statement_time
    463. max_tmp_tables
    464. max_user_connections
    465. max_write_lock_count
    466. metadata_locks_cache_size
    467. metadata_locks_hash_instances
    468. min_examined_row_limit
    469. mroonga_action_on_fulltext_query_error
    470. mroonga_default_parser
    471. mroonga_dry_write
    472. mroonga_enable_optimization
    473. mroonga_libgroonga_version
    474. mroonga_log_file
    475. mroonga_log_level
    476. mroonga_match_escalation_threshold
    477. mroonga_vector_column_delimiter
    478. mroonga_version
    479. mrr_buffer_size
    480. multi_range_count
    481. myisam_block_size
    482. myisam_data_pointer_size
    483. myisam_max_sort_file_size
    484. myisam_mmap_size
    485. myisam_recover_options
    486. myisam_repair_threads
    487. myisam_sort_buffer_size
    488. myisam_stats_method
    489. myisam_use_mmap
    490. mysql56_temporal_format
    491. named_pipe
    492. net_buffer_length
    493. net_read_timeout
    494. net_retry_count
    495. net_write_timeout
    496. old
    497. old_alter_table
    498. old_mode
    499. old_passwords
    500. open_files_limit
    501. optimizer_prune_level
    502. optimizer_search_depth
    503. optimizer_selectivity_sampling_limit
    504. optimizer_switch
    505. optimizer_use_condition_selectivity
    506. oqgraph_allow_create_integer_latch
    507. pam_use_cleartext_plugin
    508. performance_schema
    509. performance_schema_accounts_size
    510. performance_schema_digests_size
    511. performance_schema_events_stages_history_long_size
    512. performance_schema_events_stages_history_size
    513. performance_schema_events_statements_history_long_size
    514. performance_schema_events_statements_history_size
    515. performance_schema_events_waits_history_long_size
    516. performance_schema_events_waits_history_size
    517. performance_schema_hosts_size
    518. performance_schema_max_cond_classes
    519. performance_schema_max_cond_instances
    520. performance_schema_max_digest_length
    521. performance_schema_max_file_classes
    522. performance_schema_max_file_handles
    523. performance_schema_max_file_instances
    524. performance_schema_max_mutex_classes
    525. performance_schema_max_mutex_instances
    526. performance_schema_max_rwlock_classes
    527. performance_schema_max_rwlock_instances
    528. performance_schema_max_socket_classes
    529. performance_schema_max_socket_instances
    530. performance_schema_max_stage_classes
    531. performance_schema_max_statement_classes
    532. performance_schema_max_table_handles
    533. performance_schema_max_table_instances
    534. performance_schema_max_thread_classes
    535. performance_schema_max_thread_instances
    536. performance_schema_session_connect_attrs_size
    537. performance_schema_setup_actors_size
    538. performance_schema_setup_objects_size
    539. performance_schema_users_size
    540. pid_file
    541. plugin_dir
    542. plugin_maturity
    543. port
    544. preload_buffer_size
    545. profiling
    546. profiling_history_size
    547. progress_report_time
    548. protocol_version
    549. proxy_user
    550. pseudo_slave_mode
    551. pseudo_thread_id
    552. query_alloc_block_size
    553. query_cache_limit
    554. query_cache_min_res_unit
    555. query_cache_size
    556. query_cache_strip_comments
    557. query_cache_type
    558. query_cache_wlock_invalidate
    559. query_prealloc_size
    560. query_response_time_flush
    561. query_response_time_range_base
    562. query_response_time_range_exec_time_debug
    563. query_response_time_stats
    564. rand_seed1
    565. rand_seed2
    566. range_alloc_block_size
    567. read_binlog_speed_limit
    568. read_buffer_size
    569. read_only
    570. read_rnd_buffer_size
    571. relay_log
    572. relay_log_basename
    573. relay_log_index
    574. relay_log_info_file
    575. relay_log_purge
    576. relay_log_recovery
    577. relay_log_space_limit
    578. replicate_annotate_row_events
    579. replicate_do_db
    580. replicate_do_table
    581. replicate_events_marked_for_skip
    582. replicate_ignore_db
    583. replicate_ignore_table
    584. replicate_wild_do_table
    585. replicate_wild_ignore_table
    586. report_host
    587. report_password
    588. report_port
    589. report_user
    590. rowid_merge_buff_size
    591. rpl_recovery_rank
    592. safe_show_database
    593. secure_auth
    594. secure_file_priv
    595. rpl_semi_sync_master_enabled
    596. rpl_semi_sync_master_timeout
    597. rpl_semi_sync_master_trace_level
    598. rpl_semi_sync_master_wait_no_slave
    599. rpl_semi_sync_master_wait_point
    600. rpl_semi_sync_slave_enabled
    601. rpl_semi_sync_slave_trace_level
    602. server_audit_events
    603. server_audit_excl_users
    604. server_audit_file_path
    605. server_audit_file_rotate_now
    606. server_audit_file_rotate_size
    607. server_audit_file_rotations
    608. server_audit_incl_users
    609. server_audit_loc_info
    610. server_audit_logging
    611. server_audit_mode
    612. server_audit_output_type
    613. server_audit_query_limit
    614. server_audit_syslog_facility
    615. server_audit_syslog_ident
    616. server_audit_syslog_info
    617. server_audit_syslog_priority
    618. server_id
    619. session_track_schema
    620. session_track_state_change
    621. session_track_system_variables
    622. session_track_transaction_info
    623. shared_memory
    624. shared_memory_base_name
    625. simple_password_check_digits
    626. simple_password_check_letters_same_case
    627. simple_password_check_minimal_length
    628. simple_password_check_other_characters
    629. skip_external_locking
    630. skip_name_resolve
    631. skip_networking
    632. skip_parallel_replication
    633. skip_replication
    634. skip_show_database
    635. slave_compressed_protocol
    636. slave_ddl_exec_mode
    637. slave_domain_parallel_threads
    638. slave_exec_mode
    639. slave_load_tmpdir
    640. slave_max_allowed_packet
    641. slave_net_timeout
    642. slave_parallel_max_queued
    643. slave_parallel_mode
    644. slave_parallel_threads
    645. slave_run_triggers_for_rbr
    646. slave_skip_errors
    647. slave_sql_verify_checksum
    648. slave_transaction_retries
    649. slave_type_conversions
    650. slow_launch_time
    651. slow_query_log
    652. slow_query_log_file
    653. socket
    654. sort_buffer_size
    655. spider_auto_increment_mode
    656. spider_auto_increment_mode
    657. spider_auto_increment_mode
    658. spider_bgs_second_read
    659. spider_bka_engine
    660. spider_bka_mode
    661. spider_block_size
    662. spider_bulk_access_free
    663. spider_bulk_size
    664. spider_bulk_update_mode
    665. spider_bulk_update_size
    666. spider_casual_read
    667. spider_conn_recycle_mode
    668. spider_conn_recycle_strict
    669. spider_connect_error_interval
    670. spider_connect_mutex
    671. spider_connect_retry_count
    672. spider_connect_retry_interval
    673. spider_connect_timeout
    674. spider_crd_bg_mode
    675. spider_crd_interval
    676. spider_crd_mode
    677. spider_crd_sync
    678. spider_crd_type
    679. spider_crd_weight
    680. spider_delete_all_rows_type
    681. spider_direct_dup_insert
    682. spider_direct_order_limit
    683. spider_dry_access
    684. spider_error_read_mode
    685. spider_error_write_mode
    686. spider_first_read
    687. spider_force_commit
    688. spider_general_log
    689. spider_init_sql_alloc_size
    690. spider_internal_limit
    691. spider_internal_offset
    692. spider_internal_optimize
    693. spider_internal_optimize_local
    694. spider_internal_sql_log_off
    695. spider_internal_unlock
    696. spider_internal_xa
    697. spider_internal_xa_id_type
    698. spider_internal_xa_snapshot
    699. spider_local_lock_table
    700. spider_lock_exchange
    701. spider_log_result_error_with_sql
    702. spider_log_result_errors
    703. spider_low_mem_read
    704. spider_max_order
    705. spider_multi_split_read
    706. spider_net_read_timeout
    707. spider_net_write_timeout
    708. spider_ping_interval_at_trx_start
    709. spider_quick_mode
    710. spider_quick_page_size
    711. spider_read_only_mode
    712. spider_remote_access_charset
    713. spider_remote_autocommit
    714. spider_remote_default_database
    715. spider_remote_sql_log_off
    716. spider_remote_time_zone
    717. spider_remote_trx_isolation
    718. spider_reset_sql_alloc
    719. spider_same_server_link
    720. spider_second_read
    721. spider_select_column_mode
    722. spider_selupd_lock_mode
    723. spider_semi_split_read
    724. spider_semi_split_read_limit
    725. spider_semi_table_lock
    726. spider_semi_table_lock_connection
    727. spider_semi_trx
    728. spider_semi_trx_isolation
    729. spider_skip_default_condition
    730. spider_split_read
    731. spider_sts_bg_mode
    732. spider_sts_interval
    733. spider_sts_mode
    734. spider_sts_sync
    735. spider_support_xa
    736. spider_sync_autocommit
    737. spider_sync_time_zone
    738. spider_sync_trx_isolation
    739. spider_table_init_error_interval
    740. spider_udf_ct_bulk_insert_interval
    741. spider_udf_ct_bulk_insert_rows
    742. spider_udf_ds_bulk_insert_rows
    743. spider_udf_ds_table_loop_mode
    744. spider_udf_ds_use_real_table
    745. spider_udf_table_lock_mutex_count
    746. spider_udf_table_mon_mutex_count
    747. spider_use_all_conns_snapshot
    748. spider_use_consistent_snapshot
    749. spider_use_default_database
    750. spider_use_flash_logs
    751. spider_use_handler
    752. spider_use_pushdown_udf
    753. spider_use_table_charset
    754. spider_version
    755. sql_auto_is_null
    756. sql_big_selects
    757. sql_big_tables
    758. sql_buffer_result
    759. sql_error_log_filename
    760. sql_error_log_rate
    761. sql_error_log_rotate
    762. sql_error_log_rotations
    763. sql_error_log_size_limit
    764. sql_log_bin
    765. sql_log_off
    766. sql_log_update
    767. sql_low_priority_updates
    768. sql_max_join_size
    769. sql_mode
    770. sql_notes
    771. sql_quote_show_create
    772. sql_safe_updates
    773. sql_select_limit
    774. sql_slave_skip_counter
    775. sql_warnings
    776. ssl_ca
    777. ssl_capath
    778. ssl_cert
    779. ssl_cipher
    780. ssl_crl
    781. ssl_crlpath
    782. ssl_key
    783. storage_engine
    784. standard_compliant_cte
    785. stored_program_cache
    786. strict_password_validation
    787. sync_binlog
    788. sync_frm
    789. sync_master_info
    790. sync_relay_log
    791. sync_relay_log_info
    792. system_time_zone
    793. table_definition_cache
    794. table_lock_wait_timeout
    795. table_open_cache
    796. table_open_cache_instances
    797. table_type
    798. thread_cache_size
    799. thread_concurrency
    800. thread_handling
    801. thread_pool_idle_timeout
    802. thread_pool_max_threads
    803. thread_pool_min_threads
    804. thread_pool_oversubscribe
    805. thread_pool_prio_kickup_timer
    806. thread_pool_priority
    807. thread_pool_size
    808. thread_pool_stall_limit
    809. thread_stack
    810. time_format
    811. time_zone
    812. timed_mutexes
    813. timestamp
    814. tmp_table_size
    815. tmpdir
    816. tokudb_alter_print_error
    817. tokudb_analyze_time
    818. tokudb_block_size
    819. tokudb_bulk_fetch
    820. tokudb_cache_size
    821. tokudb_check_jemalloc
    822. tokudb_checkpoint_lock
    823. tokudb_checkpoint_on_flush_logs
    824. tokudb_checkpointing_period
    825. tokudb_cleaner_iterations
    826. tokudb_cleaner_period
    827. tokudb_commit_sync
    828. tokudb_create_index_online
    829. tokudb_data_dir
    830. tokudb_debug
    831. tokudb_directio
    832. tokudb_disable_hot_alter
    833. tokudb_disable_prefetching
    834. tokudb_disable_slow_alter
    835. tokudb_empty_scan
    836. tokudb_fs_reserve_percent
    837. tokudb_fsync_log_period
    838. tokudb_hide_default_row_format
    839. tokudb_killed_time
    840. tokudb_last_lock_timeout
    841. tokudb_load_save_space
    842. tokudb_loader_memory_size
    843. tokudb_lock_timeout
    844. tokudb_lock_timeout_debug
    845. tokudb_log_dir
    846. tokudb_max_lock_memory
    847. tokudb_optimize_index_fraction
    848. tokudb_optimize_index_name
    849. tokudb_optimize_throttle
    850. tokudb_pk_insert_mode
    851. tokudb_prelock_empty
    852. tokudb_read_block_size
    853. tokudb_read_buf_size
    854. tokudb_read_status_frequency
    855. tokudb_row_format
    856. tokudb_rpl_check_readonly
    857. tokudb_rpl_lookup_rows
    858. tokudb_rpl_lookup_rows_delay
    859. tokudb_rpl_unique_checks
    860. tokudb_rpl_unique_checks_delay
    861. tokudb_support_xa
    862. tokudb_tmp_dir
    863. tokudb_version
    864. tokudb_write_status_frequency
    865. transaction_alloc_block_size
    866. transaction_prealloc_size
    867. tx_isolation
    868. tx_read_only
    869. unique_checks
    870. updatable_views_with_limit
    871. use_stat_tables
    872. userstat
    873. version
    874. version_comment
    875. version_compile_machine
    876. version_compile_os
    877. version_malloc_library
    878. version_ssl_library
    879. wait_timeout
    880. warning_count
    881. wsrep_auto_increment_control
    882. wsrep_causal_reads
    883. wsrep_certify_nonPK
    884. wsrep_cluster_address
    885. wsrep_cluster_name
    886. wsrep_convert_LOCK_to_trx
    887. wsrep_data_home_dir
    888. wsrep_dbug_option
    889. wsrep_debug
    890. wsrep_desync
    891. wsrep_dirty_reads
    892. wsrep_drupal_282555_workaround
    893. wsrep_forced_binlog_format
    894. wsrep_gtid_domain_id
    895. wsrep_gtid_mode
    896. wsrep_load_data_splitting
    897. wsrep_log_conflicts
    898. wsrep_max_ws_rows
    899. wsrep_max_ws_size
    900. wsrep_mysql_replication_bundle
    901. wsrep_node_address
    902. wsrep_node_incoming_address
    903. wsrep_node_name
    904. wsrep_notify_cmd
    905. wsrep_on
    906. wsrep_OSU_method
    907. wsrep_provider
    908. wsrep_provider_options
    909. wsrep_recover
    910. wsrep_replicate_myisam
    911. wsrep_restart_slave
    912. wsrep_retry_autocommit
    913. wsrep_slave_fk_checks
    914. wsrep_slave_threads
    915. wsrep_slave_uk_checks
    916. wsrep_sst_auth
    917. wsrep_sst_donor
    918. wsrep_sst_donor_rejects_queries
    919. wsrep_sst_method
    920. wsrep_sst_receive_address
    921. wsrep_start_position
    922. wsrep_sync_wait

About the Server System Variables

MariaDB has many system variables that can be changed to suit your needs.

The full list of server variables are listed in the contents on this page, and most are described on this page, but some are described elsewhere:

See also the Full list of MariaDB options, system and status variables.

Most of these can be set with command line options and many of them can be changed at runtime.

There are a few ways to see the full list of server system variables:

  • While in the mysql client, run:
SHOW VARIABLES;
  • From your shell, run mysqld like so:
mysqld --verbose --help

Setting Server System Variables

There are several ways to set server system variables:

  • Specify them on the command line:
shell> ./mysqld_safe --aria_group_commit="hard"
aria_group_commit = "hard"
  • Set them from the mysql client using the SET command. Only variables that are dynamic can be set at runtime in this way. Note that variables set in this way will not persist after a restart.
SET GLOBAL aria_group_commit="hard";

By convention, server variables have usually been specified with an underscore in the configuration files, and a dash on the command line. You can however specify underscores as dashes - they are interchangeable.

List of Server System Variables

autocommit

  • Description: If set to 1, the default, all queries are committed immediately. If set to 0, they are only committed upon a COMMIT statement, or rolled back with a ROLLBACK statement. If autocommit is set to 0, and then changed to 1, all open transactions are immediately committed.
  • Commandline: --autocommit[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

automatic_sp_privileges

  • Description: When set to 1, the default, when a stored routine is created, the creator is automatically granted permission to ALTER (which includes dropping) and to EXECUTE the routine. If set to 0, the creator is not automatically granted these privileges.
  • Commandline: --automatic-sp-privileges, --skip-automatic-sp-privileges
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

back_log

  • Description: Connections take a small amount of time to start, and this setting determines the number of outstanding connection requests MariaDB can have, or the size of the listen queue for incoming TCP/IP requests. Requests beyond this will be refused. Increase if you expect short bursts of connections. Cannot be set higher than the operating system limit (see the Unix listen() man page).
  • Commandline: --back-log=#
  • Scope: Global
  • Dynamic: No
  • Type: number
  • Default Value: 50 (before MariaDB 10.0.8) or the lower of 150 or the value of max_connections (from MariaDB 10.0.8)

basedir

  • Description: Path to the MariaDB installation directory. Other paths are usually resolved relative to this base directory.
  • Commandline: --basedir=path or -b path
  • Scope: Global
  • Dynamic: No
  • Type: directory name

big_tables

  • Description: Old variable, which if set to 1, allows large result sets by saving all temporary sets to disk, avoiding 'table full' errors. No longer needed, as the server now handles this automatically. sql_big_tables is a synonym.
  • Commandline: --big-tables
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

bulk_insert_buffer_size

  • Description: Size in bytes of the per-thread cache tree used to speed up bulk inserts into MyISAM and Aria tables. A value of 0 disables the cache tree.
  • Commandline: --bulk-insert-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 8388608
  • Range - 32 bit: 0 to 4294967295
  • Range - 64 bit: 0 to 18446744073709547520

character_set_client

  • Description: Determines the character set for queries arriving from the client. It can be set per session by the client, although the server can be configured to ignore client requests with the --skip-character-set-client-handshake option. If the client does not request a character set, or requests a character set that the server does not support, the global value will be used. utf16, utf32 and ucs2 cannot be used as client character sets.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8 (>= MariaDB 5.5)

character_set_connection

  • Description: Character set used for number to string conversion, as well as for literals that don't have a character set introducer.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8 (>= MariaDB 5.5)

character_set_database

  • Description: Character set used by the default database, and set by the server whenever the default database is changed. If there's no default database, character_set_database contains the same value as character_set_server. This variable is dynamic, but should not be set manually, only by the server.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: latin1

character_set_filesystem

  • Description: The character set for the filesystem. Used for converting file names specified as a string literal from character_set_client to character_set_filesystem before opening the file. By default set to binary, so no conversion takes place. This could be useful for statements such as LOAD_FILE() or LOAD DATA INFILE on system where multi-byte file names are use.
  • Commandline: --character-set-filesystem=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: binary

character_set_results

  • Description: Character set used for results and error messages returned to the client.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: utf8

character_set_server

  • Description: Default character set used by the server. See character_set_database for character sets used by the default database. Defaults may be different on some systems, see for example Differences in MariaDB in Debian.
  • Commandline: --character-set-server
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: latin1

character_set_system

  • Description: Character set used by the server to store identifiers, always set to utf8.
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: utf8

character_sets_dir

  • Description: Directory where the character sets are installed.
  • Commandline: --character-sets-dir=path
  • Scope: Global
  • Dynamic: No
  • Type: directory name

check_constraint_checks

  • Description: If set to 0, will disable constraint checks, for example when loading a table that violates some constraints that you plan to fix later.
  • Commandline: --check-constraint-checks=[0|1]
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default: ON
  • Introduced: MariaDB 10.2.1

collation_connection

  • Description: Collation used for the connection character set.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string

collation_database

  • Description: Collation used for the default database. Set by the server if the default database changes, if there is no default database the value from the collation_server variable is used. This variable is dynamic, but should not be set manually, only by the server.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string

collation_server

  • Description: Default collation used by the server. This is set to the default collation for a given character set automatically when character_set_server is changed, but it can also be set manually. Defaults may be different on some systems, see for example Differences in MariaDB in Debian.
  • Commandline: --collation-server=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: latin1_swedish_ci

completion_type

  • Description: The transaction completion type. If set to NO_CHAIN or 0 (the default), there is no effect on commits and rollbacks. If set to CHAIN or 1, a COMMIT statement is equivalent to COMMIT AND CHAIN, while a ROLLBACK is equivalent to ROLLBACK AND CHAIN, so a new transaction starts straight away with the same isolation level as transaction that's just finished. If set to RELEASE or 2, a COMMIT statement is equivalent to COMMIT RELEASE, while a ROLLBACK is equivalent to ROLLBACK RELEASE, so the server will disconnect after the transaction completes. Note that the transaction completion type only applies to explicit commits, not implicit commits.
  • Commandline: --completion-type=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumerated (>=MariaDB/MySQL 5.5.3), numeric (<= MariaDB/MySQL 5.5.2)
  • Default Value: NO_CHAIN (>=MariaDB/MySQL 5.5.3), 0 (<= MariaDB/MySQL 5.5.2)
  • Valid Values: 0, 1, 2 (All versions), NO_CHAIN, CHAIN, RELEASE (>=MariaDB/MySQL 5.5.3)

concurrent_insert

  • Description: If set to AUTO or 1, the default, MariaDB allows concurrent INSERTs and SELECTs for MyISAM tables with no free blocks in the data. If set to NEVER or 0, concurrent inserts are disabled. If set to ALWAYS or 2, concurrent inserts are permitted for all MyISAM tables, even those with holes, in which case new rows are added at the end of a table if the table is being used by another thread. If the --skip-new option is used when starting the server, concurrent_insert is set to NEVER.
  • Commandline: --concurrent-insert[=name]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumerated (>=MariaDB/MySQL 5.5.3), numeric (<= MariaDB/MySQL 5.5.2)
  • Default Value: AUTO (>=MariaDB/MySQL 5.5.3), 1 (<= MariaDB/MySQL 5.5.2)
  • Valid Values: 0, 1, 2 (All versions), AUTO, NEVER, ALWAYS (>=MariaDB/MySQL 5.5.3)
  • Documentation: concurrent-inserts
  • Notes: Changing the variable only affects new opened tables. Use FLUSH TABLES If you want it to affect also cached tables.

connect_timeout

  • Description: Time in seconds that the server waits for a connect packet before returning a 'Bad handshake'. Increasing may help if clients regularly encounter 'Lost connection to MySQL server at 'X', system error: error_number' type-errors
  • Commandline: --connect-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 10

datadir

  • Description: Directory where the data is stored.
  • Commandline: --datadir=path or -h path
  • Scope: Global
  • Dynamic: No
  • Type: directory name

date_format

  • Description: Unused.

datetime_format

  • Description: Unused.

debug

  • Description: Used in debugging to write to a trace file. MariaDB needs to be configured with -SWITH_DEBUG=1 to enable this option.
  • Commandline: --debug[=debug_options]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: d:t:i:o,/tmp/mysqld.trace (Unix) or d:t:i:O,\mysqld.trace (Windows)

debug_no_thread_alarm

  • Description: Disable system thread alarm calls. Disabling it may be useful in debugging or testing, never do it in production.
  • Commandline: --debug-no-thead-alarm=#
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB

debug_sync

  • Description: Used in debugging to show the interface to the Debug Sync facility. MariaDB needs to be configured with -DENABLE_DEBUG_SYNC=1 for this variable to be available.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: OFF or ON - current signal signal name

default_regex_flags

  • Description: Introduced to address remaining incompatibilities between PCRE and the old regex library. Accepts a comma-separated list of zero or more of the following values:
ValuePattern equivalentMeaning
DOTALL(?s). matches anything including NL
DUPNAMES(?J)Allow duplicate names for subpatterns
EXTENDED(?x)Ignore white space and # comments
EXTRA(?X)extra features (e.g. error on unknown escape character)
MULTILINE(?m)^ and $ match newlines within data
UNGREEDY(?U)Invert greediness of quantifiers
  • Commandline: --default-regex-flags=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Default Value: empty
  • Valid Values: DOTALL, DUPNAMES, EXTENDED, EXTRA, MULTILINE, UNGREEDY
  • Introduced: MariaDB 10.0.11

default_storage_engine

  • Description: The default storage engine. The default storage engine must be enabled at server startup or the server won't start.
  • Commandline: --default-storage-engine=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: enumeration
  • Default Value: InnoDB (>= MariaDB 5.5), MyISAM (<MariaDB 5.3)
  • Introduced: MariaDB 5.5 (previously storage_engine)

default_table_type

  • Description: A synonym for default_storage_engine. Removed in MariaDB 5.5.
  • Commandline: --default-table-type=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Removed: MariaDB/MySQL 5.5

default_tmp_storage_engine

  • Description: Default storage engine that will be used for tables created with CREATE TEMPORARY TABLE where no engine is specified. For internal temporary tables see aria_used_for_temp_tables). The storage engine used must be active or the server will not start. See default_storage_engine for the default for non-temporary tables. Defaults to NULL, in which case the value from default_storage_engine is used.
  • Commandline: --default-tmp-storage-engine=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: NULL
  • Introduced: MariaDB 10.1.0

default_week_format

  • Description: Default mode for the WEEK() function. See that page for details on the different modes
  • Commandline: --default-week-format=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 7

delay_key_write

  • Description: Specifies how MyISAM tables handles CREATE TABLE DELAY_KEY_WRITE. If set to ON, the default, any DELAY KEY WRITEs are honored. The key buffer is then flushed only when the table closes, speeding up writes. MyISAM tables should be automatically checked upon startup in this case, and --external locking should not be used, as it can lead to index corruption. If set to OFF, DELAY KEY WRITEs are ignored, while if set to ALL, all new opened tables are treated as if created with DELAY KEY WRITEs enabled.
  • Commandline: --delay-key-write[=name]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: ON
  • Valid Values: ON, OFF, ALL

delayed_insert_limit

  • Description: After this many rows have been inserted with INSERT DELAYED, the handler will check for and execute any waiting SELECT statements.
  • Commandline: --delayed-insert-limit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 1 to 4294967295

delayed_insert_timeout

  • Description: Time in seconds that the INSERT DELAYED handler will wait for INSERT's before terminating.
  • Commandline: --delayed-insert-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 300

delayed_queue_size

  • Description: Number of rows, per table, that can be queued when performing INSERT DELAYED statements. If the queue becomes full, clients attempting to perform INSERT DELAYED's will wait until the queue has room available again.
  • Commandline: --delayed-queue-size=#
  • Scope: Global
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 1000
  • Range: 1 to 4294967295

div_precision_increment

  • Description: Number of digits to be returned after the decimal point in division operations. By default 4, so SELECT 2/15 would return 0.1333. After setting div_precision_increment to 6, for example, the same operation would return 0.133333.
  • Commandline: --div-precision-increment=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4
  • Range: 0 to 30

encrypt_tmp_disk_tables

  • Description: Encrypt tmp disk tables (created as part of query execution). See Table and Tablespace Encryption.
  • Commandline: --encrypt-tmp-disk-tables[={0|1}]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: 10.1.3

encrypt_tmp_files

  • Description: Encrypt temporary files (created for filesort, binary log cache, etc). See Table and Tablespace Encryption.
  • Commandline: --encrypt-tmp-files[={0|1}]
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF (>= 10.1.7), ON (<= 10.1.6)
  • Introduced: 10.1.5

encryption_algorithm

  • Description: Which encryption algorithm to use for table encryption. aes_cbc is the recommended one. See Table and Tablespace Encryption.
  • Commandline: --encryption-algorithm=value
  • Scope: Global
  • Dynamic: No
  • Data Type: enum
  • Default Value: none
  • Valid Values: none, aes_ecb, aes_cbc, aes_ctr
  • Introduced: MariaDB 10.1.3
  • Removed: MariaDB 10.1.4

enforce_storage_engine

  • Description: Force the use of a particular storage engine for new tables. Used to avoid unwanted creation of tables using another engine. For example, setting to InnoDB will prevent any MyISAM tables from being created. If another engine is specified in a CREATE TABLE statement, the outcome depends on whether the NO_ENGINE_SUBSTITUTION SQL_MODE has been set or not. If set (the default from MariaDB 10.1.7), the query will fail, while if not set, a warning will be returned and the table created according to the engine specified by this variable. The variable has a session scope, but is only modifiable by a user with the SUPER privilege.
  • Commandline: None
  • Scope: Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: none
  • Introduced: MariaDB 10.1.4

engine_condition_pushdown

  • Description: Deprecated and to be replaced by optimizer_switch. Specifies whether the engine condition pushdown optimization is enabled.
  • Commandline: --engine-condition-pushdown
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF (from MariaDB 5.5, ON in MySQL 5.5 and earlier versions of MariaDB)
  • Deprecated: MariaDB/MySQL 5.5.3

error_count

  • Description: Read-only variable denoting the number of errors from the most recent statement in the current session that generated errors. See SHOW_ERRORS().
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

event_scheduler

  • Description: Status of the Event Scheduler. Can be set to ON or OFF, while DISABLED means it cannot be set at runtime. Setting the variable will cause a load of events if they were not loaded at startup.
  • Commandline: --event-scheduler[=value]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: OFF
  • Valid Values: ON (or 1), OFF (or 0), DISABLED

expensive_subquery_limit

  • Description: Number of rows to be examined for a query to be considered expensive, that is, maximum number of rows a subquery may examine in order to be executed during optimization and used for constant optimization.
  • Commandline: --expensive-subquery-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 0 upwards
  • Introduced: MariaDB 5.5.25

explicit_defaults_for_timestamp

  • Description: This option causes CREATE TABLE to create all TIMESTAMP columns as NULL with the DEFAULT NULL attribute, Without this option, TIMESTAMP columns are NOT NULL and have implicit DEFAULT clauses. The old behavior is deprecated.
  • Commandline: --explicit-defaults-for-timestamp=[={0|1}]
  • Scope: Global
  • Dynamic: No
  • Data Type: bolean
  • Default Value: OFF
  • Introduced: MariaDB 10.1.8

external_user

  • Description: External user name set by the plugin used to authenticate the client. NULL if native MariaDB authentication is used.
  • Scope: Session
  • Dynamic: No
  • Data Type: string
  • Default Value: NULL
  • Introduced: MariaDB 5.5

flush

  • Description: Usually, MariaDB writes changes to disk after each SQL statement, and the operating system handles synchronizing (flushing) it to disk. If set to ON, the server will synchronize all changes to disk after each statement.
  • Commandline: --flush
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

flush_time

  • Description: Interval in seconds that tables are closed to synchronize (flush) data to disk and free up resources. If set to 0, the default, there is no automatic synchronizing tables and closing of tables. This option should not be necessary on systems with sufficient resources.
  • Commandline: --flush_time=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0

foreign_key_checks

  • Description: If set to 1 (the default) foreign key constraints (including ON UPDATE and ON DELETE behavior) InnoDB tables are checked, while if set to 0, they are not checked. 0 is not recommended for normal use, though it can be useful in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

ft_boolean_syntax

  • Description: List of operators supported by an IN BOOLEAN MODE full-text search. If you wish to change, note that each character must be ASCII and non-alphanumeric, the full string must be 14 characters and the first or second character must be a space. Positions 10, 13 and 14 are reserved for future extensions. Also, no duplicates are permitted except for the phrase quoting characters in positions 11 and 12, which may be the same.
  • Commandline: --ft-boolean-syntax=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: + -><()*:""&|

ft_max_word_len

  • Description: Maximum length for a word to be included in the MyISAM full-text index. If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_max_token_size for the InnoDB equivalent.
  • Commandline: --ft-max-word-len=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 84
  • Minimum Value: 10

ft_min_word_len

  • Description: Minimum length for a word to be included in the MyISAM full-text index. If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_min_token_size for the InnoDB equivalent.
  • Commandline: --ft-min-word-len=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 4
  • Minimum Value: 1

ft_query_expansion_limit

  • Description: For full-text searches, denotes the numer of top matches when using WITH QUERY EXPANSION.
  • Commandline: --ft-query-expansion-limit=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 20
  • Range: 0 to 1000

ft_stopword_file

  • Description: File containing a list of stopwords for use in MyISAM full-text searches. Unless an absolute path is specified the file will be looked for in the data directory. The file is not parsed for comments, so all words found become stopwords. By default, a built-in list of words (built from storage/myisam/ft_static.c file) is used. Stopwords can be disabled by setting this variable to '' (an empty string). If this variable is changed, the full-text index must be rebuilt. The quickest way to do this is by issuing a REPAIR TABLE table_name QUICK statement. See innodb_ft_server_stopword_table for the InnoDB equivalent.
  • Commandline: --ft-stopword-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: (built-in)

general_log

  • Description: If set to 0, the default unless the --general-log option is used, the general query log is disabled, while if set to 1, the general query log is enabled. See log_output for how log files are written. If that variable is set to NONE, no logs will be written even if general_query_log is set to 1.
  • Commandline: --general-log
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

general_log_file

  • Description: Name of the general query log file. If this is not specified the name is taken from the variablelog-basename or from your system hostname with .log as a suffix.
  • Commandline: --general-log-file=file_name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: file name
  • Default Value: host_name.log

group_concat_max_len

  • Description: Maximum length in bytes of the returned result for a GROUP_CONCAT() function.
  • Commandline: --group-concat-max-len=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
  • Range - 32-bit: 4 to 4294967295
  • Range - 64-bit: 4 to 18446744073709547520

have_compress

  • Description: If the zlib compression library is accessible to the server, this will be set to YES, otherwise it will be NO. The COMPRESS() and UNCOMPRESS() functions will only be available if set to YES.
  • Scope: Global
  • Dynamic: No

have_crypt

  • Description: If the crypt() system call is available this variable will be set to YES, otherwise it will be set to NO. If set to NO, the ENCRYPT() function cannot be used.
  • Scope: Global
  • Dynamic: No

have_csv

  • Description: If the server supports CSV tables, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_dynamic_loading

  • Description: If the server supports dynamic loading of plugins, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_geometry

  • Description: If the server supports spatial data types, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_ndbcluster

  • Description: If the server supports NDBCluster (currently disabled in MariaDB).
  • Scope: Global
  • Dynamic: No

have_partitioning

  • Description: If the server supports partitioning, will be set to YES, unless the --skip-partition option is used, in which case will be set to DISABLED. Will be set to NO otherwise. Removed in MariaDB 10.0 - SHOW PLUGINS should instead be used.
  • Scope: Global
  • Dynamic: No
  • Removed: MariaDB 10.0

have_profiling

  • Description: If statement profiling is available, will be set to YES, otherwise will be set to NO. See SHOW PROFILES() and SHOW PROFILE().
  • Scope: Global
  • Dynamic: No

have_query_cache

  • Description: If the server supports the query cache, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

have_rtree_keys

  • Description: If RTREE indexes (used for spatial indexes in MyISAM tables) are available, will be set to YES, otherwise will be set to NO.
  • Scope: Global
  • Dynamic: No

  • Description: If symbolic link support is enabled, will be set to YES, otherwise will be set to NO. Required for the INDEX DIRECTORY and DATA DIRECTORY table options (see CREATE TABLE) and Windows symlink support. Will be set to DISABLED if the server is started with the --skip-symbolic-links option.
  • Scope: Global
  • Dynamic: No

histogram_size

  • Description: Number of bytes used for a histogram. If set to 0, no histograms are created by ANALYZE.
  • Commandline: --histogram-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 255
  • Introduced: MariaDB 10.0.2

histogram_type

  • Description: Specifies the type of histograms created by ANALYZE.
    • SINGLE_PREC_HB - single precision height-balanced.
    • DOUBLE_PREC_HB - double precision height-balanced.
  • Commandline: --histogram-type=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: SINGLE_PREC_HB
  • Valid Values: SINGLE_PREC_HB, DOUBLE_PREC_HB
  • Introduced: MariaDB 10.0.2

host_cache_size

  • Description: Number of host names that will be cached to avoid resolving. Setting to 0 disables the cache. Changing the value while the server is running causes an implicit FLUSH HOSTS, clearing the host cache and truncating the performance_schema.host_cache table.
  • Commandline: --host-cache-size=#.
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 128
  • Range: 0 to 65536
  • Introduced: MariaDB 10.0

hostname

  • Description: When the server starts, this variable is set to the server host name.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

identity


ignore_db_dirs

  • Description: Comma-delimited list of directories in the data directory that are not considered as database directories. Set from --ignore-db-dir at startup.
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Introduced: MariaDB 5.5

in_transaction

  • Description: Session-only and read-only variable that is set to 1 if you are in a transaction, and 0 if not.
  • Commandline: No
  • Scope: Session
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0
  • Introduced: MariaDB 5.3

init_connect

  • Description: String containing one or more SQL statements, separated by semicolons, that will be executed by the server for each client connecting. If there's a syntax error in the one of the statements, the client will fail to connect. For this reason, the statements are not executed for users with the SUPER privilege, who can then still connect and correct the error. See also init_file.
  • Commandline: --init-connect=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string

init_file

  • Description: Name of a file containing SQL statements that will be executed by the server on startup. Each statement should be on a new line. See also init_connect.
  • Commandline: init-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name

insert_id

  • Description: Value to be used for the next statement inserting a new AUTO_INCREMENT value.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

interactive_timeout

  • Description: Time in seconds that the server waits for an interactive connection (one that connects with the mysql_real_connect() CLIENT_INTERACTIVE option) to become active before closing it. See also wait_timeout.
  • Commandline: --interactive-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 28800
  • Minimum Value: 1

join_buffer_size

  • Description: Minimum size in bytes of the buffer used for queries that cannot use an index, and instead perform a full table scan. Increase to get faster full joins when adding indexes is not possible, although be aware of memory issues, since joins will always allocate the minimum size. Best left low globally and set high in sessions that require large full joins. In 64-bit platforms, Windows truncates values above 4GB to 4GB with a warning.
  • Commandline: --join-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 262144 (256kB) (>=MariaDB 10.1.7), 131072 (128kB) (<=MariaDB 10.1.6)
  • Range (>=MariaDB/MySQL 5.5): 128 to 18446744073709547520
  • Range (<=MariaDB/MySQL 5.3, Windows): 8228 to 18446744073709547520

join_buffer_space_limit

  • Description: Maximum size in bytes of the query buffer, By default 1024*128*10. See Block-based join algorithms.
  • Commandline: --join-buffer-space-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2097152
  • Range: 2048 to 99999999997952
  • Introduced: MariaDB 5.3

join_cache_level

  • Description: Controls which of the eight block-based algorithms can be used for join operations. See Block-based join algorithms for more information.
    • 1 – flat (Block Nested Loop) BNL
    • 2 – incremental BNL
    • 3 – flat Block Nested Loop Hash (BNLH)
    • 4 – incremental BNLH
    • 5 – flat Batch Key Access (BKA)
    • 6 – incremental BKA
    • 7 – flat Batch Key Access Hash (BKAH)
    • 8 – incremental BKAH
  • Commandline: --join-cache-level=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2
  • Range: 0 to 8
  • Introduced: MariaDB 5.3

keep_files_on_create

  • Description: If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is stored in the database directory. When set to 0, the default, if MariaDB finds another .MYD file in the database directory it will overwrite it. Setting this variable to 1 means that MariaDB will return an error instead, just as it usually does in the same situation outside of the database directory. The same applies for .MYI files and no INDEX DIRECTORY option.
  • Commandline: --keep-files-on-create=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

language

  • Description: Language to use for the server error messages. Either just the language name, or the directory where the error messages are stored. An alternative is to use lc_messages_dir and lc_messages instead. See Setting the language for error messages.
  • Commandline: --language=name, -L
  • Scope: Global
  • Dynamic: No
  • Data Type: directory name
  • Default Value: /usr/local/mysql/share/mysql/english/

large_files_support

  • Description: ON if the server if was compiled with large file support or not, else OFF
  • Scope: Global
  • Dynamic: No
  • Introduced: MySQL 3.23.28

large_page_size

  • Description: Indicates the size of memory page if large page support (Linux only) is enabled. The page size is determined from the Hugepagesize setting in /proc/meminfo. See large_pages.
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: Autosized (see description)

large_pages

  • Description: Indicates whether large page support (Linux only - called huge pages) is used. This is set with --large-pages or disabled with --skip-large-pages. Large pages are used for the innodb buffer pool and for online DDL (of size 3* innodb_sort_buffer_size (or 6 when encryption is used)). To use large pages, the Linux sysctl variable vm.nr_hugepages multipled by large-page) must be larger than the usage. The ulimit for locked memory must be sufficient to cover the amount used (ulimit -l and equalivent in /etc/security/limits.conf / or in systemd LimitMEMLOCK). If these operating system controls or insufficient free huge pages are available, the allocation of large pages will fall back to conventional memory allocation and a warning will appear in the logs.
  • Commandline: --large-pages, --skip-large-pages
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

last_insert_id

  • Description: Contains the same value as that returned by LAST_INSERT_ID(). Note that setting this variable doen't update the value returned by the underlying function.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

lc_messages

  • Description: Locale to use for error messages. This will be converted to a language name and along with lc_messages_dir will produce the location for the error message file. See locales for a list of available locales and their related languages.
  • Commandline: --lc-messages=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: en_us
  • Introduced: MariaDB 5.5

lc_messages_dir

  • Description: Directory where the error messages are located. Together with lc_messages will produce the location for the error message file.
  • Commandline: --lc-messages-dir=path
  • Scope: Global
  • Dynamic: No
  • Data Type: directory name
  • Introduced: MariaDB 5.5

lc_time_names

  • Description: The locale that determines the language used for the date and time functions DAYNAME(), MONTHNAME() and DATE_FORMAT(). Locale names are language and region subtags, for example 'en_ZA' (English - South Africa) or 'es_US: Spanish - United States'. The default is always 'en-US' regardless of the system's locale setting. See server locale for a full list of supported locales.
  • Commandline: --lc-time-names=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: en_US
  • Introduced: MySQL 5.1.12

license

  • Description: Server license, for example GPL.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

local_infile

  • Description: If set to 1, LOCAL is supported for LOAD DATA INFILE statements. If set to 0, usually for security reasons, attempts to perform a LOAD DATA LOCAL will fail with an error message.
  • Commandline: --local-infile=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON

lock_wait_timeout

  • Description: Timeout in seconds for attempts to acquire metadata locks. Statements using metadata locks include FLUSH TABLES WITH READ LOCK, LOCK TABLES, HANDLER and DML and DDL operations on tables, stored procedures and functions, and views. The timeout is separate for each attempt, of which there may be multiple in a single statement.
  • Commandline: --lock-wait-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
  • Range: 1 to 31536000
  • Introduced: MariaDB/MySQL 5.5.3

locked_in_memory

  • Description: Indicates whether --memlock was used to lock mysqld in memory.
  • Commandline: --memlock
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

log

  • Description: Deprecated and removed in MariaDB 10.0, use general_log instead.
  • Commandline: -l [filename] or --log[=filename]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: string
  • Default Value: OFF
  • Deprecated: MySQL 5.1.29 and replaced by general_log
  • Removed: MariaDB 10.0

log_error

  • Description: Specifies the name of the error log. If no name is provided, errors will still be logged to hostname.err.
  • Commandline: --log-error[=name]
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: (empty string)

log_output

  • Description: How the output for the general query log and the slow query log is stored. By default written to file (FILE), it can also be stored in the general_log and slow_log tables in the mysql database (TABLE), or not stored at all (NONE). More than one option can be chosen at the same time, with NONE taking precedence if present. Logs will not be written if logging is not enabled. See Writing logs into tables, and the slow_query_log and general_log server system variables.
  • Commandline: --log-output=name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: set
  • Default Value: FILE
  • Valid Values: TABLE, FILE or NONE

log_queries_not_using_indexes

  • Description: Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log (regardless of time taken). The slow query log needs to be enabled for this to have an effect.
  • Commandline: --log-queries-not-using-indexes
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

log_slow_admin_statements


log_slow_filter

  • Description: Comma-delimited string containing one or more settings for filtering what is logged to the slow query log. If a query matches one of the types listed in the filter, and takes longer than long_query_time, it will be logged. Sets log-slow-admin-statements to ON.
    • admin log administrative queries (create, optimize, drop etc...)
    • filesort logs queries that use a filesort.
    • filesort_on_disk logs queries that perform a a filesort on disk.
    • full_join logs queries that perform a join without indexes.
    • full_scan logs queries that perform full table scans.
    • query_cache log queries that are resolved by the query cache .
    • query_cache_miss logs queries that are not found in the query cache.
    • tmp_table logs queries that create an implicit temporary table.
    • tmp_table_on_disk logs queries that create a temporary table on disk.
  • Commandline: log-slow-filter=value1[,value2...]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk
  • Valid Values: admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk

log_slow_queries

  • Description: Deprecated and removed in MariaDB 10.0, use slow_query_log instead.
  • Commandline: --log-slow-queries[=name]
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Deprecated: MySQL 5.1.29
  • Removed: MariaDB 10.0/MySQL 5.6.1

log_slow_rate_limit

  • Description: The slow query log will log every this many queries. The default is 1, or every query, while setting it to 20 would log every 20 queries, or five percent. Aims to reduce I/O usage and excessively large slow query logs. See also Slow Query Log Extended Statistics.
  • Commandline: log-slow-rate-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1
  • Range: 1 upwards
  • Introduced: MariaDB 5.1

log_slow_slave_statements

  • Description: Log slow statements executed by slave thread to the slow log if it is open. Before MariaDB 10.1.13, this was only available as a mysqld option, not a server variable.
  • Commandline: --log-slow-slave-statements
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value:
  • Introduced: MariaDB 10.1.13 (variable)

log_slow_verbosity


log_tc_size

  • Description: Size in bytes of the transaction coordinator log, defined in multiples of 4096. Always available as a commandline option, but added as a variable in MariaDB 10.1.3
  • Commandline: log-tc-size=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 24576
  • Range: 12288 to 18446744073709551615
  • Introduced: MariaDB 10.1.3 (variable)

log_warnings

  • Description: If set to 1, the default, all critical warnings are logged to the error log. Slave servers also log status information, including the binary log and relay log co-ordinates where they start replicating from, reconnects after a disconnect and when the slave switches to a new relay-log. If set to 0, warnings are not logged. If set to a number greater than 1, aborted connections and access-denied errors are also logged. Specifically, the following are logged:
    • log_warnings >= 1
      • Events_scheduler info and warnings
      • Access denied errors
      • Connections that were forced to be closed or aborted
      • System signals
      • Wrong usage of --user
      • Failed setrlimit() and mlockall()
      • Changed limits
      • Wrong values of lower_case_table_names and stack_size
      • Wrong values for command line options
      • Start log position and some master information when starting slaves
      • Slave reconnects
      • Killed slaves
      • Error reading relay logs
      • Before MariaDB 10.0.14: Statements that were unsafe to log as statement-based (when BINLOG_FLAG_UNSAFE_STMT_PRINTED is also set)
      • Disabled plugins that one tried to enable or use
      • UDF files that didn't include the required init functions.
    • log_warnings >= 2
      • Table handler errors
      • Starting from MariaDB 10.0.14: Statements that were unsafe to log as statement-based (when BINLOG_FLAG_UNSAFE_STMT_PRINTED is also set)
    • log_warnings >= 3
      • All errors and warnings during MyISAM repair and auto recover.
  • Commandline: -W [level] or --log-warnings[=level]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
  • Range: 0 to 4294967295

long_query_time

  • Description: If a query takes longer than this many seconds to execute (microseconds can be specified too), the Slow_queries status variable is incremented and, if enabled, the query is logged to the slow query log.
  • Commandline: --long-query-time=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10.000000 >= MariaDB 10.1.13, 10 <= MariaDB 10.1.12
  • Range: 0 upwards

low_priority_updates

  • Description: If set to 1 (0 is the default), for storage engines that use only table-level locking (Aria, MyISAM, MEMORY and MERGE), all INSERTs, UPDATEs, DELETEs and LOCK TABLE WRITEs will wait until there are no more SELECTs or LOCK TABLE READs pending on the relevant tables. Set this to 1 if reads are prioritized over writes. Previously named sql_low_priority_updates, which is still a synonym.
  • Commandline: --low-priority-updates
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

lower_case_file_system

  • Description: Read-only variable describing whether the file system is case-sensitive. If set to OFF, file names are case-sensitive. If set to ON, they are not case-sensitive.
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: ##

lower_case_table_names

  • Description: If set to 0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.
  • Commandline: --lower-case-table-names[=#]
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 0 (Unix), 1 (Windows), 2 (Mac OS X)
  • Range: 0 to 2

max_allowed_packet

  • Description: Maximum size in bytes of a packet or a generated/intermediate string. The packet message buffer is initialized with the value from net_buffer_length, but can grow up to max_allowed_packet bytes. Set as large as the largest BLOB, in multiples of 1024. If this value is changed, it should be changed on the client side as well. See slave_max_allowed_packet for a specific limit for replication purposes.
  • Commandline: --max-allowed-packet=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value:
  • Range: 1024 to 1073741824

max_connect_errors

  • Description: Limit to the number of successive failed connects from a host before the host is blocked from making further connections. The count for a host is reset to zero if they successfully connect. To unblock, flush the host cache with a FLUSH HOSTS statement or mysqladmin flush-hosts.
  • Commandline: --max-connect-errors=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100 (from MariaDB 10.0), 10 (before MariaDB 10.0)

max_connections

  • Description: The maximum number of simultaneous client connections. See also Handling Too Many Connections. Note that this value affects the number of file descriptors required on the operating system.
  • Commandline: --max-connections=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 151
  • Range: 1 to 100000

max_delayed_threads

  • Description: Limits to the number of INSERT DELAYED threads. Once this limit is reached, the insert is handled as if there was no DELAYED attribute. If set to 0, DELAYED is ignored entirely. The session value can only be set to 0 or to the same as the global value.
  • Commandline: --max-delayed-threads=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 20
  • Range: 0 to 16384

max_digest_length

  • Description: Maximum length considered for computing a statement digest, such as used by the Performance Schema and query rewrite plugins. Statements that differ after this many bytes produce the same digest, and are aggregated for statistics purposes. The variable is allocated per session. Increasing will allow longer statements to be distinguished from each other, but increase memory use, while decreasing will reduce memory use, but more statements may become indistinguishable.
  • Commandline: --max-digest-length=#
  • Scope: Global,
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1024
  • Range: 0 to 1048576
  • Introduced: MariaDB 10.1.5

max_error_count

  • Description: Specifies the maximum number of messages stored for display by SHOW ERRORS and SHOW WARNINGS statements.
  • Commandline: --max-error-count=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 64
  • Range: 0 to 65535

max_heap_table_size

  • Description: Maximum size in bytes for user-created MEMORY tables. Setting the variable while the server is active has no effect on existing tables unless they are recreated or altered. The smaller of max_heap_table_size and tmp_table_size also limits internal in-memory tables. When the maximum size is reached, any further attempts to insert data will receive a "table ... is full" error. Temporary tables created with CREATE TEMPORARY will not be converted to Aria, as occurs with internal temporary tables, but will also receive a table full error.
  • Commandline: --max-heap-table-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16777216
  • Range : 16384 to 4294966272

max_insert_delayed_threads


max_join_size

  • Description: Statements will not be performed if they are likely to need to examine more than this number of rows, row combinations or do more disk seeks. Can prevent poorly-formatted queries from taking server resources. Changing this value to anything other the default will reset sql_big_selects to 0. If sql_big_selects is set again, max_join_size will be ignored. This limit is also ignored if the query result is sitting in the query cache. Previously named sql_max_join_size, which is still a synonym.
  • Commandline: --max-join-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 4294967295 (< MariaDB/MySQL 5.5.0)
  • Range: 1 to 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 1 to 4294967295 (< MariaDB/MySQL 5.5.0)

max_length_for_sort_data

  • Description: Used to decide which algorithm to choose when sorting rows. If the total size of the column data, not including columns that are part of the sort, is less than max_length_for_sort_data, then we add these to the sort key. This can speed up the sort as we don't have to re-read the same row again later. Setting the value too high can slow things down as there will be a higher disk activity for doing the sort.
  • Commandline: --max-length-for-sort-data=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1024
  • Range: 4 to 8388608

max_long_data_size

  • Description: Maximum size for parameter values sent with mysql_stmt_send_long_data(). If not set, will default to the value of max_allowed_packet. Deprecated, use max_allowed_packet instead.
  • Commandline: --max-long-data-size=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 1048576
  • Range: 1024 to 4294967295
  • Introduced: MariaDB 5.5
  • Deprecated: MariaDB 5.5

max_prepared_stmt_count

  • Description: Maximum number of prepared statements on the server. Can help prevent certain forms of denial-of-service attacks. If set to 0, no prepared statements are permitted on the server.
  • Commandline: --max-prepared-stmt-count=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16382
  • Range: 0 to 1048576

max_recursive_iterations

  • Description: Maximum number of iterations when executing recursive queries.
  • Commandline: --max-recursive-iterations=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4294967295
  • Range: 0 to 4294967295
  • Introduced: MariaDB 10.2.2

max_seeks_for_key

  • Description: The optimizer assumes that the number specified here is the most key seeks required when searching with an index, regardless of the actual index cardinality. If this value is set lower than its default and maximum, indexes will tend to be preferred over table scans.
  • Commandline: --max-seeks-for-key=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4294967295
  • Range: 1 to 4294967295

max_session_mem_used

  • Description: Amount of memory a single user session is allowed to allocate. This limits the value of the session variable MEM_USED.
  • Commandline: --max-session-mem-used=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 9223372036854775807
  • Range: 8192 to 18446744073709551615
  • Introduced: MariaDB 10.1.21

max_sort_length

  • Description: Maximum size in bytes used for sorting data values - anything exceeding this is ignored.
  • Commandline: --max-sort-length=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1024
  • Range: 4 to 8388608

max_sp_recursion_depth

  • Description: Permitted number of recursive calls for a stored procedure. 0, the default, no recursion is permitted. Increasing this value increases the thread stack requirements, so you may need to increase thread_stack as well. This limit doesn't apply to stored functions.
  • Commandline: --max-sp-recursion-depth[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0 to 255

max_statement_time


max_tmp_tables

  • Description: Unused.

max_user_connections

  • Description: Maximum simultaneous connections permitted for a user account. If set to 0, the default, there is no per-user limit. The session value is read-only and takes the global value unless there's a nonzero MAX_USER_CONNECTIONS value for the user. Previously having a minimum value of 0, from MariaDB 5.3, max_user_connections can be set to -1 to stop users from connecting to the server. The global max_user_connections variable does not affect users with the SUPER privilege.
  • Commandline: --max-user-connections=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: -1 to 4294967295

max_write_lock_count

  • Description: Read lock requests will be permitted for processing after this many write locks. Applies only to storage engines that use table level locks (thr_lock), so no effect with InnoDB/XtraDB or [archive|Archive]].
  • Commandline: --max-write-lock-count=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 4294967295
  • Range: 0-4294967295

metadata_locks_cache_size

  • Description: Size of the metadata locks cache, used for reducing the need to create and destroy synchronization objects. It is particularly helpful on systems where this process is inefficient, such as Windows XP.
  • Commandline: --metadata-locks-cache-size=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 1024
  • Range: 1 to 1048576
  • Introduced: MariaDB 5.5

metadata_locks_hash_instances

  • Description: Number of hashes used by the set of metadata locks. The metadata locks are partitioned into separate hashes in order to reduce contention.
  • Commandline: --metadata-locks-hash-instances=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 8
  • Range: 1 to 1024
  • Introduced: MariaDB 10.0

min_examined_row_limit

  • Description: If a query examines more than this number of rows, it is logged to the slow-query-log. If set to 0, the default, no row limit is used.
  • Commandline: --min-examined-row-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0
  • Range: 0-4294967295

mrr_buffer_size

  • Description: Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization for more information.
  • Commandline: --mrr-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 262144
  • Range 8192 to 2147483648
  • Introduced: MariaDB 5.3

multi_range_count


mysql56_temporal_format

  • Description: If set, MariaDB uses the MySQL-5.6 low level formats for TIME, DATETIME and TIMESTAMP instead of the MariaDB 5.3+ version.
  • Commandline: --mysql56-temporal-format
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: ON
  • Introduced: MariaDB 10.1.2

named_pipe

  • Description: On Windows systems, determines whether connections over named pipes are permitted.
  • Commandline: --enable-named-pipe
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF

net_buffer_length

  • Description: The starting size, in bytes, for the connection and thread buffers for each client thread. The size can grow to max_allowed_packet. This variable's session value is read-only. Can be set to the expected length of client statements if memory is a limitation.
  • Commandline: --net-buffer-length=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16384
  • Range: 1024 to 1048576

net_read_timeout

  • Description: Time in seconds the server will wait for a client connection to send more data before aborting the read. See also net_write_timeout and slave_net_timeout
  • Commandline: --net-read-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 30
  • Range: 1 upwards

net_retry_count

  • Description: Permit this many retries before aborting when attempting to read or write on a communication port. On FreeBSD systems should be set higher as threads are sent internal interrupts..
  • Commandline: --net-retry-count=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 10
  • Range: 1 to 4294967295

net_write_timeout

  • Description: Time in seconds to wait on writing a block to a connection before aborting the write. See also net_read_timeout and slave_net_timeout.
  • Commandline: --net-write-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 60
  • Range: 1 upwards

old

  • Description: Disabled by default, enabling it reverts index hints to those used before MySQL 5.1.17. Enabling may lead to replication errors. Being replaced by old_mode. Before MariaDB 5.5, the variable was read-only and global in scope only.
  • Commandline: --old
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

old_alter_table

  • Description: If set to 1 (0 is default), MariaDB reverts to the non-optimized, pre-MySQL 5.0, method of processing ALTER TABLE statements.
  • Commandline: --old-alter-table
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

old_mode

  • Description: Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode. Will be used to replace the old variable over time.
  • Commandline: --old-mode
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: (empty string)
  • Introduced: MariaDB 5.5.35
  • Valid Values: See OLD Mode for the full list.

old_passwords

  • Description: If set to 1 (0 is default), MariaDB reverts to the pre-MySQL 4.1 form of password hashing.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

open_files_limit

  • Description: The number of file descriptors available to mysqld. Increase if you are getting the Too many open files error. If set to 0, then mysqld will reserve max_connections*5 or max_connections + table_open_cache*2 (whichever is larger) number of files. Note that MariaDB cannot set this to exceed the hard limit imposed by the operating system, and you may need to change this. For example, by adding the following lines to /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535

rebooting, and then create /etc/systemd/system/mysqld.service.d/filelimit.conf or /etc/systemd/system/mariadb.service.d/filelimit.conf (corresponding to the service name).

[service]
LimitNOFILE=infinity
  • Commandline: --open-files-limit=count
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: Autosized (see description)
  • Range: 0 to 4294967295

optimizer_prune_level

  • Description: If set to 1, the default, the optimizer will use heuristics to prune less-promising partial plans from the optimizer search space. If set to 0, heuristics are disabled and an exhaustive search is performed.
  • Commandline: --optimizer-prune-level[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

optimizer_search_depth

  • Description: Maximum search depth by the query optimizer. Smaller values lead to less time spent on execution plans, but potentially less optimal results. If set to 0, MariaDB will automatically choose a reasonable value. Since the better results from more optimal planning usually offset the longer time spent on planning, this is set as high as possible by default.
  • Commandline: --optimizer-search-depth[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 62
  • Range: 0 to 62

optimizer_selectivity_sampling_limit

  • Description: Controls number of record samples to check condition selectivity
  • Commandline: optimizer-selectivity-sampling-limit[=#]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 100
  • Range: 10 upwards
  • Introduced: MariaDB 10.0

optimizer_switch


optimizer_use_condition_selectivity

  • Description: Controls which statistics can be used by the optimizer when looking for the best query execution plan.
    • 1 Use selectivity of predicates as in MariaDB 5.5.
    • 2 Use selectivity of all range predicates supported by indexes.
    • 3 Use selectivity of all range predicates estimated without histogram.
    • 4 Use selectivity of all range predicates estimated with histogram.
    • 5 Additionally use selectivity of certain non-range predicates calculated on record sample.
  • Commandline: --optimizer-use-condition-selectivity=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1
  • Range: 1 to 5
  • Introduced: MariaDB 10.0

pam_use_cleartext_plugin

  • Description: Use mysql_cleartext_plugin on the client side instead of the dialog plugin. This may be needed for compatibility reasons, but it only supports simple PAM policies that don't require anything besides a password. See PAM Authentication Plugin.
  • Commandline: <<code>>--pam-use-cleartext-plugin</code>>
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.1.3 (default)

pid_file

  • Description: Full path of the process ID file.
  • Commandline: --pid-file=file_name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name

plugin_dir

  • Description: Path to the plugin directory. For security reasons, either make sure this directory can only be read by the server, or set secure_file_priv.
  • Commandline: --plugin-dir=path
  • Scope: Global
  • Dynamic: No
  • Data Type: directory name
  • Default Value: BASEDIR/lib/plugin
  • Introduced: MariaDB 5.5

plugin_maturity

  • Description: The lowest acceptable plugin maturity. MariaDB will not load plugins less mature than that.
  • Commandline: --plugin-maturity=level
  • Scope: Global
  • Dynamic: No
  • Type: enum
  • Default Value: unknown
  • Valid Values: unknown, experimental, alpha, beta, gamma, stable
  • Introduced: MariaDB 5.2.1

port

  • Description: Port to listen for TCP/IP connections. If set to 0, will default to, in order of preference, my.cnf, the MYSQL_TCP_PORT environment variable, /etc/services, built-in default (3306).
  • Commandline: --port=#, -P
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 3306
  • Range: 0 to 65535

preload_buffer_size

  • Description: Size in bytes of the buffer allocated when indexes are preloaded.
  • Commandline: --preload-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 32768
  • Range: 1024 to 1073741824

profiling

  • Description: If set to 1 (0 is default), statement profiling will be enabled. See SHOW PROFILES() and SHOW PROFILE().
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

profiling_history_size

  • Description: Number of statements about which profiling information is maintained. If set to 0, no profiles are stored. See SHOW PROFILES.
  • Commandline: --profiling-history-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 15
  • Range: 0 to 100

progress_report_time


protocol_version

  • Description: Client/server protocol version number.
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Range: to

proxy_user

  • Description: Set to the proxy user account name if the current client is a proxy, else NULL.
  • Scope: Session
  • Dynamic: No
  • Data Type: string
  • Introduced: MariaDB 5.5.20

pseudo_slave_mode

  • Description: For internal use by the server.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: OFF
  • Introduced: MariaDB/MySQL 5.5.30

pseudo_thread_id

  • Description: For internal use only.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: numeric

query_alloc_block_size

  • Description: Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).
  • Commandline: --query-alloc-block-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16384 (from MariaDB 10.1.2), 8192 (before MariaDB 10.1.2)
  • Range: 1024 to 4294967295

query_cache_limit

  • Description: Size in bytes for which results larger than this are not stored in the query cache.
  • Commandline: --query-cache-limit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1048576 (1MB)
  • Range: 0 to 4294967295

query_cache_min_res_unit

  • Description: Minimum size in bytes of the blocks allocated for query cache results.
  • Commandline: --query-cache-min-res-unit=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4096 (4KB)
  • Range: 0 to 4294967295

query_cache_size

  • Description: Size in bytes available to the query cache. About 40KB is needed for query cache structures, so setting a size lower than this will result in a warning. 0, the default before MariaDB 10.1.7, effectively disables the query cache. Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value.
  • Commandline: --query-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 1M (>= MariaDB 10.1.7), 0 (<= MariaDB 10.1.6) (although frequently given a default value in some setups)
  • Valid Values: 0 upwards in units of 1024.

query_cache_strip_comments

  • Description: If set to 1 (0 is default), the server will strip any comments from the query before searching to see if it exists in the query cache. Multiple space, line feeds, tab and other white space characters will also be removed.
  • Commandline: query-cache-strip-comments
  • Scope: Session (from MariaDB 5.5.20), Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

query_cache_type

  • Description: If set to 0, the query cache is disabled (although a buffer of query_cache_size bytes is still allocated). If set to 1 all SELECT queries will be cached unless SQL_NO_CACHE is specified. If set to 2 (or DEMAND), only queries with the SQL CACHE clause will be cached. Note that if the server is started with the query cache disabled, it cannot be enabled at runtime. Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value.
  • Commandline: --query-cache-type=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enumeration
  • Default Value: OFF (>= MariaDB 10.1.7), ON (<= MariaDB 10.1.6)
  • Valid Values: 0 or OFF, 1 or ON, 2 or DEMAND

query_cache_wlock_invalidate

  • Description: If set to 0, the default, results present in the query cache will be returned even if there's a write lock on the table. If set to 1, the client will first have to wait for the lock to be released.
  • Commandline: --query-cache-wlock-invalidate
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

query_prealloc_size

  • Description: Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect. Increasing may be useful if complex queries are being run, as this will reduce the need for more memory allocations during query operation. See also query_alloc_block_size.
  • Commandline: --query-prealloc-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 24576 (from MariaDB 10.1.2) 8192 (before MariaDB 10.1.2)
  • Range: 1024 to 4294967295 (from MariaDB 10.1.2), 8192 to 4294967295 (before MariaDB 10.1.2)

rand_seed1

  • Description: rand_seed1 and rand_seed2 facilitate replication of the RAND() function. The master passes the value of these to the slaves so that the random number generator is seeded in the same way, and generates the same value, on the slave as on the master. The variable value cannot be viewed.

rand_seed2


range_alloc_block_size

  • Description: Size in bytes of blocks allocated during range optimization. The unit size in 1024.
  • Commandline: --range-alloc-block-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 4096
  • Range - 32 bit: 4096 to 4294967295

read_buffer_size

  • Description: Each thread performing a sequential scan (for MyISAM, Aria and MERGE tables) allocates a buffer of this size in bytes for each table scanned. Increase if you perform many sequential scans. If not in a multiple of 4KB, will be rounded down to the nearest multiple. Also used in ORDER BY's for caching indexes in a temporary file (not temporary table), for caching results of nested queries, for bulk inserts into partitions, and to determine the memory block size of MEMORY tables.
  • Commandline: --read-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 131072
  • Range: 8200 to 2147479552

read_only

  • Description: When set to 1 (0 is default), no updates are permitted except from users with the SUPER privilege or slave servers updating from a master. The read_only variable is useful for slave servers to ensure no updates are accidentally made outside of what are performed on the master. Inserting rows to log tables, updates to temporary tables and OPTIMIZE or ANALYZE TABLE statements are excluded from this limitation. From MariaDB 5.5, if read_only is set to 1, SET PASSWORD is limited only to users with the SUPER privilege. Attempting to set this variable to 1 will fail if the current session has table locks or transactions pending, while if other sessions hold table locks, the statement will wait until these locks are released before completing. While the attempt to set read_only is waiting, other requests for table locks or transactions will also wait until read_only has been set.
  • Commandline: --read-only
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF

read_rnd_buffer_size

  • Description: Size in bytes of the buffer used when reading rows from a MyISAM table in sorted order after a key sort. Larger values improve ORDER BY performance, although rather increase the size by SESSION where the need arises to avoid excessive memory use.
  • Commandline: --read-rnd-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 262144
  • Range: 8200 to 2147483647

-

-

rowid_merge_buff_size

  • Description: The maximum size in bytes of the memory available to the Rowid-merge strategy. See Non-semi-join subquery optimizations for more information.
  • Commandline: --rowid-merge-buff-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 8388608
  • Range: 0 to 2147483647
  • Introduced: MariaDB 5.3.0

rpl_recovery_rank


safe_show_database

  • Description: This variable was removed in MariaDB/MySQL 5.5.3, and has been replaced by the more flexible SHOW DATABASES privilege.
  • Commandline: --safe-show-database (until MySQL 4.1.1)
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Removed: MariaDB/MySQL 5.5.3

secure_auth

  • Description: Connections will be blocked unless clients use the current password format. Prior to MySQL 4.1 passwords used an old, less secure format.. For the mysql client, the --secure-auth option blocks connections to servers requiring the old format. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format.
  • Commandline: --secure-auth
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON (>= MariaDB 10.1.7), OFF (<= MariaDB 10.1.6)

secure_file_priv

  • Description: LOAD DATA, SELECT ... INTO and LOAD FILE() will only work with files in the specified path. If not set, the default, the statements will work with any files that can be accessed.
  • Commandline: --secure-file-priv=path
  • Scope: Global
  • Dynamic: No
  • Data Type: path name
  • Default Value: None

session_track_schema

  • Description: Whether to track changes to the default schema within the current session.
  • Commandline: --session-track-schema={0|1}
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON
  • Introduced: MariaDB 10.2.2

session_track_state_change

  • Description: Whether to track changes to the session state.
  • Commandline: --session-track-state-change={0|1}
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 10.2.2

session_track_system_variables

  • Description: Comma-separated list of session system variables for which to track changes. By default no variables are tracked. For compatibility with MySQL defaults this variable should be set to "autocommit, character_set_client, character_set_connection, character_set_results, time_zone". The * character tracks all session variables.
  • Commandline: --session-track-system-variables=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: (empty string)
  • Introduced: MariaDB 10.2.2

session_track_transaction_info

  • Description: Track changes to the transaction attributes. OFF to disable; STATE to track just transaction state (Is there an active transaction? Does it have any data? etc.); CHARACTERISTICS to track transaction state and report all statements needed to start a transaction with the same characteristics (isolation level, read only/read write,snapshot - but not any work done / data modified within the transaction).
  • Commandline: --session-track-transaction-info=value
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: OFF
  • Valid Values: OFF, STATE, CHARACTERISTICS
  • Introduced: MariaDB 10.2.2

shared_memory

  • Description: Windows only, determines whether the server permits shared memory connections. See also shared_memory_base_name.
  • Scope: Global
  • Dynamic: No

shared_memory_base_name

  • Description: Windows only, specifies the name of the shared memory to use for shared memory connection. Mainly used when running more than one instance on the same physical machine. By default the name is MYSQL and is case sensitive. See also shared_memory.
  • Scope: Global
  • Dynamic: No
  • Data Type: string
  • Default Value: MYSQL

skip_external_locking

  • Description: If set, external locking for MyISAM tables is disabled.
  • Commandline: --skip-external-locking
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 1

skip_name_resolve

  • Description: If set to 1 (0 is the default), only IP addresses are used for connections. Host names are not resolved. All host values in the GRANT tables must be IP addresses (or localhost).
  • Commandline: --skip-name-resolve
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

skip_networking

  • Description: If set to 1, (0 is the default), the server does not listen for TCP/IP connections. All interaction with the server by be through socket files (Unix) or named pipes or shared memory (Windows). It's recommended to use this option if only local clients are permitted to connect to the server. Enabling this option also prevents a server from functioning as a replication client.
  • Commandline: --skip-networking
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

skip_show_database

  • Description: If set to 1, (0 is the default), only users with the SHOW DATABASES privilege can use the SHOW DATABASES statement to see all database names.
  • Commandline: --skip-show-database
  • Scope: Global
  • Dynamic: No
  • Data Type: boolean
  • Default Value: 0

slow_launch_time

  • Description: Time in seconds. If a thread takes longer than this to launch, the slow_launch_threads server status variable is incremented.
  • Commandline: --slow-launch-time=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2

slow_query_log

  • Description: If set to 0, the default unless the --slow-query-log option is used, the slow query log is disabled, while if set to 1 (both global and session variables), the slow query log is enabled. MariaDB 10.1 added support for session variables.
  • Commandline: --slow-query-log
  • Scope: Global, Session (MariaDB 10.1)
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0
  • See also: See log_output to see how log files are written. If that variable is set to NONE, no logs will be written even if slow_query_log is set to 1.

slow_query_log_file

  • Description: Name of the slow query log file.
  • Commandline: --slow-query-log-file=file_name
  • Scope: Global
  • Dynamic: Yes
  • Data Type: file name
  • Default Value: host_name-slow.log

socket

  • Description: On Unix-like systems, this is the name of the socket file used for local client connections, by default /tmp/mysql.sock, often changed by the distribution, for example /var/lib/mysql/mysql.sock. On Windows, this is the name of the named pipe used for local client connections, by default MySQL. On Windows, this is not case-sensitive.
  • Commandline: --socket=name
  • Scope: Global
  • Dynamic: No
  • Data Type: file name
  • Default Value: /tmp/mysql.sock (Unix), MySQL (Windows)

sort_buffer_size

  • Description: Each session performing a sort allocates a buffer with this amount of memory. Not specific to any storage engine. If the status variable sort_merge_passes is too high, you may need to look at improving your query indexes, or increasing this. Consider reducing where there are many small sorts, such as OLTP, and increasing where needed by session. 16k is a suggested minimum.
  • Commandline: --sort-buffer-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: number
  • Default Value: 2M (2097152) (some distributions increase the default)

sql_auto_is_null

  • Description: If set to 1, the query SELECT * FROM table_name WHERE auto_increment_column IS NULL will return an auto-increment that has just been successfully inserted, the same as the LAST_INSERT_ID() function. Some ODBC programs make use of this IS NULL comparison.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0 (>= MariaDB/MySQL 5.5.3), 1 (MariaDB/MySQL <= 5.5.2)

sql_big_selects

  • Description: If set to 0, MariaDB will not perform large SELECTs. See max_join_size for details. If max_join_size is set to anything but DEFAULT, sql_big_selects is automatically set to 0. If sql_big_selects is again set, max_join_size will be ignored.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

sql_big_tables


sql_buffer_result

  • Description: If set to 1 (0 is default), results from SELECT statements are always placed into temporary tables. This can help the server when it takes a long time to send the results to the client by allowing the table locks to be freed early.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

sql_log_off

  • Description: If set to 1 (0 is the default), no logging to the general query log is done for the client. Only clients with the SUPER privilege can update this variable.
  • Scope: Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 0

sql_log_update

  • Description: Removed. Use sql_log_bin instead.
  • Removed: MariaDB/MySQL 5.5

sql_low_priority_updates


sql_max_join_size


sql_mode

  • Description: Sets the SQL Mode. Multiple modes can be set, separated by a comma.
  • Commandline: --sql-mode=value[,value[,value...]]
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value:
  • Valid Values: See SQL Mode for the full list.

sql_notes

  • Description: If set to 1, the default, warning_count is incremented each time a Note warning is encountered. If set to 0, Note warnings are not recorded. mysqldump has outputs to set this variable to 0 so that no unnecessary increments occur when data is reloaded.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

sql_quote_show_create

  • Description: If set to 1, the default, the server will quote identifiers for SHOW CREATE DATABASE, SHOW CREATE TABLE and SHOW CREATE VIEW statements. Quoting is disabled if set to 0. Enable to ensure replications works when identifiers require quoting.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: 1

sql_safe_updates

  • Description: If set to 1, UPDATE's and DELETE's need either a key in the WHERE clause, or a LIMIT clause, or else they will aborted. Prevents the common mistake of accidentally deleting or updating every row in a table.
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF (0)

sql_select_limit

  • Description: Maximum number of rows that can be returned from a SELECT query. Default is the maximum number of rows permitted per table by the server, usually 232-1 or 264-1. Can be restored to the default value after being changed by assigning it a value of DEFAULT.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0

sql_warnings

  • Description: If set to 1, single-row INSERT's will produce a string containing warning information if a warning occurs.
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF (0)

storage_engine


standard_compliant_cte

  • Description: Allow only standard-compliant common table expressions. Prior to version 10.2.4, this variable was named standards_compliant_cte.
  • Commandline: --standard-compliant-cte={0|1}
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON
  • Introduced: MariaDB 10.2.2

stored_program_cache

  • Description: Limit to the number of stored routines held in the stored procedures and stored functions caches. Each time a stored routine is executed, this limit is first checked, and if the number held in the cache exceeds this, that cache is flushed and memory freed.
  • Commandline: --stored-program-cache=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 256
  • Range: 256 to 524288
  • Introduced: MariaDB/MySQL 5.5.21

strict_password_validation

  • Description: When password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash). This excludes direct updates to the privilege tables.
  • Commandline: --strict-password-validation
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: ON
  • Introduced: MariaDB 10.1.2

sync_frm

  • Description: If set to 1, the default, each time a non-temporary table is created, its .frm definition file is synced to disk. Fractionally slower, but safer in case of a crash.
  • Commandline: --sync-frm
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: TRUE

system_time_zone

  • Description: Server system time zone, inherited from the machine setting when the server starts. Usually specified by the TZ environment variable, can also be specified by the --timezone option of the mysqld_safe script, or affected by the environment of the account starting the server. Not the same as the time_zone system variable, which is used for client time zones.
  • Scope: Global
  • Dynamic: No
  • Data Type: string

table_definition_cache

  • Description: Number of table definitions that can be cached. Table definitions are taken from the .frm files, and if there are a large number of tables increasing the cache size can speed up table opening. Unlike the table_open_cache, as the table_definition_cache doesn't use file descriptors, and is much smaller.
  • Commandline: --table-definition-cache=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 400
  • Range: 400 to 524288

table_lock_wait_timeout

  • Description: Unused, and removed in MariaDB/MySQL 5.5.3
  • Commandline: --table-lock-wait-timeout=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 50
  • Range: 1 to 1073741824
  • Removed: MariaDB/MySQL 5.5.3

table_open_cache

  • Description: Number of open tables for all threads. See Optimizing table_open_cache for suggestions on optimizing. Increasing table_open_cache increases the number of file descriptors required. Previously called table_cache prior to MySQL 5.1.3
  • Commandline: --table-open-cache=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 2000 (>= MariaDB 10.1.7), 400 (<= MariaDB 10.1.6)
  • Range: 1 to 1048576 (1024K) (>= MariaDB 10.1.20), 1 to 524288 (512K) (<= MariaDB 10.1.19)

table_open_cache_instances

  • Description: From MariaDB 10.2.2, specifies the maximum number of table cache instances. This is different to the MySQL version which specifies the number of table cache instances. The MariaDB implementation is more efficient, and always starts with 1 instance, increasing the number up to the maximum when there's contention. Whenever an instance is activated a note is written to error log, for example Detected table cache mutex contention at instance 1: 25% waits. Additional table cache instance activated. Number of instances after activation: 2. The number of instances does not decrease again. The current default value (8) is expected to handle up to 100 CPU cores - beyond this, the setting should be increased. Until MariaDB 10.0.7, this was an unused MySQL 5.6 compatibility option, as MariaDB achieved similar results in a different way (see MDEV-4702).
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 8 (>= MariaDB 10.2.2), 1 (<= MariaDB 10.0.7)
  • Range:
  • Introduced: MariaDB 10.2.2 (previously in MariaDB 10.0.4)
  • Removed: MariaDB 10.0.7, reintroduced MariaDB 10.2.2

table_type


thread_cache_size

  • Description: Number of threads server caches for re-use. If this limit hasn't been reached, when a client disconnects, its threads are put into the cache, and re-used where possible. These are freed after 5 minutes of idle time. Normally this setting has little effect, as the other aspects of the thread implementation are more important, but increasing it can help servers with high volumes of connections per second so that most can use a cached, rather than a new, thread. The cache miss rate can be calculated as the server status variables threads_created/connections. If the thread pool is active, thread_cache_size is ignored. From MariaDB 10.2.0, the default is automatically set to the smaller of either 256, or the max_connections size.
  • Commandline: --thread-cache-size=#
  • Scope: Global
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 0 (<= MariaDB 10.1), Auto (from MariaDB 10.2.0)
  • Range: 0 to 16384

thread_concurrency

  • Description: Allows applications to give the system a hint about the desired number of threads. Specific to Solaris only, invokes thr_setconcurrency().
  • Commandline: --thread-concurrency=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value: 10
  • Range: 1 to 512

thread_stack

  • Description: Stack size for each thread. If set too small, limits recursion depth of stored procedures and complexity of SQL statements the server can handle in memory. Also affects limits in the crash-me test.
  • Commandline: --thread-stack=#
  • Scope: Global
  • Dynamic: No
  • Data Type: numeric
  • Default Value:
  • Range: 131072 to 18446744073709551615

time_format

  • Description: Unused.

time_zone

  • Description: The current time zone, used to initialize the time zone for a client when it connects. Set to SYSTEM by default, in which the client uses the system time zone value.
  • Commandline: --default-time-zone=string
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: string
  • Default Value: SYSTEM

timed_mutexes

  • Description: Determines whether InnoDB mutexes are timed. OFF, the default, disables mutex timing, while ON enables it. See also SHOW ENGINE for more on mutex statistics. Deprecated in MariaDB 5.5.39, as has no effect anymore.
  • Commandline: --timed-mutexes
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Deprecated: MariaDB 5.5.39

timestamp

  • Description: Sets the time for the client. This will affect the result returned by the NOW() function, not the SYSDATE() function, unless the server is started with the --sysdate-is-now option, in which case SYSDATE becomes an alias of NOW, and will also be affected. Also used to get the original timestamp when restoring rows from the binary log.
  • Scope: Session
  • Dynamic: Yes
  • Valid Values: timestamp_value (Unix epoch timestamp, not MariaDB timestamp), DEFAULT

tmp_table_size

  • Description: The largest size for temporary tables in memory (not MEMORY tables) although if max_heap_table_size is smaller the lower limit will apply. If a table exceeds the limit, MariaDB converts it to a MyISAM or Aria table. You can see if it's necessary to increase by comparing the status variables Created_tmp_disk_tables and Created_tmp_tables to see how many temporary tables out of the total created needed to be converted to disk. Often complex GROUP BY queries are responsible for exceeding the limit. Defaults may be different on some systems, see for example Differences in MariaDB in Debian.
  • Commandline: --tmp-table-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: numeric
  • Default Value: 16777216 (16MB)
  • Range: 1024 to 4294967295

tmpdir

  • Description: Directory for storing temporary tables and files. Can specify a list (separated by semicolons in Windows, and colons in Unix that will then be used in round-robin fashion. This can be used for load balancing across several disks. Note that if the server is a replication slave, and slave_load_tmpdir, which overrides tmpdir for slave replication, is not set, you should not set tmpdir to a directory that is cleared when the machine restarts, or else replication may fail.
  • Commandline: --tmpdir=path or -t path
  • Scope: Global
  • Dynamic: No
  • Type: directory name/s

transaction_alloc_block_size

  • Description: Size in bytes to increase the memory pool available to each transaction when the available pool is not large enough. See transaction_prealloc_size.
  • Commandline: --transaction-alloc-block-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 8192
  • Range: 1024 to 4294967295
  • Block Size: 1024

transaction_prealloc_size

  • Description: Initial size of a memory pool available to each transaction for various memory allocations. If the memory pool is not large enough for an allocation, it is increased by transaction_alloc_block_size bytes, and truncated back to transaction_prealloc_size bytes when the transaction is completed. If set large enough to contain all statements in a transaction, extra malloc() calls are avoided.
  • Commandline: --transaction-prealloc-size=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 4096
  • Range: 1024 to 4294967295
  • Block Size: 1024

tx_isolation

  • Description: The transaction isolation level. See also SET TRANSACTION ISOLATION LEVEL.
  • Commandline: --transaction-isolation=name
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: enumeration
  • Default Value: REPEATABLE-READ
  • Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

tx_read_only

  • Description: Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION.
  • Commandline: --transaction-read-only=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: OFF-READ
  • Introduced: MariaDB 10.0

unique_checks

  • Description: If set to 1, the default, secondary indexes in InnoDB tables are performed. If set to 0, storage engines can (but are not required to) assume that duplicate keys are not present in input data. Set to 0 to speed up imports of large tables to InnoDB. The storage engine will still issue a duplicate key error if it detects one, even if set to 0.
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: 1

updatable_views_with_limit

  • Description: Determines whether view updates can be made with an UPDATE or DELETE statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table. 0 prohibits this, while 1 permits it while issuing a warning (the default).
  • Commandline: --updatable-views-with-limit=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: boolean
  • Default Value: 1

use_stat_tables

  • Description: Controls the use of engine-independent table statistics.
    • never The optimizer will not use data from statistics tables.
    • complementary The optimizer uses data from statistics tables if the same kind of data is not provided by the storage engine.
    • preferably Prefer the data from statistics tables, if it's not available there, use the data from the storage engine.
  • Commandline: --use-stat-tables=mode
  • Scope: Global, Session
  • Dynamic: Yes
  • Data Type: enum
  • Default Value: never
  • Introduced: MariaDB 10.0.1

userstat

  • Description: If set to 1, user statistics will be activated.
  • Commandline: --userstat=1
  • Scope: Global
  • Dynamic: Yes
  • Data Type: boolean
  • Default Value: OFF
  • Introduced: MariaDB 5.2.0

version

  • Description: Server version number. It may also include a suffix with configuration or build information. -debug indicates debugging support was enabled on the server, and -log indicates at least one of the binary log, general log or slow query log are enabled, for example 10.0.1-MariaDB-mariadb1precise-log. From MariaDB 10.2.1, this variable can be set at startup in order to fake the server version.
  • Commandline: -V, --version[=name] (>= MariaDB 10.2.1), --version (<= MariaDB 10.2.0)
  • Scope: Global
  • Dynamic: No
  • Type: string

version_comment

  • Description: Value of the COMPILATION_COMMENT option specified by CMake when building MariaDB, for example mariadb.org binary distribution
  • Scope: Global
  • Dynamic: No
  • Type: string

version_compile_machine

  • Description: The machine type or architecture MariaDB was built on, for example i686
  • Scope: Global
  • Dynamic: No
  • Type: string

version_compile_os

  • Description: Operating system that MariaDB was built on, for example debian-linux-gnu
  • Scope: Global
  • Dynamic: No
  • Type: string

version_malloc_library

  • Commandline: No
  • Description: Version of the used malloc library
  • Scope: Global
  • Dynamic: No
  • Type: string
  • Introduced: MariaDB 10.0.8

wait_timeout

  • Description: Time in seconds that the server waits for a connection to become active before closing it. The session value is initialized when a thread starts up from either the global value, if the connection is non-interactive, or from the interactive_timeout value, if the connection is interactive.
  • Commandline: --wait-timeout=#
  • Scope: Global, Session
  • Dynamic: Yes
  • Type: numeric
  • Default Value: 28800
  • Range: 1 to 2147483

warning_count

  • Description: Read-only variable indicating the number of warnings, errors and notes resulting from the most recent statement that generated messages. See SHOW WARNINGS for more. Note warnings will only be recorded if sql_notes is true (the default).
  • Scope: Session
  • Dynamic: No
  • Type: numeric

Comments

Comments loading...
Loading