>$ mysql --help | grep my.cnf

?參數(shù)文件：告訴MySQL實(shí)例啟動時在哪里可以找到數(shù)據(jù)庫文件，并且指定某些初始化參數(shù)，這些參數(shù)定義了某種內(nèi)存結(jié)構(gòu)的大小等設(shè)置，還會介紹各種參數(shù)的類型。
?日志文件：用來記錄MySQL實(shí)例對某種條件做出響應(yīng)時寫入的文件，如錯誤日志文件、二進(jìn)制日志文件、慢查詢?nèi)罩疚募?、查詢?nèi)罩疚募取?br> ?socket文件：當(dāng)用UNIX域套接字方式進(jìn)行連接時需要的文件。
?pid文件：MySQL實(shí)例的進(jìn)程ID文件。
?MySQL表結(jié)構(gòu)文件：用來存放MySQL表結(jié)構(gòu)定義文件。
?存儲引擎文件：因?yàn)镸ySQL表存儲引擎的關(guān)系，每個存儲引擎都會有自己的文件來保存各種數(shù)據(jù)。這些存儲引擎真正存儲了記錄和索引等數(shù)據(jù)。

參數(shù)文件

MySQL數(shù)據(jù)庫中的參數(shù)可以分為兩類：
?動態(tài)（dynamic）參數(shù)
?靜態(tài)（static）參數(shù)
動態(tài)參數(shù)意味著可以在MySQL實(shí)例運(yùn)行中進(jìn)行更改，靜態(tài)參數(shù)說明在整個實(shí)例生命周期內(nèi)都不得進(jìn)行更改，就好像是只讀（read only）的。

日志文件

?錯誤日志（error log）
?二進(jìn)制日志（binlog）
?慢查詢?nèi)罩荆╯low query log）
?查詢?nèi)罩荆╨og）

錯誤日志

>$ show variables like "log_error"

慢查詢?nèi)罩?/h5>

>$ show variables like "long_query_time"

>$ show variables like "slow_query_log"

查詢?nèi)罩?/h5>

查詢?nèi)罩旧踔劣涗浟藢ccess denied的請求，即對于未能正確執(zhí)行的SQL語句，查詢?nèi)罩疽矔M(jìn)行記錄。

二進(jìn)制日志

二進(jìn)制日志（binary log）記錄了對MySQL數(shù)據(jù)庫執(zhí)行更改的所有操作，但是不包括SELECT和SHOW這類操作，因?yàn)檫@類操作對數(shù)據(jù)本身并沒有修改。然而，若操作本身并沒有導(dǎo)致數(shù)據(jù)庫發(fā)生變化，那么該操作可能也會寫入二進(jìn)制日志。

二進(jìn)制日志主要有以下幾種作用：
?恢復(fù)（recovery）：某些數(shù)據(jù)的恢復(fù)需要二進(jìn)制日志，例如，在一個數(shù)據(jù)庫全備文件恢復(fù)后，用戶可以通過二進(jìn)制日志進(jìn)行point-in-time的恢復(fù)。
?復(fù)制（replication）：其原理與恢復(fù)類似，通過復(fù)制和執(zhí)行二進(jìn)制日志使一臺遠(yuǎn)程的MySQL數(shù)據(jù)庫（一般稱為slave或standby）與一臺MySQL數(shù)據(jù)庫（一般稱為master或primary）進(jìn)行實(shí)時同步。
?審計（audit）：用戶可以通過二進(jìn)制日志中的信息來進(jìn)行審計，判斷是否有對數(shù)據(jù)庫進(jìn)行注入的攻擊。

套接字文件

在UNIX系統(tǒng)下本地連接MySQL可以采用UNIX域套接字方式，這種方式需要一個套接字（socket）文件。套接字文件可由參數(shù)socket控制。一般在/tmp目錄下，名為mysql.sock：
（很少看到解釋）

pid文件

當(dāng)MySQL實(shí)例啟動時，會將自己的進(jìn)程ID寫入一個文件中——該文件即為pid文件。該文件可由參數(shù)pid_file控制，默認(rèn)位于數(shù)據(jù)庫目錄下，文件名為主機(jī)名.pid

