MySQL的存儲(chǔ)引擎與日志說明

MySQL的存儲(chǔ)引擎與日志說明  

1.1 存儲(chǔ)引擎的介紹

1.1.1 文件系統(tǒng)存儲(chǔ)

　　文件系統(tǒng)：操作系統(tǒng)組織和存取數(shù)據(jù)的一種機(jī)制。文件系統(tǒng)是一種軟件。

　　類型：ext2 3 4 ，xfs 數(shù)據(jù)。  不管使用什么文件系統(tǒng)，數(shù)據(jù)內(nèi)容不會(huì)變化，不同的是，存儲(chǔ)空間、大小、速度。

1.1.2 mysql數(shù)據(jù)庫存儲(chǔ)

　　MySQL引擎： 可以理解為，MySQL的“文件系統(tǒng)”，只不過功能更加強(qiáng)大。

　　MySQL引擎功能： 除了可以提供基本的存取功能，還有更多功能事務(wù)功能、鎖定、備份和恢復(fù)、優(yōu)化以及特殊功能。

1.1.3 MySQL存儲(chǔ)引擎種類

MySQL 提供以下存儲(chǔ)引擎：

InnoDB、MyISAM (最常用的兩種) MEMORY、ARCHIVE、FEDERATED、EXAMPLE BLACKHOLE、MERGE、NDBCLUSTER、CSV

　　除此之外還可以使用第三方存儲(chǔ)引擎。

1.1.4 innodb與myisam對(duì)比

InnoDb 引擎

1. 支持ACID的事務(wù)，支持事務(wù)的四種隔離級(jí)別；
2. 支持行級(jí)鎖及外鍵約束：因此可以支持寫并發(fā)；
3. 不存儲(chǔ)總行數(shù)；
4. 一個(gè)InnoDb引擎存儲(chǔ)在一個(gè)文件空間（共享表空間，表大小不受操作系統(tǒng)控制，一個(gè)表可能分布在多個(gè)文件里），也有可能為多個(gè)（設(shè)置為獨(dú)立表空，表大小受操作系統(tǒng)文件大小限制，一般為2G），受操作系統(tǒng)文件大小的限制；
5. 主鍵索引采用聚集索引（索引的數(shù)據(jù)域存儲(chǔ)數(shù)據(jù)文件本身），輔索引的數(shù)據(jù)域存儲(chǔ)主鍵的值；因此從輔索引查找數(shù)據(jù)，需要先通過輔索引找到主鍵值，再訪問輔索引；最好使用自增主鍵，防止插入數(shù)據(jù)時(shí)，為維持B+樹結(jié)構(gòu)，文件的大調(diào)整。

Innodb 的主索引結(jié)構(gòu)如下：

MyISAM 引擎

1. 不支持事務(wù)，但是每次查詢都是原子的；
2. 支持表級(jí)鎖，即每次操作是對(duì)整個(gè)表加鎖；
3. 存儲(chǔ)表的總行數(shù)；
4. 一個(gè)MYISAM表有三個(gè)文件：索引文件、表結(jié)構(gòu)文件、數(shù)據(jù)文件；
5. 采用菲聚集索引，索引文件的數(shù)據(jù)域存儲(chǔ)指向數(shù)據(jù)文件的指針。輔索引與主索引基本一致，但是輔索引不用保證唯一性。

MYISAM 的主索引結(jié)構(gòu)如下：

兩種索引數(shù)據(jù)查找過程如下：

1.2 innodb存儲(chǔ)引擎

　　在MySQL5.5版本之后，默認(rèn)的 存儲(chǔ)引擎 ，提供高可靠性和高性能。

1.2.1 Innodb引擎的優(yōu)點(diǎn)

a)    事務(wù)安全（遵從ACID） b)    MVCC（Multi-Versioning Concurrency Control，多版本并發(fā)控制） c)    InnoDB行級(jí)鎖 d)    支持外鍵引用完整性約束 e)    出現(xiàn)故障后快速自動(dòng)恢復(fù)（crash safe recovery） f)    用于在內(nèi)存中緩存數(shù)據(jù)和索引的緩沖區(qū)池（buffer pool（data buffer page  log buffer page） 、undo buffer page） g)    大型數(shù)據(jù)卷上的最大性能 h)    將對(duì)表的查詢與不同存儲(chǔ)引擎混合 i)    Oracle樣式一致非鎖定讀取(共享鎖) j)    表數(shù)據(jù)進(jìn)行整理來優(yōu)化基于主鍵的查詢（聚集索引）

1.2.2 Innodb功能總覽

1.2.3 查詢存儲(chǔ)引擎的方法

1、使用 SELECT 確認(rèn)會(huì)話存儲(chǔ)引擎：

SELECT @@default_storage_engine; 或 show variables like '%engine%';

2、使用 SHOW 確認(rèn)每個(gè)表的存儲(chǔ)引擎：

SHOW CREATE TABLE City\G SHOW TABLE STATUS LIKE 'CountryLanguage'\G

