MySQL檔案概述

lhrbest發表於2017-08-24
MySql

MySQL檔案概述

 Mysql  資料庫的檔案包括：

1、引數檔案： my.cnf

2、日誌檔案，包括錯誤日誌、查詢日誌、慢查詢日誌、二進位制日誌

3、Mysql 表檔案：用來存放 mysql 表結構的檔案，一般以 .frm 為字尾

4、Socket 檔案：當用 unix 域套接字方式進行連線時需要的檔案

5、Pid 檔案： mysql 例項的程式 ID 檔案

6、儲存引擎檔案：每個儲存引擎都有自己的資料夾來儲存各種資料，這些儲存引擎真正儲存了資料和索引等資料。

引數檔案

可以透過命令列“ mysql --help | grep my.cnf ”檢視 my.cnf 檔案的位置；

Mysql 在啟動時可以不需要引數檔案，但是如果在預設的資料庫目錄下找不到 mysql 架構，則啟動會失敗；

Mysql 的引數可以透過“ show variables ”來檢視，由於 從 mysql5.1 版本開始，可以透過 information_schema 架構下的 GLOBAL_VARIABLES 檢視來進行查詢 ，所以也可以這樣檢視

“ select * from information_schema.global_variables ”；

Mysql  的引數型別：分為動態（ dynamic ）和靜態引數（ static ）， 動態引數意味著可以再 mysql 例項執行中進行更改；靜態引數說明在整個例項宣告週期內都不得進行更改，就好像是隻讀的。在動態引數中，有些引數修改可以是基於回話的也可以是基於整個例項的生命週期。 Mysql5.1 的動態引數 在這裡。

Mysql 表檔案

不論採用何種搜尋引擎， mysql 都有一個以 frm 為字尾名的檔案，這個檔案記錄了該表的表結構定義檔案。 值得注意的是， frm 還可以用來存放檢視的定義 。

Socket 檔案

透過使用命令“ show variables like  ‘socket’; ”檢視 socket 檔案的目錄

 

Pid  檔案

當 mysql 例項啟動時，會將自己的程式 ID 寫入一個檔案中，該檔案即為 pid 檔案，檔名為主機名 .pid ，透過命令“ show variables like  ‘pid_file’; ”來檢視。

 

日誌檔案   http://blog.itpub.net/26736162/viewspace-2142929/

1、錯誤日誌，該檔案對 mysql 的啟動、執行、關閉過程進行了記錄，在遇到問題時，首先應該檢視此檔案，可以透過“ show variables like  ‘log_error’; ”來定位該檔案。

 

2、慢查詢日誌，該檔案記錄了所有執行時間超過閥值的 SQL 語句，該閥值可以透過引數 long_query_time 來設定。預設值為 10 秒。

 

但是在預設情況下，mysql 並不啟動滿查詢日誌， 需要手工修改這個引數；

 

需要注意的兩點內容： 首先，慢查詢日誌記錄的是大於閥值的SQL 語句，而不是大於等於！！！其次，從 mysql5.1 開始， long_quey_time 開始以微秒記錄 sql 語句執行時間 。

另一個和慢查詢相關的引數是 log_queries_not_using_index ，如果執行的 SQL 語句沒有使用索引，則 mysql 則同樣會將這條 SQL 語句記錄到慢查詢日誌檔案中 ；

 

在慢查詢日誌檔案逐漸增大時，可考慮使用工具mysqldumpslow 工具幫助我們分析。

該工具的使用您可以使用 “ man mysqldumpslow ” 或者去 這裡 瞭解。

Mysql5.1 開始可以將慢查詢的日誌記錄放入一張表中，該表在 mysql.slow_log 表中。

是否放在表中由引數‘ log_output ’來決定；引數 log_output 指定了慢查詢輸出的格式，預設為 FILE, 也可以將其設定為 TABLE ，就可以去 Mysql.slow_log 中去查詢了 。

 

同樣也可以將查詢日誌的記錄放入mysql 架構下的 general_log 表。

3、查詢日誌

查詢日誌記錄了所有對 Mysql 資料庫請求的資訊。

4、二進位制日誌

因為二進位制日誌太重要了，在這裡暫且不提。

InnoDB 儲存引擎檔案

這些檔案包括表空間檔案和重做日誌檔案。表空間的介紹請看 Mysql InnoDB 儲存結構總結 ，重做日誌也很重要，在這裡暫且不提。

本章將分析構成MySQL資料庫和InnoDB儲存引擎表的各種型別檔案，如下所示。

引數檔案：告訴MySQL例項啟動時在哪裡可以找到資料庫檔案，並且指定某些初始化引數，這些引數定義了某種記憶體結構的大小等設定，還會介紹各種引數的型別。

日誌檔案：用來記錄MySQL例項對某種條件做出響應時寫入的檔案。如錯誤日誌檔案、二進位制日誌檔案、滿查詢日誌檔案、查詢日誌檔案等。

socket檔案：當用Unix域套接字方式進行連線時需要的檔案。

pid檔案：MySQL例項的程式ID檔案。

MySQL表結構檔案：用來存放MySQL表結構定義檔案。

儲存引擎檔案：因為MySQL表儲存引擎的關係，每個儲存引擎都會有自己的檔案來儲存各種資料。這些儲存引擎真正儲存了資料和索引等資料。本章主要介紹與InnoDB有關的儲存引擎檔案。

3.1   引數檔案

在第1章中已經介紹過了，當MySQL例項啟動時，MySQL會先去讀一個配置引數檔案，用來尋找資料庫的各種檔案所在位置以及指定某些初始化引數，這些引數通常定義了某種記憶體結構有多大等設定。預設情況下，MySQL例項會按照一定的次序去取，你只需透過命令mysql --help | grep my.cnf來尋找即可。

MySQL引數檔案的作用和Oracle的引數檔案極其類似；不同的是，Oracle例項啟動時若找不到引數檔案，是不能進行裝載（mount）操作的。MySQL稍微有所不同，MySQL例項可以不需要引數檔案，這時所有的引數值取決於編譯MySQL時指定的預設值和原始碼中指定引數的預設值。但是，如果MySQL在預設的資料庫目錄下找不到mysql架構，則啟動同樣會失敗，你可能在錯誤日誌檔案中找到如下內容：

  1. 090922 16:25:52  mysqld started  
  2. 090922 16:25:53  InnoDB: Started; log sequence number 8 2801063211  
  3. InnoDB: !!! innodb_force_recovery is set to 1 !!!  
  4. 090922 16:25:53 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist  
  5. 090922 16:25:53  mysqld ended 

MySQL中的mysql架構中記錄了訪問該例項的許可權，當找不到這個架構時，MySQL例項不會成功啟動。

和Oracle引數檔案不同的是，Oracle的引數檔案分為二進位制的引數檔案（spfile）和文字型別的引數檔案（init.ora），而MySQL的引數檔案僅是文字的，方便的是，你可以透過一些常用的編輯軟體（如vi和emacs）進行引數的編輯。

3.1.1   什麼是引數

簡單地說，可以把資料庫引數看成一個鍵/值對。第2章已經介紹了一個對於InnoDB儲存引擎很重要的引數innodb_buffer_pool_size。如我們將這個引數設定為1G，即innodb_buffer_pool_size=1G,，這裡的“鍵”是innodb_buffer_pool_size，“值”是1G，這就是我們的鍵值對。可以透過show variables檢視所有的引數，或透過like來過濾引數名。從MySQL 5.1版本開始，可以透過information_schema架構下的GLOBAL_VARIABLES檢視來進行查詢，如下所示。

  1. mysql >  select * from GLOBAL_VARIABLES where  VARIABLE_NAME like 'innodb_buffer%'\G;  
  2. *************************** 1. row ***************************  
  3. VARIABLE_NAME: INNODB_BUFFER_POOL_SIZE  
  4. VARIABLE_VALUE: 1073741824  
  5. 1 row in set (0.00 sec)  
  6.  
  7. mysql >  show variables like 'innodb_buffer%'\G;  
  8. *************************** 1. row ***************************  
  9. Variable_name: innodb_buffer_pool_size  
  10.         Value: 1073741824  
  11. 1 row in set (0.00 sec) 

