詳解 Mysql 事務(wù)和Mysql 日志

#查全局事務(wù)隔離級(jí)別
SELECT @@global.tx_isolation;
#查當(dāng)前會(huì)話事務(wù)隔離級(jí)別
SELECT @@session.tx_isolation; 
#查當(dāng)前事務(wù)隔離級(jí)別
SELECT @@tx_isolation;
#設(shè)置全局隔離級(jí)別
set global transaction isolation level read committed;
#設(shè)置當(dāng)前會(huì)話隔離級(jí)別
set session transaction isolation level read committed;

串行化是最高的隔離級(jí)別，它通過(guò)強(qiáng)制事務(wù)排序，使之不可能相互沖突，從而解決幻讀問(wèn)題。簡(jiǎn)言之,它是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖，在這個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)。

共享鎖(Share)：共享鎖的代號(hào)是S

mysql日志文件系統(tǒng)組成

1、MySQL日志文件系統(tǒng)的組成

   a、錯(cuò)誤日志：記錄啟動(dòng)、運(yùn)行或停止mysqld時(shí)出現(xiàn)的問(wèn)題。
   b、通用日志：記錄建立的客戶端連接和執(zhí)行的語(yǔ)句。
   c、更新日志：記錄更改數(shù)據(jù)的語(yǔ)句。該日志在MySQL 5.1中已不再使用。
   d、二進(jìn)制日志：記錄所有更改數(shù)據(jù)的語(yǔ)句。還用于復(fù)制。
   e、慢查詢?nèi)罩荆河涗浰袌?zhí)行時(shí)間超過(guò)long_query_time秒的所有查詢或不使用索引的查詢。
   f、Innodb日志：innodb redo log

二進(jìn)制日志（binlog）：

包含了所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)(比如沒有匹配任何行的一個(gè)DELETE)

包含關(guān)于每個(gè)更新數(shù)據(jù)庫(kù)(DML)的語(yǔ)句的執(zhí)行時(shí)間信息

不包含沒有修改任何數(shù)據(jù)的語(yǔ)句，如果需要啟用該選項(xiàng)，需要開啟通用日志功能

主要目的是盡可能的將數(shù)據(jù)庫(kù)恢復(fù)到數(shù)據(jù)庫(kù)故障點(diǎn)，因?yàn)槎M(jìn)制日志包含備份后進(jìn)行的所有更新

用于在主復(fù)制服務(wù)器上記錄所有將發(fā)送給從服務(wù)器的語(yǔ)句

啟用該選項(xiàng)數(shù)據(jù)庫(kù)性能降低1%，但保障數(shù)據(jù)庫(kù)完整性，對(duì)于重要數(shù)據(jù)庫(kù)值得以性能換完整。有些類似于oracle開啟歸檔模式。

show variables like '%version%'; 
show variables like '%log_bin%'; //是否啟用 binlog
show variables like '%binlog%'; //binlog 相關(guān)參數(shù)
show variables like '%datadir%';  //數(shù)據(jù)文件目錄，默認(rèn)日志存在該目錄

#編輯my.cnf來(lái)設(shè)定binary log日志位置(注，配置二進(jìn)制日志路徑及文件名后，系統(tǒng)變量log_bin被自動(dòng)置為on) 

log_bin=/var/lib/mysql/binarylog/binlog 

#如果在my.cnf里面只設(shè)置log_bin，但是不指定file_name，然后重啟數(shù)據(jù)庫(kù)。你會(huì)發(fā)現(xiàn)二進(jìn)制日志文件名稱為${hostname}-bin 這樣的格式
#切換日志
show master status;
flush logs;
show master status;

每次重啟MySQL服務(wù)也會(huì)生成一個(gè)新的二進(jìn)制日志文件，相當(dāng)于二進(jìn)制日志切換。切換二進(jìn)制日志時(shí)，你會(huì)看到這些number會(huì)不斷遞增。另外，除了這些二進(jìn)制日志文件外，你會(huì)看到還生成了一個(gè)DB-Server-bin.index的文件，這個(gè)文件中存儲(chǔ)所有二進(jìn)制日志文件的清單又稱為二進(jìn)制文件的索引

