To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:

mysqld --verbose --help

To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:

mysqld --no-defaults --verbose --help

To see the current values used by a running server, use the SHOW VARIABLES statement.

Section 5.1.6, “Using System Variables”, discusses the syntax for setting and displaying system variable values.

Section 5.1.6.2, “Dynamic System Variables”, lists the variables that can be set at runtime.

Information on tuning system variables can be found in Section 5.1.1, “Configuring the Server”.

Section 14.14, “InnoDB Startup Options and System Variables”, lists InnoDB system variables.

Section 18.3.3.8.2, “NDB Cluster System Variables”, lists system variables which are specific to MySQL Cluster.

For information on server system variables specific to replication, see Section 17.1.4, “Replication and Binary Logging Options and Variables”.

authentication_windows_log_level

This variable is available only if the authentication_windows Windows authentication plugin is enabled and debugging code is enabled. See Section 6.5.1.6, “The Windows Native Authentication Plugin”.

This variable sets the logging level for the Windows authentication plugin. The following table shows the permitted values.

This variable was added in MySQL 5.6.10.

authentication_windows_use_principal_name

This variable is available only if the authentication_windows Windows authentication plugin is enabled. See Section 6.5.1.6, “The Windows Native Authentication Plugin”.

A client that authenticates using the InitSecurityContext() function should provide a string identifying the service to which it connects (targetName). MySQL uses the principal name (UPN) of the account under which the server is running. The UPN has the form user_id@computer_name and need not be registered anywhere to be used. This UPN is sent by the server at the beginning of authentication handshake.

This variable controls whether the server sends the UPN in the initial challenge. By default, the variable is enabled. For security reasons, it can be disabled to avoid sending the server's account name to a client in clear text. If the variable is disabled, the server always sends a 0x00 byte in the first challenge, the client does not specify targetName, and as a result, NTLM authentication is used.

If the server fails to obtain its UPN (which will happen primarily in environments that do not support Kerberos authentication), the UPN is not sent by the server and NTLM authentication is used.

This variable was added in MySQL 5.6.10.

autocommit

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

By default, client connections begin with autocommit set to 1. To cause clients to begin with a default of 0, set the global autocommit value by starting the server with the --autocommit=0 option. To set the variable using an option file, include these lines:

[mysqld]
autocommit=0

automatic_sp_privileges

When this variable has a value of 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.

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

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

avoid_temporal_upgrade

This variable controls whether ALTER TABLE implicitly upgrades temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). Upgrading such columns requires a table rebuild, which prevents any use of fast alterations that might otherwise apply to the operation to be performed.

This variable is disabled by default. Enabling it causes ALTER TABLE not to rebuild temporal columns and thereby be able to take advantage of possible fast alterations.

This variable was added in MySQL 5.6.24. It is deprecated and will be removed in a future MySQL release.

back_log

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

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

As of MySQL 5.6.6, the default value is based on the following formula, capped to a limit of 900:

50 + (max_connections / 5)

Before 5.6.6, the default is 50.

basedir

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

big_tables

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

bind_address

The value of the --bind-address option. This variable was added in MySQL 5.6.1.

block_encryption_mode

This variable controls the block encryption mode for block-based algorithms such as AES. It affects encryption for AES_ENCRYPT() and AES_DECRYPT().

block_encryption_mode takes a value in aes-keylen-mode format, where keylen is the key length in bits and mode is the encryption mode. The value is not case sensitive. Permitted keylen values are 128, 192, and 256. Permitted encryption modes depend on whether MySQL was compiled using OpenSSL or yaSSL:

For example, this statement causes the AES encryption functions to use a key length of 256 bits and the CBC mode:

SET block_encryption_mode = 'aes-256-cbc';

An error occurs for attempts to set block_encryption_mode to a value containing an unsupported key length or a mode that the SSL library does not support.

This variable was added in MySQL 5.6.17.

bulk_insert_buffer_size

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

character_set_client

The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server. (Many clients support a --default-character-set option to enable this character set to be specified explicitly. See also Section 10.1.4, “Connection Character Sets and Collations”.) The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests:

ucs2, utf16, utf16le, and utf32 cannot be used as a client character set, which means that they also do not work for SET NAMES or SET CHARACTER SET.

character_set_connection

