MySQL Reference Manual

Chapter 4. Database Administration

Configuring MySQL

mysqld Command-Line Options

In most cases you should manage mysqld options through option files. See Section 4.1.2.

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 the following command-line options:

--ansi

Use ANSI SQL syntax instead of MySQL syntax. See Section 1.7.2.

-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.

--character-sets-dir=path

Directory where character sets are. See Section 4.6.1.

--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. See Section 4.7.2. 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 D.1.2.

--default-character-set=charset

Set the default character set. See Section 4.6.1.

--default-table-type=type

Set the default table type for tables. See Chapter 7.

--delay-key-write-for-all-tables

Don’t flush key buffers between writes for any MyISAM table. See Section 5.5.2.

--des-key-file=filename

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

--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.

-?, --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.

-l, --log[=file]

Log connections and queries to file. See Section 4.9.2.

--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.

--log-update[=file]

Log updates to file.# where # is a unique number if not given. See Section 4.9.3.

--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} 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. See Section 5.3.2.

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

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-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 affect 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 data file 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 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. See Section 2.5.4.

--one-thread

Only use one thread (for debugging under Linux). See Section D.1.

-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.6.4. The tuning server parameters section includes information on how to optimise these. See Section 5.5.2.

--safe-mode

Skip some optimisation 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 privileges to the mysql.user table or any column in this table.

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

Ignore the delay_key_write option for all tables. See Section 5.5.2.

--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 hostname cache for faster name-ip resolution, but query the DNS server on every connect instead. See Section 5.5.5.

--skip-locking

Don’t use system locking. To use isamchk or myisamchk you must shut down the server. See Section 1.2.3. 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.

--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. See Section 5.5.5.

--skip-new

Don’t use new, possibly wrong routines. Implies --skip-delay-key-write. This will also set the default table type to ISAM. See Section 7.3.

--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. See Section D.1.

--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, and ONLY_FULL_GROUP_BY. It can also be empty ("") if you want to reset this.

Specifying all of these options is the same as using --ansi. With this option one can turn on only needed SQL modes. See Section 1.7.2.

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

Sets the default transaction isolation level. See Section 6.7.3.

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

Run mysqld daemon as user user_name or userid (numeric). This option is mandatory when starting mysqld as root.

-V, --version

Output version information and exit.

-W, --warnings

Print out warnings like Aborted connection... to the .err file. See Section A.2.9.

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 effect 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

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 previously. 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. See Appendix E.

The following programs support option files: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, and myisampack.

Any long option that may be given on the command-line when running a MySQL program can be given in an option file as well (without the leading double dash). 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:

Option	Description
--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 preceding 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 multiple-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 preceding 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 different 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 different users access to different mysqld servers that they manage themselves.

One way to get a new server running is by starting it with a different 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 affect mysqld. See Appendix E.

This 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 preceding 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. In your startup script that is executed at boot time you should specify for both servers:

safe_mysqld --default-file=path-to-option-file

At least the following options should be different per server:

• port=#

• socket=path

• pid-file=path

The following options should be different, 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 differently:

• tmpdir=path

• bdb-tmpdir=path

Section 4.1.1.

If you are installing binary MySQL versions (.tar files) and start them with ./bin/safe_mysqld, in most cases the only option you need to add/change is the socket and port argument to safe_mysqld.

Section 4.1.4.

Running Multiple MySQL Servers on the Same Machine

There are circumstances when you might want to run multiple servers on the same machine. For example, you might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you might be an Internet service provider that wants to provide independent MySQL installations for different customers.

If you want to run multiple servers, the easiest way is to compile the servers with different TCP/IP ports and socket files so they are not both listening to the same TCP/IP port or socket file. See Section 4.7.3.

Assume an existing server is configured for the default port number and socket file. Then configure the new server with a configure command something like this:

shell> ./configure  --with-tcp-port=port_number \
             --with-unix-socket-path=file_name \
             --prefix=/usr/local/mysql-3.22.9

Here port_number and file_name should be different from the default port number and socket file pathname, and the --prefix value should specify an installation directory different from the one under which the existing MySQL installation is located.

You can check the socket used by any currently executing MySQL server with this command:

shell> mysqladmin -h hostname --port=port_number variables

Note that if you specify "localhost" as a hostname, mysqladmin will default to using Unix sockets instead of TCP/IP.

If you have a MySQL server running on the port you used, you will get a list of some of the most important configurable variables in MySQL, including the socket name.

You don’t have to recompile a new MySQL server just to start with a different port and socket. You can change the port and socket to be used by specifying them at runtime as options to safe_mysqld:

shell> /path/to/safe_mysqld --socket=file_name --port=port_number

mysqld_multi can also take safe_mysqld (or mysqld) as an argument and pass the options from a configuration file to safe_mysqld and further to mysqld.

If you run the new server on the same database directory as another server with logging enabled, you should also specify the name of the log files to safe_mysqld with --log, --log-update, or --log-slow-queries. Otherwise, both servers may be trying to write to the same log file.

Warning: normally you should never have two servers that update data in the same database! If your OS doesn’t support fault-free system locking, this may lead to unpleasant surprises!

If you want to use another database directory for the second server, you can use the --datadir=path option to safe_mysqld.

Note also that starting several MySQL servers (mysqlds) in different machines and letting them access one data directory over NFS is generally a bad idea! The problem is that the NFS will become the bottleneck with the speed. It is not meant for such use. And last but not least, you would still have to come up with a solution for ensuring that two or more mysqlds are not interfering with each other. At the moment there is no platform that would 100% reliably do the file locking (lockd daemon usually) in every situation. Yet there would be one more possible risk with NFS; it would make the work even more complicated for lockd daemon to handle. So make it easy for yourself and forget about the idea. The working solution is to have one computer with an operating system that efficiently handles threads and have several CPUs in it.

When you want to connect to a MySQL server that is running with a different port than the port that is compiled into your client, you can use one of the following methods:

• Start the client with --host 'hostname' --port=port_number to connect with TCP/IP, or [--host localhost] --socket=file_name to connect via a Unix socket.

• In your C or Perl programs, you can give the port or socket arguments when connecting to the MySQL server.

• If you are using the Perl DBD::mysql module you can read the options from the MySQL option files. See Section 4.1.2.

  $dsn = "DBI:mysql:test;mysql_read_default_group=client;
          mysql_read_default_file=/usr/local/mysql/data/my.cnf"
  $dbh = DBI->connect($dsn, $user, $password);

• Set the MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables to point to the Unix socket and TCP/IP port before you start your clients. If you normally use a specific socket or port, you should place commands to set these environment variables in your .login file. See Appendix E.

• Specify the default socket and TCP/IP port in the .my.cnf file in your home directory. See Section 4.1.2.