二進(jìn)制日志的刪除可以通過(guò)命令手工刪除，也可以設(shè)置自動(dòng)清理。

show binary logs;
mysql> purge binary logs to 'DB-Server-bin.000002';

purge binary logs to xxx; 表示刪除某個(gè)日志之前的所有二進(jìn)制日志文件。這個(gè)命令會(huì)修改index中相關(guān)數(shù)據(jù)
purge binary logs before '2017-03-10 10:10:00'; 清除某個(gè)時(shí)間點(diǎn)以前的二進(jìn)制日志文件。
purge master logs before date_sub( now( ), interval 7 day);清除7天前的二進(jìn)制日志文件
reset master;清除所有的二進(jìn)制日志文件（當(dāng)前不存在主從復(fù)制關(guān)系）

show variables like 'expire_logs_days';我們也可以設(shè)置expire_logs_days參數(shù)，設(shè)置自動(dòng)清理，其默認(rèn)值為0,表示不啟用過(guò)期自動(dòng)刪除功能，如果啟用了自動(dòng)清理功能，表示超出此天數(shù)的二進(jìn)制日志文件將被自動(dòng)刪除，自動(dòng)刪除工作通常發(fā)生在MySQL啟動(dòng)時(shí)或FLUSH日志時(shí)。
set expire_logs_days=7;

二進(jìn)制日志相關(guān)參數(shù)

1、系統(tǒng)變量log_bin_trust_function_creators，默認(rèn)為OFF，這個(gè)參數(shù)開啟會(huì)限制存儲(chǔ)過(guò)程、Function、觸發(fā)器的創(chuàng)建。

2：系統(tǒng)變量sql_log_bin 用于控制會(huì)話級(jí)別二進(jìn)制日志功能的開啟或關(guān)閉，默認(rèn)為ON，表示啟用二進(jìn)制日志功能。

3、系統(tǒng)變量binlog_cache_size 表示為每個(gè)客戶端分配binlog_cache_size大小的緩存，默認(rèn)值32768。二進(jìn)制日志緩存使用的前提條件是服務(wù)器端使用了支持事務(wù)的引擎以及開啟了bin log功能，它是MySQL用來(lái)提高binlog的效率而設(shè)計(jì)的一個(gè)用于短時(shí)間內(nèi)臨時(shí)緩存binlog數(shù)據(jù)的內(nèi)存區(qū)域。一般來(lái)說(shuō)，如果我們的數(shù)據(jù)庫(kù)中沒有什么大事務(wù)，寫入也不是特別頻繁，2MB～4MB是一個(gè)合適的選擇。但是如果我們的數(shù)據(jù)庫(kù)大事務(wù)較多或多事務(wù)語(yǔ)句，寫入量比較大，可適當(dāng)調(diào)高binlog_cache_size。同時(shí)，我們可以通過(guò)binlog_cache_use 以及 binlog_cache_disk_use來(lái)分析設(shè)置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內(nèi)存大小不夠而使用臨時(shí)文件（binlog_cache_disk_use）來(lái)緩存了。

可以通過(guò)查看Binlog_cache_disk_use 與 Binlog_cache_use來(lái)判斷binlog_cache_size是否需要調(diào)整。

4、系統(tǒng)變量max_binlog_cache_size 二進(jìn)制日志能夠使用的最大cache內(nèi)存大小。當(dāng)執(zhí)行多語(yǔ)句事務(wù)時(shí)，max_binlog_cache_size 如果不夠大，系統(tǒng)可能會(huì)報(bào)出“Multi-statement transaction required more than ‘max_binlog_cache_size' bytes of storage”的錯(cuò)誤。

5、 系統(tǒng)變量max_binlog_stmt_cache_size