無論使用哪種方法，輸出的資訊基本上都一樣的，只不過透過檢視GLOBAL_ VARIABLES需要指定檢視的列名。推薦使用show variables命令，因為這個命令使用更為簡單，各版本的MySQL資料庫都支援它。

Oracle的引數有所謂的隱藏引數（undocumented parameter），以供Oracle“內部人士”使用，SQL Server也有類似的引數。有些DBA曾問我，MySQL中是否也有這類引數。我的回答是：沒有，也不需要。即使Oracle和SQL Server中都有些所謂的隱藏引數，在絕大多數情況下，這些資料庫廠商也不建議你在生產環境中對其進行很大的調整。

3.1.2   引數型別

MySQL引數檔案中的引數可以分為兩類：動態（dynamic）引數和靜態（static）引數。動態引數意味著你可以在MySQL例項執行中進行更改；靜態引數說明在整個例項生命週期內都不得進行更改，就好像是隻讀（read only）的。可以透過SET命令對動態的引數值進行修改，SET的語法如下：

  1. SET   
  2. | [global | session]  system_var_name =  expr  
  3. | [@@global. | @@session. | @@] system_var_name =  expr  

這裡可以看到global和session關鍵字，它們表明該引數的修改是基於當前會話還是整個例項的生命週期。有些動態引數只能在會話中進行修改，如autocommit；有些引數修改完後，在整個例項生命週期中都會生效，如binlog_cache_size；而有些引數既可以在會話又可以在整個例項的生命週期內生效，如read_buffer_size。舉例如下：

  1. mysql >  set  read_buffer_size = 524288 ;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.  
  4. mysql >  select @@session.read_buffer_size\G;  
  5. *************************** 1. row ***************************  
  6. @@session.read_buffer_size: 524288  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  select @@global.read_buffer_size\G;  
  10. *************************** 1. row ***************************  
  11. @@global.read_buffer_size: 2093056  
  12. 1 row in set (0.00 sec) 

上面我將read_buffer_size的會話值從2MB調整為了512KB，你可以看到全域性的read_buffer_size的值仍然是2MB，也就是說，如果有另一個會話登入到MySQL例項，它的read_buffer_size的值是2MB，而不是512KB。這裡使用了set global|session來改變動態變數的值。我們同樣可以直接使用set @@globl|@@session來更改，如下所示：

  1. mysql >  set @@ global.read_buffer_size = 1048576 ;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.  
  4. mysql >  select @@session.read_buffer_size\G;  
  5. *************************** 1. row ***************************  
  6. @@session.read_buffer_size: 524288  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  select @@global.read_buffer_size\G;  
  10. *************************** 1. row ***************************  
  11. @@global.read_buffer_size: 1048576  
  12. 1 row in set (0.00 sec) 

這次我們把read_buffer_size全域性值更改為1MB，而當前會話的read_buffer_size的值還是512KB。這裡需要注意的是，對變數的全域性值進行了修改，在這次的例項生命週期內都有效，但MySQL例項本身並不會對引數檔案中的該值進行修改。也就是說下次啟動時，MySQL例項還是會讀取引數檔案。如果你想讓資料庫例項下一次啟動時該引數還是保留為當前修改的值，則必須修改引數檔案。要想知道MySQL所有動態變數的可修改範圍，可以參考MySQL官方手冊的第5.1.4.2節（Dynamic System Variables）的相關內容。

對於靜態變數，如果對其進行修改，會得到類似如下的錯誤：

  1. mysql >  set global  datadir = '/db/mysql' ;  
  2. ERROR 1238 (HY000): Variable 'datadir' is a read only variable 

3.2   日誌檔案

日誌檔案記錄了影響MySQL資料庫的各種型別活動。MySQL資料庫中常見的日誌檔案有錯誤日誌、二進位制日誌、慢查詢日誌、查詢日誌。這些日誌檔案為DBA對資料庫最佳化、問題查詢等帶來了極大的便利。

3.2.1   錯誤日誌

錯誤日誌檔案對MySQL的啟動、執行、關閉過程進行了記錄。MySQL DBA在遇到問題時應該首先檢視該檔案。該檔案不但記錄了出錯資訊，也記錄一些警告資訊或者正確的資訊。總的來說，這個檔案更類似於Oracle的alert檔案，只不過在預設情況下是err結尾。你可以透過show variables like 'log_error'來定位該檔案，如：

  1. mysql >  show variables like 'log_error';  
  2. +---------------+-------------------------------------------+  
  3. | Variable_name | Value                       |  
  4. +---------------+-------------------------------------------+  
  5. | log_error     | /usr/local/mysql/data/stargazer.err |  
  6. +---------------+-------------------------------------------+  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  system hostname  
  10. stargazer 

可以看到錯誤檔案的路徑和檔名，預設情況下錯誤檔案的檔名為伺服器的主機名。如上面我們看到的，該主機名為stargazer，所以錯誤檔名為startgazer.err。當出現MySQL資料庫不能正常啟動時，第一個必須查詢的檔案應該就是錯誤日誌檔案，該檔案記錄了出錯資訊，能很好地指導我們找到問題，如果當資料庫不能重啟，透過查錯誤日誌檔案可以得到如下內容：

  1. [root@nineyou0-43 data]# tail -n 50 nineyou0-43.err   
  2. 090924 11:31:18  mysqld started  
  3. 090924 11:31:18  InnoDB: Started; log sequence number 8 2801063331  
  4. 090924 11:31:19 [ERROR] Fatal error: Can't open and lock privilege tables:   
  5. Table 'mysql.host' doesn't exist  
  6. 090924 11:31:19  mysqld ended 

這裡，錯誤日誌檔案提示了你找不到許可權庫mysql，所以啟動失敗。有時我們可以直接在錯誤日誌檔案裡得到最佳化的幫助，因為有些警告（warning）很好地說明了問題所在。而這時我們可以不需要透過檢視資料庫狀態來得知，如：


  1. 090924 11:39:44  InnoDB: ERROR: the age of the  last checkpoint is 9433712,  
  2. InnoDB: which exceeds the log group capacity 9433498.  
  3. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
  4. InnoDB: combined size of log files at least 10  times bigger than the  
  5. InnoDB: largest such row.  
  6. 090924 11:40:00  InnoDB: ERROR: the age of the  last checkpoint is 9433823,  
  7. InnoDB: which exceeds the log group capacity 9433498.  
  8. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
  9. InnoDB: combined size of log files at least 10 times bigger than the  
  10. InnoDB: largest such row.  
  11. 090924 11:40:16  InnoDB: ERROR: the age of the last checkpoint is 9433645,  
  12. InnoDB: which exceeds the log group capacity 9433498.  
  13. InnoDB: If you are using big BLOB or TEXT rows, you must set the  
  14. InnoDB: combined size of log files at least 10  times bigger than the  
  15. InnoDB: largest such row. 

3.2.2   慢查詢日誌（1）

前一小節提到可以透過錯誤日誌得到一些關於資料庫最佳化的資訊幫助，而慢查詢能為SQL語句的最佳化帶來很好的幫助。可以設一個閾值，將執行時間超過該值的所有SQL語句都記錄到慢查詢日誌檔案中。該閾值可以透過引數long_query_time來設定，預設值為10，代表10秒。

預設情況下，MySQL資料庫並不啟動慢查詢日誌，你需要手工將這個引數設為ON，然後啟動，可以看到如下結果：

  1. mysql >  show variables like '%long%';  
  2. +-----------------+------------+  
  3. | Variable_name   | Value |  
  4. +-----------------+------------+  
  5. | long_query_time | 10    |   
  6. +-----------------+------------+  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  show variables like 'log_slow_queries';  
  10. +------------------+------------+  
  11. | Variable_name | Value |  
  12. +------------------+------------+  
  13. | log_slow_queries | ON|   
  14. +------------------+------------+  
  15. 1 row in set (0.01 sec) 

