MySQL 體繫結構以及各種檔案類型學習匯總，mysql體繫結構

MySQL 體繫結構以及各種檔案類型學習匯總，mysql體繫結構
1，mysql體繫結構

由資料庫和資料庫執行個體組成，是單進場多線程架構。

資料庫：物理作業系統檔案或者其它檔案的集合，在mysql中，資料庫檔案可以是frm、myd、myi、ibd等結尾的檔案，當使用ndb儲存引擎時候，不是os檔案，是存放於記憶體中的檔案。

資料庫執行個體：由資料庫後台進程/線程以及一個共用記憶體區組成，共用記憶體可以被啟動並執行後台進程/線程所共用。

2，mysql檔案類型

Mysql主要檔案類型有如下幾種：

參數檔案：mysql執行個體啟動的時候在哪裡可以找到資料庫檔案，並且指定某些初始化參數，這些參數定義了某種記憶體結構的大小等設定，還介紹了參數類型以及定義範圍。

記錄檔：記錄mysql對某種條件做出響應時候寫入的檔案。

Socket檔案：當用linux的mysql命令列視窗登入的時候需要的檔案

Pid檔案：mysql執行個體的進程檔案

Mysql表結構檔案：存放mysql表結構定義檔案

儲存引擎檔案：記錄儲存引擎資訊的檔案。

 

 

3，參數檔案my.cnf

Ø  Mysql執行個體啟動時，會先讀取配置參數檔案my.cnf

Ø  尋找my.cnf位置

         （1）：預設情況： mysql--help|grep my.cnf

         （2）：後台進程去找：ps–eaf|grep mysql

         （3）：全域搜尋：find /-name my.cnf  

Ø  可以用vi直接維護修改裡面的參數值

         （1）dynamic ：可以通過set進行即時修改  

         （2）static，只能在my.cnf裡面修改，需要restart生效

 

Mysql參數檔案中的參數可以分為2種類型：動態（dynamic）參數和靜態參數（staitic）

動態參數意味著可以在mysql執行個體運行中進行修改，set global sort_buffer_size=32999999;修改後，別的connection重新進行串連就可以生效了。

生效範圍分為：global和session。

 

 

靜態說明在整個mysql執行個體運行期間不得進行修改，就類似一個唯讀read only

 

 

4，記錄檔

記錄檔記錄了影響mysql資料庫的各種類型活動，常見的記錄檔有錯誤記錄檔、二進位日誌、慢查詢日誌、全查詢日誌、redo日誌、undo日誌

 

5、錯誤記錄檔

錯誤記錄檔對mysql的啟動、運行、關閉過程進行了記錄，mysql dba在遇到問題時候，第一時間應該查看這個錯誤記錄檔檔案，該檔案不但記錄了出錯資訊，還記錄了一些警告資訊以及正確資訊，這個error記錄檔類似於oracle的alert檔案，只不過預設情況下是以error結尾。可以通過show variables like 'log_error';

可以看到錯誤檔案的檔案名稱為伺服器的主機名稱。當然也可以在my.cnf裡面設定錯誤記錄檔檔案的路徑：

Vim my.cnf

log-error=/usr/local/mysql/mysqld.log

 

我們可以在錯誤記錄檔檔案裡面看到一些資料庫啟動資訊，以及警示資訊還有就是報錯資訊

 

6，慢查詢日誌slow log

 

慢查詢日誌就是記錄運行較慢的sql語句資訊，給sql語句的最佳化帶來很好的協助，可以設定一個閥值，將已耗用時間超過該閥值的sql語句的運行資訊都記錄到slow log日誌裡面去。該閥值可以通過long_query_time來設定，也可以設定到毫秒微秒：

但是需要注意一點：對於已耗用時間等於該閥值的，就不會記錄在內了。

 

另外一個參數是log_queries_not_using_indexes，如果啟動並執行sql沒有使用索引，只要超過閥值了也會記錄在慢查詢日誌裡面的。

 

          long_query_time=0 （記錄所有sql可以做審計） ，dba可以通過這個審計來推動業務的發展，可以知道哪些業務開展的好那些業務開展的不好，通過慢sql可以分析出哪些應用效能較差需要最佳化改進，dba的最大職能以及貢獻就在於通過對資料庫的維護來推動業務的發展和進步。從資料到業務，這是我們需要一直努力的方向。

 

 

慢查詢日誌還可以記錄在table裡面，