max_binlog_cache_size針對(duì)事務(wù)語(yǔ)句，max_binlog_stmt_cache_size針對(duì)非事務(wù)語(yǔ)句，當(dāng)我們發(fā)現(xiàn)Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比較大時(shí)就需要考慮增大cache的大小

6、系統(tǒng)變量max_binlog_size， 表示二進(jìn)制日志的最大值，一般設(shè)置為512M或1GB，但不能超過(guò)1GB。該設(shè)置并不能嚴(yán)格控制二進(jìn)制日志的大小，尤其是二進(jìn)制日志比較靠近為不而又遇到一根比較大事務(wù)時(shí)， 為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有SQL都記錄進(jìn)當(dāng)前日志，直到事務(wù)結(jié)束。

7、系統(tǒng)變量binlog_checksum 用作復(fù)制的主從校檢。 NONE表示不生成checksum，CRC-32表示使用這個(gè)算法做校檢。

8、系統(tǒng)變量sync_binlog，這個(gè)參數(shù)對(duì)于Mysql系統(tǒng)來(lái)說(shuō)是至關(guān)重要的，它不僅影響到二進(jìn)制日志文件對(duì)MySQL所帶來(lái)的性能損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。

sync_binlog=0，當(dāng)事務(wù)提交后，Mysql僅僅是將binlog_cache中的數(shù)據(jù)寫入binlog文件，但不執(zhí)行fsync之類的磁盤同步指令通知文件系統(tǒng)將緩存刷新到磁盤，而是讓Filesystem自行決定什么時(shí)候來(lái)做同步。MySQL中默認(rèn)的設(shè)置是 sync_binlog=0，即不作任何強(qiáng)制性的磁盤刷新指令，這個(gè)設(shè)置性能是最好的，但風(fēng)險(xiǎn)也是最大的。一旦系統(tǒng)崩潰（Crash），在文件系統(tǒng)緩存中的所有二進(jìn)制日志信息都會(huì)丟失。從而帶來(lái)數(shù)據(jù)不完整問(wèn)題。

sync_binlog=n，在進(jìn)行n次事務(wù)提交以后，Mysql將執(zhí)行一次fsync之類的磁盤同步指令，同時(shí)文件系統(tǒng)將Binlog文件緩存刷新到磁盤。

可以適當(dāng)?shù)恼{(diào)整sync_binlog， 在犧牲一定的一致性下，獲取更高的并發(fā)和性能。

9、系統(tǒng)變量binlog_format 指定二進(jìn)制日志的類型。分別有STATEMENT、ROW、MIXED三種值。MySQL 5.7.6之前默認(rèn)為STATEMENT模式。MySQL 5.7.7之后默認(rèn)為ROW模式。這個(gè)參數(shù)主要影響主從復(fù)制。

基于SQL語(yǔ)句的復(fù)制(statement-based replication, SBR)，

基于行的復(fù)制(row-based replication, RBR)，

混合模式復(fù)制(mixed-based replication, MBR)。

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

方法1：使用show binlog events方式可以獲取當(dāng)前以及指定binlog的日志，不適宜提取大量日志。

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

SHOW BINLOG EVENTS IN 'mysql-bin.000005' \G

方法2: 使用mysqlbinlog命令行查看日志內(nèi)容(適宜批量提取日志)。

system mysqlbinlog /var/lib/mysql/DB-Server-bin.000013;
mysqlbinlog /var/lib/mysql/DB-Server-bin.000013 > test.sql;

二進(jìn)制日志的類型

基于段的日志格式

binlog_format=STATEMENT

記錄了操作的sql語(yǔ)句。

優(yōu)點(diǎn)：

日志記錄量相對(duì)較小，節(jié)約磁盤及網(wǎng)絡(luò)I/O,只對(duì)以一條記錄修改或插入ROW格式所產(chǎn)生日量小于段產(chǎn)生的日志量。

缺點(diǎn)：

必須記錄上下文信息，保證語(yǔ)句在從服務(wù)器上的執(zhí)行結(jié)果和在主服務(wù)器上相同。

特定函數(shù)如UUID,USER()這樣非確定性的函數(shù)無(wú)法復(fù)制。

可能造成mysql復(fù)制的主備服務(wù)器數(shù)據(jù)不一致，從而中斷復(fù)制鏈路。

顯示binlog 格式

show variables like 'binlog_format';

set session binlog_format=statement;

基于行的日志格式

將my.ini 二進(jìn)制格式修改為binlog_format=ROW

row 的優(yōu)點(diǎn)：row格式可以避免MYSQL復(fù)制中出現(xiàn)主從不一致的問(wèn)題，官方推薦這種格式。同一個(gè)sql語(yǔ)句修改了10000條數(shù)據(jù)的情況下?；诙蔚娜罩局粫?huì)記錄這個(gè)SQL語(yǔ)句。基于行的日志會(huì)有10000條記錄，分別記錄每一行數(shù)據(jù)的修改。