3、使用 INFORMATION_SCHEMA 確認(rèn)每個(gè)表的存儲(chǔ)引擎：

SELECT TABLE_NAME, ENGINE FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'City' AND TABLE_SCHEMA = 'world_innodb'\G

4、從5.1版本，遷移到5.5版本以上版本

   　　假如5.1版本數(shù)據(jù)庫所有生產(chǎn)表都是myisam的。

  　　 使用mysqldump備份后，一點(diǎn)要替換備份的文件中的engine(引擎)字段，從myisam替換為innodb（可以使用sed命令），否則遷移無任何意義。

  　　 數(shù)據(jù)庫升級(jí)時(shí)，要注意其他配套設(shè)施的兼容性，注意代碼能否兼容新特性。

1.2.4 設(shè)置存儲(chǔ)引擎

1、在啟動(dòng)配置文件中設(shè)置服務(wù)器存儲(chǔ)引擎：

[mysqld] default-storage-engine=<Storage Engine>

2、使用 SET 命令為當(dāng)前客戶機(jī)會(huì)話設(shè)置：

SET @@storage_engine=<Storage Engine>;

3、在 CREATE TABLE 語句指定：

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

1.3 InnoDB存儲(chǔ)引擎的存儲(chǔ)結(jié)構(gòu)

1.3.1 InnoDB 系統(tǒng)表空間特性

1. 默認(rèn)情況下，InnoDB 元數(shù)據(jù)、撤消日志和緩沖區(qū)存儲(chǔ)在系統(tǒng)“表空間”中。
2. 這是單個(gè)邏輯存儲(chǔ)區(qū)域，可以包含一個(gè)或多個(gè)文件。
3. 每個(gè)文件可以是常規(guī)文件或原始分區(qū)。
4. 最后的文件可以自動(dòng)擴(kuò)展。

1.3.2 表空間的定義

　　表空間：MySQL數(shù)據(jù)庫存儲(chǔ)的方式

　　　　表空間中包含數(shù)據(jù)文件

　　MySQl表空間和數(shù)據(jù)文件是1:1的關(guān)系

　　　　共享表空間除外，是可以1:N關(guān)系

1.3.3 表空間類型

　　1、共享表空間：ibdata1~ibdataN,一般是2-3個(gè)

　　2、獨(dú)立表空間：存放在指定庫目錄下，例如data/world/目錄下的city.ibd

　　　　表空間位置（datadir）：

　　　　data/目錄下

1.3.4 系統(tǒng)表空間的存儲(chǔ)內(nèi)容

共享表空間（物理存儲(chǔ)結(jié)構(gòu)）

  　　 ibdata1~N 通常被叫做系統(tǒng)表空間，是數(shù)據(jù)初始化生成的

  　　 系統(tǒng)元數(shù)據(jù)，基表數(shù)據(jù)，除了表內(nèi)容數(shù)據(jù)之外的數(shù)據(jù)。

   　　tmp 表空間（一般很少關(guān)注）

   　　undo日志 ：數(shù)據(jù)--回滾數(shù)據(jù)（回滾日志使用）

  　　 redo日志 ：ib_logfile0~N 存放系統(tǒng)的innodb表的一些重做日志。

  　　 說明：undo日志默認(rèn)實(shí)在ibdata中的，在5.6以后是可以單獨(dú)定義的。

       　　 tmp 表空間在 5.7 版本之后 被移出了ibdata1，變?yōu)閕btmp1

      　　  在 5.5 版本之前 ，所有的應(yīng)用數(shù)據(jù)也都默認(rèn)存放到了ibdata中。

獨(dú)立表空間( 一個(gè)存儲(chǔ)引擎的功能)

  　　 在5.6之后，默認(rèn)的情況下會(huì)單表單獨(dú)存儲(chǔ)到獨(dú)立表空間文件

　　　除了系統(tǒng)表空間之外，InnoDB 還在數(shù)據(jù)庫目錄中創(chuàng)建另外的表空間，用于每個(gè) InnoDB 表的 .ibd 文件。

　　   InnoDB 創(chuàng)建的每個(gè)新表在數(shù)據(jù)庫目錄中設(shè)置一個(gè) .ibd 文件來搭配表的.frm 文件。

　　　可以使用 innodb_file_per_table 選項(xiàng)控制此設(shè)置，更改該設(shè)置僅會(huì)更改已創(chuàng)建的新表的默認(rèn)值。。

1.3.5 設(shè)置共享表空間

查看當(dāng)前的共享表空間設(shè)置

mysql> show variables like 'innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name         | Value                  | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ 1 row in set (0.00 sec)

　設(shè)置共享表空間：

　　一般是在初始搭建環(huán)境的時(shí)候就配置號(hào)，預(yù)設(shè)值一般為1G；且最后一個(gè)為自動(dòng)擴(kuò)展。

[root@db02 world]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

重啟服務(wù)查看當(dāng)前的共享表空間設(shè)置

