防偽碼：竹密無妨溪水過，天高不礙白云飛。

一、MySQL Server 簡介

什么是 MySQL

MySQL 是由 MySQL AB 公司（目前已經(jīng)被 ORACLE 公司收歸麾下）自主研發(fā)的，目前 IT 行業(yè)最流行的開放源代碼的數(shù)據(jù)庫管理系統(tǒng)之一，它同時也是一個支持多線程高并發(fā)多用戶的關(guān)

系型數(shù)據(jù)庫管理系統(tǒng)。

MySQL 數(shù)據(jù)庫以其簡單高效可靠的特點，在最近短短幾年的時間就從一個名不見經(jīng)傳的

數(shù)據(jù)庫系統(tǒng)，變成一個在 IT 行業(yè)幾乎是無人不知的開源數(shù)據(jù)庫管理系統(tǒng)。從

小型的 web 網(wǎng)站，至大型的企業(yè)級應(yīng)用，到處都可見其身影的存在。

MySQL 數(shù)據(jù)庫在發(fā)展過程中一直有自己的三個原則：簡單、高效、可靠。

MySQL 的主要適用場景

MySQL 是目前最為流行的開源數(shù)據(jù)庫管理系統(tǒng)軟件了。那么 MySQL 主要用于什么場景下

1）Web 網(wǎng)站系統(tǒng)

Web 站點，是 MySQL 最大的客戶群，MySQL 之所以能成為 Web 站點開發(fā)者們最青睞的數(shù)

據(jù)庫管理系統(tǒng)，是因為 MySQL 數(shù)據(jù)庫的安裝配置都非常簡單，使用過程中的維護也不像很

多大型商業(yè)數(shù)據(jù)庫管理系統(tǒng)那么復雜，而且性能出色。還有一個非常重要的原因就是 MySQL

是開放源代碼的，完全可以免費使用。

2）日志記錄系統(tǒng)

MySQL 數(shù)據(jù)庫的插入和查詢性能都非常的高效，如果設(shè)計地較好，在使用 MyISAM 存儲引

擎的時候，兩者可以做到互不鎖定，達到很高的并發(fā)性能。所以，對需要大量的插入和查詢

日志記錄的系統(tǒng)來說，MySQL 是非常不錯的選擇。比如處理用戶的登錄日志，操作日志等

是非常適合的應(yīng)用場景。

3）數(shù)據(jù)倉庫系統(tǒng)

隨著現(xiàn)在數(shù)據(jù)倉庫數(shù)據(jù)量的飛速增長，我們需要的存儲空間越來越大。數(shù)據(jù)量的不斷增長，

使數(shù)據(jù)的統(tǒng)計分析變得越來越低效，也越來越困難。怎么辦？這里有幾個主要的解決思路，

一個是采用昂貴的高性能主機以提高計算性能，用高端存儲設(shè)備提高 I/O 性能，效果理想，

但是成本非常高；第二個就是通過將數(shù)據(jù)復制到多臺使用大容量硬盤的廉價 pc server 上，

以提高整體計算性能和 I/O 能力，效果尚可，存儲空間有一定限制，成本低廉；第三個，通

過將數(shù)據(jù)水平拆分，使用多臺廉價的 pc server 和本地磁盤來存放數(shù)據(jù)，每臺機器上面都只

有所有數(shù)據(jù)的一部分，解決了數(shù)據(jù)量的問題，所有 pc server 一起并行計算，也解決了計算

能力問題，通過中間代理程序調(diào)配各臺機器的運算任務(wù)，既可以解決計算性能問題又可以解

決 I/O 性能問題，成本也很低廉。在上面的三個方案中，第二和第三個的實現(xiàn)，MySQL 都

有較大的優(yōu)勢。通過 MySQL 的簡單復制功能，可以很好的將數(shù)據(jù)從一臺主機復制到另外一

臺，

不僅僅在局域網(wǎng)內(nèi)可以復制，在廣域網(wǎng)同樣可以。當然，其他的數(shù)據(jù)庫同樣也可以做到，不

是只有 MySQL 有這樣的功能。但是 MySQL 是免費的，其他數(shù)據(jù)庫大多都是按照主機數(shù)量或

者 cpu 數(shù)量來收費，當我們使用大量的 pcserver 的時候，license 費用相當驚人。第一個方

案，基本上所有數(shù)據(jù)庫系統(tǒng)都能夠?qū)崿F(xiàn)，但是其高昂的成本并不是每一個公司都能夠承擔的。

二、MySQL 架構(gòu)組成

Mysql  物理文件組成：

1 ） 日志文件 ： 主要包含：錯誤日志、查詢?nèi)罩?、慢查詢?nèi)罩?、事?wù)日志、二進制日志

日志是 mysql 數(shù)據(jù)庫的重要組成部分。日志文件中記錄著 mysql 數(shù)據(jù)庫運行期間發(fā)生的變

化；也就是說用來記錄 mysql 數(shù)據(jù)庫的客戶端連接狀況、SQL 語句的執(zhí)行情況和錯誤信息