1.是mysql主從復(fù)制更加安全。

2.對(duì)每一行數(shù)據(jù)修改比基于段的復(fù)制高效。如果誤操作修改了數(shù)據(jù)庫(kù)中的數(shù)據(jù)，同時(shí)沒有備份可以恢復(fù)時(shí)，我們就可以通過(guò)分析二進(jìn)制日志，對(duì)日志中記錄的數(shù)據(jù)修改操作做反向處理的方式來(lái)達(dá)到恢復(fù)數(shù)據(jù)的目的。

row 的缺點(diǎn)：記錄日志量較大

binlog_row_image=[full,minimal,noblob]

full : 記錄列的所有修改；minimal ：只記錄修改的列。noblob :如果是text類型或clob字段，不記錄 這些日志。

使用 mysqlbinlog -vv ../data/mysql-bin.000005 查看明細(xì)日志。

set session binlog_row_image=minimal

混合日志格式:

binlog_format=MIXED

特點(diǎn)：根據(jù)sql語(yǔ)句由系統(tǒng)決定在記錄段和基于行的日志格式中進(jìn)行選擇。數(shù)據(jù)量大小由所執(zhí)行的SQL決定。

如何選擇二進(jìn)制格式

建議binlog_formart =mixed    or   binlog_format=row;   binlog_row_image=minimal;

復(fù)制方式：

1.基于SQL語(yǔ)句的復(fù)制（SBR)

優(yōu)點(diǎn)：生成日志量少，節(jié)約網(wǎng)絡(luò)傳輸?shù)腎D.并不要求對(duì)主從數(shù)據(jù)庫(kù)的表定義完全相同。

相比于基于行的復(fù)制方式更為靈活。

缺點(diǎn)：對(duì)于非確定事件，無(wú)法保證主從復(fù)制數(shù)據(jù)的一致性。對(duì)于存儲(chǔ)過(guò)程，觸發(fā)器

2.基于行的復(fù)制(RBR)

優(yōu)點(diǎn)：可以應(yīng)用于任何SQL的復(fù)制包括非確定性函數(shù)，存儲(chǔ)過(guò)程等?？梢詼p少數(shù)據(jù)庫(kù)鎖的使用。

缺點(diǎn)：要求主從數(shù)據(jù)庫(kù)的表結(jié)構(gòu)相同，否則就會(huì)中斷復(fù)制。

3.復(fù)制工作方式

1.主服務(wù)器將變更寫入二進(jìn)制日志。

2.從讀取主的二進(jìn)制日志變更并寫入到relay_log中。

基于日志點(diǎn)的復(fù)制，基于GTID的復(fù)制。

3.在從上重放relay_log中的日志。

基于SQL段的日志是在從庫(kù)上重新執(zhí)行記錄的SQL。

基于行的日志則是在從庫(kù)上直接應(yīng)用對(duì)數(shù)據(jù)行的修改。

以上就是詳解 Mysql 事務(wù)和Mysql 日志的詳細(xì)內(nèi)容，更多關(guān)于Mysql 事務(wù)和Mysql 日志的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！