Slow_log表，也可以將慢查詢日誌放入一張表裡面

          show variables like ‘log_output’;查看如果是file就存放在slow log裡面，如果是table就在slow_log表裡面。

 

 

7、全查詢日誌

記錄了對mysql資料庫所有的請求資訊，不論這些請求資訊是否得到了正確的執行，預設檔案名稱為主機名稱.log，你可以看到對access denied的請求。

 

Database Audit+ 問題排查跟蹤（損失3%-5%效能）

 

 

8，二進位日誌

記錄了對資料庫進行變更的操作，但是不包括select操作以及show操作，因為這類操作對資料庫本身沒有沒有修改，如果你還想記錄select和show的話，你就需要查看前面的全查詢日誌，另外binlog還包括了執行資料庫更改操作時間和執行時間等資訊。

二進位的主要作用有如下2個：

（1）：恢複 recovery。某些資料的恢複需要二進位日誌，在全庫檔案恢複後，可以在此基礎上通過二進位日誌進行point-to-time的恢複。

（2）：複製（replication）。其原理和恢複類似，通過複製和執行二進位日誌使得一台遠端mysql資料庫（slave）於一台mysql資料庫（master）進行即時同步。

 

通過在my.cnf裡面設定log-bin =/home/data/mysql/binlog/mysql-bin.log生效，預設是在資料目錄datadir下面

 

binlog日誌參數：

max_binlog_size：指定了單個二進位檔案的最大值，如果超過了該值，就會產生新的記錄檔，尾碼名+1，並且記錄到.index檔案裡面。預設值是1G，不過從多年的dba生涯總結來說，64M是通用的大小設定。

 

binlog_cache_size：

使用innodb儲存引擎時候，所有未提交uncommitted的二進位日誌會被記錄到一個緩衝中，等該事務提交時committed直接將緩衝中的二進位日誌寫入二進位記錄檔裡面，而該緩衝的大小就由binlog_cache_size來決定，這個緩衝區是基於session的，也就是每一個線程需要事務的時候，mysql都會分配一個binlog_cache_size的緩衝，因此改值設定需要非常小心，不能設定過大，免得記憶體溢出了。

 

sync_binlog：

sync_binlog=N，參數最佳化介紹過，大概就是表示每次寫緩衝N次就同步到磁碟檔案中，如果將N設定為1的話，每次都會寫入binlog磁碟檔案中，這是最保險最安全的，如果N>1，在意外發生的時候，就表示會有N-1個dml沒有被寫入binlog中，有可能就會發生主動資料不一致的情況。

 

binlog-do-db、binlog-ingore-db：

表示需要寫入或者忽略寫入哪些庫的日誌，預設為空白，表示可以將所有庫的日誌寫入到二進位檔案裡面。

 

log-slave-update：

啟用從機伺服器上的slave日誌功能，使這台電腦可以用來構成一個鏡像鏈(A->B->C)  ，可以讓從庫上面產生二進位記錄檔，在從庫上再掛載一個從庫。

 

binlog-format：日誌格式

有statement、row、mixed格式

Statement：每一條會修改資料的sql都會記錄在binlog中。

優點：不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能。(相比row能節約多少效能與日誌量，這個取決於應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日誌量還小於Statement產生的日誌量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日誌，因此在考慮是否使用ROW格式日誌時應該跟據應用的實際情況，其所產生的日誌量會增加多少，以及帶來的IO效能問題。)

缺點：由於記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行時候相同的結果。另外mysql 的複製,像一些特定函數功能，slave可與master上要保持一致會有很多相關問題(如sleep()函數，last_insert_id()，以及user-definedfunctions(udf)會出現問題).

2.Row:不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。

優點： binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以rowlevel的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的預存程序，或function，以及trigger的調用和觸發無法被正確複製的問題

缺點:所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行altertable之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中。

3.Mixedlevel: 是以上兩種level的混合使用，一般的語句修改使用statment格式儲存binlog，如一些函數，statement無法完成主從複製的操作，則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊rowlevel模式也被做了最佳化，並不是所有的修改都會以rowlevel來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句，還是會記錄所有行的變更。

 

使用以下函數的語句也無法被複製：

*LOAD_FILE()

*UUID()

*USER()

*FOUND_ROWS()

*SYSDATE() (除非啟動時啟用了--sysdate-is-now 選項)

同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖

 

 row、mixed

 

9，通訊端socket檔案

