Mysqld Command-line Options.

In most cases you should manage mysqld options through option files.
mysqld and mysqld.server reads 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 the following command-line options:
--ansi Use ANSI SQL syntax instead of MySQL syntax.
-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 su ce. 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.
--character-sets-dir=path
Directory where character sets are.
--chroot=path
Chroot mysqld daemon during startup. Recommended security measure. It will somewhat limit 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 safe_mysqld
-h, --datadir=path
Path to the database root.
--default-character-set=charset
Set the default character set.
--default-table-type=type
Set the default table type for tables.
--debug[...]=
If MySQL is configured with --with-debug, you can use this option to get a trace file of what mysqld is doing.
--delay-key-write-for-all-tables
Don’t ush key bu ers between writes for any MyISAM table.
--enable-locking
Enable system locking. Note that if you use this option on a system which a not fully working lockd() (as on Linux) you will easily get mysqld to deadlock.
-T, --exit-info
This is a bit mask of di erent ags 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.
-?, --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.
-l, --log[=file]
Log connections and queries to file.
--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.

--log-update[=file]
Log updates to file.# where # is a unique number if not given.
--log-long-format
Log some extra information to 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}

MySQL Technical Reference
LOW_PRIORITY ... to lower the priority of only one query, or by SET OPTION SQL_LOW_PRIORITY_UPDATES=1 to change the priority in one thread.
--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.
--myisam-recover [=option[,option...]]] where option is any combination of DEFAULT, BACKUP, FORCE or QUICK. You can also set this explicitely 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 if the table wasn’t closed properly.
(The last option only works if you are running with --skip-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 a ects how the repair works.
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’ data file as
‘table_name-datetime.BAK’.
FORCE
Run recover even if we will loose more than one row from the .MYD file.
QUICK
Don’t check the rows in the table if there isn’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 data file as a backup so that you can later examine what happened.
--pid-file=path
Path to pid file used by safe_mysqld.
-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.
--one-thread
Only use one thread (for debugging under Linux).
-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. The tuning server parameters section includes information of how to optimise these.
--safe-mode

Skip some optimise stages. Implies --skip-delay-key-write.
--safe-show-database
Don’t show databases for which the user doesn’t have any privileges.
--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-concurrent-insert
Turn o the ability to select and insert at the same time on MyISAM tables.
is is only to be used if you think you have found a bug in this feature.)
--skip-delay-key-write
Ignore the delay_key_write option for all tables.
--skip-grant-tables
This option causes the server not to use the privilege system at all. This gives everyone ful l 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.
--skip-locking
Don’t use system locking. To use isamchk or myisamchk you must shut down the server. 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.
--skip-networking
Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed.
--skip-new
Don’t use new, possible wrong routines. Implies --skip-delay-key-write.
This will also set default table type to ISAM.
--skip-symlink
Don’t delete or rename files that a symlinked file in the data directory points to.
--skip-safemalloc
If MySQL is configured with --with-debug=full, all programs will check the memory for overruns for every memory allocation and memory freeing. As this checking is very slow, you can avoid this, when you don’t need memory checking, by using this option.
--skip-show-database
Don’t allow ’SHOW DATABASE’ commands, unless the user has process privilege.
--skip-stack-trace
Don’t write stack traces. This option is useful when you are running mysqld under a debugger.
--skip-thread-priority
Disable using thread priorities for faster response time.
--socket=path
Socket file to use for local connections instead of default /tmp/mysql.sock.
--sql-mode=option[,option[,option...]]
Option can be any combination of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_
QUOTES, IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY. It can also be empty ("") if you want to reset this.
By specifying all of the above options is same as using –ansi. With this option one can turn on only needed SQL modes.
transaction-isolation= { READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ
| SERIALIZABLE }
Sets the default transaction isolation level.
-t, --tmpdir=path
Path for temporary files. It may be useful if your default /tmp directory resides
on a partition too small to hold temporary tables.
-u, --user=user_name
Run mysqld daemon as user user_name. This option is mandatory when start-
ing mysqld as root.
-V, --version
Output version information and exit.
-W, --warnings
Print out warnings like Aborted connection... to the .err file.
4.1.2 my.cnf Option Files
MySQL can, since Version 3.22, read default startup options for the server and for clients from option files.
MySQL reads default options from the following files on Unix:
Filename
Purpose
/etc/my.cnf Global options
DATADIR/my.cnf Server-specific options
defaults-extra-file The file specified with –defaults-extra-file=#
~/.my.cnf User-specific options