mysql> show variables like 'innodb_data_file_path'; +-----------------------+-------------------------------------+ | Variable_name         | Value                               | +-----------------------+-------------------------------------+ | innodb_data_file_path | ibdata1:76M;ibdata2:100M:autoextend | +-----------------------+-------------------------------------+ 1 row in set (0.00 sec)

1.3.6 設(shè)置獨(dú)立表空間

　　　獨(dú)立表空間在5.6版本是默認(rèn)開啟的。

　　　 獨(dú)立表空間注意事項(xiàng)： 不開起獨(dú)立表空間，共享表空間會(huì)占用很大

mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | ON    | +-----------------------+-------+ 1 row in set (0.00 sec)

   在參數(shù)文件/etc/my.cnf  可以控制獨(dú)立表空間

關(guān)閉獨(dú)立表空間 （0是關(guān)閉，1是開啟）

[root@db02 clsn]# vim /etc/my.cnf [mysqld] innodb_file_per_table=0

查看獨(dú)立表空間配置

mysql> show variables like '%per_table%' ; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | OFF   | +-----------------------+-------+ 1 row in set (0.00 sec)

小結(jié):

innodb_file_per_table=0    關(guān)閉獨(dú)立表空間 innodb_file_per_table=1    開啟獨(dú)立表空間，單表單存儲(chǔ)

1.4 MySQL中的事務(wù)

　　一組數(shù)據(jù)操作執(zhí)行步驟，這些步驟被視為一個(gè)工作單元

  　　　　 用于對(duì)多個(gè)語句進(jìn)行分組，可以在多個(gè)客戶機(jī)并發(fā)訪問同一個(gè)表中的數(shù)據(jù)時(shí)使用。

　　所有步驟都成功或都失敗

  　　　　 如果所有步驟正常，則執(zhí)行，如果步驟出現(xiàn)錯(cuò)誤或不完整，則取消。

簡(jiǎn)單來說事務(wù)就是：保證工作單元中的語句同時(shí)成功或同時(shí)失敗。

事務(wù)處理流程示意圖

1.4.1 事務(wù)是什么

　　與其給事務(wù)定義，不如說一說事務(wù)的特性。眾所周知，事務(wù)需要滿足 ACID 四個(gè)特性。

A(atomicity) 原子性。

　　　一個(gè)事務(wù)的執(zhí)行被視為一個(gè)不可分割的最小單元。事務(wù)里面的操作，要么全部成功執(zhí)行，要么全部失敗回滾，不可以只執(zhí)行其中的一部分。

所有語句作為一個(gè)單元全部成功執(zhí)行或全部取消。 updata t1 set money=10000-17 where  id=wxid1 updata t1 set money=10000+17  where  id=wxid2

C(consistency) 一致性 。

　　一個(gè)事務(wù)的執(zhí)行不應(yīng)該破壞數(shù)據(jù)庫的完整性約束。如果上述例子中第2個(gè)操作執(zhí)行后系統(tǒng)崩潰，保證A和B的金錢總計(jì)是不會(huì)變的。

如果數(shù)據(jù)庫在事務(wù)開始時(shí)處于一致狀態(tài)，則在執(zhí)行該事務(wù)期間將保留一致狀態(tài)。     updata t1 set money=10000-17 where  id=wxid1     updata t1 set money=10000+17  where  id=wxid2     在以上操作過程中，去查自己賬戶還是10000

I(isolation) 隔離性。

　　通常來說，事務(wù)之間的行為不應(yīng)該互相影響。然而實(shí)際情況中，事務(wù)相互影響的程度受到隔離級(jí)別的影響。文章后面會(huì)詳述。

　　事務(wù)之間不相互影響。在做操作的時(shí)候，其他人對(duì)這兩個(gè)賬戶做任何操作，在不同的隔離條件下，可能一致性保證又不一樣

隔離級(jí)別

    隔離級(jí)別會(huì)影響到一致性。     read-uncommit  X     read-commit   可能會(huì)用的一種級(jí)別     repeatable-read   默認(rèn)的級(jí)別，和oracle一樣的     SERIALIZABLE       嚴(yán)格的默認(rèn)，一般不會(huì)用

　　此規(guī)則除了受隔離級(jí)別控制，還受鎖控制，可以聯(lián)想一下NFS的實(shí)現(xiàn)

D(durability) 持久性。

　　事務(wù)提交之后，需要將提交的事務(wù)持久化到磁盤。即使系統(tǒng)崩潰，提交的數(shù)據(jù)也不應(yīng)該丟失。

　保證數(shù)據(jù)落地，才算事務(wù)真正安全

1.4.2 事務(wù)的控制語句

常用的事務(wù)控制語句：

    START TRANSACTION（或 BEGIN）：顯式開始一個(gè)新事務(wù)     COMMIT：永久記錄當(dāng)前事務(wù)所做的更改(事務(wù)成功結(jié)束)     ROLLBACK：取消當(dāng)前事務(wù)所做的更改(事務(wù)失敗結(jié)束)

