MySQL innodb 事務的實現（看書筆記）

reference：MySQL技術內幕 Innodb儲存引擎
Write-Ahead Logging ,WAL：預寫日誌方式
【1】Redo Log
在Innodb儲存引擎中，事務日誌是透過redo和innodb的儲存引擎日誌緩衝（Innodb log buffer）來實現的，當開始一個事務的時候，會記錄該事務的lsn(log sequence number)號; 當事務執行時，會往InnoDB儲存引擎的日誌
的日誌快取裡面插入事務日誌；當事務提交時，必須將儲存引擎的日誌緩衝寫入磁碟（透過innodb_flush_log_at_trx_commit來控制），也就是寫資料前，需要先寫日誌。這種方式稱為“預寫日誌方式”，
innodb透過此方式來保證事務的完整性。也就意味著磁碟上儲存的資料頁和記憶體緩衝池上面的頁是不同步的，是先寫入redo log，然後寫入data file，因此是一種非同步的方式。透過 show engine innodb status\G 來觀察之間的差距

-- 建立一張表z，然後建立一個往表匯入資料的儲存過程load_test。透過命令show engine innodb status觀察當前的redo日誌情況

create table z(a int, primary key(a))engine=innodb;DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`load_test`$$CREATE PROCEDURE load_test(COUNT INT)BEGIN  DECLARE i INT UNSIGNED DEFAULT 0;  START TRANSACTION;  WHILE i < COUNT DO        REPLACE INTO z SELECT i;  -- 用replace是因為需要重複呼叫，避免主鍵重複insert報錯。           SET i=i+1;        END WHILE; COMMIT;END;$$
......
---
LOG
---
Log sequence number 20499052099 當前的LSN
Log flushed up to 20499052099 表示重新整理到redo log的LSN
Pages flushed up to 20499052099 表示重新整理到磁碟的lsn
Last checkpoint at 20499052099
0 pending log writes, 0 pending chkp writes
373 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 1270857
Buffer pool size 8191
Free buffers 7562
Database pages 612
Old database pages 205
Modified db pages 0
Pending reads 0
......

mysql> call test.load_test(100000);
Query OK, 0 rows affected, 1 warning (6.28 sec)
mysql> show engine innodb status\G
......
---
LOG
---
Log sequence number 20504734913
Log flushed up to 20504734913
Pages flushed up to 20504734913
Last checkpoint at 20504734913
0 pending log writes, 0 pending chkp writes
398 log i/o's done, 0.38 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
......
看來Log sequence number和Log flushed up以及Pages flushed u3個還是一樣的。
再多匯入點資料，重新開啟另外一個視窗看看innodb狀態
mysql> call test.load_test(1000000);
mysql> show engine innodb status\G
......
---
LOG
---
Log sequence number 20527044411
Log flushed up to 20525763843
Pages flushed up to 20517902997
Last checkpoint at 20504829471
0 pending log writes, 0 pending chkp writes
432 log i/o's done, 1.26 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
這次看到的show engine innodb status的結果就不一樣了，Log sequence number > Log flushed up > Pages flushed up > Last checkpoint，所以從這裡也可以看出，先寫redo日誌，再寫資料檔案
在實際寫比較頻繁的productiion上面，這3個值都會是不一樣的。

【2】Undo
undo的記錄正好與redo的相反，insert變成delete，update變成相反的update，redo放在redo file裡面。而undo放在一個內部的一個特殊segment上面，儲存與共享表空間內（ibdata1或者ibdata2中）。
py_innodb_page_info.py
下載地址： wget
[root@mysql data56]# python /root/py_innodb_page_info.py /data56/ibdata1
Traceback (most recent call last):
File "/root/py_innodb_page_info.py", line 3, in
import mylib
ImportError: No module named mylib
[root@mysql data56]#
還需要下載 mylib.py和include.py檔案，並和py_innodb_page_info.py放在一個目錄之下
wget
wget
ps：有的時候network會斷掉，要多執行幾次wget去下載。
[root@mysql ~]# python /root/py_innodb_page_info.py /data56/ibdata1
Total number of page: 8960:
Insert Buffer Free List: 1035
Insert Buffer Bitmap: 1
System Page: 134
Transaction system Page: 1
Freshly Allocated Page: 4010
Undo Log Page: 1556
File Segment inode: 5
B-tree Node: 2217
File Space Header: 1
[root@mysql ~]#
[root@mysql ~]#
undo不是物理恢復，是邏輯恢復，因為它是透過執行相反的dml語句來實現的。而且不會回收因為insert和upate而新增加的page頁的。
undo頁的回收是透過master thread執行緒來實現的。
驗證row模式下，生產的binlog傳到從庫上面，大概需要多久！
匯出
time /usr/local/mysql56/bin/mysqldump --socket=/data56/mysql.sock -uroot -p123456 -P3307 --extended-insert=false --all-databases> /root/adb.sql
[root@mysql ~]# time /usr/local/mysql56/bin/mysqldump --socket=/data56/mysql.sock -uroot -p123456 -P3307 --extended-insert=false --all-databases> /root/adb.sql
Warning: Using a password on the command line interface can be insecure.

real 5m10.757s
user 2m42.137s
sys 0m11.346s
匯入
time /usr/local/mysql56/bin/mysql --socket=/data56/mysql.sock -uroot -p123456 -P3307 < /root/adb.sql
在從庫上面檢查：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.88.49.119
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql56-bin.000008
Read_Master_Log_Pos: 513272653
Relay_Log_File: mysql56-relay-bin.000019
Relay_Log_Pos: 103964146
Relay_Master_Log_File: mysql56-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 418536570
Relay_Log_Space: 513273109
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 857
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11901
Master_UUID: a6a1d870-80b5-11e2-84d2-00155d016a07
Master_Info_File: /data56/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

ps：Seconds_Behind_Master: 857，有延時的情況出現，可見在row模式下，import6G的單調insert的sql的時候，有延時。