等。當數(shù)據(jù)庫遭到意外的損壞時，可以通過日志查看文件出錯的原因，并且可以通過日志文

件進行數(shù)據(jù)恢復。

1、錯誤日志：Error Log

在 mysql 數(shù)據(jù)庫中，錯誤日志功能是默認開啟的。默認情況下，錯誤日志存儲在 mysql

數(shù)據(jù)庫的數(shù)據(jù)目錄中。錯誤日志文件通常的名稱為 hostname.err。其中，hostname 表

示服務(wù)器主機名。

默認情況下錯誤日志大概記錄以下幾個方面的信息：服務(wù)器啟動和關(guān)閉過程中的信息、服務(wù)

器運行過程中的錯誤信息、事件調(diào)度器運行一個事件時產(chǎn)生的信息、在從服務(wù)器上啟動服務(wù)

器進程時產(chǎn)生的信息。

注 1：MySQL有很多系統(tǒng)變量可以設(shè)置，系統(tǒng)變量設(shè)置不同，會導致系統(tǒng)運行狀態(tài)的不同。

因此 mysql 提供兩組命令，分別查看系統(tǒng)設(shè)置和運行狀態(tài)。

1、查看系統(tǒng)設(shè)置：

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

SHOW VARIABLES：show the values of MySQL system variables.

2、運行狀態(tài)：

SHOW [GLOBAL | SESSION] STATUS [like_or_where]

SHOW STATUS：provides server status information.

如何修改系統(tǒng)配置

方法 1：配置文件設(shè)置 my.cnf

如：binlog_cache_size = 1M

方法 2：set global binlog_cache_size = 1048576;

注 2：查看 mysql 的版本

或

或

一般而言，日志級別的定義沒有會話變量都只是在全局級別下進行定義

錯誤日志的狀態(tài)：

其中

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:

更改錯誤日志位置可以使用 log-error 來設(shè)置形式如下

#vi /etc/my.cnf

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

查看 mysql 錯誤日志：

#tail /usr/local/mysql/data/mysqld.err

為了方便維護需要，有時候會希望將錯誤日志中的內(nèi)容做備份并重新開始記錄，這時候

就可以利用 MySQL 的 FLUSH LOGS 命令來告訴 MySQL 備份舊日志文件并生成新的日志文

件。備份文件名以“.old”結(jié)尾。

刪除錯誤日志：

數(shù)據(jù)庫管理員可以刪除很長時間之前的錯誤日志，以保證 mysql 服務(wù)器上的硬盤空間。

mysql 數(shù)據(jù)庫中，可以使用 mysqladmin 命令開啟新的錯誤日志。mysqladmin 命令的

語法如下：

mysqladmin –u root –p flush-logs 也可以登錄 mysql 數(shù)據(jù)庫中使用 FLUSH LOGS

語句來開啟新的錯誤日志。

先重命名原來的錯誤日志文件，執(zhí)行 mysqladmin –u root –p flush-logs 也可以登錄

mysql 數(shù)據(jù)庫中使用 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 中最為重要的日志之一，主要

用于記錄修改數(shù)據(jù)或有可能引起數(shù)據(jù)改變的 mysql 語句，并且記錄了語句發(fā)生時間、執(zhí)行

時長、操作的數(shù)據(jù)等等。所以說通過二進制日志可以查詢 mysql 數(shù)據(jù)庫中進行了哪些變化。

一般大小體積上限為 1G。

當我們通過“l(fā)og-bin=file_name”打開了記錄的功能之后，MySQL 會將所有修改數(shù)據(jù)庫數(shù)據(jù)

的 query 以二進制形式記錄到日志文件中，還包括每一條 query 所執(zhí)行的時間，所消耗的

資源，以及相關(guān)的事務(wù)信息。

binlog 記錄功能需要“l(fā)og-bin=file_name”參數(shù)的顯式指定才能開啟，如果未指定 file_name，

則會在數(shù)據(jù)目錄下記錄為 mysql-bin.******（*代表 0～9 之間的某一個數(shù)字，來表示該日志

的序號）。

二進制開啟狀態(tài)：

binlog 還有其他一些附加選項參數(shù)：

“max_binlog_size”設(shè)置 binlog 的最大存儲上限，一般設(shè)置為 512M 或 1G,一般不能超過 1G

當日志達到該上限時，MySQL 會重新創(chuàng)建一個日志開始繼續(xù)記錄。不過偶爾也有超出該設(shè)

置的 binlog 產(chǎn)生，一般都是因為在即將達到上限時，產(chǎn)生了一個較大的事務(wù)，為了保證事務(wù)

安全，MySQL 不會將同一個事務(wù)分開記錄到兩個 binlog 中。

“binlog-do-db=db_name”參數(shù)明確告訴 MySQL，需要對某個（db_name）數(shù)據(jù)庫記錄 binlog，

如果有了“binlog-do-db=db_name”參數(shù)的顯式指定，MySQL 會忽略針對其他數(shù)據(jù)庫執(zhí)行的