需要知道的事務(wù)控制語句：

    SAVEPOINT：分配事務(wù)過程中的一個(gè)位置，以供將來引用     ROLLBACK TO SAVEPOINT：取消在 savepoint 之后執(zhí)行的更改     RELEASE SAVEPOINT：刪除 savepoint 標(biāo)識(shí)符     SET AUTOCOMMIT：為當(dāng)前連接禁用或啟用默認(rèn) autocommit模式

1.4.3 autocommit參數(shù)

　　在MySQL5.5開始，開啟事務(wù)時(shí)不再需要begin或者start transaction語句。并且，默認(rèn)是開啟了Autocommit模式，作為一個(gè)事務(wù)隱式提交每個(gè)語句。

　　在有些業(yè)務(wù)繁忙企業(yè)場(chǎng)景下，這種配置可能會(huì)對(duì)性能產(chǎn)生很大影響，但對(duì)于安全性上有很大提高。將來，我們需要去權(quán)衡我們的業(yè)務(wù)需求去調(diào)整是否自動(dòng)提交。

　　注意：在生產(chǎn)中，根據(jù)實(shí)際需求選擇是否可開啟，一般銀行類業(yè)務(wù)會(huì)選擇關(guān)閉。

查看當(dāng)前autocommit狀態(tài)：

mysql> show variables like '%autoc%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | ON    | +---------------+-------+ 1 row in set (0.00 sec)

修改配置文件，并重啟

[root@db02 world]# vim /etc/my.cnf [mysqld] autocommit=0

再次查看autocommit狀態(tài)

mysql> show variables like '%autoc%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | OFF   | +---------------+-------+ 1 row in set (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ |            0 | +--------------+ 1 row in set (0.00 sec)

　　 說明： autocommit設(shè)置為開啟的對(duì)比

   　　 優(yōu)點(diǎn)： 數(shù)據(jù)安全性好，每次修改都會(huì)落地

  　　  缺點(diǎn)： 不能進(jìn)行銀行類的交易事務(wù)、產(chǎn)生大量小的IO

1.4.4 導(dǎo)致提交的非事務(wù)語句：

DDL語句： （ALTER、CREATE 和 DROP） DCL語句： （GRANT、REVOKE 和 SET PASSWORD） 鎖定語句：（LOCK TABLES 和 UNLOCK TABLES）

導(dǎo)致隱式提交的語句示例：

TRUNCATE TABLE LOAD DATA INFILE SELECT FOR UPDATE

用于隱式提交的 SQL 語句：

START TRANSACTION SET AUTOCOMMIT = 1

1.5 redo與undo

1.5.1 事務(wù)日志undo

undo原理：

　　Undo Log的原理很簡(jiǎn)單，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個(gè)地方（這個(gè)存儲(chǔ)數(shù)據(jù)備份的地方稱為Undo Log）。然后進(jìn)行數(shù)據(jù)的修改。

　　如果出現(xiàn)了錯(cuò)誤或者用戶執(zhí)行了ROLLBACK語句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)。

除了可以保證事務(wù)的原子性，Undo Log也可以用來輔助完成事務(wù)的持久化。

undo 是什么？

　　undo,顧名思義“回滾日志”，是事務(wù)日志的一種。

作用是什么？

　　在事務(wù)ACID過程中，實(shí)現(xiàn)的是“A“原子性的作用。

用Undo Log 實(shí)現(xiàn)原子性和持久化的事務(wù)的簡(jiǎn)化過程

  假設(shè)有A、B兩個(gè)數(shù)據(jù)，值分別為1,2。   A.事務(wù)開始.   B.記錄A=1到undo log.   C.修改A=3.   D.記錄B=2到undo log.   E.修改B=4.   F.將undo log寫到磁盤。   G.將數(shù)據(jù)寫到磁盤。   H.事務(wù)提交

  這里有一個(gè)隱含的前提條件：‘?dāng)?shù)據(jù)都是先讀到內(nèi)存中，然后修改內(nèi)存中的數(shù)據(jù)，最后將數(shù)據(jù)寫回磁盤之所以能同時(shí)保證原子性和持久化，是因?yàn)橐韵绿攸c(diǎn)：

  A. 更新數(shù)據(jù)前記錄Undo log。   B. 為了保證持久性，必須將數(shù)據(jù)在事務(wù)提交前寫到磁盤。只要事務(wù)成功提交，數(shù)據(jù)必然已經(jīng)持久化。   C. Undo log必須先于數(shù)據(jù)持久化到磁盤。如果在G,H之間系統(tǒng)崩潰，undo log是完整的，可以用來回滾事務(wù)。   D. 如果在A-F之間系統(tǒng)崩潰,因?yàn)閿?shù)據(jù)沒有持久化到磁盤。所以磁盤上的數(shù)據(jù)還是保持在事務(wù)開始前的狀態(tài)。

缺陷：