這裡需要注意兩點。首先，設定long_query_time這個閾值後，MySQL資料庫會記錄執行時間超過該值的所有SQL語句，但對於執行時間正好等於long_query_time的情況，並不會被記錄下。也就是說，在原始碼裡是判斷大於long_query_time，而非大於等於。其次，從MySQL 5.1開始，long_query_time開始以微秒記錄SQL語句執行時間，之前僅用秒為單位記錄。這樣可以更精確地記錄SQL的執行時間，供DBA分析。對DBA來說，一條SQL語句執行0.5秒和0.05秒是非常不同的，前者可能已經進行了表掃，後面可能是走了索引。下面的程式碼中，是在MySQL 5.1中將long_query_time設定為了0.05：

  1. mysql >  show variables like 'long_query_time';  
  2. +-----------------+--------------+  
  3. | Variable_name   | Value|  
  4. +-----------------+--------------+  
  5. | long_query_time | 0.050000 |  
  6. +-----------------+--------------+  
  7. 1 row in set (0.00 sec) 

另一個和慢查詢日誌有關的引數是log_queries_not_using_indexes，如果執行的SQL語句沒有使用索引，則MySQL資料庫同樣會將這條SQL語句記錄到慢查詢日誌檔案。首先，確認開啟了log_queries_not_using_indexes:

  1. mysql >  show variables like 'log_queries_not_using_indexes';  
  2. +-------------------------------+------------------+  
  3. | Variable_name         | Value|  
  4. +-------------------------------+------------------+  
  5. | log_queries_not_using_indexes | ON  |  
  6. +-------------------------------+------------------+  
  7. 1 row in set (0.00 sec)  
  8. update 'low_game_schema'.'item' set  SLOT = '8'  where GUID = '2222249168632297608'  and  is_destroy = '0' ; 

這裡詳細記錄了SQL語句的資訊，如上述SQL語句執行的賬戶和IP、執行時間、鎖定的時間、返回行等。我們可以透過慢查詢日誌來找出有問題的SQL語句，對其進行最佳化。隨著MySQL資料庫伺服器執行時間的增加，可能會有越來越多的SQL查詢被記錄到了慢查詢日誌檔案中，這時要分析該檔案就顯得不是很容易了。MySQL這時提供的mysqldumpslow命令，可以很好地解決這個問題：

  1. [root@nh122-190 data]# mysqldumpslow nh122-190-slow.log  
  2. Reading mysql slow query log from nh122-190-slow.log  
  3. Count: 11   Time = 10 .00s (110s)   Lock = 0 .00s (0s)   Rows = 0 .0 (0), dbother[dbother]@localhost  
  4.   insert into test.DbStatus select now(),(N-com_ select)/(N-uptime),(N-com_insert)/(N-uptime),(N-com_ update)/(N-uptime),(N-com_delete)/(N-uptime),N-(N/N), N-(N/N),N.N/N,N-N/(N*N),GetCPULoadInfo(N) from test. CheckDbStatus order by check_id desc limit N  
  5. Count: 653  Time = 0 .00s (0s)   Lock = 0 .00s (0s)   Rows = 0 .0 (0), 9YOUgs_SC[9YOUgs_SC]@ [192.168.43.7]  
  6.   select custom_name_one from 'low_game_schema'.'role_ details' where  role_id = 'S'  
  7. rse and summarize the MySQL slow query log. Options are  
  8.   --verbose    verbose  
  9.   --debug      debug  
  10.   --help       write this text to standard output  
  11.  
  12.   -v           verbose  
  13.   -d           debug  
  14.   -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  
  15.                 al: average lock time  
  16.                 ar: average rows sent  
  17.                 at: average query time  
  18.                  c: count  
  19.                  l: lock time  
  20.                  r: rows sent  
  21.                  t: query time    
  22.   -r           reverse the sort order (largest last instead of first)  
  23.   -t NUM       just show the top n queries  
  24.   -a           don't abstract all numbers to N and strings to 'S'  
  25.   -n NUM       abstract numbers with at least n digits within names  
  26.   -g PATTERN   grep: only consider stmts that include this string  
  27.   -h HOSTNAME  hostname of db server for *-slow.log  filename (can be wildcard),  
  28.                default is '*', i.e. match all  
  29.   -i NAME      name of server instance (if using mysql. server startup script)  
  30.   -l           don't subtract lock time from total time 

3.2.2   慢查詢日誌（2）

如果我們想得到鎖定時間最長的10條SQL語句，可以執行：

  1. [root@nh119-141 data]# /usr/local/mysql/bin /mysqldumpslow -s al -n 10 david.log   
  2. Reading mysql slow query log from david.log  
  3. Count: 5   Time = 0 .00s (0s)   Lock = 0 .20s (1s)  Rows = 4 .4 (22), Audition [Audition]@[192.168.30.108]  
  4.   SELECT OtherSN, State FROM wait_friend_info WHERE  UserSN  = N  
  5.  
  6. Count: 1   Time = 0 .00s (0s)   Lock = 0 .00s (0s)   Rows = 1 .0 (1), audition-kr[audition-kr]@[192.168.30.105]  
  7.   SELECT COUNT(N) FROM famverifycode WHERE  UserSN =N AND  verifycode = 'S'  
  8. ...... 

MySQL 5.1開始可以將慢查詢的日誌記錄放入一張表中，這使我們的查詢更加直觀。慢查詢表在mysql架構下，名為slow_log。其表結構定義如下：

  1. mysql >  show create table mysql.slow_log;  
  2. *************************** 1. row ***************************  
  3.        Table: slow_log  
  4. Create Table: CREATE TABLE 'slow_log' (  
  5.   'start_time' timestamp NOT NULL DEFAULT  CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  6.   'user_host' mediumtext NOT NULL,  
  7.   'query_time' time NOT NULL,  
  8.   'lock_time' time NOT NULL,  
  9.   'rows_sent' int(11) NOT NULL,  
  10.   'rows_examined' int(11) NOT NULL,  
  11.   'db' varchar(512) NOT NULL,  
  12.   'last_insert_id' int(11) NOT NULL,  
  13.   'insert_id' int(11) NOT NULL,  
  14.   'server_id' int(11) NOT NULL,  
  15.   'sql_text' mediumtext NOT NULL  
  16. )  ENGINE = CSV  DEFAULT  CHARSET = utf8   COMMENT = 'Slow log'  
  17. 1 row in set (0.00 sec) 

引數log_output指定了慢查詢輸出的格式，預設為FILE，你可以將它設為TABLE，然後就可以查詢mysql架構下的slow_log表了，如：

  1. mysql >  show variables like 'log_output';  
  2. +---------------+---------+  
  3. | Variable_name | Value |  
  4. +---------------+---------+  
  5. | log_output    | FILE |  
  6. +---------------+---------+  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  set global  log_output = 'TABLE' ;  
  10. Query OK, 0 rows affected (0.00 sec)  
  11.  
  12. mysql >  show variables like 'log_output';  
  13. +---------------+---------+  
  14. | Variable_name | Value |  
  15. +---------------+---------+  
  16. | log_output  | TABLE |  
  17. +---------------+---------+  
  18. 1 row in set (0.00 sec)  
  19.  
  20. mysql >  select sleep(10);  
  21. +-----------+  
  22. | sleep(10)|  
  23. +-----------+  
  24. |       0 |  
  25. +-----------+  
  26. 1 row in set (10.01 sec)  
  27. mysql >  select * from mysql.slow_log\G;  
  28. *************************** 1. row ***************************  
  29.     start_time: 2009-09-25 13:44:29  
  30.      user_host: david[david] @ localhost []  
  31.     query_time: 00:00:09  
  32.      lock_time: 00:00:00  
  33.      rows_sent: 1  
  34.  rows_examined: 0  
  35.             db: mysql  
  36. last_insert_id: 0  
  37.      insert_id: 0  
  38.      server_id: 0  
  39.       sql_text: select sleep(10)  
  40. 1 row in set (0.00 sec) 

引數log_output是動態的，並且是全域性的。我們可以線上進行修改。在上表中我設定了睡眠（sleep）10秒，那麼這句SQL語句就會被記錄到slow_log表了。

檢視slow_log表的定義會發現，該表使用的是CSV引擎，對大資料量下的查詢效率可能不高。我們可以把slow_log表的引擎轉換到MyISAM，用來進一步提高查詢的效率。但是，如果已經啟動了慢查詢，將會提示錯誤：

  1. mysql >  alter table mysql.slow_log  engine = myisam ;  
  2. ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled  
  3.  
  4. mysql >  set global  slow_query_log = off ;  
  5. Query OK, 0 rows affected (0.00 sec)  
  6.  
  7. mysql >  alter table mysql.slow_log  engine = myisam ;  
  8. Query OK, 1 row affected (0.00 sec)  
  9. Records: 1  Duplicates: 0  Warnings: 0 