表結(jié)構(gòu)定義文件

不論表采用何種存儲引擎，MySQL都有一個以frm為后綴名的文件，這個文件記錄了該表的表結(jié)構(gòu)定義。

InnoDB存儲引擎文件

InnoDB采用將存儲的數(shù)據(jù)按表空間（tablespace）進(jìn)行存放的設(shè)計。在默認(rèn)配置下會有一個初始大小為10MB，名為ibdata1的文件。該文件就是默認(rèn)的表空間文件（tablespace file），用戶可以通過參數(shù)innodb_data_file_path對其進(jìn)行設(shè)置。

若設(shè)置了參數(shù)innodb_file_per_table，則用戶可以將每個基于InnoDB存儲引擎的表產(chǎn)生一個獨(dú)立表空間。獨(dú)立表空間的命名規(guī)則為：表名.ibd。通過這樣的方式，用戶不用將所有數(shù)據(jù)都存放于默認(rèn)的表空間中。

單獨(dú)的表空間文件僅存儲該表的數(shù)據(jù)、索引和插入緩沖BITMAP等信息，其余信息還是存放在默認(rèn)的表空間中。

InnoDB表存儲引擎文件

重做日志文件

在默認(rèn)情況下，在InnoDB存儲引擎的數(shù)據(jù)目錄下會有兩個名為ib_logfile0和ib_logfile1的文件。在MySQL官方手冊中將其稱為InnoDB存儲引擎的日志文件，不過更準(zhǔn)確的定義應(yīng)該是重做日志文件（redo log file）。為什么強(qiáng)調(diào)是重做日志文件呢？因?yàn)橹刈鋈罩疚募τ贗nnoDB存儲引擎至關(guān)重要，它們記錄了對于InnoDB存儲引擎的事務(wù)日志。

每個InnoDB存儲引擎至少有1個重做日志文件組（group），每個文件組下至少有2個重做日志文件，如默認(rèn)的ib_logfile0和ib_logfile1。為了得到更高的可靠性，用戶可以設(shè)置多個的鏡像日志組（mirrored log groups），將不同的文件組放在不同的磁盤上，以此提高重做日志的高可用性。

重做日志文件的大小設(shè)置對于InnoDB存儲引擎的性能有著非常大的影響。一方面重做日志文件不能設(shè)置得太大，如果設(shè)置得很大，在恢復(fù)時可能需要很長的時間；另一方面又不能設(shè)置得太小了，否則可能導(dǎo)致一個事務(wù)的日志需要多次切換重做日志文件。此外，重做日志文件太小會導(dǎo)致頻繁地發(fā)生async checkpoint，導(dǎo)致性能的抖動。

重做日志有一個capacity變量，該值代表了最后的檢查點(diǎn)不能超過這個閾值，如果超過則必須將緩沖池（innodb buffer pool）中臟頁列表（flush list）中的部分臟數(shù)據(jù)頁寫回磁盤，這時會導(dǎo)致用戶線程的阻塞。

首先，二進(jìn)制日志會記錄所有與MySQL數(shù)據(jù)庫有關(guān)的日志記錄，包括InnoDB、MyISAM、Heap等其他存儲引擎的日志。而InnoDB存儲引擎的重做日志只記錄有關(guān)該存儲引擎本身的事務(wù)日志。
其次，記錄的內(nèi)容不同，無論用戶將二進(jìn)制日志文件記錄的格式設(shè)為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關(guān)于一個事務(wù)的具體操作內(nèi)容，即該日志是邏輯日志。而InnoDB存儲引擎的重做日志文件記錄的是關(guān)于每個頁（Page）的更改的物理情況。
此外，寫入的時間也不同，二進(jìn)制日志文件僅在事務(wù)提交前進(jìn)行提交，即只寫磁盤一次，不論這時該事務(wù)多大。而在事務(wù)進(jìn)行的過程中，卻不斷有重做日志條目（redo entry）被寫入到重做日志文件中。

重做日志寫入過程