　　每個(gè)事務(wù)提交前將數(shù)據(jù)和Undo Log寫入磁盤，這樣會(huì)導(dǎo)致大量的磁盤IO，因此性能很低。如果能夠?qū)?shù)據(jù)緩存一段時(shí)間，就能減少IO提高性能。但是這樣就會(huì)喪失事務(wù)的持久性。

　　因此引入了另外一種機(jī)制來實(shí)現(xiàn)持久化，即Redo Log.

1.5.2 事務(wù)日志redo

redo 原理：

　　和Undo Log相反，Redo Log記錄的是新數(shù)據(jù)的備份。在事務(wù)提交前，只要將Redo Log持久化即可，不需要將數(shù)據(jù)持久化。當(dāng)系統(tǒng)崩潰時(shí)，雖然數(shù)據(jù)沒有持久化，但是Redo Log已經(jīng)持久化。

　　系統(tǒng)可以根據(jù)Redo Log的內(nèi)容，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)。

Redo 是什么？

　　redo,顧名思義“重做日志”，是事務(wù)日志的一種。

作用是什么？

　　在事務(wù)ACID過程中，實(shí)現(xiàn)的是“D”持久化的作用。

Undo + Redo 事務(wù)的簡(jiǎn)化過程

 假設(shè)有A、B兩個(gè)數(shù)據(jù)，值分別為1,2.   A.事務(wù)開始.   B.記錄A=1到undo log.   C.修改A=3.   D.記錄A=3到redo log.   E.記錄B=2到undo log.   F.修改B=4.   G.記錄B=4到redo log.   H.將redo log寫入磁盤。   I.事務(wù)提交

Undo + Redo 事務(wù)的特點(diǎn)

 A. 為了保證持久性，必須在事務(wù)提交前將Redo Log持久化。   B. 數(shù)據(jù)不需要在事務(wù)提交前寫入磁盤，而是緩存在內(nèi)存中。   C. Redo Log 保證事務(wù)的持久性。   D. Undo Log 保證事務(wù)的原子性。   E. 有一個(gè)隱含的特點(diǎn)，數(shù)據(jù)必須要晚于redo log寫入持久存儲(chǔ)。

redo是否持久化到磁盤參數(shù)

innodb_flush_log_at_trx_commit=1/0/2

1.5.3 事務(wù)中的鎖

什么是“鎖”？

　　“鎖”顧名思義就是鎖定的意思。

“鎖”的作用是什么？

　　在事務(wù)ACID過程中，“鎖”和“隔離級(jí)別”一起來實(shí)現(xiàn)“I”隔離性的作用。

鎖的粒度：

　　1、MyIasm：低并發(fā)鎖——表級(jí)鎖

　　2、Innodb：高并發(fā)鎖——行級(jí)鎖

四種隔離級(jí)別：

READ UNCOMMITTED 許事務(wù)查看其他事務(wù)所進(jìn)行的未提交更改 READ COMMITTED    允許事務(wù)查看其他事務(wù)所進(jìn)行的已提交更改 REPEATABLE READ****** 確保每個(gè)事務(wù)的 SELECT 輸出一致； InnoDB 的默認(rèn)級(jí)別 SERIALIZABLE  將一個(gè)事務(wù)的結(jié)果與其他事務(wù)完全隔離

開銷、加鎖速度、死鎖、粒度、并發(fā)性能

表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。 行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。 頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

　　從上述特點(diǎn)可見，很難籠統(tǒng)地說哪種鎖更好，只能就具體應(yīng)用的特點(diǎn)來說哪種鎖更合適！

　　僅從鎖的角度來說：表級(jí)鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

1.6 MySQL 日志管理

1.6.1 MySQL日志類型簡(jiǎn)介

日志的類型的說明：

1.6.2 配置方法

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

[mysqld] log-error=/data/mysql/mysql.log

查看配置方式：

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

作用：

　　記錄mysql數(shù)據(jù)庫的一般狀態(tài)信息及報(bào)錯(cuò)信息，是我們對(duì)于數(shù)

　據(jù)庫常規(guī)報(bào)錯(cuò)處理的常用日志。

mysql> show variables  like '%log%err%'; +---------------------+----------------------------------+ | Variable_name       | Value                            | +---------------------+----------------------------------+ | binlog_error_action | IGNORE_ERROR                     | | log_error           | /application/mysql/data/db02.err | +---------------------+----------------------------------+ 2 rows in set (0.00 sec)

1.6.3 一般查詢?nèi)罩?

配置方法:

[mysqld] general_log=on general_log_file=/data/mysql/server2.log

查看配置方式：

show variables like '%gen%';

作用：

　　記錄mysql所有執(zhí)行成功的SQL語句信息，可以做審計(jì)用，但是我們很少開啟

mysql> show variables like '%gen%'; +------------------+----------------------------------+ | Variable_name    | Value                            | +------------------+----------------------------------+ | general_log      | OFF                              | | general_log_file | /application/mysql/data/db02.log | +------------------+----------------------------------+ 2 rows in set (0.00 sec)

1.7 二進(jìn)制日志

