Chapter聽5.聽Database Administration - MySQL 5.0参考手册英文版

Options that affect security: See Section聽5.6.3, 鈥淪ecurity-Related mysqld Options鈥�.

SSL-related options: See Section聽5.8.7.3, 鈥淪SL Command Options鈥�.

Binary log control options: See Section聽5.11.3, 鈥淭he Binary Log鈥�.

Replication-related options: See Section聽6.8, 鈥淩eplication Startup Options鈥�.

Options specific to particular storage engines: See Section聽14.1.1, 鈥�MyISAM Startup Options鈥�, Section聽14.5.3, 鈥�BDB Startup Options鈥�, Section聽14.2.4, 鈥�InnoDB Startup Options and System Variables鈥�, and Section聽15.6.5.1, 鈥淢ySQL Cluster-Related Command Options for mysqld鈥�.

--help, -?

Display a short help message and exit. Use both the --verbose and --help options to see the full message.

--abort-slave-event-count

This option is used internally by the MySQL test suite for replication testing and debugging.

--allow-suspicious-udfs

This option controls whether user-defined functions that have only an xxx symbol for the main function can be loaded. By default, the option is off and only UDFs that have at least one auxiliary symbol can be loaded; this prevents attempts at loading functions from shared object files other than those containing legitimate UDFs. This option was added in version 5.0.3. See Section聽24.2.4.6, 鈥淯ser-Defined Function Security Precautions鈥�.

--ansi

Use standard (ANSI) SQL syntax instead of MySQL syntax. For more precise control over the server SQL mode, use the --sql-mode option instead. See Section聽1.9.3, 鈥淩unning MySQL in ANSI Mode鈥�, and Section聽5.2.6, 鈥淪QL Modes鈥�.

--basedir=path, -b path

The path to the MySQL installation directory. All paths are usually resolved relative to this directory.

big-tables

Allow large result sets by saving all temporary sets in files. This option prevents most 鈥�table full鈥� errors, but also slows down queries for which in-memory tables would suffice. Since MySQL 3.23.2, the server is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary.

--bind-address=IP

The IP address to bind to.

--bootstrap

This option is used by the mysql_install_db script to create the MySQL privilege tables without having to start a full MySQL server.

This option is unavailable if MySQL was configured with the --disable-grant-options option. See Section聽2.4.14.2, 鈥淭ypical configure Options鈥�.

--character-sets-dir=path

The directory where character sets are installed. See Section聽5.10.1, 鈥淭he Character Set Used for Data and Sorting鈥�.

--character-set-client-handshake

Don't ignore character set information sent by the client. To ignore client information and use the default server character set, use --skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.

--character-set-filesystem=charset_name

The filesystem character set. This option sets the character_set_filesystem system variable. It was added in MySQL 5.0.19.

--character-set-server=charset_name, -C charset_name

Use charset_name as the default server character set. See Section聽5.10.1, 鈥淭he Character Set Used for Data and Sorting鈥�. If you use this option to specify a non-default character set, you should also use --collation-server to specify the collation.

--chroot=path

Put the mysqld server in a closed environment during startup by using the chroot() system call. This is a recommended security measure. Note that use of this option somewhat limits LOAD DATA INFILE and SELECT ... INTO OUTFILE.

--collation-server=collation_name

Use collation_name as the default server collation. See Section聽5.10.1, 鈥淭he Character Set Used for Data and Sorting鈥�.

--console

(Windows only.) Write error log messages to stderr and stdout even if --log-error is specified. mysqld does not close the console window if this option is used.

--core-file

Write a core file if mysqld dies. For some systems, you must also specify the --core-file-size option to mysqld_safe. See Section聽5.3.1, 鈥�mysqld_safe 鈥� MySQL Server Startup Script鈥�. Note that on some systems, such as Solaris, you do not get a core file if you are also using the --user option.

--datadir=path, -h path

The path to the data directory.

--debug[=debug_options], -# [debug_options]

If MySQL is configured with --with-debug, you can use this option to get a trace file of what mysqld is doing. The debug_options string often is 'd:t:o,file_name'. The default is 'd:t:i:o,mysqld.trace'. See MySQL Internals: Porting.

As of MySQL 5.0.25, using --with-debug to configure MySQL with debugging support enables you to use the --debug="d,parser_debug" option when you start the server. This causes the Bison parser that is used to process SQL statements to dump a parser trace to the server's standard error output. Typically, this output is written to the error log.

--default-character-set=charset_name (DEPRECATED)