重做日志條目是由4個部分組成：
?redo_log_type占用1字節(jié)，表示重做日志的類型
?space表示表空間的ID，但采用壓縮的方式，因此占用的空間可能小于4字節(jié)
?page_no表示頁的偏移量，同樣采用壓縮的方式
?redo_log_body表示每個重做日志的數(shù)據(jù)部分，恢復(fù)時需要調(diào)用相應(yīng)的函數(shù)進(jìn)行解析

二進(jìn)制日志的作用非常關(guān)鍵，可以用來進(jìn)行point in time的恢復(fù)以及復(fù)制（replication）環(huán)境的搭建。

問題

二進(jìn)制文件、undo、redo，這些文件有什么關(guān)系

• 首先，范圍不同。二進(jìn)制文件記錄所有與MySQL相關(guān)的日志記錄，包括InnoDB，MyISAM，Heap等存儲引擎的日志。而InnoDB的重做日志只記錄InnoDB相關(guān)的事務(wù)日志。
• 其次，內(nèi)容不同。二進(jìn)制文件記錄的是關(guān)于一個事務(wù)的具體操作內(nèi)容，而InnoDB的重做日志記錄每個數(shù)據(jù)頁（page）更改的物理情況。
• 寫入的時間不同。二進(jìn)制文件在事務(wù)提交之前記錄，在事務(wù)進(jìn)行過程中，不斷有重做日志條目寫入重做日志文件中。

表空間文件

.frm是描述了表的結(jié)構(gòu)，.MYD保存了表的數(shù)據(jù)記錄，*.MYI則是表的索引
假設(shè)我們使用系統(tǒng)默認(rèn)的參數(shù)啟動，則會有兩個tablespace，

第一個tablespace包括一個文件ibdata1，這個也可以稱為系統(tǒng)表空間，
另外一個space包括兩個日志文件(iblogfile0, iblogfile1)

ibdata1文件是InnoDB存儲引擎的共享表空間文件，該文件中主要存儲著下面這些數(shù)據(jù)：

• data dictionary
• double write buffer
• insert buffer/change buffer
• rollback segments
• undo space
• Foreign key constraint system tables

無論是否啟用了 innodb_file_per_table = 1，ibdata1文件都必須存在，因?yàn)樗仨毚鎯ι鲜?InnoDB 引擎所依賴&必須的數(shù)據(jù)，尤其是上面加粗標(biāo)識的 rollback segments 和 undo space，它倆是引起 ibdata1 文件大小增加的最大原因

一些表空間的基礎(chǔ)知識

共享表空間ibdata和用戶表空間是不一樣的，因?yàn)樗枰鎯Ω嗳值囊恍┬畔?，例如doublewrite,undo等等，所以共享表空間擁有更多的段

每個表空間都有一個唯一space id，因?yàn)楹芏嗟胤蕉夹枰褂玫竭@個id，例如內(nèi)存數(shù)據(jù)刷到磁盤時，需要使用這個space id來尋找表空間文件．InnoDB總有一個"系統(tǒng)空間"，即共享表空間，這個表系統(tǒng)表空間的space id始終為0．

頁空間結(jié)構(gòu)

一個表空間文件被分為一個個16kb的頁，每個頁都有一個32位序號(page number)，通常稱為偏移量，即離表空間初始位置的偏移量．因?yàn)槊總€頁大小為16kb，所以第0個頁的偏移量為0，第一個頁的偏移量為16384等等．因?yàn)?2位的最大值為2^{32，所以一個表空間的最大值為2}32*16kb=64TB．

共享表空間

共享表空間space id為0，包含了很多分配在固定偏移量上的頁，用來存儲和InnoDB操作相關(guān)的大量信息．系統(tǒng)表空間和其他空間一樣，也有FSP_HDR,IBUF_BITMAP和INODE頁，并且分配在前三個頁，但是第四個頁之后，和獨(dú)立表空間就不太一樣了

ibdata1 File Review