The character set used for literals that do not have a character set introducer and for number-to-string conversion. For information about introducers, see Section 10.1.3.8, “Character Set Introducers”.

character_set_database

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

character_set_filesystem

The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8'.

character_set_results

The character set used for returning query results such as result sets or error messages to the client.

character_set_server

The server's default character set.

character_set_system

The character set used by the server for storing identifiers. The value is always utf8.

character_sets_dir

The directory where character sets are installed.

collation_connection

The collation of the connection character set.

collation_database

The collation used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as collation_server.

collation_server

The server's default collation.

completion_type

The transaction completion type. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.

completion_type affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK. It does not apply to implicit commits resulting from execution of the statements listed in Section 13.3.3, “Statements That Cause an Implicit Commit”. It also does not apply for XA COMMIT, XA ROLLBACK, or when autocommit=1.

concurrent_insert

If AUTO (the default), MySQL permits INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file. If you start mysqld with --skip-new, this variable is set to NEVER.

This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.

See also Section 8.11.3, “Concurrent Inserts”.

connect_timeout

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds.

Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.

core_file

Whether to write a core file if the server crashes. This variable is set by the --core-file option. It was added in MySQL 5.6.2.

datadir

The MySQL data directory. This variable can be set with the --datadir option.

date_format

This variable is unused. It is deprecated as of MySQL 5.6.7 and will be removed in a future MySQL release.

datetime_format

This variable is unused. It is deprecated as of MySQL 5.6.7 and will be removed in a future MySQL release.

debug

This variable indicates the current debugging settings. It is available only for servers built with debugging support. The initial value comes from the value of instances of the --debug option given at server startup. The global and session values may be set at runtime; the SUPER privilege is required, even for the session value.

Assigning a value that begins with + or - cause the value to added to or subtracted from the current value:

mysql> SET debug = 'T';
mysql> SELECT @@debug;
+---------+
| @@debug |
+---------+
| T       |
+---------+

mysql> SET debug = '+P';
mysql> SELECT @@debug;
+---------+
| @@debug |
+---------+
| P:T     |
+---------+

mysql> SET debug = '-P';
mysql> SELECT @@debug;
+---------+
| @@debug |
+---------+
| T       |
+---------+

For more information, see Section 24.5.3, “The DBUG Package”.

debug_sync

This variable is the user interface to the Debug Sync facility. Use of Debug Sync requires that MySQL be configured with the -DENABLE_DEBUG_SYNC=1 CMake option (see Section 2.9.4, “MySQL Source-Configuration Options”). If Debug Sync is not compiled in, this system variable is not available.

The global variable value is read only and indicates whether the facility is enabled. By default, Debug Sync is disabled and the value of debug_sync is OFF. If the server is started with --debug-sync-timeout=N, where N is a timeout value greater than 0, Debug Sync is enabled and the value of debug_sync is ON - current signal followed by the signal name. Also, N becomes the default timeout for individual synchronization points.

The session value can be read by any user and will have the same value as the global variable. The session value can be set by users that have the SUPER privilege to control synchronization points.

For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.

default_storage_engine

The default storage engine. As of MySQL 5.6.3, this variable sets the storage engine for permanent tables only. To set the storage engine for TEMPORARY tables, set the default_tmp_storage_engine system variable.

To see which storage engines are available and enabled, use the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table.

default_storage_engine should be used in preference to storage_engine, which is deprecated.

If you disable the default storage engine at server startup, you must set the default engine for both permanent and TEMPORARY tables to a different engine or the server will not start.

default_tmp_storage_engine

The default storage engine for TEMPORARY tables (created with CREATE TEMPORARY TABLE). To set the storage engine for permanent tables, set the default_storage_engine system variable.

If you disable the default storage engine at server startup, you must set the default engine for both permanent and TEMPORARY tables to a different engine or the server will not start.

default_tmp_storage_engine was added in MySQL 5.6.3.

default_week_format

The default mode value to use for the WEEK() function. See Section 12.7, “Date and Time Functions”.

delay_key_write

This option applies only to MyISAM tables. It can have one of the following values to affect handling of the DELAY_KEY_WRITE table option that can be used in CREATE TABLE statements.

If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the --myisam-recover-options option (for example, --myisam-recover-options=BACKUP,FORCE). See Section 5.1.4, “Server Command Options”, and Section 15.2.1, “MyISAM Startup Options”.