Mysql 事務和Mysql 日志的用法

這篇文章主要講解了Mysql 事務和Mysql 日志的用法，內容清晰明了，對此有興趣的小伙伴可以學習一下，相信大家閱讀完之后會有幫助。

事務特性

1、原子性（Atomicity）：事務開始后所有操作，要么全部做完，要么全部不做，不可能停滯在中間環節。

2、一致性（Consistency）：事務開始前和結束后，數據庫的完整性約束沒有被破壞 。比如A向B轉賬，不可能A扣了錢，B卻沒收到。

3、隔離性（Isolation）：同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬。

4、持久性（Durability）：事務完成后，事務對數據庫的所有更新將被保存到數據庫，不能回滾。

事務并發問題

1、臟讀：事務A讀取了事務B更新的數據，然后B回滾操作，那么A讀取到的數據是臟數據

2、不可重復讀：事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A多次讀取同一數據時，結果 不一致。

3、幻讀：系統管理員A將數據庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束后發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

小結：不可重復讀的和幻讀很容易混淆，不可重復讀側重于修改，幻讀側重于新增或刪除。解決不可重復讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

事務隔離

mysql默認是“可重復讀”，串行化后

#查全局事務隔離級別
SELECT @@global.tx_isolation;
#查當前會話事務隔離級別
SELECT @@session.tx_isolation; 
#查當前事務隔離級別
SELECT @@tx_isolation;
#設置全局隔離級別
set global transaction isolation level read committed;
#設置當前會話隔離級別
set session transaction isolation level read committed;

串行化是最高的隔離級別，它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之,它是在每個讀的數據行上加上共享鎖，在這個級別，可能導致大量的超時現象和鎖競爭。

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

mysql日志文件系統組成

1、MySQL日志文件系統的組成

   a、錯誤日志：記錄啟動、運行或停止mysqld時出現的問題。
   b、通用日志：記錄建立的客戶端連接和執行的語句。
   c、更新日志：記錄更改數據的語句。該日志在MySQL 5.1中已不再使用。
   d、二進制日志：記錄所有更改數據的語句。還用于復制。
   e、慢查詢日志：記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢。
   f、Innodb日志：innodb redo log

二進制日志（binlog）：

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

包含關于每個更新數據庫(DML)的語句的執行時間信息

不包含沒有修改任何數據的語句，如果需要啟用該選項，需要開啟通用日志功能

主要目的是盡可能的將數據庫恢復到數據庫故障點，因為二進制日志包含備份后進行的所有更新

用于在主復制服務器上記錄所有將發送給從服務器的語句

啟用該選項數據庫性能降低1%，但保障數據庫完整性，對于重要數據庫值得以性能換完整。有些類似于oracle開啟歸檔模式。

show variables like '%version%'; 
show variables like '%log_bin%'; //是否啟用 binlog
show variables like '%binlog%'; //binlog 相關參數
show variables like '%datadir%';  //數據文件目錄，默認日志存在該目錄

#編輯my.cnf來設定binary log日志位置(注，配置二進制日志路徑及文件名后，系統變量log_bin被自動置為on) 

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

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

每次重啟MySQL服務也會生成一個新的二進制日志文件，相當于二進制日志切換。切換二進制日志時，你會看到這些number會不斷遞增。另外，除了這些二進制日志文件外，你會看到還生成了一個DB-Server-bin.index的文件，這個文件中存儲所有二進制日志文件的清單又稱為二進制文件的索引

二進制日志的刪除可以通過命令手工刪除，也可以設置自動清理。

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

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

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

二進制日志相關參數

1、系統變量log_bin_trust_function_creators，默認為OFF，這個參數開啟會限制存儲過程、Function、觸發器的創建。

2：系統變量sql_log_bin 用于控制會話級別二進制日志功能的開啟或關閉，默認為ON，表示啟用二進制日志功能。

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

可以通過查看Binlog_cache_disk_use 與 Binlog_cache_use來判斷binlog_cache_size是否需要調整。

4、系統變量max_binlog_cache_size 二進制日志能夠使用的最大cache內存大小。當執行多語句事務時，max_binlog_cache_size 如果不夠大，系統可能會報出“Multi-statement transaction required more than ‘max_binlog_cache_size' bytes of storage”的錯誤。

5、 系統變量max_binlog_stmt_cache_size

max_binlog_cache_size針對事務語句，max_binlog_stmt_cache_size針對非事務語句，當我們發現Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比較大時就需要考慮增大cache的大小

