[MySQL]: Server Administration
9.1 Server System Variables
[client]
[mysqld]
[mysqldump]
[myisamchk]
[mysqlhotcopy]
auto_increment_increment
Controls the interval between successive column values.
auto_increment_offset
determines the starting point for the AUTO_INCREMENT column value.
Autocommit
log-error
skip-networking
init_connect
A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled.
init_file
The name of the file specified with the –init-file option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts. Each statement must be on a single line and should not include comments. No statement terminator such as ;, \g, or \G should be given at the end of each statement.
join_buffer_size
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. The maximum permissible setting for join_buffer_size is 4GB.
max_allowed_packet
The maximum size of one packet or any generated/intermediate string.You must increase this value if you are using large BLOB columns or long strings.
max_connect_errors
If there are more than this number of interrupted connections from a host, that host is blocked from further connections. You can unblock blocked hosts with the FLUSH HOSTS statement. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero.
max_connections
The maximum permitted number of simultaneous client connections. By default, this is 151, beginning with MySQL 5.1.15.
–skip-external-locking
Do not use external locking (system locking). This affects only MyISAM table access.
wait_timeout(default 28800)
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.
9.2 Server Status Variables
The server maintains many status variables that provide information about its operation. You can view these variables and their values by using the SHOW [GLOBAL | SESSION] STATUS statement The optional GLOBAL keyword aggregates the values over all connections, and SESSIONshows the values for the current connection.
Aborted_clients
The number of connections that were aborted because the client died without closing the connection properly.
Aborted_connects
The number of failed attempts to connect to the MySQL server.
Bytes_received
Bytes_sent
Uptime
Com_commit
Com_delete
Com_insert
Com_select
Com_update
Max_used_connections
The maximum number of connections that have been in use simultaneously since the server started.
Queries
The number of statements executed by the server. This variable includes statements executed within stored programs.
Questions
The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs.
Select_scan
The number of joins that did a full scan.
Table_locks_immediate
The number of times that a request for a table lock could be granted immediately.
Table_locks_waited
The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
Threads_connected
The number of currently open connections.
Threads_created
The number of threads created to handle connections. If Threads_created is big, you may want to increase thethread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.
9.3 Server SQL Modes
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
You can set the default SQL mode by starting mysqld with the –sql-mode=”modes” option, or by using sql-mode=”modes” in my.cnf. The default value is empty.
You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode=’modes‘ statement to set the sql_mode system value. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.
You can retrieve the current global or session sql_mode value with the following statements:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
The most important sql_mode values are probably these:
ALLOW_INVALID_DATES
Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.
With strict mode disabled, invalid dates such as ‘2004-04-31’ are converted to ‘0000-00-00’ and a warning is generated. With strict mode enabled, invalid dates generate an error.
ANSI_QUOTES
Treat “”” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.
IGNORE_SPACE
Permit spaces between a function name and the “(” character.
NO_AUTO_CREATE_USER
Prevent the GRANT statement from automatically creating new users unless nonempty password is specified.
NO_DIR_IN_CREATE
When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.
NO_ENGINE_SUBSTITUTION
Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
NO_ZERO_IN_DATE
In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example, ‘0000-00-00’ is legal but ‘2010-00-01’ and ‘2010-01-00’ are not). If used with the IGNORE option, MySQL inserts a ‘0000-00-00’ date for any such date. When not in strict mode, the date is accepted but a warning is generated.
PIPES_AS_CONCAT
Treat || as a string concatenation operator.
REAL_AS_FLOAT
Treat REAL as a synonym for FLOAT.
STRICT_TRANS_TABLES & STRICT_ALL_TABLES
Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range.
For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.
For nontransactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:
- For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it is best to use single-row statements because these can be aborted without changing the table.
- For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement.
ANSI
This mode changes syntax and behavior to conform more closely to standard SQL
REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI
TRADITIONAL
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column.
Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a nontransactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.
Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.