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: mysql> SELECT @@GLOBAL.sql_mode; mysql> 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 non-transactional 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 confirm 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 non-transactional 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. Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server DBA Training In San Jose