MySQL Documentation summary

Source: Internet
Author: User
Tags documentation

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.