不能忽視的是，將slow_log表的儲存引擎更改為MyISAM後，對資料庫還是會造成額外的開銷。不過好在很多關於慢查詢的引數都是動態的，我們可以方便地線上進行設定或者修改。

3.2.3   查詢日誌

查詢日誌記錄了所有對MySQL資料庫請求的資訊，不論這些請求是否得到了正確的執行。預設檔名為：主機名.log。我們檢視一個查詢日誌：

  1. [root@nineyou0-43 data]# tail nineyou0-43.log   
  2. 090925 11:00:24   44 Connect     zlm@192.168.0.100 on   
  3. 44 Query       SET  AUTOCOMMIT = 0  
  4.                44 Query       set  autocommit = 0  
  5.                44 Quit         
  6. 090925 11:02:37 45 Connect   Access denied for  user 'root'@'localhost' (using password: NO)  
  7. 090925 11:03:51 46 Connect   Access denied for  user 'root'@'localhost' (using password: NO)  
  8. 090925 11:04:38  23 Query    rollback 

透過上述查詢日誌你會發現，查詢日誌甚至記錄了對access denied的請求。同樣，從MySQL 5.1開始，可以將查詢日誌的記錄放入mysql架構下的general_log表，該表的使用方法和前面小節提到的slow_log基本一樣，這裡不再贅述。

3.2.4   二進位制日誌（1）

二進位制日誌記錄了對資料庫執行更改的所有操作，但是不包括SELECT和SHOW這類操作，因為這類操作對資料本身並沒有修改，如果你還想記錄SELECT和SHOW操作，那隻能使用查詢日誌，而不是二進位制日誌了。此外，二進位制還包括了執行資料庫更改操作的時間和執行時間等資訊。二進位制日誌主要有以下兩種作用：

恢復（recovery）。某些資料的恢復需要二進位制日誌，如當一個資料庫全備檔案恢復後，我們可以透過二進位制日誌進行point-in-time的恢復。

複製（replication）。其原理與恢復類似，透過複製和執行二進位制日誌使得一臺遠端的MySQL資料庫（一般稱為slave或者standby）與一臺MySQL資料庫（一般稱為master或者primary）進行實時同步。

透過配置引數log-bin[=name]可以啟動二進位制日誌。如果不指定name，則預設二進位制日誌檔名為主機名，字尾名為二進位制日誌的序列號，所在路徑為資料庫所在目錄（datadir），如：

  1. mysql >  show variables like 'datadir';  
  2. +---------------+----------------------------+  
  3. | Variable_name | Value     |  
  4. +---------------+----------------------------+  
  5. | datadir       | /usr/local/mysql/data/ |   
  6. +---------------+----------------------------+  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  system ls -lh /usr/local/mysql/data/;  
  10. total 2.1G  
  11. -rw-rw----  1 mysql mysql 6.5M Sep 25 15:13 bin_log.000001  
  12. -rw-rw----  1 mysql mysql   17 Sep 25 00:32 bin_log.index  
  13. -rw-rw----  1 mysql mysql 300M Sep 25 15:13 ibdata1  
  14. -rw-rw----  1 mysql mysql 256M Sep 25 15:13 ib_logfile0  
  15. -rw-rw----  1 mysql mysql 256M Sep 25 15:13 ib_logfile1  
  16. drwxr-xr-x  2 mysql mysql 4.0K May  7 10:08 mysql  
  17. drwx------  2 mysql mysql 4.0K May  7 10:09 test 

這裡的bin_log.00001即為二進位制日誌檔案，我們在配置檔案中指定了名稱，所以沒有用預設的檔名。bin_log.index為二進位制的索引檔案，用來儲存過往生產的二進位制日誌序號，通常情況下，不建議手工修改這個檔案。

二進位制日誌檔案在預設情況下並沒有啟動，需要你手動指定引數來啟動。可能有人會質疑，開啟這個選項是否會對資料庫整體效能有所影響。不錯，開啟這個選項的確會影響效能，但是效能的損失十分有限。根據MySQL官方手冊中的測試表明，開啟二進位制日誌會使得效能下降1%。但考慮到可以使用複製（replication）和point-in-time的恢復，這些效能損失絕對是可以並且是應該被接受的。

以下配置檔案的引數影響著二進位制日誌記錄的資訊和行為：

這裡的bin_log.00001即為二進位制日誌檔案，我們在配置檔案中指定了名稱，所以沒有用預設的檔名。bin_log.index為二進位制的索引檔案，用來儲存過往生產的二進位制日誌序號，通常情況下，不建議手工修改這個檔案。

二進位制日誌檔案在預設情況下並沒有啟動，需要你手動指定引數來啟動。可能有人會質疑，開啟這個選項是否會對資料庫整體效能有所影響。不錯，開啟這個選項的確會影響效能，但是效能的損失十分有限。根據MySQL官方手冊中的測試表明，開啟二進位制日誌會使得效能下降1%。但考慮到可以使用複製（replication）和point-in-time的恢復，這些效能損失絕對是可以並且是應該被接受的。

以下配置檔案的引數影響著二進位制日誌記錄的資訊和行為：

max_binlog_size

binlog_cache_size

sync_binlog

binlog-do-db

binlog-ingore-db

log-slave-update

binlog_format

引數max-binlog-size指定了單個二進位制日誌檔案的最大值，如果超過該值，則產生新的二進位制日誌檔案，字尾名+1，並記錄到.index檔案。從MySQL 5.0開始的預設值為

1 073 741 824，代表1GB（之前的版本max-binlog-size預設大小為1.1GB）。

3.2.4   二進位制日誌（2）

當使用事務的表儲存引擎（如InnoDB儲存引擎）時，所有未提交（uncommitted）的二進位制日誌會被記錄到一個快取中，等該事務提交時（committed）時直接將緩衝中的二進位制日誌寫入二進位制日誌檔案，而該緩衝的大小由binlog_cache_size決定，預設大小為32KB。此外，binlog_cache_size是基於會話（session）的，也就是說，當一個執行緒開始一個事務時，MySQL會自動分配一個大小為binlog_cache_size的快取，因此該值的設定需要相當小心，不能設定過大。當一個事務的記錄大於設定的binlog_cache_size時，MySQL會把緩衝中的日誌寫入一個臨時檔案中，因此該值又不能設得太小。透過SHOW GLOBAL STATUS命令檢視binlog_cache_use、binlog_cache_disk_use的狀態,可以判斷當前binlog_cache_size的設定是否合適。Binlog_cache_use記錄了使用緩衝寫二進位制日誌的次數，binlog_cache_disk_use記錄了使用臨時檔案寫二進位制日誌的次數。現在來看一個資料庫的狀態：

  1. mysql >  show variables like 'binlog_cache_size';  
  2. +-------------------+-------+  
  3. | Variable_name   | Value |  
  4. +-------------------+-------+  
  5. | binlog_cache_size | 32768 |   
  6. +-------------------+-------+  
  7. 1 row in set (0.00 sec)  
  8.  
  9. mysql >  show global status like 'binlog_cache%';  
  10. +-----------------------+--------------+  
  11. | Variable_name  | Value |  
  12. +-----------------------+---------------+  
  13. | binlog_cache_disk_use | 0     |   
  14. | binlog_cache_use     | 33553 |   
  15. +-----------------------+---------------+  
  16. 2 rows in set (0.00 sec) 

使用緩衝次數33 553次，臨時檔案使用次數為0。看來，32KB的緩衝大小對於當前這個MySQL資料庫完全夠用，所以暫時沒有必要增加binlog_cache_size的值。

預設情況下，二進位制日誌並不是在每次寫的時候同步到磁碟（我們可以理解為緩衝寫）。因此，當資料庫所在作業系統發生當機時，可能會有最後一部分資料沒有寫入二進位制日誌檔案中。這會給恢復和複製帶來問題。引數sync_binlog=[N]表示每寫緩衝多少次就同步到磁碟。如果將N設為1，即sync_binlog=1表示採用同步寫磁碟的方式來寫二進位制日誌，這時寫操作不使用作業系統的緩衝來寫二進位制日誌。該預設值為0，如果使用InnoDB儲存引擎進行復制，並且想得到最大的高可用性，建議將該值設為ON。不過該值為ON時，確實會對資料庫的IO系統帶來一定的影響。

