防僞碼：竹密無妨溪水過，天高不礙白雲飛。
一、MySQL Server 簡介
什麼是 MySQL
MySQL 是由 MySQL AB 公司（目前已經被 ORACLE 公司收歸麾下）自主研發的，目前 IT 行業最流行的開放源代碼的資料庫管理系統之一，它同時也是一個支援多線程高并發多使用者的關
系型資料庫管理系統。
MySQL 資料庫以其簡單高效可靠的特點，在最近短短幾年的時間就從一個名不見經傳的
資料庫系統，變成一個在 IT 行業幾乎是無人不知的開源資料庫管理系統。從
小型的 web 網站，至大型的企業級應用，到處都可見其身影的存在。
MySQL 資料庫在發展過程中一直有自己的三個原則：簡單、高效、可靠。
MySQL 的主要适用場景
MySQL 是目前最為流行的開源資料庫管理系統軟體了。那麼 MySQL 主要用于什麼場景下
1）Web 網站系統
Web 站點，是 MySQL 最大的客戶群，MySQL 之是以能成為 Web 站點開發者們最青睐的數
據庫管理系統，是因為 MySQL 資料庫的安裝配置都非常簡單，使用過程中的維護也不像很
多大型商業資料庫管理系統那麼複雜，而且性能出色。還有一個非常重要的原因就是 MySQL
是開放源代碼的，完全可以免費使用。
2）日志記錄系統
MySQL 資料庫的插入和查詢性能都非常的高效，如果設計地較好，在使用 MyISAM 存儲引
擎的時候，兩者可以做到互不鎖定，達到很高的并發性能。是以，對需要大量的插入和查詢
日志記錄的系統來說，MySQL 是非常不錯的選擇。比如處理使用者的登入日志，記錄檔等
是非常适合的應用場景。
3）資料倉庫系統
随着現在資料倉庫資料量的飛速增長，我們需要的存儲空間越來越大。資料量的不斷增長，
使資料的統計分析變得越來越低效，也越來越困難。怎麼辦？這裡有幾個主要的解決思路，
一個是采用昂貴的高性能主機以提高計算性能，用高端儲存設備提高 I/O 性能，效果理想，
但是成本非常高；第二個就是通過将資料複制到多台使用大容量硬碟的廉價 pc server 上，
以提高整體計算性能和 I/O 能力，效果尚可，存儲空間有一定限制，成本低廉；第三個，通
過将資料水準拆分，使用多台廉價的 pc server 和本地磁盤來存放資料，每台機器上面都隻
有所有資料的一部分，解決了資料量的問題，所有 pc server 一起并行計算，也解決了計算
能力問題，通過中間代理程式調配各台機器的運算任務，既可以解決計算性能問題又可以解
決 I/O 性能問題，成本也很低廉。在上面的三個方案中，第二和第三個的實作，MySQL 都
有較大的優勢。通過 MySQL 的簡單複制功能，可以很好的将資料從一台主機複制到另外一
台，
不僅僅在區域網路内可以複制，在廣域網同樣可以。當然，其他的資料庫同樣也可以做到，不
是隻有 MySQL 有這樣的功能。但是 MySQL 是免費的，其他資料庫大多都是按照主機數量或
者 cpu 數量來收費，當我們使用大量的 pcserver 的時候，license 費用相當驚人。第一個方
案，基本上所有資料庫系統都能夠實作，但是其高昂的成本并不是每一個公司都能夠承擔的。
二、MySQL 架構組成
Mysql 實體檔案組成：
1 ） 日志檔案 ： 主要包含：錯誤日志、查詢日志、慢查詢日志、事務日志、二進制日志
日志是 mysql 資料庫的重要組成部分。日志檔案中記錄着 mysql 資料庫運作期間發生的變
化；也就是說用來記錄 mysql 資料庫的用戶端連接配接狀況、SQL 語句的執行情況和錯誤資訊
等。當資料庫遭到意外的損壞時，可以通過日志檢視檔案出錯的原因，并且可以通過日志文
件進行資料恢複。
1、錯誤日志：Error Log
在 mysql 資料庫中，錯誤日志功能是預設開啟的。預設情況下，錯誤日志存儲在 mysql
資料庫的資料目錄中。錯誤日志檔案通常的名稱為 hostname.err。其中，hostname 表
示伺服器主機名。
預設情況下錯誤日志大概記錄以下幾個方面的資訊：伺服器啟動和關閉過程中的資訊、服務
器運作過程中的錯誤資訊、事件排程器運作一個事件時産生的資訊、在從伺服器上啟動服務
器程序時産生的資訊。
注 1：MySQL有很多系統變量可以設定，系統變量設定不同，會導緻系統運作狀态的不同。
是以 mysql 提供兩組指令，分别檢視系統設定和運作狀态。
1、檢視系統設定：
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW VARIABLES：show the values of MySQL system variables.
2、運作狀态：
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW STATUS：provides server status information.
如何修改系統配置
方法 1：配置檔案設定 my.cnf
如：binlog_cache_size = 1M
方法 2：set global binlog_cache_size = 1048576;
注 2：檢視 mysql 的版本
一般而言，日志級别的定義沒有會話變量都隻是在全局級别下進行定義
錯誤日志的狀态：
其中
log_error 定義為錯誤日志檔案路徑
log_error_verbosity:
The MySQL error log has received some attention in MySQL 5.7, with a new setting
called log_error_verbosity.
There are three possible values, as documented in the manual:
| Verbosity Value | Message Types Logged |
| 1 | Errors only |
| 2 | Errors and warnings |
| 3 | Errors, warnings, and notes (default) |
更改錯誤日志位置可以使用 log-error 來設定形式如下
#vi /etc/my.cnf
log-error = /usr/local/mysql/data/mysqld.err
檢視 mysql 錯誤日志：
#tail /usr/local/mysql/data/mysqld.err
為了友善維護需要，有時候會希望将錯誤日志中的内容做備份并重新開始記錄，這時候
就可以利用 MySQL 的 FLUSH LOGS 指令來告訴 MySQL 備份舊日志檔案并生成新的日志文
件。備份檔案名以“.old”結尾。
删除錯誤日志：
資料庫管理者可以删除很長時間之前的錯誤日志，以保證 mysql 伺服器上的硬碟空間。
mysql 資料庫中，可以使用 mysqladmin 指令開啟新的錯誤日志。mysqladmin 指令的
文法如下：
mysqladmin –u root –p flush-logs 也可以登入 mysql 資料庫中使用 FLUSH LOGS
語句來開啟新的錯誤日志。
先重命名原來的錯誤日志檔案，執行 mysqladmin –u root –p flush-logs 也可以登入
mysql 資料庫中使用 FLUSH LOGS 語句來開啟新的錯誤日志。
方式如下：
更多資訊請查閱官方文檔：
http://dev.mysql.com/doc/refman/5.5/en/error-log.html
http://dev.mysql.com/doc/refman/5.6/en/error-log.html
http://dev.mysql.com/doc/refman/5.7/en/error-log.html
2、二進制日志：Binary Log & Binary Log Index
二進制日志，也就是我們常說的 binlog，也是 MySQL Server 中最為重要的日志之一，主要
用于記錄修改資料或有可能引起資料改變的 mysql 語句，并且記錄了語句發生時間、執行
時長、操作的資料等等。是以說通過二進制日志可以查詢 mysql 資料庫中進行了哪些變化。
一般大小體積上限為 1G。
當我們通過“log-bin=file_name”打開了記錄的功能之後，MySQL 會将所有修改資料庫資料
的 query 以二進制形式記錄到日志檔案中，還包括每一條 query 所執行的時間，所消耗的
資源，以及相關的事務資訊。
binlog 記錄功能需要“log-bin=file_name”參數的顯式指定才能開啟，如果未指定 file_name，
則會在資料目錄下記錄為 mysql-bin.******（*代表 0～9 之間的某一個數字，來表示該日志
的序号）。
二進制開啟狀态：
binlog 還有其他一些附加選項參數：
“max_binlog_size”設定 binlog 的最大存儲上限，一般設定為 512M 或 1G,一般不能超過 1G
當日志達到該上限時，MySQL 會重新建立一個日志開始繼續記錄。不過偶爾也有超出該設
置的 binlog 産生，一般都是因為在即将達到上限時，産生了一個較大的事務，為了保證事務
安全，MySQL 不會将同一個事務分開記錄到兩個 binlog 中。
“binlog-do-db=db_name”參數明确告訴 MySQL，需要對某個（db_name）資料庫記錄 binlog，
如果有了“binlog-do-db=db_name”參數的顯式指定，MySQL 會忽略針對其他資料庫執行的
query，而僅僅記錄針對指定資料庫執行的 query。
“binlog-ignore-db=db_name”與“binlog-do-db=db_name”完全相反，它顯式指定忽略某個
（db_name）資料庫的 binlog 記錄，當指定了這個參數之後，MySQL 會記錄指定資料庫以
外所有的資料庫的 binlog。
“binlog-ignore-db=db_name”與“binlog-do-db=db_name”兩個參數有一個共同的概念需要
大家了解清楚，參數中的 db_name 不是指 query 語句更新的資料所在的資料庫，而是執行
query 的時候目前所處的資料庫。不論更新哪個資料庫的資料，MySQL 僅僅比較目前連接配接
所處的資料庫（通過 use db_name 切換後所在的資料庫）與參數設定的資料庫名，而不會分
析 query 語句所更新資料所在的資料庫。
mysql-bin.index 檔案（binary log index）的功能是記錄所有 Binary Log 的絕對路徑，保證 MySQL
各種線程能夠順利的根據它找到所有需要的 Binary Log 檔案。
binlog_cache_size =32768 #預設值 32768 binlog_cache_size：一個事務，在沒有提
交（uncommitted）的時候，産生的日志，記錄到 Cache 中；等到事務送出（committed）需
要送出的時候，則把日志持久化到磁盤。一般來說，如果我們的資料庫中沒有什麼大事務，
寫入也不是特别頻繁，2MB～4MB 是一個合适的選擇。但是如果我們的資料庫大事務較多，
寫入量比較大，可與适當調高 binlog_cache_size。同時，我們可以通過 binlog_cache_use
以及 binlog_cache_disk_use 來分析設定的 binlog_cache_size 是否足夠，是否有大量的
binlog_cache 由于記憶體大小不夠而使用臨時檔案（binlog_cache_disk_use）來緩存了。
binlog_stmt_cache_size= 32768 #當非事務語句使用二進制日志緩存，但是超
出binlog_stmt_cache_size時，使用一個臨時檔案來存放這些語句。
概念解釋：
事務表支援将批處理當做一個完整的任務統一送出或復原，即對包含在事務中的多條語句要
麼全執行，要麼全部不執行
非事務表則不支援此種操作，批進行中的語句如果遇到錯誤，在錯誤前的語句執行成功，之
後的則不執行。
log-bin = mysql-bin#指定binlog的位置，預設在資料目錄下。
binlog-format= {ROW|STATEMENT|MIXED}#指定二進制日志的類型，預設為 MIXED。
概念解釋：mysql複制主要有三種方式：基于SQL語句的複制(statement-based replication, SBR)，
基于行的複制(row-based replication, RBR)，混合模式複制(mixed-based replication, MBR)。對
應的，binlog 的格式也有三種：STATEMENT，ROW，MIXED。
① STATEMENT 模式（SBR） ）
每一條會修改資料的 sql 語句會記錄到 binlog 中。優點是并不需要記錄每一行的資料變化，
減少了 binlog 日志量，節約 IO，提高性能。缺點是在某些情況下會導緻 master-slave 中的數
據不一緻(如 sleep()函數，last_insert_id()，以及 user-defined functions(udf)等會出現問題)
② ROW 模式（RBR ）
不記錄每條 sql 語句的資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。缺點是會産生
大量的日志，讓日志暴漲。
③ MIXED 模式（MBR ）
以上兩種模式的混合使用，一般的複制使用 STATEMENT 模式儲存 binlog，對于 STATEMENT
模式無法複制的操作使用 ROW 模式儲存 binlog，MySQL 會根據執行的 SQL 語句選擇日志保
存方式。即交替使用行和語句、由 mysql 伺服器自行判斷。
其中基于行的定義格式資料量會大一些但是可以保證資料的精确性
sync_binlog = 10#設定多久同步一次二進制日志至磁盤檔案中，0 表示不同步，
任何正數值都表示對二進制每多少次寫操作之後同步一次。當autocommit的值
為1時，每條語句的執行都會引起二進制日志同步，否則，每個事務的送出會引
起二進制日志同步
通過編輯 my.cnf 中的 log-bin 選項可以開啟二進制日志；形式如下：
log-bin [=DIR/[filename]]
其中，DIR 參數指定二進制檔案的存儲路徑；filename 參數指定二級制檔案的檔案名，其
形式為 filename.number，number 的形式為 000001、000002 等。每次重新開機 mysql
服務或運作 mysql> flush logs;都會生成一個新的二進制日志檔案，這些日志檔案的
number 會不斷地遞增。除了生成上述的檔案外還會生成一個名為 filename.index 的檔案。
這個檔案中存儲所有二進制日志檔案的清單又稱為二進制檔案的索引
檢視二進制日志：
二進制日志的定義方式為二進制格式；使用此格式可以存儲更多的資訊，并且可以使寫入二
進制日志的效率更高。但是不能直接使用檢視指令打開并檢視二進制日志。
目前使用的二進制檔案及所處位置
檢視目前二進制檔案的資訊：
檢視二進制日志資訊的指令：
文法格式： SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
#檢視所有的二進制資訊
mysql> show binlog events\G;
#檢視指定日志的二進制資訊
mysql> show binlog events in 'mysql.000005' from 123\G;
#從指定的事件位置開始
注：二進制日志的記錄位置：通常為上一個事件執行結束時間的位置
#指定偏移量(不是語句，是事件)
mysql>show binlog events in 'mysql.000005' from 123 limit 1;
指令行下檢視二進制日志：
由于無法使用 cat 等方式直接打開并檢視二進制日志；是以必須使用 mysqlbinlog 指令。
但是當正在執行 mysql 讀寫操作時建議不要使用此打開正在使用的二進制日志檔案；若非
要打開可 flush logs。mysqlbinlog 指令的使用方式：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4 事件開始處
#170112 19:18:34 server id 1 end_log_pos 123 CRC32 0x6fa66df5 Start: binlog v 4, server v 5.7.13-log created 170112 19:18:34 at startup
#170112 19:18:34 年月日的簡寫方式；end_log_pos 事件結束處
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
CmZ3WA8BAAAAdwAAAHsAAAABAAQANS43LjEzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAKZndYEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfVtpm8=
'/*!*/;
# at 123
#170112 19:18:34 server id 1 end_log_pos 154 CRC32 0x0dc8a64c Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
删除二進制日志資訊：
二進制日志會記錄大量的資訊（其中包含一些無用的資訊）。如果很長時間不清理二進制日
志，将會浪費很多的磁盤空間。但是，删除之後可能導緻資料庫崩潰時無法進行恢複，是以
若要删除二進制日志首先将其和資料庫備份一份，其中也隻能删除備份前的二進制日志，新
産生的日志資訊不可删。也不可在關閉 mysql 伺服器之後直接删除因為這樣可能會給資料
庫帶來錯誤的。若非要删除二進制日志需要做如下操作：導出備份資料庫和二進制日志檔案
進行壓縮歸檔存儲。删除二進制檔案的方法如下：
方法 1：根據檔案或時間點來删除二進制日志：
文法形式：
mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }
其 中 TO 'log_name' 表 示 把 這 個 文 件 之 前 的 其 他 文 件 都 删 除 掉 ， 也 可 使 用
BEFORE datetime_expr 指定把哪個時間之前的二進制檔案删除了。
或者用 ls 檢視
方法2： 删除所有的二進制日志（慎用）：
使用 RESET MASTER 語句可以删除所有的二進制日志。該語句的形式如下：
不建議在生産環境下使用此操作；删除所有的二進制日志後，Mysql 将會重新建立新的二
進制日志。新二進制日志的編号從 000001 開始。
3、事務日志(或稱 redo 日志)
事務日志（InnoDB 特有的日志）可以幫助提高事務的效率。使用事務日志，存儲引擎在修
改表的資料時隻需要修改其記憶體拷貝，再把修改行為記錄到持久在硬碟上的事務日志中，而
不用每次都将修改的資料本身持久到磁盤。事務日志采用追加的方式，是以寫日志的操作是
磁盤上一小塊區域内的順序 I/O，而不像随機 I/O 需要在磁盤的多個地方移動磁頭，是以
采用事務日志的方式相對來說要快得多。事務日志持久以後，記憶體中被修改的資料在背景可
以慢慢的刷回到磁盤。目前大多數的存儲引擎都是這樣實作的。
如果資料的修改已經記錄到事務日志并持久化，但資料本身還沒有寫回磁盤，此時系統崩潰，
存儲引擎在重新開機時能夠自動恢複這部分修改的資料。具有的恢複方式則視存儲引擎而定。
一般情況下，mysql 會預設提供多種存儲引擎,你可以通過下面的檢視:
檢視你的 mysql 現在已提供什麼存儲引擎:
mysql> show engines;
看你的 mysql 目前預設的存儲引擎:
mysql> show variables like '%storage_engine%';
你要看某個表用了什麼引擎(在顯示結果裡參數 engine 後面的就表示該表目前用的存儲引
擎):
mysql> show create table 表名;
注：
create table 庫名.表名 engine = innodb;
這樣就可以将表的引擎變更為 innodb 引擎了。
也可以在建立表之後通過下面語句來變更：
alter table 庫名.表名 engine =innodb;
檢視事務日志的定義：
mysql> show global variables like '%log%';
顯示結果：
| innodb_flush_log_at_timeout| 1 |
| innodb_flush_log_at_trx_commit | 1 #在事務送出時 innodb 是否同步日志從緩沖區到檔案中，
當這個值為 1（預設值）之時，在每個事務送出時，日志緩沖被寫到日志檔案，對日志檔案做到磁盤操作
的重新整理，性能會很差造成大量的磁盤 I/O 但這種方式最安全；如果設為 2,每次送出事務都會寫日志，但并
不會執行刷的操作。每秒定時會刷到日志檔案。要注意的是，并不能保證 100%每秒一定都會刷到磁盤，這
要取決于程序的排程。每次事務送出的時候将資料寫入事務日志，而這裡的寫入僅是調用了檔案系統的寫
入操作，而檔案系統是有 緩存的，是以這個寫入并不能保證資料已經寫入到實體磁盤。設定為 0，日志緩
沖每秒一次地被寫到日志檔案，并且對日志檔案做到磁盤操作的重新整理，但是在一個事務送出不做任何操作。
注：刷寫的概念
刷寫其實是兩個操作，刷（flush）和寫（write），區分這兩個概念是很重要的。
在大多數的作業系統中，把Innodb 的 log buffer（記憶體）寫入日志（調用系統調
用write），隻是簡單的把資料移到作業系統緩存中，作業系統緩存同樣指的是内
存。并沒有實際的持久化資料。
是以，通常設為 0 和 2 的時候，在崩潰或斷電的時候會丢失最後一秒的資料，因
為這個時候資料隻是存在于作業系統緩存。之是以說“通常”，可能會有丢失不隻
1 秒的資料的情況，比如說執行 flush操作的時候阻塞了。
總結
設為1 當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不
能接受）。如果對資料一緻性和完整性要求不高，完全可以設為 2,如果隻最求性
能，例如高并發寫的日志伺服器，設為0 來獲得更高性能
|
| innodb_locks_unsafe_for_binlog| OFF |
| innodb_log_buffer_size| 16777216 |
| innodb_log_checksums | ON
| innodb_log_compressed_pages| ON |
| innodb_log_file_size| 50331648 #日志檔案大小 |
| innodb_log_files_in_group| 2 # DB 中設定幾組事務日志，預設是 2
| innodb_log_group_home_dir| . /#定義 innodb 事務日志組的位置,此位置設定預設為 MySQL的 datadir |
每個事務日志都是大小為 50 兆的檔案（不同版本的 mysql 有差異）：
在 mysql 中預設以 ib_logfile0,ib_logfile1 名稱存在
4、慢查詢日志：slow query log
顧名思義，慢查詢日志中記錄的是執行時間較長的 query，也就是我們常說的 slowquery。
慢查詢日志采用的是簡單的文本格式，可以通過各種文本編輯器檢視其中的内容。其中
記錄了語句執行的時刻，執行所消耗的時間，執行使用者，連接配接主機等相關資訊。
慢查詢日志的作用：
慢查詢日志是用來記錄執行時間超過指定時間的查詢語句。通過慢查詢日志，可以查找出哪
些查詢語句的執行效率很低，以便進行優化。一般建議開啟，它對伺服器性能的影響微乎其
微，但是可以記錄 mysql 伺服器上執行了很長時間的查詢語句。可以幫助我們定位性能問
題的。MySQL 還提供了專門用來分析滿查詢日志的工具程式 mysqldumpslow，用來幫助數
據庫管理人員解決可能存在的性能問題。
檢視慢查詢日志的定義：
啟動和設定慢查詢日志：
方法 1：通過配置檔案 my.cnf 開啟慢查詢日志：
注：在不同的 mysql 版本中，開啟慢查詢日志參數不太一樣，不過都可以通過 show variables like "%slow%" 和 show variables like "%long%"檢視出來。
其中：
slow_query_log： off 關閉狀态 on 開啟狀态
slow_query_log_file 慢查詢日志存放地點
long_query_time 選項來設定一個時間值，時間以秒為機關，可以精确到微秒。如果查詢
時間超過了這個時間值（預設為 10 秒），這個查詢語句将被記錄到慢查詢日志中,設定為 0
的話表示記錄所有的查詢。
slow_launch_time 表示如果建立線程花費了比這個值更長的時間,slow_launch_threads 計
數器将增加
注：如果不指定存儲路徑，慢查詢日志預設存儲到 mysql 資料庫的資料檔案下，如果不指
定檔案名，預設檔案名為 hostname-slow.log
修改 my.cnf 檔案：
重新開機 mysqld 服務
再次查詢慢查詢日志定義：
方法 2：通過登入 mysql 伺服器直接定義，方式如下：
mysql>set globalslow_query_log=1; #開啟慢查詢日志
Query OK, 0 rowsaffected (0.35 sec)
mysql>setsession long_query_time=0.0001; #更改時間（目前 session 中，退
出則重置）
Query OK, 0 rowsaffected (0.00 sec)
mysql>set globallong_query_time=0.0001; #更改時間（全局中，重新開機服務則
重置）
mysql> SHOWVARIABLES LIKE 'long%'; #查詢定義時間
檢視慢查詢日志
mysql> use mysql
mysql>selectuser,host from user where user="root";
或用系統檢視檔案内容指令如 cat 直接檢視慢日志檔案
第一行表示記錄日志時的時間。其格式是 YYYY-MM-DD HH:MM:SS。我們可以看出上面的查
詢記錄于 2017 年 1月 12日上午 11:51：20 - 注意：這個是伺服器時間.
MySql 使用者、伺服器以及主機名第三行表示總的查詢時間、鎖定時間、"發送"或者傳回的行
數
Query_time: 0.000255 表示用了 0.000255 秒
Lock_time: 0.000047 表示鎖了 0.000047 秒
Rows_sent: 4 表示傳回 4 行
Rows_examined: 4 表示一共查了 4 行
SET timestamp=UNIXTIME; 這是查詢實際發生的時間
何将其變成一個有用的時間，将 Unix 時間轉成一個可讀的時間，可以使用 date –d@日志
中的時間戳
以看到查詢進行的同時記錄了該日志，但是對于一台超負載的伺服器常常并非如此。是以記
住：SET timestamp= value 才是實際的查詢的執行時間。
慢查詢分析 mysqldumpslow
們可以通過打開 log 檔案檢視得知哪些 SQL 執行效率低下。從日志中，可以發現查詢時間
超過 long_query_time 時間 的 query 為慢查詢，而小于 long_query_time 時間 的沒有
出現在此日志中。
如果慢查詢日志中記錄内容很多，可以使用 mysqldumpslow 工具（MySQL 用戶端安裝自
帶）來對慢查詢日志進行分類彙總。mysqldumpslow 對日志檔案進行了分類彙總，顯示彙
總後摘要結果
進入 log 的存放目錄，運作
[root@localhost data]# mysqldumpslow mysqld-slow.log
mysqldumpslow-s c -t 10 /database/mysql/slow-query.log
這會輸出記錄次數最多的 10 條 SQL 語句，其中：
-s, 是表示按照何種方式排序，c、t、l、r 分别是按照記錄數、查詢時間、鎖定時間、傳回行
數來排序；
-t, 是 top n 的意思，即為傳回前面多少條的資料；
-g, 後邊可以寫一個正則比對模式，大小寫不敏感的；
詳細選項檢視幫助：#mysqldumpslow --help
例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到傳回記錄集最多的 10 個查詢。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照時間排序的前 10 條裡面含有左連接配接的查詢語句。
2 ）資料文據
在 MySQL 中每一個資料庫都會在定義好（或者預設）的資料目錄下存在一個以資料庫名字
命名的檔案夾，用來存放該資料庫中各種表資料檔案。不同的 MySQL 存儲引擎有各自不同
的資料檔案。如 MyISAM 用“.MYD”作為擴充名，Innodb 用“.ibd”，Archive 用“.arc”，CSV
用“.csv”，等等。
如何檢視你的 mysql 現在已提供什麼存儲引擎:
create table 庫名.表名 engine = innodb;這樣就可以将表的引擎變更為 innodb 引擎了。
登入 mysql，建立一個資料庫如 testdb，并在資料庫中建立一個表，如下圖所示：
檢視資料庫所在目錄會發現資料目錄下存在一個以資料庫名字命名的檔案夾
檢視 testdb 目錄的檔案清單
從上圖可以看出表使用的是 innodb 存儲引擎。
以 myisam 存儲引擎建立一個測試表 tb2
檢視資料庫目錄
注：修改 mysql 的預設存儲引擎
1、檢視 mysql 存儲引擎指令，在 mysql>提示符下搞入 show engines;字段 Support 為:Default表示預設存儲引擎
2 、 設 置 InnoDB 為 默 認 引 擎 ： 在 配 置 文 件 my.cnf 中 的 [mysqld] 下 面 加 入
default-storage-engine=INNODB 一句
3、重新開機 mysql 伺服器：mysqladmin -u root -p shutdown 或者 service mysqld restart 登入 mysql資料庫。
1、“.frm”檔案
與表相關的中繼資料（meta）資訊都存放在“.frm”檔案中，包括表結構的定義資訊等。不論
是什麼存儲引擎（MySQL 常用的兩個存儲引擎是 MyISAM 和 InnoDB），每一個表都會有一
個以表名命名的“.frm”檔案。所有的“.frm”檔案都存放在所屬資料庫的檔案夾下面。
MyISAM 資料庫表檔案：.MYD 檔案：表資料檔案；.MYI 檔案：索引檔案
2、“.MYD”檔案
“.MYD”檔案是 MyISAM 存儲引擎專用，存放 MyISAM 表的資料。每一個 MyISAM 表都會有
一個“.MYD”檔案與之對應，同樣存放于所屬資料庫的檔案夾下，和“.frm”檔案在一起。
3、“.MYI”檔案
“.MYI”檔案也是專屬于 MyISAM 存儲引擎的，主要存放 MyISAM 表的索引相關資訊。對于
MyISAM 存儲來說，可以被 cache 的内容主要就是來源于“.MYI”檔案中。每一個 MyISAM
表對應一個“.MYI”檔案，存放于位置和“.frm”以及“.MYD”一樣。
InnoDB 采用表空間（tablespace）來管理資料，存儲表資料和索引。
.ibd 檔案：單表表空間檔案，每個表使用一個表空間檔案（file per table），存放使用者資料庫
表資料和索引。
InnoDB 共享表空間（即 InnoDB 檔案集，ib-file set）：ibdata1、ibdata2 等，存儲 InnoDB 系統
資訊和使用者資料庫表資料和索引，所有表共用。
4、“.ibd”檔案和 ibdata 檔案
這兩種檔案都是存放 Innodb 資料的檔案，之是以有兩種檔案來存放 Innodb 的資料（包括索
引），是因為 Innodb 的資料存儲方式能夠通過配置來決定是使用共享表空間存放存儲資料，
還是獨享表空間存放存儲資料。獨享表空間存儲方式使用“.ibd”檔案來存放資料，且每個
表一個“.ibd”檔案，檔案存放在和 MyISAM 資料相同的位置。如果選用共享存儲表空間來
存放資料，則會使用 ibdata 檔案來存放，所有表共同使用一個（或者多個，可自行配置）ibdata
檔案。
ibdata檔案可以通過innodb_data_home_dir和innodb_data_file_path兩個參數共同配置組成，
innodb_data_home_dir 配置資料存放的總目錄，而 innodb_data_file_path 配置每一個檔案的
名稱。
innodb_data_file_path 中可以一次配置多個 ibdata 檔案。檔案可以是指定大小，也可以是自
動擴充的，但是 Innodb 限制了僅僅隻有最後一個 ibdata 檔案能夠配置成自動擴充類型。當
我們需要添加新的 ibdata 檔案的時候，隻能添加在 innodb_data_file_path 配置的最後，而且
必須重新開機 MySQL 才能完成 ibdata 的添加工作。不過如果我們使用獨享表空間存儲方式的話，
就不會有這樣的問題。
總結：
共享表空間以及獨占表空間都是針對資料的存儲方式而言的。
共享表空間: 某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中。
獨占表空間: 每一個表都将會生成以獨立的檔案方式來進行存儲，每一個表都有一個.frm 表描
述檔案，還有一個.ibd 檔案。其中這個檔案包括了單獨一個表的資料内容以及索引内容。
兩者之間的優缺點
共享表空間：
優點：
可以放表空間分成多個檔案存放到各個磁盤上。資料和檔案放在一起友善管理。
缺點：
所有的資料和索引存放到一個檔案中，多個表及索引在表空間中混合存儲，這樣對于一個表
做了大量删除操作後表空間中将會有大量的空隙，特别是對于統計分析，日值系統這類應用
最不适合用共享表空間。
獨立表空間：
1．每個表都有自已獨立的表空間。
2．每個表的資料和索引都會存在自已的表空間中。
3．可以實作單表在不同的資料庫中移動。
4．空間可以回收
相比較之下，使用獨占表空間的效率以及性能會更高一點
檢視目前資料庫的表空間管理類型
ON 代表獨立表空間管理，OFF 代表共享表空間管理；（檢視單表的表空間管理方式，需要檢視每個表是否有單獨的資料檔案）
Innodb 共享表空間配置：
參數解釋：
innodb_data_home_dir = "/path/" 資料庫檔案所存放的目錄
innodb_log_group_home_dir = "/path/" 日志存放目錄
innodb_data_file_path=ibdata1:10M:autoextend 設定一個可擴充大小的尺寸為 10MB 的資料
檔案（共享資料檔案），名為 ibdata1。沒有給出檔案的位置，是以預設的是在 MySQL 的數
據目錄内。
innodb_file_per_table=1｜0 //1 為使用獨占表空間，0 為使用共享表空間
注：InnoDB 不建立目錄，是以在啟動伺服器之前請确認”所配置的路徑目錄”的确存在。
mysqld啟動失敗,檢視錯誤日志
#tail -20 /usr/local/mysql/data/mysqld.err
顯示内容如下：
注：不同版本的 mysql 報錯略有不同，注意看錯誤日志的内容。
從錯誤日志中顯示可以看出
在/etc/my.cnf 檔案中設定 6400 頁而目前 ibdata1 為 768 頁
需要計算 768/64=12
修改配置為
啟動 mysql，成功！
注：計算公式：64pages 相當于 1M，1page 是 16KB
如果不清楚預設檔案 page 大小，可以先 du -h ibdata1 檢視下，再去設定：
這說明 mysql5.7.13 中 ibdata 初始化為 12M
登入 mysql 執行 mysql> show variables like '%innodb_file_per_table%';
這時建立的表就會使用共享表空間了。
建立一個資料庫 testdb 并建立一個表
向表中插入若幹行資料
這裡定義一個存儲過程向表中插入 100000 行資料
調用存儲過程
檢視表中行數：
如何檢視表在表空間占用情況：
方法 1：對 INNODB，你可以直接用指令 show table status 檢視某個表的表空間占用情況。
方法 2：
如果想知道 MySQL 資料庫中每個表占用的空間、表記錄的行數的話，可以打開 MySQL 的
information_schema 資料庫。在該庫中有一個 TABLES 表，這個表主要字段分别是：
TABLE_SCHEMA : 資料庫名
TABLE_NAME：表名
ENGINE：所使用的存儲引擎
TABLE_ROWS：記錄數
DATA_LENGTH：資料大小
INDEX_LENGTH：索引大小
3 、Replication 相關檔案：
1）master.info 檔案：
master.info 檔案存在于 Slave 端的資料目錄下，裡面存放了該 Slave 的 Master 端的相關信
息，包括 Master 的主機位址，連接配接使用者，連接配接密碼，連接配接端口，目前日志位置，已經讀取
到的日志位置等資訊。
2）relay log 和 relay log index
mysql-relay-bin.xxxxxn 檔案用于存放 Slave 端的 I/O 線程從 Master 端所讀取到的 Binary Log
資訊，然後由 Slave 端的 SQL 線程從該 relay log 中讀取并解析相應的日志資訊，轉化成
Master 所執行的 SQL 語句，然後在 Slave 端應用。
mysql-relay-bin.index 檔案的功能類似于 mysql-bin.index，同樣是記錄日志的存放位置的絕對
路徑，隻不過他所記錄的不是 Binary Log，而是 Relay Log。
3）relay-log.info 檔案：
類似于 master.info，它存放通過 Slave 的 I/O 線程寫入到本地的 relay log 的相關信
息。供 Slave 端的 SQL 線程以及某些管理操作随時能夠擷取目前複制的相關資訊。
4 、其他檔案：
1）system config file
MySQL 的系統配置檔案一般都是 my.cnf，預設存放在"/etc"目錄下，my.cnf 檔案中包含多種
參數選項組（group），每一種參數組都通過中括号給定了固定的組名，如“[mysqld]”組中
包括了 mysqld 服務啟動時候的初始化參數，“[client]”組中包含着用戶端工具程式可以讀取
的參數。
2）pid file
pid file 是 mysqld 應用程式在 Unix/Linux 環境下的一個程序檔案，和許多其他
Unix/Linux 服務端程式一樣，存放着自己的程序 id。
3）socket file
socket 檔案也是在 Unix/Linux 環境下才有的，使用者在 Unix/Linux 環境下用戶端連接配接可以不
通過 TCP/IP 網絡而直接使用 Unix Socket 來連接配接 MySQL。
mysql 有兩種連接配接方式，常用的一般是 tcp
mysql–hmysql 主機 ip -uroot -pxxx
mysql-S /path/mysql.sock
注：采用 unix socket 連接配接方式，比用 tcp 的方式更快，但隻适用于 mysql 和應用同在一台 PC上。