Chapter 5. MySQL Server Administration
Table of Contents
- 5.1. The MySQL Server
- 5.2. MySQL Server Logs
- 5.3. General Security Issues
- 5.4. The MySQL Access Privilege System
- 5.5. MySQL User Account Management
- 5.5.1. User Names and Passwords
- 5.5.2. Adding User Accounts
- 5.5.3. Removing User Accounts
- 5.5.4. Limiting Account Resources
- 5.5.5. Assigning Account Passwords
- 5.5.6. Password Security in MySQL
- 5.5.7. Using SSL for Secure Connections
- 5.5.8. Connecting to MySQL Remotely from Windows with SSH
- 5.5.9. Auditing MySQL Account Activity
- 5.6. Running Multiple MySQL Servers on the Same Machine
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:
Server configuration
The server log files
Security issues and user-account management
Management of multiple servers on a single machine
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 are supported by your MySQL server installation, see
Section 12.5.5.13, “SHOW ENGINES Syntax”.
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. mysqld Option/Variable Summary
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
[
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.3, “Security-Related mysqld Options”.
SSL-related options: See Section 5.5.7.3, “SSL Command Options”.
Binary log control options: See Section 16.1.2.4, “Binary Log Options and Variables”.
Replication-related options: See Section 16.1.2, “Replication and Binary Logging Options and Variables”.
Options specific to particular storage engines: See Section 13.1.1, “
MyISAMStartup Options”, Section 13.5.3, “BDBStartup Options”, Section 13.2.3, “InnoDBStartup Options and System Variables”, and Section 17.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.
--help,-?Command Line Format -?Config File Format helpDisplay a short help message and exit. Use both the
--verboseand--helpoptions to see the full message.Version Introduced 5.0.3 Command Line Format --allow-suspicious-udfsConfig File Format allow-suspicious-udfsValue Set Type booleanDefault FALSEThis option controls whether user-defined functions that have only an
xxxsymbol 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. This option was added in version 5.0.3. See Section 21.2.2.6, “User-Defined Function Security Precautions”.Command Line Format --ansiConfig File Format ansiUse standard (ANSI) SQL syntax instead of MySQL syntax. For more precise control over the server SQL mode, use the
--sql-modeoption instead. See Section 1.7.3, “Running MySQL in ANSI Mode”, and Section 5.1.7, “Server SQL Modes”.Command Line Format --basedir=nameConfig File Format basedirOption Sets Variable Yes, basedirVariable Name basedirVariable Scope Global Dynamic Variable No Value Set Type filenameThe path to the MySQL installation directory. All paths are usually resolved relative to this directory.
Command Line Format --big-tablesConfig File Format big-tablesOption Sets Variable Yes, big_tablesVariable Name big-tablesVariable Scope Session Dynamic Variable Yes Value Set Type booleanAllow 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=nameConfig File Format bind-addressValue Set Type stringDefault 0.0.0.0Range 0.0.0.0-255.255.255.255The 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.0is specified, the server listens on all interfaces.Command Line Format --bootstrapConfig File Format bootstrapThis 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-optionsoption. See Section 2.16.2, “Typical configure Options”.Command Line Format --character-sets-dir=nameConfig File Format character-sets-dirOption Sets Variable Yes, character_sets_dirVariable Name character-sets-dirVariable Scope Global Dynamic Variable No Value Set Type filenameThe directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
--character-set-client-handshakeCommand Line Format --character-set-client-handshakeConfig File Format character-set-client-handshakeValue Set Type booleanDefault TRUEDon'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_nameVersion Introduced 5.0.19 Command Line Format --character-set-filesystem=nameConfig File Format character-set-filesystemOption Sets Variable Yes, character_set_filesystemVariable Name character_set_filesystemVariable Scope Both Dynamic Variable Yes Value Set Type stringThe file system character set. This option sets the
character_set_filesystemsystem variable. It was added in MySQL 5.0.19.--character-set-server=,charset_name-Ccharset_nameCommand Line Format --character-set-serverConfig File Format character-set-serverOption Sets Variable Yes, character_set_serverVariable Name character_set_serverVariable Scope Both Dynamic Variable Yes Value Set Type stringUse
charset_nameas 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-serverto specify the collation.--chroot=,path-rpathCommand Line Format --chroot=nameConfig File Format chrootValue Set Type filenamePut 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 INFILEandSELECT ... INTO OUTFILE.--collation-server=collation_nameCommand Line Format --collation-serverConfig File Format collation-serverOption Sets Variable Yes, collation_serverVariable Name collation_serverVariable Scope Both Dynamic Variable Yes Value Set Type stringUse
collation_nameas the default server collation. See Section 9.2, “The Character Set Used for Data and Sorting”.Command Line Format --consoleConfig File Format consolePlatform Specific windows (Windows only.) Write error log messages to
stderrandstdouteven if--log-erroris specified. mysqld does not close the console window if this option is used.Command Line Format --core-fileConfig File Format core-fileValue Set Type booleanDefault TRUEWrite 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.pidpidrepresents the process ID of the server process. On Mac OS X, a core file namedcore.is written to thepid/coresdirectory. 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-sizeoption 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--useroption. 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-hpathCommand Line Format --datadir=nameConfig File Format datadirOption Sets Variable Yes, datadirVariable Name datadirVariable Scope Global Dynamic Variable No Value Set Type filenameThe path to the data directory.
--debug[=,debug_options]-# [debug_options]Command Line Format --debug[=debug_options]Config File Format debugVariable Name debugVariable Scope Both Dynamic Variable Yes Value Set Type stringDefault 'd:t:o,/tmp/mysqld.traceIf MySQL is configured with
--with-debug, you can use this option to get a trace file of what mysqld is doing. A typicaldebug_optionsstring is'd:t:o,. The default isfile_name''d:t:i:o,mysqld.trace'. See MySQL Internals: Porting.As of MySQL 5.0.25, using
--with-debugto 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.--default-character-set=(DEPRECATED)charset_nameVersion Deprecated 5.0 Command Line Format --default-character-set=nameConfig File Format default-character-setDeprecated 5.0 Value Set Type stringUse
charset_nameas 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_nameCommand Line Format --default-collation=nameVariable Name default-collationVariable Scope Dynamic Variable No Deprecated 4.1.3 Value Set Type stringUse
collation_nameas the default collation. This option is deprecated in favor of--collation-server. See Section 9.2, “The Character Set Used for Data and Sorting”.Command Line Format --default-storage-engine=nameConfig File Format default-storage-engineSet the default storage engine (table type) for tables. See Chapter 13, Storage Engines.
Version Deprecated 5.0 Command Line Format --default-table-type=nameConfig File Format default-table-typeDeprecated 5.0, by default-storage-engineValue Set Type stringThis option is a deprecated synonym for
--default-storage-engine.Command Line Format --default-time-zone=nameConfig File Format default-time-zoneValue Set Type stringSet the default server time zone. This option sets the global
time_zonesystem 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_zonesystem variable.--delay-key-write[={OFF|ON|ALL}]Command Line Format --delay-key-write[=name]Config File Format delay-key-writeOption Sets Variable Yes, delay_key_writeVariable Name delay-key-writeVariable Scope Global Dynamic Variable Yes Value Set Type enumerationDefault ONValid Values ON,OFF,ALLSpecify how to use delayed key writes. Delayed key writing causes key buffers not to be flushed between writes for
MyISAMtables.OFFdisables delayed key writes.ONenables delayed key writes for those tables that were created with theDELAY_KEY_WRITEoption.ALLdelays key writes for allMyISAMtables. See Section 7.5.3, “Tuning Server Parameters”, and Section 13.1.1, “MyISAMStartup Options”.Note
If you set this variable to
ALL, you should not useMyISAMtables 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=nameConfig File Format des-key-fileRead the default DES keys from this file. These keys are used by the
DES_ENCRYPT()andDES_DECRYPT()functions.Command Line Format --named_pipeConfig File Format enable-named-pipePlatform Specific windows Enable support for named pipes. This option can be used only with the mysqld-nt and mysqld-debug servers that support named-pipe connections.
Command Line Format --enable-pstackConfig File Format enable-pstackValue Set Type booleanDefault FALSEPrint a symbolic stack trace on failure.
--engine-condition-pushdown={ON|OFF}Version Introduced 5.0.3 Command Line Format --engine-condition-pushdownConfig File Format engine-condition-pushdownOption Sets Variable Yes, engine_condition_pushdownVariable Name engine_condition_pushdownVariable Scope Both Dynamic Variable Yes Value Set (>= 5.0.3) Type booleanDefault OFFSets the
engine_condition_pushdownsystem variable. For more information, see Section 7.2.7, “Condition Pushdown Optimization”.This variable was added in MySQL 5.0.3.
--exit-info[=,flags]-T [flags]Command Line Format --exit-info[=flags]Config File Format exit-infoValue Set Type numericThis 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-lockingConfig File Format external-lockingOption Sets Variable Yes, skip_external_lockingDisabled by skip-external-lockingValue Set Type booleanDefault FALSEEnable 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
lockddoes 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”.
Command Line Format --flushConfig File Format flushVariable Name flushVariable Scope Global Dynamic Variable Yes Value Set Type booleanDefault OFFFlush (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.1.4.2, “What to Do If MySQL Keeps Crashing”.
Command Line Format --gdbConfig File Format gdbValue Set Type booleanDefault FALSEInstall an interrupt handler for
SIGINT(needed to stop mysqld with^Cto set breakpoints) and disable stack tracing and core file handling. See MySQL Internals: Porting.Command Line Format --init-file=nameConfig File Format init-fileOption Sets Variable Yes, init_fileVariable Name init_fileVariable Scope Global Dynamic Variable No Value Set Type filenameRead 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-optionsoption. See Section 2.16.2, “Typical configure Options”.Version Removed 5.0.3 Version Deprecated 5.0.3 Command Line Format --innodb_safe_binlogConfig File Format innodb_safe_binlogDeprecated 5.0.3 Value Set Type booleanIf this option is given, then after a crash recovery by
InnoDB, mysqld truncates the binary log after the last not-rolled-back transaction in the log. The option also causesInnoDBto print an error if the binary log is smaller or shorter than it should be. See Section 5.2.3, “The Binary Log”. This option was removed in MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support.--innodb-xxxThe
InnoDBoptions are listed in Section 13.2.3, “InnoDBStartup Options and System Variables”.--language=lang_name, -Llang_nameCommand Line Format --language=nameConfig File Format languageOption Sets Variable Yes, languageVariable Name languageVariable Scope Global Dynamic Variable No Value Set Type filenameDefault /usr/local/mysql/share/mysql/english/Return client error messages in the given language.
lang_namecan 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”.Version Introduced 5.0.3 Command Line Format --large-pagesConfig File Format large-pagesOption Sets Variable Yes, large_pagesVariable Name large_pagesVariable Scope Global Dynamic Variable No Platform Specific linux Value Set Type (linux) booleanDefault FALSESome 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-pagesis disabled by default. It was added in MySQL 5.0.3.--log[=,file_name]-l [file_name]Command Line Format --log[=name]Config File Format logOption Sets Variable Yes, logVariable Name logVariable Scope Global Dynamic Variable No Deprecated 5.1.29, by general-logValue Set Type stringDefault OFFLog connections and SQL statements received from clients to this file. See Section 5.2.2, “The General Query Log”. If you omit the file name, MySQL uses
host_name.logCommand Line Format --log-error[=name]Config File Format log-errorOption Sets Variable Yes, log_errorVariable Name log_errorVariable Scope Global Dynamic Variable No Value Set Type filenameLog errors and startup messages to this file. See Section 5.2.1, “The Error Log”. If you omit the file name, MySQL uses
host_name.err.err.Command Line Format --log-isam[=name]Config File Format log-isamValue Set Type filenameLog all
MyISAMchanges to this file (used only when debuggingMyISAM).--log-long-format(DEPRECATED)Command Line Format --log-long-formatConfig File Format log-long-formatDeprecated 4.1 Log extra information to the update log, binary update 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-indexesoption is available for the purpose of logging queries that do not use indexes to the slow query log.--log-queries-not-using-indexesCommand Line Format --log-queries-not-using-indexesConfig File Format log-queries-not-using-indexesOption Sets Variable Yes, log_queries_not_using_indexesVariable Name log_queries_not_using_indexesVariable Scope Global Dynamic Variable Yes Deprecated 5.1.29, by slow-query-logValue Set Type booleanIf 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.4, “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-formatConfig File Format log-short-formatValue Set Type booleanDefault FALSEOriginally intended to log less information to the update log, binary log and slow query log, if they have been activated. However, this option is not operational.
Command Line Format --log-slow-admin-statementsConfig File Format log-slow-admin-statementsValue Set Type booleanDefault FALSELog slow administrative statements such as
OPTIMIZE TABLE,ANALYZE TABLE, andALTER TABLEto the slow query log.--log-slow-queries[=file_name]Command Line Format --log-slow-queries[=name]Config File Format log-slow-queriesOption Sets Variable Yes, log_slow_queriesVariable Name log_slow_queriesVariable Scope Global Dynamic Variable No Value Set Type booleanLog all queries that have taken more than
long_query_timeseconds to execute to this file. See Section 5.2.4, “The Slow Query Log”. See the descriptions of the--log-long-formatand--log-short-formatoptions for details.Version Introduced 5.0.3 Command Line Format --log-tc=nameConfig File Format log-tcValue Set Type filenameDefault tc.logThe 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. Added in MySQL 5.0.3.Version Introduced 5.0.3 Command Line Format --log-tc-size=#Config File Format log-tc-sizeValue Set Platform Bit Size 32Type numericDefault 24576Max Value 4294967295Value Set Platform Bit Size 64Type numericDefault 24576Max Value 18446744073709547520The size in bytes of the memory-mapped transaction coordinator log. The default size is 24KB. Added in MySQL 5.0.3.
--log-warnings[=,level]-W [level]Command Line Format --log-warnings[=#]Config File Format log-warningsOption Sets Variable Yes, log_warningsVariable Name log_warningsVariable Scope Both Dynamic Variable Yes Disabled by skip-log-warningsValue Set Platform Bit Size 32Type numericDefault 1Range 0-18446744073709547520Print 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 defaultlevelvalue 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.1.2.11, “Communication Errors and Aborted Connections”.If a slave server was started with
--log-warningsenabled, 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.Command Line Format --low-priority-updatesConfig File Format low-priority-updatesOption Sets Variable Yes, low_priority_updatesVariable Name low_priority_updatesVariable Scope Both Dynamic Variable Yes Value Set Type booleanDefault FALSEGive 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 bySET LOW_PRIORITY_UPDATES=1to 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”.Command Line Format --memlockConfig File Format memlockVariable Name locked_in_memoryVariable Scope Global Dynamic Variable No Value Set Type booleanDefault FALSELock 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.
--memlockworks 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.hfile, like this:shell>
grep mlockall /usr/include/sys/mman.hIf
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.Command Line Format --myisam-block-size=#Config File Format myisam-block-sizeValue Set Type numericDefault 1024Range 1024-16384The block size to be used for
MyISAMindex pages.--myisam-recover[=option[,option]...]]Command Line Format --myisam-recover[=name]Config File Format myisam-recoverValue Set Type enumerationDefault OFFValid Values DEFAULT,BACKUP,FORCE,QUICKSet the
MyISAMstorage engine recovery mode. The option value is any combination of the values ofDEFAULT,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 aMyISAMtable, 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.
Option Description DEFAULTRecovery without backup, forcing, or quick checking. BACKUPIf the data file was changed during recovery, save a backup of the tbl_name.MYDtbl_name-datetime.BAKFORCERun recovery even if we would lose more than one row from the .MYDfile.QUICKDon't check the rows in the table if there aren't 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.Command Line Format --old_passwordsConfig File Format old-passwordsOption Sets Variable Yes, old_passwordsVariable Name old_passwordsVariable Scope Both Dynamic Variable Yes Value Set Type booleanDefault FALSEForce 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”.
Version Introduced 5.0.3 Command Line Format --old-style-user-limitsConfig File Format old-style-user-limitsValue Set Type booleanDefault FALSEEnable 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
usertable.) See Section 5.5.4, “Limiting Account Resources”. This option was added in MySQL 5.0.3.Command Line Format --one-threadConfig File Format one-threadOnly use one thread (for debugging under Linux). This option is available only if the server is built with debugging enabled. See MySQL Internals: Porting.
Command Line Format --open-files-limit=#Config File Format open-files-limitOption Sets Variable Yes, open_files_limitVariable Name open_files_limitVariable Scope Global Dynamic Variable No Value Set Type numericDefault 0Range 0-65535Changes 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_connectionsandtable_cacheto estimate whether more descriptors will be needed.Command Line Format --pid-file=nameConfig File Format pid-fileOption Sets Variable Yes, pid_fileVariable Name pid_fileVariable Scope Global Dynamic Variable No Value Set Type filenameThe path name of the process ID file. This file is used by other programs such as mysqld_safe to determine the server's process ID.
--port=,port_num-Pport_numCommand Line Format --port=#Config File Format portOption Sets Variable Yes, portVariable Name portVariable Scope Global Dynamic Variable No Value Set Type numericDefault 3306The 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
rootsystem user.Version Introduced 5.0.19 Command Line Format --port-open-timeout=#Config File Format port-open-timeoutValue Set Type numericDefault 0On 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.0.19.
Version Deprecated 5.0 Command Line Format --safe-modeConfig File Format safe-modeDeprecated 5.0 Skip some optimization stages.
--safe-show-database(DEPRECATED)Command Line Format --safe-show-databaseConfig File Format safe-show-databaseVariable Name safe_show_databaseVariable Scope Global Dynamic Variable Yes Deprecated 4.0.2 Value Set Type booleanCommand Line Format --safe-user-createConfig File Format safe-user-createValue Set Type booleanDefault FALSEIf this option is enabled, a user cannot create new MySQL users by using the
GRANTstatement unless the user has theINSERTprivilege for themysql.usertable 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
GRANTstatement to give privileges to other users.Command Line Format --secure-authConfig File Format secure-authOption Sets Variable Yes, secure_authVariable Name secure_authVariable Scope Global Dynamic Variable Yes Value Set Type booleanDefault FALSEDisallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords.
Version Introduced 5.0.38 Command Line Format --secure-file-privConfig File Format secure-file-privOption Sets Variable Yes, secure_file_privVariable Name secure_file_privVariable Scope Global Dynamic Variable No Value Set Type stringThis option limits the effect of the
LOAD_FILE()function and theLOAD DATAandSELECT ... INTO OUTFILEstatements to work only with files in the specified directory.This option was added in MySQL 5.0.38.
Enable shared-memory connections by local clients. This option is available only on Windows.
--shared-memory-base-name=nameThe 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.Disable the
BDBstorage engine. This saves memory and might speed up some operations. Do not use this option if you requireBDBtables.Turn off the ability to select and insert at the same time on
MyISAMtables. (This is to be used only if you think you have found a bug in this feature.) See Section 7.3.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.3.4, “External Locking”.
External locking has been disabled by default since MySQL 4.0.
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 PRIVILEGESstatement after connecting to the server. This option also suppresses loading of user-defined functions (UDFs).This option is unavailable if MySQL was configured with the
--disable-grant-optionsoption. See Section 2.16.2, “Typical configure 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.5.11, “How MySQL Uses DNS”.
Disable the
InnoDBstorage engine. This saves memory and disk space and might speed up some operations. Do not use this option if you requireInnoDBtables.Disable the
MERGEstorage engine. This option was added in MySQL 5.0.24. It can be used if the following behavior is undesirable: If a user has access toMyISAMtablet, that user can create aMERGEtablemthat accessest. However, if the user's privileges ontare subsequently revoked, the user can continue to accesstby doing so throughm.Do not resolve host names when checking client connections. Use only IP numbers. If you use this option, all
Hostcolumn values in the grant tables must be IP numbers orlocalhost. See Section 7.5.11, “How MySQL Uses DNS”.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”.
Options that begin with
--sslspecify 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”.Command Line Format --standaloneConfig File Format standalonePlatform Specific windows Instructs the MySQL server not to run as a service.
--symbolic-links,--skip-symbolic-linksCommand Line Format --symbolic-linksConfig File Format symbolic-linksEnable 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
db_name.symOn Unix, enabling symbolic links means that you can link a
MyISAMindex file or data file to another directory with theINDEX DIRECTORYorDATA DIRECTORYoptions of theCREATE TABLEstatement. If you delete or rename the table, the files that its symbolic links point to also are deleted or renamed. See Section 7.6.1.2, “Using Symbolic Links for Tables on Unix”.
Command Line Format --skip-safe-mallocConfig File Format skip-safemallocIf 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-safemallocoption.Command Line Format --skip-show-databaseConfig File Format skip-show-databaseOption Sets Variable Yes, skip_show_databaseVariable Name skip_show_databaseVariable Scope Global Dynamic Variable No With this option, the
SHOW DATABASESstatement is allowed only to users who have theSHOW DATABASESprivilege, and the statement displays all database names. Without this option,SHOW DATABASESis allowed to all users, but displays each database name only if the user has theSHOW DATABASESprivilege or some privilege for the database. Note that any global privilege is considered a privilege for the database.Command Line Format --skip-stack-traceConfig File Format skip-stack-traceDon'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.
Command Line Format --skip-thread-priorityConfig File Format skip-thread-priorityDeprecated 5.1.29 Disable using thread priorities for faster response time.
Command Line Format --socket=nameConfig File Format socketOption Sets Variable Yes, socketVariable Name socketVariable Scope Global Dynamic Variable No Value Set Type filenameDefault /tmp/mysql.sockOn Unix, this option specifies the Unix socket file to use when listening for local connections. The default value is
/tmp/mysql.sock. 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=nameConfig File Format sql-modeOption Sets Variable Yes, sql_modeVariable Name sql_modeVariable Scope Both Dynamic Variable Yes Value Set Type setDefault ''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_TABLESSet the SQL mode. See Section 5.1.7, “Server SQL Modes”.
Version Introduced 5.0.20 Command Line Format --sysdate-is-nowConfig File Format sysdate-is-nowValue Set Type booleanDefault FALSEAs of MySQL 5.0.12,
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.6, “Date and Time Functions” and forSET TIMESTAMPin Section 5.1.4, “Session System Variables”.This option was added in MySQL 5.0.20.
--tc-heuristic-recover={COMMIT|ROLLBACK}Version Introduced 5.0.3 Command Line Format --tc-heuristic-recover=nameConfig File Format tc-heuristic-recoverValue Set Type enumerationValid Values COMMIT,RECOVERThe type of decision to use in the heuristic recovery process. Currently, this option is unused. Added in MySQL 5.0.3.
Command Line Format --temp-poolConfig File Format temp-poolValue Set Type booleanDefault TRUEThis 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.
Command Line Format --transaction-isolation=nameConfig File Format transaction-isolationValue Set Type enumerationValid Values READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLESets the default transaction isolation level. The
levelvalue can beREAD-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ, orSERIALIZABLE. See Section 12.4.6, “SET TRANSACTIONSyntax”.--tmpdir=,path-tpathCommand Line Format --tmpdir=nameConfig File Format tmpdirOption Sets Variable Yes, tmpdirVariable Name tmpdirVariable Scope Global Dynamic Variable No Value Set Type filenameThe path of the directory to use for creating temporary files. It might be useful if your default
/tmpdirectory 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--tmpdirto 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.1.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 INFILEoperations. 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=nameConfig File Format userValue Set Type stringRun the mysqld server as the user having the name
user_nameor 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=rootoption to amy.cnffile (thus causing the server to run asroot), mysqld uses only the first--useroption specified and produces a warning if there are multiple--useroptions. Options in/etc/my.cnfand$MYSQL_HOME/my.cnfare processed before command-line options, so it is recommended that you put a--useroption in/etc/my.cnfand specify a value other thanroot. The option in/etc/my.cnfis found before any other--useroptions, which ensures that the server runs as a user other thanroot, and that a warning results if any other--useroption is found.Use this option with the
--helpoption for detailed help.--version,-VCommand Line Format --versionConfig File Format versionVariable Name versionVariable Scope Global Dynamic Variable No 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 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-
command-line option.
var_name=value
It is also possible to set variables by using
--set-variable=
or var_name=value-O
syntax. This syntax is deprecated.
var_name=value
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.3, “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.
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 VARIABLESstatement.
This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.0 releases. For historical information concerning their implementation, please see http://www.mysql.com/products/enterprise//4.1/en/.
The following table lists all available system variables:
Table 5.2. mysqld System Variable Summary
| Name | Cmd-Line | Option file | System Var | Var Scope | Dynamic |
|---|---|---|---|---|---|
| auto_increment_increment | Yes | Yes | Yes | Both | Yes |
| auto_increment_offset | Yes | Yes | Yes | Both | Yes |
| autocommit | Yes | Session | Yes | ||
| automatic_sp_privileges | Yes | Global | Yes | ||
| back_log | Yes | Yes | Yes | Global | No |
| basedir | Yes | Yes | Yes | Global | No |
| bdb_cache_size | Yes | Yes | Yes | Global | No |
| bdb-home | Yes | Yes | No | ||
| - Variable: bdb_home | Yes | Global | No | ||
| bdb-lock-detect | Yes | Yes | No | ||
| - Variable: bdb_lock_detect | Yes | Global | No | ||
| bdb_log_buffer_size | Yes | Yes | Yes | Global | No |
| bdb-logdir | Yes | Yes | No | ||
| - Variable: bdb_logdir | Yes | Global | No | ||
| bdb_max_lock | Yes | Yes | Yes | Global | No |
| bdb-shared-data | Yes | Yes | No | ||
| - Variable: bdb_shared_data | Yes | Global | No | ||
| bdb-tmpdir | Yes | Yes | No | ||
| - Variable: bdb_tmpdir | Yes | Global | No | ||
| big-tables | Yes | Yes | Yes | ||
| - Variable: big_tables | Yes | Session | Yes | ||
| binlog_cache_size | Yes | Yes | Yes | Global | Yes |
| bulk_insert_buffer_size | Yes | Yes | Yes | Both | Yes |
| character_set_client | Yes | Both | Yes | ||
| character_set_connection | Yes | Both | Yes | ||
| character_set_database[a] | Yes | Both | Yes | ||
| character-set-filesystem | Yes | Yes | Yes | ||
| - Variable: character_set_filesystem | Yes | Both | Yes | ||
| character_set_results | Yes | Both | Yes | ||
| character-set-server | Yes | Yes | Yes | ||
| - Variable: character_set_server | Yes | Both | Yes | ||
| character_set_system | Yes | Global | No | ||
| character-sets-dir | Yes | Yes | No | ||
| - Variable: character_sets_dir | Yes | Global | No | ||
| collation_connection | Yes | Both | Yes | ||
| collation_database[b] | Yes | Both | Yes | ||
| collation-server | Yes | Yes | Yes | ||
| - Variable: collation_server | Yes | Both | Yes | ||
| completion_type | Yes | Yes | Yes | Both | Yes |
| concurrent_insert | Yes | Yes | Yes | Global | Yes |
| connect_timeout | Yes | Yes | Yes | Global | Yes |
| datadir | Yes | Yes | Yes | Global | No |
| date_format | Yes | Both | Yes | ||
| datetime_format | Yes | Yes | Yes | Both | Yes |
| debug | Yes | Yes | Yes | Both | Yes |
| default_week_format | Yes | Yes | Yes | Both | Yes |
| delay-key-write | Yes | Yes | Yes | ||
| - Variable: delay_key_write | Yes | Global | Yes | ||
| delayed_insert_limit | Yes | Yes | Yes | Global | Yes |
| delayed_insert_timeout | Yes | Yes | Yes | Global | Yes |
| delayed_queue_size | Yes | Yes | Yes | Global | Yes |
| div_precision_increment | Yes | Yes | Yes | Both | Yes |
| engine-condition-pushdown | Yes | Yes | Yes | ||
| - Variable: engine_condition_pushdown | Yes | Both | Yes | ||
| error_count | Yes | Session | No | ||
| expire_logs_days | Yes | Yes | Yes | Global | Yes |
| flush | Yes | Yes | Yes | Global | Yes |
| flush_time | Yes | Yes | Yes | Global | Yes |
| foreign_key_checks | Yes | Session | Yes | ||
| ft_boolean_syntax | Yes | Yes | Yes | Global | Yes |
| ft_max_word_len | Yes | Yes | Yes | Global | No |
| ft_min_word_len | Yes | Yes | Yes | Global | No |
| ft_query_expansion_limit | Yes | Yes | Yes | Global | No |
| ft_stopword_file | Yes | Yes | Yes | Global | No |
| group_concat_max_len | Yes | Yes | Yes | Both | Yes |
| have_archive | Yes | Global | No | ||
| have_bdb | Yes | Global | No | ||
| have_blackhole_engine | Yes | Global | No | ||
| have_compress | Yes | Global | No | ||
| have_crypt | Yes | Global | No | ||
| have_csv | Yes | Global | No | ||
| have_example_engine | Yes | Global | No | ||
| have_federated_engine | Yes | Global | No | ||
| have_geometry | Yes | Global | No | ||
| have_innodb | Yes | Global | No | ||
| have_isam | Yes | Global | No | ||
| have_merge_engine | Yes | Global | No | ||
| have_ndbcluster | Yes | Global | No | ||
| have_openssl | Yes | Global | No | ||
| have_query_cache | Yes | Global | No | ||
| have_raid | Yes | Global | No | ||
| have_rtree_keys | Yes | Global | No | ||
| have_ssl | Yes | Global | No | ||
| have_symlink | Yes | Global | No | ||
| hostname | Yes | Global | No | ||
| identity | Yes | Session | Yes | ||
| init_connect | Yes | Yes | Yes | Global | Yes |
| init-file | Yes | Yes | No | ||
| - Variable: init_file | Yes | Global | No | ||
| init_slave | Yes | Yes | Yes | Global | Yes |
| innodb_adaptive_hash_index | Yes | Yes | Yes | Global | No |
| innodb_additional_mem_pool_size | Yes | Yes | Yes | Global | No |
| innodb_autoextend_increment | Yes | Yes | Yes | Global | Yes |
| innodb_buffer_pool_awe_mem_mb | Yes | Yes | Yes | Global | No |
| innodb_buffer_pool_size | Yes | Yes | Yes | Global | No |
| innodb_checksums | Yes | Yes | Yes | Global | No |
| innodb_commit_concurrency | Yes | Yes | Yes | Global | Yes |
| innodb_concurrency_tickets | Yes | Yes | Yes | Global | Yes |
| innodb_data_file_path | Yes | Yes | Yes | Global | No |
| innodb_data_home_dir | Yes | Yes | Yes | Global | No |
| innodb_doublewrite | Yes | Yes | Yes | Global | No |
| innodb_fast_shutdown | Yes | Yes | Yes | Global | Yes |
| innodb_file_io_threads | Yes | Yes | Yes | Global | No |
| innodb_file_per_table | Yes | Yes | Yes | Global | No |
| innodb_flush_log_at_trx_commit | Yes | Yes | Yes | Global | Yes |
| innodb_flush_method | Yes | Yes | Yes | Global | No |
| innodb_force_recovery | Yes | Yes | Yes | Global | No |
| innodb_lock_wait_timeout | Yes | Yes | Yes | Global | No |
| innodb_locks_unsafe_for_binlog | Yes | Yes | Yes | Global | No |
| innodb_log_arch_dir | Yes | Yes | Yes | Global | No |
| innodb_log_archive | Yes | Yes | Yes | Global | No |
| innodb_log_buffer_size | Yes | Yes | Yes | Global | No |
| innodb_log_file_size | Yes | Yes | Yes | Global | No |
| innodb_log_files_in_group | Yes | Yes | Yes | Global | No |
| innodb_log_group_home_dir | Yes | Yes | Yes | Global | No |
| innodb_max_dirty_pages_pct | Yes | Yes | Yes | Global | Yes |
| innodb_max_purge_lag | Yes | Yes | Yes | Global | Yes |
| innodb_mirrored_log_groups | Yes | Yes | Yes | Global | No |
| innodb_open_files | Yes | Yes | Yes | Global | No |
| innodb_rollback_on_timeout | Yes | Yes | Yes | Global | No |
| innodb_support_xa | Yes | Yes | Yes | Both | Yes |
| innodb_sync_spin_loops | Yes | Yes | Yes | Global | Yes |
| innodb_table_locks | Yes | Yes | Yes | Both | Yes |
| innodb_thread_concurrency | Yes | Yes | Yes | Global | Yes |
| innodb_thread_sleep_delay | Yes | Yes | Yes | Global | Yes |
| innodb_use_legacy_cardinality_algorithm | Yes | Yes | Yes | Global | Yes |
| insert_id | Yes | Session | Yes | ||
| interactive_timeout | Yes | Yes | Yes | Both | Yes |
| join_buffer_size | Yes | Yes | Yes | Both | Yes |
| keep_files_on_create | Yes | Yes | Yes | Both | Yes |
| key_buffer_size | Yes | Yes | Yes | Global | Yes |
| key_cache_age_threshold | Yes | Yes | Yes | Global | Yes |
| key_cache_block_size | Yes | Yes | Yes | Global | Yes |
| key_cache_division_limit | Yes | Yes | Yes | Global | Yes |
| language | Yes | Yes | Yes | Global | No |
| large_files_support | Yes | Global | No | ||
| large_page_size | Yes | Global | No | ||
| large-pages | Yes | Yes | No | ||
| - Variable: large_pages | Yes | Global | No | ||
| last_insert_id | Yes | Session | Yes | ||
| lc_time_names | Yes | Both | Yes | ||
| license | Yes | Global | No | ||
| local_infile | Yes | Global | Yes | ||
| locked_in_memory | Yes | Global | No | ||
| log | Yes | Yes | Yes | Global | No |
| log_bin | Yes | Global | No | ||
| log-bin | Yes | Yes | Yes | Global | No |
| log-bin-trust-function-creators | Yes | Yes | Yes | ||
| - Variable: log_bin_trust_function_creators | Yes | Global | Yes | ||
| log-bin-trust-routine-creators | Yes | Yes | Yes | ||
| - Variable: log_bin_trust_routine_creators | Yes | Global | Yes | ||
| log-error | Yes | Yes | No | ||
| - Variable: log_error | Yes | Global | No | ||
| log-queries-not-using-indexes | Yes | Yes | Yes | ||
| - Variable: log_queries_not_using_indexes | Yes | Global | Yes | ||
| log-slave-updates | Yes | Yes | No | ||
| - Variable: log_slave_updates | Yes | Global | No | ||
| log-slow-queries | Yes | Yes | No | ||
| - Variable: log_slow_queries | Yes | Global | No | ||
| log-warnings | Yes | Yes | Yes | ||
| - Variable: log_warnings | Yes | Both | Yes | ||
| long_query_time | Yes | Yes | Yes | Both | Yes |
| low-priority-updates | Yes | Yes | Yes | ||
| - Variable: low_priority_updates | Yes | Both | Yes | ||
| lower_case_file_system | Yes | Yes | Yes | Global | No |
| lower_case_table_names | Yes | Yes | Yes | Global | No |
| max_allowed_packet | Yes | Yes | Yes | Both | Yes |
| max_binlog_cache_size | Yes | Yes | Yes | Global | Yes |
| max_binlog_size | Yes | Yes | Yes | Global | Yes |
| max_connect_errors | Yes | Yes | Yes | Global | Yes |
| max_connections | Yes | Yes | Yes | Global | Yes |
| max_delayed_threads | Yes | Yes | Yes | Both | Yes |
| max_error_count | Yes | Yes | Yes | Both | Yes |
| max_heap_table_size | Yes | Yes | Yes | Both | Yes |
| max_insert_delayed_threads | Yes | Both | Yes | ||
| max_join_size | Yes | Yes | Yes | Both | Yes |
| max_length_for_sort_data | Yes | Yes | Yes | Both | Yes |
| max_prepared_stmt_count | Yes | Yes | Yes | Global | Yes |
| max_relay_log_size | Yes | Yes | Yes | Global | Yes |
| max_seeks_for_key | Yes | Yes | Yes | Both | Yes |
| max_sort_length | Yes | Yes | Yes | Both | Yes |
| max_sp_recursion_depth | Yes | Yes | Yes | Both | Yes |
| max_tmp_tables | Yes | Yes | Yes | Both | Yes |
| max_user_connections | Yes | Yes | Yes | Both | Yes |
| max_write_lock_count | Yes | Yes | Yes | Global | Yes |
| memlock | Yes | Yes | Yes | Global | No |
| multi_range_count | Yes | Yes | Yes | Both | Yes |
| myisam_data_pointer_size | Yes | Yes | Yes | Global | Yes |
| myisam_max_extra_sort_file_size | Yes | Yes | Yes | Global | No |
| myisam_max_sort_file_size | Yes | Yes | Yes | Global | Yes |
| myisam_recover_options | Yes | Global | No | ||
| myisam_repair_threads | Yes | Yes | Yes | Both | Yes |
| myisam_sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| myisam_stats_method | Yes | Yes | Yes | Both | Yes |
| named_pipe | Yes | Global | No | ||
| ndb_autoincrement_prefetch_sz | Yes | Yes | Yes | Both | Yes |
| ndb_cache_check_time | Yes | Yes | Yes | Global | Yes |
| ndb_force_send | Yes | Yes | Yes | Both | Yes |
| ndb_use_exact_count | Yes | Both | Yes | ||
| net_buffer_length | Yes | Yes | Yes | Both | Yes |
| net_read_timeout | Yes | Yes | Yes | Both | Yes |
| net_retry_count | Yes | Yes | Yes | Both | Yes |
| net_write_timeout | Yes | Yes | Yes | Both | Yes |
| new | Yes | Yes | Yes | Both | Yes |
| old-passwords | Yes | Yes | Yes | ||
| - Variable: old_passwords | Yes | Both | Yes | ||
| open-files-limit | Yes | Yes | No | ||
| - Variable: open_files_limit | Yes | Global | No | ||
| optimizer_prune_level | Yes | Yes | Yes | Both | Yes |
| optimizer_search_depth | Yes | Yes | Yes | Both | Yes |
| pid-file | Yes | Yes | No | ||
| - Variable: pid_file | Yes | Global | No | ||
| plugin_dir | Yes | Yes | Yes | Global | No |
| port | Yes | Yes | Yes | Global | No |
| preload_buffer_size | Yes | Yes | Yes | Both | Yes |
| prepared_stmt_count | Yes | Global | No | ||
| profiling | Yes | Session | Yes | ||
| profiling_history_size | Yes | Both | Yes | ||
| protocol_version | Yes | Global | No | ||
| pseudo_thread_id | Yes | Both | Yes | ||
| query_alloc_block_size | Yes | Yes | Yes | Both | Yes |
| query_cache_limit | Yes | Yes | Yes | Global | Yes |
| query_cache_min_res_unit | Yes | Yes | Yes | Global | Yes |
| query_cache_size | Yes | Yes | Yes | Global | Yes |
| query_cache_type | Yes | Yes | Yes | Both | Yes |
| query_cache_wlock_invalidate | Yes | Yes | Yes | Both | Yes |
| query_prealloc_size | Yes | Yes | Yes | Both | Yes |
| rand_seed1 | Yes | Session | Yes | ||
| rand_seed2 | Yes | Session | Yes | ||
| range_alloc_block_size | Yes | Yes | Yes | Both | Yes |
| read_buffer_size | Yes | Yes | Yes | Both | Yes |
| read_only | Yes | Yes | Yes | Global | Yes |
| read_rnd_buffer_size | Yes | Yes | Yes | Both | Yes |
| relay_log_purge | Yes | Yes | Yes | Global | Yes |
| relay_log_space_limit | Yes | Yes | Yes | Global | No |
| report-host | Yes | Yes | No | ||
| - Variable: report_host | Yes | Global | No | ||
| report-password | Yes | Yes | No | ||
| - Variable: report_password | Yes | Global | No | ||
| report-port | Yes | Yes | No | ||
| - Variable: report_port | Yes | Global | No | ||
| report-user | Yes | Yes | No | ||
| - Variable: report_user | Yes | Global | No | ||
| rpl_recovery_rank | Yes | Global | Yes | ||
| safe-show-database | Yes | Yes | Yes | Global | Yes |
| secure-auth | Yes | Yes | Yes | ||
| - Variable: secure_auth | Yes | Global | Yes | ||
| secure-file-priv | Yes | Yes | No | ||
| - Variable: secure_file_priv | Yes | Global | No | ||
| server-id | Yes | Yes | Yes | ||
| - Variable: server_id | Yes | Global | Yes | ||
| shared_memory | Yes | Global | No | ||
| shared_memory_base_name | Yes | Global | No | ||
| skip-external-locking | Yes | Yes | No | ||
| - Variable: skip_external_locking | Yes | Global | No | ||
| skip-networking | Yes | Yes | No | ||
| - Variable: skip_networking | Yes | Global | No | ||
| skip-show-database | Yes | Yes | No | ||
| - Variable: skip_show_database | Yes | Global | No | ||
| skip-sync-bdb-logs | Yes | Yes | Yes | Global | No |
| slave_compressed_protocol | Yes | Yes | Yes | Global | Yes |
| slave-load-tmpdir | Yes | Yes | No | ||
| - Variable: slave_load_tmpdir | Yes | Global | No | ||
| slave-net-timeout | Yes | Yes | Yes | ||
| - Variable: slave_net_timeout | Yes | Global | Yes | ||
| slave-skip-errors | Yes | Yes | No | ||
| - Variable: slave_skip_errors | Yes | Global | No | ||
| slave_transaction_retries | Yes | Yes | Yes | Global | Yes |
| slow_launch_time | Yes | Yes | Yes | Global | Yes |
| socket | Yes | Yes | Yes | Global | No |
| sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| sql_auto_is_null | Yes | Session | Yes | ||
| sql_big_selects | Yes | Both | Yes | ||
| sql_big_tables | Yes | Session | Yes | ||
| sql_buffer_result | Yes | Session | Yes | ||
| sql_log_bin | Yes | Session | Yes | ||
| sql_log_off | Yes | Session | Yes | ||
| sql_log_update | Yes | Session | Yes | ||
| sql_low_priority_updates | Yes | Both | Yes | ||
| sql_max_join_size | Yes | Both | Yes | ||
| sql-mode | Yes | Yes | Yes | ||
| - Variable: sql_mode | Yes | Both | Yes | ||
| sql_notes | Yes | Session | Yes | ||
| sql_quote_show_create | Yes | Session | Yes | ||
| sql_safe_updates | Yes | Session | Yes | ||
| sql_select_limit | Yes | Both | Yes | ||
| sql_slave_skip_counter | Yes | Global | Yes | ||
| sql_warnings | Yes | Session | Yes | ||
| ssl-ca | Yes | Yes | No | ||
| - Variable: ssl_ca | Yes | Global | No | ||
| ssl-capath | Yes | Yes | No | ||
| - Variable: ssl_capath | Yes | Global | No | ||
| ssl-cert | Yes | Yes | No | ||
| - Variable: ssl_cert | Yes | Global | No | ||
| ssl-cipher | Yes | Yes | No | ||
| - Variable: ssl_cipher | Yes | Global | No | ||
| ssl-key | Yes | Yes | No | ||
| - Variable: ssl_key | Yes | Global | No | ||
| storage_engine | Yes | Both | Yes | ||
| sync-bdb-logs | Yes | Yes | No | ||
| - Variable: sync_bdb_logs | Yes | Global | No | ||
| sync-binlog | Yes | Yes | Yes | ||
| - Variable: sync_binlog | Yes | Global | Yes | ||
| sync-frm | Yes | Yes | Yes | ||
| - Variable: sync_frm | Yes | Global | Yes | ||
| system_time_zone | Yes | Global | No | ||
| table_lock_wait_timeout | Yes | Yes | Yes | Global | Yes |
| table_open_cache | Yes | Yes | Yes | Global | Yes |
| table_type | Yes | Both | Yes | ||
| thread_cache_size | Yes | Yes | Yes | Global | Yes |
| thread_concurrency | Yes | Yes | Yes | Global | No |
| thread_stack | Yes | Yes | Yes | Global | No |
| time_format | Yes | Yes | Yes | Both | Yes |
| time_zone | Yes | Yes | Yes | Both | Yes |
| timed_mutexes | Yes | Yes | Yes | Global | Yes |
| timestamp | Yes | Session | Yes | ||
| tmp_table_size | Yes | Yes | Yes | Both | Yes |
| tmpdir | Yes | Yes | Yes | Global | No |
| transaction_alloc_block_size | Yes | Yes | Yes | Both | Yes |
| transaction_prealloc_size | Yes | Yes | Yes | Both | Yes |
| tx_isolation | Yes | Both | Yes | ||
| unique_checks | Yes | Session | Yes | ||
| updatable_views_with_limit | Yes | Yes | Yes | Both | Yes |
| version | Yes | Yes | Yes | Global | No |
| version_comment | Yes | Global | No | ||
| version_compile_machine | Yes | Global | No | ||
| version_compile_os | Yes | Global | No | ||
| wait_timeout | Yes | Yes | Yes | Both | Yes |
| warning_count | Yes | Session | No | ||
[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. [b] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | |||||
For additional system variable information, see these sections:
Section 5.1.4, “Session System Variables”, describes system variables that exist only as session variables (that is, they do not have any global counterpart).
Section 5.1.5, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.5.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.2.3, “
InnoDBStartup Options and System Variables”, listsInnoDBsystem variables.Section 17.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.2, “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.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
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.
Version Introduced 5.0.3 Variable Name automatic_sp_privilegesVariable Scope Global Dynamic Variable Yes Value Set Type booleanDefault TRUEWhen this variable has a value of 1 (the default), the server automatically grants the
EXECUTEandALTER ROUTINEprivileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (TheALTER ROUTINEprivilege is required to drop the routine.) The server also automatically drops those privileges when the creator drops the routine. Ifautomatic_sp_privilegesis 0, the server does not automatically add or drop these privileges. This variable was added in MySQL 5.0.3.Command Line Format --back_log=#Config File Format back_logOption Sets Variable Yes, back_logVariable Name back_logVariable Scope Global Dynamic Variable No Value Set Type numericDefault 50Range 1-65535The 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_logvalue 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_logcannot be set higher than your operating system limit.Command Line Format --basedir=nameConfig File Format basedirOption Sets Variable Yes, basedirVariable Name basedirVariable Scope Global Dynamic Variable No Value Set Type filenameThe MySQL installation base directory. This variable can be set with the
--basediroption. Relative path names for other variables usually are resolved relative to the base directory.Command Line Format --bdb_cache_size=#Config File Format bdb_cache_sizeOption Sets Variable Yes, bdb_cache_sizeVariable Name bdb_cache_sizeVariable Scope Global Dynamic Variable No Value Set Type numericMin Value 20480The size of the buffer that is allocated for caching indexes and rows for
BDBtables. If you don't useBDBtables, you should start mysqld with--skip-bdbto not allocate memory for this cache.Command Line Format --bdb-home=nameConfig File Format bdb-home=nameOption Sets Variable Yes, bdb_homeVariable Name bdb_homeVariable Scope Global Dynamic Variable No Value Set Type filenameThe base directory for
BDBtables. This should be assigned the same value as thedatadirvariable.Command Line Format --bdb_log_buffer_size=#Config File Format bdb_log_buffer_sizeOption Sets Variable Yes, bdb_log_buffer_sizeVariable Name bdb_log_buffer_sizeVariable Scope Global Dynamic Variable No Value Set Type numericRange 262144-4294967295The size of the buffer that is allocated for caching indexes and rows for
BDBtables. If you don't useBDBtables, you should set this to 0 or start mysqld with--skip-bdbto not allocate memory for this cache.Command Line Format --bdb-logdir=file_nameConfig File Format bdb-logdir=file_nameOption Sets Variable Yes, bdb_logdirVariable Name bdb_logdirVariable Scope Global Dynamic Variable No Value Set Type filenameThe directory where the
BDBstorage engine writes its log files. This variable can be set with the--bdb-logdiroption.Command Line Format --bdb_max_lock=#Config File Format bdb_max_lockOption Sets Variable Yes, bdb_max_lockVariable Name bdb_max_lockVariable Scope Global Dynamic Variable No Value Set Type numericDefault 10000The maximum number of locks that can be active for a
BDBtable (10,000 by default). You should increase this value if errors such as the following occur when you perform long transactions or when mysqld has to examine many rows to calculate a query:bdb: Lock table is out of available locks Got error 12 from ...
Command Line Format --bdb-shared-dataConfig File Format bdb-shared-dataOption Sets Variable Yes, bdb_shared_dataVariable Name bdb-shared-dataVariable Scope Global Dynamic Variable No This is
ONif you are using--bdb-shared-datato start Berkeley DB in multi-process mode. (Do not useDB_PRIVATEwhen initializing Berkeley DB.)Command Line Format --bdb-tmpdir=nameConfig File Format bdb-tmpdir=nameOption Sets Variable Yes, bdb_tmpdirVariable Name bdb-tmpdirVariable Scope Global Dynamic Variable No Value Set Type filenameThe
BDBtemporary file directory.Command Line Format --binlog_cache_size=#Config File Format binlog_cache_sizeOption Sets Variable Yes, binlog_cache_sizeVariable Name binlog_cache_sizeVariable Scope Global Dynamic Variable Yes Value Set Platform Bit Size 32Type numericDefault 32768Range 4096-4294967295Value Set Platform Bit Size 64Type numericDefault 32768Range 4096-18446744073709547520The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (
--log-binoption). If you often use large, multiple-statement transactions, you can increase this cache size to get more performance. TheBinlog_cache_useandBinlog_cache_disk_usestatus variables can be useful for tuning the size of this variable. See Section 5.2.3, “The Binary Log”.MySQL Enterprise For recommendations on the optimum setting for
binlog_cache_sizesubscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.Command Line Format --bulk_insert_buffer_size=#Config File Format bulk_insert_buffer_sizeOption Sets Variable Yes, bulk_insert_buffer_sizeVariable Name bulk_insert_buffer_sizeVariable Scope Both Dynamic Variable Yes Value Set Platform Bit Size 32Type numericDefault 8388608Range 0-4294967295Value Set Platform Bit Size 64Type numericDefault 8388608Range 0-18446744073709547520MyISAMuses a special tree-like cache to make bulk inserts faster forINSERT ... SELECT,INSERT ... VALUES (...), (...), ..., andLOAD DATA INFILEwhen 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.Variable Name character_set_clientVariable Scope Both Dynamic Variable Yes Value Set Type stringThe 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-setoption 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
sjiswhen connecting to a server not configured withsjissupport.mysqld was started with the
--skip-character-set-client-handshakeoption, 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.
Variable Name character_set_connectionVariable Scope Both Dynamic Variable Yes Value Set Type stringThe character set used for literals that do not have a character set introducer and for number-to-string conversion.
Variable Name character_set_databaseVariable Scope Both Dynamic Variable Yes Footnote This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. Value Set Type stringThe 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.Version Introduced 5.0.19 Command Line Format --character-set-filesystem=nameConfig File Format character-set-filesystemOption Sets Variable Yes, character_set_filesystemVariable Name character_set_filesystemVariable Scope Both Dynamic Variable Yes Value Set Type stringThe file system character set. This variable is used to interpret string literals that refer to file names, such as in the
LOAD DATA INFILEandSELECT ... INTO OUTFILEstatements and theLOAD_FILE()function. Such file names are converted fromcharacter_set_clienttocharacter_set_filesystembefore the file opening attempt occurs. The default value isbinary, 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, setcharacter_set_filesystemto'utf8'. This variable was added in MySQL 5.0.19.Variable Name character_set_resultsVariable Scope Both Dynamic Variable Yes Value Set Type stringThe character set used for returning query results to the client.
Command Line Format --character-set-serverConfig File Format character-set-serverOption Sets Variable Yes, character_set_serverVariable Name character_set_serverVariable Scope Both Dynamic Variable Yes Value Set Type stringThe server's default character set.
Variable Name character_set_systemVariable Scope Global Dynamic Variable No Value Set Type stringThe character set used by the server for storing identifiers. The value is always
utf8.Command Line Format --character-sets-dir=nameConfig File Format character-sets-dirOption Sets Variable Yes, character_sets_dirVariable Name character-sets-dirVariable Scope Global Dynamic Variable No Value Set Type filenameThe directory where character sets are installed.
Variable Name collation_connectionVariable Scope Both Dynamic Variable Yes Value Set Type stringThe collation of the connection character set.
Variable Name collation_databaseVariable Scope Both Dynamic Variable Yes Footnote This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. Value Set Type stringThe 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.Command Line Format --collation-serverConfig File Format collation-serverOption Sets Variable Yes, collation_serverVariable Name collation_serverVariable Scope Both Dynamic Variable Yes Value Set Type stringThe server's default collation.
Version Introduced 5.0.3 Command Line Format --completion_type=#Config File Format completion_typeOption Sets Variable Yes, completion_typeVariable Name competion_typeVariable Scope Both Dynamic Variable Yes Value Set Type numericDefault 0Valid Values 0,1,2The transaction completion type:
If the value is 0 (the default),
COMMITandROLLBACKare unaffected.If the value is 1,
COMMITandROLLBACKare equivalent toCOMMIT AND CHAINandROLLBACK AND CHAIN, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)If the value is 2,
COMMITandROLLBACKare equivalent toCOMMIT RELEASEandROLLBACK RELEASE, respectively. (The server disconnects after terminating the transaction.)
This variable was added in MySQL 5.0.3
Command Line Format --concurrent_insert[=#]Config File Format concurrent_insertOption Sets Variable Yes, concurrent_insertVariable Name concurrent_insertVariable Scope Global Dynamic Variable Yes Value Set (<= 5.0.5) Type booleanDefault TRUEValue Set (>= 5.0.6) Type numericDefault 1Valid Values 0,1,2If 1 (the default), MySQL allows
INSERTandSELECTstatements to run concurrently forMyISAMtables that have no free blocks in the middle of the data file. You can turn this option off by starting mysqld with--safe-modeor--skip-new.In MySQL 5.0.6, t