MySQL Server Administration - MariaDB - Databases - Software - Computers


MySQL Server Administration
Prev Next

MySQL Server Administration

Table of Contents

The MariaDB Server
Server Option and Variable Reference
Server Command Options
Server System Variables
Using System Variables
Server Status Variables
Server SQL Modes
Server Plugins
Server-Side Help
Server Response to Signals
The Shutdown Process
MySQL Server Logs
Selecting General Query and Slow Query Log Output Destinations
The Error Log
The General Query Log
The Binary Log
The Slow Query Log
Server Log Maintenance
General Security Issues
General Security Guidelines
Password Security in MySQL
Making MariaDB Secure Against Attackers
Security-Related mysqld Options
Security Issues with LOAD DATA LOCAL
How to Run MariaDB as a Normal User
The MariaDB Access Privilege System
Privileges Provided by MySQL
Privilege System Grant Tables
Specifying Account Names
Access Control, Stage 1: Connection Verification
Access Control, Stage 2: Request Verification
When Privilege Changes Take Effect
Causes of Access-Denied Errors
MySQL User Account Management
User Names and Passwords
Adding User Accounts
Removing User Accounts
Setting Account Resource Limits
Assigning Account Passwords
Pluggable Authentication
Proxy Users
Using SSL for Secure Connections
Connecting to MariaDB Remotely from Windows with SSH
Auditing MariaDB Account Activity
Running Multiple MariaDB Instances on One Machine
Setting Up Multiple Data Directories
Running Multiple MariaDB Instances on Windows
Running Multiple MariaDB Instances on Unix
Using Client Programs in a Multiple-Server Environment
Tracing mysqld Using DTrace
mysqld DTrace Probe Reference

MySQL Server (mysqld) is the main program that does most of the work in a MariaDB installation. This section provides an overview of MariaDB Server and covers topics that deal with administering a MariaDB installation:

The MariaDB Server

Server Option and Variable Reference
Server Command Options
Server System Variables
Using System Variables
Server Status Variables
Server SQL Modes
Server Plugins
Server-Side Help
Server Response to Signals
The Shutdown Process

mysqld is the MariaDB server. The following discussion covers these MariaDB server configuration topics:

Note

Not all storage engines are supported by all MariaDB server binaries and configurations. To find out how to determine which storage engines your MariaDB server installation supports, see , "SHOW ENGINES Syntax".

Server Option and Variable Reference

The following table provides a list of all the command line options, server and status variables applicable within mysqld.

The table lists command-line options (Cmd-line), options valid in configuration files (Option file), server system variables (System Var), and status variables (Status var) in one unified list, with notification of where each option/variable is valid. If a server option set on the command line or in an option file differs from the name of the corresponding server system or status variable, the variable name is noted immediately below the corresponding option. For status variables, the scope of the variable is shown (Scope) as either global, session, or both. Please see the corresponding sections for details on setting and using the options and variables. Where appropriate, a direct link to further information on the item as available.

Table 5.1. Option/Variable Summary

Name Cmd-Line Option file System Var Status Var Var Scope Dynamic
abort-slave-event-count Yes Yes
Aborted_clients Yes Global No
Aborted_connects Yes Global No
allow-suspicious-udfs Yes Yes
ansi Yes Yes
auto_increment_increment Yes Yes Yes Both Yes
auto_increment_offset Yes Yes Yes Both Yes
autocommit Yes Yes Yes Both Yes
automatic_sp_privileges Yes Global Yes
back_log Yes Yes Yes Global No
basedir Yes Yes Yes Global No
big-tables Yes Yes Both Yes
- Variable: big_tables Yes Both Yes
bind-address Yes Yes Yes Global No
Binlog_cache_disk_use Yes Global No
binlog_cache_size Yes Yes Yes Global Yes
Binlog_cache_use Yes Global No
binlog-checksum Yes Yes
binlog_checksum Yes Global Yes
binlog_direct_non_transactional_updates Yes Yes Yes Both Yes
binlog-do-db Yes Yes
binlog-format Yes Yes Both Yes
- Variable: binlog_format Yes Both Yes
binlog-ignore-db Yes Yes
binlog-row-event-max-size Yes Yes
binlog_row_image Yes Yes Both Yes
binlog_rows_query_log_events Yes Both Yes
binlog-rows-query-log-events Yes Yes
- Variable: binlog_rows_query_log_events
Binlog_stmt_cache_disk_use Yes Global No
binlog_stmt_cache_size Yes Yes Yes Global Yes
Binlog_stmt_cache_use Yes Global No
bootstrap Yes Yes
bulk_insert_buffer_size Yes Yes Yes Both Yes
Bytes_received Yes Both No
Bytes_sent Yes Both No
character_set_client Yes Both Yes
character-set-client-handshake Yes Yes
character_set_connection Yes Both Yes
character_set_database[a] Yes Both Yes
character-set-filesystem Yes Yes Both Yes
- Variable: character_set_filesystem Yes Both Yes
character_set_results Yes Both Yes
character-set-server Yes Yes Both Yes
- Variable: character_set_server Yes Both Yes
character_set_system Yes Global No
character-sets-dir Yes Yes Global No
- Variable: character_sets_dir Yes Global No
chroot Yes Yes
collation_connection Yes Both Yes
collation_database[b] Yes Both Yes
collation-server Yes Yes Both Yes
- Variable: collation_server Yes Both Yes
Com_admin_commands Yes Both No
Com_alter_db Yes Both No
Com_alter_db_upgrade Yes Both No
Com_alter_event Yes Both No
Com_alter_function Yes Both No
Com_alter_procedure Yes Both No
Com_alter_server Yes Both No
Com_alter_table Yes Both No
Com_alter_tablespace Yes Both No
Com_analyze Yes Both No
Com_assign_to_keycache Yes Both No
Com_begin Yes Both No
Com_binlog Yes Both No
Com_call_procedure Yes Both No
Com_change_db Yes Both No
Com_change_master Yes Both No
Com_check Yes Both No
Com_checksum Yes Both No
Com_commit Yes Both No
Com_create_db Yes Both No
Com_create_event Yes Both No
Com_create_function Yes Both No
Com_create_index Yes Both No
Com_create_procedure Yes Both No
Com_create_server Yes Both No
Com_create_table Yes Both No
Com_create_trigger Yes Both No
Com_create_udf Yes Both No
Com_create_user Yes Both No
Com_create_view Yes Both No
Com_dealloc_sql Yes Both No
Com_delete Yes Both No
Com_delete_multi Yes Both No
Com_do Yes Both No
Com_drop_db Yes Both No
Com_drop_event Yes Both No
Com_drop_function Yes Both No
Com_drop_index Yes Both No
Com_drop_procedure Yes Both No
Com_drop_server Yes Both No
Com_drop_table Yes Both No
Com_drop_trigger Yes Both No
Com_drop_user Yes Both No
Com_drop_view Yes Both No
Com_empty_query Yes Both No
Com_execute_sql Yes Both No
Com_flush Yes Both No
Com_grant Yes Both No
Com_ha_close Yes Both No
Com_ha_open Yes Both No
Com_ha_read Yes Both No
Com_help Yes Both No
Com_insert Yes Both No
Com_insert_select Yes Both No
Com_install_plugin Yes Both No
Com_kill Yes Both No
Com_load Yes Both No
Com_lock_tables Yes Both No
Com_optimize Yes Both No
Com_preload_keys Yes Both No
Com_prepare_sql Yes Both No
Com_purge Yes Both No
Com_purge_before_date Yes Both No
Com_release_savepoint Yes Both No
Com_rename_table Yes Both No
Com_rename_user Yes Both No
Com_repair Yes Both No
Com_replace Yes Both No
Com_replace_select Yes Both No
Com_reset Yes Both No
Com_resignal Yes Both No
Com_revoke Yes Both No
Com_revoke_all Yes Both No
Com_rollback Yes Both No
Com_rollback_to_savepoint Yes Both No
Com_savepoint Yes Both No
Com_select Yes Both No
Com_set_option Yes Both No
Com_show_authors Yes Both No
Com_show_binlog_events Yes Both No
Com_show_binlogs Yes Both No
Com_show_charsets Yes Both No
Com_show_collations Yes Both No
Com_show_contributors Yes Both No
Com_show_create_db Yes Both No
Com_show_create_event Yes Both No
Com_show_create_func Yes Both No
Com_show_create_proc Yes Both No
Com_show_create_table Yes Both No
Com_show_create_trigger Yes Both No
Com_show_databases Yes Both No
Com_show_engine_logs Yes Both No
Com_show_engine_mutex Yes Both No
Com_show_engine_status Yes Both No
Com_show_errors Yes Both No
Com_show_events Yes Both No
Com_show_fields Yes Both No
Com_show_function_code Yes Both No
Com_show_function_status Yes Both No
Com_show_grants Yes Both No
Com_show_keys Yes Both No
Com_show_logs Yes Both No
Com_show_master_status Yes Both No
Com_show_new_master Yes Both No
Com_show_open_tables Yes Both No
Com_show_plugins Yes Both No
Com_show_privileges Yes Both No
Com_show_procedure_code Yes Both No
Com_show_procedure_status Yes Both No
Com_show_processlist Yes Both No
Com_show_profile Yes Both No
Com_show_profiles Yes Both No
Com_show_relaylog_events Yes Both No
Com_show_slave_hosts Yes Both No
Com_show_slave_status Yes Both No
Com_show_status Yes Both No
Com_show_storage_engines Yes Both No
Com_show_table_status Yes Both No
Com_show_tables Yes Both No
Com_show_triggers Yes Both No
Com_show_variables Yes Both No
Com_show_warnings Yes Both No
Com_signal Yes Both No
Com_slave_start Yes Both No
Com_slave_stop Yes Both No
Com_stmt_close Yes Both No
Com_stmt_execute Yes Both No
Com_stmt_fetch Yes Both No
Com_stmt_prepare Yes Both No
Com_stmt_reprepare Yes Both No
Com_stmt_reset Yes Both No
Com_stmt_send_long_data Yes Both No
Com_truncate Yes Both No
Com_uninstall_plugin Yes Both No
Com_unlock_tables Yes Both No
Com_update Yes Both No
Com_update_multi Yes Both No
Com_xa_commit Yes Both No
Com_xa_end Yes Both No
Com_xa_prepare Yes Both No
Com_xa_recover Yes Both No
Com_xa_rollback Yes Both No
Com_xa_start Yes Both No
completion_type Yes Yes Yes Both Yes
Compression Yes Session No
concurrent_insert Yes Yes Yes Global Yes
connect_timeout Yes Yes Yes Global Yes
Connection_errors_accept Yes Global No
Connection_errors_internal Yes Global No
Connection_errors_max_connections Yes Global No
Connection_errors_peer_addr Yes Global No
Connection_errors_select Yes Global No
Connection_errors_tcpwrap Yes Global No
Connections Yes Global No
console Yes Yes
core-file Yes Yes
Created_tmp_disk_tables Yes Both No
Created_tmp_files Yes Global No
Created_tmp_tables Yes Both No
datadir Yes Yes Yes Global No
date_format Yes Global No
datetime_format Yes Global No
debug Yes Yes Yes Both Yes
debug_sync Yes Session Yes
debug-sync-timeout Yes Yes
default-storage-engine Yes Yes Both Yes
- Variable: default_storage_engine Yes Both Yes
default-time-zone Yes Yes
default_tmp_storage_engine Yes Yes Yes Both Yes
default_week_format Yes Yes Yes Both Yes
defaults-extra-file Yes
defaults-file Yes
defaults-group-suffix Yes
delay-key-write Yes Yes Global Yes
- Variable: delay_key_write Yes Global Yes
Delayed_errors Yes Global No
delayed_insert_limit Yes Yes Yes Global Yes
Delayed_insert_threads Yes Global No
delayed_insert_timeout Yes Yes Yes Global Yes
delayed_queue_size Yes Yes Yes Global Yes
Delayed_writes Yes Global No
des-key-file Yes Yes
disconnect-slave-event-count Yes Yes
div_precision_increment Yes Yes Yes Both Yes
enable-named-pipe Yes Yes
- Variable: named_pipe
end_markers_in_json Yes Both Yes
engine-condition-pushdown Yes Yes Both Yes
- Variable: engine_condition_pushdown Yes Both Yes
eq_range_index_dive_limit Yes Yes Yes Both Yes
error_count Yes Session No
event-scheduler Yes Yes Global Yes
- Variable: event_scheduler Yes Global Yes
exit-info Yes Yes
expire_logs_days Yes Yes Yes Global Yes
external-locking Yes Yes
- Variable: skip_external_locking
external_user Yes Session No
federated Yes Yes
flush Yes Yes Yes Global Yes
Flush_commands Yes Global No
flush_time Yes Yes Yes Global Yes
foreign_key_checks Yes Both Yes
ft_boolean_syntax Yes Yes Yes Global Yes
ft_max_word_len Yes Yes Yes Global No
ft_min_word_len Yes Yes Yes Global No
ft_query_expansion_limit Yes Yes Yes Global No
ft_stopword_file Yes Yes Yes Global No
gdb Yes Yes
general-log Yes Yes Global Yes
- Variable: general_log Yes Global Yes
general_log_file Yes Yes Yes Global Yes
group_concat_max_len Yes Yes Yes Both Yes
Handler_commit Yes Both No
Handler_delete Yes Both No
Handler_discover Yes Both No
Handler_external_lock Yes Both No
Handler_prepare Yes Both No
Handler_read_first Yes Both No
Handler_read_key Yes Both No
Handler_read_last Yes Both No
Handler_read_next Yes Both No
Handler_read_prev Yes Both No
Handler_read_rnd Yes Both No
Handler_read_rnd_next Yes Both No
Handler_rollback Yes Both No
Handler_savepoint Yes Both No
Handler_savepoint_rollback Yes Both No
Handler_update Yes Both No
Handler_write Yes Both No
have_compress Yes Global No
have_crypt Yes Global No
have_csv Yes Global No
have_dynamic_loading Yes Global No
have_geometry Yes Global No
have_innodb Yes Global No
have_ndbcluster Yes Global No
have_openssl Yes Global No
have_partitioning Yes Global No
have_profiling Yes Global No
have_query_cache Yes Global No
have_rtree_keys Yes Global No
have_ssl Yes Global No
have_symlink Yes Global No
help Yes Yes
host_cache_size Yes Yes Yes Global Yes
hostname Yes Global No
identity Yes Session Yes
ignore-builtin-innodb Yes Yes Global No
- Variable: ignore_builtin_innodb Yes Global No
ignore-db-dir Yes Yes
- Variable: ignore_db_dirs
init_connect Yes Yes Yes Global Yes
init-file Yes Yes Global No
- Variable: init_file Yes Global No
init_slave Yes Yes Yes Global Yes
innodb Yes Yes
innodb_adaptive_flushing Yes Yes Yes Global Yes
innodb_adaptive_hash_index Yes Yes Yes Global Yes
innodb_adaptive_max_sleep_delay Yes Yes Yes Global Yes
innodb_additional_mem_pool_size Yes Yes Yes Global No
innodb_analyze_is_persistent Yes Yes Yes Global Yes
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_autoinc_lock_mode Yes Yes Yes Global No
innodb_buffer_pool_dump_at_shutdown Yes Yes Yes Global Yes
innodb_buffer_pool_dump_now Yes Yes Yes Global Yes
Innodb_buffer_pool_dump_status Yes Global No
innodb_buffer_pool_filename Yes Yes Yes Global Yes
innodb_buffer_pool_instances Yes Yes Yes Global No
innodb_buffer_pool_load_abort Yes Yes Yes Global Yes
innodb_buffer_pool_load_at_startup Yes Yes Yes Global Yes
innodb_buffer_pool_load_now Yes Yes Yes Global Yes
Innodb_buffer_pool_load_status Yes Global No
Innodb_buffer_pool_pages_data Yes Global No
Innodb_buffer_pool_pages_dirty Yes Global No
Innodb_buffer_pool_pages_flushed Yes Global No
Innodb_buffer_pool_pages_free Yes Global No
Innodb_buffer_pool_pages_latched Yes Global No
Innodb_buffer_pool_pages_misc Yes Global No
Innodb_buffer_pool_pages_total Yes Global No
Innodb_buffer_pool_read_ahead Yes Global No
Innodb_buffer_pool_read_ahead_evicted Yes Global No
Innodb_buffer_pool_read_requests Yes Global No
Innodb_buffer_pool_reads Yes Global No
innodb_buffer_pool_size Yes Yes Yes Global No
Innodb_buffer_pool_wait_free Yes Global No
Innodb_buffer_pool_write_requests Yes Global No
innodb_change_buffer_max_size Yes Yes Yes Global Yes
innodb_change_buffering Yes Yes Yes Global Yes
innodb_checksum_algorithm Yes Yes Yes Global Yes
innodb_checksums Yes Yes Yes Global No
innodb_commit_concurrency Yes Yes Yes Global Yes
innodb_concurrency_tickets Yes Yes Yes Global Yes
innodb_data_file_path Yes Yes Yes Global No
Innodb_data_fsyncs Yes Global No
innodb_data_home_dir Yes Yes Yes Global No
Innodb_data_pending_fsyncs Yes Global No
Innodb_data_pending_reads Yes Global No
Innodb_data_pending_writes Yes Global No
Innodb_data_read Yes Global No
Innodb_data_reads Yes Global No
Innodb_data_writes Yes Global No
Innodb_data_written Yes Global No
Innodb_dblwr_pages_written Yes Global No
Innodb_dblwr_writes Yes Global No
innodb_doublewrite Yes Yes Yes Global No
innodb_fast_shutdown Yes Yes Yes Global Yes
innodb_file_format Yes Yes Yes Global Yes
innodb_file_format_check Yes Yes Yes Global No
innodb_file_format_max Yes Yes Yes Global Yes
innodb_file_per_table Yes Yes Yes Global Yes
innodb_flush_log_at_trx_commit Yes Yes Yes Global Yes
innodb_flush_method Yes Yes Yes Global No
innodb_flush_neighbors Yes Yes Yes Global Yes
innodb_force_load_corrupted Yes Yes Yes Global No
innodb_force_recovery Yes Yes Yes Global No
innodb_ft_aux_table Yes Yes Yes Global Yes
innodb_ft_cache_size Yes Yes Yes Global Yes
innodb_ft_enable_stopword Yes Yes Yes Global Yes
innodb_ft_max_token_size Yes Yes Yes Global Yes
innodb_ft_min_token_size Yes Yes Yes Global Yes
innodb_ft_num_word_optimize Yes Yes Yes Global Yes
innodb_ft_server_stopword_table Yes Yes Yes Global Yes
innodb_ft_sort_pll_degree Yes Yes Yes Global Yes
innodb_ft_user_stopword_table Yes Yes Yes Global Yes
Innodb_have_atomic_builtins Yes Global No
innodb_io_capacity Yes Yes Yes Global Yes
innodb_large_prefix Yes Yes Yes Global Yes
innodb_lock_wait_timeout Yes Yes Yes Both Yes
innodb_locks_unsafe_for_binlog Yes Yes Yes Global No
innodb_log_buffer_size Yes Yes Yes Global No
innodb_log_file_size Yes Yes Yes Global No
innodb_log_files_in_group Yes Yes Yes Global No
innodb_log_group_home_dir Yes Yes Yes Global No
Innodb_log_waits Yes Global No
Innodb_log_write_requests Yes Global No
Innodb_log_writes Yes Global No
innodb_lru_scan_depth Yes Yes Yes Global Yes
innodb_max_dirty_pages_pct Yes Yes Yes Global Yes
innodb_max_purge_lag Yes Yes Yes Global Yes
innodb_mirrored_log_groups Yes Yes Yes Global No
innodb_monitor_disable Yes Yes Yes Global Yes
innodb_monitor_enable Yes Yes Yes Global Yes
innodb_monitor_reset Yes Yes Yes Global Yes
innodb_monitor_reset_all Yes Yes Yes Global Yes
Innodb_num_open_files Yes Global No
innodb_old_blocks_pct Yes Yes Yes Global Yes
innodb_old_blocks_time Yes Yes Yes Global Yes
innodb_open_files Yes Yes Yes Global No
innodb_optimize_fulltext_only Yes Yes Yes Global Yes
Innodb_os_log_fsyncs Yes Global No
Innodb_os_log_pending_fsyncs Yes Global No
Innodb_os_log_pending_writes Yes Global No
Innodb_os_log_written Yes Global No
innodb_page_size Yes Yes Yes Global No
Innodb_page_size Yes Global No
Innodb_pages_created Yes Global No
Innodb_pages_read Yes Global No
Innodb_pages_written Yes Global No
innodb_print_all_deadlocks Yes Yes Yes Global Yes
innodb_purge_batch_size Yes Yes Yes Global No
innodb_purge_threads Yes Yes Yes Global No
innodb_random_read_ahead Yes Yes Yes Global Yes
innodb_read_ahead_threshold Yes Yes Yes Global Yes
innodb_read_io_threads Yes Yes Yes Global No
innodb_replication_delay Yes Yes Yes Global Yes
innodb_rollback_on_timeout Yes Yes Yes Global No
innodb_rollback_segments Yes Yes Yes Global Yes
Innodb_row_lock_current_waits Yes Global No
Innodb_row_lock_time Yes Global No
Innodb_row_lock_time_avg Yes Global No
Innodb_row_lock_time_max Yes Global No
Innodb_row_lock_waits Yes Global No
Innodb_rows_deleted Yes Global No
Innodb_rows_inserted Yes Global No
Innodb_rows_read Yes Global No
Innodb_rows_updated Yes Global No
innodb_sort_buffer_size Yes Yes Yes Global Yes
innodb_spin_wait_delay Yes Yes Yes Global Yes
innodb_stats_method Yes Yes Yes Both Yes
innodb_stats_on_metadata Yes Yes Yes Global Yes
innodb_stats_persistent_sample_pages Yes Yes Yes Global Yes
innodb_stats_sample_pages Yes Yes Yes Global Yes
innodb_stats_transient_sample_pages Yes Yes Yes Global Yes
innodb-status-file Yes Yes
innodb_strict_mode Yes Yes Yes Both Yes
innodb_support_xa Yes Yes Yes Both Yes
innodb_sync_array_size Yes Yes Yes Global No
innodb_sync_spin_loops Yes Yes Yes Global Yes
innodb_table_locks Yes Yes Yes Both Yes
innodb_thread_concurrency Yes Yes Yes Global Yes
innodb_thread_sleep_delay Yes Yes Yes Global Yes
Innodb_truncated_status_writes Yes Global No
innodb_undo_directory Yes Yes Yes Global Yes
innodb_undo_logs Yes Yes Yes Global Yes
innodb_undo_tablespaces Yes Yes Yes Global Yes
innodb_use_native_aio Yes Yes Yes Global No
innodb_use_sys_malloc Yes Yes Yes Global No
innodb_version Yes Global No
innodb_write_io_threads Yes Yes Yes Global No
insert_id Yes Session Yes
install Yes
install-manual Yes
interactive_timeout Yes Yes Yes Both Yes
join_buffer_size Yes Yes Yes Both Yes
keep_files_on_create Yes Yes Yes Both Yes
Key_blocks_not_flushed Yes Global No
Key_blocks_unused Yes Global No
Key_blocks_used Yes Global No
key_buffer_size Yes Yes Yes Global Yes
key_cache_age_threshold Yes Yes Yes Global Yes
key_cache_block_size Yes Yes Yes Global Yes
key_cache_division_limit Yes Yes Yes Global Yes
Key_read_requests Yes Global No
Key_reads Yes Global No
Key_write_requests Yes Global No
Key_writes Yes Global No
language Yes Yes Yes Global No
large_files_support Yes Global No
large_page_size Yes Global No
large-pages Yes Yes Global No
- Variable: large_pages Yes Global No
last_insert_id Yes Session Yes
Last_query_cost Yes Session No
Last_query_partial_plans Yes Session No
lc-messages Yes Yes Both Yes
- Variable: lc_messages Yes Both Yes
lc-messages-dir Yes Yes Global No
- Variable: lc_messages_dir Yes Global No
lc_time_names Yes Both Yes
license Yes Global No
local_infile Yes Global Yes
local-infile Yes Yes
- Variable: local_infile
lock_wait_timeout Yes Yes Yes Both Yes
locked_in_memory Yes Global No
log Yes Yes Yes Global Yes
log_bin Yes Global No
log-bin Yes Yes Yes Global No
log_bin_basename Yes Global No
log-bin-index Yes Yes
log-error Yes Yes Global No
- Variable: log_error Yes Global No
log-isam Yes Yes
log-output Yes Yes Global Yes
- Variable: log_output Yes Global Yes
log-queries-not-using-indexes Yes Yes Global Yes
- Variable: log_queries_not_using_indexes Yes Global Yes
log-raw Yes Yes
log-short-format Yes Yes
log-slave-updates Yes Yes Global No
- Variable: log_slave_updates Yes Global No
log-slow-admin-statements Yes Yes
log-slow-queries Yes Yes Global Yes
- Variable: log_slow_queries Yes Global Yes
log-slow-slave-statements Yes Yes
log-tc Yes Yes
log-tc-size Yes Yes
log_throttle_queries_not_using_indexes Yes Global Yes
log-warnings Yes Yes Global Yes
- Variable: log_warnings Yes Global Yes
long_query_time Yes Yes Yes Both Yes
low-priority-updates Yes Yes Both Yes
- Variable: low_priority_updates Yes Both Yes
lower_case_file_system Yes Yes Yes Global No
lower_case_table_names Yes Yes Yes Global No
master-info-file Yes Yes
master_info_repository Yes Global No
master-info-repository Yes Yes
- Variable: master_info_repository
master-retry-count Yes Yes
master_verify_checksum Yes Global Yes
master-verify-checksum Yes Yes
- Variable: master_verify_checksum
max_allowed_packet Yes Yes Yes Global Yes
max_binlog_cache_size Yes Yes Yes Global Yes
max-binlog-dump-events Yes Yes
max_binlog_size Yes Yes Yes Global Yes
max_binlog_stmt_cache_size Yes Yes Yes Global Yes
max_connect_errors Yes Yes Yes Global Yes
max_connections Yes Yes Yes Global Yes
max_delayed_threads Yes Yes Yes Both Yes
max_error_count Yes Yes Yes Both Yes
max_heap_table_size Yes Yes Yes Both Yes
max_insert_delayed_threads Yes Both Yes
max_join_size Yes Yes Yes Both Yes
max_length_for_sort_data Yes Yes Yes Both Yes
max_prepared_stmt_count Yes Yes Yes Global Yes
max_relay_log_size Yes Yes Yes Global Yes
max_seeks_for_key Yes Yes Yes Both Yes
max_sort_length Yes Yes Yes Both Yes
max_sp_recursion_depth Yes Yes Yes Both Yes
max_tmp_tables Yes Yes Yes Both Yes
Max_used_connections Yes Global No
max_user_connections Yes Yes Yes Both Yes
max_write_lock_count Yes Yes Yes Global Yes
memlock Yes Yes Yes Global No
metadata_locks_cache_size Yes Global No
min-examined-row-limit Yes Yes Yes Both Yes
myisam-block-size Yes Yes
myisam_data_pointer_size Yes Yes Yes Global Yes
myisam_max_sort_file_size Yes Yes Yes Global Yes
myisam_mmap_size Yes Yes Yes Global No
myisam-recover-options Yes Yes
- Variable: myisam_recover_options
myisam_recover_options Yes Global No
myisam_repair_threads Yes Yes Yes Both Yes
myisam_sort_buffer_size Yes Yes Yes Both Yes
myisam_stats_method Yes Yes Yes Both Yes
myisam_use_mmap Yes Yes Yes Global Yes
named_pipe Yes Global No
Ndb_conflict_fn_max Yes Global No
Ndb_conflict_fn_old Yes Global No
Ndb_number_of_data_nodes Yes Global No
net_buffer_length Yes Yes Yes Both Yes
net_read_timeout Yes Yes Yes Both Yes
net_retry_count Yes Yes Yes Both Yes
net_write_timeout Yes Yes Yes Both Yes
new Yes Yes Yes Both Yes
no-defaults Yes
Not_flushed_delayed_rows Yes Global No
old Yes Yes Yes Global No
old-alter-table Yes Yes Both Yes
- Variable: old_alter_table Yes Both Yes
old-passwords Yes Yes Both Yes
- Variable: old_passwords Yes Both Yes
old-style-user-limits Yes Yes
one-thread Yes Yes
Open_files Yes Global No
open-files-limit Yes Yes Global No
- Variable: open_files_limit Yes Global No
Open_streams Yes Global No
Open_table_definitions Yes Global No
Open_tables Yes Both No
Opened_files Yes Global No
Opened_table_definitions Yes Both No
Opened_tables Yes Both No
optimizer_join_cache_level Yes Yes Yes Both Yes
optimizer_prune_level Yes Yes Yes Both Yes
optimizer_search_depth Yes Yes Yes Both Yes
optimizer_switch Yes Yes Yes Both Yes
optimizer_trace Yes Both Yes
optimizer_trace_features Yes Both Yes
optimizer_trace_limit Yes Both Yes
optimizer_trace_max_mem_size Yes Both Yes
optimizer_trace_offset Yes Both Yes
partition Yes Yes Global No
- Variable: have_partitioning Yes Global No
performance_schema Yes Yes Yes Global No
Performance_schema_accounts_lost Yes Global No
performance_schema_accounts_size Yes Yes Yes Global No
Performance_schema_cond_classes_lost Yes Global No
Performance_schema_cond_instances_lost Yes Global No
performance_schema_consumer_xxx Yes Yes
performance_schema_events_stages_history_long_size Yes Yes Yes Global No
performance_schema_events_stages_history_size Yes Yes Yes Global No
performance_schema_events_statements_history_long_size Yes Yes Yes Global No
performance_schema_events_statements_history_size Yes Yes Yes Global No
performance_schema_events_waits_history_long_size Yes Yes Yes Global No
performance_schema_events_waits_history_size Yes Yes Yes Global No
Performance_schema_file_classes_lost Yes Global No
Performance_schema_file_handles_lost Yes Global No
Performance_schema_file_instances_lost Yes Global No
Performance_schema_hosts_lost Yes Global No
performance_schema_hosts_size Yes Yes Yes Global No
performance_schema_instrument Yes Yes
Performance_schema_locker_lost Yes Global No
performance_schema_max_cond_classes Yes Yes Yes Global No
performance_schema_max_cond_instances Yes Yes Yes Global No
performance_schema_max_file_classes Yes Yes Yes Global No
performance_schema_max_file_handles Yes Yes Yes Global No
performance_schema_max_file_instances Yes Yes Yes Global No
performance_schema_max_mutex_classes Yes Yes Yes Global No
performance_schema_max_mutex_instances Yes Yes Yes Global No
performance_schema_max_rwlock_classes Yes Yes Yes Global No
performance_schema_max_rwlock_instances Yes Yes Yes Global No
performance_schema_max_socket_classes Yes Yes Yes Global No
performance_schema_max_socket_instances Yes Yes Yes Global No
performance_schema_max_stage_classes Yes Yes Yes Global No
performance_schema_max_statement_classes Yes Yes Yes Global No
performance_schema_max_table_handles Yes Yes Yes Global No
performance_schema_max_table_instances Yes Yes Yes Global No
performance_schema_max_thread_classes Yes Yes Yes Global No
performance_schema_max_thread_instances Yes Yes Yes Global No
Performance_schema_mutex_classes_lost Yes Global No
Performance_schema_mutex_instances_lost Yes Global No
Performance_schema_rwlock_classes_lost Yes Global No
Performance_schema_rwlock_instances_lost Yes Global No
performance_schema_setup_actors_size Yes Yes Yes Global No
performance_schema_setup_objects_size Yes Yes Yes Global No
Performance_schema_socket_classes_lost Yes Global No
Performance_schema_socket_instances_lost Yes Global No
Performance_schema_stage_classes_lost Yes Global No
Performance_schema_statement_classes_lost Yes Global No
Performance_schema_table_handles_lost Yes Global No
Performance_schema_table_instances_lost Yes Global No
Performance_schema_thread_classes_lost Yes Global No
Performance_schema_thread_instances_lost Yes Global No
Performance_schema_users_lost Yes Global No
performance_schema_users_size Yes Yes Yes Global No
pid-file Yes Yes Global No
- Variable: pid_file Yes Global No
plugin Yes Yes
plugin_dir Yes Yes Yes Global No
plugin-load Yes Yes
plugin-load-add Yes Yes
port Yes Yes Yes Global No
port-open-timeout Yes Yes
preload_buffer_size Yes Yes Yes Both Yes
Prepared_stmt_count Yes Global No
print-defaults Yes
profiling Yes Both Yes
profiling_history_size Yes Yes Yes Both Yes
protocol_version Yes Global No
proxy_user Yes Session No
pseudo_thread_id Yes Session Yes
Qcache_free_blocks Yes Global No
Qcache_free_memory Yes Global No
Qcache_hits Yes Global No
Qcache_inserts Yes Global No
Qcache_lowmem_prunes Yes Global No
Qcache_not_cached Yes Global No
Qcache_queries_in_cache Yes Global No
Qcache_total_blocks Yes Global No
Queries Yes Both No
query_alloc_block_size Yes Yes Yes Both Yes
query_cache_limit Yes Yes Yes Global Yes
query_cache_min_res_unit Yes Yes Yes Global Yes
query_cache_size Yes Yes Yes Global Yes
query_cache_type Yes Yes Yes Both Yes
query_cache_wlock_invalidate Yes Yes Yes Both Yes
query_prealloc_size Yes Yes Yes Both Yes
Questions Yes Both No
rand_seed1 Yes Session Yes
rand_seed2 Yes Session Yes
range_alloc_block_size Yes Yes Yes Both Yes
read_buffer_size Yes Yes Yes Both Yes
read_only Yes Yes Yes Global Yes
read_rnd_buffer_size Yes Yes Yes Both Yes
relay-log Yes Yes
relay_log_basename Yes Global No
relay-log-index Yes Yes Both No
- Variable: relay_log_index Yes Both No
relay_log_index Yes Yes Yes Global No
relay-log-info-file Yes Yes
- Variable: relay_log_info_file
relay_log_info_file Yes Yes Yes Global No
relay-log-info-repository Yes Yes
- Variable: relay_log_info_repository
relay_log_info_repository Yes Global No
relay_log_purge Yes Yes Yes Global Yes
relay_log_recovery Yes Yes Yes Global Yes
relay_log_space_limit Yes Yes Yes Global No
remove Yes
replicate-do-db Yes Yes
replicate-do-table Yes Yes
replicate-ignore-db Yes Yes
replicate-ignore-table Yes Yes
replicate-rewrite-db Yes Yes
replicate-same-server-id Yes Yes
replicate-wild-do-table Yes Yes
replicate-wild-ignore-table Yes Yes
report-host Yes Yes Global No
- Variable: report_host Yes Global No
report-password Yes Yes Global No
- Variable: report_password Yes Global No
report-port Yes Yes Global No
- Variable: report_port Yes Global No
report-user Yes Yes Global No
- Variable: report_user Yes Global No
Rpl_semi_sync_master_clients Yes Global No
rpl_semi_sync_master_enabled Yes Global Yes
Rpl_semi_sync_master_net_avg_wait_time Yes Global No
Rpl_semi_sync_master_net_wait_time Yes Global No
Rpl_semi_sync_master_net_waits Yes Global No
Rpl_semi_sync_master_no_times Yes Global No
Rpl_semi_sync_master_no_tx Yes Global No
Rpl_semi_sync_master_status Yes Global No
Rpl_semi_sync_master_timefunc_failures Yes Global No
rpl_semi_sync_master_timeout Yes Global Yes
rpl_semi_sync_master_trace_level Yes Global Yes
Rpl_semi_sync_master_tx_avg_wait_time Yes Global No
Rpl_semi_sync_master_tx_wait_time Yes Global No
Rpl_semi_sync_master_tx_waits Yes Global No
rpl_semi_sync_master_wait_no_slave Yes Global Yes
Rpl_semi_sync_master_wait_pos_backtraverse Yes Global No
Rpl_semi_sync_master_wait_sessions Yes Global No
Rpl_semi_sync_master_yes_tx Yes Global No
rpl_semi_sync_slave_enabled Yes Global Yes
Rpl_semi_sync_slave_status Yes Global No
rpl_semi_sync_slave_trace_level Yes Global Yes
safe-mode Yes Yes
safe-user-create Yes Yes
secure-auth Yes Yes Global Yes
- Variable: secure_auth Yes Global Yes
secure-file-priv Yes Yes Global No
- Variable: secure_file_priv Yes Global No
Select_full_join Yes Both No
Select_full_range_join Yes Both No
Select_range Yes Both No
Select_range_check Yes Both No
Select_scan Yes Both No
server-id Yes Yes Global Yes
- Variable: server_id Yes Global Yes
server_uuid Yes Global No
shared_memory Yes Global No
shared_memory_base_name Yes Global No
show-slave-auth-info Yes Yes
skip-character-set-client-handshake Yes Yes
skip-concurrent-insert Yes Yes
- Variable: concurrent_insert
skip-event-scheduler Yes Yes
skip-external-locking Yes Yes Global No
- Variable: skip_external_locking Yes Global No
skip-grant-tables Yes Yes
skip-host-cache Yes Yes
skip-log-warnings Yes
skip-name-resolve Yes Yes Global No
- Variable: skip_name_resolve Yes Global No
skip-networking Yes Yes Global No
- Variable: skip_networking Yes Global No
skip-new Yes Yes
skip-partition Yes Yes
skip-show-database Yes Yes Global No
- Variable: skip_show_database Yes Global No
skip-slave-start Yes Yes
skip-ssl Yes Yes
skip-stack-trace Yes Yes
skip-symbolic-links Yes
skip-thread-priority Yes Yes
slave_compressed_protocol Yes Yes Yes Global Yes
slave_exec_mode Yes Global Yes
Slave_heartbeat_period Yes Global No
Slave_last_heartbeat Yes Global No
slave-load-tmpdir Yes Yes Global No
- Variable: slave_load_tmpdir Yes Global No
slave-net-timeout Yes Yes Global Yes
- Variable: slave_net_timeout Yes Global Yes
Slave_open_temp_tables Yes Global No
slave_parallel_workers Yes Global Yes
slave-parallel-workers Yes Yes
- Variable: slave_parallel_workers
Slave_received_heartbeats Yes Global No
Slave_retried_transactions Yes Global No
Slave_running Yes Global No
slave-skip-errors Yes Yes Global No
- Variable: slave_skip_errors Yes Global No
slave_sql_verify_checksum Yes Global Yes
slave-sql-verify-checksum Yes Yes
slave_transaction_retries Yes Yes Yes Global Yes
slave_type_conversions Yes Yes Yes Global No
Slow_launch_threads Yes Both No
slow_launch_time Yes Yes Yes Global Yes
Slow_queries Yes Both No
slow-query-log Yes Yes Global Yes
- Variable: slow_query_log Yes Global Yes
slow_query_log_file Yes Yes Yes Global Yes
slow-start-timeout Yes Yes
socket Yes Yes Yes Global No
sort_buffer_size Yes Yes Yes Both Yes
Sort_merge_passes Yes Both No
Sort_range Yes Both No
Sort_rows Yes Both No
Sort_scan Yes Both No
sporadic-binlog-dump-fail Yes Yes
sql_auto_is_null Yes Both Yes
sql_big_selects Yes Both Yes
sql_big_tables Yes Both Yes
sql_buffer_result Yes Both Yes
sql_log_bin Yes Both Yes
sql_log_off Yes Both Yes
sql_low_priority_updates Yes Both Yes
sql_max_join_size Yes Both Yes
sql-mode Yes Yes Both Yes
- Variable: sql_mode Yes Both Yes
sql_notes Yes Both Yes
sql_quote_show_create Yes Both Yes
sql_safe_updates Yes Both Yes
sql_select_limit Yes Both Yes
sql_slave_skip_counter Yes Global Yes
sql_warnings Yes Both Yes
ssl Yes Yes
Ssl_accept_renegotiates Yes Global No
Ssl_accepts Yes Global No
ssl-ca Yes Yes Global No
- Variable: ssl_ca Yes Global No
Ssl_callback_cache_hits Yes Global No
ssl-capath Yes Yes Global No
- Variable: ssl_capath Yes Global No
ssl-cert Yes Yes Global No
- Variable: ssl_cert Yes Global No
ssl-cipher Yes Yes Global No
- Variable: ssl_cipher Yes Global No
Ssl_cipher Yes Both No
Ssl_cipher_list Yes Both No
Ssl_client_connects Yes Global No
Ssl_connect_renegotiates Yes Global No
ssl-crl Yes Yes Global No
- Variable: ssl_crl Yes Global No
ssl-crlpath Yes Yes Global No
- Variable: ssl_crlpath Yes Global No
Ssl_ctx_verify_depth Yes Global No
Ssl_ctx_verify_mode Yes Global No
Ssl_default_timeout Yes Both No
Ssl_finished_accepts Yes Global No
Ssl_finished_connects Yes Global No
ssl-key Yes Yes Global No
- Variable: ssl_key Yes Global No
Ssl_server_not_after Yes Both No
Ssl_server_not_before Yes Both No
Ssl_session_cache_hits Yes Global No
Ssl_session_cache_misses Yes Global No
Ssl_session_cache_mode Yes Global No
Ssl_session_cache_overflows Yes Global No
Ssl_session_cache_size Yes Global No
Ssl_session_cache_timeouts Yes Global No
Ssl_sessions_reused Yes Both No
Ssl_used_session_cache_entries Yes Global No
Ssl_verify_depth Yes Both No
Ssl_verify_mode Yes Both No
ssl-verify-server-cert Yes Yes
Ssl_version Yes Both No
standalone Yes Yes
storage_engine Yes Both Yes
symbolic-links Yes Yes
sync_binlog Yes Yes Yes Global Yes
sync_frm Yes Yes Yes Global Yes
sync_master_info Yes Yes Yes Global Yes
sync_relay_log Yes Yes Yes Global Yes
sync_relay_log_info Yes Yes Yes Global Yes
sysdate-is-now Yes Yes
system_time_zone Yes Global No
table_definition_cache Yes Yes Yes Global Yes
Table_locks_immediate Yes Global No
Table_locks_waited Yes Global No
table_open_cache Yes Yes Yes Global Yes
tc-heuristic-recover Yes Yes
Tc_log_max_pages_used Yes Global No
Tc_log_page_size Yes Global No
Tc_log_page_waits Yes Global No
temp-pool Yes Yes
thread_cache_size Yes Yes Yes Global Yes
thread_concurrency Yes Yes Yes Global No
thread_handling Yes Yes Yes Global No
thread_stack Yes Yes Yes Global No
Threads_cached Yes Global No
Threads_connected Yes Global No
Threads_created Yes Global No
Threads_running Yes Global No
time_format Yes Global No
time_zone Yes Yes Yes Both Yes
timed_mutexes Yes Yes Yes Global Yes
timestamp Yes Session Yes
tmp_table_size Yes Yes Yes Both Yes
tmpdir Yes Yes Yes Global No
transaction_alloc_block_size Yes Yes Yes Both Yes
transaction-isolation Yes Yes
- Variable: tx_isolation
transaction_prealloc_size Yes Yes Yes Both Yes
transaction-read-only Yes Yes
- Variable: tx_read_only
tx_isolation Yes Both Yes
tx_read_only Yes Both Yes
unique_checks Yes Both Yes
updatable_views_with_limit Yes Yes Yes Both Yes
Uptime Yes Global No
Uptime_since_flush_status Yes Global No
user Yes Yes
verbose Yes Yes
version Yes Global No
version_comment Yes Global No
version_compile_machine Yes Global No
version_compile_os Yes Global No
wait_timeout Yes Yes Yes Both Yes
warning_count Yes Session No

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

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