　　二進(jìn)制日志不依賴與存儲(chǔ)引擎的。

　　依賴于sql層，記錄和sql語句相關(guān)的信息

binlog日志作用：

  　　 1、提供備份功能

  　　 2、進(jìn)行主從復(fù)制

 　　  3、基于時(shí)間點(diǎn)的任意恢復(fù)

　　記錄在sql層已經(jīng)執(zhí)行完成的語句，如果是事務(wù)，則記錄已完成的事務(wù)。

　　功能作用： 時(shí)間點(diǎn)備份 和 時(shí)間點(diǎn)恢復(fù)、 主從

二進(jìn)制日志的“總閘”

作用：

1、是否開啟 2、二進(jìn)制日志路徑/data/mysql/ 3、二進(jìn)制日志文件名前綴mysql-bin   4、文件名以"前綴".000001~N log-bin=/data/mysql/mysql-bin

二進(jìn)制日志的“分開關(guān)”：

只有總閘開啟才有意義，默認(rèn)是開啟狀態(tài)。 我們?cè)谟行r(shí)候會(huì)臨時(shí)關(guān)閉掉。 只影響當(dāng)前會(huì)話。 sql_log_bin=1/0

1.7.1 二進(jìn)制日志的格式

statement，語句模式：

記錄信息簡(jiǎn)潔，記錄的是SQL語句本身。但是在語句中出現(xiàn)函數(shù)操作的話，有可能記錄的數(shù)據(jù)不準(zhǔn)確。 5.6中默認(rèn)模式，但生產(chǎn)環(huán)境中慎用，建議改成row。

row，行模式

表中行數(shù)據(jù)的變化過程。 記錄數(shù)據(jù)詳細(xì)，對(duì)IO性能要求比較高 記錄數(shù)據(jù)在任何情況下都是準(zhǔn)確的。 生產(chǎn)中一般是這種模式。 5.7以后默認(rèn)的模式。

mixed，混合模式

經(jīng)過判斷，選擇row+statement混合的一種記錄模式。（一般不用）

1.7.2 開啟二進(jìn)制日志

mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | log_bin                         | OFF   | | log_bin_basename                |       | | log_bin_index                   |       | | log_bin_trust_function_creators | OFF   | | log_bin_use_v1_row_events       | OFF   | | sql_log_bin                     | ON    | +---------------------------------+-------+ 6 rows in set (0.00 sec)

修改配置文件開啟二進(jìn)制日志

[root@db02 tmp]# vim /etc/my.cnf [mysqld] log-bin=/application/mysql/data/mysql-bin

命令行修改的方法

mysql> SET GLOBAL binlog_format = 'STATEMENT' mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED';

查看文件二進(jìn)制日志的類型

[root@db02 data]# file mysql-bin.* mysql-bin.000001: MySQL replication log mysql-bin.index:  ASCII text

查看MySQL的配置：

mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------------------+ | Variable_name                   | Value                                   | +---------------------------------+-----------------------------------------+ | log_bin                         | ON                                      | | log_bin_basename                | /application/mysql/data/mysql-bin       | | log_bin_index                   | /application/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF                                     | | log_bin_use_v1_row_events       | OFF                                     | | sql_log_bin                     | ON                                      | +---------------------------------+-----------------------------------------+ 6 rows in set (0.00 sec)

1.7.3 定義記錄方式

查看現(xiàn)在的格式

mysql> show variables like '%format%'; +--------------------------+-------------------+ | Variable_name            | Value             | +--------------------------+-------------------+ | binlog_format            | STATEMENT         | | date_format              | %Y-%m-%d          | | datetime_format          | %Y-%m-%d %H:%i:%s | | default_week_format      | 0                 | | innodb_file_format       | Antelope          | | innodb_file_format_check | ON                | | innodb_file_format_max   | Antelope          | | time_format              | %H:%i:%s          | +--------------------------+-------------------+ 8 rows in set (0.00 sec)

修改格式

[root@db02 data]# vim /etc/my.cnf [mysqld] binlog_format=row

改完之后查看

mysql> show variables like '%format%'; +--------------------------+-------------------+ | Variable_name            | Value             | +--------------------------+-------------------+ | binlog_format            | ROW               | | date_format              | %Y-%m-%d          | | datetime_format          | %Y-%m-%d %H:%i:%s | | default_week_format      | 0                 | | innodb_file_format       | Antelope          | | innodb_file_format_check | ON                | | innodb_file_format_max   | Antelope          | | time_format              | %H:%i:%s          | +--------------------------+-------------------+ 8 rows in set (0.00 sec)

1.8 二進(jìn)制日志的操作

1.8.1 查看

  操作系統(tǒng)層面查看

[root@db02 data]# ll mysql-bin.* -rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 120 Dec 20 20:17 mysql-bin.000002 -rw-rw---- 1 mysql mysql  82 Dec 20 20:17 mysql-bin.index

刷新日志

mysql> flush logs;

刷新完成后的日志目錄

