Linux 第70天 mariadb transaction, log

Linux 第70天 mariadb transaction, log

時間: 20181011

        個人新開的小部落格，歡迎大家通路: www.winthcloud.top

目錄

并發控制

鎖政策：在鎖粒度及資料安全性尋求的平衡機制

事務transaction(ACID)

死鎖

日志

error-log,binary-log,general-log

transaction-log,slow-query-log,relay-log

mysqlbinlog工具使用

并發控制

為何要做并發控制?

即當多個請求同時到達時，到底是可以同時為其每一個請求做即時反應，還是需要等待

其它請求完成後才可對其響應，如一個請求正在寫某個表的某行資料，另一個請求要讀

此行的内容，後者到底可否讀取，還有如果請求都是讀的話是不是可以讓其所有請求都

可以同時讀，這便需要一個機制去控制。

鎖粒度：

表級鎖

行級鎖

鎖：

讀鎖：共享鎖S，隻讀不可寫，多個讀互不阻塞

寫鎖：獨占鎖,排它鎖X，一個寫鎖會阻塞其它讀和它鎖

實作

存儲引擎：自行實作其鎖政策和鎖粒度

伺服器級：實作了鎖，表級鎖；使用者可顯式請求

分類：

隐式鎖：由存儲引擎自動施加鎖

顯式鎖：使用者手動請求

鎖政策：在鎖粒度及資料安全性尋求的平衡機制

顯式使用鎖

LOCK TABLES 加鎖

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] ...

lock_type: READ ， WRITE

UNLOCK TABLES 解鎖

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE] 

查詢時加寫或讀鎖

事務

事務Transactions：一組原子性的SQL語句，或一個獨立工作單元

事務日志：記錄事務資訊，實作undo,redo等故障恢複功能

ACID特性：

A：atomicity原子性；整個事務中的所有操作要麼全部成功執行，要麼全部失敗後復原

C：consistency一緻性；資料庫總是從一個一緻性狀态轉換為另一個一緻性狀态

I：Isolation隔離性；一個事務所做出的操作在送出之前，是不能為其它事務所見；

隔離有多種隔離級别，實作并發

D：durability持久性；一旦事務送出，其所做的修改會永久儲存于資料庫中

啟動事務：

BEGIN

BEGIN WORK

START TRANSACTION

結束事務：

COMMIT：送出

ROLLBACK: 復原

注意：隻有事務型存儲引擎中的DML語句方能支援此類操作

自動送出：set autocommit={1|0} 預設為1，為0時設為非自動送出

建議：顯式請求和送出事務，而不要使用“自動送出”功能

事務支援儲存點：savepoint

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

事務隔離級别：從上至下更加嚴格

READ UNCOMMITTED 可讀取到未送出資料，産生髒讀

READ COMMITTED 可讀取到送出資料，但未送出資料不可讀，産生不可重複讀，

即可讀取到多個送出資料，導緻每次讀取資料不一緻

REPEATABLE READ 可重複讀，多次讀取資料都一緻，産生幻讀，即讀取過程中，即使有

其它送出的事務修改資料，仍隻能讀取到未修改前的舊資料。此為mariadb預設設定

SERIALIZABILE 可串行化，未送出的讀事務阻塞修改事務，或者未送出的修改事務阻塞

讀事務。導緻并發性能差

MVCC: 多版本并發控制，和事務級别相關

指定事務隔離級别：

伺服器變量tx_isolation指定，預設為REPEATABLE-READ，

可在GLOBAL和SESSION級進行設定

SET tx_isolation=''

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

伺服器選項中指定

vim /etc/my.cnf

[mysqld]

transaction-isolation=SERIALIZABLE

死鎖：

兩個或多個事務在同一資源互相占用，并請求鎖定對方占用的資源的狀态

事務日志：

事務日志的寫入類型為"追加",是以其操作為"順序IO"；

通常也被稱為：預寫式日志 write ahead logging

日志檔案： ib_logfile0 ib_logfile1

出現死鎖時預設資料庫會自行解決，可以人為檢視某些語句執行狀态，使用以下兩條指令

SHOW PROCESSLIST

KILL ID;

日志

事務日志 transaction log

中繼日志 reley log

錯誤日志 error log

通用日志 general log

慢查詢日志 slow query log

二進制日志 binary log

事務日志：transaction log

事務型存儲引擎自行管理和使用，建議和資料檔案分開存放

redo log

undo log

Innodb事務日志相關配置：

show variables like '%innodb_log%';

innodb_log_file_size 5242880 每個日志檔案大小

innodb_log_files_in_group 2 日志組成員個數

innodb_log_group_home_dir ./ 事務檔案路徑

永久儲存在my.cnf

innodb-log-group-home-dir=/data/mariadblog

中繼日志：relay log

主從複制架構中，從伺服器用于儲存從主伺服器的二進制日志中讀取的事件

錯誤日志

錯誤日志所記錄的内容

mysqld啟動和關閉過程中輸出的事件資訊

mysqld運作中産生的錯誤資訊

event scheduler運作一個event時産生的日志資訊

在主從複制架構中的從伺服器上啟動從伺服器線程時産生的資訊

