mysql文檔摘要

標籤：

以下是閱讀mysql官方文檔前五章的筆記：

mysql的batch mode：shell> mysql < batch-file 或者 mysql -e "sql" or "source batch-file".

 

mysql的四種啟動方式：1、 mysqld also known as MySQL Server, is the main program that does most of the work in a MySQL installation

2、mysqld_safe在unix下官方推薦的啟動模式,在啟動時會讀取檔案的[mysqld]、[server]、[mysqld_safe]部分選項

3、mysql.server unix環境，它是以mysqld_safe來啟動的，在啟動時會讀取檔案的[mysqld]、[mysql.server]部分選項

4、mysqld_multi 啟動多個mysqld執行個體

 

mysql變數分為global、session兩種類型，global針對所有的client(設定後啟動的),session針對當前回話的連結

back_log:tcp/ip請求的等待隊列長度，主要針對短時間大量請求的執行個體需要增加數值

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

hot_cache.key_buffer_sizehot_cache.key_cache_block_sizecold_cache.key_cache_block_size

For each structured system variable, an instance with the name of default is always 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:列出mysql所有的狀態變數可以分global、session。

Com_xxx開頭的一般是記錄xxx語句執行的次數。

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 into the MyISAM key cache.If Key_reads is large, then yourkey_buffer_size value is probably too small. The cache miss rate can be calculated asKey_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:在mysql 5.6.6以前sql_mode的值是空，5.6.6以後是NO_ENGINE_SUBSTITUTION

mysql_install_db啟動的時候，在安裝目錄會產生my.cnf檔案，其中包含了sql_mode參數

NO_ENGINE_SUBSTITUTION：在指定的儲存引擎不存在時會拋出錯誤，如果沒有這個參數，會採用預設的引擎，並且拋出警告

Combination SQL Modes:包含ANSI、DB2等。Strict SQL Mode：包含  STRICT_ALL_TABLES、STRICT_TRANS_TABLES等。


在strict 模式下：在not null定義下出現null、超出值範圍等都會拋出error。

 

mysql log:1、general query log 2、error log 3、binary log 4、slow log 5、DDL log

binary log:主要用於複製、恢複資料 log_format包含三種格式statment、row、mixed

statment：基於sql語句 row：基於資料庫行 mixed：前面兩種的混合

slow log:查詢語句超出了long_query_time的設定，或者log_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文檔摘要