[root@db02 data]# ll mysql-bin.* -rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001 -rw-rw---- 1 mysql mysql 167 Dec 20 20:24 mysql-bin.000002 -rw-rw---- 1 mysql mysql 120 Dec 20 20:24 mysql-bin.000003 -rw-rw---- 1 mysql mysql 123 Dec 20 20:24 mysql-bin.index [root@db02 data]#

查看當(dāng)前使用的二進(jìn)制日志文件

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 |      120 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

查看所有的二進(jìn)制日志文件

mysql> show binary logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001 |       143 | | mysql-bin.000002 |       167 | | mysql-bin.000003 |       120 | +------------------+-----------+ 3 rows in set (0.00 sec)

1.8.2 查看二進(jìn)制日志內(nèi)容

名詞說明：

　　1、events 事件

      　　　二進(jìn)制日志如何定義：命令的最小發(fā)生單元

　　2、position

  　　　　 每個(gè)事件在整個(gè)二進(jìn)制文件中想對(duì)應(yīng)的位置號(hào)就是position號(hào)

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 |      120 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) [root@db02 data]# mysqlbinlog  mysql-bin.000003 >/tmp/aa.ttt

導(dǎo)出所有的信息

[root@db02 data]# mysqlbinlog  mysql-bin.000003 >/tmp/aa.ttt

binlog 的查看方式：

1、查看binlog原始信息

mysqbin   mysql-bin.000002

2、在row模式下，翻譯成語句

mysqlbinlog --base64-output='decode-rows' -v   mysql-bin.000002

3、查看binlog事件

show binary logs; 所有在使用的binlog信息 show binlog events in '日志文件'

4 、如何截取binlog 內(nèi)容，按需求恢復(fù)（常規(guī)思路）

　?。?）、show binary logs;   show master status;

　?。?）、show binlog events in '' 從后往前看，找到誤操作的事務(wù)，判斷事務(wù)開始position和結(jié)束position

　?。?）、把誤操作的剔除掉，留下正常操作到2個(gè)sql文件中

　?。?）、先測(cè)試庫恢復(fù)，把誤操作的數(shù)據(jù)導(dǎo)出，然后生產(chǎn)恢復(fù)。

使用上述方法遇到的問題：

 　　 恢復(fù)事件較長

  　　　　對(duì)生產(chǎn)數(shù)據(jù)有一定的影響，有可能會(huì)出現(xiàn)冗余數(shù)據(jù)

　 較好的解決方案。

　　　　1、flashback閃回功能

　　　　2、通過備份，延時(shí)從庫

1.8.3 mysqlbinlog截取二進(jìn)制日志的方法

mysqlbinlog常見的選項(xiàng)有以下幾個(gè)：

  二進(jìn)制日志文件示例：  mysqlbinlog -- start-position=120 --stop-position=結(jié)束號(hào)  

1.8.4 刪除二進(jìn)制日志

　　 默認(rèn)情況下，不會(huì)刪除舊的日志文件。

根據(jù)存在時(shí)間刪除日志：

SET GLOBAL expire_logs_days = 7; 或 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

根據(jù)文件名刪除日志：

PURGE BINARY LOGS TO 'mysql-bin.000010';

   重置二進(jìn)制日志計(jì)數(shù)，從1開始計(jì)數(shù)，刪除原有的二進(jìn)制日志。

reset master

1.9 mysql的慢查詢?nèi)罩荆╯low log）

1.9.1 這是什么呢？

　　slow-log  記錄所有條件內(nèi)的慢的sql語句

  　 優(yōu)化的一種工具日志。能夠幫我們定位問題。

1.9.2 慢查詢?nèi)罩?

　　是將mysql服務(wù)器中影響數(shù)據(jù)庫性能的相關(guān)SQL語句記錄到日志文件

　　通過對(duì)這些特殊的SQL語句分析，改進(jìn)以達(dá)到提高數(shù)據(jù)庫性能的目的。慢日志設(shè)置

long_query_time ： 設(shè)定慢查詢的閥值，超出次設(shè)定值的SQL即被記錄到慢查詢?nèi)罩?，缺省值?0s slow_query_log ： 指定是否開啟慢查詢?nèi)罩?nbsp;slow_query_log_file ： 指定慢日志文件存放位置，可以為空，系統(tǒng)會(huì)給一個(gè)缺省的文件host_name-slow.log min_examined_row_limit：查詢檢查返回少于該參數(shù)指定行的SQL不被記錄到慢查詢?nèi)罩?nbsp;log_queries_not_using_indexes: 不使用索引的慢查詢?nèi)罩臼欠裼涗浀剿饕?

慢查詢?nèi)罩九渲?

[root@db02 htdocs]# vim /etc/my.cnf slow_query_log=ON slow_query_log_file=/tmp/slow.log long_query_time=0.5    # 控制慢日志記錄的閾值 log_queries_not_using_indexes

      配置完成后重啟服務(wù)...

查看慢查詢?nèi)罩臼欠耖_啟，及其位置。