Use charset_name as the default character set. This option is deprecated in favor of --character-set-server. See Section聽5.10.1, 鈥淭he Character Set Used for Data and Sorting鈥�.

--default-collation=collation_name

Use collation_name as the default collation. This option is deprecated in favor of --collation-server. See Section聽5.10.1, 鈥淭he Character Set Used for Data and Sorting鈥�.

--default-storage-engine=type

Set the default storage engine (table type) for tables. See Chapter聽14, Storage Engines and Table Types.

--default-table-type=type

This option is a synonym for --default-storage-engine.

--default-time-zone=timezone

Set the default server time zone. This option sets the global time_zone system variable. If this option is not given, the default time zone is the same as the system time zone (given by the value of the system_time_zone system variable.

--delay-key-write[={OFF|ON|ALL}]

Specify how to use delayed key writes. Delayed key writing causes key buffers not to be flushed between writes for MyISAM tables. OFF disables delayed key writes. ON enables delayed key writes for those tables that were created with the DELAY_KEY_WRITE option. ALL delays key writes for all MyISAM tables. See Section聽7.5.2, 鈥淭uning Server Parameters鈥�, and Section聽14.1.1, 鈥�MyISAM Startup Options鈥�.

Note: If you set this variable to ALL, you should not use MyISAM tables from within another program (such as another MySQL server or myisamchk) when the tables are in use. Doing so leads to index corruption.

--des-key-file=file_name

Read the default DES keys from this file. These keys are used by the DES_ENCRYPT() and DES_DECRYPT() functions.

--disconnect-slave-event-count

This option is used internally by the MySQL test suite for replication testing and debugging.

--enable-named-pipe

Enable support for named pipes. This option can be used only with the mysqld-nt and mysqld-debug servers that support named-pipe connections.

--exit-info[=flags], -T [flags]

This is a bit mask of different flags that you can use for debugging the mysqld server. Do not use this option unless you know exactly what it does!

--external-locking

Enable external locking (system locking), which is disabled by default as of MySQL 4.0. Note that if you use this option on a system on which lockd does not fully work (such as Linux), it is easy for mysqld to deadlock. This option previously was named --enable-locking.

For more information about external locking, including conditions under which it can and cannot be used, see Section聽7.3.4, 鈥淓xternal Locking鈥�.

--flush

Flush (synchronize) all changes to disk after each SQL statement. Normally, MySQL does a write of all changes to disk only after each SQL statement and lets the operating system handle the synchronizing to disk. See Section聽B.1.4.2, 鈥淲hat to Do If MySQL Keeps Crashing鈥�.

--enable-pstack

Print a symbolic stack trace on failure.

--gdb

Install an interrupt handler for SIGINT (needed to stop mysqld with ^C to set breakpoints) and disable stack tracing and core file handling. See MySQL Internals: Porting.

--init-file=file_name

Read SQL statements from this file at startup. Each statement must be on a single line and should not include comments.

This option is unavailable if MySQL was configured with the --disable-grant-options option. See Section聽2.4.14.2, 鈥淭ypical configure Options鈥�.

--innodb-safe-binlog

Adds consistency guarantees between the content of InnoDB tables and the binary log. See Section聽5.11.3, 鈥淭he Binary Log鈥�. This option was removed in MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support.

--innodb-xxx

The InnoDB options are listed in Section聽14.2.4, 鈥�InnoDB Startup Options and System Variables鈥�.

--language=lang_name, -L lang_name

Return client error messages in the 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. See Section聽5.10.2, 鈥淪etting the Error Message Language鈥�.

--large-pages

Some hardware/operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and OS. 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 pages support (which is called HugeTLB in Linux). We have plans to extend this support to FreeBSD, Solaris and possibly other platforms.

Before large pages can be used on Linux, it is necessary to configure the HugeTLB memory pool. For reference, consult the hugetlbpage.txt file in the Linux kernel source.

This option is disabled by default. It was added in MySQL 5.0.3.

--log[=file_name], -l [file_name]

Log connections and SQL statements received from clients to this file. See Section聽5.11.2, 鈥淭he General Query Log鈥�. If you omit the filename, MySQL uses host_name.log as the filename.

--log-bin[=base_name]

Enable binary logging. The server logs all statements that change data to the binary log, which is used for backup and replication. See Section聽5.11.3, 鈥淭he Binary Log鈥�.

The option value, if given, is the basename for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the basename. It is recommended that you specify a basename (see Section聽B.1.8.1, 鈥淥pen Issues in MySQL鈥�, for the reason). Otherwise, MySQL uses host_name-bin as the basename.

--log-bin-index[=file_name]

The index file for binary log filenames. See Section聽5.11.3, 鈥淭he Binary Log鈥�. If you omit the filename, and if you didn't specify one with --log-bin, MySQL uses host_name-bin.index as the filename.

--log-bin-trust-function-creators[={0|1}]

With no argument or an argument of 1, this option sets the log_bin_trust_function_creators system variable to 1. With an argument of 0, this option sets the system variable to 0. log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function creation. See Section聽17.4, 鈥淏inary Logging of Stored Routines and Triggers鈥�.

This option was added in MySQL 5.0.16.

--log-bin-trust-routine-creators[={0|1}]

This is the old name for --log-bin-trust-function-creators. Before MySQL 5.0.16, it also applies to stored procedures, not just stored functions and sets the log_bin_trust_routine_creators system variable. As of 5.0.16, this option is deprecated. It is recognized for backward compatibility but its use results in a warning.

This option was added in MySQL 5.0.6.

--log-error[=file_name]

Log errors and startup messages to this file. See Section聽5.11.1, 鈥淭he Error Log鈥�. If you omit the filename, MySQL uses host_name.err. If the filename has no extension, the server adds an extension of .err.

--log-isam[=file_name]

Log all MyISAM changes to this file (used only when debugging MyISAM).

--log-long-format (DEPRECATED)

Log extra information to the update log, binary update log, and slow query log, if they have been activated. For example, the username and timestamp are logged for all queries. This option is deprecated, as it now represents the default logging behavior. (See the description for --log-short-format.) The --log-queries-not-using-indexes option is available for the purpose of logging queries that do not use indexes to the slow query log.

--log-queries-not-using-indexes

If you are using this option with --log-slow-queries, queries that do not use indexes are logged to the slow query log. See Section聽5.11.4, 鈥淭he Slow Query Log鈥�.

--log-short-format

Log less information to the update log, binary update log, and slow query log, if they have been activated. For example, the username and timestamp are not logged for queries.

--log-slow-admin-statements

Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.

--log-slow-queries[=file_name]

Log all queries that have taken more than long_query_time seconds to execute to this file. See Section聽5.11.4, 鈥淭he Slow Query Log鈥�. See the descriptions of the --log-long-format and --log-short-format options for details.

--log-tc=file_name

The name of the memory-mapped transaction coordinator log file (for XA transactions that affect multiple storage engines when the binary log is disabled). The default name is tc.log. The file is created under the data directory if not given as a full pathname. Currently, this option is unused. Added in MySQL 5.0.3.

--log-tc-size=size

The 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]