錯誤日志相關配置

SHOW GLOBAL VARIABLES LIKE 'log_error'

錯誤檔案路徑：

log_error=/PATH/TO/LOG_ERROR_FILE

是否記錄警告資訊至錯誤日志檔案

log_warnings=1|0 預設值1

永久存儲變量内容

vim my.cnf

log_error=/data/mariadblog/error.log

通用日志：記錄對資料庫的通用操作，包括錯誤的SQL語句 (預設off)

通用日志可以儲存至檔案中也可以儲存至資料庫mysql的表中

檔案：file，預設值

表：table

通用日志相關設定

general_log=ON|OFF

general_log_file=HOSTNAME.log

log_output=TABLE|FILE|NONE

慢查詢日志：記錄執行查詢時長超出指定時長的操作

相關變量:

slow_query_log=ON|OFF 開啟或關閉慢查詢

long_query_time=N 慢查詢的閥值，機關秒

slow_query_log_file=HOSTNAME-slow.log 慢查詢日志檔案

log_slow_filter = admin,filesort,filesort_on_disk,full_join,

full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

上述查詢類型且查詢時長超過long_query_time，則記錄日志

log_queries_not_using_indexes=ON 不使用索引或使用全索引掃描，不論是否

達到慢查詢閥值的語句是否記錄日志，預設OFF，即不記錄

log_slow_rate_limit = 1 多少次查詢才記錄，mariadb特有

log_slow_verbosity= Query_plan,explain 記錄内容

log_slow_queries = OFF 同slow_query_log 新版已廢棄

二進制日志(重點)

記錄導緻資料改變或潛在導緻資料改變的SQL語句

記錄已送出的日志

不依賴于存儲引擎類型

功能：通過"重放"日志檔案中的事件來生成資料副本

注意：建議二進制日志和資料檔案分開存放

二進制日志記錄格式

二進制日志記錄三種格式

基于"語句"記錄：statement，記錄語句，預設模式

基于"行"記錄：row，記錄資料，日志量較大

混合模式：mixed, 讓系統自行判定該基于哪種方式進行

格式配置

show variables like 'binlog_format';

二進制日志檔案的構成

有兩類檔案

日志檔案：mysql|mariadb-bin.檔案名字尾，二進制格式

如： mariadb-bin.000001

索引檔案：mysql|mariadb-bin.index，文本格式

二進制日志相關的伺服器變量：

sql_log_bin=ON|OFF：是否記錄二進制日志，預設ON

log_bin=/PATH/BIN_LOG_FILE：指定檔案位置；預設ON，表示啟用

二進制日志功能，上述兩項都開啟才可實作記錄二進制日志

binlog_format=STATEMENT|ROW|MIXED：二進制日志記錄的格式，預設MIXED

max_binlog_size=1073741824：單個二進制日志檔案的最大體積，

到達最大值會自動滾動，預設為1G

說明：檔案達到上限時的大小未必為指定的精确值

sync_binlog=1|0：設定是否啟動二進制日志即時同步磁盤功能，

預設0，由作業系統負責同步日志到磁盤

expire_logs_days=N：二進制日志可以自動删除的天數。 預設為0，即不自動删除

log_bin_basename=/path/binary-log  設定二進制日志儲存位置和字首名字

log_bin_index=/path/binary-log.index 設定二進制索引檔案存放位置

二進制日志相關配置

檢視mariadb自行管理使用中的二進制日志檔案清單，及大小

SHOW {BINARY | MASTER} LOGS

檢視使用中的二進制日志檔案

SHOW MASTER STATUS

檢視二進制檔案中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos]

[LIMIT [offset,] row_count]

show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

mysqlbinlog：二進制日志的用戶端指令工具

指令格式：

mysqlbinlog [OPTIONS] log_file…

--start-position=# 指定開始位置

--stop-position=#

--start-datetime=

--stop-datetime=

時間格式：YYYY-MM-DD hh:mm:ss

--base64-output[=name]

示例：mysqlbinlog --start-position=6787 --stop-position=7527 

/var/lib/mysql/mariadb-bin.000003

mysqlbinlog --start-datetime="2018-01-30 20:30:10" 

--stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003

二進制日志事件的格式：

# at 328

#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 

exec_time=0 error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1446712300/*!*/;

CREATE TABLE tb1 (id int, name char(30))

/*!*/;

事件發生的日期和時間：151105 16:31:40

事件發生的伺服器辨別：server id 1

事件的結束位置：end_log_pos 431

事件的類型：Query

事件發生時所在伺服器執行此事件的線程的ID：thread_id=1

語句的時間戳與将其寫入二進制檔案中的時間差：exec_time=0

錯誤代碼：error_code=0

事件内容：

GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID

清除指定二進制日志：

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

示例：

PURGE BINARY LOGS TO 'mariadb-bin.000003'; 删除3之前的日志

PURGE BINARY LOGS BEFORE '2017-01-23';

PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二進制日志，index檔案重新記數

RESET MASTER [TO #]; 日志檔案從#開始記數，預設從1開始，

一般是master第一次啟動時執行，MariaDB10.1.6開始支援TO #