mysql> show variables like '%slow%'     -> ; +---------------------------+---------------+ | Variable_name             | Value         | +---------------------------+---------------+ | log_slow_admin_statements | OFF           | | log_slow_slave_statements | OFF           | | slow_launch_time          | 2             | | slow_query_log            | ON            | | slow_query_log_file       | /tmp/slow.log | +---------------------------+---------------+ 5 rows in set (0.00 sec)

1.9.3 mysqldumpslow命令

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

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

1.9.4 怎么保證binlog和redolog已提交事務(wù)的一致性

　　在沒有開啟binlog的時(shí)候，在執(zhí)行commit，認(rèn)為redo日志持久化到磁盤文件中，commit命令就成功。

寫binlog參數(shù)：

mysql> show variables like '%sync_binlog%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog   | 0     |    #控制binlog commit 階段 +---------------+-------+ 1 row in set (0.00 sec)

   sync_binlog 確保是否每個(gè)提交的事務(wù)都寫到binlog中。

1.9.5 mysql中的雙一標(biāo)準(zhǔn)：

　　innodb_flush_log_at_trx_commit和sync_binlog 兩個(gè)參數(shù)是控制MySQL 磁盤寫入策略以及數(shù)據(jù)安全性的關(guān)鍵參數(shù)。

參數(shù)意義說明：

innodb_flush_log_at_trx_commit=1

　　如果innodb_flush_log_at_trx_commit設(shè)置為0，log buffer將每秒一次地寫入log file中，并且log file的flush(刷到磁盤)操作同時(shí)進(jìn)行.該模式下，在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫入磁盤的操作。

　　如果innodb_flush_log_at_trx_commit設(shè)置為1，每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去.

　　如果innodb_flush_log_at_trx_commit設(shè)置為2，每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)操作并不會(huì)同時(shí)進(jìn)行。該模式下,MySQL會(huì)每秒執(zhí)行一次 flush(刷到磁盤)操作。

注意：

由于進(jìn)程調(diào)度策略問題,這個(gè)“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒”。

參數(shù)意義說明：

 　　sync_binlog = 1  

　　sync_binlog 的默認(rèn)值是0，像操作系統(tǒng)刷其他文件的機(jī)制一樣，MySQL不會(huì)同步到磁盤中去而是依賴操作系統(tǒng)來刷新binary log。

　　當(dāng)sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進(jìn)制日志binary log時(shí)，會(huì)使用fdatasync()函數(shù)將它的寫二進(jìn)制日志binary log同步到磁盤中去。

注:

　　如果啟用了autocommit，那么每一個(gè)語句statement就會(huì)有一次寫操作；否則每個(gè)事務(wù)對(duì)應(yīng)一個(gè)寫操作。

安全方面說明

　　當(dāng)innodb_flush_log_at_trx_commit和sync_binlog 都為 1 時(shí)是最安全的，在mysqld 服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log 只有可能丟失最多一個(gè)語句或者一個(gè)事務(wù)。但是魚與熊掌不可兼得，雙11 會(huì)導(dǎo)致頻繁的io操作，因此該模式也是最慢的一種方式。

　　當(dāng)innodb_flush_log_at_trx_commit設(shè)置為0，mysqld進(jìn)程的崩潰會(huì)導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失。

　　當(dāng)innodb_flush_log_at_trx_commit設(shè)置為2，只有在操作系統(tǒng)崩潰或者系統(tǒng)掉電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。

　　雙1適合數(shù)據(jù)安全性要求非常高，而且磁盤IO寫能力足夠支持業(yè)務(wù)，比如訂單,交易,充值,支付消費(fèi)系統(tǒng)。雙1模式下，當(dāng)磁盤IO無法滿足業(yè)務(wù)需求時(shí) 比如11.11 活動(dòng)的壓力。推薦的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N為500 或1000) 且使用帶蓄電池后備電源的緩存cache，防止系統(tǒng)斷電異常。

　　系統(tǒng)性能和數(shù)據(jù)安全是業(yè)務(wù)系統(tǒng)高可用穩(wěn)定的必要因素。我們對(duì)系統(tǒng)的優(yōu)化需要尋找一個(gè)平衡點(diǎn)，合適的才是最好的，根據(jù)不同的業(yè)務(wù)場(chǎng)景需求，可以將兩個(gè)參數(shù)做組合調(diào)整，以便是db系統(tǒng)的性能達(dá)到最優(yōu)化。

1.10 參考文獻(xiàn)

https://www.cnblogs.com/wangdake-qq/p/7358322.html http://www.jb51.net/article/87653.htm http://www.mysqlops.com/2012/04/06/innodb-log1.html https://www.cnblogs.com/Bozh/archive/2013/03/18/2966494.html https://www.cnblogs.com/andy6/p/6626848.html https://www.cnblogs.com/xuanzhi201111/p/4128894.html  Anemometer實(shí)現(xiàn)pt-query-digest 圖形化 http://www.coooz.com/archives/771         雙一標(biāo)準(zhǔn)