Server Command Options

When you start the mysqld server, you can specify program options using any of the methods described in , "Specifying Program Options". The most common methods are to provide options in an option file or on the command line. However, in most cases it is desirable to make sure that the server uses the same options each time it runs. The best way to ensure this is to list them in an option file. See , "Using Option Files".

mysqld reads options from the [mysqld] and [server] groups. mysqld_safe reads options from the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups. mysql.server reads options from the [mysqld] and [mysql.server] groups.

An embedded MariaDB server usually reads options from the [server], [embedded], and [xxxxx_SERVER] groups, where xxxxx is the name of the application into which the server is embedded.

mysqld accepts many command options. For a brief summary, execute mysqld --help. To see the full list, use mysqld --verbose --help.

The following list shows some of the most common server options. Additional options are described in other sections:

You can also set the values of server system variables by using variable names as options, as described at the end of this section.

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

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

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

You can assign a value to a server system variable by using an option of the form --var_name=value. For example, --key_buffer_size=32M sets the key_buffer_size variable to a value of 32MB.

Note that when you assign a value to a variable, MariaDB might automatically correct the value to stay within a given range, or adjust the value to the closest permissible value if only certain values are permitted.

If you want to restrict the maximum value to which a variable can be set at runtime with SET, you can define this by using the --maximum-var_name=value command-line option.

You can change the values of most system variables for a running server with the SET statement. See , "SET Syntax".

, "Server System Variables", provides a full description for all variables, and additional information for setting them at server startup and runtime. , "Tuning Server Parameters", includes information on optimizing the server by tuning system variables.

Server System Variables

The MariaDB server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions.

There are several ways to see the names and values of system variables:

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

The following table lists all available system variables:

Table 5.2. System Variable Summary

Name Cmd-Line Option file System Var Var Scope Dynamic
auto_increment_increment Yes Yes Yes Both Yes
auto_increment_offset Yes Yes Yes Both Yes
autocommit Yes Yes Yes Both Yes
automatic_sp_privileges Yes Global Yes
back_log Yes Yes Yes Global No
basedir Yes Yes Yes Global No
big-tables Yes Yes Yes
- Variable: big_tables Yes Both Yes
bind-address Yes Yes Yes Global No
binlog_cache_size Yes Yes Yes Global Yes
binlog_checksum Yes Global Yes
binlog_direct_non_transactional_updates Yes Yes Yes Both Yes
binlog-format Yes Yes Yes
- Variable: binlog_format Yes Both Yes
binlog_row_image Yes Yes Both Yes
binlog_stmt_cache_size Yes Yes Yes Global Yes
bulk_insert_buffer_size Yes Yes Yes Both Yes
character_set_client Yes Both Yes
character_set_connection Yes Both Yes
character_set_database[a] Yes Both Yes
character-set-filesystem Yes Yes Yes
- Variable: character_set_filesystem Yes Both Yes
character_set_results Yes Both Yes
character-set-server Yes Yes Yes
- Variable: character_set_server Yes Both Yes
character_set_system Yes Global No
character-sets-dir Yes Yes No
- Variable: character_sets_dir Yes Global No
collation_connection Yes Both Yes
collation_database[b] Yes Both Yes
collation-server Yes Yes Yes
- Variable: collation_server Yes Both Yes
completion_type Yes Yes Yes Both Yes
concurrent_insert Yes Yes Yes Global Yes
connect_timeout Yes Yes Yes Global Yes
datadir Yes Yes Yes Global No
date_format Yes Global No
datetime_format Yes Global No
debug Yes Yes Yes Both Yes
debug_sync Yes Session Yes
default-storage-engine Yes Yes Yes
- Variable: default_storage_engine Yes Both Yes
default_tmp_storage_engine Yes Yes Yes Both Yes
default_week_format Yes Yes Yes Both Yes
delay-key-write Yes Yes Yes
- Variable: delay_key_write Yes Global Yes
delayed_insert_limit Yes Yes Yes Global Yes
delayed_insert_timeout Yes Yes Yes Global Yes
delayed_queue_size Yes Yes Yes Global Yes
div_precision_increment Yes Yes Yes Both Yes
end_markers_in_json Yes Both Yes
engine-condition-pushdown Yes Yes Yes
- Variable: engine_condition_pushdown Yes Both Yes
eq_range_index_dive_limit Yes Yes Yes Both Yes
error_count Yes Session No
event-scheduler Yes Yes Yes
- Variable: event_scheduler Yes Global Yes
expire_logs_days Yes Yes Yes Global Yes
external_user Yes Session No
flush Yes Yes Yes Global Yes
flush_time Yes Yes Yes Global Yes
foreign_key_checks Yes Both Yes
ft_boolean_syntax Yes Yes Yes Global Yes
ft_max_word_len Yes Yes Yes Global No
ft_min_word_len Yes Yes Yes Global No
ft_query_expansion_limit Yes Yes Yes Global No
ft_stopword_file Yes Yes Yes Global No
general-log Yes Yes Yes
- Variable: general_log Yes Global Yes
general_log_file Yes Yes Yes Global Yes
group_concat_max_len Yes Yes Yes Both Yes
have_compress Yes Global No
have_crypt Yes Global No
have_csv Yes Global No
have_dynamic_loading Yes Global No
have_geometry Yes Global No
have_innodb Yes Global No
have_ndbcluster Yes Global No
have_openssl Yes Global No
have_partitioning Yes Global No
have_profiling Yes Global No
have_query_cache Yes Global No
have_rtree_keys Yes Global No
have_ssl Yes Global No
have_symlink Yes Global No
host_cache_size Yes Yes Yes Global Yes
hostname Yes Global No
identity Yes Session Yes
ignore-builtin-innodb Yes Yes No
- Variable: ignore_builtin_innodb Yes Global No
init_connect Yes Yes Yes Global Yes
init-file Yes Yes No
- Variable: init_file Yes Global No
init_slave Yes Yes Yes Global Yes
innodb_adaptive_flushing Yes Yes Yes Global Yes
innodb_adaptive_hash_index Yes Yes Yes Global Yes
innodb_adaptive_max_sleep_delay Yes Yes Yes Global Yes
innodb_additional_mem_pool_size Yes Yes Yes Global No
innodb_analyze_is_persistent Yes Yes Yes Global Yes
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_autoinc_lock_mode Yes Yes Yes Global No
innodb_buffer_pool_dump_at_shutdown Yes Yes Yes Global Yes
innodb_buffer_pool_dump_now Yes Yes Yes Global Yes
innodb_buffer_pool_filename Yes Yes Yes Global Yes
innodb_buffer_pool_instances Yes Yes Yes Global No
innodb_buffer_pool_load_abort Yes Yes Yes Global Yes
innodb_buffer_pool_load_at_startup Yes Yes Yes Global Yes
innodb_buffer_pool_load_now Yes Yes Yes Global Yes
innodb_buffer_pool_size Yes Yes Yes Global No
innodb_change_buffer_max_size Yes Yes Yes Global Yes
innodb_change_buffering Yes Yes Yes Global Yes
innodb_checksum_algorithm Yes Yes Yes Global Yes
innodb_checksums Yes Yes Yes Global No
innodb_commit_concurrency Yes Yes Yes Global Yes
innodb_concurrency_tickets Yes Yes Yes Global Yes
innodb_data_file_path Yes Yes Yes Global No
innodb_data_home_dir Yes Yes Yes Global No
innodb_doublewrite Yes Yes Yes Global No
innodb_fast_shutdown Yes Yes Yes Global Yes
innodb_file_format Yes Yes Yes Global Yes
innodb_file_format_check Yes Yes Yes Global No
innodb_file_format_max Yes Yes Yes Global Yes
innodb_file_per_table Yes Yes Yes Global Yes
innodb_flush_log_at_trx_commit Yes Yes Yes Global Yes
innodb_flush_method Yes Yes Yes Global No
innodb_flush_neighbors Yes Yes Yes Global Yes
innodb_force_load_corrupted Yes Yes Yes Global No
innodb_force_recovery Yes Yes Yes Global No
innodb_ft_aux_table Yes Yes Yes Global Yes
innodb_ft_cache_size Yes Yes Yes Global Yes
innodb_ft_enable_stopword Yes Yes Yes Global Yes
innodb_ft_max_token_size Yes Yes Yes Global Yes
innodb_ft_min_token_size Yes Yes Yes Global Yes
innodb_ft_num_word_optimize Yes Yes Yes Global Yes
innodb_ft_server_stopword_table Yes Yes Yes Global Yes
innodb_ft_sort_pll_degree Yes Yes Yes Global Yes
innodb_ft_user_stopword_table Yes Yes Yes Global Yes
innodb_io_capacity Yes Yes Yes Global Yes
innodb_large_prefix Yes Yes Yes Global Yes
innodb_lock_wait_timeout Yes Yes Yes Both Yes
innodb_locks_unsafe_for_binlog Yes Yes Yes Global No
innodb_log_buffer_size Yes Yes Yes Global No
innodb_log_file_size Yes Yes Yes Global No
innodb_log_files_in_group Yes Yes Yes Global No
innodb_log_group_home_dir Yes Yes Yes Global No
innodb_lru_scan_depth Yes Yes Yes Global Yes
innodb_max_dirty_pages_pct Yes Yes Yes Global Yes
innodb_max_purge_lag Yes Yes Yes Global Yes
innodb_mirrored_log_groups Yes Yes Yes Global No
innodb_monitor_disable Yes Yes Yes Global Yes
innodb_monitor_enable Yes Yes Yes Global Yes
innodb_monitor_reset Yes Yes Yes Global Yes
innodb_monitor_reset_all Yes Yes Yes Global Yes
innodb_old_blocks_pct Yes Yes Yes Global Yes
innodb_old_blocks_time Yes Yes Yes Global Yes
innodb_open_files Yes Yes Yes Global No
innodb_optimize_fulltext_only Yes Yes Yes Global Yes
innodb_page_size Yes Yes Yes Global No
innodb_print_all_deadlocks Yes Yes Yes Global Yes
innodb_purge_batch_size Yes Yes Yes Global No
innodb_purge_threads Yes Yes Yes Global No
innodb_random_read_ahead Yes Yes Yes Global Yes
innodb_read_ahead_threshold Yes Yes Yes Global Yes
innodb_read_io_threads Yes Yes Yes Global No
innodb_replication_delay Yes Yes Yes Global Yes
innodb_rollback_on_timeout Yes Yes Yes Global No
innodb_rollback_segments Yes Yes Yes Global Yes
innodb_sort_buffer_size Yes Yes Yes Global Yes
innodb_spin_wait_delay Yes Yes Yes Global Yes
innodb_stats_method Yes Yes Yes Both Yes
innodb_stats_on_metadata Yes Yes Yes Global Yes
innodb_stats_persistent_sample_pages Yes Yes Yes Global Yes
innodb_stats_sample_pages Yes Yes Yes Global Yes
innodb_stats_transient_sample_pages Yes Yes Yes Global Yes
innodb_strict_mode Yes Yes Yes Both Yes
innodb_support_xa Yes Yes Yes Both Yes
innodb_sync_array_size Yes Yes Yes Global No
innodb_sync_spin_loops Yes Yes Yes Global Yes
innodb_table_locks Yes Yes Yes Both Yes
innodb_thread_concurrency Yes Yes Yes Global Yes
innodb_thread_sleep_delay Yes Yes Yes Global Yes
innodb_undo_directory Yes Yes Yes Global Yes
innodb_undo_logs Yes Yes Yes Global Yes
innodb_undo_tablespaces Yes Yes Yes Global Yes
innodb_use_native_aio Yes Yes Yes Global No
innodb_use_sys_malloc Yes Yes Yes Global No
innodb_version Yes Global No
innodb_write_io_threads Yes Yes Yes Global No
insert_id Yes Session Yes
interactive_timeout Yes Yes Yes Both Yes
join_buffer_size Yes Yes Yes Both Yes
keep_files_on_create Yes Yes Yes Both Yes
key_buffer_size Yes Yes Yes Global Yes
key_cache_age_threshold Yes Yes Yes Global Yes
key_cache_block_size Yes Yes Yes Global Yes
key_cache_division_limit Yes Yes Yes Global Yes
language Yes Yes Yes Global No
large_files_support Yes Global No
large_page_size Yes Global No
large-pages Yes Yes No
- Variable: large_pages Yes Global No
last_insert_id Yes Session Yes
lc-messages Yes Yes Yes
- Variable: lc_messages Yes Both Yes
lc-messages-dir Yes Yes No
- Variable: lc_messages_dir Yes Global No
lc_time_names Yes Both Yes
license Yes Global No
local_infile Yes Global Yes
lock_wait_timeout Yes Yes Yes Both Yes
locked_in_memory Yes Global No
log Yes Yes Yes Global Yes
log_bin Yes Global No
log-bin Yes Yes Yes Global No
log_bin_basename Yes Global No
log-error Yes Yes No
- Variable: log_error Yes Global No
log-output Yes Yes Yes
- Variable: log_output Yes Global Yes
log-queries-not-using-indexes Yes Yes Yes
- Variable: log_queries_not_using_indexes Yes Global Yes
log-slave-updates Yes Yes No
- Variable: log_slave_updates Yes Global No
log-slow-queries Yes Yes Yes
- Variable: log_slow_queries Yes Global Yes
log_throttle_queries_not_using_indexes Yes Global Yes
log-warnings Yes Yes Yes
- Variable: log_warnings Yes Global Yes
long_query_time Yes Yes Yes Both Yes
low-priority-updates Yes Yes Yes
- Variable: low_priority_updates Yes Both Yes
lower_case_file_system Yes Yes Yes Global No
lower_case_table_names Yes Yes Yes Global No
master_info_repository Yes Global No
master_verify_checksum Yes Global Yes
max_allowed_packet Yes Yes Yes Global Yes
max_binlog_cache_size Yes Yes Yes Global Yes
max_binlog_size Yes Yes Yes Global Yes
max_binlog_stmt_cache_size Yes Yes Yes Global Yes
max_connect_errors Yes Yes Yes Global Yes
max_connections Yes Yes Yes Global Yes
max_delayed_threads Yes Yes Yes Both Yes
max_error_count Yes Yes Yes Both Yes
max_heap_table_size Yes Yes Yes Both Yes
max_insert_delayed_threads Yes Both Yes
max_join_size Yes Yes Yes Both Yes
max_length_for_sort_data Yes Yes Yes Both Yes
max_prepared_stmt_count Yes Yes Yes Global Yes
max_relay_log_size Yes Yes Yes Global Yes
max_seeks_for_key Yes Yes Yes Both Yes
max_sort_length Yes Yes Yes Both Yes
max_sp_recursion_depth Yes Yes Yes Both Yes
max_tmp_tables Yes Yes Yes Both Yes
max_user_connections Yes Yes Yes Both Yes
max_write_lock_count Yes Yes Yes Global Yes
memlock Yes Yes Yes Global No
metadata_locks_cache_size Yes Global No
min-examined-row-limit Yes Yes Yes Both Yes
myisam_data_pointer_size Yes Yes Yes Global Yes
myisam_max_sort_file_size Yes Yes Yes Global Yes
myisam_mmap_size Yes Yes Yes Global No
myisam_recover_options Yes Global No
myisam_repair_threads Yes Yes Yes Both Yes
myisam_sort_buffer_size Yes Yes Yes Both Yes
myisam_stats_method Yes Yes Yes Both Yes
myisam_use_mmap Yes Yes Yes Global Yes
named_pipe Yes Global No
net_buffer_length Yes Yes Yes Both Yes
net_read_timeout Yes Yes Yes Both Yes
net_retry_count Yes Yes Yes Both Yes
net_write_timeout Yes Yes Yes Both Yes
new Yes Yes Yes Both Yes
old Yes Yes Yes Global No
old-alter-table Yes Yes Yes
- Variable: old_alter_table Yes Both Yes
old-passwords Yes Yes Yes
- Variable: old_passwords Yes Both Yes
open-files-limit Yes Yes No
- Variable: open_files_limit Yes Global No
optimizer_join_cache_level Yes Yes Yes Both Yes
optimizer_prune_level Yes Yes Yes Both Yes
optimizer_search_depth Yes Yes Yes Both Yes
optimizer_switch Yes Yes Yes Both Yes
optimizer_trace Yes Both Yes
optimizer_trace_features Yes Both Yes
optimizer_trace_limit Yes Both Yes
optimizer_trace_max_mem_size Yes Both Yes
optimizer_trace_offset Yes Both Yes
partition Yes Yes No
- Variable: have_partitioning Yes Global No
performance_schema Yes Yes Yes Global No
performance_schema_accounts_size Yes Yes Yes Global No
performance_schema_events_stages_history_long_size Yes Yes Yes Global No
performance_schema_events_stages_history_size Yes Yes Yes Global No
performance_schema_events_statements_history_long_size Yes Yes Yes Global No
performance_schema_events_statements_history_size Yes Yes Yes Global No
performance_schema_events_waits_history_long_size Yes Yes Yes Global No
performance_schema_events_waits_history_size Yes Yes Yes Global No
performance_schema_hosts_size Yes Yes Yes Global No
performance_schema_max_cond_classes Yes Yes Yes Global No
performance_schema_max_cond_instances Yes Yes Yes Global No
performance_schema_max_file_classes Yes Yes Yes Global No
performance_schema_max_file_handles Yes Yes Yes Global No
performance_schema_max_file_instances Yes Yes Yes Global No
performance_schema_max_mutex_classes Yes Yes Yes Global No
performance_schema_max_mutex_instances Yes Yes Yes Global No
performance_schema_max_rwlock_classes Yes Yes Yes Global No
performance_schema_max_rwlock_instances Yes Yes Yes Global No
performance_schema_max_socket_classes Yes Yes Yes Global No
performance_schema_max_socket_instances Yes Yes Yes Global No
performance_schema_max_stage_classes Yes Yes Yes Global No
performance_schema_max_statement_classes Yes Yes Yes Global No
performance_schema_max_table_handles Yes Yes Yes Global No
performance_schema_max_table_instances Yes Yes Yes Global No
performance_schema_max_thread_classes Yes Yes Yes Global No
performance_schema_max_thread_instances Yes Yes Yes Global No
performance_schema_setup_actors_size Yes Yes Yes Global No
performance_schema_setup_objects_size Yes Yes Yes Global No
performance_schema_users_size Yes Yes Yes Global No
pid-file Yes Yes No
- Variable: pid_file Yes Global No
plugin_dir Yes Yes Yes Global No
port Yes Yes Yes Global No
preload_buffer_size Yes Yes Yes Both Yes
profiling Yes Both Yes
profiling_history_size Yes Yes Yes Both Yes
protocol_version Yes Global No
proxy_user Yes Session No
pseudo_thread_id Yes Session Yes
query_alloc_block_size Yes Yes Yes Both Yes
query_cache_limit Yes Yes Yes Global Yes
query_cache_min_res_unit Yes Yes Yes Global Yes
query_cache_size Yes Yes Yes Global Yes
query_cache_type Yes Yes Yes Both Yes
query_cache_wlock_invalidate Yes Yes Yes Both Yes
query_prealloc_size Yes Yes Yes Both Yes
rand_seed1 Yes Session Yes
rand_seed2 Yes Session Yes
range_alloc_block_size Yes Yes Yes Both Yes
read_buffer_size Yes Yes Yes Both Yes
read_only Yes Yes Yes Global Yes
read_rnd_buffer_size Yes Yes Yes Both Yes
relay_log_basename Yes Global No
relay-log-index Yes Yes No
- Variable: relay_log_index Yes Both No
relay_log_index Yes Yes Yes Global No
relay_log_info_file Yes Yes Yes Global No
relay_log_info_repository Yes Global No
relay_log_purge Yes Yes Yes Global Yes
relay_log_recovery Yes Yes Yes Global Yes
relay_log_space_limit Yes Yes Yes Global No
report-host Yes Yes No
- Variable: report_host Yes Global No
report-password Yes Yes No
- Variable: report_password Yes Global No
report-port Yes Yes No
- Variable: report_port Yes Global No
report-user Yes Yes No
- Variable: report_user Yes Global No
rpl_semi_sync_master_enabled Yes Global Yes
rpl_semi_sync_master_timeout Yes Global Yes
rpl_semi_sync_master_trace_level Yes Global Yes
rpl_semi_sync_master_wait_no_slave Yes Global Yes
rpl_semi_sync_slave_enabled Yes Global Yes
rpl_semi_sync_slave_trace_level Yes Global Yes
secure-auth Yes Yes Yes
- Variable: secure_auth Yes Global Yes
secure-file-priv Yes Yes No
- Variable: secure_file_priv Yes Global No
server-id Yes Yes Yes
- Variable: server_id Yes Global Yes
server_uuid Yes Global No
shared_memory Yes Global No
shared_memory_base_name Yes Global No
skip-external-locking Yes Yes No
- Variable: skip_external_locking Yes Global No
skip-name-resolve Yes Yes No
- Variable: skip_name_resolve Yes Global No
skip-networking Yes Yes No
- Variable: skip_networking Yes Global No
skip-show-database Yes Yes No
- Variable: skip_show_database Yes Global No
slave_compressed_protocol Yes Yes Yes Global Yes
slave_exec_mode Yes Global Yes
slave-load-tmpdir Yes Yes No
- Variable: slave_load_tmpdir Yes Global No
slave-net-timeout Yes Yes Yes
- Variable: slave_net_timeout Yes Global Yes
slave_parallel_workers Yes Global Yes
slave-skip-errors Yes Yes No
- Variable: slave_skip_errors Yes Global No
slave_sql_verify_checksum Yes Global Yes
slave_transaction_retries Yes Yes Yes Global Yes
slave_type_conversions Yes Yes Yes Global No
slow_launch_time Yes Yes Yes Global Yes
slow-query-log Yes Yes Yes
- Variable: slow_query_log Yes Global Yes
slow_query_log_file Yes Yes Yes Global Yes
socket Yes Yes Yes Global No
sort_buffer_size Yes Yes Yes Both Yes
sql_auto_is_null Yes Both Yes
sql_big_selects Yes Both Yes
sql_big_tables Yes Both Yes
sql_buffer_result Yes Both Yes
sql_log_bin Yes Both Yes
sql_log_off Yes Both Yes
sql_low_priority_updates Yes Both Yes
sql_max_join_size Yes Both Yes
sql-mode Yes Yes Yes
- Variable: sql_mode Yes Both Yes
sql_notes Yes Both Yes
sql_quote_show_create Yes Both Yes
sql_safe_updates Yes Both Yes
sql_select_limit Yes Both Yes
sql_slave_skip_counter Yes Global Yes
sql_warnings Yes Both Yes
ssl-ca Yes Yes No
- Variable: ssl_ca Yes Global No
ssl-capath Yes Yes No
- Variable: ssl_capath Yes Global No
ssl-cert Yes Yes No
- Variable: ssl_cert Yes Global No
ssl-cipher Yes Yes No
- Variable: ssl_cipher Yes Global No
ssl-crl Yes Yes No
- Variable: ssl_crl Yes Global No
ssl-crlpath Yes Yes No
- Variable: ssl_crlpath Yes Global No
ssl-key Yes Yes No
- Variable: ssl_key Yes Global No
storage_engine Yes Both Yes
sync_binlog Yes Yes Yes Global Yes
sync_frm Yes Yes Yes Global Yes
sync_master_info Yes Yes Yes Global Yes
sync_relay_log Yes Yes Yes Global Yes
sync_relay_log_info Yes Yes Yes Global Yes
system_time_zone Yes Global No
table_definition_cache Yes Yes Yes Global Yes
table_open_cache Yes Yes Yes Global Yes
thread_cache_size Yes Yes Yes Global Yes
thread_concurrency Yes Yes Yes Global No
thread_handling Yes Yes Yes Global No
thread_stack Yes Yes Yes Global No
time_format Yes Global No
time_zone Yes Yes Yes Both Yes
timed_mutexes Yes Yes Yes Global Yes
timestamp Yes Session Yes
tmp_table_size Yes Yes Yes Both Yes
tmpdir Yes Yes Yes Global No
transaction_alloc_block_size Yes Yes Yes Both Yes
transaction_prealloc_size Yes Yes Yes Both Yes
tx_isolation Yes Both Yes
tx_read_only Yes Both Yes
unique_checks Yes Both Yes
updatable_views_with_limit Yes Yes Yes Both Yes
version Yes Global No
version_comment Yes Global No
version_compile_machine Yes Global No
version_compile_os Yes Global No
wait_timeout Yes Yes Yes Both Yes
warning_count Yes Session No

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

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

