(mysql.info.gz) Server options
Info Catalog
(mysql.info.gz) Configuring MySQL
(mysql.info.gz) Configuring MySQL
(mysql.info.gz) Server SQL mode
5.2.1 `mysqld' Command-Line Options
-----------------------------------
When you start the `mysqld' server, you can specify program options
using any of the methods described in 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. Option files.
`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-line options. For a list, execute
`mysqld --help'. Before MySQL 4.1.1, `--help' prints the full help
message. As of 4.1.1, it prints a brief message; to see the full list,
use `mysqld --verbose --help'.
The following list shows some of the most common server options.
Additional options are described elsewhere:
* Options that affect security: See Privileges options.
* SSL-related options: See SSL options.
* Binary log control options: See Binary log.
* Replication-related options: See Replication Options.
DONTPRINTYET * Options specific to particular storage engines: See `MyISAM'
start MyISAM start, `BDB' start BDB start, *Note `InnoDB'
DONTPRINTYET * Options specific to particular storage engines: See `MyISAM'
start MyISAM start, `BDB' start BDB start, `InnoDB'
start InnoDB start.
You can also set the value of a server system variable by using the
variable name as an option, as described later in this section.
`--help, -?'
Display a short help message and exit. Before MySQL 4.1.1,
`--help' displays the full help message. As of 4.1.1, it displays
an abbreviated message only. Use both the `--verbose' and
`--help' options to see the full message.
`--ansi'
Use standard SQL syntax instead of MySQL syntax. ANSI
mode. For more precise control over the server SQL mode, use
the `--sql-mode' option instead.
`--basedir=PATH, -b PATH'
The path to the MySQL installation directory. All paths are
usually resolved relative to this.
`--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.
`--console'
Write the error log messages to stderr/stdout even if `--log-error'
is specified. On Windows, `mysqld' will not close the console
screen if this option is used.
`--character-sets-dir=PATH'
The directory where character sets are installed. Character
sets.
`--chroot=PATH'
Put the `mysqld' server in a closed environment during startup by
using the `chroot()' system call. This is a recommended security
measure as of MySQL 4.0. (MySQL 3.23 is not able to provide a
`chroot()' jail that is 100% closed.) Note that use of this
option somewhat limits `LOAD DATA INFILE' and `SELECT ... INTO
OUTFILE'.
`--character-set-server=CHARSET'
Use CHARSET as the default server character set. This option is
available as of MySQL 4.1.3. Character sets.
`--core-file'
Write a core file if `mysqld' dies. For some systems, you must
also specify the `--core-file-size' option to `mysqld_safe'.
`mysqld_safe' mysqld_safe. Note that on some systems, such
as Solaris, you will not get a core file if you are also using the
`--user' option.
`--collation-server=COLLATION'
Use COLLATION as the default server collation. This option is
available as of MySQL 4.1.3. Character sets.
`--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. The
DEBUG_OPTIONS string often is `'d:t:o,FILE_NAME''. Making
trace files.
`--default-character-set=CHARSET'
Use CHARSET as the default character set. This option is
deprecated in favor of `--character-set-server' as of MySQL 4.1.3.
Character sets.
`--default-collation=COLLATION'
Use COLLATION as the default collation. This option is deprecated
in favor of `--collation-server' as of MySQL 4.1.3.
Character sets.
`--default-storage-engine=TYPE'
This option is a synonym for `--default-table-type'. It is
available as of MySQL 4.1.2.
`--default-table-type=TYPE'
Set the default table type for tables. Storage engines.
`--default-time-zone=TYPE'
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 will be the same as the system time zone (given
by the value of the `system_time_zone' system variable. This
option is available as of MySQL 4.1.3.
`--delay-key-write[= OFF | ON | ALL]'
How the `DELAYED KEYS' option should be used. 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 `DELAYED
KEYS' option. `ALL' delays key writes for all `MyISAM' tables.
Available as of MySQL 4.0.3. Server parameters.
MyISAM start.
* If you set this variable to `ALL', you should not use
`MyISAM' tables from within another program (such as from another
MySQL server or with `myisamchk') when the table is in use. Doing
so will lead to index corruption.
`--delay-key-write-for-all-tables'
Old form of `--delay-key-write=ALL' for use prior to MySQL 4.0.3.
As of 4.0.3, use `--delay-key-write' instead.
`--des-key-file=FILE_NAME'
Read the default keys used by `DES_ENCRYPT()' and `DES_DECRYPT()'
from this file.
`--enable-named-pipe'
Enable support for named pipes. This option applies only on
Windows NT, 2000, XP, and 2003 systems, and can be used only with
the `mysqld-nt' and `mysqld-max-nt' servers that support named
pipe connections.
`--exit-info[=FLAGS], -T [FLAGS]'
This is a bit mask of different flags you can use for debugging the
`mysqld' server. Do not use this option unless you know exactly
what it does!
`--external-locking'
Enable system locking. Note that if you use this option on a
system on which `lockd' does not fully work (as on Linux), you
will easily get `mysqld' to deadlock. This option previously was
named `--enable-locking'.
* If you use this option to enable updates to `MyISAM'
tables from many MySQL processes, you have to ensure that these
conditions are satisfied:
* You should not use the query cache for queries that use
tables that are updated by another process.
* You should not use `--delay-key-write=ALL' or
`DELAY_KEY_WRITE=1' on any shared tables.
The easiest way to ensure this is to always use
`--external-locking' together with `--delay-key-write=OFF
--query-cache-size=0'.
(This is not done by default because in many setups it's useful to
have a mixture of the above options.)
`--flush'
Flush 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 synching to disk.
Crashing.
`--init-file=FILE'
Read SQL statements from this file at startup. Each statement
must be on a single line and should not include comments.
`--innodb-safe-binlog'
Adds consistency guarantees between the content of `InnoDB' tables
and the binary log. Binary log.
`--language=LANG_NAME, -L LANG_NAME'
Client error messages in given language. LANG_NAME can be given
as the language name or as the full pathname to the directory
where the language files are installed. Languages.
`--large-pages'
Some hardware/operating system architectures support memory pages
greater than the default (usually 4 KB). The actual implementation
of this support depends on the underlying hardware and OS.
Applications that perform a lot of memory access 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
pages support (which is called HugeTLB in Linux). We have plans to
extend this support to FreeBSD, Solaris and possibly other
platforms.
Before large pages can be used on Linux, it is necessary to
configure the HugeTLB memory pool. For reference, consult the
`hugetlbpage.txt' file in the Linux kernel source.
This option is disabled by default. It was added in MySQL 5.0.3.
`--log[=FILE], -l [FILE]'
Log connections and queries to this file. Query log. If
you don't specify a filename, MySQL will use `HOST_NAME.log' as
the filename.
`--log-bin=[FILE]'
The binary log file. Log all queries that change data to this
file. Used for backup and replication. Binary log. It is
recommended to specify a filename (see Open bugs for the
reason) otherwise MySQL will use `HOST_NAME-bin' as the log file
basename.
`--log-bin-index[=FILE]'
The index file for binary log filenames. Binary log. If
you don't specify a filename, and if you didn't either specify one
in `--log-bin', MySQL will use `HOST_NAME-bin.index' as the
filename.
`--log-error[=FILE]'
Log errors and startup messages to this file. Error log.
If you don't specify a filename, MySQL will use `HOST_NAME.err' as
the filename.
`--log-isam[=FILE]'
Log all `ISAM'/`MyISAM' changes to this file (used only when
debugging `ISAM'/`MyISAM').
`--log-long-format'
Log some extra information to the log files (update log, binary
update log, and slow queries log, whatever log has been
activated). For example, username and timestamp are logged for
queries. Before MySQL 4.1, if you are using `--log-slow-queries'
and `--log-long-format', queries that are not using indexes also
are logged to the slow query log. `--log-long-format' is
deprecated as of MySQL version 4.1, when `--log-short-format' was
introduced. (Long log format is the default setting since version
4.1.) Also note that starting with MySQL 4.1, 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-queries-not-using-indexes'
If you are using this option with `--log-slow-queries', then
queries that are not using indexes also are logged to the slow
query log. This option is available as of MySQL 4.1. Slow
query log.
`--log-short-format'
Log less information to the log files (update log, binary update
log, and slow queries log, whatever log has been activated). For
example, username and timestamp are not logged for queries. This
option was introduced in MySQL 4.1.
`--log-slow-queries[=FILE]'
Log all queries that have taken more than `long_query_time' seconds
to execute to this file. Slow query log. Note that the
default for the amount of information logged has changed in MySQL
4.1. See the `--log-long-format' and `--log-short-format' options
for details.
`--log-update[=FILE]'
Log updates to FILE# where # is a unique number if not given.
Update log. The update log is deprecated and is removed in
MySQL 5.0.0; you should use the binary log instead (`--log-bin').
Binary log. Starting from version 5.0.0, using
`--log-update' will just turn on the binary log instead (
News-5.0.0).
`--log-warnings, -W'
Print out warnings such as `Aborted connection...' to the error
log. Enabling this option is recommended, for example, if you use
replication (you will get more information about what is happening,
such as messages about network failures and reconnections). This
option is enabled by default as of MySQL 4.0.19 and 4.1.2; to
disable it, use `--skip-log-warnings'. As of MySQL 4.0.21 and
4.1.3, aborted connections are not logged to the error log unless
the value is greater than 1. Communication errors.
This option was named `--warnings' before MySQL 4.0.
`--low-priority-updates'
Table-modifying operations (`INSERT', `REPLACE', `DELETE',
`UPDATE') will have 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.
Table locking.
`--memlock'
Lock the `mysqld' process in memory. This works on systems such as
Solaris that support the `mlockall()' system call. This might
help if you have a problem where the operating system is causing
`mysqld' to swap on disk. Note that use of this option requires
that you run the server as `root', which is normally not a good
idea for security reasons.
`--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. You can also use a value of `""' to disable this option.
If this option is used, `mysqld' will, when it opens a `MyISAM'
table, open check whether the table is marked as crashed or wasn't
closed properly. (The last option works only if you are running
with `--skip-external-locking'.) If this is the case, `mysqld'
will run a check on the table. If the table was corrupted,
`mysqld' will attempt to repair it.
The following options affect how the repair works:
*Option* *Description*
`DEFAULT' The same as not giving any option to
`--myisam-recover'.
`BACKUP' If the data file was changed during recovery,
save a backup of the
`TBL_NAME.MYD' file as
`TBL_NAME-DATETIME.BAK'.
`FORCE' Run recovery even if we will lose more than
one row from the `.MYD'
file.
`QUICK' Don't check the rows in the table if there
aren't any delete blocks.
Before a table is automatically repaired, MySQL will add a note
about this in 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 will force a repair of a table even
if some rows would be deleted, but it will keep the old data file
as a backup so that you can later examine what happened.
This option is available as of MySQL 3.23.25.
`--ndb-connectstring=CONNECT_STRING'
When using the `NDB' storage engine, it is possible to point out
the management server that distributes the cluster configuration
by setting the connect string option. See MySQL Cluster
Connectstring for syntax.
`--ndbcluster'
If the binary includes support for the `NDB Cluster' storage engine
(from version 4.1.3, the MySQL-Max binaries are built with `NDB
Cluster' enabled) the default disabling of support for the `NDB
Cluster' storage engine can be overruled by using this option.
Using the `NDB Cluster' storage engine is necessary for using
MySQL Cluster. NDBCluster.
`--new'
The `--new' option can be used to make the server behave as 4.1 in
certain respects, easing a 4.0 to 4.1 upgrade:
* Hexadecimal strings such as `0xFF' are treated as strings by
default rather than as numbers. (Works in 4.0.12 and up.)
* `TIMESTAMP' is returned as a string with the format
`'YYYY-MM-DD HH:MM:SS''. (Works in 4.0.13 and up.)
Column types.
This option can be used to help you see how your applications will
behave in MySQL 4.1, without actually upgrading to 4.1.
`--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. Password hashing.
`--old-protocol, -o'
Use the 3.20 protocol for compatibility with some very old clients.
Upgrading-from-3.20.
`--one-thread'
Only use one thread (for debugging under Linux). This option is
available only if the server is built with debugging enabled.
Debugging server.
`--open-files-limit=COUNT'
To change the number of file descriptors available to `mysqld'.
If this is not set or set to 0, then `mysqld' will use this value
to reserve file descriptors to use with `setrlimit()'. If this
value is 0 then `mysqld' will reserve `max_connections*5' or
`max_connections + table_cache*2' (whichever is larger) number of
files. You should try increasing this if `mysqld' gives you the
error "Too many open files."
`--pid-file=PATH'
The path to the process ID file used by `mysqld_safe'.
`--port=PORT_NUM, -P PORT_NUM'
The port number to use when listening for TCP/IP connections.
`--safe-mode'
Skip some optimization stages.
`--safe-show-database'
With this option, the `SHOW DATABASES' statement displays only the
names of those databases for which the user has some kind of
privilege. As of MySQL 4.0.2, this option is deprecated and
doesn't do anything (it is enabled by default), because there is a
`SHOW DATABASES' privilege that can be used to control access to
database names on a per-account basis. Privileges
provided.
`--safe-user-create'
If this is enabled, a user can't create new users with the `GRANT'
statement, if the user doesn't have the `INSERT' privilege for the
`mysql.user' table or any column in the table.
`--secure-auth'
Disallow authentication for accounts that have old (pre-4.1)
passwords. This option is available as of MySQL 4.1.1.
`--shared-memory'
Enable shared-memory connections by local clients. This option is
available only on Windows. It was added in MySQL 4.1.0.
`--shared-memory-base-name=NAME'
The name to use for shared-memory connections. This option is
available only on Windows. It was added in MySQL 4.1.0.
`--skip-bdb'
Disable the `BDB' storage engine. This saves memory and might speed
up some operations. Do not use this option if you require `BDB'
tables.
`--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.)
`--skip-delay-key-write'
Ignore the `DELAY_KEY_WRITE' option for all tables. As of MySQL
4.0.3, you should use `--delay-key-write=OFF' instead.
Server parameters.
`--skip-external-locking'
Don't use system locking. To use `isamchk' or `myisamchk', you
must shut down the server. Stability. In MySQL 3.23, you
can use `CHECK TABLE' and `REPAIR TABLE' to check and repair
`MyISAM' tables. This option previously was named
`--skip-locking'.
`--skip-grant-tables'
This option causes the server not to use the privilege system at
all. This gives everyone _full access_ to all databases! (You can
tell a running server to start using the grant tables again by
executing a `mysqladmin flush-privileges' or `mysqladmin reload'
command, or by issuing a `FLUSH PRIVILEGES' statement.)
`--skip-host-cache'
Do not use the internal hostname cache for faster name-to-IP
resolution. Instead, query the DNS server every time a client
connects. 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-isam'
Disable the `ISAM' storage engine. As of MySQL 4.1, `ISAM' is
disabled by default, so this option applies only if the server was
configured with support for `ISAM'. This option was added in
MySQL 4.1.1.
`--skip-name-resolve'
Do not resolve hostnames 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'.
DNS.
`--skip-ndbcluster'
Disable the `NDB Cluster' storage engine. This is the default for
binaries that were built with `NDB Cluster' storage engine
support, this means that the system will only allocate memory and
other resources for this storage engine if it is explicitly
enabled.
`--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.
DNS.
`--skip-new'
Don't use new, possibly wrong routines.
`--skip-symlink'
This is the old form of `--skip-symbolic-links', for use before
MySQL 4.0.13.
`--symbolic-links, --skip-symbolic-links'
Enable or disable symbolic link support. This option has different
effects on Windows and Unix:
* On Windows, enabling symbolic links allows you to establish a
symbolic link to a database directory by creating a
`directory.sym' file that contains the path to the real
directory. Windows symbolic links.
* On Unix, enabling symbolic links means that you can link a
`MyISAM' index file or data file to another directory with
the `INDEX DIRECTORY' or `DATA DIRECTORY' options of the
`CREATE TABLE' statement. If you delete or rename the table,
the files that its symbolic links point to also are deleted or
renamed. `CREATE TABLE' CREATE TABLE.
This option was added in MySQL 4.0.13.
`--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.
`--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. Debugging server.
`--skip-thread-priority'
Disable using thread priorities for faster response time.
`--socket=PATH'
On Unix, this option specifies the Unix socket file to use for
local connections. The default value is `/tmp/mysql.sock'. On
Windows, the option specifies the pipe name to use for local
connections that use a named pipe. The default value is `MySQL'.
`--sql-mode=VALUE[,VALUE[,VALUE...]]'
Set the SQL mode for MySQL. Server SQL mode. This option
was added in 3.23.41.
`--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's being
allocated to the directory entry cache rather than to the disk
cache.
`--transaction-isolation=LEVEL'
Sets the default transaction isolation level, which can be
`READ-UNCOMMITTED', `READ-COMMITTED', `REPEATABLE-READ', or
`SERIALIZABLE'. `SET TRANSACTION' SET TRANSACTION.
`--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. Starting
from MySQL 4.1, 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 filesystem or to a directory that is cleared when the
server host restarts. 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 will fail.
`--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 will change its user ID during its startup sequence,
causing it to run as that particular user rather than as `root'.
Security guidelines.
Starting from MySQL 3.23.56 and 4.0.12: To avoid a possible
security hole where a user adds a `--user=root' option to some
`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 `datadir/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' will be 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.
`--version, -V'
Display version information and exit.
As of MySQL 4.0, 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 setting a variable to a value, MySQL might automatically
correct it to stay within a given range, or adjust the value to the
closest allowable value if only certain values are allowed.
It is also possible to set variables by using
`--set-variable=VAR_NAME=VALUE' or `-O VAR_NAME=VALUE' syntax. However,
this syntax is deprecated as of MySQL 4.0.
You can find a full description for all variables in Server
system variables. The section on tuning server parameters includes
information on how to optimize them. Server parameters.
You can change the values of most system variables for a running server
with the `SET' statement. `SET OPTION' SET OPTION.
If you want to restrict the maximum value that a startup option can be
set to with `SET', you can define this by using the
`--maximum-VAR_NAME' command-line option.
Info Catalog
(mysql.info.gz) Configuring MySQL
(mysql.info.gz) Configuring MySQL
(mysql.info.gz) Server SQL mode
automatically generated byinfo2html