|
功能
|
支持
|
功能
|
支持
|
|
存儲限制
|
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 確認每個表的存儲引擎：
SHOW CREATE TABLE City\G SHOW TABLE STATUS LIKE 'CountryLanguage'\G
3、使用 INFORMATION_SCHEMA 確認每個表的存儲引擎：
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 命令為當前客戶機會話設(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)表空間特性
默認情況下，InnoDB 元數(shù)據(jù)、撤消日志和緩沖區(qū)存儲在系統(tǒng)“表空間”中。
這是單個邏輯存儲區(qū)域，可以包含一個或多個文件。
每個文件可以是常規(guī)文件或原始分區(qū)。
最后的文件可以自動擴展。
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日志默認實在ibdata中的，在5.6以后是可以單獨定義的。
tmp 表空間在5.7版本之后被移出了ibdata1，變?yōu)閕btmp1
在5.5版本之前，所有的應(yīng)用數(shù)據(jù)也都默認存放到了ibdata中。
獨立表空間(一個存儲引擎的功能)
在5.6之后，默認的情況下會單表單獨存儲到獨立表空間文件
除了系統(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)建的新表的默認值。。
1.3.5 設(shè)置共享表空間
查看當前的共享表空間設(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；且最后一個為自動擴展。
[root@db02 world]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend
重啟服務(wù)查看當前的共享表空間設(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版本是默認開啟的。
獨立表空間注意事項：不開起獨立表空間，共享表空間會占用很大
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í)行步驟，這些步驟被視為一個工作單元
用于對多個語句進行分組，可以在多個客戶機并發(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 默認的級別，和oracle一樣的 SERIALIZABLE 嚴格的默認，一般不會用
此規(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：永久記錄當前事務(wù)所做的更改(事務(wù)成功結(jié)束) ROLLBACK：取消當前事務(wù)所做的更改(事務(wù)失敗結(jié)束)
需要知道的事務(wù)控制語句：
SAVEPOINT：分配事務(wù)過程中的一個位置，以供將來引用 ROLLBACK TO SAVEPOINT：取消在 savepoint 之后執(zhí)行的更改 RELEASE SAVEPOINT：刪除 savepoint 標識符 SET AUTOCOMMIT：為當前連接禁用或啟用默認 autocommit模式
1.4.3 autocommit參數(shù)
在MySQL5.5開始，開啟事務(wù)時不再需要begin或者start transaction語句。并且，默認是開啟了Autocommit模式，作為一個事務(wù)隱式提交每個語句。
在有些業(yè)務(wù)繁忙企業(yè)場景下，這種配置可能會對性能產(chǎn)生很大影響，但對于安全性上有很大提高。將來，我們需要去權(quán)衡我們的業(yè)務(wù)需求去調(diào)整是否自動提交。
注意：在生產(chǎn)中，根據(jù)實際需求選擇是否可開啟，一般銀行類業(yè)務(wù)會選擇關(guān)閉。
查看當前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ù)安全性好，每次修改都會落地
缺點：不能進行銀行類的交易事務(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）。然后進行數(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ù)提交
這里有一個隱含的前提條件：‘數(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ù)的持久性。
因此引入了另外一種機制來實現(xiàn)持久化，即Redo Log.
1.5.2 事務(wù)日志redo
redo原理：
和Undo Log相反，Redo Log記錄的是新數(shù)據(jù)的備份。在事務(wù)提交前，只要將Redo Log持久化即可，不需要將數(shù)據(jù)持久化。當系統(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ù)所進行的未提交更改 READ COMMITTED 允許事務(wù)查看其他事務(wù)所進行的已提交更改 REPEATABLE READ****** 確保每個事務(wù)的 SELECT 輸出一致； InnoDB 的默認級別 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
|
|||
|
二進制
|
--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 二進制日志
二進制日志不依賴與存儲引擎的。
依賴于sql層，記錄和sql語句相關(guān)的信息
binlog日志作用：
1、提供備份功能
2、進行主從復(fù)制
3、基于時間點的任意恢復(fù)
記錄在sql層已經(jīng)執(zhí)行完成的語句，如果是事務(wù)，則記錄已完成的事務(wù)。
功能作用： 時間點備份 和 時間點恢復(fù)、 主從
二進制日志的“總閘”
作用：
1、是否開啟 2、二進制日志路徑/data/mysql/ 3、二進制日志文件名前綴mysql-bin 4、文件名以"前綴".000001~N log-bin=/data/mysql/mysql-bin
二進制日志的“分開關(guān)”：
只有總閘開啟才有意義，默認是開啟狀態(tài)。 我們在有些時候會臨時關(guān)閉掉。 只影響當前會話。 sql_log_bin=1/0
1.7.1 二進制日志的格式
statement，語句模式：
記錄信息簡潔，記錄的是SQL語句本身。但是在語句中出現(xiàn)函數(shù)操作的話，有可能記錄的數(shù)據(jù)不準確。 5.6中默認模式，但生產(chǎn)環(huán)境中慎用，建議改成row。
row，行模式
表中行數(shù)據(jù)的變化過程。 記錄數(shù)據(jù)詳細，對IO性能要求比較高 記錄數(shù)據(jù)在任何情況下都是準確的。 生產(chǎn)中一般是這種模式。 5.7以后默認的模式。
mixed，混合模式
經(jīng)過判斷，選擇row+statement混合的一種記錄模式。（一般不用）
1.7.2 開啟二進制日志
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)
修改配置文件開啟二進制日志
[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';
查看文件二進制日志的類型
[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 二進制日志的操作
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]#
查看當前使用的二進制日志文件
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)
查看所有的二進制日志文件
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 查看二進制日志內(nèi)容
名詞說明：
1、events 事件
二進制日志如何定義：命令的最小發(fā)生單元
2、position
每個事件在整個二進制文件中想對應(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截取二進制日志的方法
mysqlbinlog常見的選項有以下幾個：
|
參數(shù)
|
參數(shù)說明
|
|
--start-datetime
|
從二進制日志中讀取指定等于時間戳或者晚于本地計算機的時間
|
|
--stop-datetime
|
從二進制日志中讀取指定小于時間戳或者等于本地計算機的時間取值和上述一樣
|
|
--start-position
|
從二進制日志中讀取指定position 事件位置作為開始。
|
|
--stop-position
|
從二進制日志中讀取指定position 事件位置作為事件截至
|
1.8.4 刪除二進制日志
SET GLOBAL expire_logs_days = 7; 或 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根據(jù)文件名刪除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
重置二進制日志計數(shù)，從1開始計數(shù)，刪除原有的二進制日志。
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語句分析，改進以達到提高數(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中的雙一標準：
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(刷到磁盤)操作同時進行.該模式下，在事務(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(刷到磁盤)操作并不會同時進行。該模式下,MySQL會每秒執(zhí)行一次 flush(刷到磁盤)操作。
注意：
由于進程調(diào)度策略問題,這個“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒”。
參數(shù)意義說明：
sync_binlog=1
sync_binlog 的默認值是0，像操作系統(tǒng)刷其他文件的機制一樣，MySQL不會同步到磁盤中去而是依賴操作系統(tǒng)來刷新binary log。
當sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進制日志binary log時，會使用fdatasync()函數(shù)將它的寫二進制日志binary log同步到磁盤中去。
注:
如果啟用了autocommit，那么每一個語句statement就會有一次寫操作；否則每個事務(wù)對應(yīng)一個寫操作。
安全方面說明
當innodb_flush_log_at_trx_commit和sync_binlog 都為 1 時是最安全的，在mysqld 服務(wù)崩潰或者服務(wù)器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務(wù)。但是魚與熊掌不可兼得，雙11 會導(dǎo)致頻繁的io操作，因此該模式也是最慢的一種方式。
當innodb_flush_log_at_trx_commit設(shè)置為0，mysqld進程的崩潰會導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失。
當innodb_flush_log_at_trx_commit設(shè)置為2，只有在操作系統(tǒng)崩潰或者系統(tǒng)掉電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。
雙1適合數(shù)據(jù)安全性要求非常高，而且磁盤IO寫能力足夠支持業(yè)務(wù)，比如訂單,交易,充值,支付消費系統(tǒng)。雙1模式下，當磁盤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)的性能達到最優(yōu)化。
以上這篇基于MySQL的存儲引擎與日志說明(全面講解)就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。
| ? |
|
|
|
|||||||||||||||
| 全國咨詢熱線 | |||||||||||||||||
| 400-1100-266 | |||||||||||||||||
| 在線客服( QQ：340506921 ) | |||||||||||||||||
 |
|||||||||||||||||