但是，即使將sync_binlog設為1，還是會有一種情況會導致問題的發生。當使用InnoDB儲存引擎時，在一個事務發出COMMIT動作之前，由於sync_binlog設為1，因此會將二進位制日誌立即寫入磁碟。如果這時已經寫入了二進位制日誌，但是提交還沒有發生，並且此時發生了當機，那麼在MySQL資料庫下次啟動時，因為COMMIT操作並沒有發生，所以這個事務會被回滾掉。但是二進位制日誌已經記錄了該事務資訊，不能被回滾。這個問題可以透過將引數innodb_support_xa設為1來解決，雖然innodb_support_xa與XA事務有關，但它同時也確保了二進位制日誌和InnoDB儲存引擎資料檔案的同步。

引數binlog-do-db和binlog-ignore-db表示需要寫入或者忽略寫入哪些庫的日誌。預設為空，表示需要將所有庫的日誌同步到二進位制日誌。

如果當前資料庫是複製中的slave角色，則它不會將從master取得並執行的二進位制日誌寫入自己的二進位制日誌檔案中。如果需要寫入，則需要設定log-slave-update。如果你需要搭建master=>slave=>slave架構的複製，則必須設定該引數。

binlog_format引數十分重要，這影響了記錄二進位制日誌的格式。在MySQL 5.1版本之前，沒有這個引數。所有二進位制檔案的格式都是基於SQL語句（statement）級別的，因此基於這個格式的二進位制日誌檔案的複製（Replication）和Oracle 邏輯Standby有點相似。同時，對於複製是有一定要求的如rand、uuid等函式，或者有使用觸發器等可能會導致主從伺服器上表的資料不一致（not sync），這可能使得複製變得沒有意義。另一個影響是，你會發現InnoDB儲存引擎的預設事務隔離級別是REPEATABLE READ。這其實也是因為二進位制日誌檔案格式的關係，如果使用READ COMMITTED的事務隔離級別（大多數資料庫，如Oracle、Microsoft SQL Server資料庫的預設隔離級別）會出現類似丟失更新的現象，從而出現主從資料庫上的資料不一致。

3.2.4   二進位制日誌（3）

MySQL 5.1開始引入了binlog_format引數，該引數可設的值有STATEMENT、ROW和MIXED。

（1）STATEMENT格式和之前的MySQL版本一樣，二進位制日誌檔案記錄的是日誌的邏輯SQL語句。

（2）在ROW格式下，二進位制日誌記錄的不再是簡單的SQL語句了，而是記錄表的行更改情況。基於ROW格式的複製類似於Oracle的物理Standby（當然，還是有些區別）。同時，對於上述提及的Statement格式下複製的問題給予瞭解決。MySQL 5.1版本開始，如果設定了binlog_format為ROW，你可以將InnoDB的事務隔離基本設為READ COMMITTED，以獲得更好的併發性。

（3）MIXED格式下，MySQL預設採用STATEMENT格式進行二進位制日誌檔案的記錄，但是在一些情況下會使用ROW格式，可能的情況有：

1）表的儲存引擎為NDB，這時對於表的DML操作都會以ROW格式記錄。

2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不確定函式。

3）使用了INSERT DELAY語句。

4）使用了使用者定義函式（UDF）。

5）使用了臨時表（temporary table）。

此外，binlog_format引數還有對於儲存引擎的限制，如表3-1所示。

表3-1   儲存引擎二進位制日誌格式支援情況

 

binlog_format是動態引數，因此可以在資料庫執行環境下進行更改，例如，我們可以將當前會話的binlog_format設為ROW，如：

  1. mysql >  set @@ session.binlog_format = 'ROW' ;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.  
  4. mysql >  select @@session.binlog_format;  
  5. +-------------------------------+  
  6. | @@session.binlog_format |  
  7. +-------------------------------+  
  8. | ROW                 |   
  9. +-------------------------------+  
  10. 1 row in set (0.00 sec) 

當然，也可以將全域性的binlog_format設定為你想要的格式。不過通常情況下，這個操作可能會帶來問題，執行時，請確保更改後不會對你的複製帶來影響。如：

  1. mysql >  set global  binlog_format = 'ROW' ;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.  
  4. mysql >  select @@global.binlog_format;  
  5. +------------------------------+  
  6. | @@global.binlog_format |  
  7. +------------------------------+  
  8. | ROW             |   
  9. +------------------------------+  
  10. 1 row in set (0.00 sec)  

通常情況下，我們將引數binlog_format設定為ROW，這可以為資料庫的恢復和複製帶來更好的可靠性。但是不能忽略一點的是，這會帶來二進位制檔案大小的增加，有些語句下的ROW格式可能需要更大的容量。比如我們有兩張一樣的表，大小都為100W，執行UPDATE操作，觀察二進位制日誌大小的變化：

  1. mysql >  select @@session.binlog_format\G;  
  2. *************************** 1. row ***************************  
  3. @@session.binlog_format: STATEMENT  
  4. 1 row in set (0.00 sec)  
  5.  
  6. mysql >  show master status\G;  
  7. *************************** 1. row ***************************  
  8.             File: test.000003  
  9.         Position: 106  
  10.     Binlog_Do_DB:   
  11. Binlog_Ignore_DB:   
  12. 1 row in set (0.00 sec)  
  13.  
  14. mysql >  update t1 set  username = upper (username);   
  15. Query OK, 89279 rows affected (1.83 sec)  
  16. Rows matched: 100000  Changed: 89279  Warnings: 0  
  17.  
  18. mysql >  show master status\G;  
  19. *************************** 1. row ***************************  
  20.             File: test.000003  
  21.         Position: 306  
  22.     Binlog_Do_DB:   
  23. Binlog_Ignore_DB:   
  24. 1 row in set (0.00 sec) 

3.2.4   二進位制日誌（4）

可以看到，在binlog_format格式為STATEMENT下，執行UPDATE語句二進位制日誌大小隻增加了200位元組（306-106）。如果我們使用ROW格式，同樣來操作t2表，可以看到：

  1. mysql >  set session  binlog_format = 'ROW' ;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.  
  4. mysql >  show master status\G;  
  5. *************************** 1. row ***************************  
  6.             File: test.000003  
  7.         Position: 306  
  8.     Binlog_Do_DB:   
  9. Binlog_Ignore_DB:   
  10. 1 row in set (0.00 sec)  
  11.  
  12. mysql >  update t2 set  username = upper (username);   
  13. Query OK, 89279 rows affected (2.42 sec)  
  14. Rows matched: 100000  Changed: 89279  Warnings: 0  
  15.  
  16. mysql >  show master status\G;  
  17. *************************** 1. row ***************************  
  18.             File: test.000003  
  19.         Position: 13782400  
  20.     Binlog_Do_DB:   
  21. Binlog_Ignore_DB:   
  22. 1 row in set (0.00 sec) 

這時你會驚訝地發現，同樣的操作在ROW格式下竟然需要13 782 094位元組，二進位制日誌檔案差不多增加了13MB，要知道t2表的大小也不超過17MB。而且執行時間也有所增加（這裡我設定了sync_binlog=1）。這就是因為，這時MySQL資料庫不再將邏輯的SQL操作記錄到二進位制日誌，而是記錄對於每行的更改記錄資訊。

上面的這個例子告訴我們，將引數binlog_format設定為ROW，對於磁碟空間要求有了一定的增加。而由於複製是採用傳輸二進位制日誌方式實現的，因此複製的網路開銷也有了增加。

