MySQL and mSQL System Variables (MySQL & mSQL)

Several variables can be used to customize the operation of MySQL and mSQL. Many of these are environment variables that are inherited from the user's shell, while others are set via command line options and configuration files.

16.1. MySQL System Variables

16.1.1. Environment Variables

The following variables are specific to MySQL programs. They may be defined in the current shell or as part of a shell script. To set a variable for the MySQL daemon (mysqld), define the variable in the safe_mysqld script that is used to start the daemon or define the variables in the MySQL configuration file (described later in this chapter).

MY_BASEDIR
MY_BASEDIR_VERSION: The root directory containing the subdirectories `bin', `var' and `libexec' that contain the MySQL programs and data. A default value of this (usually compiled into MySQL as /usr/local) is used if this variable does not exist. This option affects only the mysqld program.
MYSQL_DEBUG

WARNING

Be careful where you put your passwords. A common use for environment variables is to set them within scripts. Of course, setting this particular variable in a script would make your password visible to anyone who can run the script. Even setting the variable manually on the command line exposes it to the superuser and any else who has the ability to examine the system memory.

MYSQL_TCP_PORT: When used with a client program, this is the TCP port on a remote machine used to connect to the MySQL database server. When used with mysqld, this is the port used to listen for incoming connections.
MYSQL_UNIX_PORT: When used with a client program, this is the Unix socket file used to connect to the MySQL database server. When used with mysqld, this is the name of the Unix socket file created that allows local connections.

In addition, the MySQL programs use the following environment variables that are routinely set as part of the Unix environment.

EDITOR
VISUAL: The path of the default editor. The mysql program uses this program to edit SQL statements if a \e or edit command is encountered.
HOME: The home directory of the current user.
LOGIN
LOGNAME
USER: The username of the current user.
PATH: The list of directories used to find programs.
POSIXLY_CORRECT: If this variable is defined, no special processing is done on command line options. Otherwise, command line options are reordered so that extended options can be used. This variable can be used with any MySQL program.
TMP
TMPDIR: The directory in which temporary files are kept. If this variable is not defined `/tmp' is used.
TZ: The time zone of the local machine.
UMASK: The umask used when creating new files.

16.1.2. Command line variables

These options are supplied via the -O or -set-variable command line option that is available in most MySQL programs.

back_log: The number of TCP connections that can be queued at once. The default value is 5. This option is available for mysqld only.
connect_timeout: The number of seconds the mysqld server waits for a connect packet before responding with Bad handshake.
decode-bits: The number of bits used for generating certain internal tables. This should be a number between 4 and 9 (between 4 and 6 on a 16-bit operating system). The default value is 9. This option is available only for isamchk and should be used only if you understand the details of the ISAM table structure.
delayed_insert_limit: Causes the INSERT DELAYED handler to check whether there are any SELECT statements pending after inserting delayed_insert_limit rows. If so, the handler allows the statements to execute before continuing.
delayed_insert_timeout: How long an INSERT DELAYED thread should wait for INSERT statements to finish before terminating.
delayed_queue_size: How big a queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does an INSERT DELAYED must wait until there is room in the queue again.
dritebuffer: The size of the buffer used to store outgoing data. The default value is 260KB. This option is only available for isamchk.
flush_time: If set, all tables are closed then every flush_time seconds to free resources and synchronize changes to disk.
join_buffer: The size of a buffer used when performing table joins. Increasing this can speed up performance for queries that join tables. The default value is 130 KB. This option is available only for mysqld.
key_buffer_size: The size of a buffer allocated to store recently accessed keys. Increasing this can speed up performance for queries that involve the repeated use of the same keys. This option is available for isamchk (where the default value is 0.5 MB) and mysqld (default value of 1 MB).
long_query_time: If set, the slow_queries counter is incremented each time a query takes longer than long_query_time seconds.
max_allowed_packet: The maximum size of the buffer used to store incoming data. Each client connection has a separate buffer. The default value is 64 KB. This option is available only for mysqld.
max_connect_errors: If set, the server blocks further connections from a remote host when the number of interrupted connections from that host exceeds max_connect_errors. You can unblock a host with the command FLUSH HOSTS.
max_connections: The maximum number of simultaneous client connections. The default value is 90. This option can be used only with mysqld.
max_delayed_threads: Start no more than this number of threads to handle INSERT DELAYED. If a client tries to use INSERT DATA to insert new data after this limit is reached, the request is handled as if the DELAYED attribute was not specified.
max_join_size: The maximum size of a temporary table created by joining tables. The default value is 4 GB. This option can be used only with mysqld.
max_sort_length: The maximum number of characters to examine when sorting a BLOB or VARCHAR field. The default value is 1KB.
max_tmp_tables: (To be implemented later for Version 3.23.) Maximum number of temporary tables a client can keep open at the same time.
net_buffer_length: The initial size of the buffer used to store incoming data. Each client connection has a separate buffer. The default value is 8KB. This option is available for mysql, mysqld, and mysqldump.
readbuffer: The size of the buffer used to store data being read from files. The default value is 260KB. This option is available only for isamchk.
record_buffer: The size of a buffer used to read data from the tables directly (that is, not using keys). Increasing this can speed up performance for queries that do not involve keys. The default value is 130KB. This option is available only for mysqld.
sortbuffer: The size of the buffer used when sorting table data. The default value is 1MB. This option is available only for isamchk.
sort_buffer: The size of the buffer used when performing sorts on retrieved data. Increasing this can speed up performance for queries that use ORDER BY or GROUP BY statements. The default value is 2MB. This option is available only for mysqld.
sort_key_blocks: The number of blocks of keys used when sorting keys. This default value is 16. This option is only available for isamchk and should be used only if you understand the details of the ISAM table structure.
table_cache: The maximum number of tables the database server can have open at once. The default value is 64. This option is only available for mysqld.
tmp_table_size: The maximum size of temporary tables used by the database server. The default value is 1MB. This option is only available for mysqld.
thread_stack: The size of the memory stack for each thread. The default value is 64KB. This option is only available for mysqld.
wait_timeout: The number of seconds the server waits for activity on a connection before closing it.

16.1.3. The MySQL Configuration File

As of MySQL 3.22, you may specify both server and client options within a text configuration file. There is one format for this file which takes on different meaning depending on the location of the file. If the configuration file is stored in /etc/my.cnf, the options apply to all MySQL servers and clients on the machine. If it located in the data directory of a MySQL server (e.g., /usr/local/mysql/data/my.cnf) the options effect the operation of that MySQL server. Lastly, if the configuration file is named .my.cnf (note the initial period) and is located in the home directory of a user, it effects any clients run by that user.

The format of the file is similar to the one popularized by Windows initialization files. The file is broken up into stanzas, each with a group name enclosed in brackets. Underneath the group name is a list of options. Comments are indicated by a line beginning with # or ;. Each group name is the name of a MySQL client or server program you wish the option to affect. The special group name client affects all MySQL client programs (everything except mysqld).

The options given in this file can be any long form command line option to any MySQL command (excluding the double-dash "--" option indentifier). Following is a sample server-wide my.cnf file.

[client]
port=9999
socket=/dev/mysql

[mysqld]
port=9999
socket=/dev/mysql
set-variable = join_buffer=1M

[mysql]
host=dbhost
unbuffered

WARNING

Chapter 16. MySQL and mSQL System Variables

Contents:

16.1. MySQL System Variables

16.1.1. Environment Variables

16.1.2. Command line variables

16.1.3. The MySQL Configuration File