Linux系統下 本地串連mysql可以採用linux域通訊端socket方式 ，需要一個通訊端socket發檔案，可以有參數socket控制，一般預設在/tmp目錄下，也可以通過如下2種方式查看：

1，  ps -eaf|grep mysql |grep socket

[root@data01 binlog]# ps -eaf|grep mysql|grep socket

mysql    3152  1979  0 Feb28 ?        00:00:02 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/home/data/mysql/data--plugin-dir=/usr/local/mysql/lib/plugin --user=mysql--log-error=/usr/local/mysql/mysqld.log --open-files-limit=8192--pid-file=/usr/local/mysql/mysqld.pid --socket=/usr/local/mysql/mysql.sock--port=3306

[root@data01 binlog]#

2，

3，my.cnf

socket         = /usr/local/mysql/mysql.sock

 

10，pid檔案

當mysql執行個體啟動的時候，會將自己的進程id寫入一個檔案中，該檔案即為pid檔案，由參數pid_file控制，預設路徑位於資料庫目錄下，可以通過以下三種方式查看：

1)、show variableslike 'pid_file';

2)、ps -eaf|grepmysql |grep pid

 

3)、My.cnf  （pid-file        = /usr/local/mysql/mysqld.pid）

 

11，表結構檔案

*.frm

*.ibd

 

12，innodb隱藏檔

innodb儲存引擎在儲存設計上模仿了oracle，該檔案就是預設的資料表空間檔案，可以通過參數innodb_data_file_path來進行設定，格式如下：

         innodb_data_file_path= IBdata1:128M;IBdata2:128M:autoextend

可以用多個檔案組成一個資料表空間，同時制定檔案的屬性，

IBdata1和IBdata2位於不同的磁碟組上，則可以對效能帶來一定程度的提升。檔案後面的屬性工作表示檔案大小，autoextend表示還可以擴充。

但是如果設定了innodb_file_per_table為true後，那麼表資料檔案就會在單獨的.ibd檔案裡面，不在這個ibdata檔案裡面了。

 

13，redo檔案

所有的資料庫都是日誌先行，先寫日誌，再寫資料檔案，所以才會有redo log的規則。

 

預設情況下會有2個檔案名稱分別為ib_logfile0 和ib_logfile1 ，在mysql資料庫目錄下可以看到這2個檔案，這個對innodb儲存引擎非常重要，因為它們記錄了對於innodb儲存引擎的交易記錄。

 

重做記錄檔的主要目的是：萬一執行個體或者介質失敗media failure，重做日誌就可以派上用場，如果資料庫由於所在主機掉電導致執行個體失敗，innodb儲存引擎會使用重做日誌恢複到掉電前的時刻，以此來保證資料的完整性。

 

每個innodb儲存引擎至少有一個重做日誌組，每組至少有2個重做記錄檔，如預設的ib_logfile0 和ib_logfile1，為了得到更高的可靠性，你可以設定多個組，也可以將每組放在不同的磁碟上面，來提高效能

 

LSN logsequence number：

遞增產生的，可以唯一的標記一條redo日誌，對於我們資料庫故障恢複都是非常重要的，可以唯一定位元據庫運行狀態，至於如何定位細節，大家可以去看下redo、undo的源碼，源碼：在"storage/innobase/include/log0log.h"

 

 

查看參數設定：show variables like 'innodb%log%';

 

 

14，undo日誌

存在於共用資料表空間ibdata1裡面，有一個復原段地址，裡面存放了頭資訊，配置頭資訊，段的頭資訊，裡面儲存了與redo相反的資料更新操作，如果rollback的話，就把undo段裡面資料回寫到資料檔案裡面。

 

如果用了獨立資料表空間的話，則直接儲存到表私自的空間中，而不儲存到共用資料表空間中。在innodb儲存引擎中，undo log用來完成事務的復原以及MVCC的功能

 

Redo與undo他們並不是各自獨立沒有關係的，他們是有關聯的，交替合作來保證資料的一致性和安全性，具體細節大家可以參考何博士的文章，關於redo、undo，參考何博士的資料： http://hedengcheng.com/?p=489

 

何博士在資料庫領域中，是非常權威的，他的文章都是乾貨，非常值得去學習的，大家盡量抽空去學習一下。

PS：資料來源於網路整理收集，很久以前整理的，出處不記得了，如果重複雷同，見諒。

 ----------------------------------------------------------------------------------------------------------------
<著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!>
原部落格地址：       http://blog.itpub.net/26230597/viewspace-1447304/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------