For additional system variable information, see these sections:

Note

Some of the following variable descriptions refer to "enabling" or "disabling" a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, before MariaDB 5.6.2, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not. As of MariaDB 5.6.2, boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case sensitive). See , "Program Option Modifiers".

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

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

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

Using System Variables

Structured System Variables
Dynamic System Variables

The MariaDB server maintains many system variables that indicate how it is configured. , "Server System Variables", describes the meaning of these variables. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions.

The server maintains two kinds of system variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value. Global and session system variables are related as follows:

System variable values can be set globally at server startup by using options on the command line or in an option file. When you use a startup option to set a variable that takes a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243; that is, units of kilobytes, megabytes, or gigabytes, respectively. Thus, the following command starts the server with a query cache size of 16 megabytes and a maximum packet size of one gigabyte:

mysqld --query_cache_size=16M --max_allowed_packet=1G

Within an option file, those variables are set like this:

[mysqld]
query_cache_size=16M max_allowed_packet=1G

The lettercase of suffix letters does not matter; 16M and 16m are equivalent, as are 1G and 1g.

If you want to restrict the maximum value to which a system variable can be set at runtime with the SET statement, you can specify this maximum by using an option of the form --maximum-var_name=value at server startup. For example, to prevent the value of query_cache_size from being increased to more than 32MB at runtime, use the option --maximum-query_cache_size=32M.

Many system variables are dynamic and can be changed while the server runs by using the SET statement. For a list, see , "Dynamic System Variables". To change a system variable with SET, refer to it as var_name, optionally preceded by a modifier:

A SET statement can contain multiple variable assignments, separated by commas. If you set several system variables, the most recent GLOBAL or SESSION modifier in the statement is used for following variables that have no modifier specified.

Examples:

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

The @@var_name syntax for system variables is supported for compatibility with some other database systems.

If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.

If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

To prevent incorrect usage, MariaDB produces an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you do not specify GLOBAL (or @@global.) when setting a global variable.

To set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MariaDB default value, use the DEFAULT keyword. For example, the following two statements are identical in setting the session value of max_join_size to the global value:

SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

Not all system variables can be set to DEFAULT. In such cases, use of DEFAULT results in an error.

You can refer to the values of specific global or session system variables in expressions by using one of the @@-modifiers. For example, you can retrieve values in a SELECT statement like this:

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

When you refer to a system variable in an expression as @@var_name (that is, when you do not specify @@global. or @@session.), MariaDB returns the session value if it exists and the global value otherwise. (This differs from SET @@var_name = value, which always refers to the session value.)Note

Some variables displayed by SHOW VARIABLES may not be available using SELECT @@var_name syntax; an Unknown system variable occurs. As a workaround in such cases, you can use SHOW VARIABLES LIKE 'var_name'.

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not:

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024

Conversely, the second of the following lines is legal at runtime, but the first is not:

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
Note

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

To display system variable names and values, use the SHOW VARIABLES statement:

mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /home/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /home/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
...
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
...
| version | 5.1.6-alpha-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | suse-linux |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------------+

With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the "%" wildcard character in a LIKE clause:

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because "_" is a wildcard that matches any single character, you should escape it as "\_" to match it literally. In practice, this is rarely necessary.

For SHOW VARIABLES, if you specify neither GLOBAL nor SESSION, MariaDB returns SESSION values.

The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future. If we were to remove a SESSION variable that has the same name as a GLOBAL variable, a client with the SUPER privilege might accidentally change the GLOBAL variable rather than just the SESSION variable for its own connection. If we add a SESSION variable with the same name as a GLOBAL variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed.

Structured System Variables

A structured variable differs from a regular system variable in two respects:

MySQL 5.6 supports one structured variable type, which specifies parameters governing the operation of key caches. A key cache structured variable has these components:

This section describes the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in , "The MyISAM Key Cache".

To refer to a component of a structured variable instance, you can use a compound name in instance_name.component_name format. Examples:

hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size

For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus, default.key_buffer_size and key_buffer_size both refer to the same system variable.

Structured variable instances and components follow these naming rules:

Currently, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future.

With one exception, you can refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:

shell> mysqld --hot_cache.key_buffer_size=64K

In an option file, use this syntax:

[mysqld]
hot_cache.key_buffer_size=64K

If you start the server with this option, it creates a key cache named hot_cache with a size of 64KB in addition to the default key cache that has a default size of 8MB.

Suppose that you start the server as follows:

shell> mysqld --key_buffer_size=256K \
 --extra_cache.key_buffer_size=128K \
 --extra_cache.key_cache_block_size=2048

In this case, the server sets the size of the default key cache to 256KB. (You could also have written --default.key_buffer_size=256K.) In addition, the server creates a second key cache named extra_cache that has a size of 128KB, with the size of block buffers for caching table index blocks set to 2048 bytes.

The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:

shell> mysqld --key_buffer_size=6M \
 --hot_cache.key_buffer_size=2M \
 --cold_cache.key_buffer_size=2M

Structured variable values may be set and retrieved at runtime as well. For example, to set a key cache named hot_cache to a size of 10MB, use either of these statements:

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;
mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

To retrieve the cache size, do this:

mysql> SELECT @@global.hot_cache.key_buffer_size;

However, the following statement does not work. The variable is not interpreted as a compound name, but as a simple string for a LIKE pattern-matching operation:

mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';

This is the exception to being able to use structured variable names anywhere a simple variable name may occur.

Dynamic System Variables

Many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION. You can also obtain their values using SELECT. See , "Using System Variables".

The following table shows the full list of all dynamic system variables. The last column indicates for each variable whether GLOBAL or SESSION (or both) apply. The table also lists session options that can be set with the SET statement. , "Server System Variables", discusses these options.

Variables that have a type of "string" take a string value. Variables that have a type of "numeric" take a numeric value. Variables that have a type of "boolean" can be set to 0, 1, ON or OFF. (If you set them on the command line or in an option file, use the numeric values.) Variables that are marked as "enumeration" normally should be set to one of the available values for the variable, but can also be set to the number that corresponds to the desired enumeration value. For enumerated system variables, the first enumeration value corresponds to 0. This differs from ENUM columns, for which the first enumeration value corresponds to 1.

Table 5.3. Dynamic Variable Summary

Variable Name Variable Type Variable Scope
auto_increment_increment numeric GLOBAL | SESSION
auto_increment_offset numeric GLOBAL | SESSION
autocommit boolean GLOBAL | SESSION
automatic_sp_privileges boolean GLOBAL
big_tables boolean GLOBAL | SESSION
binlog_cache_size numeric GLOBAL
binlog_checksum string GLOBAL
binlog_direct_non_transactional_updates boolean GLOBAL | SESSION
binlog_format enumeration GLOBAL | SESSION
binlog_row_image=image_type enumeration GLOBAL | SESSION
binlog_rows_query_log_events boolean GLOBAL | SESSION
binlog_stmt_cache_size numeric GLOBAL
bulk_insert_buffer_size numeric GLOBAL | SESSION
character_set_client string GLOBAL | SESSION
character_set_connection string GLOBAL | SESSION
character_set_database string GLOBAL | SESSION
character_set_filesystem string GLOBAL | SESSION
character_set_results string GLOBAL | SESSION
character_set_server string GLOBAL | SESSION
collation_connection string GLOBAL | SESSION
collation_database string GLOBAL | SESSION
collation_server string GLOBAL | SESSION
completion_type numeric GLOBAL | SESSION
concurrent_insert boolean GLOBAL
connect_timeout numeric GLOBAL
debug string GLOBAL | SESSION
debug_sync string SESSION
default_storage_engine enumeration GLOBAL | SESSION
default_tmp_storage_engine enumeration GLOBAL | SESSION
default_week_format numeric GLOBAL | SESSION
delay_key_write enumeration GLOBAL
delayed_insert_limit numeric GLOBAL
delayed_insert_timeout numeric GLOBAL
delayed_queue_size numeric GLOBAL
div_precision_increment numeric GLOBAL | SESSION
end_markers_in_json boolean GLOBAL | SESSION
engine_condition_pushdown boolean GLOBAL | SESSION
eq_range_index_dive_limit numeric GLOBAL | SESSION
event_scheduler enumeration GLOBAL
expire_logs_days numeric GLOBAL
flush boolean GLOBAL
flush_time numeric GLOBAL
foreign_key_checks boolean GLOBAL | SESSION
ft_boolean_syntax string GLOBAL
general_log boolean GLOBAL
general_log_file filename GLOBAL
group_concat_max_len numeric GLOBAL | SESSION
host_cache_size numeric GLOBAL
identity numeric SESSION
init_connect string GLOBAL
init_slave string GLOBAL
innodb_adaptive_flushing boolean GLOBAL
innodb_adaptive_hash_index boolean GLOBAL
innodb_adaptive_max_sleep_delay numeric GLOBAL
innodb_analyze_is_persistent boolean GLOBAL
innodb_autoextend_increment numeric GLOBAL
innodb_buffer_pool_dump_at_shutdown boolean GLOBAL
innodb_buffer_pool_dump_now boolean GLOBAL
innodb_buffer_pool_filename boolean GLOBAL
innodb_buffer_pool_load_abort boolean GLOBAL
innodb_buffer_pool_load_at_startup boolean GLOBAL
innodb_buffer_pool_load_now boolean GLOBAL
innodb_change_buffer_max_size numeric GLOBAL
innodb_change_buffering enumeration GLOBAL
innodb_checksum_algorithm enumeration GLOBAL
innodb_commit_concurrency numeric GLOBAL
innodb_concurrency_tickets numeric GLOBAL
innodb_fast_shutdown numeric GLOBAL
innodb_file_format string GLOBAL
innodb_file_format_max string GLOBAL
innodb_file_per_table boolean GLOBAL
innodb_flush_log_at_trx_commit enumeration GLOBAL
innodb_flush_neighbors boolean GLOBAL
innodb_ft_aux_table string GLOBAL
innodb_ft_cache_size numeric GLOBAL
innodb_ft_enable_stopword boolean GLOBAL
innodb_ft_max_token_size numeric GLOBAL
innodb_ft_min_token_size numeric GLOBAL
innodb_ft_num_word_optimize numeric GLOBAL
innodb_ft_server_stopword_table string GLOBAL
innodb_ft_sort_pll_degree boolean GLOBAL
innodb_ft_user_stopword_table string GLOBAL
innodb_io_capacity numeric GLOBAL
innodb_large_prefix boolean GLOBAL
innodb_lock_wait_timeout numeric GLOBAL | SESSION
innodb_lru_scan_depth numeric GLOBAL
innodb_max_dirty_pages_pct numeric GLOBAL
innodb_max_purge_lag numeric GLOBAL
innodb_monitor_disable string GLOBAL
innodb_monitor_enable string GLOBAL
innodb_monitor_reset string GLOBAL
innodb_monitor_reset_all string GLOBAL
innodb_old_blocks_pct numeric GLOBAL
innodb_old_blocks_time numeric GLOBAL
innodb_optimize_fulltext_only boolean GLOBAL
innodb_print_all_deadlocks boolean GLOBAL
innodb_random_read_ahead boolean GLOBAL
innodb_read_ahead_threshold numeric GLOBAL
innodb_replication_delay numeric GLOBAL
innodb_rollback_segments numeric GLOBAL
innodb_sort_buffer_size numeric GLOBAL
innodb_spin_wait_delay numeric GLOBAL
innodb_stats_method enumeration GLOBAL | SESSION
innodb_stats_on_metadata boolean GLOBAL
innodb_stats_persistent_sample_pages numeric GLOBAL
innodb_stats_sample_pages numeric GLOBAL
innodb_stats_transient_sample_pages numeric GLOBAL
innodb_strict_mode boolean GLOBAL | SESSION
innodb_support_xa boolean GLOBAL | SESSION
innodb_sync_spin_loops numeric GLOBAL
innodb_table_locks boolean GLOBAL | SESSION
innodb_thread_concurrency numeric GLOBAL
innodb_thread_sleep_delay numeric GLOBAL
innodb_undo_directory string GLOBAL
innodb_undo_logs numeric GLOBAL
innodb_undo_tablespaces numeric GLOBAL
insert_id numeric SESSION
interactive_timeout numeric GLOBAL | SESSION
join_buffer_size numeric GLOBAL | SESSION
keep_files_on_create boolean GLOBAL | SESSION
key_buffer_size numeric GLOBAL
key_cache_age_threshold numeric GLOBAL
key_cache_block_size numeric GLOBAL
key_cache_division_limit numeric GLOBAL
last_insert_id numeric SESSION
lc_messages string GLOBAL | SESSION
lc_time_names string GLOBAL | SESSION
local_infile boolean GLOBAL
lock_wait_timeout numeric GLOBAL | SESSION
log string GLOBAL
log_output set GLOBAL
log_queries_not_using_indexes boolean GLOBAL
log_slow_queries boolean GLOBAL
log_throttle_queries_not_using_indexes numeric GLOBAL
log_warnings numeric GLOBAL
long_query_time numeric GLOBAL | SESSION
low_priority_updates boolean GLOBAL | SESSION
master_verify_checksum boolean GLOBAL
max_allowed_packet numeric GLOBAL
max_binlog_cache_size numeric GLOBAL
max_binlog_size numeric GLOBAL
max_binlog_stmt_cache_size numeric GLOBAL
max_connect_errors numeric GLOBAL
max_connections numeric GLOBAL
max_delayed_threads numeric GLOBAL | SESSION
max_error_count numeric GLOBAL | SESSION
max_heap_table_size numeric GLOBAL | SESSION
max_insert_delayed_threads numeric GLOBAL | SESSION
max_join_size numeric GLOBAL | SESSION
max_length_for_sort_data numeric GLOBAL | SESSION
max_prepared_stmt_count numeric GLOBAL
max_relay_log_size numeric GLOBAL
max_seeks_for_key numeric GLOBAL | SESSION
max_sort_length numeric GLOBAL | SESSION
max_sp_recursion_depth numeric GLOBAL | SESSION
max_tmp_tables numeric GLOBAL | SESSION
max_user_connections numeric GLOBAL | SESSION
max_write_lock_count numeric GLOBAL
min_examined_row_limit numeric GLOBAL | SESSION
myisam_data_pointer_size numeric GLOBAL
myisam_max_sort_file_size numeric GLOBAL
myisam_repair_threads numeric GLOBAL | SESSION
myisam_sort_buffer_size numeric GLOBAL | SESSION
myisam_stats_method enumeration GLOBAL | SESSION
myisam_use_mmap boolean GLOBAL
net_buffer_length numeric GLOBAL | SESSION
net_read_timeout numeric GLOBAL | SESSION
net_retry_count numeric GLOBAL | SESSION
net_write_timeout numeric GLOBAL | SESSION
new boolean GLOBAL | SESSION
old_alter_table boolean GLOBAL | SESSION
old_passwords boolean GLOBAL | SESSION
optimizer_join_cache_level numeric GLOBAL | SESSION
optimizer_prune_level boolean GLOBAL | SESSION
optimizer_search_depth numeric GLOBAL | SESSION
optimizer_switch set GLOBAL | SESSION
optimizer_trace string GLOBAL | SESSION
optimizer_trace_features string GLOBAL | SESSION
optimizer_trace_limit numeric GLOBAL | SESSION
optimizer_trace_max_mem_size numeric GLOBAL | SESSION
optimizer_trace_offset numeric GLOBAL | SESSION
preload_buffer_size numeric GLOBAL | SESSION
profiling boolean GLOBAL | SESSION
profiling_history_size numeric GLOBAL | SESSION
pseudo_thread_id numeric SESSION
query_alloc_block_size numeric GLOBAL | SESSION
query_cache_limit numeric GLOBAL
query_cache_min_res_unit numeric GLOBAL
query_cache_size numeric GLOBAL
query_cache_type enumeration GLOBAL | SESSION
query_cache_wlock_invalidate boolean GLOBAL | SESSION
query_prealloc_size numeric GLOBAL | SESSION
rand_seed1 numeric SESSION
rand_seed2 numeric SESSION
range_alloc_block_size numeric GLOBAL | SESSION
read_buffer_size numeric GLOBAL | SESSION
read_only numeric GLOBAL
read_rnd_buffer_size numeric GLOBAL | SESSION
relay_log_purge boolean GLOBAL
relay_log_recovery boolean GLOBAL
rpl_semi_sync_master_enabled boolean GLOBAL
rpl_semi_sync_master_timeout numeric GLOBAL
rpl_semi_sync_master_trace_level numeric GLOBAL
rpl_semi_sync_master_wait_no_slave boolean GLOBAL
rpl_semi_sync_slave_enabled boolean GLOBAL
rpl_semi_sync_slave_trace_level numeric GLOBAL
secure_auth boolean GLOBAL
server_id numeric GLOBAL
slave_compressed_protocol boolean GLOBAL
slave_exec_mode enumeration GLOBAL
slave_net_timeout numeric GLOBAL
slave_parallel_workers numeric GLOBAL
slave_sql_verify_checksum boolean GLOBAL
slave_transaction_retries numeric GLOBAL
slow_launch_time numeric GLOBAL
slow_query_log boolean GLOBAL
slow_query_log_file filename GLOBAL
sort_buffer_size numeric GLOBAL | SESSION
sql_auto_is_null boolean GLOBAL | SESSION
sql_big_selects boolean GLOBAL | SESSION
sql_big_tables boolean GLOBAL | SESSION
sql_buffer_result boolean GLOBAL | SESSION
sql_log_bin boolean GLOBAL | SESSION
sql_log_off boolean GLOBAL | SESSION
sql_low_priority_updates boolean GLOBAL | SESSION
sql_max_join_size numeric GLOBAL | SESSION
sql_mode set GLOBAL | SESSION
sql_notes boolean GLOBAL | SESSION
sql_quote_show_create boolean GLOBAL | SESSION
sql_safe_updates boolean GLOBAL | SESSION
sql_select_limit numeric GLOBAL | SESSION
sql_slave_skip_counter numeric GLOBAL
sql_warnings boolean GLOBAL | SESSION
storage_engine enumeration GLOBAL | SESSION
sync_binlog numeric GLOBAL
sync_frm boolean GLOBAL
sync_master_info numeric GLOBAL
sync_relay_log numeric GLOBAL
sync_relay_log_info numeric GLOBAL
table_definition_cache numeric GLOBAL
table_open_cache numeric GLOBAL
thread_cache_size numeric GLOBAL
time_zone string GLOBAL | SESSION
timed_mutexes boolean GLOBAL
timestamp numeric SESSION
tmp_table_size numeric GLOBAL | SESSION
transaction_alloc_block_size numeric GLOBAL | SESSION
transaction_prealloc_size numeric GLOBAL | SESSION
tx_isolation enumeration GLOBAL | SESSION
tx_read_only boolean GLOBAL | SESSION
unique_checks boolean GLOBAL | SESSION
updatable_views_with_limit boolean GLOBAL | SESSION
wait_timeout numeric GLOBAL | SESSION

Server Status Variables

The server maintains many status variables that provide information about its operation. You can view these variables and their values by using the SHOW [GLOBAL | SESSION] STATUS statement (see , "SHOW STATUS Syntax"). The optional GLOBAL keyword aggregates the values over all connections, and SESSION shows the values for the current connection.

mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
...
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 2 |
...
| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+-----------------------------------+------------+

Many status variables are reset to 0 by the FLUSH STATUS statement.

The following table lists all available server status variables:

Table 5.4. Status Variable Summary