Print out warnings such as Aborted connection... to the error log. Enabling this option is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). This option is enabled (1) by default, and the default level value if omitted is 1. To disable this option, use --log-warnings=0. Aborted connections are not logged to the error log unless the value is greater than 1. See Section聽B.1.2.10, 鈥淐ommunication Errors and Aborted Connections鈥�.

--low-priority-updates

Give table-modifying operations (INSERT, REPLACE, DELETE, UPDATE) lower priority than selects. This can also be done via {INSERT | REPLACE | DELETE | UPDATE} LOW_PRIORITY ... to lower the priority of only one query, or by SET LOW_PRIORITY_UPDATES=1 to change the priority in one thread. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). See Section聽7.3.2, 鈥淭able Locking Issues鈥�.

--max-binlog-dump-events

This option is used internally by the MySQL test suite for replication testing and debugging.

--memlock

Lock the mysqld process in memory. This works on systems such as Solaris that support the mlockall() system call. This might help if you have a problem where the operating system is causing mysqld to swap on disk. Note that use of this option requires that you run the server as root, which is normally not a good idea for security reasons. See Section聽5.6.5, 鈥淗ow to Run MySQL as a Normal User鈥�.

--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, each time mysqld opens a MyISAM table, it checks whether the table is marked as crashed or wasn't closed properly. (The last option works only if you are running with external locking disabled.) If this is the case, mysqld runs a check on the table. If the table was corrupted, mysqld attempts to repair it.

The following options affect how the repair works:

Before the server automatically repairs a table, it writes a note about the repair to the error log. If you want to be able to recover from most problems without user intervention, you should use the options BACKUP,FORCE. This forces a repair of a table even if some rows would be deleted, but it keeps the old data file as a backup so that you can later examine what happened.

See Section聽14.1.1, 鈥�MyISAM Startup Options鈥�.

--ndb-connectstring=connect_string

