MySQL Documentation summary

Here are the notes from the first five chapters of the MySQL official documentation:

MySQL's Batch mode:shell>mysql < batch-file 或者 mysql -e "sql" or "source batch-file".

Four ways to start MySQL: 1, mysqld also known as MySQL Server, is the main program that does most of the works in a MySQL installation

2, Mysqld_safe in Unix under the official recommended boot mode, at startup will read the file's [mysqld], [Server], [Mysqld_safe] part of the options

3, Mysql.server UNIX environment, it is started with Mysqld_safe, at startup will read [mysqld], [Mysql.server] part of the file options

4. Mysqld_multi Start multiple mysqld instances

MySQL variable is divided into global, session two types, global for all the client (set up after the start), session for the current link

BACK_LOG:TCP/IP the waiting queue length of the request, which is primarily for instances with large requests for a short time.

To refer to a component of a structured variable instance, you can use a compound name instance_name.component_name in format. Examples:

Hot_cache.key_buffer_sizehot_cache.key_cache_block_sizecold_cache.key_cache_block_size

For each structured system variable, a instance with the name of are always default predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus, default.key_buffer_size and key_buffer_size both refer to the same system variable.

MySQL Status variables: List all the status variables of MySQL can be divided into global, session.

Com_xxx usually begins with the number of times the XXX statement is executed.

Key_read_requests:the number of requests to read a key block from the MyISAM key cache.

Key_reads:the number of physical reads of a key block from disk to the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can calculated as Key_reads / Key_read_requests .

opened_tables:The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Innodb_buffer_pool_reads:The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

Innodb_buffer_pool_wait_free:Normally, writes to the InnoDB buffer pool happen in the background. When InnoDB needs to read or create a pageand no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits. If innodb_buffer_pool_size has been set properly, this value should be small.

MySQL SQL mode: The value of Sql_mode before MySQL 5.6.6 is empty, 5.6.6 later isNO_ENGINE_SUBSTITUTION

When the mysql_install_db is started, the my.cnf file is generated in the installation directory, which contains the Sql_mode parameters

NO_ENGINE_SUBSTITUTION：在指定的存储引擎不存在时会抛出错误，如果没有这个参数，会采用默认的引擎，并且抛出警告

Combination SQL Modes: Contains ANSI, DB2, and so on. Strict SQL Mode: Contains STRICT_ALL_TABLES、STRICT_TRANS_TABLES等。


In strict mode, an error is thrown when NULL is present under a NOT NULL definition, out of range of values, and so on.

MySQL log:1, general query log 2, error log 3, binary log 4, slow log 5, DDL log

Binary log: Mainly used for copying and recovering data Log_format contains three formats statment, row, mixed

statment: SQL-based statement row: Database line mixed: previous two mixes

Slow log: The query statement exceeded the Long_query_time setting, orlog_queries_not_using_indexes参数打开，并且出现全表扫描的情况即没有使用索引的语句


min_examined_row_limit:如果打开了log_queries_not_using_indexes参数那么slow log可能会比较多，所以可以设置该参数限制频率

log_slow_admin_statements:打开该参数可以针对一些入alter table create table等语句进行根据。

The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE .

MySQL Documentation summary