下面是分配的頁:
Page 3，type SYS:保存和插入緩存相關(guān)的信息
Page 4,type INDEX:插入緩存的索引結(jié)構(gòu)的root頁，插入緩存也是一個B+樹；
Page 5,type TRX_SYS: 記錄和InnoDB事務(wù)操作相關(guān)的信息，例如最近一次的事務(wù)id,MySQL二進(jìn)制日志信息以及兩次寫緩沖區(qū)的位置信息；
Page 6,type SYS: 第一個回滾段(rollback segment)頁，回滾段例外的頁或者整個區(qū)當(dāng)需要的時候才會被分配出來存儲回滾段信息；
Page 7,type SYS: 存儲和數(shù)據(jù)字典相關(guān)的信息，包含了數(shù)據(jù)字典中所有表格root頁面的個數(shù)．當(dāng)我們要訪問數(shù)據(jù)字典表頁時，就需要這些信息．
Pages 64-127: 第一個double write buffer頁塊（64塊，即一個extent）．double write buffer是InnoDB的一個恢復(fù)機(jī)制；
Pages 128-191:double write buffer的第二個頁塊．
系統(tǒng)表空間其他頁則在索引，回滾段和undo logs等等需要時分配．

單獨(dú)表空間

從MySQL5.6開始，默認(rèn)就是一個表，一個表空間文件．以.ibd后綴的文件就是單獨(dú)表空間文件，有如下結(jié)構(gòu)

IBD File Overview

單獨(dú)表空間文件結(jié)構(gòu)很簡單，沒有共享表空間存儲數(shù)據(jù)類型種類多；前三個頁分別是FSP_HDR,IBUF_BITMAP和INODE頁，從第四個頁開始存儲索引頁．在InnoDB中，B+樹內(nèi)部節(jié)點(diǎn)頁和葉子節(jié)點(diǎn)頁統(tǒng)稱為索引頁(index pages)．第四個頁固定為主鍵的root頁，其他頁則可能是內(nèi)部節(jié)點(diǎn)頁，主鍵葉子節(jié)點(diǎn)，輔助索引root頁，輔助索引內(nèi)部節(jié)點(diǎn)頁和輔助索引葉子節(jié)點(diǎn)頁．

因?yàn)榇蟛糠諭nnoDB系統(tǒng)信息都保存在共享表空間中，所以單獨(dú)表空間(per-table space)大部分都是type INDEX頁保存表數(shù)據(jù)．

InnoDB是支持MVCC的，它和ORACLE類似，采用 undo log、redo log來實(shí)現(xiàn)MVCC特性的。在事務(wù)中對一行數(shù)據(jù)進(jìn)行修改時，InnoDB 會把這行數(shù)據(jù)的舊版本數(shù)據(jù)存儲一份在undo log中，如果這時候有另一個事務(wù)又要修改這行數(shù)據(jù)，就又會把該事物最新可見的數(shù)據(jù)版本存儲一份在undo log中，以此類推，如果該數(shù)據(jù)當(dāng)前有N個事務(wù)要對其進(jìn)行修改，就需要存儲N份歷史版本（和ORACLE略有不同的是，InnoDB的undo log不完全是物理block，主要是邏輯日志，這個可以查看 InnoDB 源碼或其他相關(guān)資料）。這些 undo log 需要等待該事務(wù)結(jié)束后，并再次根據(jù)事務(wù)隔離級別所決定的對其他事務(wù)而言的可見性進(jìn)行判斷，確認(rèn)是否可以將這些 undo log 刪除掉，這個工作稱為 purge（purge 工作不僅僅是刪除過期不用的 undo log，還有其他，以后有機(jī)會再說）。

那么問題來了，如果當(dāng)前有個事務(wù)中需要讀取到大量數(shù)據(jù)的歷史版本，而該事務(wù)因?yàn)槟承┰驘o法今早提交或回滾，而該事務(wù)發(fā)起之后又有大量事務(wù)需要對這些數(shù)據(jù)進(jìn)行修改，這些新事務(wù)產(chǎn)生的 undo log 就一直無法被刪除掉，形成了堆積，這就是導(dǎo)致 ibdata1 文件大小增大最主要的原因之一。這種情況最經(jīng)典的場景就是大量數(shù)據(jù)備份，因此我們建議把 備份工作放在專用的 slave server 上，不要放在 master server 上 。