二進位制日誌檔案的檔案格式為二進位制（好像有點廢話），不能像錯誤日誌檔案，慢查詢日誌檔案用cat、head、tail等命令來檢視。想要檢視二進位制日誌檔案的內容，須透過MySQL提供的工具mysqlbinlog。對於STATEMENT格式的二進位制日誌檔案，使用mysqlbinlog後，看到就是執行的邏輯SQL語句，如：

  1. [root@nineyou0-43 data]# mysqlbinlog  --start-position = 203  test.000004  
  2. /*!40019 SET @@ session.max_insert_delayed_threads = 0 */;  
  3. ....  
  4. #090927 15:43:11 server id 1  end_log_pos 376    Query    thread_id = 188     exec_time = 1       error_code = 0  
  5. SET  TIMESTAMP = 1254037391 /*!*/;  
  6. update t2 set  username = upper (username) where  id = 1  
  7. /*!*/;  
  8. # at 376  
  9. #090927 15:43:11 server id 1  end_log_pos 403    Xid  =  1009  
  10. COMMIT/*!*/;  
  11. DELIMITER ;  
  12. # End of log file  
  13. ROLLBACK /* added by mysqlbinlog */;  
  14. /*!50003 SET  COMPLETION_TYPE =@OLD_COMPLETION_TYPE*/; 

update t2 set username=upper（username） where id=1，這個可以看到日誌的記錄以SQL語句的方式（為了排版的方便，省去了一些開始的資訊）。在這個情況下，mysqlbinlog和Oracle LogMiner類似。但是如果這時使用ROW格式的記錄方式，則會發現mysqlbinlog的結果變得“不可讀”（unreadable），如：

  1. [root@nineyou0-43 data]# mysqlbinlog   --start-position = 1065  test.000004  
  2. /*!40019 SET @@ session.max_insert_delayed_threads = 0 */;  
  3. ......  
  4. # at 1135  
  5. # at 1198  
  6. #090927 15:53:52 server id 1  end_log_pos 1198   Table_map: 'member'.'t2' mapped to number 58  
  7. #090927 15:53:52 server id 1  end_log_pos 1378  Update_rows: table id 58 flags: STMT_END_F  
  8.  
  9. BINLOG '  
  10. EBq/ShMBAAAAPwAAAK4EAAAAADoAAAAAAAAABm1lbWJ lcgACdDIACgMPDw/+CgsPAQwKJAAoAEAA  
  11. /gJAAAAA  
  12. EBq/ShgBAAAAtAAAAGIFAAAQADoAAAAAAAEACv////8A /AEAAAALYWxleDk5ODh5b3UEOXlvdSA3  
  13. Y2JiMzI1MmJhNmI3ZTljNDIyZmFjNTMzNGQyMjA1NA FNLacPAAAAAABjEnpxPBIAAAD8AQAAAAtB  
  14. TEVYOTk4OFlPVQQ5eW91IDdjYmIzMjUyYmE2YjdlOW M0MjJmYWM1MzM0ZDIyMDU0AU0tpw8AAAAA  
  15. AGMSenE8EgAA  
  16. '/*!*/;  
  17. # at 1378  
  18. #090927 15:53:52 server id 1  end_log_pos 1405   Xid  =  1110  
  19. COMMIT/*!*/;  
  20. DELIMITER ;  
  21. # End of log file  
  22. ROLLBACK /* added by mysqlbinlog */;  
  23. /*!50003 SET  COMPLETION_TYPE =@OLD_COMPLETION_TYPE*/; 

3.2.4   二進位制日誌（5）

我們看不到執行的SQL語句，反而是一大串我們看不到的字元。其實只要加上引數-v或者-vv，就能清楚地看到執行的具體資訊了，-vv會比-v多顯示出更新的型別，這次我們加上-vv選項，得到：

  1. [root@nineyou0-43 data]# mysqlbinlog -vv   - -start-position = 1065  test.000004  
  2. ......  
  3. BINLOG '  
  4. EBq/ShMBAAAAPwAAAK4EAAAAADoAAAAAAAAABm1lbWJlcgA CdDIACgMPDw/+CgsPAQwKJAAoAEAA  
  5. /gJAAAAA  
  6. EBq/ShgBAAAAtAAAAGIFAAAQADoAAAAAAAEACv////8A/A EAAAALYWxleDk5ODh5b3UEOXlvdSA3  
  7. Y2JiMzI1MmJhNmI3ZTljNDIyZmFjNTMzNGQyMjA1NAFNLa cPAAAAAABjEnpxPBIAAAD8AQAAAAtB  
  8. TEVYOTk4OFlPVQQ5eW91IDdjYmIzMjUyYmE2YjdlOWM0MjJ mYWM1MzM0ZDIyMDU0AU0tpw8AAAAA  
  9. AGMSenE8EgAA  
  10. '/*!*/;  
  11. ### UPDATE member.t2  
  12. ### WHERE  
  13. ###   @ 1 1 =1 /* INT  meta = 0   nullable = 0   is_null = 0  */  
  14. ###   @ 2 = 'david'  /* VARSTRING(36)  meta = 36   nullable = 0   is_null = 0  */  
  15. ###   @ 3 = 'family'  /* VARSTRING(40)  meta = 40   nullable = 0   is_null = 0  */  
  16. ###   @ 4 = '7cbb3252ba6b7e9c422fac5334d22054'  /*  VARSTRING(64)  meta = 64   nullable = 0   is_null = 0  */  
  17. ###   @ 5 = 'M'  /* STRING(2)  meta = 65026   nullable = 0   is_null = 0  */  
  18. ###   @ 6 = '2009:09:13'  /* DATE  meta = 0   nullable = 0   is_null = 0  */  
  19. ###   @ 7 = '00:00:00'  /* TIME  meta = 0   nullable = 0   is_null = 0  */  
  20. ###   @ 8 = ''  /* VARSTRING(64)  meta = 64   nullable = 0   is_null = 0  */  
  21. ###   @ 9 = 0  /* TINYINT  meta = 0   nullable = 0   is_null = 0  */  
  22. ###   @ 10 = 2009 -08-11 16:32:35 /* DATETIME  meta = 0   nullable = 0   is_null = 0  */  
  23. ### SET  
  24. ###   @ 1 1 =1 /* INT  meta = 0   nullable = 0   is_null = 0  */  
  25. ###   @ 2 = 'DAVID'  /* VARSTRING(36)  meta = 36   nullable = 0   is_null = 0  */  
  26. ###   @ 3 = family  /* VARSTRING(40)  meta = 40   nullable = 0   is_null = 0  */  
  27. ###   @ 4 = '7cbb3252ba6b7e9c422fac5334d22054'  /* VA RSTRING(64)  meta = 64   nullable = 0   is_null = 0  */  
  28. ###   @ 5 = 'M'  /* STRING(2)  meta = 65026   nullable = 0   is_null = 0  */  
  29. ###   @ 6 = '2009:09:13'  /* DATE  meta = 0   nullable = 0   is_null = 0  */  
  30. ###   @ 7 = '00:00:00'  /* TIME  meta = 0   nullable = 0   is_null = 0  */  
  31. ###   @ 8 = ''  /* VARSTRING(64)  meta = 64   nullable = 0   is_null = 0  */  
  32. ###   @ 9 = 0  /* TINYINT  meta = 0   nullable = 0   is_null = 0  */  
  33. ###   @ 10 = 2009 -08-11 16:32:35 /* DATETIME  meta = 0   nullable = 0   is_null = 0  */  
  34. # at 1378  
  35. #090927 15:53:52 server id 1  end_log_pos 1405   Xid  =  1110  
  36. COMMIT/*!*/;  
  37. DELIMITER ;  
  38. # End of log file  
  39. ROLLBACK /* added by mysqlbinlog */;  
  40. /*!50003 SET  COMPLETION_TYPE =@OLD_COMPLETION_TYPE*/; 

現在mysqlbinlog向我們解釋了具體做的事情。可以看到，一句簡單的update t2 set username=upper（username）where id=1語句記錄為了對於整個行更改的資訊，這也解釋了為什麼前面我們更新了10萬行的資料，在ROW格式下，二進位制日誌檔案會增大了13MB。

3.3   套接字檔案

前面提到過，Unix系統下本地連線MySQL可以採用Unix域套接字方式，這種方式需要一個套接字（socke）檔案。套接字檔案可由引數socket控制。一般在/tmp目錄下，名為mysql.sock：

  1. mysql >  show variables like 'socket'\G;  
  2. *************************** 1. row ***************************  
  3. Variable_name: socket  
  4.       Value: /tmp/mysql.sock  
  5. 1 row in set (0.00 sec)  
  6. 3.4   pid檔案 