query，而僅僅記錄針對指定數(shù)據(jù)庫執(zhí)行的 query。

“binlog-ignore-db=db_name”與“binlog-do-db=db_name”完全相反，它顯式指定忽略某個

（db_name）數(shù)據(jù)庫的 binlog 記錄，當指定了這個參數(shù)之后，MySQL 會記錄指定數(shù)據(jù)庫以

外所有的數(shù)據(jù)庫的 binlog。

“binlog-ignore-db=db_name”與“binlog-do-db=db_name”兩個參數(shù)有一個共同的概念需要

大家理解清楚，參數(shù)中的 db_name 不是指 query 語句更新的數(shù)據(jù)所在的數(shù)據(jù)庫，而是執(zhí)行

query 的時候當前所處的數(shù)據(jù)庫。不論更新哪個數(shù)據(jù)庫的數(shù)據(jù)，MySQL 僅僅比較當前連接

所處的數(shù)據(jù)庫（通過 use db_name 切換后所在的數(shù)據(jù)庫）與參數(shù)設(shè)置的數(shù)據(jù)庫名，而不會分

析 query 語句所更新數(shù)據(jù)所在的數(shù)據(jù)庫。

mysql-bin.index 文件（binary log index）的功能是記錄所有 Binary Log 的絕對路徑，保證 MySQL

各種線程能夠順利的根據(jù)它找到所有需要的 Binary Log 文件。

binlog_cache_size =32768 #默認值 32768 binlog_cache_size：一個事務(wù)，在沒有提

交（uncommitted）的時候，產(chǎn)生的日志，記錄到 Cache 中；等到事務(wù)提交（committed）需

要提交的時候，則把日志持久化到磁盤。一般來說，如果我們的數(shù)據(jù)庫中沒有什么大事務(wù)，

寫入也不是特別頻繁，2MB～4MB 是一個合適的選擇。但是如果我們的數(shù)據(jù)庫大事務(wù)較多，

寫入量比較大，可與適當調(diào)高 binlog_cache_size。同時，我們可以通過 binlog_cache_use

以及 binlog_cache_disk_use 來分析設(shè)置的 binlog_cache_size 是否足夠，是否有大量的

binlog_cache 由于內(nèi)存大小不夠而使用臨時文件（binlog_cache_disk_use）來緩存了。

binlog_stmt_cache_size= 32768 #當非事務(wù)語句使用二進制日志緩存，但是超

出binlog_stmt_cache_size時，使用一個臨時文件來存放這些語句。

概念解釋：

事務(wù)表支持將批處理當做一個完整的任務(wù)統(tǒng)一提交或回滾，即對包含在事務(wù)中的多條語句要

么全執(zhí)行，要么全部不執(zhí)行

非事務(wù)表則不支持此種操作，批處理中的語句如果遇到錯誤，在錯誤前的語句執(zhí)行成功，之

后的則不執(zhí)行。

log-bin = mysql-bin#指定binlog的位置，默認在數(shù)據(jù)目錄下。

binlog-format= {ROW|STATEMENT|MIXED}#指定二進制日志的類型，默認為 MIXED。

概念解釋：mysql復制主要有三種方式：基于SQL語句的復制(statement-based replication, SBR)，

基于行的復制(row-based replication, RBR)，混合模式復制(mixed-based replication, MBR)。對

應(yīng)的，binlog 的格式也有三種：STATEMENT，ROW，MIXED。

①  STATEMENT  模式（SBR） ）

每一條會修改數(shù)據(jù)的 sql 語句會記錄到 binlog 中。優(yōu)點是并不需要記錄每一行的數(shù)據(jù)變化，

減少了 binlog 日志量，節(jié)約 IO，提高性能。缺點是在某些情況下會導致 master-slave 中的數(shù)

據(jù)不一致(如 sleep()函數(shù)，last_insert_id()，以及 user-defined functions(udf)等會出現(xiàn)問題)

②  ROW  模式（RBR ）

不記錄每條 sql 語句的信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。缺點是會產(chǎn)生

大量的日志，讓日志暴漲。

③  MIXED  模式（MBR ）

以上兩種模式的混合使用，一般的復制使用 STATEMENT 模式保存 binlog，對于 STATEMENT

模式無法復制的操作使用 ROW 模式保存 binlog，MySQL 會根據(jù)執(zhí)行的 SQL 語句選擇日志保

存方式。即交替使用行和語句、由 mysql 服務(wù)器自行判斷。

其中基于行的定義格式數(shù)據(jù)量會大一些但是可以保證數(shù)據(jù)的精確性

sync_binlog = 10#設(shè)定多久同步一次二進制日志至磁盤文件中，0 表示不同步，

任何正數(shù)值都表示對二進制每多少次寫操作之后同步一次。當autocommit的值

為1時，每條語句的執(zhí)行都會引起二進制日志同步，否則，每個事務(wù)的提交會引

起二進制日志同步

通過編輯 my.cnf 中的 log-bin 選項可以開啟二進制日志；形式如下：