6、系統變量max_binlog_size， 表示二進制日志的最大值，一般設置為512M或1GB，但不能超過1GB。該設置并不能嚴格控制二進制日志的大小，尤其是二進制日志比較靠近為不而又遇到一根比較大事務時， 為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進當前日志，直到事務結束。

7、系統變量binlog_checksum 用作復制的主從校檢。 NONE表示不生成checksum，CRC-32表示使用這個算法做校檢。

8、系統變量sync_binlog，這個參數對于Mysql系統來說是至關重要的，它不僅影響到二進制日志文件對MySQL所帶來的性能損耗，而且還影響到MySQL中數據的完整性。

sync_binlog=0，當事務提交后，Mysql僅僅是將binlog_cache中的數據寫入binlog文件，但不執行fsync之類的磁盤同步指令通知文件系統將緩存刷新到磁盤，而是讓Filesystem自行決定什么時候來做同步。MySQL中默認的設置是 sync_binlog=0，即不作任何強制性的磁盤刷新指令，這個設置性能是最好的，但風險也是最大的。一旦系統崩潰（Crash），在文件系統緩存中的所有二進制日志信息都會丟失。從而帶來數據不完整問題。

sync_binlog=n，在進行n次事務提交以后，Mysql將執行一次fsync之類的磁盤同步指令，同時文件系統將Binlog文件緩存刷新到磁盤。

可以適當的調整sync_binlog， 在犧牲一定的一致性下，獲取更高的并發和性能。

9、系統變量binlog_format 指定二進制日志的類型。分別有STATEMENT、ROW、MIXED三種值。MySQL 5.7.6之前默認為STATEMENT模式。MySQL 5.7.7之后默認為ROW模式。這個參數主要影響主從復制。

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

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

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

查看二進制日志內容

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

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

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

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

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

二進制日志的類型

基于段的日志格式

binlog_format=STATEMENT

記錄了操作的sql語句。

優點：

日志記錄量相對較小，節約磁盤及網絡I/O,只對以一條記錄修改或插入ROW格式所產生日量小于段產生的日志量。

缺點：

必須記錄上下文信息，保證語句在從服務器上的執行結果和在主服務器上相同。

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

可能造成mysql復制的主備服務器數據不一致，從而中斷復制鏈路。

顯示binlog 格式

show variables like 'binlog_format';

set session binlog_format=statement;

基于行的日志格式

將my.ini 二進制格式修改為binlog_format=ROW

row 的優點：row格式可以避免MYSQL復制中出現主從不一致的問題，官方推薦這種格式。同一個sql語句修改了10000條數據的情況下。基于段的日志只會記錄這個SQL語句。基于行的日志會有10000條記錄，分別記錄每一行數據的修改。

1.是mysql主從復制更加安全。

2.對每一行數據修改比基于段的復制高效。如果誤操作修改了數據庫中的數據，同時沒有備份可以恢復時，我們就可以通過分析二進制日志，對日志中記錄的數據修改操作做反向處理的方式來達到恢復數據的目的。

row 的缺點：記錄日志量較大

binlog_row_image=[full,minimal,noblob]

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

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

set session binlog_row_image=minimal

混合日志格式:

binlog_format=MIXED

特點：根據sql語句由系統決定在記錄段和基于行的日志格式中進行選擇。數據量大小由所執行的SQL決定。

如何選擇二進制格式

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

復制方式：

1.基于SQL語句的復制（SBR)

優點：生成日志量少，節約網絡傳輸的ID.并不要求對主從數據庫的表定義完全相同。

相比于基于行的復制方式更為靈活。

缺點：對于非確定事件，無法保證主從復制數據的一致性。對于存儲過程，觸發器

2.基于行的復制(RBR)

優點：可以應用于任何SQL的復制包括非確定性函數，存儲過程等。可以減少數據庫鎖的使用。

缺點：要求主從數據庫的表結構相同，否則就會中斷復制。

3.復制工作方式

1.主服務器將變更寫入二進制日志。

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

基于日志點的復制，基于GTID的復制。

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

基于SQL段的日志是在從庫上重新執行記錄的SQL。

基于行的日志則是在從庫上直接應用對數據行的修改。

看完上述內容，是不是對Mysql 事務和Mysql 日志的用法有進一步的了解，如果還想學習更多內容，歡迎關注億速云行業資訊頻道。