Variable Name Variable Type Variable Scope
Aborted_clients numeric GLOBAL
Aborted_connects numeric GLOBAL
Binlog_cache_disk_use numeric GLOBAL
Binlog_cache_use numeric GLOBAL
binlog_rows_query_log_events boolean GLOBAL | SESSION
Binlog_stmt_cache_disk_use numeric GLOBAL
Binlog_stmt_cache_use numeric GLOBAL
Bytes_received numeric GLOBAL | SESSION
Bytes_sent numeric GLOBAL | SESSION
Com_admin_commands numeric GLOBAL | SESSION
Com_alter_db numeric GLOBAL | SESSION
Com_alter_db_upgrade numeric GLOBAL | SESSION
Com_alter_event numeric GLOBAL | SESSION
Com_alter_function numeric GLOBAL | SESSION
Com_alter_procedure numeric GLOBAL | SESSION
Com_alter_server numeric GLOBAL | SESSION
Com_alter_table numeric GLOBAL | SESSION
Com_alter_tablespace numeric GLOBAL | SESSION
Com_analyze numeric GLOBAL | SESSION
Com_assign_to_keycache numeric GLOBAL | SESSION
Com_begin numeric GLOBAL | SESSION
Com_binlog numeric GLOBAL | SESSION
Com_call_procedure numeric GLOBAL | SESSION
Com_change_db numeric GLOBAL | SESSION
Com_change_master numeric GLOBAL | SESSION
Com_check numeric GLOBAL | SESSION
Com_checksum numeric GLOBAL | SESSION
Com_commit numeric GLOBAL | SESSION
Com_create_db numeric GLOBAL | SESSION
Com_create_event numeric GLOBAL | SESSION
Com_create_function numeric GLOBAL | SESSION
Com_create_index numeric GLOBAL | SESSION
Com_create_procedure numeric GLOBAL | SESSION
Com_create_server numeric GLOBAL | SESSION
Com_create_table numeric GLOBAL | SESSION
Com_create_trigger numeric GLOBAL | SESSION
Com_create_udf numeric GLOBAL | SESSION
Com_create_user numeric GLOBAL | SESSION
Com_create_view numeric GLOBAL | SESSION
Com_dealloc_sql numeric GLOBAL | SESSION
Com_delete numeric GLOBAL | SESSION
Com_delete_multi numeric GLOBAL | SESSION
Com_do numeric GLOBAL | SESSION
Com_drop_db numeric GLOBAL | SESSION
Com_drop_event numeric GLOBAL | SESSION
Com_drop_function numeric GLOBAL | SESSION
Com_drop_index numeric GLOBAL | SESSION
Com_drop_procedure numeric GLOBAL | SESSION
Com_drop_server numeric GLOBAL | SESSION
Com_drop_table numeric GLOBAL | SESSION
Com_drop_trigger numeric GLOBAL | SESSION
Com_drop_user numeric GLOBAL | SESSION
Com_drop_view numeric GLOBAL | SESSION
Com_empty_query numeric GLOBAL | SESSION
Com_execute_sql numeric GLOBAL | SESSION
Com_flush numeric GLOBAL | SESSION
Com_grant numeric GLOBAL | SESSION
Com_ha_close numeric GLOBAL | SESSION
Com_ha_open numeric GLOBAL | SESSION
Com_ha_read numeric GLOBAL | SESSION
Com_help numeric GLOBAL | SESSION
Com_insert numeric GLOBAL | SESSION
Com_insert_select numeric GLOBAL | SESSION
Com_install_plugin numeric GLOBAL | SESSION
Com_kill numeric GLOBAL | SESSION
Com_load numeric GLOBAL | SESSION
Com_lock_tables numeric GLOBAL | SESSION
Com_optimize numeric GLOBAL | SESSION
Com_preload_keys numeric GLOBAL | SESSION
Com_prepare_sql numeric GLOBAL | SESSION
Com_purge numeric GLOBAL | SESSION
Com_purge_before_date numeric GLOBAL | SESSION
Com_release_savepoint numeric GLOBAL | SESSION
Com_rename_table numeric GLOBAL | SESSION
Com_rename_user numeric GLOBAL | SESSION
Com_repair numeric GLOBAL | SESSION
Com_replace numeric GLOBAL | SESSION
Com_replace_select numeric GLOBAL | SESSION
Com_reset numeric GLOBAL | SESSION
Com_resignal numeric GLOBAL | SESSION
Com_revoke numeric GLOBAL | SESSION
Com_revoke_all numeric GLOBAL | SESSION
Com_rollback numeric GLOBAL | SESSION
Com_rollback_to_savepoint numeric GLOBAL | SESSION
Com_savepoint numeric GLOBAL | SESSION
Com_select numeric GLOBAL | SESSION
Com_set_option numeric GLOBAL | SESSION
Com_show_authors numeric GLOBAL | SESSION
Com_show_binlog_events numeric GLOBAL | SESSION
Com_show_binlogs numeric GLOBAL | SESSION
Com_show_charsets numeric GLOBAL | SESSION
Com_show_collations numeric GLOBAL | SESSION
Com_show_contributors numeric GLOBAL | SESSION
Com_show_create_db numeric GLOBAL | SESSION
Com_show_create_event numeric GLOBAL | SESSION
Com_show_create_func numeric GLOBAL | SESSION
Com_show_create_proc numeric GLOBAL | SESSION
Com_show_create_table numeric GLOBAL | SESSION
Com_show_create_trigger numeric GLOBAL | SESSION
Com_show_databases numeric GLOBAL | SESSION
Com_show_engine_logs numeric GLOBAL | SESSION
Com_show_engine_mutex numeric GLOBAL | SESSION
Com_show_engine_status numeric GLOBAL | SESSION
Com_show_errors numeric GLOBAL | SESSION
Com_show_events numeric GLOBAL | SESSION
Com_show_fields numeric GLOBAL | SESSION
Com_show_function_code numeric GLOBAL | SESSION
Com_show_function_status numeric GLOBAL | SESSION
Com_show_grants numeric GLOBAL | SESSION
Com_show_keys numeric GLOBAL | SESSION
Com_show_logs numeric GLOBAL | SESSION
Com_show_master_status numeric GLOBAL | SESSION
Com_show_new_master numeric GLOBAL | SESSION
Com_show_open_tables numeric GLOBAL | SESSION
Com_show_plugins numeric GLOBAL | SESSION
Com_show_privileges numeric GLOBAL | SESSION
Com_show_procedure_code numeric GLOBAL | SESSION
Com_show_procedure_status numeric GLOBAL | SESSION
Com_show_processlist numeric GLOBAL | SESSION
Com_show_profile numeric GLOBAL | SESSION
Com_show_profiles numeric GLOBAL | SESSION
Com_show_relaylog_events numeric GLOBAL | SESSION
Com_show_slave_hosts numeric GLOBAL | SESSION
Com_show_slave_status numeric GLOBAL | SESSION
Com_show_status numeric GLOBAL | SESSION
Com_show_storage_engines numeric GLOBAL | SESSION
Com_show_table_status numeric GLOBAL | SESSION
Com_show_tables numeric GLOBAL | SESSION
Com_show_triggers numeric GLOBAL | SESSION
Com_show_variables numeric GLOBAL | SESSION
Com_show_warnings numeric GLOBAL | SESSION
Com_signal numeric GLOBAL | SESSION
Com_slave_start numeric GLOBAL | SESSION
Com_slave_stop numeric GLOBAL | SESSION
Com_stmt_close numeric GLOBAL | SESSION
Com_stmt_execute numeric GLOBAL | SESSION
Com_stmt_fetch numeric GLOBAL | SESSION
Com_stmt_prepare numeric GLOBAL | SESSION
Com_stmt_reprepare numeric GLOBAL | SESSION
Com_stmt_reset numeric GLOBAL | SESSION
Com_stmt_send_long_data numeric GLOBAL | SESSION
Com_truncate numeric GLOBAL | SESSION
Com_uninstall_plugin numeric GLOBAL | SESSION
Com_unlock_tables numeric GLOBAL | SESSION
Com_update numeric GLOBAL | SESSION
Com_update_multi numeric GLOBAL | SESSION
Com_xa_commit numeric GLOBAL | SESSION
Com_xa_end numeric GLOBAL | SESSION
Com_xa_prepare numeric GLOBAL | SESSION
Com_xa_recover numeric GLOBAL | SESSION
Com_xa_rollback numeric GLOBAL | SESSION
Com_xa_start numeric GLOBAL | SESSION
Compression numeric SESSION
Connection_errors_accept numeric GLOBAL
Connection_errors_internal numeric GLOBAL
Connection_errors_max_connections numeric GLOBAL
Connection_errors_peer_addr numeric GLOBAL
Connection_errors_select numeric GLOBAL
Connection_errors_tcpwrap numeric GLOBAL
Connections numeric GLOBAL
Created_tmp_disk_tables numeric GLOBAL | SESSION
Created_tmp_files numeric GLOBAL
Created_tmp_tables numeric GLOBAL | SESSION
Delayed_errors numeric GLOBAL
Delayed_insert_threads numeric GLOBAL
Delayed_writes numeric GLOBAL
Flush_commands numeric GLOBAL
Handler_commit numeric GLOBAL | SESSION
Handler_delete numeric GLOBAL | SESSION
Handler_discover numeric GLOBAL | SESSION
Handler_external_lock numeric GLOBAL | SESSION
Handler_prepare numeric GLOBAL | SESSION
Handler_read_first numeric GLOBAL | SESSION
Handler_read_key numeric GLOBAL | SESSION
Handler_read_last numeric GLOBAL | SESSION
Handler_read_next numeric GLOBAL | SESSION
Handler_read_prev numeric GLOBAL | SESSION
Handler_read_rnd numeric GLOBAL | SESSION
Handler_read_rnd_next numeric GLOBAL | SESSION
Handler_rollback numeric GLOBAL | SESSION
Handler_savepoint numeric GLOBAL | SESSION
Handler_savepoint_rollback numeric GLOBAL | SESSION
Handler_update numeric GLOBAL | SESSION
Handler_write numeric GLOBAL | SESSION
Innodb_buffer_pool_dump_status numeric GLOBAL
Innodb_buffer_pool_load_status numeric GLOBAL
Innodb_buffer_pool_pages_data numeric GLOBAL
Innodb_buffer_pool_pages_dirty numeric GLOBAL
Innodb_buffer_pool_pages_flushed numeric GLOBAL
Innodb_buffer_pool_pages_free numeric GLOBAL
Innodb_buffer_pool_pages_latched numeric GLOBAL
Innodb_buffer_pool_pages_misc numeric GLOBAL
Innodb_buffer_pool_pages_total numeric GLOBAL
Innodb_buffer_pool_read_ahead numeric GLOBAL
Innodb_buffer_pool_read_ahead_evicted numeric GLOBAL
Innodb_buffer_pool_read_requests numeric GLOBAL
Innodb_buffer_pool_reads numeric GLOBAL
Innodb_buffer_pool_wait_free numeric GLOBAL
Innodb_buffer_pool_write_requests numeric GLOBAL
Innodb_data_fsyncs numeric GLOBAL
Innodb_data_pending_fsyncs numeric GLOBAL
Innodb_data_pending_reads numeric GLOBAL
Innodb_data_pending_writes numeric GLOBAL
Innodb_data_read numeric GLOBAL
Innodb_data_reads numeric GLOBAL
Innodb_data_writes numeric GLOBAL
Innodb_data_written numeric GLOBAL
Innodb_dblwr_pages_written numeric GLOBAL
Innodb_dblwr_writes numeric GLOBAL
Innodb_have_atomic_builtins numeric GLOBAL
Innodb_log_waits numeric GLOBAL
Innodb_log_write_requests numeric GLOBAL
Innodb_log_writes numeric GLOBAL
Innodb_num_open_files numeric GLOBAL
Innodb_os_log_fsyncs numeric GLOBAL
Innodb_os_log_pending_fsyncs numeric GLOBAL
Innodb_os_log_pending_writes numeric GLOBAL
Innodb_os_log_written numeric GLOBAL
Innodb_page_size numeric GLOBAL
Innodb_pages_created numeric GLOBAL
Innodb_pages_read numeric GLOBAL
Innodb_pages_written numeric GLOBAL
Innodb_row_lock_current_waits numeric GLOBAL
Innodb_row_lock_time numeric GLOBAL
Innodb_row_lock_time_avg numeric GLOBAL
Innodb_row_lock_time_max numeric GLOBAL
Innodb_row_lock_waits numeric GLOBAL
Innodb_rows_deleted numeric GLOBAL
Innodb_rows_inserted numeric GLOBAL
Innodb_rows_read numeric GLOBAL
Innodb_rows_updated numeric GLOBAL
Innodb_truncated_status_writes numeric GLOBAL
Key_blocks_not_flushed numeric GLOBAL
Key_blocks_unused numeric GLOBAL
Key_blocks_used numeric GLOBAL
Key_read_requests numeric GLOBAL
Key_reads numeric GLOBAL
Key_write_requests numeric GLOBAL
Key_writes numeric GLOBAL
Last_query_cost numeric SESSION
Last_query_partial_plans numeric SESSION
Max_used_connections numeric GLOBAL
Ndb_conflict_fn_max numeric GLOBAL
Ndb_conflict_fn_old numeric GLOBAL
Ndb_number_of_data_nodes numeric GLOBAL
Not_flushed_delayed_rows numeric GLOBAL
Open_files numeric GLOBAL
Open_streams numeric GLOBAL
Open_table_definitions numeric GLOBAL
Open_tables numeric GLOBAL | SESSION
Opened_files numeric GLOBAL
Opened_table_definitions numeric GLOBAL | SESSION
Opened_tables numeric GLOBAL | SESSION
Performance_schema_accounts_lost numeric GLOBAL
Performance_schema_cond_classes_lost numeric GLOBAL
Performance_schema_cond_instances_lost numeric GLOBAL
Performance_schema_file_classes_lost numeric GLOBAL
Performance_schema_file_handles_lost numeric GLOBAL
Performance_schema_file_instances_lost numeric GLOBAL
Performance_schema_hosts_lost numeric GLOBAL
Performance_schema_locker_lost numeric GLOBAL
Performance_schema_mutex_classes_lost numeric GLOBAL
Performance_schema_mutex_instances_lost numeric GLOBAL
Performance_schema_rwlock_classes_lost numeric GLOBAL
Performance_schema_rwlock_instances_lost numeric GLOBAL
Performance_schema_socket_classes_lost numeric GLOBAL
Performance_schema_socket_instances_lost numeric GLOBAL
Performance_schema_stage_classes_lost numeric GLOBAL
Performance_schema_statement_classes_lost numeric GLOBAL
Performance_schema_table_handles_lost numeric GLOBAL
Performance_schema_table_instances_lost numeric GLOBAL
Performance_schema_thread_classes_lost numeric GLOBAL
Performance_schema_thread_instances_lost numeric GLOBAL
Performance_schema_users_lost numeric GLOBAL
Prepared_stmt_count numeric GLOBAL
Qcache_free_blocks numeric GLOBAL
Qcache_free_memory numeric GLOBAL
Qcache_hits numeric GLOBAL
Qcache_inserts numeric GLOBAL
Qcache_lowmem_prunes numeric GLOBAL
Qcache_not_cached numeric GLOBAL
Qcache_queries_in_cache numeric GLOBAL
Qcache_total_blocks numeric GLOBAL
Queries numeric GLOBAL | SESSION
Questions numeric GLOBAL | SESSION
Rpl_semi_sync_master_clients numeric GLOBAL
Rpl_semi_sync_master_net_avg_wait_time numeric GLOBAL
Rpl_semi_sync_master_net_wait_time numeric GLOBAL
Rpl_semi_sync_master_net_waits numeric GLOBAL
Rpl_semi_sync_master_no_times numeric GLOBAL
Rpl_semi_sync_master_no_tx numeric GLOBAL
Rpl_semi_sync_master_status boolean GLOBAL
Rpl_semi_sync_master_timefunc_failures numeric GLOBAL
Rpl_semi_sync_master_tx_avg_wait_time numeric GLOBAL
Rpl_semi_sync_master_tx_wait_time numeric GLOBAL
Rpl_semi_sync_master_tx_waits numeric GLOBAL
Rpl_semi_sync_master_wait_pos_backtraverse numeric GLOBAL
Rpl_semi_sync_master_wait_sessions numeric GLOBAL
Rpl_semi_sync_master_yes_tx numeric GLOBAL
Rpl_semi_sync_slave_status boolean GLOBAL
Select_full_join numeric GLOBAL | SESSION
Select_full_range_join numeric GLOBAL | SESSION
Select_range numeric GLOBAL | SESSION
Select_range_check numeric GLOBAL | SESSION
Select_scan numeric GLOBAL | SESSION
Slave_heartbeat_period GLOBAL
Slave_last_heartbeat GLOBAL
Slave_open_temp_tables numeric GLOBAL
Slave_received_heartbeats GLOBAL
Slave_retried_transactions numeric GLOBAL
Slave_running boolean GLOBAL
Slow_launch_threads numeric GLOBAL | SESSION
Slow_queries numeric GLOBAL | SESSION
Sort_merge_passes numeric GLOBAL | SESSION
Sort_range numeric GLOBAL | SESSION
Sort_rows numeric GLOBAL | SESSION
Sort_scan numeric GLOBAL | SESSION
Ssl_accept_renegotiates numeric GLOBAL
Ssl_accepts numeric GLOBAL
Ssl_callback_cache_hits numeric GLOBAL
Ssl_cipher string GLOBAL | SESSION
Ssl_cipher_list string GLOBAL | SESSION
Ssl_client_connects numeric GLOBAL
Ssl_connect_renegotiates numeric GLOBAL
Ssl_ctx_verify_depth numeric GLOBAL
Ssl_ctx_verify_mode numeric GLOBAL
Ssl_default_timeout numeric GLOBAL | SESSION
Ssl_finished_accepts numeric GLOBAL
Ssl_finished_connects numeric GLOBAL
Ssl_server_not_after numeric GLOBAL | SESSION
Ssl_server_not_before numeric GLOBAL | SESSION
Ssl_session_cache_hits numeric GLOBAL
Ssl_session_cache_misses numeric GLOBAL
Ssl_session_cache_mode string GLOBAL
Ssl_session_cache_overflows numeric GLOBAL
Ssl_session_cache_size numeric GLOBAL
Ssl_session_cache_timeouts numeric GLOBAL
Ssl_sessions_reused numeric GLOBAL | SESSION
Ssl_used_session_cache_entries numeric GLOBAL
Ssl_verify_depth numeric GLOBAL | SESSION
Ssl_verify_mode numeric GLOBAL | SESSION
Ssl_version string GLOBAL | SESSION
Table_locks_immediate numeric GLOBAL
Table_locks_waited numeric GLOBAL
Tc_log_max_pages_used numeric GLOBAL
Tc_log_page_size numeric GLOBAL
Tc_log_page_waits numeric GLOBAL
Threads_cached numeric GLOBAL
Threads_connected numeric GLOBAL
Threads_created numeric GLOBAL
Threads_running numeric GLOBAL
Uptime numeric GLOBAL
Uptime_since_flush_status numeric GLOBAL

The status variables have the following meanings.

Server SQL Modes

The MariaDB server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements.

For answers to some questions that are often asked about server SQL modes in MySQL, see "MySQL 5.6 FAQ: Server SQL Mode".

Modes define what SQL syntax MariaDB should support and what kind of data validation checks it should perform. This makes it easier to use MariaDB in different environments and to use MariaDB together with other database servers.

When working with InnoDB tables, consider also the innodb_strict_mode configuration option. It enables additional error checks for InnoDB tables, as listed in , "InnoDB Strict Mode".

Setting the SQL Mode

You can set the default SQL mode by starting mysqld with the --sql-mode='modes' option, or by using sql-mode='modes' in my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by comma (",") characters. The default value is empty (no modes set). The modes value also can be empty (--sql-mode='' on the command line, or sql-mode='' in my.cnf on Unix systems or in my.ini on Windows) if you want to clear it explicitly.

You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system value. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.Important

SQL mode and user-defined partitioning. Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data. It is strongly recommended that you never change the SQL mode once you have created tables employing user-defined partitioning.

When replicating partitioned tables, differing SQL modes on master and slave can also lead to problems. For best results, you should always use the same server SQL mode on the master and on the slave.

See , "Restrictions and Limitations on Partitioning", for more information.

You can retrieve the current global or session sql_mode value with the following statements:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

Most Important SQL Modes

The most important sql_mode values are probably these:

When this manual refers to "strict mode," it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.

Full List of SQL Modes

The following list describes all supported modes:

Strict mode controls how MariaDB handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

For nontransactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero month or day parts such as '2004-04-00' or "zero" dates. To disallow these as well, enable the NO-ZERO-IN-DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

If you are not using strict mode (that is, neither STRICT-TRANS-TABLES nor STRICT_ALL_TABLES is enabled), MariaDB inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See , "SHOW WARNINGS Syntax".

Strict mode does not affect whether foreign key constraints are checked. foreign_key_checks can be used for that. (See , "Server System Variables".)

Combination Modes

The following special modes are provided as shorthand for combinations of mode values from the preceding list.

The descriptions include all mode values that are available in the most recent version of MySQL. For older versions, a combination mode does not include individual mode values that are not available except in newer versions.

Server Plugins

Installing and Uninstalling Plugins
Obtaining Server Plugin Information

MySQL supports a plugin API that enables creation of server components. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server. The components supported by this interface include, but are not limited to, storage engines, full-text parser plugins, partitioning support, and server extensions.

Installing and Uninstalling Plugins

Server plugins must be loaded in to the server before they can be used. MariaDB enables you to load a plugin at server startup or at runtime. It is also possible to control the activation of loaded plugins at startup, and to unload them at runtime.

Installing Plugins

Server plugins must be known to the server before they can be used. A plugin can be made known several ways, as described here. In the following descriptions, plugin_name stands for a plugin name such as innodb or csv.

Built-in plugins:

A plugin that is built in to the server is known by the server automatically. Normally, the server enables the plugin at startup, although this can be changed with the --plugin_name option.

Plugins registered in the mysql.plugin table:

The mysql.plugin table serves as a registry of plugins. The server normally enables each plugin listed in the table at startup, although whether a given plugin is enabled can be changed with the --plugin_name option. If the server is started with the --skip-grant-tables option, it does not consult this table and does not load the plugins listed there.

Plugins named with command-line options:

A plugin that is located in a plugin library file can be loaded at server startup with the --plugin-load option. Normally, the server enables the plugin at startup, although this can be changed with the --plugin_name option.

The option value is a semicolon-separated list of name=plugin_library pairs. Each name is the name of the plugin, and plugin_library is the name of the shared library that contains the plugin code. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. Each library file must be located in the directory named by the plugin_dir system variable.

This option does not register any plugin in the mysql.plugin table. For subsequent restarts, the server loads the plugin again only if --plugin-load is given again. That is, this option effects a one-time installation that persists only for one server invocation.

--plugin-load enables plugins to be loaded even when --skip-grant-tables is given (which causes the server to ignore the mysql.plugin table). --plugin-load also enables plugins to be loaded at startup under configurations when plugins cannot be loaded at runtime.

The --plugin-load-add option complements the --plugin-load option. --plugin-load-add adds a plugin or plugins to the set of plugins to be loaded at startup. The argument format is the same as for --plugin-load. --plugin-load-add can be used to avoid specifying a large set of plugins as a single long unwieldy --plugin-load. argument. --plugin-load-add can be given in the absence of --plugin-load, but any instance of --plugin-load-add that appears before --plugin-load. has no effect because --plugin-load resets the set of plugins to load. In other words, these options:

--plugin-load=x --plugin-load-add=y

are equivalent to this option:

--plugin-load=x;y

But these options:

--plugin-load-add=y --plugin-load=x

are equivalent to this option:

--plugin-load=x

Plugins installed with the INSTALL PLUGIN statement:

A plugin that is located in a plugin library file can be loaded at runtime with the INSTALL PLUGIN statement. The statement also registers the plugin in the mysql.plugin table to cause the server to load it on subsequent restarts. For this reason, INSTALL PLUGIN requires the INSERT privilege for the mysql.plugin table.

If a plugin is named both using a --plugin-load option and in the mysql.plugin table, the server starts but writes these messages to the error log:

100310 19:15:44 [ERROR] Function 'plugin_name' already exists
100310 19:15:44 [Warning] Couldn't load plugin named 'plugin_name'
with soname 'plugin_object_file'.

Example: The --plugin-load option installs a plugin at server startup. To install a plugin named myplugin in a plugin library file named somepluglib.so, use these lines in a my.cnf file:

[mysqld]
plugin-load=myplugin=somepluglib.so

In this case, the plugin is not registered in mysql.plugin. Restarting the server without the --plugin-load option causes the plugin not to be loaded at startup.

Alternatively, the INSTALL PLUGIN statement causes the server to load the plugin code from the library file at runtime:

mysql> INSTALL PLUGIN myplugin SONAME 'somepluglib.so';

INSTALL PLUGIN also causes "permanent" plugin registration: The server lists the plugin in the mysql.plugin table to ensure that it is loaded on subsequent server restarts.

Many plugins can be loaded either at server startup or at runtime. However, if a plugin is designed such that it must be loaded and initialized during server startup, use --plugin-load rather than INSTALL PLUGIN.

While a plugin is loaded, information about it is available at runtime from several sources, such as the INFORMATION_SCHEMA.PLUGINS table and the SHOW PLUGINS statement. For more information, see , "Obtaining Server Plugin Information".

Controlling Plugin Activation

If the server knows about a plugin when it starts (for example, because the plugin is named using a --plugin-load option or registered in the mysql.plugin table), the server loads and enables the plugin by default. It is possible to control activation for such a plugin using a --plugin_name[=value] startup option named after the plugin. In the following descriptions, plugin_name stands for a plugin name such as innodb or csv. As with other options, dashes and underscores are interchangeable in option names. For example, --my_plugin=ON and --my-plugin=ON are equivalent.

The values OFF, ON, FORCE, and FORCE_PLUS_PERMANENT are not case sensitive.

The activation state for plugins is visible in the LOAD_OPTION column of the INFORMATION_SCHEMA.PLUGINS table.

Suppose that CSV, BLACKHOLE, and ARCHIVE are built-in pluggable storage engines and that you want the server to load them at startup, subject to these conditions: The server is permitted to run if CSV initialization fails, but must require that BLACKHOLE initialization succeeds, and ARCHIVE should be disabled. To accomplish that, use these lines in an option file:

[mysqld]
csv=ON blackhole=FORCE archive=OFF

The --enable-plugin_name option format is supported as a synonym for --plugin_name=ON. The --disable-plugin_name and --skip-plugin_name option formats are supported as synonyms for --plugin_name=OFF.

If a plugin is disabled, either explicitly with OFF or implicitly because it was enabled with ON but failed to initialize, aspects of server operation that require the plugin will change. For example, if the plugin implements a storage engine, existing tables for the storage engine become inaccessible, and attempts to create new tables for the storage engine result in tables that use the default storage engine unless the NO_ENGINE_SUBSTITUTION SQL mode has been enabled to cause an error to occur instead.

Disabling a plugin may require adjustment to other options. For example, if you start the server using --skip-innodb to disable InnoDB, other innodb_xxx options likely will need to be omitted from the startup command. In addition, because InnoDB is the default storage engine, it will not start unless you specify another available storage engine with --default_storage_engine. As of MariaDB 5.6.3, you will also need to set --default_tmp_storage_engine.

Uninstalling Plugins

A plugin known to the server can be uninstalled to disable it at runtime with the UNINSTALL PLUGIN statement. The statement unloads the plugin and removes it from the mysql.plugin table if it is registered there. For this reason, UNINSTALL PLUGIN statement requires the DELETE privilege for the mysql.plugin table. With the plugin no longer registered in the table, the server will not load the plugin automatically for subsequent restarts.

UNINSTALL PLUGIN can unload plugins regardless of whether they were loaded with INSTALL PLUGIN or --plugin-load.

UNINSTALL PLUGIN is subject to these exceptions:

Obtaining Server Plugin Information

There are several ways to determine which plugins are installed in the server:

Server-Side Help

MySQL Server supports a HELP statement that returns online information from the MariaDB Reference manual (see , "HELP Syntax"). The proper operation of this statement requires that the help tables in the MariaDB database be initialized with help topic information, which is done by processing the contents of the fill_help_tables.sql script.

If you install MariaDB using a binary or source distribution on Unix, help table setup occurs when you run mysql_install_db. For an RPM distribution on Linux or binary distribution on Windows, help table setup occurs as part of the MariaDB installation process.

If you upgrade MariaDB using a binary distribution, the help tables are not upgraded automatically, but you can upgrade them manually. Locate the fill_help_tables.sql file in the share or share/mysql directory. Change location into that directory and process the file with the mysql client as follows:

shell> mysql -u root mysql < fill_help_tables.sql

You can also obtain the latest fill_help_tables.sql at any time to upgrade your help tables. Download the proper file for your version of MariaDB from http://dev.mysql.com/doc/index-other.html. After downloading and uncompressing the file, process it with mysql as described previously.

If you are working with Bazaar and a MariaDB development source tree, you will need to download the fill_help_tables.sql file because the tree contains only a "stub" version.

Server Response to Signals

On Unix, signals can be sent to processes. mysqld responds to signals sent to it as follows:

On some Mac OS X 10.3 versions, mysqld ignores SIGHUP and SIGQUIT.

The Shutdown Process

The server shutdown process takes place as follows:

  1. The shutdown process is initiated.

    This can occur initiated several ways. For example, a user with the SHUTDOWN privilege can execute a mysqladmin shutdown command. mysqladmin can be used on any platform supported by MySQL. Other operating system-specific shutdown initiation methods are possible as well: The server shuts down on Unix when it receives a SIGTERM signal. A server running as a service on Windows shuts down when the services manager tells it to.

  2. The server creates a shutdown thread if necessary.

    Depending on how shutdown was initiated, the server might create a thread to handle the shutdown process. If shutdown was requested by a client, a shutdown thread is created. If shutdown is the result of receiving a SIGTERM signal, the signal thread might handle shutdown itself, or it might create a separate thread to do so. If the server tries to create a shutdown thread and cannot (for example, if memory is exhausted), it issues a diagnostic message that appears in the error log:

    Error: Can't create thread to kill server
    
  3. The server stops accepting new connections.

    To prevent new activity from being initiated during shutdown, the server stops accepting new client connections by closing the handlers for the network interfaces to which it normally listens for connections: the TCP/IP port, the Unix socket file, the Windows named pipe, and shared memory on Windows.

  4. The server terminates current activity.

    For each thread associated with a client connection, the server breaks the connection to the client and marks the thread as killed. Threads die when they notice that they are so marked. Threads for idle connections die quickly. Threads that currently are processing statements check their state periodically and take longer to die. For additional information about thread termination, see , "KILL Syntax", in particular for the instructions about killed REPAIR TABLE or OPTIMIZE TABLE operations on MyISAM tables.

    For threads that have an open transaction, the transaction is rolled back. Note that if a thread is updating a nontransactional table, an operation such as a multiple-row UPDATE or INSERT may leave the table partially updated because the operation can terminate before completion.

    If the server is a master replication server, it treats threads associated with currently connected slaves like other client threads. That is, each one is marked as killed and exits when it next checks its state.

    If the server is a slave replication server, it stops the I/O and SQL threads, if they are active, before marking client threads as killed. The SQL thread is permitted to finish its current statement (to avoid causing replication problems), and then stops. If the SQL thread was in the middle of a transaction at this point, the transaction is rolled back. Since non-transactional statements cannot be rolled back, in order to guarantee crash-safe replication, only transactional tables should be used.

    See also , "Replication Relay and Status Logs").

  5. The server shuts down or closes storage engines.

    At this stage, the server flushes the table cache and closes all open tables.

    Each storage engine performs any actions necessary for tables that it manages. InnoDB flushes its buffer pool to disk (unless innodb_fast_shutdown is 2), writes the current LSN to the tablespace, and terminates its own internal threads. MyISAM flushes any pending index writes for a table.

  6. The server exits.

MySQL Server Logs

Selecting General Query and Slow Query Log Output Destinations
The Error Log
The General Query Log
The Binary Log
The Slow Query Log
Server Log Maintenance

MySQL Server has several logs that can help you find out what activity is taking place.

Log Type Information Written to Log
Error log Problems encountered starting, running, or stopping mysqld
General query log Established client connections and statements received from clients
Binary log Statements that change data (also used for replication)
Relay log Data changes received from a replication master server
Slow query log Queries that took more than long_query_time seconds to execute

By default, no logs are enabled. The following log-specific sections provide information about the server options that enable logging.

By default, the server writes files for all enabled logs in the data directory. You can force the server to close and reopen the log files (or in some cases switch to a new log file) by flushing the logs. Log flushing occurs when you issue a FLUSH LOGS statement; execute mysqladmin with a flush-logs or refresh argument; or execute mysqldump with a --flush-logs or --master-data option. See , "FLUSH Syntax", , "mysqladmin - Client for Administering a MariaDB Server", and , "mysqldump - A Database Backup Program". In addition, the binary log is flushed when its size reaches the value of the max_binlog_size system variable.

You can control the general query and slow query logs during runtime. You can enable or disable logging, or change the log file name. You can tell the server to write general query and slow query entries to log tables, log files, or both. For details, see , "Selecting General Query and Slow Query Log Output Destinations", , "The General Query Log", and , "The Slow Query Log".

The relay log is used only on slave replication servers, to hold data changes from the master server that must also be made on the slave. For discussion of relay log contents and configuration, see , "The Slave Relay Log".

For information about log maintenance operations such as expiration of old log files, see , "Server Log Maintenance".

For information about keeping logs secure, see , "Administrator Guidelines for Password Security".

Selecting General Query and Slow Query Log Output Destinations

MySQL Server provides flexible control over the destination of output to the general query log and the slow query log, if those logs are enabled. Possible destinations for log entries are log files or the general_log and slow_log tables in the MariaDB database. Either or both destinations can be selected.

Currently, logging to tables incurs significantly more server overhead than logging to files. If you enable the general log or slow query log and require highest performance, you should use file logging, not table logging.

Log control at server startup. The --log-output option specifies the destination for log output. This option does not in itself enable the logs. Its syntax is --log-output[=value,...]:

The general_log system variable controls logging to the general query log for the selected log destinations. If specified at server startup, general_log takes an optional argument of 1 or 0 to enable or disable the log. To specify a file name other than the default for file logging, set the general_log_file variable. Similarly, the slow_query_log variable controls logging to the slow query log for the selected destinations and setting slow_query_log_file specifies a file name for file logging. If either log is enabled, the server opens the corresponding log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected.

Examples:

Log control at runtime. The system variables associated with log tables and files enable runtime control over logging:

The use of tables for log output offers the following benefits:

The log table implementation has the following characteristics:

The Error Log

The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

On some operating systems, the error log contains a stack trace if mysqld dies. The trace can be used to determine where mysqld died. See MySQL Internals: Porting.

You can specify where mysqld writes the error log with the --log-error[=file_name] option. If the option is given with no file_name value, mysqld uses the name host_name.err by default. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

If you do not specify --log-error, or (on Windows) if you use the --console option, errors are written to stderr, the standard error output. Usually this is your terminal.

On Windows, error output is always written to the error log if --console is not given.

In addition, on Windows, events and error messages are written to the Windows Event Log within the Application log. Entries marked as Warning and Note are written to the Event Log, but informational messages (such as information statements from individual storage engines) are not copied to the Event Log. The log entries have a source of MySQL. You cannot disable writing information to the Windows Event Log.

If you flush the logs using FLUSH LOGS or mysqladmin flush-logs and mysqld is writing the error log to a file (for example, if it was started with the --log-error option), the server closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands:

shell> mv host_name.err host_name.err-old
shell> mysqladmin flush-logs
shell> mv host_name.err-old backup-directory

On Windows, use rename rather than mv.

No error log renaming occurs when the logs are flushed if the server is not writing to a named file.

If you use mysqld-safe to start mysqld, mysqld_safe arranges for mysqld to write error messages to a log file or to syslog mysqld_safe has three error-logging options, --syslog, --skip-syslog, and --log-error. The default with no logging options or with --skip-syslog is to use the default log file. To explicitly specify use of an error log file, specify --log-error=file_name to mysqld_safe, and mysqld_safe will arrange for mysqld to write messages to a log file. To use syslog instead, specify the --syslog option.

If you specify --log-error in an option file in a [mysqld], [server], or [mysqld_safe] section, mysqld_safe will find and use the option.

If mysqld-safe is used to start mysqld and mysqld dies unexpectedly, mysqld_safe notices that it needs to restart mysqld and writes a restarted mysqld message to the error log.

The --log-warnings option or log_warnings system variable can be used to control warning logging to the error log. The default value is enabled (1). Warning logging can be disabled using a value of 0. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See "Communication Errors and Aborted Connections".

The General Query Log

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order contrasts to the binary log, for which statements are written after they are executed but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)

By default, the general query log is disabled. To specify the initial general query log state explicitly, use --general_log[={0|1}]. With no argument or an argument of 1, --general_log enables the log. With an argument of 0, this option disables the log. To specify a log file name, use --general_log_file=file_name. To specify the log destination, use --log-output (as described in , "Selecting General Query and Slow Query Log Output Destinations").

If you specify no name for the general query log file, the default name is host_name.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the general query log or change the log file name at runtime, use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the general query log is enabled, the server writes output to any destinations specified by the --log-output option or log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the general log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). You can rename the file and create a new one by using the following commands:

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory

On Windows, use rename rather than mv.

You can also rename the general query log file at runtime by disabling the log:

SET GLOBAL general_log = 'OFF';

With the log disabled, rename the log file externally; for example, from the command line. Then enable the log again:

SET GLOBAL general_log = 'ON';

This method works on any platform and does not require a server restart.

The session sql_log_off variable can be set to ON or OFF to disable or enable general query logging for the current connection.