當MySQL例項啟動時，會將自己的程式ID寫入一個檔案中—該檔案即為pid檔案。該檔案可由引數pid_file控制。預設路徑位於資料庫目錄下，檔名為主機名.pid。

  1. mysql >  show variables like 'pid_file'\G;  
  2. *************************** 1. row ***************************  
  3. Variable_name: pid_file  
  4.        Value: /usr/local/mysql/data/xen-server.pid  
  5. 1 row in set (0.00 sec) 

3.4   pid檔案

當MySQL例項啟動時，會將自己的程式ID寫入一個檔案中—該檔案即為pid檔案。該檔案可由引數pid_file控制。預設路徑位於資料庫目錄下，檔名為主機名.pid。

3.5   表結構定義檔案

因為MySQL外掛式儲存引擎的體系結構的關係，MySQL對於資料的儲存是按照表的，所以每個表都會有與之對應的檔案（對比SQL Server是按照每個資料庫下的所有表或索引都存在mdf檔案中）。不論採用何種儲存引擎，MySQL都有一個以frm為字尾名的檔案，這個檔案記錄了該表的表結構定義。

frm還用來存放檢視的定義，如我們建立了一個v_a檢視，那麼對應地會產生一個v_a.frm檔案，用來記錄檢視的定義，該檔案是文字檔案，可以直接使用cat命令進行檢視：

  1. [root@xen-server test]# cat v_a.frm   
  2. TYPE = VIEW  
  3. query = select  'test'.'a'.'b' AS 'b' from 'test'.'a'  
  4. md5 = 4eda70387716a4d6c96f3042dd68b742  
  5. updatable = 1  
  6. algorithm = 0  
  7. definer_user = root  
  8. definer_host = localhost  
  9. suid = 2  
  10. with_check_option = 0  
  11. timestamp = 2010 -08-04 07:23:36  
  12. create-version = 1  
  13. source = select  * from a  
  14. client_cs_name = utf8  
  15. connection_cl_name = utf8_general_ci  
  16. view_body_utf8 = select  'test'.'a'.'b' AS 'b' from 'test'.'a' 

3.6   InnoDB儲存引擎檔案

之前介紹的檔案都是MySQL資料庫本身的檔案，和儲存引擎無關。除了這些檔案外，每個表儲存引擎還有其自己獨有的檔案。這一節將具體介紹和InnoDB儲存引擎密切相關的檔案，這些檔案包括重做日誌檔案、表空間檔案。

3.6.1   表空間檔案

InnoDB儲存引擎在儲存設計上模仿了Oracle，將儲存的資料按表空間進行存放。預設配置下，會有一個初始化大小為10MB、名為ibdata1的檔案。該檔案就是預設的表空間檔案（tablespace file）。你可以透過引數innodb_data_file_path對其進行設定。格式如下：

  1. innodb_data_file_path = datafile_spec1 [;datafile_spec2]... 

你也可以用多個檔案組成一個表空間，同時制定檔案的屬性，如：

  1. [mysqld]  
  2. innodb_data_file_path  = /db/ibdata1:2000M; /dr2/db/ibdata2:2000M:autoextend 

這裡將/db/ibdata1和/dr2/db/ibdata2兩個檔案用來組成表空間。若這兩個檔案位於不同的磁碟上，則可以對效能帶來一定程度的提升。兩個檔案的檔名後都跟了屬性，表示檔案idbdata1的大小為2000MB，檔案ibdata2的大小為2000MB，但是如果用滿了這2000MB後，該檔案可以自動增長（autoextend）。