When using the NDB storage engine, it is possible to point out the management server that distributes the cluster configuration by setting the connect string option. See Section聽15.4.4.2, 鈥淭he Cluster Connectstring鈥�, for syntax.

--ndbcluster

If the binary includes support for the NDB Cluster storage engine, this option enables the engine, which is disabled by default. See Chapter聽15, MySQL Cluster.

--old-passwords

Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs. See Section聽5.7.9, 鈥淧assword Hashing as of MySQL 4.1鈥�.

--one-thread

Only use one thread (for debugging under Linux). This option is available only if the server is built with debugging enabled. See MySQL Internals: Porting.

--open-files-limit=count

Change the number of file descriptors available to mysqld. If this option is not set or is set to 0, mysqld uses the value to reserve file descriptors with setrlimit(). If the value is 0, mysqld reserves max_connections脳5 or max_connections + table_open_cache脳2 files (whichever is larger). You should try increasing this value if mysqld gives you the error Too many open files.

--pid-file=path

The pathname 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, -P port_num

The port number to use when listening for TCP/IP connections. The port number must be 1024 or higher unless the server is started by the root system user.

--port-open-timeout=num

On some systems, when the server is stopped, the TCP/IP port might not become available immediately. If the server is restarted quickly afterward, its attempt to reopen the port can fail. This option indicates how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened. The default is not to wait. This option was added in MySQL 5.0.19.

--safe-mode

Skip some optimization stages.

--safe-show-database (DEPRECATED)

See Section聽5.7.3, 鈥淧rivileges Provided by MySQL鈥�.

--safe-user-create

If this option is enabled, a user cannot create new MySQL users by using the GRANT statement, if the user doesn't have the INSERT privilege for the mysql.user table or any column in the table.

--secure-auth

Disallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords.

--shared-memory

Enable shared-memory connections by local clients. This option is available only on Windows.

--shared-memory-base-name=name

The name of shared memory to use for shared-memory connections. This option is available only on Windows. The default name is MYSQL. The name is case sensitive.

--skip-bdb

Disable the BDB storage engine. This saves memory and might speed up some operations. Do not use this option if you require BDB tables.

--skip-concurrent-insert

Turn off the ability to select and insert at the same time on MyISAM tables. (This is to be used only if you think you have found a bug in this feature.) See Section聽7.3.3, 鈥淐oncurrent Inserts鈥�.

--skip-external-locking

Do not use external locking (system locking). For more information about external locking, including conditions under which it can and cannot be used, see Section聽7.3.4, 鈥淓xternal Locking鈥�.

External locking has been disabled by default since MySQL 4.0.

--skip-grant-tables

This option causes the server not to use the privilege system at all, which gives anyone with access to the server unrestricted access to all databases. You can cause a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload command from a system shell, or by issuing a MySQL FLUSH PRIVILEGES statement after connecting to the server. This option also suppresses loading of user-defined functions (UDFs).

This option is unavailable if MySQL was configured with the --disable-grant-options option. See Section聽2.4.14.2, 鈥淭ypical configure Options鈥�.

--skip-host-cache

Do not use the internal hostname cache for faster name-to-IP resolution. Instead, query the DNS server every time a client connects. See Section聽7.5.7, 鈥淗ow MySQL Uses DNS鈥�.

--skip-innodb

Disable the InnoDB storage engine. This saves memory and disk space and might speed up some operations. Do not use this option if you require InnoDB tables.

--skip-merge

Disable the MERGE storage 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 to MyISAM table t, that user can create a MERGE table m that accesses t. However, if the user's privileges on t are subsequently revoked, the user can continue to access t by doing so through m.

--skip-name-resolve

Do not resolve hostnames when checking client connections. Use only IP numbers. If you use this option, all Host column values in the grant tables must be IP numbers or localhost. See Section聽7.5.7, 鈥淗ow MySQL Uses DNS鈥�.

--skip-ndbcluster

Disable the NDB Cluster storage engine. This is the default for binaries that were built with NDB Cluster storage engine support; the server allocates memory and other resources for this storage engine only if the --ndbcluster option is given explicitly. See Section聽15.4.3, 鈥淨uick Test Setup of MySQL Cluster鈥�, for an example of usage.

--skip-networking

Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are allowed. See Section聽7.5.7, 鈥淗ow MySQL Uses DNS鈥�.

--sporadic-binlog-dump-fail

This option is used internally by the MySQL test suite for replication testing and debugging.

--ssl*

Options that begin with --ssl specify whether to allow clients to connect via SSL and indicate where to find SSL keys and certificates. See Section聽5.8.7.3, 鈥淪SL Command Options鈥�.