As of MariaDB 5.6.3, passwords in statements written to the general query log are rewritten by the server not to occur literally in plain text. Password rewriting can be suppressed for the general query log by starting the server with the --log-raw option. This option may be useful for diagnostic purposes, to see the exact text of statements as received by the server, but for security reasons is not recommended for production use.

Before MariaDB 5.6.3, passwords in statements are not rewritten and the general query log should be protected. See , "Administrator Guidelines for Password Security".

The Binary Log

Binary Logging Formats
Setting The Binary Log Format
Mixed Binary Logging Format
Logging Format for Changes to MariaDB Database Tables

The binary log contains "events" that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

Beginning with MariaDB 5.6.2, the binary log is crash-safe. Only complete events or transactions are logged or read back.

By default, the server logs the length of the event as well as the event itself and uses this to verify that the event was written correctly. You can also cause the server to write checksums for the events by setting the binlog_checksum system variable. When reading back from the binary log, the master uses the event length by default, but can be made to use checksums if available by enabling the master_verify_checksum system variable. The slave I/O thread also verifies events received from the master. You can cause the slave SQL thread to use checksums if available when reading from the relay log by enabling the slave_sql_verify_checksum system variable.

For information about server options and variables affecting the operation of binary logging, see , "Binary Log Options and Variables".

The binary log is not used for statements such as SELECT or SHOW that do not modify data. If you want to log all statements (for example, to identify a problem query), use the general query log. See , "The General Query Log".

As of MariaDB 5.6.3, passwords in statements written to the binary log are rewritten by the server not to occur literally in plain text. Before MariaDB 5.6.3, passwords in statements are not rewritten and the binary log should be protected. See , "Administrator Guidelines for Password Security".

The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging. The binary logging format used depends on the MariaDB version. For more information on logging formats, see , "Binary Logging Formats".

For information about the format of the binary log itself, see http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log.

To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin. If the basename is given, the server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory. It is recommended that you specify a basename; see "Known Issues in MySQL", for the reason.

If you supply an extension in the log name (for example, --log-bin=base_name.extension), the extension is silently removed and ignored.

mysqld appends a numeric extension to the binary log basename to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max-binlog-size. A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files.

To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same basename as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

The term "binary log file" generally denotes an individual numbered file containing database events. The term "binary log" collectively denotes the set of numbered binary log files plus the index file.

The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the --replicate-do-db and --replicate-ignore-db options. For information about how this is done, see , "Evaluation of Database-Level Replication and Binary Logging Options".

A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-bin option (see , "Replication Slave Options and Variables"). This is done when a slave is also to act as a master to other slaves in chained replication.

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. See , "RESET Syntax", and , "PURGE BINARY LOGS Syntax".

If you are using replication, you should not delete old binary log files on the master until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use PURGE BINARY LOGS, which also safely updates the binary log index file for you (and which can take a date argument). See , "PURGE BINARY LOGS Syntax".

A client that has the SUPER privilege can disable binary logging of its own statements by using a SET sql_log_bin=0 statement. See , "Server System Variables".

You can display the contents of binary log files with the mysqlbinlog utility. This can be useful when you want to reprocess statements in the log for a recovery operation. For example, you can update a MariaDB server from the binary log as follows:

shell> mysqlbinlog log_file | mysql -h server_name

mysqlbinlog also can be used to display replication slave relay log file contents because they are written using the same format as binary log files. For more information on the mysqlbinlog utility and how to use it, see , "mysqlbinlog - Utility for Processing Binary Log Files". For more information about the binary log and recovery operations, see , "Point-in-Time (Incremental) Recovery Using the Binary Log".

Binary logging is done immediately after a statement completes but before any locks are released or any commit is done. This ensures that the log is logged in execution order.

Updates to nontransactional tables are stored in the binary log immediately after execution.

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that the modifications to those tables are replicated.

When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.

The Binlog_cache_use status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. The Binlog_cache_disk_use status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size to a large enough value that avoids the use of temporary files.

The max_binlog_cache_size system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.

If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation. If you are using statement-based logging, the original statement is written to the log.

The binary log format has some known limitations that can affect recovery from backups. See , "Replication Features and Issues".

Binary logging for stored programs is done as described in , "Binary Logging of Stored Programs".

Note that the binary log format differs in MariaDB 5.6 from previous versions of MySQL, due to enhancements in replication. See , "Replication Compatibility Between MariaDB Versions".

Writes to the binary log file and binary log index file are handled in the same way as writes to MyISAM tables. See "How MariaDB Handles a Full Disk".

By default, the binary log is not synchronized to disk at each write. So if the operating system or machine (not only the MariaDB server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, you can make the binary log be synchronized to disk after every N writes to the binary log, with the sync_binlog system variable. See , "Server System Variables". 1 is the safest value for sync_binlog, but also the slowest. Even with sync_binlog set to 1, there is still the chance of an inconsistency between the table content and binary log content in case of a crash. For example, if you are using InnoDB tables and the MariaDB server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. To resolve this, you should set --innodb_support_xa to 1. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized.

For this option to provide a greater degree of safety, the MariaDB server should also be configured to synchronize the binary log and the InnoDB logs to disk at every transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 can be used to synchronize the binary log. The effect of this option is that at restart after a crash, after doing a rollback of transactions, the MariaDB server cuts rolled back InnoDB transactions from the binary log. This ensures that the binary log reflects the exact data of InnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

If the MariaDB server discovers at crash recovery that the binary log is shorter than it should have been, it lacks at least one successfully committed InnoDB transaction. This should not happen if sync_binlog=1 and the disk/file system do an actual sync when they are requested to (some do not), so the server prints an error message The binary log file_name is shorter than its expected size. In this case, this binary log is not correct and replication should be restarted from a fresh snapshot of the master's data.

The session values of the following system variables are written to the binary log and honored by the replication slave when parsing the binary log:

Binary Logging Formats

The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MariaDB being used. There are three logging formats:

In MariaDB 5.6, the default binary logging format is STATEMENT.

The logging format can also be set or limited by the storage engine being used. This helps to eliminate issues when replicating certain statements between a master and slave which are using different storage engines.

With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MariaDB determines whether it can guarantee that the statement can be replicated using statement-based logging. If MariaDB cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.

You can avoid these issues by using MySQL's row-based replication instead.

Setting The Binary Log Format

You can select the binary logging format explicitly by starting the MariaDB server with --binlog-format=type. The supported values for type are:

In MariaDB 5.6, the default binary logging format is STATEMENT.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

An individual client can control the logging format for its own statements by setting the session value of binlog_format:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
Note

Each MariaDB Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.

To change the global or session binlog-format value, you must have the SUPER privilege.

In addition to switching the logging format manually, a slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters an event in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterward.

There are several reasons why a client might want to set binary logging on a per-session basis:

There are exceptions when you cannot switch the replication format at runtime:

Trying to switch the format in any of these cases results in an error.

Switching the replication format at runtime is not recommended when any temporary tables exist, because temporary tables are logged only when using statement-based replication, whereas with row-based replication they are not logged. With mixed replication, temporary tables are usually logged; exceptions happen with user-defined functions (UDFs) and with the UUID() function.

With the binary log format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

The --binlog-row-event-max-size option is available for servers that are capable of row-based replication. Rows are stored into the binary log in chunks having a size in bytes not exceeding the value of this option. The value must be a multiple of 256. The default value is 1024.Warning

When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication. For a detailed explanation of this issue, see "Known Issues in MySQL".

For information about logs kept by replication slaves, see , "Replication Relay and Status Logs".

Mixed Binary Logging Format

When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:

Note

A warning is generated if you try to execute a statement using statement-based logging that should be written using row-based logging. The warning is shown both in the client (in the output of SHOW WARNINGS) and through the mysqld error log. A warning is added to the SHOW WARNINGS table each time such a statement is executed. However, only the first statement that generated the warning for each client session is written to the error log to prevent flooding the log.

In addition to the decisions above, individual engines can also determine the logging format used when information in a table is updated. The logging capabilities of an individual engine can be defined as follows:

A given storage engine can support either or both logging formats. The following table lists the formats supported by each engine.

Storage Engine Row Logging Supported Statement Logging Supported
ARCHIVE Yes Yes
BLACKHOLE Yes Yes
CSV Yes Yes
EXAMPLE Yes No
FEDERATED Yes Yes
HEAP Yes Yes
InnoDB Yes Yes when the transaction isolation level is REPEATABLE READ or SERIALIZABLE; No otherwise.
MyISAM Yes Yes
MERGE Yes Yes
NDBCLUSTER Yes No

In MariaDB 5.6, whether a statement is to be logged and the logging mode to be used is determined according to the type of statement (safe, unsafe, or binary injected), the binary logging format (STATEMENT, ROW, or MIXED), and the logging capabilities of the storage engine (statement capable, row capable, both, or neither). Statements may be logged with or without a warning; failed statements are not logged, but generate errors in the log. This is shown in the following decision table, where SLC stands for "statement-logging capable" and RLC stands for "row-logging capable".

Condition Action
Type binlog_format SLC RLC Error / Warning Logged as
* * No No Error: Cannot execute statement: Binary logging is impossible since at least one engine is involved that is both row-incapable and statement-incapable. -
Safe STATEMENT Yes No - STATEMENT
Safe MIXED Yes No - STATEMENT
Safe ROW Yes No Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging. -
Unsafe STATEMENT Yes No Warning: Unsafe statement binlogged in statement format, since BINLOG_FORMAT = STATEMENT STATEMENT
Unsafe MIXED Yes No Error: Cannot execute statement: Binary logging of an unsafe statement is impossible when the storage engine is limited to statement-based logging, even if BINLOG_FORMAT = MIXED. -
Unsafe ROW Yes No Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = ROW and at least one table uses a storage engine that is not capable of row-based logging. -
Row Injection STATEMENT Yes No Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging. -
Row Injection MIXED Yes No Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging. -
Row Injection ROW Yes No Error: Cannot execute row injection: Binary logging is not possible since at least one table uses a storage engine that is not capable of row-based logging. -
Safe STATEMENT No Yes Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging. -
Safe MIXED No Yes - ROW
Safe ROW No Yes - ROW
Unsafe STATEMENT No Yes Error: Cannot execute statement: Binary logging is impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine that is not capable of statement-based logging. -
Unsafe MIXED No Yes - ROW
Unsafe ROW No Yes - ROW
Row Injection STATEMENT No Yes Error: Cannot execute row injection: Binary logging is not possible since BINLOG_FORMAT = STATEMENT. -
Row Injection MIXED No Yes - ROW
Row Injection ROW No Yes - ROW
Safe STATEMENT Yes Yes - STATEMENT
Safe MIXED Yes Yes - ROW
Safe ROW Yes Yes - ROW
Unsafe STATEMENT Yes Yes Warning: Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. STATEMENT
Unsafe MIXED Yes Yes - ROW
Unsafe ROW Yes Yes - ROW
Row Injection STATEMENT Yes Yes Error: Cannot execute row injection: Binary logging is not possible because BINLOG_FORMAT = STATEMENT. -
Row Injection MIXED Yes Yes - ROW
Row Injection ROW Yes Yes - ROW

When a warning is produced by the determination, a standard MariaDB warning is produced (and is available using SHOW WARNINGS). The information is also written to the mysqld error log. Only one error for each error instance per client connection is logged to prevent flooding the log. The log message includes the SQL statement that was attempted.

If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.

Logging Format for Changes to MariaDB Database Tables

The contents of the grant tables in the MariaDB database can be modified directly (for example, with INSERT or DELETE) or indirectly (for example, with GRANT or CREATE USER). Statements that affect MariaDB database tables are written to the binary log using the following rules:

CREATE TABLE ... SELECT is a combination of data definition and data manipulation. The CREATE TABLE part is logged using statement format and the SELECT part is logged according to the value of binlog_format.

The Slow Query Log

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.

The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.

By default, the slow query log is disabled. To specify the initial slow query log state explicitly, use --slow_query_log[={0|1}]. With no argument or an argument of 1, --slow_query_log enables the log. With an argument of 0, this option disables the log. To specify a log file name, use --slow_query_log_file=file_name. To specify the log destination, use --log-output (as described in , "Selecting General Query and Slow Query Log Output Destinations").

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set slow_query_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the slow query log is enabled, the server writes output to any destinations specified by the --log-output option or log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the slow query log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

The server writes less information to the slow query log (and binary log) if you use the --log-short-format option.

To include slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE in the statements written to the slow query log, enable the log_slow_admin_statements system variable.

To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.

The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:

  1. The query must either not be an administrative statement, or log_slow_admin_statements must be enabled.
  2. The query must have taken at least long-query-time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.
  3. The query must have examined at least min_examined_row_limit rows.
  4. The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.

The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.

By default, a replication slave does not write replicated queries to the slow query log. To change this, enable the log_slow_slave_statements system variable.

As of MariaDB 5.6.3, passwords in statements written to the slow query log are rewritten by the server not to occur literally in plain text. Before MariaDB 5.6.3, passwords in statements are not rewritten and the slow query log should be protected. See , "Administrator Guidelines for Password Security".

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See , "mysqldumpslow - Summarize Slow Query Log Files".

Server Log Maintenance

As described in , "MySQL Server Logs", MariaDB Server can create several different log files to help you see what activity is taking place. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.

When using MariaDB with logging enabled, you may want to back up and remove old log files from time to time and tell MariaDB to start logging to new files. See , "Database Backup Methods".

On a Linux (Red Hat) installation, you can use the mysql-log-rotate script for this. If you installed MariaDB from an RPM distribution, this script should have been installed automatically. Be careful with this script if you are using the binary log for replication. You should not remove binary logs until you are certain that their contents have been processed by all slaves.

On other systems, you must install a short script yourself that you start from cron (or its equivalent) for handling log files.

For the binary log, you can set the expire_logs_days system variable to expire binary log files automatically after a given number of days (see , "Server System Variables"). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master. To remove binary logs on demand, use the PURGE BINARY LOGS statement (see , "PURGE BINARY LOGS Syntax").

You can force MariaDB to start using new log files by flushing the logs. Log flushing occurs when you issue a FLUSH LOGS statement or execute a mysqladmin flush-logs, mysqladmin refresh, mysqldump --flush-logs, or mysqldump --master-data command. See , "FLUSH Syntax", , "mysqladmin - Client for Administering a MariaDB Server", and , "mysqldump - A Database Backup Program". In addition, the binary log is flushed when its size reaches the value of the max_binlog_size system variable.

FLUSH LOGS supports optional modifiers to enable selective flushing of individual logs (for example, FLUSH BINARY LOGS).

A log-flushing operation does the following:

The server creates a new binary log file when you flush the logs. However, it just closes and reopens the general and slow query log files. To cause new files to be created on Unix, rename the current log files before flushing them. At flush time, the server opens new log files with the original names. For example, if the general and slow query log files are named mysql.log and mysql-slow.log, you can use a series of commands like this:

shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mv mysql-slow.log mysql-slow.old
shell> mysqladmin flush-logs

On Windows, use rename rather than mv.

At this point, you can make a backup of mysql.old and mysql-slow.old and then remove them from disk.

A similar strategy can be used to back up the error log file, if there is one.

You can rename the general query log or slow query log at runtime by disabling the log:

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

With the logs disabled, rename the log files externally; for example, from the command line. Then enable the logs again:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

This method works on any platform and does not require a server restart.

General Security Issues

General Security Guidelines
Password Security in MySQL
Making MariaDB Secure Against Attackers
Security-Related mysqld Options
Security Issues with LOAD DATA LOCAL
How to Run MariaDB as a Normal User

This section describes some general security issues to be aware of and what you can do to make your MariaDB installation more secure against attack or misuse. For information specifically about the access control system that MariaDB uses for setting up user accounts and checking database access, see , "The MariaDB Access Privilege System".

For answers to some questions that are often asked about MariaDB Server security issues, see "MySQL 5.6 FAQ: Security".

General Security Guidelines

Anyone using MariaDB on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not just the MariaDB server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MariaDB clients and servers. Many of the concepts discussed here are not specific to MariaDB at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines whenever possible:

Password Security in MariaDB

Administrator Guidelines for Password Security
End-User Guidelines for Password Security
Password Hashing in MySQL
Implications of Password Hashing Changes in MariaDB for Application Programs

Passwords occur in several contexts within MySQL. The following sections provide guidelines that enable administrators and end users to keep these passwords secure and avoid exposing them. There is also a discussion of how MariaDB uses password hashing internally.

Administrator Guidelines for Password Security

Database administrators should use the following guidelines to keep passwords secure.

MySQL stores passwords for user accounts in the mysql.user table. Access to this table should never be granted to any nonadministrative accounts.

A user who has access to modify the plugin directory (the value of the plugin_dir system variable) or the my.cnf file that specifies the location of the plugin directory can replace plugins and modify the capabilities provided by plugins.

Passwords can appear as plain text in SQL statements such as CREATE USER, GRANT, and SET PASSWORD, or statements that invoke the PASSWORD() function. If these statements are logged by the MariaDB server, the passwords become available to anyone with access to the logs.

As of MariaDB 5.6.3, passwords in statements written to the general query log, slow query log, and binary log are rewritten by the server not to occur literally in plain text. Password rewriting can be suppressed for the general query log by starting the server with the --log-raw option. This option may be useful for diagnostic purposes, to see the exact text of statements as received by the server, but for security reasons is not recommended for production use.

To guard against unwarranted exposure to log files, they should be located in a directory that restricts access to only the server and the database administrator. If you log to tables in the MariaDB database, access to the tables should never be granted to any nonadministrative accounts.

Replication slaves store the password for the replication master in the master.info file. Access to this file should be restricted to the database administrator.

Database backups that include tables or log files containing passwords should be protected using a restricted access mode.

End-User Guidelines for Password Security

MySQL users should use the following guidelines to keep passwords secure.

When you run a client program to connect to the MariaDB server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method. In short, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.

On Unix, the mysql client writes a record of executed statements to a history file (see , "mysql History File"). By default, this file is named .mysql_history and is created in your home directory. Passwords can appear as plain text in SQL statements such as CREATE USER, GRANT, and SET PASSWORD, so if you use these statements, they are logged in the history file. To keep this file safe, use a restrictive access mode, the same way as described earlier for the .my.cnf file.

If your command interpreter is configured to maintain a history, any file in which the commands are saved will contain MariaDB passwords entered on the command line. For example, bash uses ~/.bash_history. Any such file should have a restrictive access mode.

Password Hashing in MariaDB

MySQL user accounts are listed in the user table of the MariaDB database. Each MariaDB account is assigned a password, although what is stored in the Password column of the user table is not the plaintext version of the password, but a hash value computed from it. Password hash values are computed by the PASSWORD() function.

MySQL uses passwords in two phases of client/server communication:

In other words, the server uses hash values during authentication when a client first attempts to connect. The server generates hash values if a connected client invokes the PASSWORD() function or uses a GRANT or SET PASSWORD statement to set or change a password.

The password hashing mechanism was updated in MariaDB to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MariaDB (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 or newer server may run into difficulties. For example, a 3.23 mysql client that attempts to connect to a 5.6 server may fail with the following error message:

shell> mysql -h localhost -u root
Client does not support authentication protocol requested by server; consider upgrading MariaDB client

Another common example of this phenomenon occurs for attempts to use the older PHP MariaDB extension after upgrading to MariaDB or newer. (See , "Common Problems with MariaDB and PHP".)

The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server but need to maintain backward compatibility with pre-4.1 clients. Additional information can be found in "Client does not support authentication protocol". This information is of particular importance to PHP programmers migrating MariaDB databases from version 4.0 or lower to version 4.1 or higher.Note

This discussion contrasts 4.1 behavior with pre-4.1 behavior, but the 4.1 behavior described here actually begins with 4.1.1. MariaDB 4.1.0 is an "odd" release because it has a slightly different mechanism than that implemented in 4.1.1 and up. Differences between 4.1.0 and more recent versions are described further in MariaDB 5.5 Reference Manual.

Prior to MySQL, password hashes computed by the PASSWORD() function are 16 bytes long. Such hashes look like this:

mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e |
+--------------------+

The Password column of the user table (in which these hashes are stored) also is 16 bytes long before MariaDB 4.1.

As of MySQL, the PASSWORD() function has been modified to produce a longer 41-byte hash value:

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

Accordingly, the Password column in the user table also must be 41 bytes long to store these values:

A widened Password column can store password hashes in both the old and new formats. The format of any given password hash value can be determined two ways:

The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.

The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.

The way in which the server uses password hashes during authentication is affected by the width of the Password column:

Even for short-hash accounts, the authentication process is actually a bit more secure for 4.1 and later clients than for older clients. In terms of security, the gradient from least to most secure is:

The way in which the server generates password hashes for connected clients is affected by the width of the Password column and by the --old-passwords option. A 4.1 or later server generates long hashes only if certain conditions are met: The Password column must be wide enough to hold long values and the --old-passwords option must not be given. These conditions apply as follows:

The purpose of the --old-passwords option is to enable you to maintain backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. The option does not affect authentication (4.1 and later clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the user table as the result of a password-changing operation. Were that to occur, the account no longer could be used by pre-4.1 clients. Without the --old-passwords option, the following undesirable scenario is possible:

This scenario illustrates that, if you must support older pre-4.1 clients, it is dangerous to run a 4.1 or newer server without using the --old-passwords option. By running the server with --old-passwords, password-changing operations do not generate long password hashes and thus do not cause accounts to become inaccessible to older clients. (Those clients cannot inadvertently lock themselves out by changing their password and ending up with a long password hash.)

The downside of the --old-passwords option is that any passwords you create or change use short hashes, even for 4.1 clients. Thus, you lose the additional security provided by long password hashes. If you want to create an account that has a long hash (for example, for use by 4.1 clients), you must do so while running the server without --old-passwords.

The following scenarios are possible for running a 4.1 or later server:

Scenario 1: Short Password column in user table:

Scenario 2: Long Password column; server not started with --old-passwords option:

As indicated earlier, a danger in this scenario is that it is possible for accounts that have a short password hash to become inaccessible to pre-4.1 clients. A change to such an account's password made using GRANT, PASSWORD(), or SET PASSWORD results in the account being given a long password hash. From that point on, no pre-4.1 client can authenticate to that account until the client upgrades to 4.1.

To deal with this problem, you can change a password in a special way. For example, normally you use SET PASSWORD as follows to change an account password:

SET PASSWORD FOR 'some_user'@'some_host' = PASSWORD('mypass');

To change the password but create a short hash, use the OLD_PASSWORD() function instead:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

OLD_PASSWORD() is useful for situations in which you explicitly want to generate a short hash.

Scenario 3: Long Password column; 4.1 or newer server started with --old-passwords option:

In this scenario, you cannot create accounts that have long password hashes, because the --old-passwords option prevents generation of long hashes. Also, if you create an account with a long hash before using the --old-passwords option, changing the account's password while --old-passwords is in effect results in the account being given a short password, causing it to lose the security benefits of a longer hash.

The disadvantages for these scenarios may be summarized as follows:

In scenario 1, you cannot take advantage of longer hashes that provide more secure authentication.

In scenario 2, accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly using OLD_PASSWORD().

In scenario 3, --old-passwords prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes, and you cannot change them back to long hashes while --old-passwords is in effect.

Implications of Password Hashing Changes in MariaDB for Application Programs

An upgrade to MariaDB version 4.1 or later can cause compatibility issues for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MariaDB accounts. But some applications use PASSWORD() for their own purposes anyway.

If you upgrade to 4.1 or later from a pre-4.1 version of MariaDB and run the server under conditions where it generates long password hashes, an application using PASSWORD() for its own passwords breaks. The recommended course of action in such cases is to modify the application to use another function, such as SHA1() or MD5(), to produce hashed values. If that is not possible, you can use the OLD_PASSWORD() function, which is provided for generate short hashes in the old format. However, you should note that OLD_PASSWORD() may one day no longer be supported.

If the server is running under circumstances where it generates short hashes, OLD_PASSWORD() is available but is equivalent to PASSWORD().

PHP programmers migrating their MariaDB databases from version 4.0 or lower to version 4.1 or higher should see , "MySQL PHP API".

Making MariaDB Secure Against Attackers

When you connect to a MariaDB server, you should use a password. The password is not transmitted in clear text over the connection. Password handling during the client connection sequence was upgraded in MariaDB to be very secure. If you are still using pre-4.1.1-style passwords, the encryption algorithm is not as strong as the newer algorithm. With some effort, a clever attacker who can sniff the traffic between the client and the server can crack the password. (See , "Password Hashing in MySQL", for a discussion of the different password handling methods.)

All other information is transferred as text, and can be read by anyone who is able to watch the connection. If the connection between the client and the server goes through an untrusted network, and you are concerned about this, you can use the compressed protocol to make traffic much more difficult to decipher. You can also use MySQL's internal SSL support to make the connection even more secure. See , "Using SSL for Secure Connections". Alternatively, use SSH to get an encrypted TCP/IP connection between a MariaDB server and a MariaDB client. You can find an Open Source SSH client at http://www.openssh.org/, and a commercial SSH client at http://www.ssh.com/.

To make a MariaDB system secure, you should strongly consider the following suggestions:

Security-Related mysqld Options

The following mysqld options affect security:

Table 5.5. Security Option/Variable Summary

Name Cmd-Line Option file System Var Status Var Var Scope Dynamic
allow-suspicious-udfs Yes Yes
automatic_sp_privileges Yes Global Yes
chroot Yes Yes
des-key-file Yes Yes
local_infile Yes Global Yes
local-infile Yes Yes
- Variable: local_infile
old-passwords Yes Yes Both Yes
- Variable: old_passwords Yes Both Yes
safe-user-create Yes Yes
secure-auth Yes Yes Global Yes
- Variable: secure_auth Yes Global Yes
secure-file-priv Yes Yes Global No
- Variable: secure_file_priv Yes Global No
skip-grant-tables Yes Yes
skip-name-resolve Yes Yes Global No
- Variable: skip_name_resolve Yes Global No
skip-networking Yes Yes Global No
- Variable: skip_networking Yes Global No
skip-show-database Yes Yes Global No
- Variable: skip_show_database Yes Global No

Security Issues with LOAD DATA LOCAL

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified.

There are two potential security issues with supporting the LOCAL version of LOAD DATA statements:

To deal with these problems, we changed how LOAD DATA LOCAL is handled as of MariaDB 3.23.49 and MariaDB 4.0.2 (4.0.13 on Windows):

How to Run MariaDB as a Normal User

On Windows, you can run the server as a Windows service using a normal user account.

On Unix, the MariaDB server mysqld can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons. To change mysqld to run as a normal unprivileged Unix user user_name, you must do the following:

  1. Stop the server if it is running (use mysqladmin shutdown).
  2. Change the database directories and files so that user_name has privileges to read and write files in them (you might need to do this as the Unix root user):

    shell> chown -R user_name /path/to/mysql/datadir
    

    If you do not do this, the server will not be able to access databases or tables when it runs as user_name.

    If directories or files within the MariaDB data directory are symbolic links, chown -R might not follow symbolic links for you. If it does not, you will also need to follow those links and change the directories and files they point to.

  3. Start the server as user user_name. Another alternative is to start mysqld as the Unix root user and use the --user=user-name option. mysqld starts up, then switches to run as the Unix user user_name before accepting any connections.
  4. To start the server as the given user automatically at system startup time, specify the user name by adding a user option to the [mysqld] group of the /etc/my.cnf option file or the my.cnf option file in the server's data directory. For example:

    [mysqld]
    user=user_name
    

If your Unix machine itself is not secured, you should assign passwords to the MariaDB root accounts in the grant tables. Otherwise, any user with a login account on that machine can run the mysql client with a --user=root option and perform any operation. (It is a good idea to assign passwords to MariaDB accounts in any case, but especially so when other login accounts exist on the server host.) See , "Postinstallation Setup and Testing".

The MariaDB Access Privilege System

Privileges Provided by MySQL
Privilege System Grant Tables
Specifying Account Names
Access Control, Stage 1: Connection Verification
Access Control, Stage 2: Request Verification
When Privilege Changes Take Effect
Causes of Access-Denied Errors

The primary function of the MariaDB privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE. Additional functionality includes the ability to have anonymous users and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

There are some things that you cannot do with the MariaDB privilege system:

The user interface to the MariaDB privilege system consists of SQL statements such as CREATE USER, GRANT, and REVOKE. See , "Account Management Statements".

Internally, the server stores privilege information in the grant tables of the MariaDB database (that is, in the database named MariaDB). The MariaDB server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables.

The MariaDB privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MariaDB server, your identity is determined by the host from which you connect and the user name you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MariaDB handles this by enabling you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com. To see what privileges a given account has, use the SHOW GRANTS statement. For example:

SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';

MySQL access control involves two stages when you run a client program that connects to the server:

Stage 1: The server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password.

Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

For a more detailed description of what happens during each stage, see , "Access Control, Stage 1: Connection Verification", and , "Access Control, Stage 2: Request Verification".

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. For details about the conditions under which the server reloads the grant tables, see , "When Privilege Changes Take Effect".

For general security-related advice, see , "General Security Issues". For help in diagnosing privilege-related problems, see , "Causes of Access-Denied Errors".

Privileges Provided by MariaDB

MySQL provides privileges that apply in different contexts and at different levels of operation:

Information about account privileges is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables in the MariaDB database (see , "Privilege System Grant Tables"). The MariaDB server reads the contents of these tables into memory when it starts and reloads them under the circumstances indicated in , "When Privilege Changes Take Effect". Access-control decisions are based on the in-memory copies of the grant tables.

Some releases of MariaDB introduce changes to the structure of the grant tables to add new access privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See , "mysql_upgrade - Check Tables for MariaDB Upgrade".

The following table shows the privilege names used at the SQL level in the GRANT and REVOKE statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Table 5.6. Permissible Privileges for GRANT and REVOKE

Privilege Column Context
CREATE Create_priv databases, tables, or indexes
DROP Drop_priv databases, tables, or views
GRANT OPTION Grant_priv databases, tables, or stored routines
LOCK TABLES Lock_tables_priv databases
REFERENCES References_priv databases or tables
EVENT Event_priv databases
ALTER Alter_priv tables
DELETE Delete_priv tables
INDEX Index_priv tables
INSERT Insert_priv tables or columns
SELECT Select_priv tables or columns
UPDATE Update_priv tables or columns
CREATE TEMPORARY TABLES Create_tmp_table_priv tables
TRIGGER Trigger_priv tables
CREATE VIEW Create_view_priv views
SHOW VIEW Show_view_priv views
ALTER ROUTINE Alter_routine_priv stored routines
CREATE ROUTINE Create_routine_priv stored routines
EXECUTE Execute_priv stored routines
FILE File_priv file access on server host
CREATE TABLESPACE Create_tablespace_priv server administration
CREATE USER Create_user_priv server administration
PROCESS Process_priv server administration
PROXY see proxies_priv table server administration
RELOAD Reload_priv server administration
REPLICATION CLIENT Repl_client_priv server administration
REPLICATION SLAVE Repl_slave_priv server administration
SHOW DATABASES Show_db_priv server administration
SHUTDOWN Shutdown_priv server administration
SUPER Super_priv server administration
ALL [PRIVILEGES] server administration
USAGE server administration

The following list provides a general description of each privilege available in MySQL. Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting the FILE and administrative privileges:

Privilege System Grant Tables

Normally, you manipulate the contents of the grant tables in the MariaDB database indirectly by using statements such as GRANT and REVOKE to set up accounts and control the privileges available to each one. See , "Account Management Statements". The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.

These MariaDB database tables contain grant information:

Other tables in the MariaDB database do not hold grant information and are discussed elsewhere:

Each grant table contains scope columns and privilege columns:

The server uses the grant tables in the following manner:

The server uses the user, db, and host tables in the MariaDB database at both the first and second stages of access control (see , "The MariaDB Access Privilege System"). The columns in the user and db tables are shown here. The host table is similar to the db table but has a specialized use as described in , "Access Control, Stage 2: Request Verification".

Table 5.7. user and db Table Columns

Table Name user db
Scope columns Host Host
User Db
Password User
Privilege columns Select_priv Select_priv
Insert_priv Insert_priv
Update_priv Update_priv
Delete_priv Delete_priv
Index_priv Index_priv
Alter_priv Alter_priv
Create_priv Create_priv
Drop_priv Drop_priv
Grant_priv Grant_priv
Create_view_priv Create_view_priv
Show_view_priv Show_view_priv
Create_routine_priv Create_routine_priv
Alter_routine_priv Alter_routine_priv
Execute_priv Execute_priv
Trigger_priv Trigger_priv
Event_priv Event_priv
Create_tmp_table_priv Create_tmp_table_priv
Lock_tables_priv Lock_tables_priv
References_priv References_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Show_db_priv
Super_priv
Repl_slave_priv
Repl_client_priv
Create_user_priv
Create_tablespace_priv
Security columns ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin
authentication_string
Resource control columns max_questions
max_updates
max_connections
max_user_connections

The mysql.user table plugin and authentication_string columns store authentication plugin information.

If the plugin column for an account row is empty, the server uses native authentication for connection attempts for the account: Clients must match the password in the Password column of the account row.

If an account row names a plugin in the plugin column, the server uses it to authenticate connection attempts for the account. Whether the plugin uses the value in the Password column is up to the plugin.

During the second stage of access control, the server performs request verification to make sure that each client has sufficient privileges for each request that it issues. In addition to the user, db, and host grant tables, the server may also consult the tables_priv and columns_priv tables for requests that involve tables. The latter tables provide finer privilege control at the table and column levels. They have the columns shown in the following table.

Table 5.8. tables_priv and columns_priv Table Columns

Table Name tables_priv columns_priv
Scope columns Host Host
Db Db
User User
Table_name Table_name
Column_name
Privilege columns Table_priv Column_priv
Column_priv
Other columns Timestamp Timestamp
Grantor

The Timestamp and Grantor columns currently are unused and are discussed no further here.

For verification of requests that involve stored routines, the server may consult the procs_priv table, which has the columns shown in the following table.

Table 5.9. procs_priv Table Columns

Table Name procs_priv
Scope columns Host
Db
User
Routine_name
Routine_type
Privilege columns Proc_priv
Other columns Timestamp
Grantor

The Routine_type column is an ENUM column with values of 'FUNCTION' or 'PROCEDURE' to indicate the type of routine the row refers to. This column enables privileges to be granted separately for a function and a procedure with the same name.

The Timestamp and Grantor columns currently are unused and are discussed no further here.

The proxies_priv table records information about proxy users. It has these columns:

Scope columns in the grant tables contain strings. They are declared as shown here; the default value for each is the empty string.

Table 5.10. Grant Table Scope Column Types

Column Name Type
Host CHAR(60)
User CHAR(16)
Password CHAR(41)
Db CHAR(64)
Table_name CHAR(64)
Column_name CHAR(64)
Routine_name CHAR(64)

For access-checking purposes, comparisons of User, Password, Db, and Table_name values are case sensitive. Comparisons of Host, Column_name, and Routine_name values are not case sensitive.

In the user, db, and host tables, each privilege is listed in a separate column that is declared as ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.

In the tables_priv, columns_priv, and procs_priv tables, the privilege columns are declared as SET columns. Values in these columns can contain any combination of the privileges controlled by the table. Only those privileges listed in the column value are enabled.

Table 5.11. Set-Type Privilege Column Values

Table Name Column Name Possible Set Elements
tables_priv Table_priv 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger'
tables_priv Column_priv 'Select', 'Insert', 'Update', 'References'
columns_priv Column_priv 'Select', 'Insert', 'Update', 'References'
procs_priv Proc_priv 'Execute', 'Alter Routine', 'Grant'

Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. Administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. Consequently, to determine whether you can perform an administrative operation, the server need consult only the user table.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld server reads the contents of the grant tables into memory when it starts. You can tell it to reload the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in , "When Privilege Changes Take Effect".

When you modify an account's privileges, it is a good idea to verify that the changes set up privileges the way you want. To check the privileges for a given account, use the SHOW GRANTS statement (see , "SHOW GRANTS Syntax"). For example, to determine the privileges that are granted to an account with user name and host name values of bob and pc84.example.com, use this statement:

SHOW GRANTS FOR 'bob'@'pc84.example.com';

Specifying Account Names

MySQL account names consist of a user name and a host name. This enables creation of accounts for users with the same name who can connect from different hosts. This section describes how to write account names, including special values and wildcard rules.

In SQL statements such as CREATE USER, GRANT, and SET PASSWORD, write account names using the following rules:

MySQL stores account names in grant tables in the MariaDB database using separate columns for the user name and host name parts:

For additional detail about grant table structure, see , "Privilege System Grant Tables".

User names and host names have certain special values or wildcard conventions, as described following.

A user name is either a nonblank value that literally matches the user name for incoming connection attempts, or a blank value (empty string) that matches any user name. An account with a blank user name is an anonymous user. To specify an anonymous user in SQL statements, use a quoted empty user name part, such as ''@'localhost'.

The host name part of an account name can take many forms, and wildcards are permitted:

Access Control, Stage 1: Connection Verification

When you attempt to connect to a MariaDB server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

Identity checking is performed using the three user table scope columns (Host, User, and Password). The server accepts the connection only if the Host and User columns in some user table row match the client host name and user name and the client supplies the password specified in that row. The rules for permissible Host and User values are given in , "Specifying Account Names".

If the User column value is nonblank, the user name in an incoming connection must match exactly. If the User value is blank, it matches any user name. If the user table row that matches an incoming connection has a blank user name, the user is considered to be an anonymous user with no name, not a user with the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2).

The Password column can be blank. This is not a wildcard and does not mean that any password matches. It means that the user must connect without specifying a password. If the server authenticates a client using a plugin, the authentication method that the plugin implements may or may not use the password in the Password column. In this case, it is possible that an external password is also used to authenticate to the MariaDB server.

Nonblank Password values in the user table represent encrypted passwords. MariaDB does not store passwords in plaintext form for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the PASSWORD() function). The encrypted password then is used during the connection process when checking whether the password is correct. This is done without the encrypted password ever traveling over the connection. See , "User Names and Passwords".

From MySQL's point of view, the encrypted password is the real password, so you should never give anyone access to it. In particular, do not give nonadministrative users read access to tables in the MariaDB database.

The following table shows how various combinations of Host and User values in the user table apply to incoming connections.

Host Value User Value Permissible Connections
'thomas.loc.gov' 'fred' fred, connecting from thomas.loc.gov
'thomas.loc.gov' '' Any user, connecting from thomas.loc.gov
'%' 'fred' fred, connecting from any host
'%' '' Any user, connecting from any host
'%.loc.gov' 'fred' fred, connecting from any host in the loc.gov domain
'x.y.%' 'fred' fred, connecting from x.y.net, x.y.com, x.y.edu, and so on; this is probably not useful
'144.155.166.177' 'fred' fred, connecting from the host with IP address 144.155.166.177
'144.155.166.%' 'fred' fred, connecting from any host in the 144.155.166 class C subnet
'144.155.166.0/255.255.255.0' 'fred' Same as previous example

It is possible for the client host name and user name of an incoming connection to match more than one row in the user table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from thomas.loc.gov by fred.

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means "any host" and is least specific. The empty string '' also means "any host" but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific).