設定innodb_data_file_path引數後，之後對於所有基於InnoDB儲存引擎的表的資料都會記錄到該檔案內。而透過設定引數innodb_file_per_table，我們可以將每個基於InnoDB儲存引擎的表單獨產生一個表空間，檔名為表名.ibd，這樣不用將所有資料都存放於預設的表空間中。下面這臺伺服器設定了innodb_file_per_table，可以看到：

  1. mysql >  show variables like 'innodb_file_per_table'\G;  
  2. *************************** 1. row ***************************  
  3. Variable_name: innodb_file_per_table  
  4.         Value: ON  
  5. 1 row in set (0.00 sec)  
  6.  
  7. mysql >  system ls -lh /usr/local/mysql/data/member/*  
  8. -rw-r-----  1 mysql mysql 8.7K 2009-02-24  / usr/local/mysql/data/member/ Profile.frm  
  9. -rw-r-----  1 mysql mysql 1.7G  9月 25 11:13 /usr/local/mysql/data/member/ Profile.ibd  
  10. -rw-rw----  1 mysql mysql 8.7K  9月 27 13:38  /usr/local/mysql/data/member/t1.frm  
  11. -rw-rw----  1 mysql mysql  17M  9月 27 13:40  /usr/local/mysql/data/member/t1.ibd  
  12. -rw-rw----  1 mysql mysql 8.7K  9月 27 15:42 /usr/local/mysql/data/member/t2.frm  
  13. -rw-rw----  1 mysql mysql  17M  9月 27 15:54 /usr/local/mysql/data/member/t2.ibd 

表Profile、t1、t2都是InnoDB的儲存引擎，由於設定引數innodb_file_per_table=ON，因此產生了單獨的.ibd表空間檔案。需要注意的是，這些單獨的表空間檔案僅儲存該表的資料、索引和插入緩衝等資訊，其餘資訊還是存放在預設的表空間中。圖3-1顯示了InnoDB儲存引擎對於檔案的儲存方式：

 
圖3-1   InnoDB表儲存引擎檔案
【責任編輯：  TEL：（010）68476606】

3.6.2   重做日誌檔案（1）

預設情況下會有兩個檔案，名稱分別為ib_logfile0和ib_logfile1。MySQL官方手冊中將其稱為InnoDB儲存引擎的日誌檔案，不過更準確的定義應該是重做日誌檔案（redo log file）。為什麼強調是重做日誌檔案呢？因為重做日誌檔案對於InnoDB儲存引擎至關重要，它們記錄了對於InnoDB儲存引擎的事務日誌。

重做日誌檔案的主要目的是，萬一例項或者介質失敗（media failure），重做日誌檔案就能派上用場。如資料庫由於所在主機掉電導致例項失敗，InnoDB儲存引擎會使用重做日誌恢復到掉電前的時刻，以此來保證資料的完整性。

每個InnoDB儲存引擎至少有1個重做日誌檔案組（group），每個檔案組下至少有2個重做日誌檔案，如預設的ib_logfile0、ib_logfile1。為了得到更高的可靠性，你可以設定多個映象日誌組（mirrored log groups），將不同的檔案組放在不同的磁碟上。日誌組中每個重做日誌檔案的大小一致，並以迴圈方式使用。InnoDB儲存引擎先寫重做日誌檔案1，當達到檔案的最後時，會切換至重做日誌檔案2，當重做日誌檔案2也被寫滿時，會再切換到重做日誌檔案1中。圖3-2顯示了一個擁有3個重做日誌檔案的重做日誌檔案組。

 
圖3-2   日誌檔案組

引數innodb_log_file_size、innodb_log_files_in_group、innodb_mirrored_log_groups、innodb_log_group_home_dir影響著重做日誌檔案的屬性。引數innodb_log_file_size指定了重做日誌檔案的大小；innodb_log_files_in_group指定了日誌檔案組中重做日誌檔案的數量，預設為2；innodb_mirrored_log_groups指定了日誌映象檔案組的數量，預設為1，代表只有一個日誌檔案組，沒有映象；innodb_log_group_home_dir指定了日誌檔案組所在路徑，預設在資料庫路徑下。以下顯示了一個關於重做日誌組的配置：

  1. mysql >  show variables like 'innodb%log%'\G;  
  2. *************************** 1. row ***************************  
  3. Variable_name: innodb_flush_log_at_trx_commit  
  4.         Value: 1  
  5. *************************** 2. row ***************************  
  6. Variable_name: innodb_locks_unsafe_for_binlog  
  7.         Value: OFF  
  8. *************************** 3. row ***************************  
  9. Variable_name: innodb_log_buffer_size  
  10.         Value: 8388608  
  11. *************************** 4. row ***************************  
  12. Variable_name: innodb_log_file_size  
  13.         Value: 5242880  
  14. *************************** 5. row ***************************  
  15. Variable_name: innodb_log_files_in_group  
  16.         Value: 2  
  17. *************************** 6. row ***************************  
  18. Variable_name: innodb_log_group_home_dir  
  19.         Value: ./  
  20. *************************** 7. row ***************************  
  21. Variable_name: innodb_mirrored_log_groups  
  22.         Value: 1  
  23. 7 rows in set (0.00 sec) 

重做日誌檔案的大小設定對於MySQL資料庫各方面還是有影響的。一方面不能設定得太大，如果設定得很大，在恢復時可能需要很長的時間；另一方面又不能太小了，否則可能導致一個事務的日誌需要多次切換重做日誌檔案。在錯誤日誌中可能會看到如下警告：

  1. 090924 11:39:44  InnoDB: ERROR: the age of the  last checkpoint is 9433712,  
  2. InnoDB: which exceeds the log group capacity 9433498.  
  3. InnoDB: If you are using big BLOB or TEXT rows, you must set the  
  4. InnoDB: combined size of log files at least 10  times bigger than the  
  5. InnoDB: largest such row.  
  6. 090924 11:40:00  InnoDB: ERROR: the age of the  last checkpoint is 9433823,  
  7. InnoDB: which exceeds the log group capacity 9433498.  
  8. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
  9. InnoDB: combined size of log files at least 10  times bigger than the  
  10. InnoDB: largest such row.  
  11. 090924 11:40:16  InnoDB: ERROR: the age of the  last checkpoint is 9433645,  
  12. InnoDB: which exceeds the log group capacity 9433498.  
  13. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
  14. InnoDB: combined size of log files at least 10  times bigger than the  
  15. InnoDB: largest such row. 


3.6.2   重做日誌檔案（2）

上面錯誤集中在InnoDB: ERROR: the age of the last checkpoint is 9433645,InnoDB: which exceeds the log group capacity 9433498。這是因為重做日誌有一個capacity變數，該值代表了最後的檢查點不能超過這個閾值，如果超過則必須將緩衝池（innodb buffer pool）中重新整理列表（flush list）中的部分髒資料頁寫回磁碟。

也許有人會問，既然同樣是記錄事務日誌，那和我們之前的二進位制日誌有什麼區別？首先，二進位制日誌會記錄所有與MySQL有關的日誌記錄，包括InnoDB、MyISAM、Heap等其他儲存引擎的日誌。而InnoDB儲存引擎的重做日誌只記錄有關其本身的事務日誌。其次，記錄的內容不同，不管你將二進位制日誌檔案記錄的格式設為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關於一個事務的具體操作內容。而InnoDB儲存引擎的重做日誌檔案記錄的關於每個頁（Page）的更改的物理情況（如表3-2所示）。此外，寫入的時間也不同，二進位制日誌檔案是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日誌條目（redo entry）被寫入重做日誌檔案中。

表3-2   重做日誌結構

 

在第2章中已經提到，對於寫入重做日誌檔案的操作不是直接寫，而是先寫入一個重做日誌緩衝（redo log buffer）中，然後根據按照一定的條件寫入日誌檔案。圖3-3很好地表示了這個過程。


 
圖3-3   重做日誌寫入過程

上面提到了從日誌緩衝寫入磁碟上的重做日誌檔案是按一定條件的，那這些條件有哪些呢？第2章分析了主執行緒（master thread），知道在主執行緒中每秒會將重做日誌緩衝寫入磁碟的重做日誌檔案中，不論事務是否已經提交。另一個觸發這個過程是由引數innodb_ flush_log_at_trx_commit控制，表示在提交（commit）操作時，處理重做日誌的方式。

引數innodb_flush_log_at_trx_commit可設的值有0、1、2。0代表當提交事務時，並不將事務的重做日誌寫入磁碟上的日誌檔案，而是等待主執行緒每秒的重新整理。而1和2不同的地方在於：1是在commit時將重做日誌緩衝同步寫到磁碟；2是重做日誌非同步寫到磁碟，即不能完全保證commit時肯定會寫入重做日誌檔案，只是有這個動作。

3.7   小結

本章介紹了與MySQL資料庫相關的一些檔案，並瞭解了檔案可以分為MySQL資料庫檔案以及和各儲存引擎有關的檔案。與MySQL資料庫有關的檔案中，錯誤檔案和二進位制日誌檔案非常重要。當MySQL資料庫發生任何錯誤時，DBA首先就應該去檢視錯誤檔案，從檔案提示的內容中找出問題的所在。當然，錯誤檔案不僅記錄了錯誤的內容，也記錄了警告的資訊，透過一些警告也有助於DBA對於資料庫和儲存引擎的最佳化。

二進位制日誌的作用非常關鍵，可以用來進行point in time的恢復以及複製（replication）環境的搭建。因此，建議在任何時候時都啟用二進位制日誌的記錄。從MySQL 5.1開始，二進位制日誌支援STATEMENT、ROW、MIX三種格式，用來更好地同步資料庫。DBA應該十分清楚三種不同格式之間的差異。

本章的最後介紹了和InnoDB儲存引擎相關的檔案，包括表空間檔案和重做日誌檔案。表空間檔案是用來管理InnoDB儲存引擎的儲存，分為共享表空間和獨立表空間。重做日誌非常重要，用來記錄InnoDB儲存引擎的事務日誌，也因為重做日誌的存在，才使得InnoDB儲存引擎可以提供可靠的事務。




MySQL8.0: Serialized Dictionary Information(SDI) 檔案淺析

MySQL8.0，多了一些sdi檔案，開啟這類檔案察看，卻發現內容就是一個JSON。

內容如下：
{"mysqld_version_id":80017,"dd_version":80017,"sdi_version":80016,"dd_object_type":"Table","dd_object":{"name":"ss_site","mysql_version_id":80017,"created":20191105032845,"last_altered":20191105032845...}

檢察了下，發現就是描述表結構的一些後設資料 ：

sdi是Serialized Dictionary Infor ion的縮寫，是MySQL8.0重新 資料詞典後引入的新產物。MySQL8.0已經開始統一使用InnoDB儲存引擎來儲存表的後設資料資訊，但對於非InnoDB引擎，MySQL還提供了另外一種可讀的檔案格式來描述表的後設資料資訊，在磁碟上以 $tbname.sdi的命名儲存在資料庫目錄下。

而對於InnoDB表就不會建立sdi檔案，而是將sdi資訊冗餘儲存到表 中（臨時表空間和undo表空間除外）。

那麼問題就來了，既然已經有了新的資料詞典系統，為什麼還需要冗餘的sdi資訊呢，這主要從幾個方面考慮：

1）當資料詞典損壞時，可能無法啟動，在這種情況下可以透過冗餘的sdi資訊，將資料複製到另外一個例項上，並進行資料重建。
2）可以離線的檢視錶的定義
3）還可以基於此實現簡單的資料轉儲
4）MySQL Cluster/Ndb也依賴sdi資訊來進行後設資料同步

官方提供了一個工具叫做ibd2sdi，可以離線的將ibd檔案中的冗餘儲存的sdi資訊提取出來，並以json的格式輸出到終端。

比如下例[預設情況下列印所有資訊包含所有的列，索引的各個屬性]：

ibd2sdi data/test/t1.ibd

當然也可以只查詢部分後設資料資訊，更多的引數可以透過如下命令獲取。
ib2sdi --help



About Me

.............................................................................................................................................

● 本文整理自網路

● 本文在itpub（ http://blog.itpub.net/26736162/abstract/1/）、部落格園（ http://www.cnblogs.com/lhrbest）和個人微信公眾號（ xiaomaimiaolhr）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址： http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號： 230161599（滿） 、618766405

● 微信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由

● 於 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用 微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）， 學習最實用的資料庫技術。

   小麥苗的微信公眾號      小麥苗的DBA寶典QQ群1     小麥苗的 DBA寶典QQ群2        小麥苗的微店

.............................................................................................................................................

MySQL檔案概述
DBA筆試面試講解群1
DBA筆試面試講解群2
歡迎與我聯絡



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2144040/，如需轉載，請註明出處，否則將追究法律責任。

相關文章