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”.
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
[
groups, where xxxxx
_SERVER]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.4, “Security-Related mysqld Options”.
SSL-related options: See Section 5.5.8.3, “SSL Command Options”.
Binary log control options: See Section 5.2.4, “The Binary Log”.
Replication-related options: See Section 17.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”, and Section 13.6.4, “InnoDB
Startup Options and System Variables”.
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
,-?
Command-Line Format -?
--help
Option-File Format help
Display a short help message and exit. Use both the
--verbose
and--help
options to see the full message.Command-Line Format --allow-suspicious-udfs
Option-File Format allow-suspicious-udfs
Permitted Values Type boolean
Default FALSE
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 23.3.2.6, “User-Defined Function Security Precautions”.Command-Line Format --ansi
-a
Option-File Format 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.7, “Server SQL Modes”.Command-Line Format --basedir=path
-b
Option-File Format basedir
Option Sets Variable Yes, basedir
Variable Name basedir
Variable Scope Global Dynamic Variable No Permitted Values Type file name
The path to the MySQL installation directory. All paths are usually resolved relative to this directory.
Command-Line Format --big-tables
Option-File Format big-tables
Option Sets Variable Yes, big_tables
Variable Name big-tables
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Enable 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.
Command-Line Format --bind-address=name
Option-File Format bind-address=name
Variable Name bind-address
Variable Scope Global Dynamic Variable No Permitted Values Type string
Default 0.0.0.0
Range 0.0.0.0-255.255.255.255
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}
Command-Line Format --binlog-format=format
Option-File Format binlog-format=format
Option Sets Variable Yes, binlog_format
Variable Name binlog_format
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type enumeration
Default STATEMENT
Valid Values ROW
,STATEMENT
,MIXED
Specify whether to use row-based, statement-based, or mixed replication. Statement-based is the default in MySQL 5.5. See Section 17.1.2, “Replication Formats”.
Previous to MySQL 5.5, setting the binary logging format without enabling binary logging prevented the MySQL server from starting. In MySQL 5.5, the server starts in such cases, the
binlog_format
global system variable is set, and a warning is logged instead of an error. (Bug#42928)Command-Line Format --bootstrap
Option-File Format 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
compiler flag. See Section 2.11.4, “MySQL Source-Configuration Options”.Command-Line Format --character-sets-dir=path
Option-File Format character-sets-dir=path
Option Sets Variable Yes, character_sets_dir
Variable Name character-sets-dir
Variable Scope Global Dynamic Variable No Permitted Values Type directory name
The directory where character sets are installed. See Section 9.5, “Character Set Configuration”.
--character-set-client-handshake
Command-Line Format --character-set-client-handshake
Option-File Format character-set-client-handshake
Permitted Values Type boolean
Default TRUE
Do not 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
Command-Line Format --character-set-filesystem=name
Option-File Format character-set-filesystem
Option Sets Variable Yes, character_set_filesystem
Variable Name character_set_filesystem
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type string
The file system character set. This option sets the
character_set_filesystem
system variable.--character-set-server=
,charset_name
-C
charset_name
Command-Line Format --character-set-server
Option-File Format character-set-server
Option Sets Variable Yes, character_set_server
Variable Name character_set_server
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type string
Use
charset_name
as the default server character set. See Section 9.5, “Character Set Configuration”. 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
Command-Line Format --chroot=name
-r name
Option-File Format chroot
Permitted Values Type file name
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 limitsLOAD DATA INFILE
andSELECT ... INTO OUTFILE
.--collation-server=
collation_name
Command-Line Format --collation-server
Option-File Format collation-server
Option Sets Variable Yes, collation_server
Variable Name collation_server
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type string
Use
collation_name
as the default server collation. See Section 9.5, “Character Set Configuration”.Command-Line Format --console
Option-File Format console
Platform Specific windows (Windows only.) Write error log messages to
stderr
andstdout
even if--log-error
is specified. mysqld does not close the console window if this option is used.Command-Line Format --core-file
Option-File Format core-file
Permitted Values Type boolean
Default FALSE
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.
is written to the current working directory of the process, which for mysqld is the data directory.pid
pid
represents the process ID of the server process. On Mac OS X, a core file namedcore.
is written to thepid
/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
Command-Line Format --datadir=path
-h
Option-File Format datadir
Option Sets Variable Yes, datadir
Variable Name datadir
Variable Scope Global Dynamic Variable No Permitted Values Type file name
The path to the data directory.
--debug[=
,debug_options
]-# [
debug_options
]Command-Line Format --debug[=debug_options]
Option-File Format debug
Variable Name debug
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type string
Default 'd:t:o,/tmp/mysqld.trace'
If MySQL is configured with
-DWITH_DEBUG=1
, you can use this option to get a trace file of what mysqld is doing. A typicaldebug_options
string is'd:t:o,
. The default isfile_name
''d:t:i:o,mysqld.trace'
. See MySQL Internals: Porting.Using
-DWITH_DEBUG=1
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 toP:T
.Command-Line Format --debug-sync-timeout[=#]
Option-File Format debug-sync-timeout
Permitted Values Type numeric
Controls whether the Debug Sync facility for testing and debugging is enabled. Use of Debug Sync requires that MySQL be configured with the
-DENABLE_DEBUG_SYNC=1
option (see Section 2.11.4, “MySQL Source-Configuration 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.
--default-character-set=
charset_name
Command-Line Format --default-character-set=name
-C name
Option-File Format default-character-set=name
Deprecated 5.0 Permitted Values Type string
Use
charset_name
as the default character set. This option is deprecated in favor of--character-set-server
. See Section 9.5, “Character Set Configuration”.--default-character-set
was removed in MySQL 5.5.3.--default-collation=
collation_name
Command-Line Format --default-collation=name
Option-File Format default-collation=name
Deprecated 4.1.3 Permitted Values Type string
Use
collation_name
as the default collation. This option is deprecated in favor of--collation-server
. See Section 9.5, “Character Set Configuration”.--default-collation
was removed in MySQL 5.5.3.Command-Line Format --default-storage-engine=name
Option-File Format default-storage-engine
Option Sets Variable Yes, default_storage_engine
Variable Name default-storage-engine
Variable Scope Global, Session Dynamic Variable Yes Permitted Values (<= 5.5.4) Type enumeration
Default MyISAM
Permitted Values (>= 5.5.5) Type enumeration
Default InnoDB
Set the default storage engine (table type) for tables. See Chapter 13, Storage Engines.
Command-Line Format --default-time-zone=name
Option-File Format default-time-zone
Permitted Values Type string
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 thesystem_time_zone
system variable.--delay-key-write[={OFF|ON|ALL}]
Command-Line Format --delay-key-write[=name]
Option-File Format delay-key-write
Option Sets Variable Yes, delay_key_write
Variable Name delay-key-write
Variable Scope Global Dynamic Variable Yes Permitted Values Type enumeration
Default ON
Valid Values ON
,OFF
,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 theDELAY_KEY_WRITE
option.ALL
delays key writes for allMyISAM
tables. See Section 7.11.2, “Tuning Server Parameters”, and Section 13.5.1, “MyISAM
Startup Options”.NoteIf you set this variable to
ALL
, you should not useMyISAM
tables from within another program (such as another MySQL server or myisamchk) when the tables are in use. Doing so leads to index corruption.Command-Line Format --des-key-file=file_name
Option-File Format des-key-file=file_name
Read the default DES keys from this file. These keys are used by the
DES_ENCRYPT()
andDES_DECRYPT()
functions.Command-Line Format --enable-named-pipe
Option-File Format enable-named-pipe
Option Sets Variable Yes, named_pipe
Platform Specific windows Enable support for named pipes. This option applies only on Windows.
Version Removed 5.5.7 Command-Line Format --enable-pstack
Option-File Format enable-pstack
Deprecated 5.1.54 Permitted Values Type boolean
Default FALSE
This option is nonfunctional before MySQL 5.5.7 and removed in 5.5.7.
--engine-condition-pushdown={ON|OFF}
Version Deprecated 5.5.3 Command-Line Format --engine-condition-pushdown
Option-File Format engine-condition-pushdown
Option Sets Variable Yes, engine_condition_pushdown
Variable Name engine_condition_pushdown
Variable Scope Global, Session Dynamic Variable Yes Deprecated 5.5.3, by optimizer_switch
Permitted Values Type boolean
Default ON
Sets the
engine_condition_pushdown
system variable. For more information, see Section 7.13.3, “Engine Condition Pushdown Optimization”.Command-Line Format --event-scheduler[=value]
Option-File Format event-scheduler
Option Sets Variable Yes, event_scheduler
Variable Name event_scheduler
Variable Scope Global Dynamic Variable Yes Permitted Values Type enumeration
Default OFF
Valid Values ON
,OFF
,DISABLED
Enable or disable, and start or stop, the event scheduler.
For detailed information, see The
--event-scheduler
Option.--exit-info[=
,flags
]-T [
flags
]Command-Line Format --exit-info[=flags]
-T [flags]
Option-File Format exit-info
Permitted Values Type numeric
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!
Command-Line Format --external-locking
Option-File Format external-locking
Option Sets Variable Yes, skip_external_locking
Disabled by skip-external-locking
Permitted Values Type boolean
Default FALSE
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.For more information about external locking, including conditions under which it can and cannot be used, see Section 7.10.5, “External Locking”.
Command-Line Format --flush
Option-File Format flush
Variable Name flush
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default OFF
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 C.5.4.2, “What to Do If MySQL Keeps Crashing”.
Command-Line Format --gdb
Option-File Format gdb
Permitted Values Type boolean
Default FALSE
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.Command-Line Format --general-log
Option-File Format general-log
Option Sets Variable Yes, general_log
Variable Name general_log
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default OFF
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.Command-Line Format --init-file=file_name
Option-File Format init-file=file_name
Option Sets Variable Yes, init_file
Variable Name init_file
Variable Scope Global Dynamic Variable No Permitted Values Type 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
compiler flag. See Section 2.11.4, “MySQL Source-Configuration Options”.--innodb-
xxx
The
InnoDB
options are listed in Section 13.6.4, “InnoDB
Startup Options and System Variables”.--language=
lang_name
, -Llang_name
Version Deprecated 5.5.0 Command-Line Format --language=name
-L
Option-File Format language
Option Sets Variable Yes, language
Variable Name language
Variable Scope Global Dynamic Variable No Deprecated 5.5.0, by lc-messages-dir
Permitted Values Type directory name
Default /usr/local/mysql/share/mysql/english/
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.2, “Setting the Error Message Language”.As of MySQL 5.5,
--lc-messages-dir
and--lc-messages
should be used rather than--language
, which is deprecated and handled as an alias for--lc-messages-dir
.Command-Line Format --large-pages
Option-File Format large-pages
Option Sets Variable Yes, large_pages
Variable Name large_pages
Variable Scope Global Dynamic Variable No Platform Specific linux Permitted Values Type (linux) boolean
Default FALSE
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.
MySQL 5.5 supports the Linux implementation of large page support (which is called HugeTLB in Linux). See Section 7.11.4.2, “Enabling Large Page Support”. For Solaris support of large pages, see the description of the
--super-large-pages
option.--large-pages
is disabled by default.Command-Line Format --lc-messages=name
Option-File Format lc-messages
Option Sets Variable Yes, lc_messages
Variable Name lc-messages
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type string
The locale to use for error messages. The server converts the argument to a language name and combines it with the value of the
--lc-messages-dir
to produce the location for the error message file. See Section 9.2, “Setting the Error Message Language”.Command-Line Format --lc-messages-dir=path
Option-File Format lc-messages-dir
Option Sets Variable Yes, lc_messages_dir
Variable Name lc-messages-dir
Variable Scope Global Dynamic Variable No Permitted Values Type directory name
The directory where error messages are located. The value is used together with the value of
--lc-messages
to produce the location for the error message file. See Section 9.2, “Setting the Error Message Language”.--log[=
,file_name
]-l [
file_name
]Command-Line Format --log[=name]
-l
Option-File Format log
Option Sets Variable Yes, log
Variable Name log
Variable Scope Global Dynamic Variable Yes Deprecated 5.1.29, by general-log
Permitted Values Type string
Default OFF
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. If you omit the file name, MySQL uses
as the file name. See Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”, and Section 5.2.3, “The General Query Log”.host_name
.logThe
--log
option is deprecated and will be removed (along with thelog
system variable) in MySQL 7.0. Instead, use the--general_log
option to enable the general query log and the--general_log_file=
option to set the general query log file name.file_name
Command-Line Format --log-error[=name]
Option-File Format log-error
Option Sets Variable Yes, log_error
Variable Name log_error
Variable Scope Global Dynamic Variable No Permitted Values Type 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
. If the file name has no extension, the server adds an extension ofhost_name
.err.err
.Command-Line Format --log-isam[=name]
Option-File Format log-isam
Permitted Values Type file name
Log all
MyISAM
changes to this file (used only when debuggingMyISAM
).Command-Line Format --log-long-format
-0
Option-File Format log-long-format
Deprecated 4.1 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-long-format
was removed in MySQL 5.5.3.Command-Line Format --log-output[=name]
Option-File Format log-output
Option Sets Variable Yes, log_output
Variable Name log_output
Variable Scope Global Dynamic Variable Yes Permitted Values Type set
Default FILE
Valid Values TABLE
,FILE
,NONE
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
, orNONE
. If the option is given without a value, the default isFILE
.TABLE
select logging to thegeneral_log
andslow_log
tables in themysql
database as a destination.FILE
selects logging to log files as a destination.NONE
disables logging. IfNONE
is present in the option value, it takes precedence over any other words that are present.TABLE
andFILE
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. ForFILE
logging, the--general_log_file
and-slow_query_log_file
options determine the log file location. For more information, see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”.--log-queries-not-using-indexes
Command-Line Format --log-queries-not-using-indexes
Option-File Format log-queries-not-using-indexes
Option Sets Variable Yes, log_queries_not_using_indexes
Variable Name log_queries_not_using_indexes
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
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.
Command-Line Format --log-short-format
Option-File Format log-short-format
Permitted Values Type boolean
Default FALSE
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.
Command-Line Format --log-slow-admin-statements
Option-File Format log-slow-admin-statements
Permitted Values Type boolean
Default FALSE
Log slow administrative statements such as
OPTIMIZE TABLE
,ANALYZE TABLE
, andALTER TABLE
to the slow query log.--log-slow-queries[=
file_name
]Command-Line Format --log-slow-queries[=name]
Option-File Format log-slow-queries
Option Sets Variable Yes, log_slow_queries
Variable Name log_slow_queries
Variable Scope Global Dynamic Variable Yes Deprecated 5.1.29, by slow-query-log
Permitted Values Type boolean
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. If you omit the file name, MySQL uses
as the file name. See Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”, and Section 5.2.5, “The Slow Query Log”.host_name
-slow.logThe
--log-slow-queries
option is deprecated and will be removed (along with thelog_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=
option to set the slow query log file name.file_name
Command-Line Format --log-tc=name
Option-File Format log-tc
Permitted Values Type file name
Default tc.log
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.Command-Line Format --log-tc-size=#
Option-File Format log-tc-size
Permitted Values Platform Bit Size 32
Type numeric
Default 24576
Max Value 4294967295
Permitted Values Platform Bit Size 64
Type numeric
Default 24576
Max Value 18446744073709547520
The size in bytes of the memory-mapped transaction coordinator log. The default size is 24KB.
--log-warnings[=
,level
]-W [
level
]Command-Line Format --log-warnings[=#]
-W [#]
Option-File Format log-warnings
Option Sets Variable Yes, log_warnings
Variable Name log_warnings
Variable Scope Global, Session Dynamic Variable Yes Disabled by skip-log-warnings
Permitted Values Platform Bit Size 64
Type numeric
Default 1
Range 0-18446744073709547520
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 defaultlevel
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, and access-denied errors for new connection attempts are written. See Section C.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. The server logs messages about statements that are unsafe for statement-based logging only if--log-warnings
is enabled.Command-Line Format --low-priority-updates
Option-File Format low-priority-updates
Option Sets Variable Yes, low_priority_updates
Variable Name low_priority_updates
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Default FALSE
Give table-modifying operations (
INSERT
,REPLACE
,DELETE
,UPDATE
) lower priority than selects. This can also be done using{INSERT | REPLACE | DELETE | UPDATE} LOW_PRIORITY ...
to lower the priority of only one query, or bySET 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.10.2, “Table Locking Issues”.--min-examined-row-limit=
number
Command-Line Format --min-examined-row-limit=#
Option-File Format min-examined-row-limit
Variable Name min_examined_row_limit
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Platform Bit Size 32
Type numeric
Default 0
Range 0-4294967295
Permitted Values Platform Bit Size 64
Type numeric
Default 0
Range 0-18446744073709547520
When this option is set, queries which examine fewer than
number
rows are not written to the slow query log. The default is 0.Command-Line Format --memlock
Option-File Format memlock
Variable Name locked_in_memory
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default FALSE
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 themlockall()
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 notmlockall()
(and thus this option) is supported by checking to see whether or not it is defined in the systemmman.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;
ImportantUsing 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.6, “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.Command-Line Format --myisam-block-size=#
Option-File Format myisam-block-size
Permitted Values Type numeric
Default 1024
Range 1024-16384
The block size to be used for
MyISAM
index pages.--myisam-recover[=
option
[,option
]...]]This option is renamed as of MySQL 5.5.3 to
--myisam-recover-options
. See the description of that option for more information.--myisam-recover-options[=
option
[,option
]...]]Version Introduced 5.5.3 Command-Line Format --myisam-recover-options[=name]
Option-File Format myisam-recover-options
Option Sets Variable Yes, myisam_recover_options
Permitted Values Type enumeration
Default OFF
Valid Values OFF
,DEFAULT
,BACKUP
,FORCE
,QUICK
Set the
MyISAM
storage engine recovery mode. The option value is any combination of the values ofDEFAULT
,OFF
,BACKUP
,FORCE
, orQUICK
. If you specify multiple values, separate them by commas. Specifying the option with no argument is the same as specifyingDEFAULT
, and specifying with an explicit value of""
disables recovery (same as not giving the option). If recovery is enabled, each time mysqld opens aMyISAM
table, it checks whether the table is marked as crashed or was not 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.
Option Description DEFAULT
Recovery without backup, forcing, or quick checking. OFF
Recovery without backup, forcing, or quick checking. BACKUP
If the data file was changed during recovery, save a backup of the
file astbl_name
.MYD
.tbl_name-datetime
.BAKFORCE
Run recovery even if we would lose more than one row from the .MYD
file.QUICK
Do not check the rows in the table if there are not any delete blocks. 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.This option was named
--myisam-recover
, before MySQL 5.5.3. The old option name still works because it is recognized as an unambiguous prefix of the new name,--myisam-recover-options
. (Option prefix recognition occurs as described in Section 4.2.3, “Specifying Program Options”.)The option value
OFF
is available as of MySQL 5.5.3.Command-Line Format --old-alter-table
Option-File Format old-alter-table
Option Sets Variable Yes, old_alter_table
Variable Name old-alter-table
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Default OFF
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 ofALTER TABLE
, see Section 12.1.6, “ALTER TABLE
Syntax”.Command-Line Format --old_passwords
Option-File Format old-passwords
Option Sets Variable Yes, old_passwords
Variable Name old_passwords
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Default FALSE
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.3.2.3, “Password Hashing in MySQL”.
Command-Line Format --old-style-user-limits
Option-File Format old-style-user-limits
Permitted Values Type boolean
Default FALSE
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, “Setting Account Resource Limits”.Command-Line Format --one-thread
Option-File Format 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.
This option is deprecated; use
--thread_handling=no-threads
instead.Command-Line Format --open-files-limit=#
Option-File Format open-files-limit
Option Sets Variable Yes, open_files_limit
Variable Name open_files_limit
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 0
Range 0-65535
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 withsetrlimit()
. 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
andtable_open_cache
to estimate whether more descriptors will be needed.Command-Line Format --partition
Option-File Format partition
Option Sets Variable Yes, have_partitioning
Variable Name partition
Variable Scope Global Dynamic Variable No Disabled by skip-partition
Permitted Values Type boolean
Default ON
Enables or disables user-defined partitioning support in the MySQL Server.
Command-Line Format --pid-file=file_name
Option-File Format pid-file=file_name
Option Sets Variable Yes, pid_file
Variable Name pid_file
Variable Scope Global Dynamic Variable No Permitted Values Type file name
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.
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 forInnoDB
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
disablesinnodb_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.Command-Line Format --plugin-load=plugin_list
Option-File Format plugin-load
Permitted Values Type string
This option tells the server to load the named plugins at startup. The option value is a semicolon-separated list of
pairs. Eachname
=plugin_library
name
is the name of the plugin, andplugin_library
is the name of the shared library that contains the plugin code. Each library file must be located in the directory named by theplugin_dir
system variable. For example, if plugins namedmyplug1
andmyplug2
have library filesmyplug1.so
andmyplug2.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.Each named plugin is loaded for a single invocation of mysqld only. After a restart, the plugin is not loaded unless
--plugin-load
is used again. This is in contrast toINSTALL PLUGIN
, which adds an entry to themysql.plugins
table to cause the plugin to be loaded for every normal server startup.Under normal startup, the server determines which plugins to load by reading the
mysql.plugins
system table. If the server is started with the--skip-grant-tables
option, it does not consult themysql.plugins
table and thus does not load plugins installed with theINSTALL PLUGIN
statement.--plugin-load
enables plugins to be loaded even when--skip-grant-tables
is given.--plugin-load
also enables plugins to be loaded at startup under configurations when plugins cannot be loaded at runtime.For additional information about plugin loading, see Section 12.4.3.1, “Installing and Uninstalling Plugins”.
--port=
,port_num
-P
port_num
Command-Line Format --port=#
-P
Option-File Format port
Option Sets Variable Yes, port
Variable Name port
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 3306
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.Command-Line Format --port-open-timeout=#
Option-File Format port-open-timeout
Permitted Values Type numeric
Default 0
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.
Command-Line Format --safe-mode
Option-File Format safe-mode
Deprecated 5.0 Skip some optimization stages.
Command-Line Format --safe-show-database
(until 4.1.1) Option-File Format safe-show-database
Variable Name safe_show_database
Variable Scope Global Dynamic Variable Yes Deprecated 4.0.2 Permitted Values Type boolean
This option is deprecated and does not do anything because there is a
SHOW DATABASES
privilege that can be used to control access to database names on a per-account basis. See Section 5.4.1, “Privileges Provided by MySQL”.--safe-show-database
was removed in MySQL 5.5.3.Command-Line Format --safe-user-create
Option-File Format safe-user-create
Permitted Values Type boolean
Default FALSE
If this option is enabled, a user cannot create new MySQL users by using the
GRANT
statement unless the user has theINSERT
privilege for themysql.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.Command-Line Format --secure-auth
Option-File Format secure-auth
Option Sets Variable Yes, secure_auth
Variable Name secure_auth
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default FALSE
Disallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords.
Command-Line Format --secure-file-priv=path
Option-File Format secure-file-priv=path
Option Sets Variable Yes, secure_file_priv
Variable Name secure-file-priv
Variable Scope Global Dynamic Variable No Permitted Values Type string
This option limits the effect of the
LOAD_FILE()
function and theLOAD DATA
andSELECT ... INTO OUTFILE
statements to work only with files in the specified directory.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.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.10.3, “Concurrent Inserts”.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.10.5, “External Locking”.
External locking has been disabled by default since MySQL 4.0.
Command-Line Format --skip-event-scheduler
--disable-event-scheduler
Option-File Format 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.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 that were installed with theINSTALL PLUGIN
statement, user-defined functions (UDFs), and scheduled events. To cause plugins to be loaded anyway, use the--plugin-load
option.--skip-grant-tables
is unavailable if MySQL was configured with theDISABLE_GRANT_OPTIONS
compiler flag. See Section 2.11.4, “MySQL Source-Configuration Options”.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.11.5.2, “How MySQL Uses DNS”.
Disable the
InnoDB
storage engine. In this case, the server will not start if the default storage engine is set toInnoDB
. Use--default-storage-engine
to set the default to some other engine if necessary.Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all
Host
column values in the grant tables must be IP addresses orlocalhost
. See Section 7.11.5.2, “How MySQL Uses DNS”.Do not listen for TCP/IP connections at all. All interaction with mysqld must be made using 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 permitted. See Section 7.11.5.2, “How MySQL Uses DNS”.
Command-Line Format --skip-partition
--disable-partition
Option-File Format skip-partition
Disables user-defined partitioning. Existing partitioned tables cannot be accessed when the server has been started with this option.
Options that begin with
--ssl
specify whether to permit clients to connect using SSL and indicate where to find SSL keys and certificates. See Section 5.5.8.3, “SSL Command Options”.Command-Line Format --standalone
Option-File Format standalone
Platform Specific windows Available on Windows only; instructs the MySQL server not to run as a service.
Standard use of large pages in MySQL attempts to use the largest size supported, up to 4MB. Under Solaris, a “super large pages” feature enables uses of pages up to 256MB. This feature is available for recent SPARC platforms. It can be enabled or disabled by using the
--super-large-pages
or--skip-super-large-pages
option.--symbolic-links
,--skip-symbolic-links
Command-Line Format --symbolic-links
Option-File Format symbolic-links
Enable or disable symbolic link support. This option has different effects on Windows and Unix:
On Windows, enabling symbolic links enables you to establish a symbolic link to a database directory by creating a
file that contains the path to the real directory. See Section 7.11.3.1.3, “Using Symbolic Links for Databases on Windows”.db_name
.symOn Unix, enabling symbolic links means that you can link a
MyISAM
index file or data file to another directory with theINDEX DIRECTORY
orDATA DIRECTORY
options of theCREATE TABLE
statement. If you delete or rename the table, the files that its symbolic links point to also are deleted or renamed. See Section 7.11.3.1.2, “Using Symbolic Links for Tables on Unix”.
Version Removed 5.5.6 Command-Line Format --skip-safemalloc
Option-File Format skip-safemalloc
Previously, if MySQL was configured with full debugging support, 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 do not need it by using the
--skip-safemalloc
option.safemalloc
, along with this option, was removed in MySQL 5.5.6.Command-Line Format --skip-show-database
Option-File Format skip-show-database
Option Sets Variable Yes, skip_show_database
Variable Name skip_show_database
Variable Scope Global Dynamic Variable No With this option, the
SHOW DATABASES
statement is permitted only to users who have theSHOW DATABASES
privilege, and the statement displays all database names. Without this option,SHOW DATABASES
is permitted to all users, but displays each database name only if the user has theSHOW DATABASES
privilege or some privilege for the database. Note that any global privilege is considered a privilege for the database.Command-Line Format --skip-stack-trace
Option-File Format skip-stack-trace
Do not 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.
Command-Line Format --skip-thread-priority
Option-File Format skip-thread-priority
Deprecated 5.1.29 Disable using thread priorities for faster response time. This option is deprecated.
Command-Line Format --slow-query-log
Option-File Format slow-query-log
Option Sets Variable Yes, slow_query_log
Variable Name slow_query_log
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default OFF
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.Command-Line Format --socket=name
Option-File Format socket
Option Sets Variable Yes, socket
Variable Name socket
Variable Scope Global Dynamic Variable No Permitted Values Type file name
Default /tmp/mysql.sock
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 isMySQL
(not case sensitive).--sql-mode=
value
[,value
[,value
...]]Command-Line Format --sql-mode=name
Option-File Format sql-mode
Option Sets Variable Yes, sql_mode
Variable Name sql_mode
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type set
Default ''
Valid Values ALLOW_INVALID_DATES
,ANSI_QUOTES
,ERROR_FOR_DIVISION_BY_ZERO
,HIGH_NOT_PRECEDENCE
,IGNORE_SPACE
,NO_AUTO_CREATE_USER
,NO_AUTO_VALUE_ON_ZERO
,NO_BACKSLASH_ESCAPES
,NO_DIR_IN_CREATE
,NO_ENGINE_SUBSTITUTION
,NO_FIELD_OPTIONS
,NO_KEY_OPTIONS
,NO_TABLE_OPTIONS
,NO_UNSIGNED_SUBTRACTION
,NO_ZERO_DATE
,NO_ZERO_IN_DATE
,ONLY_FULL_GROUP_BY
,PAD_CHAR_TO_FULL_LENGTH
,PIPES_AS_CONCAT
,REAL_AS_FLOAT
,STRICT_ALL_TABLES
,STRICT_TRANS_TABLES
Set the SQL mode. See Section 5.1.7, “Server SQL Modes”.
Command-Line Format --sysdate-is-now
Option-File Format sysdate-is-now
Permitted Values Type boolean
Default FALSE
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 ofNOW()
. This option causesSYSDATE()
to be an alias forNOW()
. For information about the implications for binary logging and replication, see the description forSYSDATE()
in Section 11.7, “Date and Time Functions” and forSET TIMESTAMP
in Section 5.1.4, “Server System Variables”.--tc-heuristic-recover={COMMIT|ROLLBACK}
Command-Line Format --tc-heuristic-recover=name
Option-File Format tc-heuristic-recover
Permitted Values Type enumeration
Valid Values COMMIT
,RECOVER
The type of decision to use in the heuristic recovery process. Currently, this option is unused.
Command-Line Format --temp-pool
Option-File Format temp-pool
Permitted Values Type boolean
Default TRUE
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. This option is ignored except on Linux.
Command-Line Format --transaction-isolation=name
Option-File Format transaction-isolation
Permitted Values Type enumeration
Valid Values READ-UNCOMMITTED
,READ-COMMITTED
,REPEATABLE-READ
,SERIALIZABLE
Sets the default transaction isolation level. The
level
value can beREAD-UNCOMMITTED
,READ-COMMITTED
,REPEATABLE-READ
, orSERIALIZABLE
. See Section 12.3.6, “SET TRANSACTION
Syntax”.--tmpdir=
,path
-t
path
Command-Line Format --tmpdir=path
-t
Option-File Format tmpdir
Option Sets Variable Yes, tmpdir
Variable Name tmpdir
Variable Scope Global Dynamic Variable No Permitted Values Type file name
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. 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 C.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 orLOAD 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
}Command-Line Format --user=name
-u name
Option-File Format user
Permitted Values Type string
Run the mysqld server as the user having the name
user_name
or the numeric user IDuser_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 asroot
. See Section 5.3.1, “General Security Guidelines”.To avoid a possible security hole where a user adds a
--user=root
option to amy.cnf
file (thus causing the server to run asroot
), 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 thanroot
. The option in/etc/my.cnf
is found before any other--user
options, which ensures that the server runs as a user other thanroot
, and that a warning results if any other--user
option is found.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
--
.
For example, var_name
=value
--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 permissible value if only certain values are permitted.
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-
command-line option.
var_name
=value
You can change the values of most system variables for a running
server with the
SET
statement. See Section 12.4.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.11.2, “Tuning Server Parameters”, includes information on optimizing the server by tuning system variables.