To see how this works, suppose that the user table looks like this:

+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-

When the server reads the table into memory, it sorts the rows using the rules just described. The result after sorting looks like this:

+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-

When a client attempts to connect, the server looks through the sorted rows and uses the first match found. For a connection from localhost by jeffrey, two of the rows from the table match: the one with Host and User values of 'localhost' and '', and the one with values of '%' and 'jeffrey'. The 'localhost' row appears first in sorted order, so that is the one the server uses.

Here is another example. Suppose that the user table looks like this:

+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| % | jeffrey | ...
| thomas.loc.gov | | ...
+----------------+----------+-

The sorted table looks like this:

+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| thomas.loc.gov | | ...
| % | jeffrey | ...
+----------------+----------+-

A connection by jeffrey from thomas.loc.gov is matched by the first row, whereas a connection by jeffrey from any host is matched by the second.Note

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.

If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER() function. (See , "Information Functions".) It returns a value in user_name@host_name format that indicates the User and Host values from the matching user table row. Suppose that jeffrey connects and issues the following query:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+

The result shown here indicates that the matching user table row had a blank User column value. In other words, the server is treating jeffrey as an anonymous user.

Another way to diagnose authentication problems is to print out the user table and sort it by hand to see where the first match is being made.

Access Control, Stage 2: Request Verification

After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, columns_priv, or procs_priv tables. (You may find it helpful to refer to , "Privilege System Grant Tables", which lists the columns present in each of the grant tables.)

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the default database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! It is wise to grant privileges in the user table only to people who need them, such as database administrators. For other users, you should leave all privileges in the user table set to 'N' and grant privileges at more specific levels only. You can grant privileges for particular databases, tables, columns, or routines.

The db and host tables grant database-specific privileges. Values in the scope columns of these tables can take the following forms:

The server reads the db and host tables into memory and sorts them at the same time that it reads the user table. The server sorts the db table based on the Host, Db, and User scope columns, and sorts the host table based on the Host and Db scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds.

The tables_priv, columns_priv, and procs_priv tables grant table-specific, column-specific, and routine-specific privileges. Values in the scope columns of these tables can take the following forms:

The server sorts the tables_priv, columns_priv, and procs_priv tables based on the Host, Db, and User columns. This is similar to db table sorting, but simpler because only the Host column can contain wildcards.

The server uses the sorted tables to verify each request that it receives. For requests that require administrative privileges such as SHUTDOWN or RELOAD, the server checks only the user table row because that is the only table that specifies administrative privileges. The server grants access if the row permits the requested operation and denies access otherwise. For example, if you want to execute mysqladmin shutdown but your user table row does not grant the SHUTDOWN privilege to you, the server denies access without even checking the db or host tables. (They contain no Shutdown_priv column, so there is no need to do so.)

For database-related requests (INSERT, UPDATE, and so on), the server first checks the user's global privileges by looking in the user table row. If the row permits the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db and host tables:

  1. The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user's host name and MariaDB user name. The Db column is matched to the database that the user wants to access. If there is no row for the Host and User, access is denied.
  2. If there is a matching db table row and its Host column is not blank, that row defines the user's database-specific privileges.
  3. If the matching db table row's Host column is blank, it signifies that the host table enumerates which hosts should be permitted access to the database. In this case, a further lookup is done in the host table to find a match on the Host and Db columns. If no host table row matches, access is denied. If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in the db and host table entries; that is, the privileges that are 'Y' in both entries. (This way you can grant general privileges in the db table row and then selectively restrict them on a host-by-host basis using the host table entries.)

After determining the database-specific privileges granted by the db and host table entries, the server adds them to the global privileges granted by the user table. If the result permits the requested operation, access is granted. Otherwise, the server successively checks the user's table and column privileges in the tables_priv and columns_priv tables, adds those to the user's privileges, and permits or denies access based on the result. For stored-routine operations, the server uses the procs_priv table rather than tables_priv and columns_priv.

Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:

global privileges OR (database privileges AND host privileges)
OR table privileges OR column privileges OR routine privileges

It may not be apparent why, if the global user row privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database, table, and column privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT INTO ... SELECT statement, you need both the INSERT and the SELECT privileges. Your privileges might be such that the user table row grants one privilege and the db table row grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the entries in both tables must be combined.

The host table is not affected by the GRANT or REVOKE statements, so it is unused in most MariaDB installations. If you modify it directly, you can use it for some specialized purposes, such as to maintain a list of secure servers on the local network that are granted all privileges.

You can also use the host table to indicate hosts that are not secure. Suppose that you have a machine public.your.domain that is located in a public area that you do not consider secure. You can enable access to all hosts on your network except that machine by using host table entries like this:

+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | % | ... (all privileges set to 'N')
| %.your.domain | % | ... (all privileges set to 'Y')
+--------------------+----+-

When Privilege Changes Take Effect

When mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

A grant table reload affects privileges for each existing client connection as follows:

If the server is started with the --skip-grant-tables option, it does not read the grant tables or implement any access control. Anyone can connect and do anything, which is insecure. To cause a server thus started to read the tables and enable access checking, flush the privileges.

Causes of Access-Denied Errors

If you encounter problems when you try to connect to the MariaDB server, the following items describe some courses of action you can take to correct the problem.

MySQL User Account Management

User Names and Passwords
Adding User Accounts
Removing User Accounts
Setting Account Resource Limits
Assigning Account Passwords
Pluggable Authentication
Proxy Users
Using SSL for Secure Connections
Connecting to MariaDB Remotely from Windows with SSH
Auditing MariaDB Account Activity

This section describes how to set up accounts for clients of your MariaDB server. It discusses the following topics:

See also , "Account Management Statements", which describes the syntax and use for all user-management SQL statements.

User Names and Passwords

MySQL stores accounts in the user table of the MariaDB database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server. The account may also have a password. For information about account representation in the user table, see , "Privilege System Grant Tables". MariaDB 5.6 supports authentication plugins, so it is possible that an account authenticates using some external authentication method. See , "Pluggable Authentication".

There are several distinctions between the way user names and passwords are used by MariaDB and the way they are used by your operating system:

When you install MySQL, the grant tables are populated with an initial set of accounts. The names and access privileges for these accounts are described in , "Securing the Initial MariaDB Accounts", which also discusses how to assign passwords to them. Thereafter, you normally set up, modify, and remove MariaDB accounts using statements such as CREATE USER, GRANT, and REVOKE. See , "Account Management Statements".

When you connect to a MariaDB server with a command-line client, specify the user name and password as necessary for the account that you want to use:

shell> mysql --user=monty --password=password db_name

If you prefer short options, the command looks like this:

shell> mysql -u monty -ppassword db_name

There must be no space between the -p option and the following password value.

If you omit the password value following the --password or -p option on the command line, the client prompts for one.

Specifying a password on the command line should be considered insecure. See , "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line.

For additional information about specifying user names, passwords, and other connection parameters, see , "Connecting to the MariaDB Server".

Adding User Accounts

You can create MariaDB accounts in two ways:

The preferred method is to use account-creation statements because they are more concise and less error-prone than manipulating the grant tables directly. CREATE USER and GRANT are described in , "Account Management Statements".

Another option for creating accounts is to use one of several available third-party programs that offer capabilities for MariaDB account administration. phpMyAdmin is one such program.

The following examples show how to use the mysql client program to set up new accounts. These examples assume that privileges have been set up according to the defaults described in , "Securing the Initial MariaDB Accounts". This means that to make changes, you must connect to the MariaDB server as the MariaDB root user, and the root account must have the INSERT privilege for the MariaDB database and the RELOAD administrative privilege.

As noted in the examples where appropriate, some of the statements will fail if the server's SQL mode has been set to enable certain restrictions. In particular, strict mode (STRICT_TRANS_TABLES, STRICT_ALL_TABLES) and NO_AUTO_CREATE_USER will prevent the server from accepting some of the statements. Workarounds are indicated for these cases. For more information about SQL modes and their effect on grant table manipulation, see , "Server SQL Modes", and , "GRANT Syntax".

First, use the mysql program to connect to the server as the MariaDB root user:

shell> mysql --user=root mysql

If you have assigned a password to the root account, you will also need to supply a --password or -p option, both for this mysql command and for those later in this section.

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
 ->  WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
 ->  WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by these statements have the following properties:

The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a nonempty password.

To check the privileges for an account, use SHOW GRANTS:

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
+-----------------------------------------------------+

As an alternative to CREATE USER and GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables using FLUSH PRIVILEGES:

shell> mysql --user=root mysql
mysql> INSERT INTO user
 -> VALUES('localhost','monty',PASSWORD('some_pass'),
 -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
 -> VALUES('%','monty',PASSWORD('some_pass'),
 -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
 -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
 -> '','','','',0,0,0,0);
mysql> INSERT INTO user SET Host='localhost',User='admin',
 -> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
 -> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

When you create accounts with INSERT, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. Otherwise, the changes go unnoticed until you restart the server. With CREATE USER, FLUSH PRIVILEGES is unnecessary.

The reason for using the PASSWORD() function with INSERT is to encrypt the password. The CREATE USER statement encrypts the password for you, so PASSWORD() is unnecessary.

The 'Y' values enable privileges for the accounts. Depending on your MariaDB version, you may have to use a different number of 'Y' values in the first two INSERT statements. The INSERT statement for the admin account employs the more readable extended INSERT syntax using SET.

In the INSERT statement for the dummy account, only the Host, User, and Password columns in the user table row are assigned values. None of the privilege columns are set explicitly, so MariaDB assigns them all the default value of 'N'. This is equivalent to what CREATE USER does.

If strict SQL mode is enabled, all columns that have no default value must have a value specified. In this case, INSERT statements must explicitly specify values for the ssl_cipher, x509_issuer, and x509_subject columns.

To set up a superuser account, it is necessary only to insert a user table row with all privilege columns set to 'Y'. The user table privileges are global, so no entries in any of the other grant tables are needed.

The next examples create three accounts and give them access to specific databases. Each of them has a user name of custom and password of obscure.

To create the accounts with CREATE USER and GRANT, use the following statements:

shell> mysql --user=root mysql
mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
 ->  ON bankaccount.*
 ->  TO 'custom'@'localhost';
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
 ->  ON expenses.*
 ->  TO 'custom'@'host47.example.com';
mysql> CREATE USER 'custom'@'server.domain' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
 ->  ON customer.*
 ->  TO 'custom'@'server.domain';

The three accounts can be used as follows:

To set up the custom accounts without GRANT, use INSERT statements as follows to modify the grant tables directly:

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
 -> VALUES('localhost','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
 -> VALUES('host47.example.com','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
 -> VALUES('server.domain','custom',PASSWORD('obscure'));
mysql> INSERT INTO db
 -> (Host,Db,User,Select_priv,Insert_priv,
 -> Update_priv,Delete_priv,Create_priv,Drop_priv)
 -> VALUES('localhost','bankaccount','custom',
 -> 'Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
 -> (Host,Db,User,Select_priv,Insert_priv,
 -> Update_priv,Delete_priv,Create_priv,Drop_priv)
 -> VALUES('host47.example.com','expenses','custom',
 -> 'Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
 -> (Host,Db,User,Select_priv,Insert_priv,
 -> Update_priv,Delete_priv,Create_priv,Drop_priv)
 -> VALUES('server.domain','customer','custom',
 -> 'Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

The first three INSERT statements add user table entries that permit the user custom to connect from the various hosts with the given password, but grant no global privileges (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual when you modify the grant tables directly, you must tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.

To create a user who has access from all machines in a given domain (for example, mydomain.com), you can use the "%" wildcard character in the host part of the account name:

mysql> CREATE USER 'myname'@'%.mydomain.com' IDENTIFIED BY 'mypass';

To do the same thing by modifying the grant tables directly, do this:

mysql> INSERT INTO user (Host,User,Password,...)
 -> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;

Removing User Accounts

To remove an account, use the DROP USER statement, which is described in , "DROP USER Syntax".

Setting Account Resource Limits

One means of limiting use of MariaDB server resources is to set the global max_user_connections system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. In addition, setting max_user_connections does not enable management of individual accounts. Both types of control are of interest to many MariaDB administrators, particularly those working for Internet Service Providers.

In MariaDB 5.6, you can limit use of the following server resources for individual accounts:

Any statement that a client can issue counts against the query limit (unless its results are served from the query cache). Only statements that modify databases or tables count against the update limit.

An "account" in this context corresponds to a row in the mysql.user table. That is, a connection is assessed against the User and Host values in the user table row that applies to the connection. For example, an account 'usera'@'%.example.com' corresponds to a row in the user table that has User and Host values of usera and %.example.com, to permit usera to connect from any host in the example.com domain. In this case, the server applies resource limits in this row collectively to all connections by usera from any host in the example.com domain because all such connections use the same account.

Before MariaDB 5.0.3, an "account" was assessed against the actual host from which a user connects. This older method accounting may be selected by starting the server with the --old-style-user-limits option. In this case, if usera connects simultaneously from host1.example.com and host2.example.com, the server applies the account resource limits separately to each connection. If usera connects again from host1.example.com, the server applies the limits for that connection together with the existing connection from that host.

To set resource limits for an account, use the GRANT statement (see , "GRANT Syntax"). Provide a WITH clause that names each resource to be limited. The default value for each limit is zero (no limit). For example, to create a new account that can access the customer database, but only in a limited fashion, issue these statements:

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
 ->  WITH MAX_QUERIES_PER_HOUR 20
 ->  MAX_UPDATES_PER_HOUR 10
 ->  MAX_CONNECTIONS_PER_HOUR 5
 ->  MAX_USER_CONNECTIONS 2;

The limit types need not all be named in the WITH clause, but those named can be present in any order. The value for each per-hour limit should be an integer representing a count per hour. For MAX_USER_CONNECTIONS, the limit is an integer representing the maximum number of simultaneous connections by the account. If this limit is set to zero, the global max_user_connections system variable value determines the number of simultaneous connections. If max_user_connections is also zero, there is no limit for the account.

To modify existing limits for an account, use a GRANT USAGE statement at the global level (ON *.*). The following statement changes the query limit for francis to 100:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
 -> WITH MAX_QUERIES_PER_HOUR 100;

The statement modifies only the limit value specified and leaves the account otherwise unchanged.

To remove a limit, set its value to zero. For example, to remove the limit on how many times per hour francis can connect, use this statement:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
 -> WITH MAX_CONNECTIONS_PER_HOUR 0;

As mentioned previously, the simultaneous-connection limit for an account is determined from the MAX_USER_CONNECTIONS limit and the max_user_connections system variable. Suppose that the global max_user_connections value is 10 and three accounts have resource limits specified with GRANT:

GRANT ... TO 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0;
GRANT ... TO 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5;
GRANT ... TO 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;

user1 has a connection limit of 10 (the global max_user_connections value) because it has a zero MAX_USER_CONNECTIONS limit). user2 and user3 have connection limits of 5 and 20, respectively, because they have nonzero MAX_USER_CONNECTIONS limits.

The server stores resource limits for an account in the user table row corresponding to the account. The max_questions, max_updates, and max_connections columns store the per-hour limits, and the max_user_connections column stores the MAX_USER_CONNECTIONS limit. (See , "Privilege System Grant Tables".)

Resource-use counting takes place when any account has a nonzero limit placed on its use of any of the resources.

As the server runs, it counts the number of times each account uses resources. If an account reaches its limit on number of connections within the last hour, further connections for the account are rejected until that hour is up. Similarly, if the account reaches its limit on the number of queries or updates, further queries or updates are rejected until the hour is up. In all such cases, an appropriate error message is issued.

Resource counting is done per account, not per client. For example, if your account has a query limit of 50, you cannot increase your limit to 100 by making two simultaneous client connections to the server. Queries issued on both connections are counted together.

The current per-hour resource-use counts can be reset globally for all accounts, or individually for a given account:

Counter resets do not affect the MAX_USER_CONNECTIONS limit.

All counts begin at zero when the server starts; counts are not carried over through a restart.

For the MAX_USER_CONNECTIONS limit, an edge case can occur if the account currently has open the maximum number of connections permitted to it: A disconnect followed quickly by a connect can result in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) if the server has not fully processed the disconnect by the time the connect occurs. When the server finishes disconnect processing, another connection will once more be permitted.

Assigning Account Passwords

Required credentials for clients that connect to the MariaDB server can include a password. This section describes how to assign passwords for MariaDB accounts. In MariaDB 5.6, it is also possible for clients to authenticate using plugins. For information, see , "Pluggable Authentication".

To assign a password when you create a new account with CREATE USER, include an IDENTIFIED BY clause:

mysql> CREATE USER 'jeffrey'@'localhost'
 -> IDENTIFIED BY 'mypass';

To assign or change a password for an existing account, one way is to issue a SET PASSWORD statement:

mysql> SET PASSWORD FOR
 -> 'jeffrey'@'localhost' = PASSWORD('mypass');

MySQL stores passwords in the user table in the MariaDB database. Only users such as root that have update access to the MariaDB database can change the password for other users. If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:

mysql> SET PASSWORD = PASSWORD('mypass');

You can also use a GRANT USAGE statement at the global level (ON *.*) to assign a password to an account without affecting the account's current privileges:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost'
 -> IDENTIFIED BY 'mypass';

To assign a password from the command line, use the mysqladmin command:

shell> mysqladmin -u user_name -h host_name password 'newpwd'

The account for which this command sets the password is the one with a user table row that matches user_name in the User column and the client host from which you connect in the Host column.

It is preferable to assign passwords using one of the preceding methods, but it is also possible to modify the user table directly. In this case, you must also use FLUSH PRIVILEGES to cause the server to reread the grant tables. Otherwise, the change remains unnoticed by the server until you restart it.

During authentication when a client connects to the server, MariaDB treats the password in the user table as an encrypted hash value (the value that PASSWORD() would return for the password). When assigning a password to an account, it is important to store an encrypted value, not the plaintext password. Use the following guidelines:

In MariaDB 5.6, enabling the read-only system variable prevents the use of the SET PASSWORD statement by any user not having the SUPER privilege.Note

PASSWORD() encryption differs from Unix password encryption. See , "User Names and Passwords".

Pluggable Authentication

The Native Authentication Plugins
The Clear-Text Client-Side Authentication Plugin
The Socket Peer-Credential Authentication Plugin
The Test Authentication Plugin

When a client connects to the MariaDB server, the server uses the user name provided by the client and the client host to select the appropriate account row from the mysql.user table. It then uses this row to authenticate the client.

In MariaDB 5.6, the server authenticates clients using plugins, as follows:

Pluggable authentication enables two important capabilities:

Several authentication plugins are available in MySQL. The following sections provide details about specific plugins.

Note

For information about current restrictions on the use of pluggable authentication, including which connectors support which plugins, see "Restrictions on Pluggable Authentication".

Third-party connector developers should read that section to determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.

If you are interested in writing your own authentication plugins, see , "Writing Authentication Plugins".

In general, pluggable authentication uses corresponding plugins on the server and client sides, so you use a given authentication method like this:

The remainder of this section provides general instructions for installing and using authentication plugins. The instructions use an an example authentication plugin included in MariaDB distributions (see , "The Test Authentication Plugin"). The procedure is similar for other authentication plugins; substitute the appropriate plugin and file names.

The example authentication plugin has these characteristics:

Install and use the example authentication plugin as follows:

  1. Make sure that the plugin library is installed on the server and client hosts.
  2. Install the server-side test plugin at server startup or at runtime:

    • To install the plugin at startup, use the --plugin-load option. For example, use these lines in a my.cnf option file:

      [mysqld]
      plugin-load=test_plugin_server=auth_test_plugin.so
      

      With this plugin-loading method, the option must be given each time you start the server. The plugin is not installed if you omit the option.

    • To install the plugin at runtime, use the INSTALL PLUGIN statement:

      mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';
      

      This installs the plugin permanently and need be done only once.

      PAM authentication, when not done through proxy users or groups, requires the MariaDB account to have the same user name as the Unix account. Because MariaDB user names are limited to 16 characters (see , "Privilege System Grant Tables"), this limits PAM nonproxy authentication to Unix accounts with names of at most 16 characters.

  3. Verify that the plugin is installed. For example, use SHOW PLUGINS:

    mysql> SHOW PLUGINS\G
    ...
    *************************** 21. row ***************************
     Name: test_plugin_server
     Status: ACTIVE
     Type: AUTHENTICATION Library: auth_test_plugin.so License: GPL
    

    For other ways to check the plugin, see , "Obtaining Server Plugin Information".

  4. To specify that a MariaDB user must be authenticated using the plugin, name it in the IDENTIFIED WITH clause of the CREATE USER statement that creates the user:

    CREATE USER 'testuser'@'localhost' IDENTIFIED WITH test_plugin_server;
    
  5. Connect to the server using a client program. The test plugin authenticates the same way as native MariaDB authentication, so provide the usual --user and --password options that you normally use to connect to the server. For example:

    shell> mysql --user=your_name --password=your_pass
    

    For connections by testuser, the server sees that the account must be authenticated using the server-side plugin named test_plugin_server and communicates to the client program which client-side plugin it must use-in this case, auth_test_plugin.

    In the case that the account uses the authentication method that is the default for both the server and the client program, the server need not communicate to the client which plugin to use, and a round trip in client/server negotiation can be avoided. Currently this is true for accounts that use native MariaDB authentication (mysql_native_password).

    The --default-auth=plugin-name option can be specified on the mysql command line to make explicit which client-side plugin the program can expect to use, although the server will override this if the user account requires a different plugin.

    If mysql does not find the plugin, specify a --plugin-dir=dir_name option to indicate where the plugin is located.

Note

If you start the server with the --skip-grant-tables option, authentication plugins are not used even if loaded because the server performs no client authentication and permits any client to connect. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

The Native Authentication Plugins

MySQL includes two plugins that implement the same kind of native authentication that older servers provide; that is, authentication against passwords stored in the Password column of the mysql.user table:

The native authentication plugins are backward compatible. Clients older than MariaDB 5.5.7 do not support authentication plugins but use native authentication, so they can connect to servers from 5.5.7 and up.

The following tables show the plugin names. Both are considered to implement native authentication even though only one has "native" in the name.

Table 5.12. MariaDB Native Password Authentication Plugin

Server-side plugin name mysql_native_password
Client-side plugin name mysql_native_password
Library object file name None (built in)

Table 5.13. MariaDB Native Old-Password Authentication Plugin

Server-side plugin name mysql_old_password
Client-side plugin name mysql_old_password
Library object file name None (built in)

Each plugin exists in both client and server form. MariaDB client programs use mysql_native_password by default. The --default-auth option can be used to specify either plugin explicitly:

shell> mysql --default-auth=mysql_native_password ...
shell> mysql --default-auth=mysql_old_password ...

The server-side plugins are built into the server and cannot be disabled by unloading them. The client-side plugins are built into the libmysql client library as of MariaDB 5.5.7 and available to any program linked against libmysql from that version or newer.

For general information about pluggable authentication in MySQL, see , "Pluggable Authentication".

The Clear-Text Client-Side Authentication Plugin

As of MariaDB 5.6.2, a client-side authentication plugin is available that sends the password to the server without hashing or encryption. This plugin is built into the MariaDB client library.

The following table shows the plugin name.

Table 5.14. MariaDB Clear Text Authentication Plugin

Server-side plugin name None, see discussion
Client-side plugin name mysql_clear_password
Library object file name None (built in)

With native MariaDB authentication, the client performs one-way hashing on the password before sending it to the server. This enables the client to avoid sending the password in clear text. See , "Password Hashing in MySQL". However, because the hash algorithm is one way, the original password cannot be recovered on the server side.

One-way hashing cannot be done for authentication schemes that require the server to receive the password as entered on the client side. In such cases, the mysql_clear_password client-side plugin can be used to send the password to the server in clear text. There is no corresponding server-side plugin. Rather, the client-side plugin can be used by any server-side plugin that needs a clear text password.

For general information about pluggable authentication in MySQL, see , "Pluggable Authentication".Note

Sending passwords in clear text may be a security problem in some configurations. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MariaDB Server using a method that protects the password. Possibilities include SSL (see , "Using SSL for Secure Connections"), IPsec, or a private network.

The Socket Peer-Credential Authentication Plugin

As of MariaDB 5.6.2, a server-side authentication plugin is available that authenticates clients that connect from the local host through the Unix socket file.

The source code for this plugin can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file location is the directory named by the plugin_dir system variable. For installation information, see , "Pluggable Authentication".

Table 5.15. MariaDB Socket Peer-Credential Authentication Plugin

Server-side plugin name auth_socket
Client-side plugin name None, see discussion
Library object file name auth_socket.so

The auth_socket authentication plugin authenticates clients that connect from the local host through the Unix socket file. The plugin uses the SO_PEERCRED socket option to obtain information about the user running the client program. The plugin checks whether the user name matches the MariaDB user name specified by the client program to the server, and permits the connection only if the names match. The plugin can be built only on systems that support the SO_PEERCRED option, such as Linux.

Suppose that a MariaDB account is created for a user named valerie who is to be authenticated by the auth_socket plugin for connections from the local host through the socket file:

CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;

If a user on the local host with a login name of stefanie invokes mysql with the option --user=valerie to connect through the socket file, the server uses auth_socket to authenticate the client. The plugin determines that the --user option value (valerie) differs from the client user's name (stephanie) and refuses the connection. If a user named valerie tries the same thing, the plugin finds that the user name and the MariaDB user name are both valerie and permits the connection. However, the plugin refuses the connection even for valerie if the connection is made using a different protocol, such as TCP/IP.

For general information about pluggable authentication in MySQL, see , "Pluggable Authentication".

The Test Authentication Plugin

MySQL includes a test plugin that authenticates using MariaDB native authentication, but is a loadable plugin (not built in) and must be installed prior to use. It can authenticate against either normal or older (shorter) password hash values.

This plugin is intended for testing and development purposes, and not for use in production environments. The test plugin source code is separate from the server source, unlike the built-in native plugin, so it can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file location is the directory named by the plugin_dir system variable. For installation information, see , "Pluggable Authentication".

Table 5.16. MariaDB Test Authentication Plugin

Server-side plugin name test_plugin_server
Client-side plugin name auth_test_plugin
Library object file name auth_test_plugin.so

Because the test plugin authenticates the same way as native MariaDB authentication, provide the usual --user and --password options that you normally use for accounts that use native authentication when you connect to the server. For example:

shell> mysql --user=your_name --password=your_pass

For general information about pluggable authentication in MySQL, see , "Pluggable Authentication".

Proxy Users

When authentication to the MariaDB server occurs through an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to have the privileges of the second user. In other words, the external user is a "proxy user" (a user who can impersonate or become known as another user) and the second user is a "proxied user" (a user whose identity can be taken on by a proxy user).

This section describes how the proxy user capability works. For general information about authentication plugins, see , "Pluggable Authentication". If you are interested in writing your own authentication plugins that support proxy users, see , "Implementing Proxy User Support in Authentication Plugins".

For proxying to occur, these conditions must be satisfied:

Consider the following definitions:

CREATE USER 'empl_external'@'localhost'
 IDENTIFIED WITH auth_plugin AS 'auth_string';
CREATE USER 'employee'@'localhost'
 IDENTIFIED BY 'employee_pass';
GRANT PROXY
 ON 'employee'@'localhost'
 TO 'empl_external'@'localhost';

When a client connects as empl_external from the local host, MariaDB uses auth_plugin to perform authentication. If auth_plugin returns the employee user name to the server (based on the content of 'auth_string' and perhaps by consulting some external authentication system), that serves as a request to the server to treat this client, for purposes of privilege checking, as the employee local user.

In this case, empl_external is the proxy user and employee is the proxied user.

The server verifies that proxy authentication for employee is possible for the empl_external user by checking whether empl_external has the PROXY privilege for employee. (If this privilege had not been granted, an error would occur.)

When proxying occurs, the USER() and CURRENT_USER() functions can be used to see the difference between the connecting user and the account whose privileges apply during the current session. For the example just described, those functions return these values:

mysql> SELECT USER(), CURRENT_USER();
+-------------------------+--------------------+
| USER() | CURRENT_USER() |
+-------------------------+--------------------+
| empl_external@localhost | employee@localhost |
+-------------------------+--------------------+

The IDENTIFIED WITH clause that names the authentication plugin may be followed by an AS clause specifying a string that the server passes to the plugin when the user connects. It is up to each plugin whether the AS clause is required. If it is required, the format of the authentication string depends on how the plugin intends to use it. Consult the documentation for a given plugin for information about the authentication string values it accepts.

Granting the Proxy Privilege

A special PROXY privilege is needed to enable an external user to connect as and have the privileges of another user. To grant this privilege, use the GRANT statement. For example:

GRANT PROXY ON 'proxied_user' TO 'proxy_user';

proxy_user must represent a valid externally authenticated MariaDB user at connection time or connection attempts fail. proxied_user must represent a valid locally authenticated user at connection time or connection attempts fail.

The corresponding REVOKE syntax is:

REVOKE PROXY ON 'proxied_user' FROM 'proxy_user';

MySQL GRANT and REVOKE syntax extensions work as usual. For example:

GRANT PROXY ON 'a' TO 'b', 'c', 'd';
GRANT PROXY ON ''@'' TO 'd';
GRANT PROXY ON 'a' TO 'd' IDENTIFIED BY ...;
GRANT PROXY ON 'a' TO 'd' WITH GRANT OPTION;
REVOKE PROXY ON 'a' FROM 'b', 'c', 'd';

In the preceding example, ''@'' is the default proxy user and means "any user." The default proxy user is discussed later in this section.

The PROXY privilege can be granted in these cases:

The root account created by default during MariaDB installation has the PROXY ... WITH GRANT OPTION privilege for ''@'', that is, for all users. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. For example, root can do this:

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'test';
GRANT PROXY ON ''@'' TO 'admin'@'localhost' WITH GRANT OPTION;

Now the admin user can manage all the specific GRANT PROXY mappings. For example, admin can do this:

GRANT PROXY ON sally TO joe;

Default Proxy Users

To specify that some or all users should connect using a given external plugin, create a "blank" MariaDB user, set it up to use that plugin for authentication, and let the plugin return the real authenticated user name (if different from the blank user). For example, suppose that there exists a hypothetical plugin named ldap_auth that implements LDAP authentication:

CREATE USER ''@'' IDENTIFIED WITH ldap_auth AS 'O=Oracle, OU=MySQL';
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'developer_pass';
CREATE USER 'manager'@'localhost' IDENTIFIED BY 'manager_pass';
GRANT PROXY ON 'manager'@'localhost' TO ''@'';
GRANT PROXY ON 'developer'@'localhost' TO ''@'';

Now assume that a client tries to connect as follows:

mysql --user=myuser --password='myuser_pass' ...

The server will not find myuser defined as a MariaDB user. But because there is a blank user account (''@''), that matches the client user name and host name, the server authenticates the client against that account: The server invokes ldap_auth, passing it myuser and myuser_pass as the user name and password.

If the ldap_auth plugin finds in the LDAP directory that myuser_pass is not the correct password for myuser, authentication fails and the server rejects the connection.

If the password is correct and ldap_auth finds that myuser is a developer, it returns the user name developer to the MariaDB server, rather than myuser. The server verifies that ''@'' can authenticate as developer (because it has the PROXY privilege to do so) and accepts the connection. The session proceeds with myuser having the privileges of developer. (These privileges should be set up by the DBA using GRANT statements, not shown.) The USER() and CURRENT_USER() functions return these values:

mysql> SELECT USER(), CURRENT_USER();
+------------------+---------------------+
| USER() | CURRENT_USER() |
+------------------+---------------------+
| myuser@localhost | developer@localhost |
+------------------+---------------------+

If the plugin instead finds in the LDAP directory that myuser is a manager, it returns manager as the user name and the session proceeds with myuser having the privileges of manager.

mysql> SELECT USER(), CURRENT_USER();
+------------------+-------------------+
| USER() | CURRENT_USER() |
+------------------+-------------------+
| myuser@localhost | manager@localhost |
+------------------+-------------------+

For simplicity, external authentication cannot be multilevel: Neither the credentials for developer nor those for manager are taken into account in the preceding example. However, they are still used if a client tries to authenticate directly against the developer or manager account, which is why those accounts should be assigned passwords.

The default proxy account uses '' in the host part, which matches any host. If you set up a default proxy user, take care to also check for accounts with '%' in the host part, because that also matches any host, but has precedence over '' by the rules that the server uses to sort account rows internally (see , "Access Control, Stage 1: Connection Verification").

Suppose that a MariaDB installation includes these two accounts:

CREATE USER ''@'' IDENTIFIED WITH some_plugin;
CREATE USER ''@'%' IDENTIFIED BY 'some_password';

The intent of the first account is to serve as the default proxy user, to be used to authenticate connections for users who do not otherwise match a more-specific account. The second account might have been created, for example, to enable users without their own account as the anonymous user.

However, in this configuration, the first account will never be used because the matching rules sort ''@'%' ahead of ''@''. For accounts that do not match any more-specific account, the server will attempt to authenticate them against ''@'%' rather than ''@''.

If you intend to create a default proxy user, check for other existing "match any user" accounts that will take precedence over the default proxy user and thus prevent that user from working as intended. It may be necessary to remove any such accounts.

Proxy User System Variables

Two system variables help trace the proxy login process:

Using SSL for Secure Connections

Basic SSL Concepts
Using SSL Connections
SSL Command Options
Setting Up SSL Certificates for MySQL

MySQL supports secure (encrypted) connections between MariaDB clients and the server using the Secure Sockets Layer (SSL) protocol. This section discusses how to use SSL connections. For information on how to require users to use SSL connections, see the discussion of the REQUIRE clause of the GRANT statement in , "GRANT Syntax".

The standard configuration of MariaDB is intended to be as fast as possible, so encrypted connections are not used by default. For applications that require the security provided by encrypted connections, the extra computation to encrypt the data is worthwhile.

MySQL enables encryption on a per-connection basis. You can choose a normal unencrypted connection or a secure encrypted SSL connection according the requirements of individual applications.

Secure connections are based on the OpenSSL API and are available through the MariaDB C API. Replication uses the C API, so secure connections can be used between master and slave servers.

Another way to connect securely is from within an SSH connection to the MariaDB server host. For an example, see , "Connecting to MariaDB Remotely from Windows with SSH".

Basic SSL Concepts

To understand how MariaDB uses SSL, it is necessary to explain some basic SSL and X509 concepts. People who are familiar with these can skip this part of the discussion.

By default, MariaDB uses unencrypted connections between the client and the server. This means that someone with access to the network could watch all your traffic and look at the data being sent or received. They could even change the data while it is in transit between client and server. To improve security a little, you can compress client/server traffic by using the --compress option when invoking client programs. However, this does not foil a determined attacker.

When you need to move information over a network in a secure fashion, an unencrypted connection is unacceptable. Encryption is the way to make any kind of data unreadable. In fact, today's practice requires many additional security elements from encryption algorithms. They should resist many kind of known attacks such as changing the order of encrypted messages or replaying data twice.

SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard.

X509 makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company called a "Certificate Authority" (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can show the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.

If you need more information about SSL, X509, or encryption, use your favorite Internet search engine to search for the keywords in which you are interested.

Using SSL Connections

To use SSL connections between the MariaDB server and client programs, your system must support either OpenSSL or yaSSL and your version of MariaDB must be built with SSL support.

To make it easier to use secure connections, MariaDB is bundled with yaSSL. (MySQL and yaSSL employ the same licensing model, whereas OpenSSL uses an Apache-style license.) yaSSL support initially was available only for a few platforms, but now it is available on all MariaDB platforms supported by Oracle Corporation.

To get secure connections to work with MariaDB and SSL, you must do the following:

  1. If you are not using a binary (precompiled) version of MariaDB that has been built with SSL support, and you are going to use OpenSSL rather than the bundled yaSSL library, install OpenSSL if it has not already been installed. We have tested MariaDB with OpenSSL 0.9.6. To obtain OpenSSL, visit http://www.openssl.org.

    Building MariaDB using OpenSSL requires a shared OpenSSL library, otherwise linker errors occur. Alternatively, build MariaDB using yaSSL.

  2. If you are not using a binary (precompiled) version of MariaDB that has been built with SSL support, configure a MariaDB source distribution to use SSL. When you configure MySQL, invoke CMake like this:

    shell> cmake . -DWITH_SSL=bundled
    

    That configures the distribution to use the bundled yaSSL library. To use the system SSL library instead, specify the option as -DWITH_SSL=system instead. See , "MySQL Source-Configuration Options".

    Note that yaSSL support on Unix platforms requires that either /dev/urandom or /dev/random be available to retrieve true random numbers. For additional information (especially regarding yaSSL on Solaris versions prior to 2.8 and HP-UX), see Bug #13164.

  3. Make sure that the user in the MariaDB database includes the SSL-related columns (beginning with ssl_ and x509_). If your user table does not have these columns, it must be upgraded; see , "mysql_upgrade - Check Tables for MariaDB Upgrade".
  4. To check whether a server binary is compiled with SSL support, invoke it with the --ssl option. An error will occur if the server does not support SSL:

    shell> mysqld --ssl --help
    060525 14:18:52 [ERROR] mysqld: unknown option '--ssl'
    

    To check whether a running mysqld server supports SSL, examine the value of the have_ssl system variable (if you have no have_ssl variable, check for have_openssl):

    mysql> SHOW VARIABLES LIKE 'have_ssl';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | have_ssl | YES |
    +---------------+-------+
    

    If the value is YES, the server supports SSL connections. If the value is DISABLED, the server supports SSL connections but was not started with the appropriate --ssl-xxx options (described later in this section).

To enable SSL connections, the proper SSL-related options must be used (see , "SSL Command Options").

To start the MariaDB server so that it permits clients to connect using SSL, use the options that identify the key and certificate files the server needs when establishing a secure connection:

shell> mysqld --ssl-ca=ca-cert.pem \
 --ssl-cert=server-cert.pem \
 --ssl-key=server-key.pem

To establish a secure connection to a MariaDB server with SSL support, the options that a client must specify depend on the SSL requirements of the user account that the client uses. (See the discussion of the REQUIRE clause in , "GRANT Syntax".)

If the account has no special SSL requirements or was created using a GRANT statement that includes the REQUIRE SSL option, a client can connect securely by using just the --ssl-ca option:

shell> mysql --ssl-ca=ca-cert.pem

To require that a client certificate also be specified, create the account using the REQUIRE X509 option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

shell> mysql --ssl-ca=ca-cert.pem \
 --ssl-cert=client-cert.pem \
 --ssl-key=client-key.pem

In other words, the options are similar to those used for the server. Note that the Certificate Authority certificate has to be the same.

A client can determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher status variable. The value of Ssl_cipher is nonempty if SSL is used, and empty otherwise. For example:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| Ssl_cipher | DHE-RSA-AES256-SHA |
+---------------+--------------------+

For the mysql client, you can use the STATUS or \s command and check the SSL line:

mysql> \s
...
SSL: Not in use
...

Or:

mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
...

To establish a secure connection from within an application program, use the mysql_ssl_set() C API function to set the appropriate certificate options before calling mysql-real-connect(). See , "mysql_ssl_set()". After the connection is established, you can use mysql_get_ssl_cipher() to determine whether SSL is in use. A non-NULL return value indicates a secure connection and names the SSL cipher used for encryption. A NULL return value indicates that SSL is not being used. See , "mysql_get_ssl_cipher()".

SSL Command Options

The following list describes options that are used for specifying the use of SSL, certificate files, and key files. They can be given on the command line or in an option file. These options are not available unless MariaDB has been built with SSL support. See , "Using SSL Connections".

Table 5.17. SSL Option/Variable Summary

Name Cmd-Line Option file System Var Status Var Var Scope Dynamic
have_openssl Yes Global No
have_ssl Yes Global No
skip-ssl Yes Yes
ssl Yes Yes
ssl-ca Yes Yes Global No
- Variable: ssl_ca Yes Global No
ssl-capath Yes Yes Global No
- Variable: ssl_capath Yes Global No
ssl-cert Yes Yes Global No
- Variable: ssl_cert Yes Global No
ssl-cipher Yes Yes Global No
- Variable: ssl_cipher Yes Global No
ssl-crl Yes Yes Global No
- Variable: ssl_crl Yes Global No
ssl-crlpath Yes Yes Global No
- Variable: ssl_crlpath Yes Global No
ssl-key Yes Yes Global No
- Variable: ssl_key Yes Global No
ssl-verify-server-cert Yes Yes

If you use SSL when establishing a client connection, you can tell the client not to authenticate the server certificate by specifying neither --ssl-ca nor --ssl-capath. The server still verifies the client according to any applicable requirements established using GRANT statements for the client, and it still uses any --ssl-ca/--ssl-capath values that were passed to server at startup time.

Setting Up SSL Certificates for MariaDB

This section demonstrates how to set up SSL certificate and key files for use by MariaDB servers and clients. The first example shows a simplified procedure such as you might use from the command line. The second shows a script that contains more detail. The first two examples are intended for use on Unix and both use the openssl command that is part of OpenSSL. The third example describes how to set up SSL files on Windows.

Following the third example, instructions are given for using the files to test SSL connections. You can also use the files as described in , "Using SSL Connections".

Example 1: Creating SSL Files from the Command Line on Unix

The following example shows a set of commands to create MariaDB server and client certificate and key files. You will need to respond to several prompts by the openssl commands. To generate test files, you can press Enter to all prompts. To generate files for production use, you should provide nonempty responses.

# Create clean environment shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts
# Create CA certificate shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 \
 -key ca-key.pem -out ca-cert.pem
# Create server certificate, remove passphrase, and sign it shell> openssl req -newkey rsa:2048 -days 1000 \
 -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 1000 \
 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# Create client certificate, remove passphrase, and sign it shell> openssl req -newkey rsa:2048 -days 1000 \
 -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

After generating the certificates, verify them:

mysql> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Example 2: Creating SSL Files Using a Script on Unix

Here is an example script that shows how to set up SSL certificates for MySQL:

DIR=`pwd`/openssl PRIV=$DIR/private mkdir $DIR $PRIV $DIR/newcerts cp /usr/share/ssl/openssl.cnf $DIR replace ./demoCA $DIR -- $DIR/openssl.cnf
# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)
touch $DIR/index.txt echo '01' > $DIR/serial
#
# Generation of Certificate Authority(CA)
#
openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/ca-cert.pem \
 -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/monty/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MariaDB Foundation
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:
#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
 $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/monty/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MariaDB Foundation
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove the passphrase from the key
#
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
#
# Sign server cert
#
openssl ca -policy policy_anything -out $DIR/server-cert.pem \
 -config $DIR/openssl.cnf -infiles $DIR/server-req.pem
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MariaDB Foundation'
# commonName :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
 $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/monty/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MariaDB Foundation
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove the passphrase from the key
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
#
# Sign client cert
#
openssl ca -policy policy_anything -out $DIR/client-cert.pem \
 -config $DIR/openssl.cnf -infiles $DIR/client-req.pem
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MariaDB Foundation'
# commonName :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create a my.cnf file that you can use to test the certificates
#
cnf=''
cnf='$cnf [client]'
cnf='$cnf ssl-ca=$DIR/ca-cert.pem'
cnf='$cnf ssl-cert=$DIR/client-cert.pem'
cnf='$cnf ssl-key=$DIR/client-key.pem'
cnf='$cnf [mysqld]'
cnf='$cnf ssl-ca=$DIR/ca-cert.pem'
cnf='$cnf ssl-cert=$DIR/server-cert.pem'
cnf='$cnf ssl-key=$DIR/server-key.pem'
echo $cnf | replace ' ' '
' > $DIR/my.cnf
Example 3: Creating SSL Files on Windows

Download OpenSSL for Windows. An overview of available packages can be seen here: http://www.slproweb.com/products/Win32OpenSSL.html Choose the Win32 OpenSSL Light or Win64 OpenSSL Light package, depending on your architecture (32-bit or 64-bit). The default installation location will be C:\OpenSSL-Win32 or C:\OpenSSL-Win64, depending on which package you downloaded. The following instructions assume a default location of C:\OpenSSL-Win32. Modify this as necessary if you are using the 64-bit package.

if a message occurs during setup indicating '...critical component is missing: Microsoft Visual C++ 2008 Redistributables', cancel the setup and download one of the following packages as well, again depending on your architecture (32-bit or 64-bit):

After installing the additional package, restart the OpenSSL setup.

During installation, leave the default C:\OpenSSL-Win32 as the install path, and also leave the default option 'Copy OpenSSL DLL files to the Windows system directory' selected.

When the installation has finished, add C:\OpenSSL-Win32\bin to the Windows System Path variable of your server:

  1. On the Windows desktop, right-click the My Computer icon, and select Properties.
  2. Select the Advanced tab from the System Properties menu that appears, and click the Environment Variables button.
  3. Under System Variables, select Path, then click the Edit button. The Edit System Variable dialogue should appear.
  4. Add ';C:\OpenSSL-Win32\bin' to the end (notice the semicolon).
  5. Press OK 3 times.
  6. Check that OpenSSL was correctly integrated into the Path variable by opening a new command console (Start>Run>cmd.exe) and verifying that OpenSSL is available:

    Microsoft Windows [Version ...]
    Copyright (c) 2006 Microsoft Corporation. All rights reserved.
    C:\Windows\system32>cd \
    C:\>openssl
    OpenSSL> exit <<< If you see the OpenSSL prompt, installation was successful.
    C:\>
    

Depending on your version of Windows, the preceding instructions might be slightly different.

After OpenSSL has been installed, use instructions similar to those from from Example 1 (shown earlier in this section), with the following changes:

Testing SSL Connections

To test SSL connections, start the server as follows, where $DIR is the path name to the directory where the sample my.cnf option file (or my.ini on Windows) is located:

shell> mysqld --defaults-file=$DIR/my.cnf &

Then invoke a client program using the same option file:

shell> mysql --defaults-file=$DIR/my.cnf

If you have a MariaDB source distribution, you can also test your setup by modifying the preceding my.cnf file to refer to the demonstration certificate and key files in the mysql-test/std_data directory of the distribution.

Connecting to MariaDB Remotely from Windows with SSH

This section describes how to get a secure connection to a remote MariaDB server with SSH. The information was provided by David Carlson <dcarlson@mplcomm.com>.

  1. Install an SSH client on your Windows machine. As a user, the best nonfree one I have found is from SecureCRT from http://www.vandyke.com/. Another option is f-secure from http://www.f-secure.com/. You can also find some free ones on Google at http://directory.google.com/Top/Computers/Internet/Protocols/SSH/Clients/Windows/.
  2. Start your Windows SSH client. Set Host_Name = yourmysqlserver_URL_or_IP. Set userid=your_userid to log in to your server. This userid value might not be the same as the user name of your MariaDB account.
  3. Set up port forwarding. Either do a remote forward (Set local_port: 3306, remote_host: yourmysqlservername_or_ip, remote_port: 3306 ) or a local forward (Set port: 3306, host: localhost, remote port: 3306).
  4. Save everything, otherwise you will have to redo it the next time.
  5. Log in to your server with the SSH session you just created.
  6. On your Windows machine, start some ODBC application (such as Access).
  7. Create a new file in Windows and link to MariaDB using the ODBC driver the same way you normally do, except type in localhost for the MariaDB host server, not yourmysqlservername.

At this point, you should have an ODBC connection to MySQL, encrypted using SSH.

Auditing MariaDB Account Activity

Applications can use the following guidelines to perform auditing that ties database activity to MariaDB accounts.

MySQL accounts correspond to rows in the mysql.user table. When a client connects successfully, the server authenticates the client to a particular row in this table. The User and Host column values in this row uniquely identify the account and correspond to the 'user_name'@'host_name' format in which account names are written in SQL statements.

The account used to authenticate a client determines which privileges the client has. Normally, the CURRENT_USER() function can be invoked to determine which account this is for the client user. Its value is constructed from the User and Host columns of the user table row for the account.

However, there are circumstances under which the CURRENT_USER() value corresponds not to the client user but to a different account. This occurs in contexts when privilege checking is not based the client's account:

In those contexts, privilege checking is done against the DEFINER account and CURRENT_USER() refers to that account, not to the account for the client who invoked the stored routine or view or who caused the trigger to activate. To determine the invoking user, you can call the USER() function, which returns a value indicating the actual user name provided by the client and the host from which the client connected. However, this value does not necessarily correspond directly to an account in the user table, because the USER() value never contains wildcards, whereas account values (as returned by CURRENT_USER()) may contain user name and host name wildcards.

For example, a blank user name matches any user, so an account of ''@'localhost' enables clients to connect as an anonymous user from the local host with any user name. If this case, if a client connects as user1 from the local host, USER() and CURRENT_USER() return different values:

mysql> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER() | CURRENT_USER() |
+-----------------+----------------+
| user1@localhost | @localhost |
+-----------------+----------------+

The host name part of an account can contain wildcards, too. If the host name contains a '%' or '_' pattern character or uses netmask notation, the account can be used for clients connecting from multiple hosts and the CURRENT_USER() value will not indicate which one. For example, the account 'user2'@'%.example.com' can be used by user2 to connect from any host in the example.com domain. If user2 connects from remote.example.com, USER() and CURRENT_USER() return different values:

mysql> SELECT USER(), CURRENT_USER();
+--------------------------+---------------------+
| USER() | CURRENT_USER() |
+--------------------------+---------------------+
| user2@remote.example.com | user2@%.example.com |
+--------------------------+---------------------+

If an application must invoke USER() for user auditing (for example, if it does auditing from within triggers) but must also be able to associate the USER() value with an account in the user table, it is necessary to avoid accounts that contain wildcards in the User or Host column. Specifically, do not permit User to be empty (which creates an anonymous-user account), and do not permit pattern characters or netmask notation in Host values. All accounts must have a nonempty User value and literal Host value.

With respect to the previous examples, the ''@'localhost' and 'user2'@'%.example.com' accounts should be changed not to use wildcards:

RENAME USER ''@'localhost' TO 'user1'@'localhost';
RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';

If user2 must be able to connect from several hosts in the example.com domain, there should be a separate account for each host.

To extract the user name or host name part from a CURRENT-USER() or USER() value, use the SUBSTRING() function:

mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',1);
+---------------------------------------+
| SUBSTRING_INDEX(CURRENT_USER(),'@',1) |
+---------------------------------------+
| user1 |
+---------------------------------------+
mysql> SELECT SUBSTRING_INDEX(CURRENT_USER(),'@',-1);
+----------------------------------------+
| SUBSTRING_INDEX(CURRENT_USER(),'@',-1) |
+----------------------------------------+
| localhost |
+----------------------------------------+

Running Multiple MariaDB Instances on One Machine

Setting Up Multiple Data Directories
Running Multiple MariaDB Instances on Windows
Running Multiple MariaDB Instances on Unix
Using Client Programs in a Multiple-Server Environment

In some cases, you might want to run multiple instances of MariaDB on a single machine. You might want to test a new MariaDB release while leaving an existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet Service Provider that wants to provide independent MariaDB installations for different customers.)

It is possible to use a different MariaDB server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MariaDB 5.5 and one from MariaDB 5.6, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases.

Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances. Parameters can be set on the command line, in option files, or by setting environment variables. See , "Specifying Program Options". To see the values used by a given instance, connect to it and execute a SHOW VARIABLES statement.

The primary resource managed by a MariaDB instance is the data directory. Each instance should use a different data directory, the location of which is specified using the --datadir=path option. For methods of configuring each instance with its own data directory, and warnings about the dangers of failing to do so, see , "Setting Up Multiple Data Directories".

In addition to using different data directories, several other options must have different values for each server instance:

If you use the following log file options, their values must differ for each server:

For further discussion of log file options, see , "MySQL Server Logs".

To achieve better performance, you can specify the following option differently for each server, to spread the load between several physical disks:

Having different temporary directories also makes it easier to determine which MariaDB server created any given temporary file.

If you have multiple MariaDB installations in different locations, you can specify the base directory for each installation with the --basedir=path option. This causes each instance to automatically use a different data directory, log files, and PID file because the default for each of those parameters is relative to the base directory. In that case, the only other options you need to specify are the --socket and --port options. Suppose that you install different versions of MariaDB using tar file binary distributions. These install in different locations, so you can start the server for each installation using the command bin/mysqld_safe under its corresponding base directory. mysqld_safe determines the proper --basedir option to pass to mysqld, and you need specify only the --socket and --port options to mysqld_safe.

As discussed in the following sections, it is possible to start additional servers by specifying appropriate command options or by setting environment variables. However, if you need to run multiple servers on a more permanent basis, it is more convenient to use option files to specify for each server those option values that must be unique to it. The --defaults-file option is useful for this purpose.

Setting Up Multiple Data Directories

Each MariaDB Instance on a machine should have its own data directory. The location is specified using the --datadir=path option.

There are different methods of setting up a data directory for a new instance:

The following discussion provides more detail about each method.Warning

Normally, you should never have two servers that update data in the same databases. This may lead to unpleasant surprises if your operating system does not support fault-free system locking. If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers try to log to the same files.

Even when the preceding precautions are observed, this kind of setup works only with MyISAM and MERGE tables, and not with any of the other storage engines. Also, this warning against sharing a data directory among servers always applies in an NFS environment. Permitting multiple MariaDB servers to access a common data directory over NFS is a very bad idea. The primary problem is that NFS is the speed bottleneck. It is not meant for such use. Another risk with NFS is that you must devise a way to ensure that two or more servers do not interfere with each other. Usually NFS file locking is handled by the lockd daemon, but at the moment there is no platform that performs locking 100% reliably in every situation.

Create a New Data Directory

With this method, the data directory will be in the same state as when you first install MySQL. It will have the default set of MariaDB accounts and no user data.

On Unix, initialize the data directory by running mysql_install_db. See , "Unix Postinstallation Procedures".

On Windows, the data directory is included in the MariaDB distribution:

Copy an Existing Data Directory

With this method, any MariaDB accounts or user data present in the data directory are carried over to the new data directory.

  1. Stop the existing MariaDB instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes to disk.
  2. Copy the data directory to the location where the new data directory should be.
  3. Copy the my.cnf or my.ini option file used by the existing instance. This serves as a basis for the new instance.
  4. Modify the new option file so that any pathnames referring to the original data directory refer to the new data directory. Also, modify any other options that must be unique per instance, such as the TCP/IP port number and the log files. For a list of parameters that must be unique per instance, see , "Running Multiple MariaDB Instances on One Machine".
  5. Start the new instance, telling it to use the new option file.

Running Multiple MariaDB Instances on Windows

Starting Multiple MariaDB Instances at the Windows Command Line
Starting Multiple MariaDB Instances as Windows Services

You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters, or by installing several servers as Windows services and running them that way. General instructions for running MariaDB from the command line or as a service are given in , "Installing MariaDB on Microsoft Windows". The following sections describe how to start each server with different values for those options that must be unique per server, such as the data directory. These options are listed in , "Running Multiple MariaDB Instances on One Machine".

Starting Multiple MariaDB Instances at the Windows Command Line

The procedure for starting a single MariaDB server manually from the command line is described in , "Starting MariaDB from the Windows Command Line". To start multiple servers this way, you can specify the appropriate options on the command line or in an option file. It is more convenient to place the options in an option file, but it is necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the file name with a --defaults-file option when you run it.

Suppose that you want to run mysqld on port 3307 with a data directory of C:\mydata1, and mysqld-debug on port 3308 with a data directory of C:\mydata2. Use this procedure:

  1. Make sure that each data directory exists, including its own copy of the MariaDB database that contains the grant tables.
  2. Create two option files. For example, create one file named C:\my-opts1.cnf that looks like this:

    [mysqld]
    datadir = C:/mydata1
    port = 3307
    

    Create a second file named C:\my-opts2.cnf that looks like this:

    [mysqld]
    datadir = C:/mydata2
    port = 3308
    
  3. Use the --defaults-file option to start each server with its own option file:

    C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnf
    C:\> C:\mysql\bin\mysqld-debug --defaults-file=C:\my-opts2.cnf
    

    Each server starts in the foreground (no new prompt appears until the server exits later), so you will need to issue those two commands in separate console windows.

To shut down the servers, connect to each using the appropriate port number:

C:\> C:\mysql\bin\mysqladmin --port=3307 shutdown
C:\> C:\mysql\bin\mysqladmin --port=3308 shutdown

Servers configured as just described permit clients to connect over TCP/IP. If your version of Windows supports named pipes and you also want to permit named-pipe connections, use the mysqld or mysqld-debug server and specify options that enable the named pipe and specify its name. Each server that supports named-pipe connections must use a unique pipe name. For example, the C:\my-opts1.cnf file might be written like this:

[mysqld]
datadir = C:/mydata1
port = 3307
enable-named-pipe socket = mypipe1

Modify C:\my-opts2.cnf similarly for use by the second server. Then start the servers as described previously.

A similar procedure applies for servers that you want to permit shared-memory connections. Enable such connections with the --shared-memory option and specify a unique shared-memory name for each server with the --shared-memory-base-name option.

Starting Multiple MariaDB Instances as Windows Services

On Windows, a MariaDB server can run as a Windows service. The procedures for installing, controlling, and removing a single MariaDB service are described in , "Starting MariaDB as a Windows Service".

To set up multiple MariaDB services, you must make sure that each instance uses a different service name in addition to the other parameters that must be unique per instance.

For the following instructions, suppose that you want to run the mysqld server from two different versions of MariaDB that are installed at C:\mysql-5.5.9 and C:\mysql-5.6.6, respectively. (This might be the case if you are running 5.5.9 as your production server, but also want to conduct tests using 5.6.6.)

To install MariaDB as a Windows service, use the --install or --install-manual option. For information about these options, see , "Starting MariaDB as a Windows Service".

Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, shut down and remove any existing MariaDB services.

To remove multiple services, use mysqld --remove for each one, specifying a service name following the --remove option. If the service name is the default (MariaDB), you can omit it.

Running Multiple MariaDB Instances on Unix

One way is to run multiple MariaDB instances on Unix is to compile different servers with different default TCP/IP ports and Unix socket files so that each one listens on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server.

Assume that an existing 5.5 server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new 5.6.6 server to have different operating parameters, use a CMake command something like this:

shell> cmake . -DMYSQL_TCP_PORT=port_number \
 -DMYSQL_UNIX_ADDR=file_name \
 -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.6

Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file path name, and the CMAKE_INSTALL_PREFIX value should specify an installation directory different from the one under which the existing MariaDB installation is located.

If you have a MariaDB server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket file name:

shell> mysqladmin --host=host_name --port=port_number variables

With the information displayed by that command, you can tell what option values not to use when configuring an additional server.

If you specify localhost as the host name, mysqladmin defaults to using a Unix socket file connection rather than TCP/IP. To explicitly specify the connection protocol, use the --protocol={TCP|SOCKET|PIPE|MEMORY} option.

You need not compile a new MariaDB server just to start with a different Unix socket file and TCP/IP port number. It is also possible to use the same server binary and start each invocation of it with different parameter values at runtime. One way to do so is by using command-line options:

shell> mysqld_safe --socket=file_name --port=port_number

To start a second server, provide different --socket and --port option values, and pass a --datadir=path option to mysqld_safe so that the server uses a different data directory.

Alternatively, put the options for each server in a different option file, then start each server using a --defaults-file option that specifies the path to the appropriate option file. For example, if the option files for two server instances are named /usr/local/mysql/my.cnf and /usr/local/mysql/my.cnf2, start the servers like this: command:

shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf
shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2

Another way to achieve a similar effect is to use environment variables to set the Unix socket file name and TCP/IP port number:

shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> mysql_install_db --user=mysql
shell> mysqld_safe --datadir=/path/to/datadir &

This is a quick way of starting a second server to use for testing. The nice thing about this method is that the environment variable settings apply to any client programs that you invoke from the same shell. Thus, connections for those clients are automatically directed to the second server.

, "Environment Variables", includes a list of other environment variables you can use to affect MariaDB programs.

On Unix, the mysqld_multi script provides another way to start multiple servers. See , "mysqld_multi - Manage Multiple MariaDB Servers".

Using Client Programs in a Multiple-Server Environment

To connect with a client program to a MariaDB server that is listening to different network interfaces from those compiled into your client, you can use one of the following methods:

Tracing mysqld Using DTrace

mysqld DTrace Probe Reference

The DTrace probes in the MariaDB server are designed to provide information about the execution of queries within MariaDB and the different areas of the system being utilized during that process. The organization and triggering of the probes means that the execution of an entire query can be monitored with one level of probes (query-start and query-done) but by monitoring other probes you can get successively more detailed information about the execution of the query in terms of the locks used, sort methods and even row-by-row and storage-engine level execution information.

The DTrace probes are organized so that you can follow the entire query process, from the point of connection from a client, through the query execution, row-level operations, and back out again. You can think of the probes as being fired within a specific sequence during a typical client connect/execute/disconnect sequence, as shown in the following figure.

Figure 5.1. The MariaDB Architecture Using Pluggable Storage Engines

DTrace Probe Structure in
 mysqld

Global information is provided in the arguments to the DTrace probes at various levels. Global information, that is, the connection ID and user/host and where relevant the query string, is provided at key levels (connection-start, command-start, query-start, and query-exec-start). As you go deeper into the probes, it is assumed either you are only interested in the individual executions (row-level probes provide information on the database and table name only), or that you will combine the row-level probes with the notional parent probes to provide the information about a specific query. Examples of this will be given as the format and arguments of each probe are provided.

For more information on DTrace and writing DTrace scripts, read the DTrace User Guide.

MySQL 5.6 includes support for DTrace probes on Solaris 10 Update 5 (Solaris 5/08) on SPARC, x86 and x86_64 platforms. Probes are also supported on Mac OS X 10.4 and higher. Enabling the probes should be automatic on these platforms. To explicitly enable or disable the probes during building, use the -DENABLE-DTRACE=1 or -DENABLE_DTRACE=0 option to CMake.

mysqld DTrace Probe Reference

Connection Probes
Command Probes
Query Probes
Query Parsing Probes
Query Cache Probes
Query Execution Probes
Row-Level Probes
Read Row Probes
Index Probes
Lock Probes
Filesort Probes
Statement Probes
Network Probes
Keycache Probes

MySQL supports the following static probes, organized into groups of functionality.

Table 5.18. MariaDB DTrace Probes

Group Probes Introduced
Connection connection-start, connection-done 5.4.0
Command command-start, command-done 5.4.0
Query query-start, query-done 5.4.0
Query Parsing query-parse-start, query-parse-done 5.4.0
Query Cache query-cache-hit, query-cache-miss 5.4.0
Query Execution query-exec-start, query-exec-done 5.4.0
Row Level insert-row-start, insert-row-done 5.4.0
update-row-start, update-row-done 5.4.0
delete-row-start, delete-row-done 5.4.0
Row Reads read-row-start, read-row-done 5.4.0
Index Reads index-read-row-start, index-read-row-done 5.4.0
Lock handler-rdlock-start, handler-rdlock-done 5.4.0
handler-wrlock-start, handler-wrlock-done 5.4.0
handler-unlock-start, handler-unlock-done 5.4.0
Filesort filesort-start, filesort-done 5.4.0
Statement select-start, select-done 5.4.0
insert-start, insert-done 5.4.0
insert-select-start, insert-select-done 5.4.0
update-start, update-done 5.4.0
multi-update-start, multi-update-done 5.4.0
delete-start, delete-done 5.4.0
multi-delete-start, multi-delete-done 5.4.0
Network net-read-start, net-read-done, net-write-start, net-write-done 5.4.0
Keycache keycache-read-start, keycache-read-block, keycache-read-done, keycache-read-hit, keycache-read-miss, keycache-write-start, keycache-write-block, keycache-write-done 5.4.0
Note

When extracting the argument data from the probes, each argument is available as argN, starting with arg0. To identify each argument within the definitions they are provided with a descriptive name, but you must access the information using the corresponding argN parameter.

Connection Probes

The connection-start and connection-done probes enclose a connection from a client, regardless of whether the connection is through a socket or network connection.

connection-start(connectionid, user, host)
connection-done(status, connectionid)

The following D script will quantify and summarize the average duration of individual connections, and provide a count, dumping the information every 60 seconds:

#!/usr/sbin/dtrace -s mysql*:::connection-start
{
 self->start = timestamp;
}
mysql*:::connection-done
/self->start/
{
 @ = quantize(((timestamp - self->start)/1000000));
 self->start = 0;
}
tick-60s
{
 printa(@);
}

When executed on a server with a large number of clients you might see output similar to this:

 1 57413 :tick-60s
 value ------------- Distribution ------------- count
 -1 | 0
 0 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 30011
 1 | 59
 2 | 5
 4 | 20
 8 | 29
 16 | 18
 32 | 27
 64 | 30
 128 | 11
 256 | 10
 512 | 1
 1024 | 6
 2048 | 8
 4096 | 9
 8192 | 8
 16384 | 2
 32768 | 1
 65536 | 1
 131072 | 0
 262144 | 1
 524288 | 0 

Command Probes

The command probes are executed before and after a client command is executed, including any SQL statement that might be executed during that period. Commands include operations such as the initialization of the DB, use of the COM_CHANGE_USER operation (supported by the MariaDB protocol), and manipulation of prepared statements. Many of these commands are used only by the MariaDB client API from various connectors such as PHP and Java.

command-start(connectionid, command, user, host)
command-done(status)

The command-start and command-done probes are best used when combined with the statement probes to get an idea of overall execution time.

Query Probes

The query-start and query-done probes are triggered when a specific query is received by the server and when the query has been completed and the information has been successfully sent to the client.

query-start(query, connectionid, database, user, host)
query-done(status)

You can get a simple report of the execution time for each query using the following D script:

#!/usr/sbin/dtrace -s
#pragma D option quiet dtrace:::BEGIN
{
 printf('%-20s %-20s %-40s %-9s\n', 'Who', 'Database', 'Query', 'Time(ms)');
}
mysql*:::query-start
{
 self->query = copyinstr(arg0);
 self->connid = arg1;
 self->db = copyinstr(arg2);
 self->who = strjoin(copyinstr(arg3),strjoin('@',copyinstr(arg4)));
 self->querystart = timestamp;
}
mysql*:::query-done
{
 printf('%-20s %-20s %-40s %-9d\n',self->who,self->db,self->query,
 (timestamp - self->querystart) / 1000000);
}

When executing the above script you should get a basic idea of the execution time of your queries:

shell> ./query.d Who Database Query Time(ms)
root@localhost test select * from t1 order by i limit 10 0
root@localhost test set global query_cache_size=0 0
root@localhost test select * from t1 order by i limit 10 776
root@localhost test select * from t1 order by i limit 10 773
root@localhost test select * from t1 order by i desc limit 10 795 

Query Parsing Probes

The query parsing probes are triggered before the original SQL statement is parsed and when the parsing of the statement and determination of the execution model required to process the statement has been completed:

query-parse-start(query)
query-parse-done(status)

For example, you could monitor the execution time for parsing a given query using the following D script:

#!/usr/sbin/dtrace -s
#pragma D option quiet mysql*:::query-parse-start
{
 self->parsestart = timestamp;
 self->parsequery = copyinstr(arg0);
}
mysql*:::query-parse-done
/arg0 == 0/
{
 printf('Parsing %s: %d microseconds\n', self->parsequery,((timestamp - self->parsestart)/1000));
}
mysql*:::query-parse-done
/arg0 != 0/
{
 printf('Error parsing %s: %d microseconds\n', self->parsequery,((timestamp - self->parsestart)/1000));
}

In the above script a predicate is used on query-parse-done so that different output is generated based on the status value of the probe.

When running the script and monitoring the execution:

shell> ./query-parsing.d Error parsing select from t1 join (t2) on (t1.i = t2.i) order by t1.s,t1.i limit 10: 36 ms Parsing select * from t1 join (t2) on (t1.i = t2.i) order by t1.s,t1.i limit 10: 176 ms

Query Cache Probes

The query cache probes are fired when executing any query. The query-cache-hit query is triggered when a query exists in the query cache and can be used to return the query cache information. The arguments contain the original query text and the number of rows returned from the query cache for the query. If the query is not within the query cache, or the query cache is not enabled, then the query-cache-miss probe is triggered instead.

query-cache-hit(query, rows)
query-cache-miss(query)

The query cache probes are best combined with a probe on the main query so that you can determine the differences in times between using or not using the query cache for specified queries. For example, in the following D script, the query and query cache information are combined into the information output during monitoring:

#!/usr/sbin/dtrace -s
#pragma D option quiet dtrace:::BEGIN
{
 printf('%-20s %-20s %-40s %2s %-9s\n', 'Who', 'Database', 'Query', 'QC', 'Time(ms)');
}
mysql*:::query-start
{
 self->query = copyinstr(arg0);
 self->connid = arg1;
 self->db = copyinstr(arg2);
 self->who = strjoin(copyinstr(arg3),strjoin('@',copyinstr(arg4)));
 self->querystart = timestamp;
 self->qc = 0;
}
mysql*:::query-cache-hit
{
 self->qc = 1;
}
mysql*:::query-cache-miss
{
 self->qc = 0;
}
mysql*:::query-done
{
 printf('%-20s %-20s %-40s %-2s %-9d\n',self->who,self->db,self->query,(self->qc ? 'Y' : 'N'),
 (timestamp - self->querystart) / 1000000);
}

When executing the script you can see the effects of the query cache. Initially the query cache is disabled. If you set the query cache size and then execute the query multiple times you should see that the query cache is being used to return the query data:

shell> ./query-cache.d root@localhost test select * from t1 order by i limit 10 N 1072
root@localhost set global query_cache_size=262144 N 0
root@localhost test select * from t1 order by i limit 10 N 781
root@localhost test select * from t1 order by i limit 10 Y 0 

Query Execution Probes

The query execution probe is triggered when the actual execution of the query starts, after the parsing and checking the query cache but before any privilege checks or optimization. By comparing the difference between the start and done probes you can monitor the time actually spent servicing the query (instead of just handling the parsing and other elements of the query).

query-exec-start(query, connectionid, database, user, host, exec_type)
query-exec-done(status)
Note

The information provided in the arguments for query-start and query-exec-start are almost identical and designed so that you can choose to monitor either the entire query process (using query-start) or only the execution (using query-exec-start) while exposing the core information about the user, client, and query being executed.

Row-Level Probes

The *row-{start,done} probes are triggered each time a row operation is pushed down to a storage engine. For example, if you execute an INSERT statement with 100 rows of data, then the insert-row-start and insert-row-done probes will be triggered 100 times each, for each row insert.

insert-row-start(database, table)
insert-row-done(status)
update-row-start(database, table)
update-row-done(status)
delete-row-start(database, table)
delete-row-done(status)

The arguments supported by the probes are consistent for the corresponding start and done probes in each case:

Because the row-level probes are triggered for each individual row access, these probes can be triggered many thousands of times each second, which may have a detrimental effect on both the monitoring script and MySQL. The DTrace environment should limit the triggering on these probes to prevent the performance being adversely affected. Either use the probes sparingly, or use counter or aggregation functions to report on these probes and then provide a summary when the script terminates or as part of a query-done or query-exec-done probes.

The following example script summarizes the duration of each row operation within a larger query:

#!/usr/sbin/dtrace -s
#pragma D option quiet dtrace:::BEGIN
{
 printf('%-2s %-10s %-10s %9s %9s %-s \n',
 'St', 'Who', 'DB', 'ConnID', 'Dur ms', 'Query');
}
mysql*:::query-start
{
 self->query = copyinstr(arg0);
 self->who = strjoin(copyinstr(arg3),strjoin('@',copyinstr(arg4)));
 self->db = copyinstr(arg2);
 self->connid = arg1;
 self->querystart = timestamp;
 self->rowdur = 0;
}
mysql*:::query-done
{
 this->elapsed = (timestamp - self->querystart) /1000000;
 printf('%2d %-10s %-10s %9d %9d %s\n',
 arg0, self->who, self->db,
 self->connid, this->elapsed, self->query);
}
mysql*:::query-done
/ self->rowdur /
{
 printf('%34s %9d %s\n', '', (self->rowdur/1000000), '-> Row ops');
}
mysql*:::insert-row-start
{
 self->rowstart = timestamp;
}
mysql*:::delete-row-start
{
 self->rowstart = timestamp;
}
mysql*:::update-row-start
{
 self->rowstart = timestamp;
}
mysql*:::insert-row-done
{
 self->rowdur += (timestamp-self->rowstart);
}
mysql*:::delete-row-done
{
 self->rowdur += (timestamp-self->rowstart);
}
mysql*:::update-row-done
{
 self->rowdur += (timestamp-self->rowstart);
}

Running the above script with a query that inserts data into a table, you can monitor the exact time spent performing the raw row insertion:

St Who DB ConnID Dur ms Query
 0 @localhost test 13 20767 insert into t1(select * from t2)
 4827 -> Row ops

Read Row Probes

The read row probes are triggered at a storage engine level each time a row read operation occurs. These probes are specified within each storage engine (as opposed to the *row-start probes which are in the storage engine interface). These probes can therefore be used to monitor individual storage engine row-level operations and performance. Because these probes are triggered around the storage engine row read interface, they may be hit a significant number of times during a basic query.

read-row-start(database, table, scan_flag)
read-row-done(status)

Index Probes

The index probes are triggered each time a a row is read using one of the indexes for the specified table. The probe is triggered within the corresponding storage engine for the table.

index-read-row-start(database, table)
index-read-row-done(status)

Lock Probes

The lock probes are called whenever an external lock is requested by MariaDB for a table using the corresponding lock mechanism on the table as defined by the table's engine type. There are three different types of lock, the read lock, write lock, and unlock operations. Using the probes you can determine the duration of the external locking routine (that is, the time taken by the storage engine to implement the lock, including any time waiting for another lock to become free) and the total duration of the lock/unlock process.

handler-rdlock-start(database, table)
handler-rdlock-done(status)
handler-wrlock-start(database, table)
handler-wrlock-done(status)
handler-unlock-start(database, table)
handler-unlock-done(status)

You can use arrays to monitor the locking and unlocking of individual tables and then calculate the duration of the entire table lock using the following script:

#!/usr/sbin/dtrace -s
#pragma D option quiet mysql*:::handler-rdlock-start
{
 self->rdlockstart = timestamp;
 this->lockref = strjoin(copyinstr(arg0),strjoin('@',copyinstr(arg1)));
 self->lockmap[this->lockref] = self->rdlockstart;
 printf('Start: Lock->Read %s.%s\n',copyinstr(arg0),copyinstr(arg1));
}
mysql*:::handler-wrlock-start
{
 self->wrlockstart = timestamp;
 this->lockref = strjoin(copyinstr(arg0),strjoin('@',copyinstr(arg1)));
 self->lockmap[this->lockref] = self->rdlockstart;
 printf('Start: Lock->Write %s.%s\n',copyinstr(arg0),copyinstr(arg1));
}
mysql*:::handler-unlock-start
{
 self->unlockstart = timestamp;
 this->lockref = strjoin(copyinstr(arg0),strjoin('@',copyinstr(arg1)));
 printf('Start: Lock->Unlock %s.%s (%d ms lock duration)\n',
 copyinstr(arg0),copyinstr(arg1),
 (timestamp - self->lockmap[this->lockref])/1000000);
}
mysql*:::handler-rdlock-done
{
 printf('End: Lock->Read %d ms\n',
 (timestamp - self->rdlockstart)/1000000);
}
mysql*:::handler-wrlock-done
{
 printf('End: Lock->Write %d ms\n',
 (timestamp - self->wrlockstart)/1000000);
}
mysql*:::handler-unlock-done
{
 printf('End: Lock->Unlock %d ms\n',
 (timestamp - self->unlockstart)/1000000);
}

When executed, you should get information both about the duration of the locking process itself, and of the locks on a specific table:

Start: Lock->Read test.t2
End: Lock->Read 0 ms Start: Lock->Unlock test.t2 (25743 ms lock duration)
End: Lock->Unlock 0 ms Start: Lock->Read test.t2
End: Lock->Read 0 ms Start: Lock->Unlock test.t2 (1 ms lock duration)
End: Lock->Unlock 0 ms Start: Lock->Read test.t2
End: Lock->Read 0 ms Start: Lock->Unlock test.t2 (1 ms lock duration)
End: Lock->Unlock 0 ms Start: Lock->Read test.t2
End: Lock->Read 0 ms

Filesort Probes

The filesort probes are triggered whenever a filesort operation is applied to a table. For more information on filesort and the conditions under which it occurs, see , "ORDER BY Optimization".

filesort-start(database, table)
filesort-done(status, rows)

An example of this is in the following script, which tracks the duration of the filesort process in addition to the duration of the main query:

#!/usr/sbin/dtrace -s
#pragma D option quiet dtrace:::BEGIN
{
 printf('%-2s %-10s %-10s %9s %18s %-s \n',
 'St', 'Who', 'DB', 'ConnID', 'Dur microsec', 'Query');
}
mysql*:::query-start
{
 self->query = copyinstr(arg0);
 self->who = strjoin(copyinstr(arg3),strjoin('@',copyinstr(arg4)));
 self->db = copyinstr(arg2);
 self->connid = arg1;
 self->querystart = timestamp;
 self->filesort = 0;
 self->fsdb = '';
 self->fstable = '';
}
mysql*:::filesort-start
{
 self->filesort = timestamp;
 self->fsdb = copyinstr(arg0);
 self->fstable = copyinstr(arg1);
}
mysql*:::filesort-done
{
 this->elapsed = (timestamp - self->filesort) /1000;
 printf('%2d %-10s %-10s %9d %18d Filesort on %s\n',
 arg0, self->who, self->fsdb,
 self->connid, this->elapsed, self->fstable);
}
mysql*:::query-done
{
 this->elapsed = (timestamp - self->querystart) /1000;
 printf('%2d %-10s %-10s %9d %18d %s\n',
 arg0, self->who, self->db,
 self->connid, this->elapsed, self->query);
}

Executing a query on a large table with an ORDER BY clause that triggers a filesort, and then creating an index on the table and then repeating the same query, you can see the difference in execution speed:

St Who DB ConnID Dur microsec Query
 0 @localhost test 14 11335469 Filesort on t1
 0 @localhost test 14 11335787 select * from t1 order by i limit 100
 0 @localhost test 14 466734378 create index t1a on t1 (i)
 0 @localhost test 14 26472 select * from t1 order by i limit 100

Statement Probes

The individual statement probes are provided to give specific information about different statement types. For the start probes the string of the query is provided as a the only argument. Depending on the statement type, the information provided by the corresponding done probe will differ. For all done probes the status of the operation (0 for success, >0 for failure) is provided. For SELECT, INSERT, INSERT ... (SELECT FROM ...), DELETE, and DELETE FROM t1,t2 operations the number of rows affected is returned.

For UPDATE and UPDATE t1,t2 ... statements the number of rows matched and the number of rows actually changed is provided. This is because the number of rows actually matched by the corresponding WHERE clause, and the number of rows changed can differ. MariaDB does not update the value of a row if the value already matches the new setting.

select-start(query)
select-done(status,rows)
insert-start(query)
insert-done(status,rows)
insert-select-start(query)
insert-select-done(status,rows)
update-start(query)
update-done(status,rowsmatched,rowschanged)
multi-update-start(query)
multi-update-done(status,rowsmatched,rowschanged)
delete-start(query)
delete-done(status,rows)
multi-delete-start(query)
multi-delete-done(status,rows)

The arguments for the statement probes are:

You use these probes to monitor the execution of these statement types without having to monitor the user or client executing the statements. A simple example of this is to track the execution times:

#!/usr/sbin/dtrace -s
#pragma D option quiet dtrace:::BEGIN
{
 printf('%-60s %-8s %-8s %-8s\n', 'Query', 'RowsU', 'RowsM', 'Dur (ms)');
}
mysql*:::update-start, mysql*:::insert-start,
mysql*:::delete-start, mysql*:::multi-delete-start,
mysql*:::multi-delete-done, mysql*:::select-start,
mysql*:::insert-select-start, mysql*:::multi-update-start
{
 self->query = copyinstr(arg0);
 self->querystart = timestamp;
}
mysql*:::insert-done, mysql*:::select-done,
mysql*:::delete-done, mysql*:::multi-delete-done, mysql*:::insert-select-done
/ self->querystart /
{
 this->elapsed = ((timestamp - self->querystart)/1000000);
 printf('%-60s %-8d %-8d %d\n',
 self->query,
 0,
 arg1,
 this->elapsed);
 self->querystart = 0;
}
mysql*:::update-done, mysql*:::multi-update-done
/ self->querystart /
{
 this->elapsed = ((timestamp - self->querystart)/1000000);
 printf('%-60s %-8d %-8d %d\n',
 self->query,
 arg1,
 arg2,
 this->elapsed);
 self->querystart = 0;
}

When executed you can see the basic execution times and rows matches:

Query RowsU RowsM Dur (ms)
select * from t2 0 275 0
insert into t2 (select * from t2) 0 275 9
update t2 set i=5 where i > 75 110 110 8
update t2 set i=5 where i < 25 254 134 12
delete from t2 where i < 5 0 0 0

Another alternative is to use the aggregation functions in DTrace to aggregate the execution time of individual statements together:

#!/usr/sbin/dtrace -s
#pragma D option quiet mysql*:::update-start, mysql*:::insert-start,
mysql*:::delete-start, mysql*:::multi-delete-start,
mysql*:::multi-delete-done, mysql*:::select-start,
mysql*:::insert-select-start, mysql*:::multi-update-start
{
 self->querystart = timestamp;
}
mysql*:::select-done
{
 @statements['select'] = sum(((timestamp - self->querystart)/1000000));
}
mysql*:::insert-done, mysql*:::insert-select-done
{
 @statements['insert'] = sum(((timestamp - self->querystart)/1000000));
}
mysql*:::update-done, mysql*:::multi-update-done
{
 @statements['update'] = sum(((timestamp - self->querystart)/1000000));
}
mysql*:::delete-done, mysql*:::multi-delete-done
{
 @statements['delete'] = sum(((timestamp - self->querystart)/1000000));
}
tick-30s
{
 printa(@statements);
}

The script just shown aggregates the times spent doing each operation, which could be used to help benchmark a standard suite of tests.

 delete 0
 update 0
 insert 23
 select 2484
 delete 0
 update 0
 insert 39
 select 10744
 delete 0
 update 26
 insert 56
 select 10944
 delete 0
 update 26
 insert 2287
 select 15985

Network Probes

The network probes monitor the transfer of information from the MariaDB server and clients of all types over the network. The probes are defined as follows:

net-read-start()
net-read-done(status, bytes)
net-write-start(bytes)
net-write-done(status)

You can use the network probes to monitor the time spent reading from and writing to network clients during execution. The following D script provides an example of this. Both the cumulative time for the read or write is calculated, and the number of bytes. Note that the dynamic variable size has been increased (using the dynvarsize option) to cope with the rapid firing of the individual probes for the network reads/writes.

#!/usr/sbin/dtrace -s
#pragma D option quiet
#pragma D option dynvarsize=4m dtrace:::BEGIN
{
 printf('%-2s %-30s %-10s %9s %18s %-s \n',
 'St', 'Who', 'DB', 'ConnID', 'Dur microsec', 'Query');
}
mysql*:::query-start
{
 self->query = copyinstr(arg0);
 self->who = strjoin(copyinstr(arg3),strjoin('@',copyinstr(arg4)));
 self->db = copyinstr(arg2);
 self->connid = arg1;
 self->querystart = timestamp;
 self->netwrite = 0;
 self->netwritecum = 0;
 self->netwritebase = 0;
 self->netread = 0;
 self->netreadcum = 0;
 self->netreadbase = 0;
}
mysql*:::net-write-start
{
 self->netwrite += arg0;
 self->netwritebase = timestamp;
}
mysql*:::net-write-done
{
 self->netwritecum += (timestamp - self->netwritebase);
 self->netwritebase = 0;
}
mysql*:::net-read-start
{
 self->netreadbase = timestamp;
}
mysql*:::net-read-done
{
 self->netread += arg1;
 self->netreadcum += (timestamp - self->netreadbase);
 self->netreadbase = 0;
}
mysql*:::query-done
{
 this->elapsed = (timestamp - self->querystart) /1000000;
 printf('%2d %-30s %-10s %9d %18d %s\n',
 arg0, self->who, self->db,
 self->connid, this->elapsed, self->query);
 printf('Net read: %d bytes (%d ms) write: %d bytes (%d ms)\n',
 self->netread, (self->netreadcum/1000000),
 self->netwrite, (self->netwritecum/1000000));
}

When executing the above script on a machine with a remote client, you can see that approximately a third of the time spent executing the query is related to writing the query results back to the client.

St Who DB ConnID Dur microsec Query
 0 root@::ffff:192.168.0.108 test 31 3495 select * from t1 limit 1000000
Net read: 0 bytes (0 ms) write: 10000075 bytes (1220 ms)

Keycache Probes

The keycache probes are triggered when using the index key cache used with the MyISAM storage engine. Probes exist to monitor when data is read into the keycache, cached key data is written from the cache into a cached file, or when accessing the keycache.

Keycache usage indicates when data is read or written from the index files into the cache, and can be used to monitor how efficient the memory allocated to the keycache is being used. A high number of keycache reads across a range of queries may indicate that the keycache is too small for size of data being accessed.

keycache-read-start(filepath, bytes, mem_used, mem_free)
keycache-read-block(bytes)
keycache-read-hit()
keycache-read-miss()
keycache-read-done(mem_used, mem_free)
keycache-write-start(filepath, bytes, mem_used, mem_free)
keycache-write-block(bytes)
keycache-write-done(mem_used, mem_free)

When reading data from the index files into the keycache, the process first initializes the read operation (indicated by keycache-read-start), then loads blocks of data (keycache-read-block), and then the read block is either matches the data being identified (keycache-read-hit) or more data needs to be read (keycache-read-miss). Once the read operation has completed, reading stops with the keycache-read-done.

Data will be read from the index file into the keycache only when the specified key is not already within the keycache.

Keycache writes occur when the index information is updated during an INSERT, UPDATE, or DELETE operation, and the cached key information is flushed back to the index file.

Copyright 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices
Prev Next
MySQL Programs Home Chapter 6. Backup and Recovery