|
Chapter 5. MySQL Server Administration
MySQL Server (mysqld) is the main program that
does most of the work in a MySQL installation. This section provides
an overview of MySQL Server and covers topics that deal with
administering a MySQL installation:
mysqld is the MySQL server. The following
discussion covers these MySQL server configuration topics:
Startup options that the server supports
Server system variables
Server status variables
How to set the server SQL mode
The server shutdown process
Note
Not all storage engines are supported by all MySQL server binaries
and configurations. To find out how to determine which storage
engines your MySQL server installation supports, see
Section 12.5.5.17, “SHOW ENGINES Syntax”.
5.1.1. 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 5.1.2. Server Command Options
When you start the mysqld server, you can
specify program options using any of the methods described in
Section 4.2.3, “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
Section 4.2.3.3, “Using Option Files”.
MySQL Enterprise
For expert advice on setting command options, subscribe to the
MySQL Enterprise Monitor. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
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 MySQL 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:
Options that affect security: See
Section 5.3.3, “Security-Related mysqld Options”.
SSL-related options: See Section 5.5.7.3, “SSL Command Options”.
Binary log control options: See Section 5.2.4, “The Binary Log”.
Replication-related options: See
Section 16.1.3, “Replication and Binary Logging Options and Variables”.
Options for loading plugins such as pluggable storage engines:
See Section 5.1.3, “Server Options for Loading Plugins”.
Options specific to particular storage engines: See
Section 13.5.1, “MyISAM Startup Options”,
Section 13.6.3, “InnoDB Startup Options and System Variables”, and
Section 17.3.4.2, “mysqld Command Options for MySQL Cluster”.
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.
--help, -?
Display a short help message and exit. Use both the
--verbose and
--help options to see the full
message.
--allow-suspicious-udfs
This option controls whether user-defined functions that have
only an xxx symbol for the main function
can be loaded. By default, the option is off and only UDFs
that have at least one auxiliary symbol can be loaded; this
prevents attempts at loading functions from shared object
files other than those containing legitimate UDFs. See
Section 22.3.2.6, “User-Defined Function Security Precautions”.
--ansi
Use standard (ANSI) SQL syntax instead of MySQL syntax. For
more precise control over the server SQL mode, use the
--sql-mode option instead. See
Section 1.8.3, “Running MySQL in ANSI Mode”, and
Section 5.1.8, “Server SQL Modes”.
--basedir=path,
-b
path
The path to the MySQL installation directory. All paths are
usually resolved relative to this directory.
--big-tables
Allow large result sets by saving all temporary sets in files.
This option prevents most “table full” errors,
but also slows down queries for which in-memory tables would
suffice. Since MySQL 3.23.2, the server is able to handle
large result sets automatically by using memory for small
temporary tables and switching to disk tables where necessary.
--bind-address=IP
The IP address to bind to. Only one address can be selected.
If this option is specified multiple times, the last address
given is used.
If no address or 0.0.0.0 is specified, the
server listens on all interfaces.
--binlog-format={ROW|STATEMENT|MIXED}
Specify whether to use row-based, statement-based, or mixed
replication (statement-based was the default prior to MySQL
5.1.12; in 5.1.12, the default was changed to mixed
replication; in 5.1.29, the default was changed back to
statement-based). See Section 16.1.2, “Replication Formats”.
This option was added in MySQL 5.1.5.
Important
Setting the binary logging format without enabling binary
logging prevents the MySQL server from starting. This is a
known issue in MySQL 5.1 which is fixed in
MySQL 5.5. (Bug#42928)
MySQL Cluster.
The default value for this option in all MySQL Cluster NDB
6.1, 6.2, 6.3, and later 6.x releases is
MIXED. See
Section 17.6.2, “MySQL Cluster Replication — Assumptions and General Requirements”, for
more information.
--bootstrap
This option is used by the mysql_install_db
script to create the MySQL privilege tables without having to
start a full MySQL server.
This option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.3.2, “Typical configure Options”.
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--character-set-client-handshake
Don't ignore character set information sent by the client. To
ignore client information and use the default server character
set, use
--skip-character-set-client-handshake;
this makes MySQL behave like MySQL 4.0.
--character-set-filesystem=charset_name
The file system character set. This option sets the
character_set_filesystem
system variable. It was added in MySQL 5.1.6.
--character-set-server=charset_name,
-C charset_name
Use charset_name as the default
server character set. See
Section 9.2, “The Character Set Used for Data and Sorting”. If you use this
option to specify a nondefault character set, you should also
use --collation-server to
specify the collation.
--chroot=path,
-r path
Put the mysqld server in a closed
environment during startup by using the
chroot() system call. This is a recommended
security measure. Note that use of this option somewhat limits
LOAD DATA
INFILE and
SELECT ... INTO
OUTFILE.
--collation-server=collation_name
Use collation_name as the default
server collation. See Section 9.2, “The Character Set Used for Data and Sorting”.
--console
(Windows only.) Write error log messages to
stderr and stdout even
if --log-error is specified.
mysqld does not close the console window if
this option is used.
--core-file
Write a core file if mysqld dies. The name
and location of the core file is system dependent. On Linux, a
core file named
core.pid is
written to the current working directory of the process, which
for mysqld is the data directory.
pid represents the process ID of
the server process. On Mac OS X, a core file named
core.pid is
written to the /cores directory. On
Solaris, use the coreadm command to specify
where to write the core file and how to name it.
For some systems, to get a core file you must also specify the
--core-file-size option to
mysqld_safe. See
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”. On some systems, such as
Solaris, you do not get a core file if you are also using the
--user option. There might be
additional restrictions or limitations. For example, it might
be necessary to execute ulimit -c unlimited
before starting the server. Consult your system documentation.
--datadir=path,
-h path
The path to the data directory.
--debug[=debug_options],
-# [debug_options]
If MySQL is configured with
--with-debug, you can use
this option to get a trace file of what
mysqld is doing. A typical
debug_options string is
'd:t:o,file_name'.
The default is 'd:t:i:o,mysqld.trace'. See
MySQL
Internals: Porting.
As of MySQL 5.1.12, using
--with-debug to configure
MySQL with debugging support enables you to use the
--debug="d,parser_debug" option
when you start the server. This causes the Bison parser that
is used to process SQL statements to dump a parser trace to
the server's standard error output. Typically, this output is
written to the error log.
This option may be given multiple times. Values that begin
with + or - are added to
or subtracted from the previous value. For example,
--debug=T
--debug=+P sets the value to
P:T.
--debug-sync-timeout[=N]
Controls whether the Debug Sync facility for testing and
debugging is enabled. Use of Debug Sync requires that MySQL be
configured with the
--enable-debug-sync option
(see Section 2.3.2, “Typical configure Options”). If Debug Sync is
not compiled in, this option is not available. The option
value is a timeout in seconds. The default value is 0, which
disables Debug Sync. To enable it, specify a value greater
than 0; this value also becomes the default timeout for
individual synchronization points. If the option is given
without a value, the timeout is set to 300 seconds.
For a description of the Debug Sync facility and how to use
synchronization points, see
MySQL
Internals: Test Synchronization.
This option was added in MySQL 5.1.41.
--default-character-set=charset_name
(DEPRECATED)
Use charset_name as the default
character set. This option is deprecated in favor of
--character-set-server. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--default-collation=collation_name
Use collation_name as the default
collation. This option is deprecated in favor of
--collation-server. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--default-storage-engine=type
Set the default storage engine (table type) for tables. See
Chapter 13, Storage Engines.
--default-table-type=type
This option is a deprecated synonym for
--default-storage-engine.
--default-time-zone=timezone
Set the default server time zone. This option sets the global
time_zone system variable. If
this option is not given, the default time zone is the same as
the system time zone (given by the value of the
system_time_zone system
variable.
--delay-key-write[={OFF|ON|ALL}]
Specify how to use delayed key writes. Delayed key writing
causes key buffers not to be flushed between writes for
MyISAM tables. OFF
disables delayed key writes. ON enables
delayed key writes for those tables that were created with the
DELAY_KEY_WRITE option.
ALL delays key writes for all
MyISAM tables. See
Section 7.5.3, “Tuning Server Parameters”, and
Section 13.5.1, “MyISAM Startup Options”.
Note
If you set this variable to ALL, you
should not use MyISAM tables from within
another program (such as another MySQL server or
myisamchk) when the tables are in use.
Doing so leads to index corruption.
--des-key-file=file_name
Read the default DES keys from this file. These keys are used
by the DES_ENCRYPT() and
DES_DECRYPT() functions.
--enable-named-pipe
Enable support for named pipes. This option applies only on
Windows NT, 2000, XP, and 2003 systems. For MySQL 5.1.20 and
earlier, this option is available only when using the
mysqld-nt and
mysqld-debug servers that support
named-pipe connections. For MySQL 5.1.21 and later,
mysqld-nt is not available, but support is
included in the standard mysqld and
mysqld-debug servers.
--enable-pstack
Print a symbolic stack trace on failure. This capability is
available only on Intel Linux systems, and only if MySQL was
configured with the --with-pstack option.
--engine-condition-pushdown={ON|OFF}
Sets the
engine_condition_pushdown
system variable. For more information, see
Section 7.2.7, “Condition Pushdown Optimization”.
--event-scheduler[=value]
Enable or disable, and start or stop, the event scheduler.
This option was added in MySQL 5.1.6. Note that its permitted
values and behavior changed in MySQL 5.1.11, and again in
MySQL 5.1.12.
For detailed information, see
The
--event-scheduler Option.
--exit-info[=flags],
-T [flags]
This is a bit mask of different flags that you can use for
debugging the mysqld server. Do not use
this option unless you know exactly what
it does!
--external-locking
Enable external locking (system locking), which is disabled by
default as of MySQL 4.0. Note that if you use this option on a
system on which lockd does not fully work
(such as Linux), it is easy for mysqld to
deadlock. This option previously was named
--enable-locking.
For more information about external locking, including
conditions under which it can and cannot be used, see
Section 7.3.4, “External Locking”.
--flush
Flush (synchronize) all changes to disk after each SQL
statement. Normally, MySQL does a write of all changes to disk
only after each SQL statement and lets the operating system
handle the synchronizing to disk. See
Section B.5.4.2, “What to Do If MySQL Keeps Crashing”.
--gdb
Install an interrupt handler for SIGINT
(needed to stop mysqld with
^C to set breakpoints) and disable stack
tracing and core file handling. See
MySQL
Internals: Porting.
--general-log[={0|1}]
Specify the initial general query log state. With no argument
or an argument of 1, the
--general-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log. This option was added in MySQL 5.1.12.
--init-file=file_name
Read SQL statements from this file at startup. Each statement
must be on a single line and should not include comments.
This option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.3.2, “Typical configure Options”.
--innodb-xxx
The InnoDB options are listed in
Section 13.6.3, “InnoDB Startup Options and System Variables”.
--language=lang_name,
-L lang_name
The language to use for error messages.
lang_name can be given as the
language name or as the full path name to the directory where
the language files are installed. See
Section 9.3, “Setting the Error Message Language”.
--large-pages
Some hardware/operating system architectures support memory
pages greater than the default (usually 4KB). The actual
implementation of this support depends on the underlying
hardware and operating system. Applications that perform a lot
of memory accesses may obtain performance improvements by
using large pages due to reduced Translation Lookaside Buffer
(TLB) misses.
Currently, MySQL supports only the Linux implementation of
large page support (which is called HugeTLB in Linux). See
Section 7.5.9, “Enabling Large Page Support”.
--large-pages is disabled by
default.
--log[=file_name],
-l [file_name]
This option enables logging to the general query log, which
contains entries that record client connections and SQL
statements received from clients. The log output destination
can be selected with the
--log-output option as of MySQL
5.1.6. Before 5.1.6, logging occurs to the general query log
file. If you omit the file name, MySQL uses
host_name.log
As of MySQL 5.1.29, the --log
option is deprecated and will be removed (along with the
log system variable) in MySQL
7.0. Instead, use the
--general_log option to enable
the general query log and the
--general_log_file=file_name
option to set the general query log file name.
--log-error[=file_name]
Log errors and startup messages to this file. See
Section 5.2.2, “The Error Log”. If you omit the file name, MySQL
uses
host_name.err.err.
--log-isam[=file_name]
Log all MyISAM changes to this file (used
only when debugging MyISAM).
--log-long-format
(DEPRECATED)
Log extra information to the binary log and slow query log, if
they have been activated. For example, the user name and
timestamp are logged for all queries. This option is
deprecated, as it now represents the default logging behavior.
(See the description for
--log-short-format.) The
--log-queries-not-using-indexes
option is available for the purpose of logging queries that do
not use indexes to the slow query log.
--log-output[=value,...]
This option determines the destination for general query log
and slow query log output. The option value can be given as
one or more of the words TABLE,
FILE, or NONE. If the
option is given without a value, the default is
FILE. (For MySQL 5.1.6 through 5.1.20, the
default is TABLE.) TABLE
select logging to the
general_log and
slow_log tables in the
mysql database as a destination.
FILE selects logging to log files as a
destination. NONE disables logging. If
NONE is present in the option value, it
takes precedence over any other words that are present.
TABLE and FILE can both
be given to select to both log output destinations.
This option selects log output destinations, but does not
enable log output. To do that, use the
--general_log and
--slow_query_log options. For
FILE logging, the
--general_log_file and
-slow_query_log_file options determine the
log file location. (Before MySQL 5.1.29, enable the logs with
the --log and
--log-slow-queries options. The
options take an optional file name argument to specify the log
file name.) For more information, see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”.
The --log-output option was
added in MySQL 5.1.6.
--log-queries-not-using-indexes
If you are using this option with the slow query log enabled,
queries that are expected to retrieve all rows are logged. See
Section 5.2.5, “The Slow Query Log”. This option does not
necessarily mean that no index is used. For example, a query
that uses a full index scan uses an index but would be logged
because the index would not limit the number of rows.
--log-short-format
Originally intended to log less information to the binary log
and slow query log, if they have been activated. However, this
option is not operational.
--log-slow-admin-statements
Log slow administrative statements such as
OPTIMIZE TABLE,
ANALYZE TABLE, and
ALTER TABLE to the slow query
log.
--log-slow-queries[=file_name]
This option enables logging to the slow query log, which
contains entries for all queries that have taken more than
long_query_time seconds to
execute. See the descriptions of the
--log-long-format and
--log-short-format options for
details.
The log output destination can be selected with the
--log-output option as of MySQL
5.1.6. Before 5.1.6, logging occurs to the slow query log
file. If you omit the file name, MySQL uses
host_name-slow.log
As of MySQL 5.1.29, the
--log-slow-queries option is
deprecated and will be removed (along with the
log_slow_queries system
variable) in MySQL 7.0. Instead, use the
--slow_query_log option to
enable the slow query log and the
--slow_query_log_file=file_name
option to set the slow query log file name.
--log-tc=file_name
The name of the memory-mapped transaction coordinator log file
(for XA transactions that affect multiple storage engines when
the binary log is disabled). The default name is
tc.log. The file is created under the
data directory if not given as a full path name. Currently,
this option is unused.
--log-tc-size=size
The size in bytes of the memory-mapped transaction coordinator
log. The default size is 24KB.
--log-warnings[=level],
-W [level]
Print out warnings such as Aborted
connection... to the error log. Enabling this option
is recommended, for example, if you use replication (you get
more information about what is happening, such as messages
about network failures and reconnections). This option is
enabled (1) by default, and the default
level value if omitted is 1. To
disable this option, use
--log-warnings=0. If the value
is greater than 1, aborted connections are written to the
error log. See Section B.5.2.11, “Communication Errors and Aborted Connections”.
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. As of MySQL 5.1.38, the server logs messages about
statements that are unsafe for statement-based logging only if
--log-warnings is enabled.
--low-priority-updates
Give table-modifying operations
(INSERT,
REPLACE,
DELETE,
UPDATE) lower priority than
selects. This can also be done via {INSERT | REPLACE
| DELETE | UPDATE} LOW_PRIORITY ... to lower the
priority of only one query, or by SET
LOW_PRIORITY_UPDATES=1 to change the priority in one
thread. This affects only storage engines that use only
table-level locking (MyISAM,
MEMORY, MERGE). See
Section 7.3.2, “Table Locking Issues”.
--min-examined-row-limit=number
When this option is set, queries which examine fewer than
number rows are not written to the
slow query log. The default is 0.
This option was introduced in MySQL 5.1.21.
--memlock
Lock the mysqld process in memory. This
option might help if you have a problem where the operating
system is causing mysqld to swap to disk.
--memlock works on systems that
support the mlockall() system call; this
includes Solaris as well as most Linux distributions that use
a 2.4 or newer kernel. On Linux systems, you can tell whether
or not mlockall() (and thus this option) is
supported by checking to see whether or not it is defined in
the system mman.h file, like this:
shell> grep mlockall /usr/include/sys/mman.h
If mlockall() is supported, you should see
in the output of the previous command something like the
following:
extern int mlockall (int __flags) __THROW;
Important
Using this option requires that you run the server as
root, which, for reasons of security, is
normally not a good idea. See
Section 5.3.5, “How to Run MySQL as a Normal User”.
You must not try to use this option on a system that does
not support the mlockall() system call;
if you do so, mysqld will very likely
crash as soon as you try to start it.
--myisam-block-size=N
The block size to be used for MyISAM index
pages.
--myisam-recover[=option[,option]...]]
Set the MyISAM storage engine recovery
mode. The option value is any combination of the values of
DEFAULT, BACKUP,
FORCE, or QUICK. If you
specify multiple values, separate them by commas. Specifying
the option with no argument is the same as specifying
DEFAULT, and specifying with an explicit
value of "" disables recovery (same as not
giving the option). If recovery is enabled, each time
mysqld opens a MyISAM
table, it checks whether the table is marked as crashed or
wasn't closed properly. (The last option works only if you are
running with external locking disabled.) If this is the case,
mysqld runs a check on the table. If the
table was corrupted, mysqld attempts to
repair it.
The following options affect how the repair works.
Before the server automatically repairs a table, it writes a
note about the repair to the error log. If you want to be able
to recover from most problems without user intervention, you
should use the options BACKUP,FORCE. This
forces a repair of a table even if some rows would be deleted,
but it keeps the old data file as a backup so that you can
later examine what happened.
See Section 13.5.1, “MyISAM Startup Options”.
--old-alter-table
When this option is given, the server does not use the
optimized method of processing an ALTER
TABLE operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE, see
Section 12.1.7, “ALTER TABLE Syntax”.
--old-passwords
Force the server to generate short (pre-4.1) password hashes
for new passwords. This is useful for compatibility when the
server must support older client programs. See
Section 5.5.6.3, “Password Hashing in MySQL”.
--old-style-user-limits
Enable old-style user limits. (Before MySQL 5.0.3, account
resource limits were counted separately for each host from
which a user connected rather than per account row in the
user table.) See
Section 5.5.4, “Limiting Account Resources”.
--one-thread
Only use one thread (for debugging under Linux). This option
is available only if the server is built with debugging
enabled. See
MySQL
Internals: Porting.
As of MySQL 5.1.17, this option is deprecated; use
--thread_handling=no-threads
instead.
--open-files-limit=count
Changes the number of file descriptors available to
mysqld. You should try increasing the value
of this option if mysqld gives you the
error Too many open files.
mysqld uses the option value to reserve
descriptors with setrlimit(). If the
requested number of file descriptors cannot be allocated,
mysqld writes a warning to the error log.
mysqld may attempt to allocate more than
the requested number of descriptors (if they are available),
using the values of
max_connections and
table_open_cache to estimate
whether more descriptors will be needed.
--partition[=value]
Enables or disables user-defined partitioning support in the
MySQL Server.
--pid-file=path
The path name of the process ID file. The server creates the
file in the data directory unless an absolute path name is
given to specify a different directory. This file is used by
other programs such as mysqld_safe to
determine the server's process ID.
--plugin-xxx
Specifies an option that pertains to a server plugin. For
example, many storage engines can be built as plugins, and for
such engines, options for them can be specified with a
--plugin prefix. Thus, the
--innodb_file_per_table option
for InnoDB can be specified as
--plugin-innodb_file_per_table.
For boolean options that can be enabled or disabled, the
--skip prefix and other alternative formats
are supported as well (see
Section 4.2.3.2, “Program Option Modifiers”). For example,
--skip-plugin-innodb_file_per_table
disables innodb_file_per_table.
The rationale for the --plugin prefix is that
it enables plugin options to be specified unambigously if
there is a name conflict with a built-in server option. For
example, were a plugin writer to name a plugin
“sql” and implement a “mode” option,
the option name might be
--sql-mode, which would
conflict with the built-in option of the same name. In such
cases, references to the conflicting name are resolved in
favor of the built-in option. To avoid the ambiguity, users
can specify the plugin option as
--plugin-sql-mode. Use of the
--plugin prefix for plugin options is
recommended to avoid any question of ambiguity.
--plugin-load=plugin_list
Under normal startup, the server determines which plugins to
load by reading the mysql.plugins system
table. This option enables plugins to be loaded even when
--skip-grant-tables is given
(which tells the server not to read system tables). This
option also enables plugins to be loaded at startup under
configurations when plugins cannot be loaded at runtime. The
option was added in MySQL 5.1.18.
The option value is a semicolon-separated list of
name=plugin_libraryname is the name of
the plugin, and plugin_library is
the name of the shared library that contains the plugin code.
Each library file must be located in the directory named by
the plugin_dir system
variable. For example, if plugins named
myplug1 and myplug2 have
library files myplug1.so and
myplug2.so, use this option to load them
at startup:
shell> mysqld --plugin-load=myplug1=myplug1.so;myplug2=myplug2.so
All plugins to load must be named in the same
--plugin-load option. If
multiple --plugin-load options
are given, only the last one is used.
The plugins are loaded for a single invocation of
mysqld only. After a restart, the plugins
are not loaded unless
--plugin-load is used again.
This is in contrast to INSTALL
PLUGIN, which adds an entry to the
mysql.plugins table to cause the plugin to
be loaded for every normal server startup.
--port=port_num,
-P port_num
The port number to use when listening for TCP/IP connections.
The port number must be 1024 or higher unless the server is
started by the root system user.
--port-open-timeout=num
On some systems, when the server is stopped, the TCP/IP port
might not become available immediately. If the server is
restarted quickly afterward, its attempt to reopen the port
can fail. This option indicates how many seconds the server
should wait for the TCP/IP port to become free if it cannot be
opened. The default is not to wait. This option was added in
MySQL 5.1.5.
--safe-mode
Skip some optimization stages.
--safe-show-database
(DEPRECATED)
See Section 5.4.1, “Privileges Provided by MySQL”.
--safe-user-create
If this option is enabled, a user cannot create new MySQL
users by using the GRANT
statement unless the user has the
INSERT privilege for the
mysql.user table or any column in the
table. If you want a user to have the ability to create new
users that have those privileges that the user has the right
to grant, you should grant the user the following privilege:
GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';
This ensures that the user cannot change any privilege columns
directly, but has to use the
GRANT statement to give
privileges to other users.
--secure-auth
Disallow authentication by clients that attempt to use
accounts that have old (pre-4.1) passwords.
--secure-file-priv=path
This option limits the effect of the
LOAD_FILE() function and the
LOAD DATA and
SELECT ... INTO
OUTFILE statements to work only with files in the
specified directory.
This option was added in MySQL 5.1.17.
--shared-memory
Enable shared-memory connections by local clients. This option
is available only on Windows.
--shared-memory-base-name=name
The name of shared memory to use for shared-memory
connections. This option is available only on Windows. The
default name is MYSQL. The name is case
sensitive.
--skip-concurrent-insert
Turn off the ability to select and insert at the same time on
MyISAM tables. (This is to be used only if
you think you have found a bug in this feature.) See
Section 7.3.3, “Concurrent Inserts”.
--skip-external-locking
Do not use external locking (system locking). For more
information about external locking, including conditions under
which it can and cannot be used, see
Section 7.3.4, “External Locking”.
External locking has been disabled by default since MySQL 4.0.
--skip-event-scheduler
Turns the Event Scheduler OFF. This is not
the same as disabling the Event Scheduler, which requires
setting
--event-scheduler=DISABLED; see
The
--event-scheduler Option, for more
information.
--skip-grant-tables
This option causes the server to start without using the
privilege system at all, which gives anyone with access to the
server unrestricted access to all
databases. You can cause a running server to start
using the grant tables again by executing mysqladmin
flush-privileges or mysqladmin
reload command from a system shell, or by issuing a
MySQL FLUSH
PRIVILEGES statement after connecting to the server.
This option also suppresses loading of plugins, user-defined
functions (UDFs), and, beginning with MySQL 5.1.17, scheduled
events. To cause plugins to be loaded anyway, use the
--plugin-load option.
--skip-grant-tables is
unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.3.2, “Typical configure Options”.
--skip-host-cache
Do not use the internal host name cache for faster name-to-IP
resolution. Instead, query the DNS server every time a client
connects. See Section 7.5.11, “How MySQL Uses DNS”.
--skip-innodb
Disable the InnoDB storage engine. This
saves memory and disk space and might speed up some
operations. Do not use this option if you require
InnoDB tables.
--skip-name-resolve
Do not resolve host names when checking client connections.
Use only IP numbers. If you use this option, all
Host column values in the grant tables must
be IP numbers or localhost. See
Section 7.5.11, “How MySQL Uses DNS”.
--skip-networking
Don't listen for TCP/IP connections at all. All interaction
with mysqld must be made via named pipes or
shared memory (on Windows) or Unix socket files (on Unix).
This option is highly recommended for systems where only local
clients are allowed. See Section 7.5.11, “How MySQL Uses DNS”.
--skip-partition
Disables user-defined partitioning. Existing partitioned
tables cannot be accessed when the server has been started
with this option.
--ssl*
Options that begin with --ssl
specify whether to allow clients to connect via SSL and
indicate where to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
--standalone
Available on Windows NT-based systems only; instructs the
MySQL server not to run as a service.
--symbolic-links,
--skip-symbolic-links
Enable or disable symbolic link support. This option has
different effects on Windows and Unix:
--skip-safemalloc
If MySQL is configured with
--with-debug=full, all MySQL
programs check for memory overruns during each memory
allocation and memory freeing operation. This checking is very
slow, so for the server you can avoid it when you don't need
it by using the
--skip-safemalloc option.
--skip-show-database
With this option, the SHOW
DATABASES statement is allowed only to users who
have the SHOW DATABASES
privilege, and the statement displays all database names.
Without this option, SHOW
DATABASES is allowed to all users, but displays each
database name only if the user has the
SHOW DATABASES privilege or
some privilege for the database. Note that
any global privilege is considered a
privilege for the database.
--skip-stack-trace
Don't write stack traces. This option is useful when you are
running mysqld under a debugger. On some
systems, you also must use this option to get a core file. See
MySQL
Internals: Porting.
--skip-thread-priority
Disable using thread priorities for faster response time. This
option is deprecated as of MySQL 5.1.29.
Prior to MySQL 5.1.29, mysqld makes a large
number of invalid calls to thread scheduling routines on
Linux. These calls do not affect performance noticeably but
may be a source of “noise” for debugging tools.
For example, they can overwhelm other information of more
interest in kernel logs. To avoid these calls, start the
server with the
--skip-thread-priority option.
--slow-query-log[={0|1}]
Specify the initial slow query log state. With no argument or
an argument of 1, the
--slow-query-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log. This option was added in MySQL 5.1.12.
--socket=path
On Unix, this option specifies the Unix socket file to use
when listening for local connections. The default value is
/tmp/mysql.sock. If this option is given,
the server creates the file in the data directory unless an
absolute path name is given to specify a different directory.
On Windows, the option specifies the pipe name to use when
listening for local connections that use a named pipe. The
default value is MySQL (not case
sensitive).
--sql-mode=value[,value[,value...]]
Set the SQL mode. See Section 5.1.8, “Server SQL Modes”.
--sysdate-is-now
SYSDATE() by default returns
the time at which it executes, not the time at which the
statement in which it occurs begins executing. This differs
from the behavior of NOW().
This option causes SYSDATE() to
be an alias for NOW(). For
information about the implications for binary logging and
replication, see the description for
SYSDATE() in
Section 11.6, “Date and Time Functions” and for SET
TIMESTAMP in
Section 5.1.5, “Session System Variables”.
This option was added in MySQL 5.1.8.
--tc-heuristic-recover={COMMIT|ROLLBACK}
The type of decision to use in the heuristic recovery process.
Currently, this option is unused.
--temp-pool
This option causes most temporary files created by the server
to use a small set of names, rather than a unique name for
each new file. This works around a problem in the Linux kernel
dealing with creating many new files with different names.
With the old behavior, Linux seems to “leak”
memory, because it is being allocated to the directory entry
cache rather than to the disk cache. As of MySQL 5.1.31, this
option is ignored except on Linux.
--transaction-isolation=level
Sets the default transaction isolation level. The
level value can be
READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE. See
Section 12.4.6, “SET TRANSACTION Syntax”.
--tmpdir=path,
-t path
The path of the directory to use for creating temporary files.
It might be useful if your default /tmp
directory resides on a partition that is too small to hold
temporary tables. This option accepts several paths that are
used in round-robin fashion. Paths should be separated by
colon characters (“:”) on Unix
and semicolon characters (“;”)
on Windows, NetWare, and OS/2. If the MySQL server is acting
as a replication slave, you should not set
--tmpdir to point to a
directory on a memory-based file system or to a directory that
is cleared when the server host restarts. For more information
about the storage location of temporary files, see
Section B.5.4.4, “Where MySQL Stores Temporary Files”. A replication slave needs
some of its temporary files to survive a machine restart so
that it can replicate temporary tables or
LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts, replication
fails.
--user={user_name|user_id},
-u
{user_name|user_id}
Run the mysqld server as the user having
the name user_name or the numeric
user ID user_id.
(“User” in this context refers to a system login
account, not a MySQL user listed in the grant tables.)
This option is mandatory when starting
mysqld as root. The
server changes its user ID during its startup sequence,
causing it to run as that particular user rather than as
root. See
Section 5.3.1, “General Security Guidelines”.
To avoid a possible security hole where a user adds a
--user=root option to a
my.cnf file (thus causing the server to
run as root), mysqld
uses only the first --user
option specified and produces a warning if there are multiple
--user options. Options in
/etc/my.cnf and
$MYSQL_HOME/my.cnf are processed before
command-line options, so it is recommended that you put a
--user option in
/etc/my.cnf and specify a value other
than root. The option in
/etc/my.cnf is found before any other
--user options, which ensures
that the server runs as a user other than
root, and that a warning results if any
other --user option is found.
--verbose,
-v
Use this option with the --help
option for detailed help.
--version, -V
Display version information and exit.
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, MySQL might
automatically correct the value to stay within a given range, or
adjust the value to the closest allowable value if only certain
values are allowed.
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 Section 12.5.4, “SET Syntax”.
Section 5.1.4, “Server System Variables”, provides a full
description for all variables, and additional information for
setting them at server startup and runtime.
Section 7.5.3, “Tuning Server Parameters”, includes information on
optimizing the server by tuning system variables.
5.1.3. Server Options for Loading Plugins
The server can load plugins under the control of startup options.
For example, many storage engines can be built in pluggable form
and loaded when the server starts. In the following descriptions,
plugin_name stands for a plugin name
such as innodb.
As of MySQL 5.1.36, options used to control plugin loading have a
tristate format:
--plugin_name=OFF
Do not enable the plugin.
--plugin_name[=ON]
Enable the plugin. If plugin initialization fails, start the
server anyway, but with the plugin disabled. Specifying the
option as
--plugin_name
without a value also enables the plugin.
--plugin_name=FORCE
Enable the plugin. If plugin initialization fails, do not
start the server. In other words, force the server to run with
the plugin or not at all.
The values OFF, ON, and
FORCE are not case sensitive.
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.
Suppose that CSV, InnoDB,
and ARCHIVE are built as pluggable storage
engines and that you want the server to load them at startup,
subject to these conditions: The server is allowed to run if
CSV initialization fails, but must require that
InnoDB initialization succeed.
ARCHIVE should be disabled. To accomplish that,
use these lines in an option file:
[mysqld]
csv=ON
innodb=FORCE
archive=OFF
--enable-plugin_name
is also supported and is the same as
--plugin_name=ON.
--disable-plugin_name
and --skip-plugin_name
are also supported and are the same as
--plugin_name=OFF.
Before MySQL 5.1.36, plugin options are boolean options (see
Section 4.2.3.2, “Program Option Modifiers”). That is, any of these options
enable the plugin:
--plugin_name
--plugin_name=1
--enable-plugin_name
And these options disable the plugin:
--plugin_name=0
--disable-plugin_name
--skip-plugin_name
If the plugin fails to initialize, the server runs with the plugin
disabled.
If you upgrade to MySQL 5.1.36 or later from an older version and
previously used options of the form
--plugin_name=0 or
--plugin_name=1, the
equivalent options are now
--plugin_name=OFF and
--plugin_name=ON,
respectively. You also have the choice of requiring plugins to
start successfully by using
--plugin_name=FORCE.
Plugins can also be loaded at runtime, via the
INSTALL PLUGIN statement (see
Section 12.5.3.3, “INSTALL PLUGIN Syntax”).
5.1.4. Server System Variables
The MySQL 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:
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.
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:
Table 5.2. System Variable Summary
For additional system variable information, see these sections:
Section 5.1.5, “Session System Variables”, describes system
variables that exist only as session variables (that is, they
do not have any global counterpart).
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 7.5.3, “Tuning Server Parameters”.
Section 13.6.3, “InnoDB Startup Options and System Variables”, lists
InnoDB system variables.
Section 17.3.4.3, “MySQL Cluster System Variables”, lists system
variables which are specific to MySQL Cluster.
For information on server system variables specific to
replication, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.
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, 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.
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.
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 19.2.2, “Stored Routines and MySQL Privileges”.
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.
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.
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 9.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:
The client is from a version of MySQL older than MySQL
4.1, and thus does not request a character set.
The client requests a character set not known to the
server. For example, a Japanese-enabled client requests
sjis when connecting to a server not
configured with sjis support.
mysqld was started with the
--skip-character-set-client-handshake
option, which causes it to ignore client character set
configuration. This reproduces MySQL 4.0 behavior and is
useful should you wish to upgrade the server without
upgrading all the clients.
character_set_connection
The character set for the current connection. Used for
literals that do not have an explicit character set
specification and for number-to-string conversion.
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 multi-byte file names are
allowed, a different value may be more appropriate. For
example, if the system represents file names using UTF-8, set
character_set_filesystem to
'utf8'. This variable was added in MySQL
5.1.6.
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:
If the value is 0 (the default),
COMMIT and
ROLLBACK
are unaffected.
If the value is 1, COMMIT
and
ROLLBACK
are equivalent to COMMIT AND CHAIN and
ROLLBACK AND CHAIN, respectively. (A
new transaction starts immediately with the same isolation
level as the just-terminated transaction.)
If the value is 2, COMMIT
and
ROLLBACK
are equivalent to COMMIT RELEASE and
ROLLBACK RELEASE, respectively. (The
server disconnects after terminating the transaction.)
concurrent_insert
If 1 (the default), MySQL allows
INSERT and
SELECT statements to run
concurrently for MyISAM tables that have no
free blocks in the middle of the data file. You can turn this
option off by starting mysqld with
--safe-mode or
--skip-new.
This variable can take three integer values.
See also Section 7.3.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 as of MySQL 5.1.23 and 5 seconds before that.
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.
datadir
The MySQL data directory. This variable can be set with the
--datadir option.
date_format
This variable is unused.
datetime_format
This variable is unused.
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 |
+---------+
This variable was added in MySQL 5.1.7.
debug_sync
This variable is the user interface to the Debug Sync
facility. Use of Debug Sync requires that MySQL be configured
with the --enable-debug-sync
option (see Section 2.3.2, “Typical configure 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.
This variable was added in MySQL 5.1.41.
default_week_format
The default mode value to use for the
WEEK() function. See
Section 11.6, “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 option (for
example,
--myisam-recover=BACKUP,FORCE).
See Section 5.1.2, “Server Command Options”, and
Section 13.5.1, “MyISAM Startup Options”.
Warning
If you enable external locking with
--external-locking, there is
no protection against index corruption for tables that use
delayed key writes.
delayed_insert_limit
After inserting
delayed_insert_limit delayed
rows, the INSERT DELAYED
handler thread checks whether there are any
SELECT statements pending. If
so, it allows them to execute before continuing to insert
delayed rows.
delayed_insert_timeout
How many seconds an INSERT
DELAYED handler thread should wait for
INSERT statements before
terminating.
delayed_queue_size
This is a per-table limit on the number of rows to queue when
handling INSERT DELAYED
statements. If the queue becomes full, any client that issues
an INSERT DELAYED statement
waits until there is room in the queue again.
div_precision_increment
This variable indicates the number of digits by which to
increase the scale of the result of division operations
performed with the
/ operator.
The default value is 4. The minimum and maximum values are 0
and 30, respectively. The following example illustrates the
effect of increasing the default value.
mysql> SELECT 1/7;
+--------+
| 1/7 |
+--------+
| 0.1429 |
+--------+
mysql> SET div_precision_increment = 12;
mysql> SELECT 1/7;
+----------------+
| 1/7 |
+----------------+
| 0.142857142857 |
+----------------+
engine_condition_pushdown
When the value of this variable is 0 (OFF),
a query such as SELECT * FROM t WHERE mycol =
42, where mycol is a nonindexed
column, is executed as a full table scan. The storage engine
sends every row to the MySQL server, which applies the
WHERE condition. If
engine_condition_pushdown is
set to 1 (ON), the condition is
“pushed down” to the storage engine, which uses
the condition to perform the scan, and sends back to the MySQL
server only those rows that match the condition. By default,
this variable is OFF.
In MySQL 5.1, this variable is useful only with
the NDBCLUSTER storage engine.
However, we intend to implement it for additional storage
engines in future MySQL releases.
Setting this variable to ON on a MySQL
Server acting as a MySQL Cluster SQL node causes
WHERE conditions on unindexed columns to be
evaluated on the cluster's data nodes and only the rows that
match to be sent back to the SQL node that issued the query.
This means the amount of cluster data that must be sent over
the network is greatly reduced, increasing the efficiency with
which results are returned.
For more information, see
Section 7.2.7, “Condition Pushdown Optimization”.
event_scheduler
This variable indicates the status of the Event Scheduler; as
of MySQL 5.1.12, possible values are ON,
OFF, and DISABLED, with
the default being OFF. This variable and
its effects on the Event Scheduler's operation are discussed
in greater detail in the
Overview section
of the Events chapter.
This variable was added in MySQL 5.1.6.
expire_logs_days
The number of days for automatic binary log file removal. The
default is 0, which means “no automatic removal.”
Possible removals happen at startup and when the binary log is
flushed. Log flushing occurs as indicated in
Section 5.2, “MySQL Server Logs”.
To remove binary log files manually, use the
PURGE BINARY LOGS statement.
See Section 12.6.1.1, “PURGE BINARY LOGS Syntax”.
flush
If ON, the server flushes (synchronizes)
all changes to disk after each SQL statement. Normally, MySQL
does a write of all changes to disk only after each SQL
statement and lets the operating system handle the
synchronizing to disk. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”. This
variable is set to ON if you start
mysqld with the
--flush option.
flush_time
If this is set to a nonzero value, all tables are closed every
flush_time seconds to free up
resources and synchronize unflushed data to disk. This option
is best used only on Windows 9x or Me, or on systems with
minimal resources.
ft_boolean_syntax
The list of operators supported by boolean full-text searches
performed using IN BOOLEAN MODE. See
Section 11.8.2, “Boolean Full-Text Searches”.
The default variable value is
'+ -><()~*:""&|'. The rules
for changing the value are as follows:
Operator function is determined by position within the
string.
The replacement value must be 14 characters.
Each character must be an ASCII nonalphanumeric character.
Either the first or second character must be a space.
No duplicates are allowed except the phrase quoting
operators in positions 11 and 12. These two characters are
not required to be the same, but they are the only two
that may be.
Positions 10, 13, and 14 (which by default are set to
“:”,
“&”, and
“|”) are reserved for
future extensions.
ft_max_word_len
The maximum length of the word to be included in a
FULLTEXT index.
Note
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
tbl_name QUICK.
ft_min_word_len
The minimum length of the word to be included in a
FULLTEXT index.
Note
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
tbl_name QUICK.
ft_query_expansion_limit
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION.
ft_stopword_file
The file from which to read the list of stopwords for
full-text searches. The server looks for the file in the data
directory unless an absolute path name is given to specify a
different directory. All the words from the file are used;
comments are not honored. By default, a
built-in list of stopwords is used (as defined in the
storage/myisam/ft_static.c file). Setting
this variable to the empty string ('')
disables stopword filtering.
Note
FULLTEXT indexes must be rebuilt after
changing this variable or the contents of the stopword file.
Use REPAIR TABLE
tbl_name QUICK.
general_log
Whether the general query log is enabled. The value can be 0
(or OFF) to disable the log or 1 (or
ON) to enable the log. The default value
depends on whether the
--general_log option is given
(--log before MySQL 5.1.29).
The destination for log output is controlled by the
log_output system variable;
if that value is NONE, no log entries are
written even if the log is enabled. The
general_log variable was
added in MySQL 5.1.12.
general_log_file
The name of the general query log file. The default value is
host_name.log--general_log_file option
(--log before MySQL 5.1.29).
This variable was added in MySQL 5.1.12.
group_concat_max_len
The maximum allowed result length in bytes for the
GROUP_CONCAT() function. The
default is 1024.
have_archive
YES if mysqld supports
ARCHIVE tables, NO if
not. This variable was removed in MySQL 5.1.14.
have_blackhole_engine
YES if mysqld supports
BLACKHOLE tables, NO if
not. This variable was removed in MySQL 5.1.14.
have_compress
YES if the zlib
compression library is available to the server,
NO if not. If not, the
COMPRESS() and
UNCOMPRESS() functions cannot
be used.
have_community_features
YES if statement profiling is enabled,
NO if not. See
Section 12.5.5.32, “SHOW PROFILE Syntax”.
This variable is renamed to have_profiling
in MySQL 5.5.
have_crypt
YES if the crypt()
system call is available to the server, NO
if not. If not, the ENCRYPT()
function cannot be used.
have_csv
YES
| |