log-bin [=DIR/[filename]]

其中，DIR 參數(shù)指定二進制文件的存儲路徑；filename 參數(shù)指定二級制文件的文件名，其

形式為 filename.number，number 的形式為 000001、000002 等。每次重啟 mysql

服務(wù)或運行 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;

#從指定的事件位置開始

注：二進制日志的記錄位置：通常為上一個事件執(zhí)行結(jié)束時間的位置

#指定偏移量(不是語句，是事件)

mysql>show binlog events in 'mysql.000005' from 123 limit 1;

命令行下查看二進制日志：

由于無法使用 cat 等方式直接打開并查看二進制日志；所以必須使用 mysqlbinlog 命令。

但是當正在執(zhí)行 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 事件結(jié)束處

# 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*/;

刪除二進制日志信息：

二進制日志會記錄大量的信息（其中包含一些無用的信息）。如果很長時間不清理二進制日

志，將會浪費很多的磁盤空間。但是，刪除之后可能導致數(shù)據(jù)庫崩潰時無法進行恢復，所以

若要刪除二進制日志首先將其和數(shù)據(jù)庫備份一份，其中也只能刪除備份前的二進制日志，新

產(chǎn)生的日志信息不可刪。也不可在關(guān)閉 mysql 服務(wù)器之后直接刪除因為這樣可能會給數(shù)據(jù)

庫帶來錯誤的。若非要刪除二進制日志需要做如下操作：導出備份數(shù)據(jù)庫和二進制日志文件

進行壓縮歸檔存儲。刪除二進制文件的方法如下：

方法 1：根據(jù)文件或時間點來刪除二進制日志：

語法形式：

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

其 中 TO 'log_name' 表 示 把 這 個 文 件 之 前 的 其 他 文 件 都 刪 除 掉 ， 也 可 使 用

BEFORE datetime_expr 指定把哪個時間之前的二進制文件刪除了。

或者用 ls 查看

方法2： 刪除所有的二進制日志（慎用）：

使用 RESET MASTER 語句可以刪除所有的二進制日志。該語句的形式如下：

不建議在生產(chǎn)環(huán)境下使用此操作；刪除所有的二進制日志后，Mysql 將會重新創(chuàng)建新的二

進制日志。新二進制日志的編號從 000001 開始。

3、事務(wù)日志(或稱 redo 日志)

事務(wù)日志（InnoDB 特有的日志）可以幫助提高事務(wù)的效率。使用事務(wù)日志，存儲引擎在修

改表的數(shù)據(jù)時只需要修改其內(nèi)存拷貝，再把修改行為記錄到持久在硬盤上的事務(wù)日志中，而

不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志采用追加的方式，因此寫日志的操作是

磁盤上一小塊區(qū)域內(nèi)的順序 I/O，而不像隨機 I/O 需要在磁盤的多個地方移動磁頭，所以

采用事務(wù)日志的方式相對來說要快得多。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺可

以慢慢的刷回到磁盤。目前大多數(shù)的存儲引擎都是這樣實現(xiàn)的。

如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化，但數(shù)據(jù)本身還沒有寫回磁盤，此時系統(tǒng)崩潰，

存儲引擎在重啟時能夠自動恢復這部分修改的數(shù)據(jù)。具有的恢復方式則視存儲引擎而定。

一般情況下，mysql 會默認提供多種存儲引擎,你可以通過下面的查看:

查看你的 mysql 現(xiàn)在已提供什么存儲引擎:

mysql> show engines;

看你的 mysql 當前默認的存儲引擎:

mysql> show variables like '%storage_engine%';

你要看某個表用了什么引擎(在顯示結(jié)果里參數(shù) engine 后面的就表示該表當前用的存儲引

擎):

mysql> show create table 表名;

注：

create table 庫名.表名 engine = innodb;

這樣就可以將表的引擎變更為 innodb 引擎了。

也可以在創(chuàng)建表之后通過下面語句來變更：

alter table 庫名.表名 engine =innodb;

查看事務(wù)日志的定義：

mysql> show global variables like '%log%';

顯示結(jié)果：

| innodb_flush_log_at_timeout| 1 |

| innodb_flush_log_at_trx_commit | 1 #在事務(wù)提交時 innodb 是否同步日志從緩沖區(qū)到文件中，

當這個值為 1（默認值）之時，在每個事務(wù)提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作

的刷新，性能會很差造成大量的磁盤 I/O 但這種方式最安全；如果設(shè)為 2,每次提交事務(wù)都會寫日志，但并

不會執(zhí)行刷的操作。每秒定時會刷到日志文件。要注意的是，并不能保證 100%每秒一定都會刷到磁盤，這

要取決于進程的調(diào)度。每次事務(wù)提交的時候?qū)?shù)據(jù)寫入事務(wù)日志，而這里的寫入僅是調(diào)用了文件系統(tǒng)的寫

入操作，而文件系統(tǒng)是有 緩存的，所以這個寫入并不能保證數(shù)據(jù)已經(jīng)寫入到物理磁盤。設(shè)置為 0，日志緩

沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務(wù)提交不做任何操作。

注：刷寫的概念

刷寫其實是兩個操作，刷（flush）和寫（write），區(qū)分這兩個概念是很重要的。

在大多數(shù)的操作系統(tǒng)中，把Innodb 的 log buffer（內(nèi)存）寫入日志（調(diào)用系統(tǒng)調(diào)

用write），只是簡單的把數(shù)據(jù)移到操作系統(tǒng)緩存中，操作系統(tǒng)緩存同樣指的是內(nèi)

存。并沒有實際的持久化數(shù)據(jù)。

所以，通常設(shè)為 0 和 2 的時候，在崩潰或斷電的時候會丟失最后一秒的數(shù)據(jù)，因

為這個時候數(shù)據(jù)只是存在于操作系統(tǒng)緩存。之所以說“通?！?，可能會有丟失不只

1 秒的數(shù)據(jù)的情況，比如說執(zhí)行 flush操作的時候阻塞了。

總結(jié)

設(shè)為1 當然是最安全的，但性能頁是最差的（相對其他兩個參數(shù)而言，但不是不

能接受）。如果對數(shù)據(jù)一致性和完整性要求不高，完全可以設(shè)為 2,如果只最求性

能，例如高并發(fā)寫的日志服務(wù)器，設(shè)為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 中設(shè)置幾組事務(wù)日志，默認是 2

|

| innodb_log_group_home_dir| .  /#定義 innodb 事務(wù)日志組的位置,此位置設(shè)置默認為 MySQL的 datadir |

每個事務(wù)日志都是大小為 50 兆的文件（不同版本的 mysql 有差異）：

在 mysql 中默認以 ib_logfile0,ib_logfile1 名稱存在

4、慢查詢?nèi)罩荆簊low query log

顧名思義，慢查詢?nèi)罩局杏涗浀氖菆?zhí)行時間較長的 query，也就是我們常說的 slowquery。

慢查詢?nèi)罩静捎玫氖呛唵蔚奈谋靖袷?，可以通過各種文本編輯器查看其中的內(nèi)容。其中

記錄了語句執(zhí)行的時刻，執(zhí)行所消耗的時間，執(zhí)行用戶，連接主機等相關(guān)信息。

慢查詢?nèi)罩镜淖饔茫?/span>

慢查詢?nèi)罩臼怯脕碛涗泩?zhí)行時間超過指定時間的查詢語句。通過慢查詢?nèi)罩?，可以查找出?/span>

些查詢語句的執(zhí)行效率很低，以便進行優(yōu)化。一般建議開啟，它對服務(wù)器性能的影響微乎其

微，但是可以記錄 mysql 服務(wù)器上執(zhí)行了很長時間的查詢語句?？梢詭椭覀兌ㄎ恍阅軉?/span>

題的。MySQL 還提供了專門用來分析滿查詢?nèi)罩镜墓ぞ叱绦?mysqldumpslow，用來幫助數(shù)

據(jù)庫管理人員解決可能存在的性能問題。

查看慢查詢?nèi)罩镜亩x：

啟動和設(shè)置慢查詢?nèi)罩荆?/span>

方法 1：通過配置文件 my.cnf 開啟慢查詢?nèi)罩荆?/span>

注：在不同的 mysql 版本中，開啟慢查詢?nèi)罩緟?shù)不太一樣，不過都可以通過 show variables like "%slow%" 和 show variables like "%long%"查看出來。

其中：

slow_query_log： off 關(guān)閉狀態(tài) on 開啟狀態(tài)

slow_query_log_file 慢查詢?nèi)罩敬娣诺攸c

long_query_time 選項來設(shè)置一個時間值，時間以秒為單位，可以精確到微秒。如果查詢

時間超過了這個時間值（默認為 10 秒），這個查詢語句將被記錄到慢查詢?nèi)罩局?設(shè)置為 0

的話表示記錄所有的查詢。

slow_launch_time 表示如果建立線程花費了比這個值更長的時間,slow_launch_threads 計

數(shù)器將增加

注：如果不指定存儲路徑，慢查詢?nèi)罩灸J存儲到 mysql 數(shù)據(jù)庫的數(shù)據(jù)文件下，如果不指

定文件名，默認文件名為 hostname-slow.log

修改 my.cnf 文件：

重啟 mysqld 服務(wù)

再次查詢慢查詢?nèi)罩径x：

方法 2：通過登錄 mysql 服務(wù)器直接定義，方式如下：

mysql>set globalslow_query_log=1; #開啟慢查詢?nèi)罩?/span>

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; #更改時間（全局中，重啟服務(wù)則

重置）

mysql> SHOWVARIABLES LIKE 'long%'; #查詢定義時間

查看慢查詢?nèi)罩?/span>

mysql> use mysql

mysql>selectuser,host from user where user="root";

或用系統(tǒng)查看文件內(nèi)容命令如 cat 直接查看慢日志文件