另一種情況是，InnoDB的 purge 工作因?yàn)楸敬?file i/o 性能是在太差或其他的原因，一直無法及時把可以刪除的 undo log 進(jìn)行purge 從而形成堆積，這是導(dǎo)致 ibdata1 文件大小增大另一個最主要的原因。這種場景發(fā)生在服務(wù)器硬件配置比較弱，沒有及時跟上業(yè)務(wù)發(fā)展而升級的情況。

比較少見的一種是在早期運(yùn)行在32位系統(tǒng)的MySQL版本中存在bug，當(dāng)發(fā)現(xiàn)待 purge 的 undo log 總量超過某個值時，purge 線程直接放棄抵抗，再也不進(jìn)行 purge 了，這個問題在我們早期使用32位MySQL 5.0版本時遇到的比較多，我們曾經(jīng)遇到這個文件漲到100多G的情況。后來我們費(fèi)了很大功夫把這些實(shí)例都遷移到64位系統(tǒng)下，終于解決了這個問題。

最后一個是，選項 innodb_data_file_path 值一開始就沒調(diào)整或者設(shè)置很小，這就必不可免導(dǎo)致 ibdata1 文件增大了。Percona官方提供的 my.cnf 參考文件中也一直沒把這個值加大，讓我百思不得其解，難道是為了像那個經(jīng)常被我吐槽的xx那樣，故意留個暗門，好方便后續(xù)幫客戶進(jìn)行優(yōu)化嗎？（我心理太陰暗了，不好不好~~）

稍微總結(jié)下，導(dǎo)致ibdata1文件大小暴漲的原因有下面幾個：

• 有大量并發(fā)事務(wù)，產(chǎn)生大量的undo log；
• 有舊事務(wù)長時間未提交，產(chǎn)生大量舊undo log；
• file i/o性能差，purge進(jìn)度慢；
• 初始化設(shè)置太小不夠用；
• 32-bit系統(tǒng)下有bug。

InnoDB 還沒有辦法對 ibdata1 文件表空間進(jìn)行回收/收縮，一旦 ibdata1 文件的肚子被搞大了，只能把數(shù)據(jù)先備份后恢復(fù)再次重新初始化實(shí)例才能恢復(fù)原先的大小，或者把依次把各個獨(dú)立表空間文件備份恢復(fù)到一個新實(shí)例中，除此外，沒什么更好的辦法了。
相應(yīng)的建議對策是：

• 升級到5.6及以上（64-bit），采用獨(dú)立undo表空間，5.6版本開始就支持獨(dú)立的undo表空間了，再也不用擔(dān)心會把 ibdata1 文件搞大；
• 初始化設(shè)置時，把 ibdata1 文件至少設(shè)置為1GB以上；
• 增加purge線程數(shù)，比如設(shè)置 innodb_purge_threads = 8；
• 提高file i/o能力，該上SSD的趕緊上；
• 事務(wù)及時提交，不要積壓；
• 默認(rèn)打開autocommit = 1，避免忘了某個事務(wù)長時間未提交；
• 檢查開發(fā)框架，確認(rèn)是否設(shè)置了 autocommit=0，記得在事務(wù)結(jié)束后都有顯式提交或回滾。

該FLUSH TABLES … FOR EXPORT語句確保對命名表的更改已刷新到磁盤，以便在實(shí)例運(yùn)行時可以進(jìn)行二進(jìn)制表副本。當(dāng)FLUSH TABLES … FOR EXPORT運(yùn)行時， InnoDB產(chǎn)生了.cfg在同一個數(shù)據(jù)庫的目錄表文件。該.cfg文件包含導(dǎo)入表空間文件時用于模式驗(yàn)證的元數(shù)據(jù)。

這個命令值得一提的是，保持當(dāng)前的窗口，不要關(guān)閉，如果關(guān)閉，cfg文件就會自動刪除，可以看到命令運(yùn)行后生成了cfg文件。

InnoDB表空間
MySQL · 引擎特性 · InnoDB 文件系統(tǒng)之文件物理結(jié)構(gòu)
MySQL日志——Undo | Redo