DATADIR is the MySQL data directory (typically ‘/usr/local/mysql/data’ for a binary installation or ‘/usr/local/var’ for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! (--datadir has no e ect on where the server looks for option files, because it looks for them before it processes any command-line arguments.)
MySQL reads default options from the following files on Windows:
Filename
Purpose
windows-system-
directory\my.ini
Global options
C:\my.cnf Global options
C:\mysql\data\my.cnf Server-specific options
Note that on Windows, you should specify all paths with / instead of \. If you use \, you need to specify this twice, as \ is the escape character in MySQL.
MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command line or in option files take precedence over environment variable values.
The following programs support option files: mysql, mysqladmin, mysqld, mysqld_ safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, and myisampack.

You can use option files to specify any long option that a program supports! Run the program with --help to get a list of available options.
An option file can contain lines of the following forms:
#comment Comment lines start with ‘#’ or ‘;’. Empty lines are ignored.
[group] group is the name of the program or group for which you want to set options.
After a group line, any option or set-variable lines apply to the named group until the end of the option file or another group line is given.
option This is equivalent to --option on the command line.
option=value
This is equivalent to --option=value on the command line.
set-variable = variable=value
This is equivalent to --set-variable variable=value on the command line.
This syntax must be used to set a mysqld variable.
The client group allows you to specify options that apply to all MySQL clients (not mysqld). This is the perfect group to use to specify the password you use to connect to the server. (But make sure the option file is readable and writable only by yourself.)

Note that for options and values, all leading and trailing blanks are automatically deleted.
You may use the escape sequences ‘\b’, ‘\t’, ‘\n’, ‘\r’, ‘\\’, and ‘\s’ in your value string
(‘\s’ == blank).
Here is a typical global option file:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M
[mysqldump]
quick
Here is typical user option file:
[client]
# The following password will be sent to all standard MySQL clients password=my_password
[mysql]
no-auto-rehash
set-variable = connect_timeout=2
[mysqlhotcopy]
interactive-timeout

If you have a source distribution, you will find sample configuration files named ‘my-xxxx.cnf’ in the ‘support-files’ directory. If you have a binary distribution, look in the ‘DIR/support-files’ directory, where DIR is the pathname to the MySQL installation directory (typically ‘/usr/local/mysql’). Currently there are sample configuration files for small, medium, large, and very large systems. You can copy ‘my-xxxx.cnf’ to your home directory (rename the copy to ‘.my.cnf’) to experiment with this.
All MySQL clients that support option files support the following options:
–no-defaults
Don’t read any option files.
–print-defaults
Print the program name and all options that it will get.
–defaults-file=full-path-to-default-
file
Only use the given configuration file.
–defaults-extra-file=full-path-to-default-file
Read this configuration file after the global configuration file but before the user configuration file.

Note that the above options must be first on the command line to work! --print-defaults may however be used directly after the --defaults-xxx-file commands.
Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments.

This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply-specified options this way but doesn’t read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.
In shell scripts you can use the ‘my_print_defaults’ command to parse the config files:
shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
The above output contains all options for the groups ’client’ and ’mysql’.

Installing Many Servers on the Same Machine
In some cases you may want to have many di erent mysqld daemons (servers) running on the same machine. You may for example want to run a new version of MySQL for testing together with an old version that is in production. Another case is when you want to give di erent users access to di erent mysqld servers that they manage themselves.
One way to get a new server running is by starting it with a di erent socket and port as follows:
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> scripts/mysql_install_db
shell> bin/safe_mysqld &

The environment variables appendix includes a list of other environment variables you can use to a ect mysqld.
The above is the quick and dirty way that one commonly uses for testing. The nice thing with this is that all connections you do in the above shell will automatically be directed to the new running server!
If you need to do this more permanently, you should create an option file for each server.
See Section 4.1.2 [Option files], page 168. In your startup script that is executed at boot time (mysql.server?) you should specify for both servers:
safe_mysqld --default-file=path-to-option-file
At least the following options should be di erent per server:
port=#
socket=path
pid-file=path
The following options should be di erent, if they are used:
log=path
log-bin=path
log-update=path
log-isam=path
bdb-logdir=path
If you want more performance, you can also specify the following di erently:
tmpdir=path
bdb-tmpdir=path
See Section 4.1.1 [Command-line options], page 164.
If you are installing binary MySQL versions (.tar files) and start them with ./bin/safe_mysqld then in most cases the only option you need to add/change is the socket and port argument to safe_mysqld.

If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog: