Book HomeMySQL and mSQLSearch this book

Chapter 16. MySQL and mSQL System Variables

Contents:

MySQL System Variables
mSQL System Variables

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

The debugging level for the program. This option can be used with any MySQL program. The debugging library used by MySQL has many options. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html. The most common set of options is d:t:o,/tmp/debugfile.

MYSQL_HOST

The hostname used to connect to a remote MySQL database server. This option can be used with any of the MySQL client programs (mysql, mysqlshow, mysqladmin, etc.).

MYSQL_PWD

The password used to connect to the MySQL database server. This option can be used with any of the MySQL client programs.

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


Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.