15.2. mysqld Server
| mysqld |
mysqld [options] |
When mysqld starts, various options can be used to alter the server's
behavior. Although you don't need to know all of the server options
available or use them—quite often the default settings are fine—as a
database administrator, it's useful to know what options exist for
various categories that may be related to your needs.
Options may be given at the command line when starting or
restarting the server. However, it's common practice to enter them
into a configuration file. On Unix-based systems, the main configuration file
typically is /etc/my.cnf. For Windows systems, the main file is usually either
c:\systems\my.ini or
c:\my.conf. Options are entered on separate lines
and follow a variable=value format. Some
options are binary and can be enabled by just including the option at
the command line when starting the server or in the options file with
no value (or an equals sign followed by no value).
Within the options file, options are grouped under headings
contained within square brackets. The mysqld
daemon reads options from the configuration file under the headings of
[mysqld] and [server] as it's
started. For more recent versions of the MySQL server, the group
[mysqld-5.0] is also read. Groups are read in the
order mentioned here, and the last setting for an option read is the
one used. To get a list of options that mysqld is
using on a particular server, enter the following line from the
command line (results follow):
$ mysqld --print-defaults
/usr/libexec/mysqld would have been started with the following arguments:
--datadir=/data/mysql --socket=/var/lib/mysql/mysql.sock
--old-passwords=1
As the resulting message indicates, the
--print-defaults options draws information from the
options files and indicates the options and what their values would be
if the MySQL server were restarted. However, if the options files were
changed since MySQL was started, or if MySQL was started from the
command line or with command-line options from a script on the server,
this output will not reflect those options. Basically, the results of
--print-defaults do not reflect the current
settings, just the options it finds in the options files for the
relevant server groups. To determine the current server options that
have been used—other than the default options—while a server is
running, you can enter the following command from a Unix system
(sample results follow):
$ ps aux | grep mysql
mysql 27670 0.2 3.2 124252 17296 ? Sl Aug21 25:06
/usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr
--datadir=/data/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid
--skip-locking --socket=/var/lib/mysql/mysql.sock
If you see an option that you don't see in your default options
file, it may be coming from a different options file. You may even be
running a different installation of mysqld than you
think. In such a situation, you would have to specify the path to the
mysqld you want to use when starting the
server.
In the following sections of this chapter, options are grouped
by their use:
Location
These options specify where the server can find files and
directories it needs.Security and connection
These options are related to user and database security,
limits on connections, and how clients connect to the
server.Global
These options affect server behavior, and are stored in
global variables.Logs
These options relate to server logs.
Performance optimization
This section contains several options that could be
included in other categories, but they are worth considering
together because they can affect the speed of the database.Replication
These options are strictly related to replication.
Storage engine specific options
These options concerning storage engines (formerly known
as table types) are grouped into subsections based on the
specific storage engines to which they relate.
Some options are listed in more than one section because they
have more than one use relative to the sections listed.
The options are shown as they would be entered from the
command line. If an option is used in a configuration file,
the long form should be used and the double-dash prefix
should be omitted. For example,
--basedir=/data/mysql would be entered from the
command line. However, in a configuration file the same option would
read as basedir=/data/mysql on
its own separate line.
The syntax for listing options is as follows:
--option=value
An option that requires a value
--option[=value]
An option that can take a value, but does not require
one--option[=value]
A binary option that is to be given without a value
A few options have single-letter abbreviations, also called
short forms. The short form is shown in parentheses
after the long form.
As new versions of MySQL are released, more options may be
added. To get a list for your version, type mysqld --verbose
--help from the command line on the server host.
For many of the options, there is a system variable with the
same name as the option, but without the leading double-dashes. For
some options, the dashes within the name will need to be changed to
underscores (e.g., the variable associated with
--setting-example would be
setting_example). Before changing the value or the
setting of a variable, it's often a good idea to see what the variable
is set to. You can do this by entering a statement like this:
SHOW VARIABLES LIKE 'setting_example';
Location
Some mysqld options allow you to instruct MySQL where files are located and
what network settings should be used when clients connect to it
remotely. An alphabetical list of these options follows, along with
the syntax and an explanation of each. This list does not include
storage system specific options related to file paths. See the section
for the particular storage engine's options later in this
chapter:
--basedir=path,
-b pathIf you've installed more than one version of MySQL on your
server or if you have moved the binary files for MySQL, you will
need to specify the base directory for the MySQL installation.
This option is particularly necessary if you're using
mysqld_safe to keep the
mysqld daemon running; list this option
under the [mysqld_safe] group heading.--character-sets-dir=path
This option specifies the absolute path to the directory
containing character sets. By default, this directory is in the
subdirectory charsets in the directory
where MySQL is installed (e.g.,
/usr/share/mysql/charsets/).--datadir=path,
-h pathIf you want to put your datafiles for MySQL (i.e.,
database directories and table files) in a different directory
from the default, you need to use this option. This is useful
especially if you want the data on a different hard drive.
Within the directory that you name, MySQL will create
subdirectories for each database. If you use this option, be
sure that the mysql user on the filesystem
has permissions to read and write to the directory. Generally,
you would make it the owner of the directory.--init-file=filename
If you have a set of SQL commands that you must execute
every time you restart the server, rather than enter them
manually you could put them in a file and use this option to
tell MySQL to execute them for you at startup. Each SQL
statement in the file must be on a separate line. Unfortunately,
you cannot include comments in the file. You could put them in a
separate text file in the same directory, perhaps with a similar
same filename (e.g., init.sql and
init.txt).--secure-file-priv=path
Use this option to restrict the importing of files to the
given path. This is related to the SELECT...INTO
OUTFILE and LOAD DATA statements,
as well as the LOAD_FILE( ) function.
This option is available as of version 5.1.17 of MySQL.--pid-file=filename
Instead of starting mysqld directly,
the common method used lately is to start the script
mysqld_safe. It will in turn start
mysqld and make sure it keeps running. Thus,
if mysqld crashes,
mysqld_safe will automatically restart it. To
keep track of the system process for mysqld,
the mysqld_safe program will record the
process identification number in a file called
mysqld.pid. With this option, you can tell
MySQL where to put that file.--plugin-dir=path
This option sets the directory where plugins on the server
are placed. It's available as of version 5.1.2 of MySQL.--skip-symbolic-links
This option is used to disable symbolic links. The reverse
is to enable them through --symbolic-links.
Prior to version 4.0.3 of MySQL, this option was
--skip-symlink.--slave-load-tmpdir=value
This option specifies the directory where a slave server
stores temporary files when the LOAD DATA
INFILE statement is executed.--slow-query-log-file=filename
See the Performance optimization" section later in
this chapter.--socket=filename
Socket files are used on Unix systems. With this option,
you may specify the path and filename of the socket file. If you
don't use this option, recent versions of MySQL place the socket
file in the data directory of MySQL. On Windows systems, this
option may be used to provide the pipe name
(MySQL by default) for local connections.
Just as with the --port option, the
--socket option may be used for multiple
instances of MySQL. You could issue one
mysqld_safe command with the default socket
file and another with an option such as
--socket=mysqld_test.sock to indicate a test
server. A second server that you assign to the same socket file
will refuse to start because otherwise the daemons would
conflict with each other. Incidentally, it's not necessary to
specify a separate port and socket file, but most administrators
do it all the same.--symbolic-links,
-sThis option enables symbolic links at the filesystem level
for database directories and table files. MySQL expects to find
the files in its data directory, but if you want to store the
data in other directories in order to find more space or spread
reads and writes around, this option allows you to create links
in the data directory that point to where the data actually is
stored. On Windows systems, this allows you to create shortcuts
to databases (e.g., database.sym). On Unix
systems with MyISAM tables, this option allows you to specify a
different directory for a table's location with the
DATA DIRECTORY or INDEX
DIRECTORY options of both the ALTER
TABLE and CREATE TABLE SQL
statements. When the table is renamed or deleted, the related
files that are symbolically linked will be renamed or deleted,
respectively.--sync-frm
This option instructs the server to synchronize the
.frm files with the filesystem when a table
is created. This slows down table creation slightly, but is more
stable than leaving it in memory only.--temp-pool
This option instructs the server to utilize a small set of
names for temporary file-naming rather than unique names for
each file.--tmpdir=path,
-t pathIf you want to control where MySQL places its temporary
files, specify this option. You can give multiple file paths in
a colon-separated list. When you're using a storage engine such
as InnoDB to create tablespaces over multiple files and you're
working with huge tables of data that would exceed the
filesystem limits, this option is useful for working around
those limits. For instance, if you have a system with a file or
directory size limit of 4 MB, you can provide two directories
with the --tmpdir option
and thereby double your physical table limitations to 8 MB. The
directories could even be on separate filesystems that your
operating system mounts.
Security and connections
These mysqld server options relate to security, user-related settings, and
the network connections clients make to the server:
--allow-suspicious-udfs[={0|1}]
As of version 5.0.3 of MySQL, the server requires
user-defined functions to be named with an acceptable
suffix—function_name_add( ),
function_name_clear( ),
function_name_deinit( ),
function_name_init( ),
function_name_reset( ),
etc.—and won't load functions that fail to adhere to that
standard. However, you can disable that security protection by
giving this option a value of 0. A value of 1 enables it and is
the default.--automatic-sp-privileges[={0|1}]
By default, this option is set to 1 and therefore gives
users the ALTER ROUTINE and the
EXECUTE privileges for any stored routine
that the user has created, as long as the user and those
routines exist. If you set this option to 0, the user does not
get those privileges and therefore cannot alter or execute
routines. However, you can explicitly grant users those
privileges, as with other MySQL privileges.--back-log=value
When the primary thread of the MySQL server gets many
connection requests simultaneously, they are backlogged while
the server begins new threads. Use this option to set the number
of connections that may be backed up. The number cannot exceed
the system value for TCP/IP connections related to the
listen( ) system function.--bind-address=address
This option specifies the IP address the server binds to.
It's used to restrict network access on a host with multiple IP
addresses.--bootstrap
This option isn't normally used by administrators. It's
used by the mysql_install_db script to create
the necessary privileges tables without the
mysqld daemon running.--character-set-client-handshake
Use this option at the command line only (not available in
the options file) to instruct the server not to ignore strange
characters that it receives (perhaps due to a character set
mismatch) from the client. Use
--skip-character-set-client-handshake to
disable this option because it's set by default.--chroot=path
This option runs the daemon with
chroot( ) from the filesystem so as to
start it in a closed environment for additional security. This
is a recommended security measure.--connect-timeout=value
This option may be used to change the number of seconds
that the server should wait for a connection packet before
terminating the connection and returning Bad
Handshake. As of version 5.1.23, the related variable
is set to five seconds by default. If clients display messages
saying that they lost the connection to the server, you might
try increasing this value.--des-key-file=filename
This option instructs the server to obtain the default
keys from the given file when the MySQL functions
DES_ENCRYPT( ) or
DES_DECRYPT( ) are used.--enable-named-pipe
This option enables support for named pipe connections
with the mysqld-nt and
mysqld-max-nt servers, which support them.
It's used only with Windows NT, 2000, XP, and 2003 systems; do
not use it on non-Windows systems (e.g., Linux or Mac OS X). Use
the --socket option with this one to specify
the path and name of the pipe.--init-connect='string'
This option specifies one or more SQL statements, all
combined in a single string, that are
to be executed each time a client connects to the server. It
will not allow SQL statements to be executed for users with the
SUPER privilege.--init-file=filename
This option indicates a file containing SQL statements
that are to be executed when the server is started. This option
will not work if the --disable-grant-options
option is enabled. SQL statements need to be on separate lines,
and comments are not permitted in the file.--interactive-timeout=value
For interactive clients (clients using
mysql_real_connect( ) with the CLIENT_INTERACTIVE flag), this
option sets the number of seconds of inactivity allowed before
closing the connection.--local-infile[={0|1}]
The SQL statement LOAD DATA INFILE can
import data from a file on either the server's host or the
client's host. By adding the LOCAL option,
the client instructs the server to import locally from the
client machine. This has the potential to be a security problem,
though, because the file being loaded could have malicious code.
Therefore, some administrators for public servers want to
prevent clients from being able to import files local to the
client, while still allowing them to import files located on the
server. Use this option and set it to 0 to disable importing
files local to the client. By default this is set to 1.--max-allowed-packet=value
See the Performance optimization" section later in
this chapter.--max-connect-errors=value
If the client has problems connecting and the number of
attempts exceeds the value of the MySQL variable
max_connect_errors (10 by default), the host
address for the client will be blocked from further attempts.
Use this option to change that value of that variable. To reset
blocked hosts, run the FLUSH HOSTS statement
on the server.--max-connections=value
Clients are not permitted to have more connections than
the number specified by the variable
max_connections. By default it's either 100
or 150, depending on your version. Use this option to change
that value.--max-user-connections=value
This option limits the number of connections per user
account. Set the value to 0 to disable the limit and thereby
allow a single user to create as many connections as MySQL and
the operating system allow.--net-buffer-length=value
Memory is allocated by MySQL for each thread's connection
and results. The amount initially allocated for each of these
buffers is controlled by the variable
net_buffer_length. You can use this option to
change the value, but you normally shouldn't. Each buffer can
expand as needed until it reaches the limit specified in
max_allowed_packet, but when each thread
finishes its work, the buffers contract again to their initial
sizes.--net-read-timeout=value
This option sets the number of seconds the server will
wait for a response from the client while reading from it before
terminating the connection. Use --net-write-timeout to set the
amount of time the server should wait when writing to a client
before terminating. The timeouts apply only to TCP/IP
connections and not to connections made through a socket file, a
named pipe, or shared memory.--net-retry-count=value
If the connection to the client is interrupted while the
server is reading, the server will try to reestablish the
connection a number of times. That number can be set with this
option.--net-write-timeout=value
This option sets the number of seconds the server will
wait for a response from the client while writing to it before
terminating the connection. Use
--net-read-timeout to set the amount of time
the server should wait when reading from a client before
terminating. The timeouts apply only to TCP/IP connections and
not to connections made through a socket file, a named pipe, or
shared memory.--old-passwords
This option permits clients to continue to use passwords
that were created before version 4.1 of MySQL, along with the
old, less secure encryption method in use in earlier
versions.--old-protocol, -o
This option has the server use version 3.20 protocol of
MySQL for compatibility with older clients.--old-style-user-limits
Prior to version 5.0.3 of MySQL, user resource limits were
based on each combination of user and host. Since then, user
resources are counted based on the user regardless of the host.
To continue to count resources based on the old method, use this
option.--one-thread
This option instructs the server to run only one thread,
which is needed when debugging a Linux system using older
versions of the gdb debugger.--port=port, -P
portThis option specifies the port on which the server will
listen for client connections. By default, MySQL uses port 3306.
However, if you want to use a separate port, you may specify one
with this option. This feature can be useful if you are running
more than one instance of MySQL on your server. For example, you
might use port 3306 for your regular MySQL server and port 3307
for a particular department's databases, as well as 3308 for
testing a new version of MySQL.--port-open-timeout=value
As of version 5.1.5 of MySQL, this option may be used to
set the number of seconds the server should wait for a TCP/IP
port to become available. This usually comes into play when the
server has been restarted.--safe-show-database
This option hides database names that a user does not have
permission to access.--safe-user-create
This option prevents a user from creating new users
without the INSERT privilege for the
user table in the mysql
database.--secure
This option enables reverse host lookup of IP addresses,
which provides some defense against spoofing domain names but
adds overhead to each remote connection.--secure-auth
This option prevents authentication of users with
passwords created prior to version 4.1 of MySQL.--secure-file-priv=path
See the Location" section earlier
in this chapter.--skip-automatic-sp-privileges
This option disables the
--automatic-sp-privileges option, which is
related to users automatically being granted ALTER
ROUTINE and EXECUTE privileges on
stored procedures that they create.--skip-character-set-client-handshake
This option disables the
--character-set-client-handshake
option.--skip-grant-tables
This option instructs the server not to use the grants
table and thus give all users full access. This option presents
a security risk. It may be used if the root
password is lost so that you may log in without it and then
reset the password. Restart the server without this option or
run the FLUSH PRIVILEGES statement from the
monitor to reenable privileges.--skip-host-cache
This option disables the use of the internal host cache,
which requires a DNS lookup for each new connection.--skip-name-resolve
This option requires a client's IP address to be named in
the privileges tables for tighter security and faster
connections.--skip-networking
This option prevents network connections of clients and
allows only local connections.--skip-show-database
This option prevents the SHOW DATABASES
statement from being executed by users without the specific
privilege.--skip-ssl
This option specifies that an SSL connection should not be
used, if SSL is enabled by default.--ssl
This option specifies the use of SSL-protected
connections. It requires the server to be SSL-enabled. If this
option is enabled on the utility by default, use
--skip-ssl to disable it.--ssl-ca=pem_file
This option specifies the file (i.e., the
pem file) that provides a list of trusted
SSL CAs.--ssl-capath=path
This option specifies a directory of files that provide
trusted SSL certificates (i.e., pem
files).--ssl-cert=filename
This option specifies the SSL certificate file for SSL
connections.--ssl-cipher=ciphers
This option gives a list of ciphers that may be used for
SSL encryption.--ssl-key=filename
This option specifies the SSL key file for secure
connections.--ssl-verify-server-cert
This option has the client verify its certificate with the
server during an SSL connection. It is available as of version
5.1.11 of MySQL.--standalone
If MySQL is running Windows NT, this option instructs the
server not to run as a service.--thread-handling={one-thread|one-thread-per-connection}
This option specifies the thread handling model that the
server is to use. The one-thread option is
basically used for debugging;
one-thread-per-connection is the default.
This option is available as of version 5.1.17 of MySQL.--user=user, -u
userThis option instructs the client to access MySQL under a
username different from the current system user.
Global
Following is a list of global server options related to the server's
behavior:
--ansi, -a
This option instructs the server to use standard American
National Standards Institute (ANSI) SQL syntax instead of MySQL
syntax.--auto-increment-increment[=value]
This option and the
--auto-increment-offset option are used when
replicating a master to a master server. They determine the
amount by which an AUTO_INCREMENT column is
increased with each new row inserted into any table in the
system. By default, the variable associated with this option is
set to 1. Each can be set to a value from 1 to 65535. If either
option is set to 0, they both will be set back to 1. If either
is set to a non-integer value, it will remain unchanged. If
either is set to a negative value or a value in excess of 65535,
they both will then be set to 65535. Don't use these options
with MySQL Cluster, as they cause problems.--auto-increment-offset[=value]
This option sets the starting number for
AUTO_INCREMENT columns on all tables on the
server. Each successive row inserted into tables will be
incremented by the value of the
auto-increment-increment system variable. If
that variable is set to a number lower than the value set by
this option, the value of the
auto-increment-offset system variable (set by
this option) will be ignored. See the description of the
--auto-increment-increment option
previously for more restrictions on this option.--character-set-server=set,
-CThis option makes the server use a particular character
set by default for its calculations. It's available as of
version 4.1.3 of MySQL.--character-set-filesystem=value
This option specifies the character set that the
filesystem uses. It was added in version 5.1.6 of MySQL.--completion-type=[=0|1|2]
The SQL statements COMMIT and
ROLLBACK support an optional AND
CHAIN parameter that automatically begins a new
transaction at the same isolation level after the end of the
transaction completed by these statements. If this option is set
to 1, this chaining effect will be the default setting for those
SQL statements. Similarly, if this option is set to 2, the
default setting for the statements will be
RELEASE, which causes the server to
disconnect after each transaction is terminated. A value of 0,
which is the default, does nothing.--console
On Windows systems, this option has the server display
error messages to stdout and
std.err even if
--log-error is enabled.--core-file
This option instructs the server to create a core file if
the daemon dies. Some systems require the
--skip-stack-trace option to be set as well.
Some systems also require the
--core-file-size option when using
mysqld_safe. On Solaris systems, if the
--user option is used
also, the server will not create the core file.--date-format=value
The variable associated with this option is not yet
implemented. It's expected to be used to set the default date
format for the MySQL server.--datetime-format=value
The variable associated with this option is not yet
implemented. It's expected to be used to set the default
datetime format for the MySQL server.--default-week-format=value
The variable associated with this option is not yet
implemented. It's expected to be used to set the default format
for the days of the week on the MySQL server.--debug[=options],
-# options]This option is used to get a trace file of the daemon's
activities. The debug options are typically
d:t:o,filename.
See Table 16-1 at the end of the
list of options for the mysqldump utility
later in this chapter for an explanation of these flags and
others that may be used. MySQL has to be compiled for debugging
using the --with-debug option when
configuring.--default-character-set=character_set
This option is used to specify the default character set.
This option is deprecated as of version 4.1.3 of MySQL. Use the
--character-set-server option instead.--default-collation=collation
This option specifies the collation to use as the default.
This option is deprecated as of version 4.1.3 of MySQL. Use the
--collation-server option instead.--default-time-zone=zone
This option specifies the default time zone for the
server. The filesystem time zone is used by default.--div-precision-increment=value
This option sets the number of decimal places to show in
the results of dividing numbers. The variable associated with
this option (div_precision_increment) has a
default value of 4. You can set it from 0 to 30.--enable-pstack
This option instructs the server to print a symbolic stack
trace if the server fails and exits.--exit-info[=flags],
-T [flags]This option displays debugging information when the server
exits.--external-locking
This option allows system locking. Be careful when using
it on a platform with problems with lockd,
such as Linux, because the mysqld daemon
may deadlock and require rebooting the server to unlock it. This
option was previously called --enable-locking.--flush
This option flushes all changes to disk after each SQL
statement instead of waiting for the filesystem to do the writes
at regular intervals.--flush-time=seconds
This option sets the flush_time
variable, which specifies the number of seconds a table can
remain open before it's closed and flushed to free resources and
to synchronize data. For current operating systems, this option
shouldn't be used because it will slow the server. A value of 0
disables it and is the default.--gdb
This option is recommended when debugging the MySQL
daemon. It enables a handler for SIGINT,
which is necessary for the server daemon to be stopped with
Ctrl-C at debugging breakpoints. It also disables core file
handling as well as stack tracing.--group-concat-max-len=value
This option sets the maximum length of a value created by
the GROUP_CONCAT( ) function.--language=[language|pathname]
This option specifies the language the daemon should use
to display messages. It can be the name of a language or a
pathname to the language file.--lower-case-table-names[=0|1|2]
If this option is set to 1, database and table names will
be saved in lowercase letters on the server, and MySQL will not
consider case when given database and table names. A value of 2
causes databases and tables to be stored on the filesystem in
filenames with uppercase and lowercase based on what it is given
when they are created. However, they will be treated as
lowercase. A value of 0 disables these features, but you
shouldn't set it to 0 if using a case-insensitive filesystem,
such as Windows.--max-error-count=value
When errors, warnings, and notes are generated, they are
stored by the server to be displayed when the SHOW
ERRORS or SHOW WARNINGS statements
are executed. This option limits the number of messages that
will be stored. The default value is 64.--max-join-size=value
This option sets the maximum number of rows in a join. By
default, this option is set very high. You may want to lower it
if you suspect abuse from users. To reset it to the default
value, enter a value of DEFAULT. If you set
this option to any other value, it causes the system variable
SQL_BIG_SELECTS to be set to 0. If the
SQL_BIG_SELECTS variable is then set to
another value, this option's setting is ignored.--max_length_for_sort_data=value
This option sets the maximum size of data that can be
sorted with the ORDER BY clause.--max_prepared_stmt_count=value
This option sets the maximum number of prepared statements
allowed on the server. Values from 0 to 1000000 (one million)
are accepted; the default is 16382. If you set the value lower
than the current number of prepared statements, existing ones
will be unaffected. But when they are removed, new ones cannot
be added until the total count falls below the value given with
this option. This option is available as of version 5.1.10 of
MySQL.--new, -n
At the time of this writing, this option is used to test
queries before upgrading from version 4.0 to 4.1.--open_files_limit=value
This option specifies the maximum number of files the
daemon can keep open, which may require it to close tables more
often than is optimal.--help, -?
This option displays basic help information. It displays
more information when combined with the
--verbose option.--read_only
If this option is used, users cannot add, change, or
delete data on the server, unless they have
SUPER privileges. The other exception is that
updates from slave threads are allowed. This option does not
carry to the slaves. It can be set on slaves independently from
the master and may be useful to keep slaves synchronized
properly.--safe-mode=value
This option disables some optimizations at startup.
--set-variable variable
= value, -0
variable =
valueThis option sets a server variable. Enter mysqld
--verbose --help to see the current values for
particular server variables.--skip-external-locking
Previously called --skip-locking, this
option prevents system locking.--skip-locking
This option disables system locking of the server.
--skip-new
This option instructs the server not to use new
options—i.e., options that are enabled by default but are still
in beta testing mode.--sql-mode=value
This option covers a number of possible ways of
interpreting SQL statements, mostly for compatibility with other
database engines. Multiple values may be given in a
comma-separated list.--sql_auto_is_null={0|1}
If you enable this option by setting it to 1, you can give
the name of a column that uses AUTO_INCREMENT
in WHERE clauses with a condition of NULL to
find the last inserted row. For example, SELECT...WHERE
client_id IS NULL; will return the row that was last
inserted into a table where client_id is the
primary key. A value of 0 for this option will disable it. The
option is useful when interfacing with ODBC applications (e.g.,
MS Access).--sql_big_selects={0|1}
Disable (set to 0) this option to prevent large
SELECT statements from being executed. Large
statements are defined as joins whose results would exceed the
maximum number of rows set by the
--max_join_size option. The default value of
1 enables large SQL statements. Setting the
--max_join_size option to something other
than DEFAULT will reset this option back to
0.--sql_buffer_result={0|1}
If this option is set to 1, the results of
SELECT statements will be sent to a buffer
before being returned to the client. This slows the results, but
unlocks the associated tables faster for the use of other
clients. The default setting of 0 disables this option.--sql-safe-updates={0|1}
This option, when set to 1, is useful in helping to
prevent inadvertent deletion of multiple and possibly all rows
in a table. It requires that DELETE and
UPDATE statements contain a
WHERE clause with a key column and value. The
default value of 0 disables the option.--sql_select_limit={value|DEFAULT}
This option limits the number of rows returned from a
SELECT statement when the
LIMIT clause hasn't been given. The value of
DEFAULT means that there is no limit.--sysdate-is-now
The SYSDATE( ) function returns
the date and time in which the function was executed by MySQL
within an SQL statement. It doesn't return the time that the SQL
statement started, as the NOW( )
function does. If you want SYSDATE( ) to
return the same time as NOW( ), use this
option. See the description of
SYSDATE( ) in Chapter 12 for an example.--tc-heuristic-recover={COMMIT|ROLLBACK}
This option is not yet implemented by MySQL. It will
relate to the heuristic recovery process when it is
implemented.--time_format=value
The variable associated with this option is not yet
implemented. It's expected to be used to set the default time
format for the MySQL server.--transaction-isolation=option
This option sets the default transaction isolation level.
The available levels are READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE.--updatable_views_with_limit={0|1}
Set this option to 1 to prevent updates to views that do
not contain all of the columns of the primary key of the
underlying table; the option applies only when the SQL statement
contains a LIMIT clause. If set to the
default value of 1, only a warning is returned and the update is
not prevented.--version
This option displays the version of MySQL that is running
on the server.--version_compile_machine
This option displays the type of machine on which MySQL
was compiled.--version_compile_os
This option displays the type of operating system on which
MySQL was compiled.
Logs
These mysqld server options relate to general logs created by MySQL. For
storage engine specific logs, see the Storage engine specific options" section later in this chapter.
--binlog-do-db=value
This option limits the binary log to entries created by
SQL statements executed against the database given, and only
when it is the default database. If the user sets the default
database to another database, but executes SQL statements
affecting the database given with this option, those statements
will not be written to the binary log. Additional databases may
be specified with multiple instances of this option. Despite
this option, though, ALTER DATABASE,
CREATE DATABASE, and DROP
DATABASE statements for the given database will be
logged regardless of the default database setting.--binlog-ignore-db=value
This option omits entries from the binary log for SQL
statements executed against the database given, but only when it
is the default database. So when the user sets the default
database to another database, but executes SQL statements
affecting the database given with this option, those statements
will be written to the binary log. Additional databases may be
specified with multiple instances of this option. Despite this
option, though, ALTER DATABASE,
CREATE DATABASE, and DROP
DATABASE statements for the given database will be
logged regardless of the default database setting.--log[=filename],
-l [filename]This option instructs the server to log connection
information and queries to the given file, or to the default
(host.log) if none is given.--log-bin[=filename]
This option records database changes to a binary log to
the filename given. If a filename isn't provided, the default
name of
host-bin.index
will be used, where host is the
hostname of the server and index is a
numeric count.--log-bin-trust-function-creators[={0|1}]
By default, if binary logging is enabled, when creating a
stored procedure you have to state whether the function is
deterministic and whether it will modify data. If this option is
specified without a value or with a value of 1, this requirement
is disabled. If set to 0, which is the default setting, the
requirement is enabled.--log-error[=filename]
This option activates logging of error messages and server
startup messages to the filename given. The default name for the
log if none is specified is
host.err, where
host is the server's hostname.--log-long-format,
-0This option instructs the server to be more verbose in
logs. This is the default setting as of version 4.1 of MySQL.
Use the --log-short-format option to disable
this option.--log-short-format
This option instructs the server to be less verbose in
logs. It is available as of version 4.1 of MySQL.--log-queries-not-using-indexes
See Performance optimization" later in this
chapter.--log-slave-updates
This option is used on a slave server to instruct it to
write to its own binary log any updates to data made from SQL
threads. The option requires that the
--log-bin option be used on the slave. With
this method, it's possible to have a slave act as master to a
slave under it.--log-slow-admin-statements
See Performance optimization."
--log-slow-queries[=filename]
See Performance optimization."
--log-tc=filename
This option specifies the filename of the memory-mapped
transaction coordinator log. The default filename is
tc.log, located in the data directory for
MySQL.--log-tc-size=size
This option specifies the size of the memory-mapped
transaction coordinator log. The default is 24 KB.--log-update[=filename]
Activates logging of updates to the filename given. This
feature is deprecated in favor of binary logging.--log-warnings,
-WThis option activates logging of warning messages. Prior
to version 4.0 of MySQL, this option was invoked with the
--warnings option. After version 4.1.2, this
option is enabled by default and can be disabled with the
--skip-log-warnings option.--long_query_time=value
See Performance optimization."
--max-binlog-dump-events
This option is used by the MySQL test suite for testing
and debugging replication.--relay-log=filename
See Replication" later in this
chapter.--relay-log-index=filename
See Replication" later in this
chapter.--relay-log-info-file=filename
See Replication" later in this
chapter.--relay-log-purge[={0|1}]
See Replication" later in this
chapter.--relay-log-space-limit=value
See Replication" later in this
chapter.--skip-log-warnings
This option disables the --log-warnings
feature so that warning messages are not logged.--skip-stack-trace
This option prevents the writing of stack traces.
--slow-query-log[={0|1}]
See Performance optimization."
--slow-query-log-file=filename
See Performance optimization."
--sporadic-binlog-dump-fail
This option is used by the MySQL test suite for testing
and debugging replication.--sql_log_bin={0|1}
The default value of 1 for this option has clients log to
the binary log. A value of 0 disables it.--sql_log_off={0|1}
The default value of 0 for this option has clients log to
the general query log. A value of 1 disables it and general
logging is not done for the client.--sql_notes={0|1}
If this option is set to the default of 1, note-level
warning messages are logged. A value of 0 disables it.--sql_warnings={0|1}
If this option is set to 1, warning messages for single
row INSERT statements generate an information
string. The default value of 0 disables it.--sql_quote_show_create={0|1}
If this option is set to the default of 1, identifiers in
statements will be quoted in the logs. This can be necessary for
certain slave servers that may require identifiers to be
contained within quotes. A value of 0 disables it.--sync_binlog={0|1}
If this option is set to a value of 1, the server will
synchronize every write to the binary log to the disk. The
default value of 0 disables this feature.
Performance optimization
These mysqld server options relate to improving server performance. Before
changing a server's setting, you should make note of its current
setting, and then use the BENCHMARK( ) function to determine
performance before changes are made. After implementing the new server
setting, run the BENCHMARK( ) function again
to compare the results. This is just one of many ways in which you
might test a server's performance before and after making changes to
its settings. The important thing is not to assume that a particular
setting will improve performance and to be aware that a change could
cause other problems. Test and monitor changes to be sure. For
performance options that are specific to InnoDB, see the InnoDB" subsection of the Storage engine specific options" section.
--big-tables
This option instructs the server to save temporary results
sets to a file to solve problems where results are large and
error messages indicate that tables are full.--bulk_insert_buffer_size=value
When bulk inserting data into an existing table that
already contains data, the MyISAM storage engine uses a
special buffer to make the process faster. You can use this
option to set the size of that buffer to improve performance.
The default value is 8 MB. A value of 0 disables the
buffer.--concurrent-insert[={0|1|2}]
If this option is set to its default of 1, the MyISAM
storage engine will allow simultaneous inserting and selecting
of data, but only if there are no free spaces on the filesystem
within the datafile. A setting of 2 for this option allows
concurrent reading and writing despite spaces in the datafile.
It just writes the new rows to the end of the datafile if reads
are occurring while the server is trying to write. If no
concurrent reads are taking place, the server will get a write
lock on the table and make use of the blank space. A value of 0
for this option disables concurrent inserting and reading.--delayed_insert_limit=value
If an INSERT statement is entered with
the DELAYED parameter, the server delays
entering rows if there are SELECT statements
already running against the table. When the table is free, the
server will then insert the delayed rows. This option causes the
server to enter a fixed number of rows before rechecking to see
whether new SELECT statements are queued. If
there are, it will delay the inserts again.--delayed_insert_timeout=value
When an INSERT statement has been
issued with the DELAYED parameter, the server
will wait for the outstanding SELECT
statements against the table to finish running before executing
it. Use this option to set the number of seconds that the server
should wait before terminating the INSERT
statement.--delay-key-write[=option]
This option instructs the server how to handle key buffers
between writes for MyISAM
tables. The choices are OFF,
ON, and ALL. The
ON choice delays writes for tables created
with DELAYED KEYS. The ALL
choice delays writes for all MyISAM tables. MyISAM tables should
not be accessed by another server or clients such as myisamcheck when the
ALL choice is used; it may cause corruption
of indexes.--delay-key-write-for-all-tables
This option instructs the server not to flush key buffers
between writes for MyISAM tables. As of version 4.0.3 of MySQL,
use --delay-key-write=ALL instead.--delayed_queue_size=value
When an INSERT statement has been
entered with the DELAYED parameter, the
server will wait for the outstanding SELECT
statements against the table to finish running before executing
it. Use this option to set the maximum number of rows that the
server should queue from inserts. Any additional rows will not
be queued, and the INSERT statements will
have to wait until the queue is reduced.--join_buffer_size=value
This option sets the size of the buffer file to use for
joins in which an index is not used. The maximum value for this
option is 4 GB, but on 64-bit operating systems, as of version
5.1.23, a larger buffer size may be possible.--key_buffer_size=value
This option sets the key cache size. This is a buffer used
by MyISAM tables for index blocks. The maximum value for this
option is 4 GB, but on 64-bit operating systems, as of version
5.1.23, a larger buffer size may be possible. Execute the
SHOW STATUS statement on the server to see
the settings for the key cache.--key_cache_age_threshold=value
This option sets the point at which a buffer will be
switched from what is known as a hot subchain in the key cache
to a warm one. Lower values cause the switching to occur faster.
The default value is 300. The lowest value allowed is
100.--key_cache_block_size=value
This option sets the size of blocks in the key cache. The
values are in bytes. The default is 1024.--key_cache_division_limit=value
This option sets the division point between hot and warm
subchains in the key cache. The value given represents a
percentage of the whole buffer. The default value is 100. A
value of 1 to 100 is allowed.--large-pages
This option enables large pages in memory.
--log-slow-admin-statements
If this option is enabled, administrative SQL statements
that take too long to execute will be logged. These include
statements such as ALTER TABLE,
CHECK TABLE, and OPTIMIZE
TABLE.--log-slow-queries[=filename]
This option instructs the server to log queries that take
longer than the number of seconds specified in the value of the
long_query_time variable. If
filename is specified, entries are
recorded in the log file named.--log-queries-not-using-indexes
When used with the --log-slow-queries
option, this option causes all queries that do not use indexes
to be logged to the slow query log. It is available as of
version 4.1 of MySQL.--long_query_time=value
This option sets the number of seconds that a query can
take to execute before it's considered a slow query. If the
--log-slow-queries option is in use, queries
that exceed the number of seconds set by this option will be
logged.--low-priority-updates
This option sets all SQL statements that modify data to a
lower priority than SELECT statements, by
default.--max_allowed_packet=value
This option sets the maximum size of a packet or a
generated string. If using BLOB or
TEXT columns, the variable associated with
this option should be at least as large as the largest entry for
the column. To determine this, you can execute SHOW
TABLE STATUS LIKE
'table';
on the server and look for the
Max_data_length field. The maximum size
allowed for this option is 1 GB. The
--net_buffer_length option sets the initial
size of buffer packets.--max_delayed_threads=value
This option sets the maximum number of threads the server
can use to handle delayed inserts. See the
--delayed_insert_limit and
--delayed_insert_timeout options earlier in
this chapter for more information.--max_seeks_for_key=value
When MySQL searches a table for data based on a
WHERE clause using an index, it expects to
have to search a certain number of rows in the index. You can
adjust this expectation with this option. A lower value causes
the MySQL optimizer to give preference to indexes over table
scans.--max_sort_length=value
This option sets the maximum number of bytes the server
can examine in each field when sorting BLOB
or TEXT columns. Any bytes of data beyond the
value set for this option are ignored in sorting. The default is
1024.--max_sp_recursion_depth[=value]
This option sets the maximum depth to which a stored
procedure can invoke itself. The default is 0, which disables
all recursion, and the maximum depth allowed is 255.--max_tmp_tables=value
This is a new option that has not yet been implemented.
When it is, you will be able to use it to limit the number of
temporary tables that a client can have open at one time.--max_write_lock_count=value
This option limits the number of write locks that may be
made without allowing reads to be performed.--multi_range_count=value
This option sets the maximum number of ranges that may be
sent to a table handler at one time for a range select. The
default is 256.--memlock
This option is used on filesystems that support
mlockall( ) system calls (e.g., Solaris)
to lock the daemon in memory and thereby avoid the use of disk
swapping in an attempt to improve performance. Requires the
daemon to be started by root, which may be
a security problem.--optimizer_prune_level[={0|1}]
This option sets the behavior of the optimizer when it
tries to reduce or remove plans that don't seem to be useful. A
value of 0 disables heuristics and instructs the optimizer to
search as much as possible. The default value of 1 enables
heuristics and thereby instructs the optimizer to prune
plans.--optimizer_search_depth[=value]
This option sets the maximum depth of searches performed
by the query optimizer. A lower number will make for better
queries, but it will take longer to perform. A higher number
should make queries faster. If the value is set to 0, the server
will attempt to decide on the best setting.--preload_buffer_size=value
This option sets the size of the buffer used to hold
preloaded indexes. The default is 32768 (32 KB).--query_alloc_block_size=value
This option sets the size of memory blocks that are
allocated for use in parsing and executing a statement.--query_cache_limit=value
This option sets the maximum size of the query cache in
bytes. The default is 1 MB.--query_cache_min_res_unit=value
This option sets the minimum size in bytes of blocks used
for the query cache. The default is 4096 (4 KB).--query_cache_size=value
This option sets the maximum size in bytes of the cache
used for query results. The default is 0. Values should be given
in multiples of 1024 (1 KB).--query_cache_type={0|1|2}
This option sets the type of query cache to use on the
server. A value of 0 causes the query cache not to be used. The
default value of 1 causes all queries to be cached except
SELECT statements that include the
SQL_NO_CACHE parameter. A value of 2 means
that no queries will be cached except SELECT
statements that include the SQL_CACHE parameter.--query_cache_wlock_invalidate[={0|1}]
If a table is locked, but the results of querying the same
table are already contained in the query cache, the results of a
query will be returned if this option is set to 0, the default.
Setting it to 1 will disable this feature and users will have to
wait for the write lock to be released before reading the table
and the related query cache data.--query_prealloc_size=value
This option sets the size of the persistent buffer used
for parsing and executing statements.--range_alloc_block_size=value
This option sets the size of blocks of memory allocated
for range queries.--read_buffer_size=value
This option sets the size in bytes of the buffer to use
for each thread when doing sequential scans. The default value
is 131072; the maximum is 2 GB.--read_rnd_buffer_size=value
Rows that are sorted by an index are read into a buffer to
minimize disk activity. You can set the size of this buffer with
this option to a maximum of 2 GB.--safemalloc-mem-limit=value
This option is used to simulate a memory shortage when the
server has been compiled with the
--with-debug=full option.--shared-memory
This option allows shared memory connections by Windows
clients locally. It is available as of version 4.1 of
MySQL.--shared-memory-base-name=name
This option sets the name to use for shared memory
connections in Windows. It is available as of version 4.1 of
MySQL.--skip-concurrent-insert
This option prevents simultaneous
SELECT and INSERT
statements for MyISAM
tables.--skip-delay-key-write
This option disregards tables marked as
DELAY_KEY_WRITE. As of version 4.0.3 of
MySQL, use --delay-key-write=OFF
instead.--skip-safemalloc
This option prevents the server from checking for memory
overruns when performing memory allocation and memory freeing
activities.--skip-thread-priority
This option prevents prioritizing of threads.
--slow-query-log[={0|1}]
Slow queries are ones that take more than the number of
seconds set by the --long_query_time option. A value
of 1 for this option enables the logging of slow queries; the
default value of 0 disables it. This option is available as of
version 5.1.12 of MySQL.--slow-query-log-file=filename
This option sets the name of the slow query log file. By
default it's
host_name-slow.log.
This option is available as of version 5.1.12 of MySQL.--slow_launch_time
This option causes a thread's
Slow_launch_threads status to be updated to
reflect whether a thread takes too long to launch.--sort_buffer_size=value
This option sets the size of the buffer each thread should
use when sorting data for a query. The maximum value for this
option is 4 GB, but on 64-bit operating systems, as of version
5.1.23, a larger buffer size may be possible.--table_lock_wait_timeout=value
This option sets the number of seconds that the server
should wait to get a table lock before it terminates and returns
an error. The timeout is related only to connections with active
cursors. The default value is 50.--table_open_cache=value
This option sets the maximum number of open tables allowed
for all threads. Prior to version 5.1.3, this option was called
--table_cache. Executing the FLUSH
TABLES statement will close any open tables and reopen
any in use.--thread_cache_size=value
With this option, you can set the number of threads that
the server should cache for reuse. This may lead to quicker
connection times for new connections that are made by
clients.--thread_concurrency=value
The value of the variable associated with this option is
used by applications to provide a hint regarding the number of
threads that the server should run concurrently. It's used on
Solaris systems in conjunction with the
thr_setconcurrency( ) system
function.--thread_stack=value
This option sets the size of the stack for each thread.
The default value is 192 KB.--tmp_table_size=value
This option sets the maximum size of internal, in-memory
temporary tables. This option is not related to MEMORY tables,
though.--transaction_alloc_block_size=value
The memory pool described under the
--transaction_prealloc_size option is
increased as needed in increments. The amount of increments is
drawn from the value of the
transaction_alloc_block_size server variable.
This option can be used to change that variable.--transaction_prealloc_size=value
A memory pool is used to temporarily store activities
related to transactions. The size of that pool expands as
needed. Initially, it is set to the size of the value of the
server variable transaction_prealloc_size.
This option can be used to set that variable higher to improve
performance.--wait_timeout=value
This option sets the number of seconds that the server
will wait before terminating a nonresponsive connection based on
TCP/IP or a socket file. This option is not associated with
connections through named pipes or shared memory.
Replication
An alphabetical list follows of mysqld server options
related to replication. Many also appear earlier in Chapter 8. Although these options can be set at the
command line when starting the server, and some can also be set with
SQL statements while the server is running, as a general policy the
options should be given in the server's options file (e.g.,
my.cnf or my.ini, depending
on your system). Otherwise, there's a chance that the options may be
lost when the server is restarted, in which case replication may fail
or at least not function as you want:
--abort-slave-event-count=value
This option is used by the MySQL test suite for testing
and debugging replication.--disconnect-slave-event-count=value
This option is used by the MySQL test suite for testing
and debugging replication.--init_slave='string'
Use this option on the server to specify one or more SQL
statements, all combined in a single
string, that are to be executed by
the slave each time its SQL thread starts.--log-slave-updates
This option is used on a slave server to instruct it to
write to its own binary log any updates to data made from SQL
threads. It requires that the --log-bin
option be used on the slave. With this method it's possible to
have a slave act as master to a slave under it.--master-connect-retry=seconds
This option sets the number of seconds that a slave thread
may sleep before trying to reconnect to the master. The default
is 60 seconds. This value is also included in the master.info file. If that file
exists and is accessible, the value contained in it will
override this option.--master-host=host
This option is superseded by the same information in the
master.info file and is necessary for
replication. It that file doesn't exist or is inaccessible, this
option may be used to set the hostname or IP address of the
master server.--master-info-file=filename
This option sets the name of the master information file.
This file is described in detail in Chapter 8 in Section 8.2." By default this file is
named master.info and
is located in the data directory of MySQL.--master-password=password
If the master.info file doesn't exist
or is inaccessible, this option may be used to set the password
used by the slave thread for accessing the master server.--master-port=port
This option sets the port number on which the master will
listen for replication. By default it's 3306. The value for this
variable in the master.info file, if
available, will override this option.--master-retry-count=value
This option specifies the number of times the slave should
try to connect to the master if attempts fail. The default value
is 86400. The interval between retries is set by the option
--master-connect-retry.
Retries are initiated when the slave connection times out for
the amount of time set with the
--slave-net-timeout option.--master-ssl
This option is similar to --ssl in the
Security and connections" section earlier in this
chapter, but it applies to a slave's SSL connection with the
master server.--master-ssl-ca[=value]
This option is similar to --ssl-ca in
the Security and connections" section earlier in this
chapter, but it applies to a slave's SSL connection with the
master server.--master-ssl-capath[=value]
This option is similar to --ssl-capath
in the Security and connections" section earlier in this
chapter, but it applies to a slave's SSL connection with the
master server.--master-ssl-cert[=value]
This option is similar to --ssl-cert in
the Security and connections" section earlier in this
chapter, but it applies to a slave's SSL connection with the
master server.--master-ssl-cipher[=value]
This option is similar to --ssl-cipher
in the Security and connections" section earlier in this
chapter, but it applies to a slave's SSL connection with the
master server.--master-ssl-key[=value]
This option is similar to --ssl-key in
the Security and connections" section earlier in this
chapter, but it applies to a slave's SSL connection with the
master server.--master-user=value
This option sets the name of the user account that the
slave thread uses to connect to the master server for
replication. The user given must have the REPLICATION
SLAVE privilege on the master. This option is
overridden by the master.info file.--max-binlog-dump-events=value
This option is used by the MySQL test suite for testing
and debugging replication.--read_only
This option prevents users from adding, changing, or
deleting data on the server, except for users with
SUPER privileges. The other exception is that
updates from slave threads are allowed. This option does not
carry over from a master to its slaves. It can be set on slaves
independently from the master and may be useful to do so to keep
slaves synchronized properly.--relay-log=filename
This option sets the root name of the relay log file. By
default it's
slave_host_name-relay-bin.
MySQL will rotate the log files and append a suffix to the file
name given with this option. The suffix is generally a seven
digit number, counting from 0000001.--relay-log-index=filename
This option sets the name of the relay log index file. By
default it's slave_host_name-relay-bin.index.--relay-log-info-file=filename
This option sets the name of the file that the slave will
use to record information related to the relay log. By default
it's relay-log.info and is located in the
data directory of MySQL.--relay_log_purge[={0|1}]
This option is used to make the server automatically purge
relay logs when it determines they are no longer necessary. The
default value of 1 enables it; a value of 0 disables it.--replicate-do-db=database
This option tells the slave thread to limit replication to
SQL statements executed against the database given, and only
when it is the default database. When the user sets the default
database to another database, but executes SQL statements
affecting the database given with this option, those statements
will not be replicated. Additional databases may be specified
with multiple instances of this option.--replicate-do-table=database.table
This option tells the slave thread to limit replication to
SQL statements executed against the table given. Additional
tables may be specified with multiple instances of this
option.--replicate-ignore-db=database
This option skips replication for SQL statements executed
against the database given, but only when it is the default
database. So when the user sets the default database to another
database, but executes SQL statements affecting the database
given with this option, those statements will be replicated.
Additional databases may be specified with multiple instances of
this option.--replicate-ignore-table=database.table
This option omits replication of SQL statements executed
against the table given. Additional tables may be specified with
multiple instances of this option.--replicate-rewrite-db='filename->filename'
This option tells the slave to change the database with
the first name to have the second name (the name after the
->), but only when the default database on
the master is set to the first database.--replicate-same-server-id[={0|1}]
If this option is set to 1, entries in the binary log with
the same server-id as the slave will be
replicated. This can potentially cause an infinite loop of
replication, so it shouldn't be implemented unless necessary and
then only for a limited time and purpose. This option is set to
0 by default and is used on the slave server. The option is
ignored if --log-slave-updates is
enabled.--replicate-wild-do-table=database.table
This option is similar to
--replicate-do-table except that you may give
wildcards (% or _) for the
database and table names. For instance, to match all tables that
start with the name clients, you would give
a value of clients%. To literally give a
percent sign or an underscore, escape the character with a
preceding backslash (i.e., \% and \_). Additional tables may be
specified with multiple instances of this option.--replicate-wild-ignore-table=database.table
This option is similar to
--replicate-ignore-table except that you may
give wildcards (% or _)
for the database and table names. For instance, to match all
tables that start with the name clients,
you would give a value of clients%. To
literally give a percent sign or an underscore, escape the
character with a preceding backslash (i.e.,
\% and \_). Additional
tables may be specified with multiple instances of this
option.--report-host=host
Because the master cannot always ascertain the slave's
hostname or IP address, use this option to have the slave
register with the master and report its hostname or IP address.
This information will be returned when SHOW SLAVE
HOSTS is executed on the master.--report-password=value
This option sets the password used by the slave to
register with the master. If the
--show-slave-auth-info option is enabled,
this information will be returned when SHOW SLAVE
HOSTS is executed on the master.--report-port=value
This option sets the port used by the slave to communicate
with the master. It should be employed only when a special port
is being used or if the server has special tunneling
requirements.--report-user=value
This option sets the username used by the slave to
register with the master. If the
--show-slave-auth-info option is enabled,
this information will be returned when SHOW SLAVE
HOSTS is executed on the master.--server-id=value
This option ets the local server's server identifier. It
must be used on the master as well as each slave, must be unique
for each server, and should be set in the options file.--show-slave-auth-info
This option causes the SQL statement SHOW SLAVE
HOSTS to reveal the slave's username and password if
the slave was started with the --report-user
and the --report-password options.--slave_compressed_protocol[={0|1}]
If set to 1, this option instructs the slave to compress
data passed between it and the master, if they support
compression. The default is 0.--slave_load_tmpdir=value
This option specifies the directory where the slave stores
temporary files used by the LOAD DATA INFILE
statement.--slave-net-timeout=value
This option specifies the number of seconds before a slave
connection times out and the slave attempts to reconnect. See
the options --master-connect-retry and
--master-retry-count
earlier in this chapter, as they relate to this option.--slave-skip-errors=error_nbr,...|all
By default, replication stops on the slave when an error
occurs. This option instructs the slave not to terminate
replication for specific errors. Error numbers for the errors
should be given in a comma-separated list. You may specify all
errors by giving the value of all. This
option generally should not be used, and the value of
all in particular should probably never be
used.--sql-slave-skip-counter=number
When the slave begins to re-execute commands that the
master executed, this option causes the slave to skip the first
number events from the master's
log.--skip-slave-start
If this option is enabled, the master server won't
automatically start the slaves when it's restarted. Instead, you
will have to enter the START SLAVE statement
on each slave to start it.--slave_transaction_retries=value
This option specifies the number of times the slave should
try to execute a transaction before returning an error if the
transaction fails because of problems related to InnoDB or NDB
settings. For InnoDB, this applies if there is a deadlock or if
the transaction takes more time than is allowed by innodb_lock_wait_timeout. For
NDB, this applies if the
transaction takes more time than is allowed by TransactionDeadlockDetectionTimeout
or TransactionInactiveTimeout. The
default value of this option is 10.
Storage engine specific options
An alphabetical list follows of mysqld
server options recognized by particular storage engines (formerly
known as table types). The options are grouped into subsections based
on the storage engines: MyISAM," InnoDB," and Other storage engine options," which include MEMORY, MERGE, and NDB
(MySQL Cluster).
Older versions of MySQL offered BDB options that are not covered
in this book because MySQL no longer supports the BDB storage engine.
See the documentation on MySQL's web site for information on BDB
options if you're still using BDB tables. It's recommended that you
migrate those tables to another storage engine. For a list of storage
engines and to see their status on your server, enter SHOW
ENGINES.
Here are a couple of related options that aren't used for a
particular storage engine:
--default-storage-engine=engine
This option specifies the default storage engine. MyISAM
is the default unless changed with this option. The server
variable associated with this option is storage_engine. This option is
synonymous with the --default-table-type
option.--default-table-type=engine
This option is synonymous with
--default-table-engine.
MyISAM
These options are related to the MyISAM storage engine,
which is typically the default storage engine for MySQL. To
determine the default storage engine, enter SHOW VARIABLES
LIKE 'storage_engine'; on the server. You can change the
default storage engine with the
--default-storage-engine option:
--bulk_insert_buffer_size=value
See Performance optimization" later in this
chapter.--ft_boolean_syntax=value
This option sets the operators that may be used for
FULLTEXT searches of TEXT columns in MyISAM
tables. The default operators are: +,
–, >,
<, (,
), ~,
*, :,
"", &, and
|.--ft_max_word_len=value
This option sets the maximum length of a word for which
a FULLTEXT search of a table may be made. After setting this
option, rebuild the FULLTEXT index by executing
REPAIR TABLE
table QUICK; on
the server.--ft_min_word_len=value
Use this option to set the minimum length of a word for
which a FULLTEXT search of a table may be made. After setting
this option, rebuild the FULLTEXT index by executing
REPAIR TABLE table
QUICK; on the server.--ft_query_expansion_limit=value
This option sets the maximum number of matches for
FULLTEXT searches that can be made when using the
WITH QUERY EXPANSION clause.--ft_stopword_file=filename
This option specifies a text file containing stopwords,
which are words not to be considered in FULLTEXT searches.
Comments should not be included in this file, only stopwords.
A list of words is built into MySQL by default.--keep_files_on_create[={0|1}]
If for some reason a file with the prefix
.MYD or .MYI is
located in the data directory of MySQL, but wasn't placed
there by the server, and a new table is created with the same
name as the prefix of the files, MyISAM will overwrite the
files. However, if this option is set to 1, the files won't be
overwritten and an error will be returned instead. This option
was added as of version 5.1.23 of MySQL.--myisam_block_size=value
This option sets the block size in bytes for index pages
in MyISAM.--myisam_data_pointer_size=value
This option sets the default pointer size in bytes for
MyISAM tables when tables are created without the
MAX_ROWS option of the CREATE
TABLE statement. The default value is 6; valid
values range from 2 to 7.--myisam_max_extra_sort_file_size=value
This option is deprecated as of version 5.1 of
MySQL.--myisam_max_sort_file_size=value
This option sets the maximum file size in bytes of the
temporary file used by MyISAM when recreating a table's
index (i.e., when running the ALTER TABLE,
LOAD DATA INFILE, or REPAIR
TABLE statements). Any space in excess of this value
that may be required will be handled in the key cache. The
default value is 2 GB.--myisam-recover[=value,...]
This option sets the MyISAM storage engine's recovery
mode so that all MyISAM tables will be automatically checked
and repaired if needed when the server starts. The choices of
settings are BACKUP (makes backups of
recovered tables that were changed),
DEFAULT (disables this option),
FORCE (runs recovery regardless of the risk
of losing data), or QUICK (doesn't check
rows for tables without any deletions). Multiple choices may
be given in a comma-separated list.--myisam_repair_threads[={0|1}]
With this option enabled, when repairing a table's index
each index will be sorted in its own thread. This will
potentially increase the speed of the repair process. However,
this option is still in beta testing mode. Its default value
is 1, enabling the option.--myisam_sort_buffer_size=value
This option sets the size of the buffer used for sorting
indexes in a MyISAM table. The maximum value for this option
is 4 GB, but on 64-bit operating systems, as of version 5.1.23
a larger buffer size may be possible. The variable associated
with this option is used when the ALTER
TABLE, CREATE INDEX, or
REPAIR TABLE statements are
executed.--myisam_stats_method={nulls_equal|nulls_unequal}
When aggregate or statistical functions are used, MyISAM
has to decide how to treat NULL values for indexes. If this
option is set to nulls_equal, all NULL
values will be considered equal and their associated columns
will be grouped together. If nulls_unequal
is given, each row will be considered a separate and distinct
value and they won't be grouped together.--myisam_use_mmap
This option instructs MyISAM to use memory mapping on
the underlying operating system when reading from and writing
to tables.
InnoDB
These options are related to the InnoDB storage engine, a
transactional storage engine:
--innodb
This option enables support for the InnoDB storage
engine. It is enabled by default. Run the SHOW
STORAGE ENGINES; statement on the server to see
which storage engines are enabled.--innodb_additional_mem_pool_size=value
This option sets the size in bytes of the memory pool
used by InnoDB for storing the data dictionary and other
internal data structure information. The default value is 1
MB. If this option does not allocate enough memory, InnoDB
will write warning messages to the error log.--innodb_autoextend_increment=value
This option sets the size in megabytes of increments
made to the size of a tablespace in InnoDB when it is
automatically extended. The default value is 8 (i.e., 8
MB).--innodb_autoinc_lock_mode={0|1|2}
This option sets the locking mode used when the storage
engine generates automatically incremented values. Possible
values are 0 (traditional mode), 1
(consecutive mode), and 2
(interleaved mode). The differences are
described in the MySQL online manual. In general, processing
can get faster under some circumstances as the value of this
option gets higher, but results may not always be safe. This
option is available as of version 5.1.22 of MySQL.--innodb_buffer_pool_awe_mem_mb=value
On 32-bit Windows systems, Address Windowing Extensions
(AWE) may be available for making use of more than the normal
4 GB memory limit. On such a server, you can use this option
to set the amount of AWE memory in megabytes that InnoDB will
use for its buffer pool. This option allows for a value of 0
to 63,000. A value of 0 disables it. To take advantage of AWE,
you need to recompile MySQL.--innodb_buffer_pool_size=value
This option sets the size in bytes of the memory buffer
used by InnoDB for caching data and indexes.--innodb_checksums
With this option, which is enabled by default, checksum
validation is used on pages read from the filesystem. This
provides greater assurance that when data was retrieved there
wasn't a problem due to corrupted files or hardware-related
trouble. Use the
--skip-innodb-checksums option to disable
it.--innodb_commit_concurrency=value
This option sets the maximum number of threads that may
commit transactions simultaneously. A value of 0 removes the
limit on concurrent commits.--innodb_data_file_path=path:size...
This option allows you to increase the storage space for
InnoDB datafiles by specifying names and sizes of datafiles
within the directory given with the --innodb_data_home_dir option.
Each size is a number followed by
M for megabytes or G for
gigabytes. The minimum total of the file sizes should be 10
MB. If no size is given, a 10 MB datafile with autoextending
capability will be used by default. For most operating
systems, there is a 4 GB maximum limit.--innodb_data_home_dir=path
This option specifies the base directory for InnoDB
datafiles. If not used, the default will be the data directory
for MySQL.--innodb_doublewrite
This option, enabled by default, causes InnoDB to write
the data it receives twice. First it writes data to a buffer,
then it writes the data to the filesystem, then it compares
the data for integrity. To disable this behavior, use the
--skip-innodb_doublewrite
option.--innodb_fast_shutdown[={0|1|2}]
This option determines the general procedures that
InnoDB follows when shutting down the storage engine. If it is
set to 0, the process will go much slower (from minutes to
hours longer): it will involve a full purge and a merge of the
insert buffer. If this option is set to the default of 1, the
process is disabled. If it's set to 2, InnoDB will flush its
logs and shut down rapidly. When it's restarted, a crash
recovery will be conducted. This option is not allowed on
NetWare systems.--innodb_file_io_threads=value
This option sets the number of file I/O threads
permitted. The default value is 4. Changing this on Unix-type
systems has no effect. On Windows systems, however,
performance may be improved with a higher value.--innodb_file_per_table
InnoDB uses a shared tablespace by default. When this
option is enabled, a separate .idb file
will be created for each new table to be used for data and
indexes instead of using the shared tablespace. By default
this is disabled.--innodb_flush_log_at_trx_commit={0|1|2}
This option determines the procedure for flushing and
writing to logs along with transaction commits. If it's set to
a value of 0, the log buffer is written to the log file and
the log is flushed every second, but not at a transaction
commit. If it's set to the default of 1, the log buffer is
written to the log file and the log is flushed at every
transaction commit. If it's set to 2, the log buffer is
written to the log file at each transaction commit and the log
is flushed every second without reference to the actual
commit. It's recommended generally that this option be left at
the default value of 1 and that
--sync_binlog also be set to 1 to enable
it.--innodb_flush_method={fdatasync | O_DIRECT |
O_DSYNC}This option sets the method of synchronizing data and
flushing logs with InnoDB. The default value of
fdatasync instructs InnoDB to use the
operating system's fsync( ) call to
synchronize datafiles and log files. The value of
O_DIRECT has the server use
O_DIRECT for opening datafiles and
fsync( ) to synchronize datafiles and
log files. This value is available only for Linux, FreeBSD,
and Solaris systems. O_DSYNC has the server
use O_SYNCH for opening and flushing log
files, but uses fsync( ) to flush
datafiles.--innodb_force_recovery=level
This option puts InnoDB in crash recovery mode. The
allowable values are 1 through 6. Each level includes all
previous levels. Level 1 indicates that the server should
continue running even if it finds corrupt pages. Level 2
prevents the main thread from running a purge operation if it
would cause the server to crash. A value of 3 prevents
transaction rollbacks from being run after the recovery is
finished. A setting of 4 prevents operations from the insert
buffer from running if they would cause the server to crash.
Level 5 causes InnoDB not to consider undo logs when starting
and to consider all transactions to have been committed.
Finally, level 6 instructs the server not to perform a log
roll-forward during the recovery.--innodb_lock_wait_timeout=value
This option sets the maximum number of seconds that
InnoDB can wait to get a lock on a table before it gives up
and rolls back a transaction. The default value is 50.--innodb_locks_unsafe_for_binlog
To achieve something like row-level locking, InnoDB
locks the key for a row. This will also generally prevent
other users from writing to the space next to the row that has
its key locked. Setting this option to a value of 1 disables
this extra protection. Setting it to the default value of 0
protects that next key.--innodb_log_arch_dir=value
This option sets the file path where completed log files
should be archived. Generally, it should be set to
the same directory as the option --innodb_log_group_home_dir.
Archiving is generally not used, as it's not needed or used
for recovery.--innodb_log_archive[={0|1}]
A value of 1 instructs InnoDB to archive log files. By
default, it's set to 0 because it's no longer used.--innodb_log_buffer_size=value
This option sets the size in bytes of InnoDB's log
buffer. InnoDB writes from the buffer to the log file. The
default value is 1 MB.--innodb_log_file_size=value
This option sets the size in bytes of the log file in a
log group to use with InnoDB. The default value is 5 MB.
Larger values for this option make recovery slower. The total
of all log files normally cannot be more than 4 GB.--innodb_log_files_in_group=value
This option determines the number of log files in a log
group. The default is 2. Log files are written to in a
circular manner.--innodb_log_group_home_dir[=path]
This option sets the file path for InnoDB log files. By
default, InnoDB creates two log files in the data directory of
MySQL called ib_logfile0 and
ib_logfile1.--innodb_max_dirty_pages_pct=value
In this context, dirty pages are pages that are in the
buffer pool but are not yet written to the datafiles. Use this
option to set the percentage of dirty pages that may be
allowed in the buffer pool. The value given can range from 0
to 100; the default is 90.--innodb_max_purge_lag=value
This option is related to delays caused by purge
operations that are running slowly or are backed up, thus
holding up SQL statements that change data. Set the value to
the number of such statements that may be delayed during purge
operations. The default value of 0 instructs InnoDB not to
delay them at all.--innodb_mirrored_log_groups=value
This option sets the number of mirrored log groups that
InnoDB should maintain. By default, this is set to 1 and is
usually sufficient.--innodb_open_files=value
This option sets the maximum number of
.idb files that may be open at one time.
The minimum value is 10; the default is 300. This option
applies only when multiple tablespaces are used.--innodb-safe-binlog
This option ensures consistency between the contents of
InnoDB tables and the binary log.--innodb_status_file
This option has InnoDB keep a status file of the results
of the SHOW ENGINE INNODB STATUS statement.
It writes to the file occasionally. The file is named
innodb_status.pid
and is usually located in the data directory of MySQL.--innodb_support_xa
This option enables support for a two-phase commit for
XA transactions. It's enabled and set to 1 by default. A value
of 0 disables it and can sometimes improve performance if the
system doesn't use XA transactions.--innodb_sync_spin_loops=value
This option sets the number of times a thread in InnoDB
will wait for a mutex to be free. Once this is exceeded, the
thread will be suspended.--innodb_table_locks[={0|1}]
When enabled (i.e., set to 1), this option causes InnoDB
to internally lock a table if the LOCK
TABLE statement is run and
AUTOCOMMIT is set to 0.--innodb_thread_concurrency=value
This option sets the maximum number of threads that can
concurrently use InnoDB. Additional threads that try to access
InnoDB tables are put into wait mode. The value can be from 0 to
1,000. Before version 5.1.12 of MySQL, any value over 20 was
the same as unlimited. A value of 0 disables the waiting
behavior and allows unlimited concurrent threads.--innodb_thread_sleep_delay=microseconds
This option sets the number of microseconds that a
thread may sleep before being put on a queue. The default
value is 10,000; 0 disables sleep.--skip-innodb
This option disables the InnoDB storage engine.
--skip-innodb-checksums
By default, InnoDB uses checksum validation on pages
read from the filesystem (see
--innodb-checksums earlier in this
section). This option disables this behavior.--skip-innodb-doublewrite
By default, InnoDB writes to a buffer before writing to
the filesystem (see --innodb-doublewrite
earlier in this section). This option disables this
behavior.--timed_mutexes[={0|1}]
When this option is set to 1, the server stores the
amount of time InnoDB threads waits for mutexes. The default
value of 0 disables this option.
Other storage engine options
These options are recognized by storage engines not
previously listed. This section includes MEMORY and NDB specific
options for the mysqld daemon:
--max_heap_table_size=value
This option sets the maximum number of rows in a MEMORY
table. It applies only to tables created or altered after it's
set.--ndbcluster
This option enables support for the NDB Cluster storage
engine.--ndb-connectstring=string
This option specifies the connect string that the NDB
storage engine uses to create its place in a cluster.--skip-merge
This option disables the MERGE storage engine. It was
added in version 5.1.12 of MySQL.--skip-ndbcluster
This option disables the NDB Cluster storage
engine.
No comments:
Post a Comment