第一行表示記錄日志時的時間。其格式是 YYYY-MM-DD HH:MM:SS。我們可以看出上面的查

詢記錄于 2017 年 1月 12日上午 11:51：20 - 注意：這個是服務(wù)器時間.

MySql 用戶、服務(wù)器以及主機名第三行表示總的查詢時間、鎖定時間、"發(fā)送"或者返回的行

數(shù)

Query_time: 0.000255 表示用了 0.000255 秒

Lock_time: 0.000047 表示鎖了 0.000047 秒

Rows_sent: 4 表示返回 4 行

Rows_examined: 4 表示一共查了 4 行

SET timestamp=UNIXTIME; 這是查詢實際發(fā)生的時間

何將其變成一個有用的時間，將 Unix 時間轉(zhuǎn)成一個可讀的時間，可以使用 date –d@日志

中的時間戳

以看到查詢進行的同時記錄了該日志，但是對于一臺超負載的服務(wù)器常常并非如此。因此記

?。篠ET timestamp= value 才是實際的查詢的執(zhí)行時間。

慢查詢分析 mysqldumpslow

們可以通過打開 log 文件查看得知哪些 SQL 執(zhí)行效率低下。從日志中，可以發(fā)現(xiàn)查詢時間

超過 long_query_time 時間 的 query 為慢查詢，而小于 long_query_time 時間 的沒有

出現(xiàn)在此日志中。

如果慢查詢?nèi)罩局杏涗泝?nèi)容很多，可以使用 mysqldumpslow 工具（MySQL 客戶端安裝自

帶）來對慢查詢?nèi)罩具M行分類匯總。mysqldumpslow 對日志文件進行了分類匯總，顯示匯

總后摘要結(jié)果

進入 log 的存放目錄，運行

[root@localhost data]# mysqldumpslow mysqld-slow.log

注：

mysqldumpslow-s c -t 10 /database/mysql/slow-query.log

這會輸出記錄次數(shù)最多的 10 條 SQL 語句，其中：

-s, 是表示按照何種方式排序，c、t、l、r 分別是按照記錄數(shù)、查詢時間、鎖定時間、返回行

數(shù)來排序；

-t, 是 top n 的意思，即為返回前面多少條的數(shù)據(jù)；

-g, 后邊可以寫一個正則匹配模式，大小寫不敏感的；

詳細選項查看幫助：#mysqldumpslow --help

例如：

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回記錄集最多的 10 個查詢。

/path/mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/slow-log

得到按照時間排序的前 10 條里面含有左連接的查詢語句。

2 ）數(shù)據(jù)文據(jù)

在 MySQL 中每一個數(shù)據(jù)庫都會在定義好（或者默認）的數(shù)據(jù)目錄下存在一個以數(shù)據(jù)庫名字

命名的文件夾，用來存放該數(shù)據(jù)庫中各種表數(shù)據(jù)文件。不同的 MySQL 存儲引擎有各自不同

的數(shù)據(jù)文件。如 MyISAM 用“.MYD”作為擴展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV

用“.csv”，等等。

如何查看你的 mysql 現(xiàn)在已提供什么存儲引擎:

mysql> show engines;

看你的 mysql 當前默認的存儲引擎:

mysql> show variables like '%storage_engine%';

你要看某個表用了什么引擎(在顯示結(jié)果里參數(shù) engine 后面的就表示該表當前用的存儲引

擎):

mysql> show create table 表名;

注：

create table 庫名.表名 engine = innodb;這樣就可以將表的引擎變更為 innodb 引擎了。

登錄 mysql，創(chuàng)建一個數(shù)據(jù)庫如 testdb，并在數(shù)據(jù)庫中創(chuàng)建一個表，如下圖所示：

查看數(shù)據(jù)庫所在目錄會發(fā)現(xiàn)數(shù)據(jù)目錄下存在一個以數(shù)據(jù)庫名字命名的文件夾

查看 testdb 目錄的文件列表

從上圖可以看出表使用的是 innodb 存儲引擎。

以 myisam 存儲引擎創(chuàng)建一個測試表 tb2

查看數(shù)據(jù)庫目錄

注：修改 mysql 的默認存儲引擎

1、查看 mysql 存儲引擎命令，在 mysql>提示符下搞入 show engines;字段 Support 為:Default表示默認存儲引擎

2 、 設(shè) 置 InnoDB 為 默 認 引 擎 ： 在 配 置 文 件 my.cnf 中 的 [mysqld] 下 面 加 入

default-storage-engine=INNODB 一句

3、重啟 mysql 服務(wù)器：mysqladmin -u root -p shutdown 或者 service mysqld restart 登錄 mysql數(shù)據(jù)庫。

1、“.frm”文件

與表相關(guān)的元數(shù)據(jù)（meta）信息都存放在“.frm”文件中，包括表結(jié)構(gòu)的定義信息等。不論

是什么存儲引擎（MySQL 常用的兩個存儲引擎是 MyISAM 和 InnoDB），每一個表都會有一

個以表名命名的“.frm”文件。所有的“.frm”文件都存放在所屬數(shù)據(jù)庫的文件夾下面。

MyISAM 數(shù)據(jù)庫表文件：.MYD 文件：表數(shù)據(jù)文件；.MYI 文件：索引文件

2、“.MYD”文件

“.MYD”文件是 MyISAM 存儲引擎專用，存放 MyISAM 表的數(shù)據(jù)。每一個 MyISAM 表都會有

一個“.MYD”文件與之對應(yīng)，同樣存放于所屬數(shù)據(jù)庫的文件夾下，和“.frm”文件在一起。

3、“.MYI”文件

“.MYI”文件也是專屬于 MyISAM 存儲引擎的，主要存放 MyISAM 表的索引相關(guān)信息。對于

MyISAM 存儲來說，可以被 cache 的內(nèi)容主要就是來源于“.MYI”文件中。每一個 MyISAM

表對應(yīng)一個“.MYI”文件，存放于位置和“.frm”以及“.MYD”一樣。

InnoDB 采用表空間（tablespace）來管理數(shù)據(jù)，存儲表數(shù)據(jù)和索引。

.ibd 文件：單表表空間文件，每個表使用一個表空間文件（file per table），存放用戶數(shù)據(jù)庫

表數(shù)據(jù)和索引。

InnoDB 共享表空間（即 InnoDB 文件集，ib-file set）：ibdata1、ibdata2 等，存儲 InnoDB 系統(tǒng)

信息和用戶數(shù)據(jù)庫表數(shù)據(jù)和索引，所有表共用。

4、“.ibd”文件和 ibdata 文件

這兩種文件都是存放 Innodb 數(shù)據(jù)的文件，之所以有兩種文件來存放 Innodb 的數(shù)據(jù)（包括索

引），是因為 Innodb 的數(shù)據(jù)存儲方式能夠通過配置來決定是使用共享表空間存放存儲數(shù)據(jù)，

還是獨享表空間存放存儲數(shù)據(jù)。獨享表空間存儲方式使用“.ibd”文件來存放數(shù)據(jù)，且每個

表一個“.ibd”文件，文件存放在和 MyISAM 數(shù)據(jù)相同的位置。如果選用共享存儲表空間來

存放數(shù)據(jù)，則會使用 ibdata 文件來存放，所有表共同使用一個（或者多個，可自行配置）ibdata

文件。

ibdata文件可以通過innodb_data_home_dir和innodb_data_file_path兩個參數(shù)共同配置組成，

innodb_data_home_dir 配置數(shù)據(jù)存放的總目錄，而 innodb_data_file_path 配置每一個文件的

名稱。

innodb_data_file_path 中可以一次配置多個 ibdata 文件。文件可以是指定大小，也可以是自

動擴展的，但是 Innodb 限制了僅僅只有最后一個 ibdata 文件能夠配置成自動擴展類型。當

我們需要添加新的 ibdata 文件的時候，只能添加在 innodb_data_file_path 配置的最后，而且

必須重啟 MySQL 才能完成 ibdata 的添加工作。不過如果我們使用獨享表空間存儲方式的話，

就不會有這樣的問題。

總結(jié)：

共享表空間以及獨占表空間都是針對數(shù)據(jù)的存儲方式而言的。

共享表空間: 某一個數(shù)據(jù)庫的所有的表數(shù)據(jù)，索引文件全部放在一個文件中。

獨占表空間: 每一個表都將會生成以獨立的文件方式來進行存儲，每一個表都有一個.frm 表描

述文件，還有一個.ibd 文件。其中這個文件包括了單獨一個表的數(shù)據(jù)內(nèi)容以及索引內(nèi)容。

兩者之間的優(yōu)缺點

共享表空間：

優(yōu)點：

可以放表空間分成多個文件存放到各個磁盤上。數(shù)據(jù)和文件放在一起方便管理。

缺點：

所有的數(shù)據(jù)和索引存放到一個文件中，多個表及索引在表空間中混合存儲，這樣對于一個表

做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統(tǒng)計分析，日值系統(tǒng)這類應(yīng)用

最不適合用共享表空間。

獨立表空間：

優(yōu)點：

1．每個表都有自已獨立的表空間。

2．每個表的數(shù)據(jù)和索引都會存在自已的表空間中。

3．可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。

4．空間可以回收

相比較之下，使用獨占表空間的效率以及性能會更高一點

查看當前數(shù)據(jù)庫的表空間管理類型

ON 代表獨立表空間管理，OFF 代表共享表空間管理；（查看單表的表空間管理方式，需要查看每個表是否有單獨的數(shù)據(jù)文件）

Innodb 共享表空間配置：

修改 my.cnf 文件：

參數(shù)解釋：

innodb_data_home_dir = "/path/" 數(shù)據(jù)庫文件所存放的目錄

innodb_log_group_home_dir = "/path/" 日志存放目錄

innodb_data_file_path=ibdata1:10M:autoextend 設(shè)置一個可擴展大小的尺寸為 10MB 的數(shù)據(jù)

文件（共享數(shù)據(jù)文件），名為 ibdata1。沒有給出文件的位置，所以默認的是在 MySQL 的數(shù)

據(jù)目錄內(nèi)。

innodb_file_per_table=1｜0 //1 為使用獨占表空間，0 為使用共享表空間

注：InnoDB 不創(chuàng)建目錄，所以在啟動服務(wù)器之前請確認”所配置的路徑目錄”的確存在。

重啟 mysqld 服務(wù)

mysqld啟動失敗,查看錯誤日志

#tail -20 /usr/local/mysql/data/mysqld.err

顯示內(nèi)容如下：

注：不同版本的 mysql 報錯略有不同，注意看錯誤日志的內(nèi)容。

從錯誤日志中顯示可以看出

在/etc/my.cnf 文件中設(shè)置 6400 頁而當前 ibdata1 為 768 頁

需要計算 768/64=12

修改配置為

重啟 mysqld 服務(wù)

啟動 mysql，成功！

注：計算公式：64pages 相當于 1M，1page 是 16KB

如果不清楚默認文件 page 大小，可以先 du -h ibdata1 查看下，再去設(shè)置：

這說明 mysql5.7.13 中 ibdata 初始化為 12M

登錄 mysql 執(zhí)行 mysql> show variables like '%innodb_file_per_table%';

這時新建的表就會使用共享表空間了。

創(chuàng)建一個數(shù)據(jù)庫 testdb 并新建一個表

向表中插入若干行數(shù)據(jù)

這里定義一個存儲過程向表中插入 100000 行數(shù)據(jù)

調(diào)用存儲過程

查看表中行數(shù)：

如何查看表在表空間占用情況：

方法 1：對 INNODB，你可以直接用命令 show table status 查看某個表的表空間占用情況。

方法 2：

如果想知道 MySQL 數(shù)據(jù)庫中每個表占用的空間、表記錄的行數(shù)的話，可以打開 MySQL 的

information_schema 數(shù)據(jù)庫。在該庫中有一個 TABLES 表，這個表主要字段分別是：

TABLE_SCHEMA : 數(shù)據(jù)庫名

TABLE_NAME：表名

ENGINE：所使用的存儲引擎

TABLE_ROWS：記錄數(shù)

DATA_LENGTH：數(shù)據(jù)大小

INDEX_LENGTH：索引大小

3 、Replication  相關(guān)文件：

1）master.info 文件：

master.info 文件存在于 Slave 端的數(shù)據(jù)目錄下，里面存放了該 Slave 的 Master 端的相關(guān)信

息，包括 Master 的主機地址，連接用戶，連接密碼，連接端口，當前日志位置，已經(jīng)讀取

到的日志位置等信息。

2）relay log 和 relay log index

mysql-relay-bin.xxxxxn 文件用于存放 Slave 端的 I/O 線程從 Master 端所讀取到的 Binary Log

信息，然后由 Slave 端的 SQL 線程從該 relay log 中讀取并解析相應(yīng)的日志信息，轉(zhuǎn)化成

Master 所執(zhí)行的 SQL 語句，然后在 Slave 端應(yīng)用。

mysql-relay-bin.index 文件的功能類似于 mysql-bin.index，同樣是記錄日志的存放位置的絕對

路徑，只不過他所記錄的不是 Binary Log，而是 Relay Log。

3）relay-log.info 文件：

類似于 master.info，它存放通過 Slave 的 I/O 線程寫入到本地的 relay log 的相關(guān)信

息。供 Slave 端的 SQL 線程以及某些管理操作隨時能夠獲取當前復制的相關(guān)信息。

4 、其他文件：

1）system config file

MySQL 的系統(tǒng)配置文件一般都是 my.cnf，默認存放在"/etc"目錄下，my.cnf 文件中包含多種

參數(shù)選項組（group），每一種參數(shù)組都通過中括號給定了固定的組名，如“[mysqld]”組中

包括了 mysqld 服務(wù)啟動時候的初始化參數(shù)，“[client]”組中包含著客戶端工具程序可以讀取

的參數(shù)。

2）pid file

pid file 是 mysqld 應(yīng)用程序在 Unix/Linux 環(huán)境下的一個進程文件，和許多其他

Unix/Linux 服務(wù)端程序一樣，存放著自己的進程 id。

3）socket file

socket 文件也是在 Unix/Linux 環(huán)境下才有的，用戶在 Unix/Linux 環(huán)境下客戶端連接可以不

通過 TCP/IP 網(wǎng)絡(luò)而直接使用 Unix Socket 來連接 MySQL。

mysql 有兩種連接方式，常用的一般是 tcp

mysql–hmysql 主機 ip -uroot -pxxx

mysql-S /path/mysql.sock

注：采用 unix socket 連接方式，比用 tcp 的方式更快，但只適用于 mysql 和應(yīng)用同在一臺 PC上。

謝謝觀看，真心的希望能幫到您！

本文出自 “一盞燭光” 博客，謝絕轉(zhuǎn)載！