|
功能
|
支持
|
功能
|
支持
|
|
存儲限制
|
64 TB
|
索引高速緩存
|
是
|
|
MVCC
|
是
|
數(shù)據(jù)高速緩存
|
是
|
|
B 樹索引
|
是
|
自適應(yīng)散列索引
|
是
|
|
群集索引
|
是
|
復(fù)制
|
是
|
|
壓縮數(shù)據(jù)
|
是
|
更新數(shù)據(jù)字典
|
是
|
|
加密數(shù)據(jù)[b]
|
是
|
地理空間數(shù)據(jù)類型
|
是
|
|
查詢高速緩存
|
是
|
地理空間索引
|
否
|
|
事務(wù)
|
是
|
全文搜索索引
|
是
|
|
鎖定粒度
|
行
|
群集數(shù)據(jù)庫
|
否
|
|
外鍵
|
是
|
備份和恢復(fù)
|
是
|
|
文件格式管理
|
是
|
快速索引創(chuàng)建
|
是
|
|
多個緩沖區(qū)池
|
是
|
PERFORMANCE_SCHEMA
|
是
|
|
更改緩沖
|
是
|
自動故障恢復(fù)
|
是
|
1.2.3 查詢存儲引擎的方法
SELECT @@default_storage_engine; 或 show variables like '%engine%';
2、使用 SHOW 確認(rèn)每個表的存儲引擎：
SHOW CREATE TABLE City\G SHOW TABLE STATUS LIKE 'CountryLanguage'\G
3、使用 INFORMATION_SCHEMA 確認(rèn)每個表的存儲引擎：
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備份后，一點要替換備份的文件中的engine(引擎)字段，從myisam替換為innodb（可以使用sed命令），否則遷移無任何意義。
數(shù)據(jù)庫升級時，要注意其他配套設(shè)施的兼容性，注意代碼能否兼容新特性。
1.2.4 設(shè)置存儲引擎
1、在啟動配置文件中設(shè)置服務(wù)器存儲引擎：
[mysqld] default-storage-engine=Storage Engine>
2、使用 SET 命令為當(dāng)前客戶機(jī)會話設(shè)置：
SET @@storage_engine=Storage Engine>;
3、在 CREATE TABLE 語句指定：
CREATE TABLE t (i INT) ENGINE = Storage Engine>;
1.3 InnoDB存儲引擎的存儲結(jié)構(gòu)
1.3.1 InnoDB 系統(tǒng)表空間特性
默認(rèn)情況下，InnoDB 元數(shù)據(jù)、撤消日志和緩沖區(qū)存儲在系統(tǒng)“表空間”中。
這是單個邏輯存儲區(qū)域，可以包含一個或多個文件。
每個文件可以是常規(guī)文件或原始分區(qū)。
最后的文件可以自動擴(kuò)展。
1.3.2 表空間的定義
表空間：MySQL數(shù)據(jù)庫存儲的方式
表空間中包含數(shù)據(jù)文件
MySQl表空間和數(shù)據(jù)文件是1:1的關(guān)系
共享表空間除外，是可以1:N關(guān)系
1.3.3 表空間類型
1、共享表空間：ibdata1~ibdataN,一般是2-3個
2、獨立表空間：存放在指定庫目錄下，例如data/world/目錄下的city.ibd
表空間位置（datadir）：
data/目錄下
1.3.4 系統(tǒng)表空間的存儲內(nèi)容
共享表空間（物理存儲結(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)實在ibdata中的，在5.6以后是可以單獨定義的。
tmp 表空間在5.7版本之后被移出了ibdata1，變?yōu)閕btmp1
在5.5版本之前，所有的應(yīng)用數(shù)據(jù)也都默認(rèn)存放到了ibdata中。
獨立表空間(一個存儲引擎的功能)
在5.6之后，默認(rèn)的情況下會單表單獨存儲到獨立表空間文件
除了系統(tǒng)表空間之外，InnoDB 還在數(shù)據(jù)庫目錄中創(chuàng)建另外的表空間，用于每個 InnoDB 表的 .ibd 文件。
InnoDB 創(chuàng)建的每個新表在數(shù)據(jù)庫目錄中設(shè)置一個 .ibd 文件來搭配表的.frm 文件。
可以使用 innodb_file_per_table 選項控制此設(shè)置，更改該設(shè)置僅會更改已創(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 | +-----------------------+------------------------+ row in set (0.00 sec)
設(shè)置共享表空間：
一般是在初始搭建環(huán)境的時候就配置號，預(yù)設(shè)值一般為1G；且最后一個為自動擴(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 | +-----------------------+-------------------------------------+ row in set (0.00 sec)
1.3.6 設(shè)置獨立表空間
獨立表空間在5.6版本是默認(rèn)開啟的。
獨立表空間注意事項：不開起獨立表空間，共享表空間會占用很大
mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ row in set (0.00 sec)
在參數(shù)文件/etc/my.cnf 可以控制獨立表空間
關(guān)閉獨立表空間 （0是關(guān)閉，1是開啟）
[root@db02 clsn]# vim /etc/my.cnf [mysqld] innodb_file_per_table=0
查看獨立表空間配置
mysql> show variables like '%per_table%' ; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ row in set (0.00 sec)
小結(jié):
innodb_file_per_table=0 關(guān)閉獨立表空間 innodb_file_per_table=1 開啟獨立表空間，單表單存儲
1.4 MySQL中的事務(wù)
一組數(shù)據(jù)操作執(zhí)行步驟，這些步驟被視為一個工作單元
用于對多個語句進(jìn)行分組，可以在多個客戶機(jī)并發(fā)訪問同一個表中的數(shù)據(jù)時使用。
所有步驟都成功或都失敗
如果所有步驟正常，則執(zhí)行，如果步驟出現(xiàn)錯誤或不完整，則取消。
簡單來說事務(wù)就是：保證工作單元中的語句同時成功或同時失敗。
事務(wù)處理流程示意圖
1.4.1 事務(wù)是什么
與其給事務(wù)定義，不如說一說事務(wù)的特性。眾所周知，事務(wù)需要滿足ACID四個特性。
A(atomicity) 原子性。
一個事務(wù)的執(zhí)行被視為一個不可分割的最小單元。事務(wù)里面的操作，要么全部成功執(zhí)行，要么全部失敗回滾，不可以只執(zhí)行其中的一部分。
所有語句作為一個單元全部成功執(zhí)行或全部取消。 updata t1 set money=10000-17 where id=wxid1 updata t1 set money=10000+17 where id=wxid2
C(consistency) 一致性。
一個事務(wù)的執(zhí)行不應(yīng)該破壞數(shù)據(jù)庫的完整性約束。如果上述例子中第2個操作執(zhí)行后系統(tǒng)崩潰，保證A和B的金錢總計是不會變的。
如果數(shù)據(jù)庫在事務(wù)開始時處于一致狀態(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)該互相影響。然而實際情況中，事務(wù)相互影響的程度受到隔離級別的影響。文章后面會詳述。
事務(wù)之間不相互影響。在做操作的時候，其他人對這兩個賬戶做任何操作，在不同的隔離條件下，可能一致性保證又不一樣
隔離級別
隔離級別會影響到一致性。 read-uncommit X read-commit 可能會用的一種級別 repeatable-read 默認(rèn)的級別，和oracle一樣的 SERIALIZABLE 嚴(yán)格的默認(rèn)，一般不會用
此規(guī)則除了受隔離級別控制，還受鎖控制，可以聯(lián)想一下NFS的實現(xiàn)
D(durability) 持久性。
事務(wù)提交之后，需要將提交的事務(wù)持久化到磁盤。即使系統(tǒng)崩潰，提交的數(shù)據(jù)也不應(yīng)該丟失。
保證數(shù)據(jù)落地，才算事務(wù)真正安全
1.4.2 事務(wù)的控制語句
常用的事務(wù)控制語句：
START TRANSACTION（或 BEGIN）：顯式開始一個新事務(wù) COMMIT：永久記錄當(dāng)前事務(wù)所做的更改(事務(wù)成功結(jié)束) ROLLBACK：取消當(dāng)前事務(wù)所做的更改(事務(wù)失敗結(jié)束)
需要知道的事務(wù)控制語句：
SAVEPOINT：分配事務(wù)過程中的一個位置，以供將來引用 ROLLBACK TO SAVEPOINT：取消在 savepoint 之后執(zhí)行的更改 RELEASE SAVEPOINT：刪除 savepoint 標(biāo)識符 SET AUTOCOMMIT：為當(dāng)前連接禁用或啟用默認(rèn) autocommit模式
1.4.3 autocommit參數(shù)
在MySQL5.5開始，開啟事務(wù)時不再需要begin或者start transaction語句。并且，默認(rèn)是開啟了Autocommit模式，作為一個事務(wù)隱式提交每個語句。
在有些業(yè)務(wù)繁忙企業(yè)場景下，這種配置可能會對性能產(chǎn)生很大影響，但對于安全性上有很大提高。將來，我們需要去權(quán)衡我們的業(yè)務(wù)需求去調(diào)整是否自動提交。
注意：在生產(chǎn)中，根據(jù)實際需求選擇是否可開啟，一般銀行類業(yè)務(wù)會選擇關(guān)閉。
查看當(dāng)前autocommit狀態(tài)：
mysql> show variables like '%autoc%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 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 | +---------------+-------+ row in set (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ row in set (0.00 sec)
說明： autocommit設(shè)置為開啟的對比
優(yōu)點：數(shù)據(jù)安全性好，每次修改都會落地
缺點：不能進(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的原理很簡單，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個地方（這個存儲數(shù)據(jù)備份的地方稱為Undo Log）。然后進(jìn)行數(shù)據(jù)的修改。
如果出現(xiàn)了錯誤或者用戶執(zhí)行了ROLLBACK語句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)。
除了可以保證事務(wù)的原子性，Undo Log也可以用來輔助完成事務(wù)的持久化。
undo是什么？
undo,顧名思義“回滾日志”，是事務(wù)日志的一種。
作用是什么？
在事務(wù)ACID過程中，實現(xiàn)的是“A“原子性的作用。
用Undo Log實現(xiàn)原子性和持久化的事務(wù)的簡化過程
假設(shè)有A、B兩個數(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ù)提交
這里有一個隱含的前提條件：‘?dāng)?shù)據(jù)都是先讀到內(nèi)存中，然后修改內(nèi)存中的數(shù)據(jù)，最后將數(shù)據(jù)寫回磁盤之所以能同時保證原子性和持久化，是因為以下特點：
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)崩潰,因為數(shù)據(jù)沒有持久化到磁盤。所以磁盤上的數(shù)據(jù)還是保持在事務(wù)開始前的狀態(tài)。
缺陷：
每個事務(wù)提交前將數(shù)據(jù)和Undo Log寫入磁盤，這樣會導(dǎo)致大量的磁盤IO，因此性能很低。如果能夠?qū)?shù)據(jù)緩存一段時間，就能減少IO提高性能。但是這樣就會喪失事務(wù)的持久性。
因此引入了另外一種機(jī)制來實現(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ù)據(jù)沒有持久化，但是Redo Log已經(jīng)持久化。
系統(tǒng)可以根據(jù)Redo Log的內(nèi)容，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)。
Redo是什么？
redo,顧名思義“重做日志”，是事務(wù)日志的一種。
作用是什么？
在事務(wù)ACID過程中，實現(xiàn)的是“D”持久化的作用。
Undo + Redo事務(wù)的簡化過程
假設(shè)有A、B兩個數(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ù)的特點
A. 為了保證持久性，必須在事務(wù)提交前將Redo Log持久化。 B. 數(shù)據(jù)不需要在事務(wù)提交前寫入磁盤，而是緩存在內(nèi)存中。 C. Redo Log 保證事務(wù)的持久性。 D. Undo Log 保證事務(wù)的原子性。 E. 有一個隱含的特點，數(shù)據(jù)必須要晚于redo log寫入持久存儲。
redo是否持久化到磁盤參數(shù)
innodb_flush_log_at_trx_commit=1/0/2
1.5.3 事務(wù)中的鎖
什么是“鎖”？
“鎖”顧名思義就是鎖定的意思。
“鎖”的作用是什么？
在事務(wù)ACID過程中，“鎖”和“隔離級別”一起來實現(xiàn)“I”隔離性的作用。
鎖的粒度：
1、MyIasm：低并發(fā)鎖——表級鎖
2、Innodb：高并發(fā)鎖——行級鎖
四種隔離級別：
READ UNCOMMITTED 許事務(wù)查看其他事務(wù)所進(jìn)行的未提交更改 READ COMMITTED 允許事務(wù)查看其他事務(wù)所進(jìn)行的已提交更改 REPEATABLE READ****** 確保每個事務(wù)的 SELECT 輸出一致； InnoDB 的默認(rèn)級別 SERIALIZABLE 將一個事務(wù)的結(jié)果與其他事務(wù)完全隔離
開銷、加鎖速度、死鎖、粒度、并發(fā)性能
表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
從上述特點可見，很難籠統(tǒng)地說哪種鎖更好，只能就具體應(yīng)用的特點來說哪種鎖更合適！
僅從鎖的角度來說：表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；而行級鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。
1.6 MySQL 日志管理
1.6.1 MySQL日志類型簡介
日志的類型的說明：
|
日志文件
|
選項
|
文件名
|
程序
N/A
|
|
表名稱
|
|||
|
錯誤
|
--log-error
|
host_name.err
|
N/A
|
|
常規(guī)
|
--general_log
|
host_name.log
|
mysqldumpslow
mysqlbinlog
|
|
general_log
|
|||
|
慢速查詢
|
--slow_query_log
--long_query_time
|
host_name-slow.log
|
N/A
程序
|
|
slow_log
|
|||
|
二進(jìn)制
|
--log-bin
--expire-logs-days
|
host_name-bin.000001
|
N/A
|
|
審計
|
--audit_log
--audit_log_file
|
audit.log
|
N/A
|
1.6.2 配置方法
狀態(tài)錯誤日志：
[mysqld] log-error=/data/mysql/mysql.log
查看配置方式：
mysql> show variables like '%log%error%';
作用：
記錄mysql數(shù)據(jù)庫的一般狀態(tài)信息及報錯信息，是我們對于數(shù)
據(jù)庫常規(guī)報錯處理的常用日志。
mysql> show variables like '%log%err%'; +---------------------+----------------------------------+ | Variable_name | Value | +---------------------+----------------------------------+ | binlog_error_action | IGNORE_ERROR | | log_error | /application/mysql/data/db02.err | +---------------------+----------------------------------+ rows in set (0.00 sec)
1.6.3 一般查詢?nèi)罩?/strong>
配置方法:
[mysqld] general_log=on general_log_file=/data/mysql/server2.log
查看配置方式：
show variables like '%gen%';
作用：
記錄mysql所有執(zhí)行成功的SQL語句信息，可以做審計用，但是我們很少開啟
mysql> show variables like '%gen%'; +------------------+----------------------------------+ | Variable_name | Value | +------------------+----------------------------------+ | general_log | OFF | | general_log_file | /application/mysql/data/db02.log | +------------------+----------------------------------+ rows in set (0.00 sec)
1.7 二進(jìn)制日志
二進(jìn)制日志不依賴與存儲引擎的。
依賴于sql層，記錄和sql語句相關(guān)的信息
binlog日志作用：
1、提供備份功能
2、進(jìn)行主從復(fù)制
3、基于時間點的任意恢復(fù)
記錄在sql層已經(jīng)執(zhí)行完成的語句，如果是事務(wù)，則記錄已完成的事務(wù)。
功能作用： 時間點備份 和 時間點恢復(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)。 我們在有些時候會臨時關(guān)閉掉。 只影響當(dāng)前會話。 sql_log_bin=1/0
1.7.1 二進(jìn)制日志的格式
statement，語句模式：
記錄信息簡潔，記錄的是SQL語句本身。但是在語句中出現(xiàn)函數(shù)操作的話，有可能記錄的數(shù)據(jù)不準(zhǔn)確。 5.6中默認(rèn)模式，但生產(chǎn)環(huán)境中慎用，建議改成row。
row，行模式
表中行數(shù)據(jù)的變化過程。 記錄數(shù)據(jù)詳細(xì)，對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 | +---------------------------------+-------+ 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 | +---------------------------------+-----------------------------------------+ 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 | +--------------------------+-------------------+ 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 | +--------------------------+-------------------+ 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 | | | | +------------------+----------+--------------+------------------+-------------------+ 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 | +------------------+-----------+ rows in set (0.00 sec)
1.8.2 查看二進(jìn)制日志內(nèi)容
名詞說明：
1、events 事件
二進(jìn)制日志如何定義：命令的最小發(fā)生單元
2、position
每個事件在整個二進(jìn)制文件中想對應(yīng)的位置號就是position號
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 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ī)思路）
（1）、show binary logs; show master status;
（2）、show binlog events in '' 從后往前看，找到誤操作的事務(wù)，判斷事務(wù)開始position和結(jié)束position
（3）、把誤操作的剔除掉，留下正常操作到2個sql文件中
（4）、先測試庫恢復(fù)，把誤操作的數(shù)據(jù)導(dǎo)出，然后生產(chǎn)恢復(fù)。
使用上述方法遇到的問題：
恢復(fù)事件較長
對生產(chǎn)數(shù)據(jù)有一定的影響，有可能會出現(xiàn)冗余數(shù)據(jù)
較好的解決方案。
1、flashback閃回功能
2、通過備份，延時從庫
1.8.3 mysqlbinlog截取二進(jìn)制日志的方法
mysqlbinlog常見的選項有以下幾個：
|
參數(shù)
|
參數(shù)說明
|
|
--start-datetime
|
從二進(jìn)制日志中讀取指定等于時間戳或者晚于本地計算機(jī)的時間
|
|
--stop-datetime
|
從二進(jìn)制日志中讀取指定小于時間戳或者等于本地計算機(jī)的時間取值和上述一樣
|
|
--start-position
|
從二進(jìn)制日志中讀取指定position 事件位置作為開始。
|
|
--stop-position
|
從二進(jìn)制日志中讀取指定position 事件位置作為事件截至
|
1.8.4 刪除二進(jìn)制日志
SET GLOBAL expire_logs_days = 7; 或 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根據(jù)文件名刪除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
重置二進(jìn)制日志計數(shù)，從1開始計數(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)罩?/strong>
是將mysql服務(wù)器中影響數(shù)據(jù)庫性能的相關(guān)SQL語句記錄到日志文件
通過對這些特殊的SQL語句分析，改進(jìn)以達(dá)到提高數(shù)據(jù)庫性能的目的。慢日志設(shè)置
long_query_time ： 設(shè)定慢查詢的閥值，超出次設(shè)定值的SQL即被記錄到慢查詢?nèi)罩荆笔≈禐?0s slow_query_log ： 指定是否開啟慢查詢?nèi)罩? slow_query_log_file ： 指定慢日志文件存放位置，可以為空，系統(tǒng)會給一個缺省的文件host_name-slow.log min_examined_row_limit：查詢檢查返回少于該參數(shù)指定行的SQL不被記錄到慢查詢?nèi)罩? log_queries_not_using_indexes: 不使用索引的慢查詢?nèi)罩臼欠裼涗浀剿饕?/pre>
慢查詢?nèi)罩九渲?/p>
[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 | +---------------------------+---------------+ rows in set (0.00 sec)
1.9.3 mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log
這會輸出記錄次數(shù)最多的10條SQL語句，其中：
|
參數(shù)
|
說明
|
|
-s
|
是表示按照何種方式排序，c、t、l、r分別是按照記錄次數(shù)、時間、查詢
時間、返回的記錄數(shù)來排序，ac、at、al、ar，表示相應(yīng)的倒敘；
|
|
-t
|
是top n的意思，即為返回前面多少條的數(shù)據(jù)；
|
|
-g
|
后邊可以寫一個正則匹配模式，大小寫不敏感的；
|
|
例子：
/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條里面含有左連接的查詢語句。
|
|
1.9.4 怎么保證binlog和redolog已提交事務(wù)的一致性
mysql> show variables like '%sync_binlog%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | #控制binlog commit 階段 +---------------+-------+ row in set (0.00 sec)
sync_binlog 確保是否每個提交的事務(wù)都寫到binlog中。
1.9.5 mysql中的雙一標(biāo)準(zhǔn)：
innodb_flush_log_at_trx_commit和sync_binlog 兩個參數(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(刷到磁盤)操作同時進(jìn)行.該模式下，在事務(wù)提交的時候，不會主動觸發(fā)寫入磁盤的操作。
如果innodb_flush_log_at_trx_commit設(shè)置為1，每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去.
如果innodb_flush_log_at_trx_commit設(shè)置為2，每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)操作并不會同時進(jìn)行。該模式下,MySQL會每秒執(zhí)行一次 flush(刷到磁盤)操作。
注意：
由于進(jìn)程調(diào)度策略問題,這個“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒”。
參數(shù)意義說明：
sync_binlog=1
sync_binlog 的默認(rèn)值是0，像操作系統(tǒng)刷其他文件的機(jī)制一樣，MySQL不會同步到磁盤中去而是依賴操作系統(tǒng)來刷新binary log。
當(dāng)sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進(jìn)制日志binary log時，會使用fdatasync()函數(shù)將它的寫二進(jìn)制日志binary log同步到磁盤中去。
注:
如果啟用了autocommit，那么每一個語句statement就會有一次寫操作；否則每個事務(wù)對應(yīng)一個寫操作。
安全方面說明
當(dāng)innodb_flush_log_at_trx_commit和sync_binlog 都為 1 時是最安全的，在mysqld 服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務(wù)。但是魚與熊掌不可兼得，雙11 會導(dǎo)致頻繁的io操作，因此該模式也是最慢的一種方式。
當(dāng)innodb_flush_log_at_trx_commit設(shè)置為0，mysqld進(jìn)程的崩潰會導(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ù)需求時 比如11.11 活動的壓力。推薦的做法是 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)定的必要因素。我們對系統(tǒng)的優(yōu)化需要尋找一個平衡點，合適的才是最好的，根據(jù)不同的業(yè)務(wù)場景需求，可以將兩個參數(shù)做組合調(diào)整，以便是db系統(tǒng)的性能達(dá)到最優(yōu)化。
以上這篇基于MySQL的存儲引擎與日志說明(全面講解)就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。
標(biāo)簽：安徽 山南 安康 四川 浙江 平頂山 那曲 濮陽
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《基于MySQL的存儲引擎與日志說明(全面講解)》，本文關(guān)鍵詞 基于,MySQL,的,存儲,引擎,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
