( Server options

Info Catalog ( Configuring MySQL ( Configuring MySQL ( Server SQL mode
 5.2.1 `mysqld' Command-Line Options
 When you start the `mysqld' server, you can specify program options
 using any of the methods described in  Program Options. The most
 common methods are to provide options in an option file or on the
 command line. However, in most cases it is desirable to make sure that
 the server uses the same options each time it runs. The best way to
 ensure this is to list them in an option file.   Option files.
 `mysqld' reads options from the `[mysqld]' and `[server]' groups.
 `mysqld_safe' reads options from the `[mysqld]', `[server]',
 `[mysqld_safe]', and `[safe_mysqld]' groups.  `mysql.server' reads
 options from the `[mysqld]' and `[mysql.server]' groups.  An embedded
 MySQL server usually reads options from the `[server]', `[embedded]',
 and `[XXXXX_SERVER]' groups, where XXXXX is the name of the application
 into which the server is embedded.
 `mysqld' accepts many command-line options.  For a list, execute
 `mysqld --help'. Before MySQL 4.1.1, `--help' prints the full help
 message. As of 4.1.1, it prints a brief message; to see the full list,
 use `mysqld --verbose --help'.
 The following list shows some of the most common server options.
 Additional options are described elsewhere:
    * Options that affect security: See  Privileges options.
    * SSL-related options: See  SSL options.
    * Binary log control options: See  Binary log.
    * Replication-related options: See  Replication Options.
DONTPRINTYET     * Options specific to particular storage engines: See  `MyISAM'
      start MyISAM start,  `BDB' start BDB start, *Note `InnoDB'
DONTPRINTYET     * Options specific to particular storage engines: See  `MyISAM'
      start MyISAM start,  `BDB' start BDB start,  `InnoDB'

      start InnoDB start.
 You can also set the value of a server system variable by using the
 variable name as an option, as described later in this section.
 `--help, -?'
      Display a short help message and exit.  Before MySQL 4.1.1,
      `--help' displays the full help message.  As of 4.1.1, it displays
      an abbreviated message only.  Use both the `--verbose' and
      `--help' options to see the full message.
      Use standard SQL syntax instead of MySQL syntax.   ANSI
      mode.  For more precise control over the server SQL mode, use
      the `--sql-mode' option instead.
 `--basedir=PATH, -b PATH'
      The path to the MySQL installation directory. All paths are
      usually resolved relative to this.
      Allow large result sets by saving all temporary sets in files.
      This option prevents most "table full" errors, but also slows down
      queries for which in-memory tables would suffice.  Since MySQL
      3.23.2, the server is able to handle large result sets
      automatically by using memory for small temporary tables and
      switching to disk tables where necessary.
      The IP address to bind to.
      Write the error log messages to stderr/stdout even if `--log-error'
      is specified.  On Windows, `mysqld' will not close the console
      screen if this option is used.
      The directory where character sets are installed.   Character
      Put the `mysqld' server in a closed environment during startup by
      using the `chroot()' system call. This is a recommended security
      measure as of MySQL 4.0. (MySQL 3.23 is not able to provide a
      `chroot()' jail that is 100% closed.)  Note that use of this
      option somewhat limits `LOAD DATA INFILE' and `SELECT ... INTO
      Use CHARSET as the default server character set.  This option is
      available as of MySQL 4.1.3.   Character sets.
      Write a core file if `mysqld' dies.  For some systems, you must
      also specify the `--core-file-size' option to `mysqld_safe'.
       `mysqld_safe' mysqld_safe.  Note that on some systems, such
      as Solaris, you will not get a core file if you are also using the
      `--user' option.
      Use COLLATION as the default server collation.  This option is
      available as of MySQL 4.1.3.   Character sets.
 `--datadir=PATH, -h PATH'
      The path to the data directory.
      If MySQL is configured with `--with-debug', you can use this
      option to get a trace file of what `mysqld' is doing.  The
      DEBUG_OPTIONS string often is `'d:t:o,FILE_NAME''.   Making
      trace files.
      Use CHARSET as the default character set.  This option is
      deprecated in favor of `--character-set-server' as of MySQL 4.1.3.
       Character sets.
      Use COLLATION as the default collation.  This option is deprecated
      in favor of `--collation-server' as of MySQL 4.1.3.  
      Character sets.
      This option is a synonym for `--default-table-type'.  It is
      available as of MySQL 4.1.2.
      Set the default table type for tables.   Storage engines.
      Set the default server time zone.  This option sets the global
      `time_zone' system variable.  If this option is not given, the
      default time zone will be the same as the system time zone (given
      by the value of the `system_time_zone' system variable.  This
      option is available as of MySQL 4.1.3.
 `--delay-key-write[= OFF | ON | ALL]'
      How the `DELAYED KEYS' option should be used.  Delayed key writing
      causes key buffers not to be flushed between writes for `MyISAM'
      tables.  `OFF' disables delayed key writes.  `ON' enables delayed
      key writes for those tables that were created with the `DELAYED
      KEYS' option.  `ALL' delays key writes for all `MyISAM' tables.
      Available as of MySQL 4.0.3.   Server parameters. 
      MyISAM start.
      * If you set this variable to `ALL', you should not use
      `MyISAM' tables from within another program (such as from another
      MySQL server or with `myisamchk') when the table is in use. Doing
      so will lead to index corruption.
      Old form of `--delay-key-write=ALL' for use prior to MySQL 4.0.3.
      As of 4.0.3, use `--delay-key-write' instead.
      Read the default keys used by `DES_ENCRYPT()' and `DES_DECRYPT()'
      from this file.
      Enable support for named pipes.  This option applies only on
      Windows NT, 2000, XP, and 2003 systems, and can be used only with
      the `mysqld-nt' and `mysqld-max-nt' servers that support named
      pipe connections.
 `--exit-info[=FLAGS], -T [FLAGS]'
      This is a bit mask of different flags you can use for debugging the
      `mysqld' server. Do not use this option unless you know exactly
      what it does!
      Enable system locking.  Note that if you use this option on a
      system on which `lockd' does not fully work (as on Linux), you
      will easily get `mysqld' to deadlock.  This option previously was
      named `--enable-locking'.
      * If you use this option to enable updates to `MyISAM'
      tables from many MySQL processes, you have to ensure that these
      conditions are satisfied:
         * You should not use the query cache for queries that use
           tables that are updated by another process.
         * You should not use `--delay-key-write=ALL' or
           `DELAY_KEY_WRITE=1' on any shared tables.
      The easiest way to ensure this is to always use
      `--external-locking' together with `--delay-key-write=OFF
      (This is not done by default because in many setups it's useful to
      have a mixture of the above options.)
      Flush all changes to disk after each SQL statement.  Normally MySQL
      does a write of all changes to disk only after each SQL statement
      and lets the operating system handle the synching to disk.  
      Read SQL statements from this file at startup.  Each statement
      must be on a single line and should not include comments.
      Adds consistency guarantees between the content of `InnoDB' tables
      and the binary log.   Binary log.
 `--language=LANG_NAME, -L LANG_NAME'
      Client error messages in given language.  LANG_NAME can be given
      as the language name or as the full pathname to the directory
      where the language files are installed.   Languages.
      Some hardware/operating system architectures support memory pages
      greater than the default (usually 4 KB). The actual implementation
      of this support depends on the underlying hardware and OS.
      Applications that perform a lot of memory access may obtain
      performance improvements by using large pages due to reduced
      Translation Lookaside Buffer (TLB) misses.
      Currently, MySQL supports only the Linux implementation of large
      pages support (which is called HugeTLB in Linux). We have plans to
      extend this support to FreeBSD, Solaris and possibly other
      Before large pages can be used on Linux, it is necessary to
      configure the HugeTLB memory pool. For reference, consult the
      `hugetlbpage.txt' file in the Linux kernel source.
      This option is disabled by default.  It was added in MySQL 5.0.3.
 `--log[=FILE], -l [FILE]'
      Log connections and queries to this file.  Query log. If
      you don't specify a filename, MySQL will use `HOST_NAME.log' as
      the filename.
      The binary log file.  Log all queries that change data to this
      file. Used for backup and replication.   Binary log.  It is
      recommended to specify a filename (see  Open bugs for the
      reason) otherwise MySQL will use `HOST_NAME-bin' as the log file
      The index file for binary log filenames.  Binary log.  If
      you don't specify a filename, and if you didn't either specify one
      in `--log-bin', MySQL will use `HOST_NAME-bin.index' as the
      Log errors and startup messages to this file.  Error log.
      If you don't specify a filename, MySQL will use `HOST_NAME.err' as
      the filename.
      Log all `ISAM'/`MyISAM' changes to this file (used only when
      debugging `ISAM'/`MyISAM').
      Log some extra information to the log files (update log, binary
      update log, and slow queries log, whatever log has been
      activated). For example, username and timestamp are logged for
      queries. Before MySQL 4.1, if you are using `--log-slow-queries'
      and `--log-long-format', queries that are not using indexes also
      are logged to the slow query log.  `--log-long-format' is
      deprecated as of MySQL version 4.1, when `--log-short-format' was
      introduced. (Long log format is the default setting since version
      4.1.)  Also note that starting with MySQL 4.1, the
      `--log-queries-not-using-indexes' option is available for the
      purpose of logging queries that do not use indexes to the slow
      query log.
      If you are using this option with `--log-slow-queries', then
      queries that are not using indexes also  are logged to the slow
      query log. This option is available as of MySQL 4.1.  Slow
      query log.
      Log less information to the log files (update log, binary update
      log, and slow queries log, whatever log has been activated). For
      example, username and timestamp are not logged for queries. This
      option was introduced in MySQL 4.1.
      Log all queries that have taken more than `long_query_time' seconds
      to execute to this file.   Slow query log.  Note that the
      default for the amount of information logged has changed in MySQL
      4.1. See the `--log-long-format' and `--log-short-format' options
      for details.
      Log updates to FILE# where # is a unique number if not given.
       Update log. The update log is deprecated and is removed in
      MySQL 5.0.0; you should use the binary log instead (`--log-bin').
       Binary log. Starting from version 5.0.0, using
      `--log-update' will just turn on the binary log instead (
 `--log-warnings, -W'
      Print out warnings such as `Aborted connection...' to the error
      log.  Enabling this option is recommended, for example, if you use
      replication (you will get more information about what is happening,
      such as messages about network failures and reconnections).  This
      option is enabled by default as of MySQL 4.0.19 and 4.1.2; to
      disable it, use `--skip-log-warnings'.  As of MySQL 4.0.21 and
      4.1.3, aborted connections are not logged to the error log unless
      the value is greater than 1.   Communication errors.
      This option was named `--warnings' before MySQL 4.0.
      Table-modifying operations (`INSERT', `REPLACE', `DELETE',
      `UPDATE') will have lower priority than selects.  This can also be
      to lower the priority of only one query, or by `SET
      LOW_PRIORITY_UPDATES=1' to change the priority in one thread.
       Table locking.
      Lock the `mysqld' process in memory.  This works on systems such as
      Solaris that support the `mlockall()' system call.  This might
      help if you have a problem where the operating system is causing
      `mysqld' to swap on disk.  Note that use of this option requires
      that you run the server as `root', which is normally not a good
      idea for security reasons.
 `--myisam-recover [=OPTION[,OPTION...]]]'
      Set the `MyISAM' storage engine recovery mode.  The option value
      is any combination of the values of `DEFAULT', `BACKUP', `FORCE',
      or `QUICK'.  If you specify multiple values, separate them by
      commas.  You can also use a value of `""' to disable this option.
      If this option is used, `mysqld' will, when it opens a `MyISAM'
      table, open check whether the table is marked as crashed or wasn't
      closed properly.  (The last option works only if you are running
      with `--skip-external-locking'.)  If this is the case, `mysqld'
      will run a check on the table. If the table was corrupted,
      `mysqld' will attempt to repair it.
      The following options affect how the repair works:
      *Option*   *Description*
      `DEFAULT'  The same as not giving any option to
      `BACKUP'   If the data file was changed during recovery,
                 save a                     backup of the
                 `TBL_NAME.MYD' file as
      `FORCE'    Run recovery even if we will lose more than
                 one row                     from the `.MYD'
      `QUICK'    Don't check the rows in the table if there
                 aren't any                     delete blocks.
      Before a table is automatically repaired, MySQL will add a note
      about this in the error log.  If you want to be able to recover
      from most problems without user intervention, you should use the
      options `BACKUP,FORCE'.  This will force a repair of a table even
      if some rows would be deleted, but it will keep the old data file
      as a backup so that you can later examine what happened.
      This option is available as of MySQL 3.23.25.
      When using the `NDB' storage engine, it is possible to point out
      the management server that distributes the cluster configuration
      by setting the connect string option. See  MySQL Cluster
      Connectstring for syntax.
      If the binary includes support for the `NDB Cluster' storage engine
      (from version 4.1.3, the MySQL-Max binaries are built with `NDB
      Cluster' enabled) the default disabling of support for the `NDB
      Cluster' storage engine can be overruled by using this option.
      Using the `NDB Cluster' storage engine is necessary for using
      MySQL Cluster.   NDBCluster.
      The `--new' option can be used to make the server behave as 4.1 in
      certain respects, easing a 4.0 to 4.1 upgrade:
         * Hexadecimal strings such as `0xFF' are treated as strings by
           default rather than as numbers.  (Works in 4.0.12 and up.)
         * `TIMESTAMP' is returned as a string with the format
           `'YYYY-MM-DD HH:MM:SS''.  (Works in 4.0.13 and up.)  
           Column types.
      This option can be used to help you see how your applications will
      behave in MySQL 4.1, without actually upgrading to 4.1.
      Force the server to generate short (pre-4.1) password hashes for
      new passwords. This is useful for compatibility when the server
      must support older client programs.   Password hashing.
 `--old-protocol, -o'
      Use the 3.20 protocol for compatibility with some very old clients.
      Only use one thread (for debugging under Linux).  This option is
      available only if the server is built with debugging enabled.
       Debugging server.
      To change the number of file descriptors available to `mysqld'.
      If this is not set or set to 0, then `mysqld' will use this value
      to reserve file descriptors to use with `setrlimit()'.  If this
      value is 0 then `mysqld' will reserve `max_connections*5' or
      `max_connections + table_cache*2' (whichever is larger) number of
      files.  You should try increasing this if `mysqld' gives you the
      error "Too many open files."
      The path to the process ID file used by `mysqld_safe'.
 `--port=PORT_NUM, -P PORT_NUM'
      The port number to use when listening for TCP/IP connections.
      Skip some optimization stages.
      With this option, the `SHOW DATABASES' statement displays only the
      names of those databases for which the user has some kind of
      privilege.  As of MySQL 4.0.2, this option is deprecated and
      doesn't do anything (it is enabled by default), because there is a
      `SHOW DATABASES' privilege that can be used to control access to
      database names on a per-account basis.   Privileges
      If this is enabled, a user can't create new users with the `GRANT'
      statement, if the user doesn't have the `INSERT' privilege for the
      `mysql.user' table or any column in the table.
      Disallow authentication for accounts that have old (pre-4.1)
      passwords.  This option is available as of MySQL 4.1.1.
      Enable shared-memory connections by local clients. This option is
      available only on Windows. It was added in MySQL 4.1.0.
      The name to use for shared-memory connections.  This option is
      available only on Windows. It was added in MySQL 4.1.0.
      Disable the `BDB' storage engine. This saves memory and might speed
      up some operations.  Do not use this option if you require `BDB'
      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.)
      Ignore the `DELAY_KEY_WRITE' option for all tables.  As of MySQL
      4.0.3, you should use `--delay-key-write=OFF' instead.  
      Server parameters.
      Don't use system locking.  To use `isamchk' or `myisamchk', you
      must shut down the server.   Stability.  In MySQL 3.23, you
      can use `CHECK TABLE' and `REPAIR TABLE' to check and repair
      `MyISAM' tables.  This option previously was named
      This option causes the server not to use the privilege system at
      all. This gives everyone _full access_ to all databases!  (You can
      tell a running server to start using the grant tables again by
      executing a `mysqladmin flush-privileges' or `mysqladmin reload'
      command, or by issuing a `FLUSH PRIVILEGES' statement.)
      Do not use the internal hostname cache for faster name-to-IP
      resolution.  Instead, query the DNS server every time a client
      connects.   DNS.
      Disable the `InnoDB' storage engine.  This saves memory and disk
      space and might speed up some operations.  Do not use this option
      if you require `InnoDB' tables.
      Disable the `ISAM' storage engine.  As of MySQL 4.1, `ISAM' is
      disabled by default, so this option applies only if the server was
      configured with support for `ISAM'.  This option was added in
      MySQL 4.1.1.
      Do not resolve hostnames when checking client connections. Use
      only IP numbers. If you use this option, all `Host' column values
      in the grant tables must be IP numbers or `localhost'.  
      Disable the `NDB Cluster' storage engine. This is the default for
      binaries that were built with `NDB Cluster' storage engine
      support, this means that the system will only allocate memory and
      other resources for this storage engine if it is explicitly
      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.
      Don't use new, possibly wrong routines.
      This is the old form of `--skip-symbolic-links', for use before
      MySQL 4.0.13.
 `--symbolic-links, --skip-symbolic-links'
      Enable or disable symbolic link support. This option has different
      effects on Windows and Unix:
         * On Windows, enabling symbolic links allows you to establish a
           symbolic link to a database directory by creating a
           `directory.sym' file that contains the path to the real
           directory.   Windows symbolic links.
         * On Unix, enabling symbolic links means that you can link a
           `MyISAM' index file or data file to another directory with
           the `INDEX DIRECTORY' or `DATA DIRECTORY' options of the
           `CREATE TABLE' statement.  If you delete or rename the table,
           the files that its symbolic links point to also are deleted or
           renamed.  `CREATE TABLE' CREATE TABLE.
      This option was added in MySQL 4.0.13.
      If MySQL is configured with `--with-debug=full', all MySQL programs
      check for memory overruns during each memory allocation and memory
      freeing operation.  This checking is very slow, so for the server
      you can avoid it when you don't need it by using the
      `--skip-safemalloc' option.
      With this option, the `SHOW DATABASES' statement is allowed only to
      users who have the `SHOW DATABASES' privilege, and the statement
      displays all database names.  Without this option, `SHOW
      DATABASES' is allowed to all users, but displays each database
      name only if the user has the `SHOW DATABASES' privilege or some
      privilege for the database.
      Don't write stack traces.  This option is useful when you are
      running `mysqld' under a debugger. On some systems, you also must
      use this option to get a core file.  Debugging server.
      Disable using thread priorities for faster response time.
      On Unix, this option specifies the Unix socket file to use for
      local connections. The default value is `/tmp/mysql.sock'.  On
      Windows, the option specifies the pipe name to use for local
      connections that use a named pipe. The default value is `MySQL'.
      Set the SQL mode for MySQL.  Server SQL mode. This option
      was added in 3.23.41.
      This option causes most temporary files created by the server to
      use a small set of names, rather than a unique name for each new
      file. This works around a problem in the Linux kernel dealing with
      creating many new files with different names. With the old
      behavior, Linux seems to "leak" memory, because it's being
      allocated to the directory entry cache rather than to the disk
      Sets the default transaction isolation level, which can be
 `--tmpdir=PATH, -t PATH'
      The path of the directory to use for creating temporary files. It
      might be useful if your default `/tmp' directory resides on a
      partition that is too small to hold temporary tables.  Starting
      from MySQL 4.1, this option accepts several paths that are used in
      round-robin fashion. Paths should be separated by colon characters
      (`:') on Unix and semicolon characters (`;') on Windows, NetWare,
      and OS/2.  If the MySQL server is acting as a replication slave,
      you should not set `--tmpdir' to point to a directory on a
      memory-based filesystem or to a directory that is cleared when the
      server host restarts.  A replication slave needs some of its
      temporary files to survive a machine restart so that it can
      replicate temporary tables or `LOAD DATA INFILE' operations. If
      files in the temporary file directory are lost when the server
      restarts, replication will fail.
 `--user={USER_NAME | USER_ID}, -u {USER_NAME | USER_ID}'
      Run the `mysqld' server as the user having the name USER_NAME or
      the numeric user ID USER_ID.  ("User" in this context refers to a
      system login account, not a MySQL user listed in the grant tables.)
      This option is _mandatory_ when starting `mysqld' as `root'.  The
      server will change its user ID during its startup sequence,
      causing it to run as that particular user rather than as `root'.
       Security guidelines.
      Starting from MySQL 3.23.56 and 4.0.12: To avoid a possible
      security hole where a user adds a `--user=root' option to some
      `my.cnf' file (thus causing the server to run as `root'), `mysqld'
      uses only the first `--user' option specified and produces a
      warning if there are multiple `--user' options. Options in
      `/etc/my.cnf' and `datadir/my.cnf' are processed before
      command-line options, so it is recommended that you put a `--user'
      option in `/etc/my.cnf' and specify a value other than `root'. The
      option in `/etc/my.cnf' will be found before any other `--user'
      options, which ensures that the server runs as a user other than
      `root', and that a warning results if any other `--user' option is
 `--version, -V'
      Display version information and exit.
 As of MySQL 4.0, you can assign a value to a server system variable by
 using an option of the form `--VAR_NAME=VALUE'. For example,
 `--key_buffer_size=32M' sets the `key_buffer_size' variable to a value
 of 32MB.
 Note that when setting a variable to a value, MySQL might automatically
 correct it to stay within a given range, or adjust the value to the
 closest allowable value if only certain values are allowed.
 It is also possible to set variables by using
 `--set-variable=VAR_NAME=VALUE' or `-O VAR_NAME=VALUE' syntax. However,
 this syntax is deprecated as of MySQL 4.0.
 You can find a full description for all variables in  Server
 system variables.  The section on tuning server parameters includes
 information on how to optimize them.   Server parameters.
 You can change the values of most system variables for a running server
 with the `SET' statement.  `SET OPTION' SET OPTION.
 If you want to restrict the maximum value that a startup option can be
 set to with `SET', you can define this by using the
 `--maximum-VAR_NAME' command-line option.
Info Catalog ( Configuring MySQL ( Configuring MySQL ( Server SQL mode
automatically generated byinfo2html