--standalone

Instructs the MySQL server not to run as a service.

--symbolic-links, --skip-symbolic-links

Enable or disable symbolic link support. This option has different effects on Windows and Unix:

--skip-safemalloc

If MySQL is configured with --with-debug=full, all MySQL programs check for memory overruns during each memory allocation and memory freeing operation. This checking is very slow, so for the server you can avoid it when you don't need it by using the --skip-safemalloc option.

--skip-show-database

With this option, the SHOW DATABASES statement is allowed only to users who have the SHOW DATABASES privilege, and the statement displays all database names. Without this option, SHOW DATABASES is allowed to all users, but displays each database name only if the user has the SHOW DATABASES privilege or some privilege for the database. Note that any global privilege is considered a privilege for the database.

--skip-stack-trace

Don't write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. See MySQL Internals: Porting.

--skip-thread-priority

Disable using thread priorities for faster response time.

--socket=path

On Unix, this option specifies the Unix socket file to use when listening for local connections. The default value is /tmp/mysql.sock. On Windows, the option specifies the pipe name to use when listening for local connections that use a named pipe. The default value is MySQL (not case sensitive).

--sql-mode=value[,value[,value...]]

Set the SQL mode. See Section聽5.2.6, 鈥淪QL Modes鈥�.

--sysdate-is-now

As of MySQL 5.0.13, SYSDATE() by default returns the time at which it executes, not the time at which the statement in which it occurs begins executing. This differs from the behavior of NOW(). This option causes SYSDATE() to be an alias for NOW(). For information about the implications for binary logging and replication, see the description for SYSDATE() in Section聽12.6, 鈥淒ate and Time Functions鈥� and for SET TIMESTAMP in Section聽13.5.3, 鈥�SET Syntax鈥�.

This option was added in MySQL 5.0.20.

--tc-heuristic-recover={COMMIT|ROLLBACK}

The type of decision to use in the heuristic recovery process. Currently, this option is unused. Added in MySQL 5.0.3.

--temp-pool

This option causes most temporary files created by the server to use a small set of names, rather than a unique name for each new file. This works around a problem in the Linux kernel dealing with creating many new files with different names. With the old behavior, Linux seems to 鈥�leak鈥� memory, because it is being allocated to the directory entry cache rather than to the disk cache.

--transaction-isolation=level

Sets the default transaction isolation level. The level value can be READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. See Section聽13.4.6, 鈥�SET TRANSACTION Syntax鈥�.

--tmpdir=path, -t path

The path of the directory to use for creating temporary files. It might be useful if your default /tmp directory resides on a partition that is too small to hold temporary tables. This option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters (鈥�:鈥�) on Unix and semicolon characters (鈥�;鈥�) on Windows, NetWare, and OS/2. If the MySQL server is acting as a replication slave, you should not set --tmpdir to point to a directory on a memory-based filesystem 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, 鈥淲here MySQL Stores Temporary Files鈥�. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails.

--user={user_name|user_id}, -u {user_name|user_id}

Run the mysqld server as the user having the name user_name or the numeric user ID user_id. (鈥�User鈥� in this context refers to a system login account, not a MySQL user listed in the grant tables.)

This option is mandatory when starting mysqld as root. The server changes its user ID during its startup sequence, causing it to run as that particular user rather than as root. See Section聽5.6.1, 鈥淕eneral Security Guidelines鈥�.

To avoid a possible security hole where a user adds a --user=root option to a my.cnf file (thus causing the server to run as root), mysqld uses only the first --user option specified and produces a warning if there are multiple --user options. Options in /etc/my.cnf and $MYSQL_HOME/my.cnf are processed before command-line options, so it is recommended that you put a --user option in /etc/my.cnf and specify a value other than root. The option in /etc/my.cnf is found before any other --user options, which ensures that the server runs as a user other than root, and that a warning results if any other --user option is found.

--version, -V

Display version information and exit.

To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:

mysqld --verbose --help

To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:

mysqld --no-defaults --verbose --help

To see the current values used by a running server, use the SHOW VARIABLES statement.

Section聽5.2.4, 鈥淯sing System Variables鈥�, discusses the syntax for setting and displaying system variable values.

Section聽5.2.4.2, 鈥淒ynamic System Variables鈥�, lists the variables that can be set at runtime.

Information on tuning sytem variables can be found in Section聽7.5.2, 鈥淭uning Server Parameters鈥�.

Section聽14.2.4, 鈥�InnoDB Startup Options and System Variables鈥�, lists InnoDB system variables.