• mysqld --verbose --help

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

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

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

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

The following table lists all available system variables:

For additional system variable information, see these sections:

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

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

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

• autocommit

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

• automatic_sp_privileges

  Variable Name	automatic_sp_privileges
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	boolean
  	Default	TRUE

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

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

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

• back_log

  Command-Line Format	--back_log=#
  Config-File Format	back_log
  Option Sets Variable	Yes, back_log
  Variable Name	back_log
  Variable Scope	Global
  Dynamic Variable	No
  	Permitted Values
  	Type	numeric
  	Default	50
  	Range	1-65535

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

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

• basedir

  Command-Line Format	--basedir=name
  Config-File Format	basedir
  Option Sets Variable	Yes, basedir
  Variable Name	basedir
  Variable Scope	Global
  Dynamic Variable	No
  	Permitted Values
  	Type	filename

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

• big_tables

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

  Note

SLEEP()

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