4.1.1 mysqld Command-line Options

In most cases you should manage mysqld options through option files. See section 4.1.2 `my.cnf' Option Files.

mysqld and mysqld.server read options from the mysqld and server groups. mysqld_safe read options from the mysqld, server, mysqld_safe and safe_mysqld groups. An embedded MySQL server usually reads options from the server, embedded and xxxxx_SERVER, where xxxxx is the name of the application.

mysqld accepts a lot of command-line options. Here follows some of the most common ones. For a full list execute mysqld --help. Options used for replication are listed in a separate section, see section 4.10.6 Replication Options in `my.cnf'.

--ansi

Use SQL-99 syntax instead of MySQL syntax. See section 1.8.2 Running MySQL in ANSI Mode.

-b, --basedir=path

Path to installation directory. All paths are usually resolved relative to this.

--big-tables

Allow big result sets by saving all temporary sets on file. It solves most 'table full' errors, but also slows down the queries where in-memory tables would suffice. Since Version 3.23.2, MySQL is able to solve it automatically by using memory for small temporary tables and switching to disk tables where necessary.

--bind-address=IP

IP address to bind to.

--console

Write the error log messages to stderr/stdout even if --log-error is specified. On Windows, mysqld will not close the console screen if this option is used.

--character-sets-dir=path

Directory where character sets are. See section 4.6.1 The Character Set Used for Data and Sorting.

--chroot=path

Put mysqld daemon in chroot environment at startup. Recommended security measure since MySQL 4.0 (MySQL 3.23 is not able to provide 100% closed chroot jail). It somewhat limits LOAD DATA INFILE and SELECT ... INTO OUTFILE though.

--core-file

Write a core file if mysqld dies. For some systems you must also specify --core-file-size to mysqld_safe. See section 4.7.2 mysqld_safe, The Wrapper Around mysqld. Note that on some systems, like Solaris, you will not get a core file if you are also using the --user option.

-h, --datadir=path

Path to the database root.

--debug[...]=

If MySQL is configured with --with-debug, you can use this option to get a trace file of what mysqld is doing. See section E.1.2 Creating Trace Files.

--default-character-set=charset

Set the default character set. See section 4.6.1 The Character Set Used for Data and Sorting.

--default-table-type=type

Set the default table type for tables. See section 7 MySQL Table Types.

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

How MyISAM DELAYED KEYS should be used. See section 5.5.2 Tuning Server Parameters.

--delay-key-write-for-all-tables; In MySQL 4.0.3 you should use --delay-key-write=ALL instead.

Don't flush key buffers between writes for any MyISAM table. See section 5.5.2 Tuning Server Parameters.

--des-key-file=filename

Read the default keys used by DES_ENCRYPT() and DES_DECRYPT() from this file.

--enable-external-locking (was --enable-locking)

Enable system locking. Note that if you use this option on a system on which lockd does not fully work (as on Linux), you will easily get mysqld to deadlock.

--enable-named-pipe

Enable support for named pipes (only on NT/Win2000/XP).

-T, --exit-info

This is a bit mask of different flags one can use for debugging the mysqld server; one should not use this option if one doesn't know exactly what it does!

--flush

Flush all changes to disk after each SQL command. Normally MySQL only does a write of all changes to disk after each SQL command and lets the operating system handle the syncing to disk. See section A.4.1 What To Do If MySQL Keeps Crashing.

-?, --help

Display short help and exit.

--init-file=file

Read SQL commands from this file at startup.

-L, --language=...

Client error messages in given language. May be given as a full path. See section 4.6.2 Non-English Error Messages.

-l, --log[=file]

Log connections and queries to file. See section 4.9.2 The General Query Log.

--log-bin=[file]

Log all queries that change data to the file. Used for backup and replication. See section 4.9.4 The Binary Log.

--log-bin-index[=file]

Index file for binary log file names. See section 4.9.4 The Binary Log.

--log-error[=file]

Log errors and startup messages to this file. See section 4.9.1 The Error Log.

--log-isam[=file]

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

--log-slow-queries[=file]

Log all queries that have taken more than long_query_time seconds to execute to file. See section 4.9.5 The Slow Query Log.

--log-update[=file]

Log updates to file.# where # is a unique number if not given. See section 4.9.3 The Update Log. The update log is deprecated and will be removed in MySQL 5.0; you should use the binary log instead (--log-bin). See section 4.9.4 The Binary Log. Starting from version 5.0, using --log-update will just turn on the binlog instead.

--log-long-format

Log some extra information to the update log. If you are using --log-slow-queries then queries that are not using indexes are logged to the slow query log.

--low-priority-updates

Table-modifying operations (INSERT/DELETE/UPDATE) will have lower priority than selects. It can also be done via {INSERT | REPLACE | UPDATE | DELETE} LOW_PRIORITY ... to lower the priority of only one query, or by SET LOW_PRIORITY_UPDATES=1 to change the priority in one thread. See section 5.3.2 Table Locking Issues.

--memlock

Lock the mysqld process in memory. This works only if your system supports the mlockall() system call (like Solaris). This may 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...]]]

Option is any combination of DEFAULT, BACKUP, FORCE or QUICK. You can also set this explicitly to "" if you want to disable this option. If this option is used, mysqld will on open check if the table is marked as crashed or if the table wasn't closed properly. (The last option only works if you are running with --skip-external-locking.) If this is the case mysqld will run check on the table. If the table was corrupted, mysqld will attempt to repair it. The following options affects how the repair works.

Option	Description
DEFAULT	The same as not giving any option to --myisam-recover.
BACKUP	If the data table was changed during recover, save a backup of the `table_name.MYD' datafile as `table_name-datetime.BAK'.
FORCE	Run recover even if we will lose more than one row from the .MYD file.
QUICK	Don't check the rows in the table if there aren't any delete blocks.

Before a table is automatically repaired, MySQL will add a note about this in the error log. If you want to be able to recover from most things 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 datafile as a backup so that you can later examine what happened.

--new

From version 4.0.12, the --new option can be used to make the server behave as 4.1 in certain aspects, easing a 4.0 to 4.1 upgrade:

• TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. See section 6.2 Column Types.

--pid-file=path

Path to pid file used by mysqld_safe.

-P, --port=...

Port number to listen for TCP/IP connections.

-o, --old-protocol

Use the 3.20 protocol for compatibility with some very old clients. See section 2.5.5 Upgrading from Version 3.20 to 3.21.

--one-thread

Only use one thread (for debugging under Linux). See section E.1 Debugging a MySQL server.

-O, --set-variable var=option

Give a variable a value. --help lists variables. You can find a full description for all variables in the SHOW VARIABLES section in this manual. See section 4.5.7.4 SHOW VARIABLES. The tuning server parameters section includes information of how to optimise these. Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own. See section 5.5.2 Tuning Server Parameters. In MySQL 4.0.2 one can set a variable directly with --variable-name=option and set-variable is no longer needed in option files. If you want to restrict the maximum value a startup option can be set to with SET, you can define this by using the --maximum-variable-name command line option. See section 5.5.6 SET Syntax. Note that when setting a variable to a value, MySQL may automatically correct it to stay within a given range and also adjusts the value a little to fix for the used algorithm.

--safe-mode

Skip some optimise stages.

--safe-show-database

With this option, the SHOW DATABASES command returns only those databases for which the user has some kind of privilege. From version 4.0.2 this option is deprecated and doesn't do anything (the option is enabled by default) as we now have the SHOW DATABASES privilege. See section 4.3.1 GRANT and REVOKE Syntax.

--safe-user-create

If this is enabled, a user can't create new users with the GRANT command, if the user doesn't have INSERT privilege to the mysql.user table or any column in this table.

--skip-bdb

Disable usage of BDB tables. This will save memory and may speed up some things.

--skip-concurrent-insert

Turn off the ability to select and insert at the same time on MyISAM tables. (This is only to be used if you think you have found a bug in this feature.)

--skip-delay-key-write

In MySQL 4.0.3 you should use --delay-key-write=OFF instead. Ignore the DELAY_KEY_WRITE option for all tables. See section 5.5.2 Tuning Server Parameters.

--skip-grant-tables

This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)

--skip-host-cache

Never use host name cache for faster name-ip resolution, but query DNS server on every connect instead. See section 5.5.5 How MySQL uses DNS.

--skip-innodb

Disable usage of Innodb tables. This will save memory and disk space and speed up some things.

--skip-external-locking (was --skip-locking)

Don't use system locking. To use isamchk or myisamchk you must shut down the server. See section 1.2.3 How Stable Is MySQL?. Note that in MySQL Version 3.23 you can use REPAIR and CHECK to repair/check MyISAM tables.

--skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost. See section 5.5.5 How MySQL uses DNS.

--skip-networking

Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes or Unix sockets. This option is highly recommended for systems where only local requests are allowed. See section 5.5.5 How MySQL uses DNS.

--skip-new

Don't use new, possibly wrong routines.

--skip-symlink

Deprecated option in 4.0.13; use --skip-symbolic-links instead.

--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. See section 2.6.1.5 Distributing Data Across Different Disks on Windows. On Unix, enabling symbolic links means that you can link a MyISAM index file or datafile 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 will be deleted or renamed.

--skip-safemalloc

If MySQL is configured with --with-debug=full, all programs check memory for overruns for every memory allocation and memory freeing operations. 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

Don't allow the SHOW DATABASES command, unless the user has the SHOW DATABASES privilege. From version 4.0.2 on, you should no longer need this option, because access can now be granted specifically with the SHOW DATABASES privilege that was added in that version.

--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 section E.1 Debugging a MySQL server.

--skip-thread-priority

Disable using thread priorities for faster response time.

--socket=path

On Unix, the socket file to use for local connections (default `/tmp/mysql.sock'). On Windows, the pipe name to use for local connections that use a named pipe (default MySql).

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

The option values can be any combination of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, MYSQL323, MYSQL40, DB2, MSSQL, ORACLE, POSTGRESQL, SAPDB, or ANSI. The value also can be empty (--sql-mode="") if you want to reset it. Several of the option values are used for compatibility with other servers. If specified, they cause the server to omit from the output of SHOW CREATE TABLE those parts of the statement that are not understood by earlier versions of MySQL or other database servers. Using these option values results in CREATE TABLE statements that are more portable for use with other servers:

• The NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_KEY_OPTIONS values cause omission of table options, or options pertaining to column or index definitions.
• The values MYSQL323 and MYSQL40 are for compatibility with MySQL 3.23 and MySQL 4.0.
• The values used for compatibility with other servers are DB2, MSSQL, ORACLE, POSTGRESQL, and SAPDB.

These options also affect the output of mysqldump, because that program uses SHOW CREATE TABLE to obtain the table-creation statements that it includes in its own output. Several of the option values have a complex effect because they are shorthand for a group or set of values. For example, you can tell the server to run in ANSI mode by using the --sql-mode=ANSI (or --ansi) option, which is equivalent to specifying both of the following command-line options:

--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
--transaction-isolation=SERIALIZABLE

Note that specifying ANSI mode in this way also has the effect of setting the transaction isolation level. For more information about running the server in ANSI mode, see section 1.8.2 Running MySQL in ANSI Mode. Other ``group'' values are DB2, MSSQL, ORACLE, POSTGRESQL, and SAPDB. Specifying any of them turns on the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_KEY_OPTIONS values. The --sql-mode option was added in MySQL 3.23.41. The NO_UNSIGNED_SUBTRACTION value was added in 4.0.0. The NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, MYSQL323, MYSQL40, DB2, MSSQL, ORACLE, POSTGRESQL, SAPDB, and values were added in 4.1.1. ANSI was added in 4.1.1.

--temp-pool

Using this option will cause most temporary files created by the server to use a small set of names, rather than a unique name for each new file. This is to work around a problem in the Linux kernel dealing with creating many new files with different names. With the old behaviour, Linux seems to ``leak'' memory, as it's being allocated to the directory entry cache rather than to the disk cache.

--transaction-isolation={ READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE }

Sets the default transaction isolation level. See section 6.7.4 SET TRANSACTION Syntax.

-t, --tmpdir=path

Path of the directory to use for creating temporary files. It may 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.

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

Run the mysqld server as the user having the name user_name or 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. See section 4.2.2 How to Make MySQL Secure Against Crackers. Starting from MySQL 3.23.56 and 4.0.12: To avoid a possible security hole where a user adds a --user=root option to some `my.cnf' file (thus causing the server to run as root), mysqld uses only the first --user option specified and produces a warning if there are multiple --user options. Options in `/etc/my.cnf' and `datadir/my.cnf' are processed before command-line options, so it is recommended that you put a --user option in `/etc/my.cnf' and specify a value other than root. The option in `/etc/my.cnf' will be found before any other --user options, which ensures that the server runs as a user other than root, and that a warning results if any other --user option is found.

-V, --version

Display version information and exit.

-W, --log-warnings

Print out warnings like Aborted connection... to the `.err' file. 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). See section A.2.10 Communication Errors / Aborted Connection. This option used to be called --warnings.

You can change most values for a running server with the SET command. See section 5.5.6 SET Syntax.

User Comments

Solaris does not allow setuid() programs to
produce core files. If you want MySQL to produce
a core file when it segfaults on a Solaris system,
you need to either run it as root or start the
daemon from another user. If you are using the
"mysql.server" init script, you can modify the
line that runs the safe_mysqld script and prepend
"su - USER_NAME -c ". This will change to the
specified user and then start MySQL. Be sure this
user has write permission to the MySQL log file.

Since Solaris 7 you can enable suid/setgid
programms to dump core by setting
allow_setid_core to 1 in /etc/system.

When using --chroot=/path, the following steps
need to be taken before MySQL can be started :
<ul><li>If using --user=mysql, create
a /path/etc/passwd containing only the entry for
the 'mysql' user from your system-
wide /etc/passwd. You could probably get around
this by using --user=uid instead.<li>Make sure
your 'tmpdir' exists relative
to /path.<li>Under /path/var/lib create a symlink
for 'mysql' -> '../..'. This will make sure any
configured and hardwired paths will resolve under
the chrooted environment. If your MySQL root
directory is not /var/lib/mysql, substitute that
here.</ul>One side-effect is that MySQL
interprets 'etc' and 'var' as empty databases.

Can you bind to multiple addresses such as 192.168.1.*?

Wouldn't it be nice for another command line switch, similar to --bind-address, that let you bind to maybe a comma seperated list of addresses... such as possibly

--bind-addresses=127.0.0.1,192.168.1.2

Add your own comment.