mysql資料庫回滾查看（oracle資料庫表回滾）

本文目錄一覽：

1、mysql不小心批量update，請問如何數據回滾
2、mysql資料庫結構的修改，無法事務回滾嗎
3、mysql命令行下怎樣實現數據的回滾操作
4、php關於mysql資料庫簡單回滾問題
5、mysql 能否設置DDL語句可以回滾？

mysql不小心批量update，請問如何數據回滾

首先確定執行update語句時是否設置成的非自動提交，若設置了，直接rollback；若未設置，查看是否啟用了二進位日誌文件，若是，將其恢復到執行該時間點前；若未設置，那資料庫層面就沒辦法了。

mysql資料庫結構的修改，無法事務回滾嗎

1、為什麼auto_increament沒有回滾看

因為innodb的auto_increament的計數器記錄的當前值是保存在存內存中的，並不是存在於磁碟上，當mysql server處於運行的時候，這個計數值只會隨著insert改增長，不會隨著delete而減少。而當mysql server啟動時，當我們需要去查詢auto_increment計數值時，mysql便會自動執行：SELECT MAX(id) FROM 表名 FOR UPDATE;語句來獲得當前auto_increment列的最大值，然後將這個值放到auto_increment計數器中。所以就算 Rollback MySQL的auto_increament計數器也不會作負運算。

2、MySQL的事務對錶操作的時候是否是物理操作看

MySQL的事務是有redo和undo的，redo操作的所有信息都是記錄到 redo_log中，也就是說當一個事務做commit操作時，需要先把這個事務的操作寫到redo_log中，然後再把這些操作flush到磁碟上，當出現故障時，只需要讀取redo_log,然後再重新flush到磁碟就行了。

而對於undo就比較麻煩，MySQL在處理事務時，會在數據共享表空間里申請一個段叫做segment段，用保存undo信息，當在處理rollback，不是完完全全的物理undo，而是邏輯undo,就是說會對之前的操作進行反操作，但是這些共享表空間是不進行回收的。這些表空間的回收需要由mysql的master thread進程來進行回收。

mysql命令行下怎樣實現數據的回滾操作

當啟動Binlog後，事務會產生Binlog Event，這些Event被看做事務數據的一部分。因此要保證事務的Binlog Event和InnoDB引擎中的數據的一致性。所以帶Binlog的CrashSafe要求MySQL宕機重啟後能夠保證：

– 所有已經提交的事務的數據仍然存在。

– 所有沒有提交的事務的數據自動回滾。

– 所有已經提交了的事務的Binlog Event也仍然存在。

– 所有沒有提交事務沒有記錄Binlog Event。

這些要求很好理解，如果重啟後數據還在，但是Binlog Event沒有了，就沒辦法複製到其他節點上了。如果重啟後，數據沒了，但是Binlog Event還在，那麼不存在的數據就會被複制到其他節點上，從而導致主從的不一致。

為了保證帶Binlog的CrashSafe，MySQL內部使用的兩階段提交(Two Phase Commit)。

2 – MySQL的Two Phase Commit(2PC)

在開啟Binlog後，MySQL內部會自動將普通事務當做一個XA事務來處理：

– 自動為每個事務分配一個唯一的ID

– COMMIT會被自動的分成Prepare和Commit兩個階段。

– Binlog會被當做事務協調者(Transaction Coordinator)，Binlog Event會被當做協調者日誌。

想了解2PC，可以參考文檔：【。】

– 分散式事務ID(XID)

使用2PC時，MySQL會自動的為每一個事務分配一個ID，叫XID。XID是唯一的，每個事務的XID都不相同。XID會分別被Binlog和InnoDB記入日誌中，供恢復時使用。MySQ內部的XID由三部分組成：

– 前綴部分

前綴部分是字元串”MySQLXid”

– Server ID部分

當前MySQL的server_id

– query_id部分

為了保證XID的的唯一性，數字部分使用了query_id。MySQL內部會自動的為每一個語句分配一個query_id，全局唯一。

參考代碼：sql/xa。h的struct xid_t結構。

– 事務的協調者Binlog

Binlog在2PC中充當了事務的協調者（Transaction Coordinator）。由Binlog來通知InnoDB引擎來執行prepare，commit或者rollback的步驟。事務提交的整個過程如下：

1. 協調者準備階段(Prepare Phase)

告訴引擎做Prepare，InnoDB更改事務狀態，並將Redo Log刷入磁碟。

2. 協調者提交階段(Commit Phase)

2.1 記錄協調者日誌，即Binlog日誌。

2.2 告訴引擎做commit。

注意：記錄Binlog是在InnoDB引擎Prepare(即Redo Log寫入磁碟)之後，這點至關重要。

在MySQ的代碼中將協調者叫做tc_log。在MySQL啟動時，tc_log將被初始化為mysql_bin_log對象。參考sql/binlog.cc中的init_server_components()：

if (opt_bin_log) tc_log= mysql_bin_log;

而在事務提交時，會依次執行：

tc_log-prepare()；

tc_log-commit()；

參考代碼：sql/binlog.cc中的ha_commit_trans()。當mysql_bin_log是tc_log時，prepare和commit的代碼在sql/binlog.cc中：

MYSQL_BIN_LOG::prepare()；

MYSQL_BIN_LOG::commit()；

-協調者日誌Xid_log_event

作為協調者，Binlog需要將事務的XID記入日誌，供恢復時使用。Xid_log_event有以下幾個特點：

– 僅記錄query_id

因為前綴部分不變，server_id已經記錄在Event Header中，Xid_log_event中只記錄query_id部分。

– 標誌事務的結束

在Binlog中相當於一個事務的COMMIT語句。

一個事務在Binlog中看起來時這樣的：

Query_log_event(“BEGIN”);DML產生的events; Xid_log_event;

– DDL沒有BEGIN，也沒有Xid_log_event 。

– 僅InnoDB的DML會產生Xid_log_event

因為MyISAM不支持2PC所以不能用Xid_log_event ，但會有COMMIT Event。

Query_log_event(“BEGIN”);DML產生的events;Query_log_event(“COMMIT”);

問題：Query_log_event(“COMMIT”)和Xid_log_event 有不同的影響嗎？

– Xid_log_event 中的Xid可以幫助master實現CrashSafe。

– Slave的CrashSafe不依賴Xid_log_event

事務在Slave上重做時，會重新產生XID。所以Slave伺服器的CrashSafe並不依賴於Xid_log_event 。Xid_log_event 和Query_log_event(“COMMIT”)，只是作為事務的結尾，告訴Slave Applier去提交這個事務。因此二者在Slave上的影響是一樣的。

3 – 恢復(Recovery)

這個機制是如何保證MySQL的CrashSafe的呢，我們來分析一下。這裡我們假設用戶設置了以下參數來保證可靠性：

– 恢復前事務的狀態

在恢復開始前事務有以下幾種狀態：

– InnoDB中已經提交

根據前面2PC的過程，可知Binlog中也一定記錄了該事務的的Events。所以這種事務是一致的不需要處理。

– InnoDB中是prepared狀態，Binlog中有該事務的Events。

需要通知InnoDB提交這些事務。

– InnoDB中是prepared狀態，Binlog中沒有該事務的Events。

因為Binlog還沒記錄，需要通知InnoDB回滾這些事務。

– Before InnoDB Prepare

事務可能還沒執行完，因此InnoDB中的狀態還沒有prepare。根據2PC的過程，Binlog中也沒有該事務的events。需要通知InnoDB回滾這些事務。

– 恢復過程

從上面的事務狀態可以看出：恢復時事務要提交還是回滾，是由Binlog來決定的。

– 事務的Xid_log_event 存在，就要提交。

– 事務的Xid_log_event 不存在，就要回滾。

恢復的過程非常簡單：

– 從Binlog中讀出所有的Xid_log_event

– 告訴InnoDB提交這些XID的事務

– InnoDB回滾其它的事務

php關於mysql資料庫簡單回滾問題

我們經常會遇到操作一張大表，發現操作時間過長或影響在線業務了，想要回退大表操作的場景。在我們停止大表操作之後，等待回滾是一個很漫長的過程，儘管你可能對知道一些縮短時間的方法，處於對生產環境數據完整性的敬畏，也會選擇不做介入。最終選擇不作為的原因大多源於對操作影響的不確定性。實踐出真知，下面針對兩種主要提升事務回滾速度的方式進行驗證，一種是提升操作可用內存空間，一種是通過停實例，禁用 redo 回滾方式進行進行驗證。

仔細閱讀過官方手冊的同學，一定留意到了對於提升大事務回滾效率，官方提供了兩種方法：一是增加 innodb_buffer_pool_size 參數大小，二是合理利用 innodb_force_recovery=3 參數，跳過事務回滾過程。第一種方式比較溫和，innodb_buffer_pool_size 參數是可以動態調整的，可行性也較高。第二種方式相較之下較暴力，但效果較好。

兩種方式各有自己的優點，第一種方式對線上業務系統影響較小，不會中斷在線業務。第二種方式效果更顯著，會短暫影響業務連續，回滾所有沒有提交的事務。

mysql 能否設置DDL語句可以回滾？

MySQL8.0 開始支持原⼦ DDL(atomic DDL)，數據字典的更新，存儲引擎操作，寫⼆進位日誌結合成了一個事務。在沒有原⼦DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數據字典的區別

在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ⽂件外，還存在於系統表中（MyISAM ⾮事務引擎表中），在MySQL8.0 ，Data Dictionary 全部存在於Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原⼦性成為了可能

存儲引擎⽀持

目前，只有InnoDB存儲引擎⽀持原子DDL，為了實現原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是⼀個隱藏在mysql.ibd 數據字典表空間⾥的數據字典表。要看mysql.innodb_ddl_log 中的內容，需要

SET GLOBAL LOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認為2，error log 記錄Errors and

warnings，不不記錄notes）

SET GLOBAL innodb_print_ddl_logs=1;

CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;

查看error log

[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=30,

thread_id=25, space_id=9, old_file_path=./test/t1.ibd]

[Note] [MY-011066] InnoDB: DDL log delete : by id 30

[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=31,

thread_id=25, table_id=1066, new_file_path=test/t1]

[Note] [MY-011066] InnoDB: DDL log delete : by id 31

[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: FREE, id=32, thread_

id=25, space_id=9, index_id=143, page_no=4]

[Note] [MY-011066] InnoDB: DDL log delete : by id 32

[Note] [MY-011066] InnoDB: DDL log post ddl : begin for thread id : 25

[Note] [MY-011066] InnoDB: DDL log post ddl : end for thread id : 25

原子DDL 操作步驟

準備：創建所需的對象並將DDL⽇志寫入 mysql.innodb_ddl_log表中。DDL日誌定義了如何前滾和回滾DDL操作。

執行：執⾏DDL操作。例如，為CREATE TABLE操作執⾏創建。

提交：更新數據字典並提交數據字典事務。

Post-DDL：重播並從mysql.innodb_ddl_log表格中刪除DDL⽇志。為確保回滾可以安全執⾏⽽不引⼊不⼀致性，在此最後階段執⾏⽂件操作（如重命名或刪除數據文件）。這一階段還從 mysql.innodb_dynamic_metadata的數據字典表刪除的動態元數據為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。

⽆論事務是提交還是回滾，DDL日誌都會mysql.innodb_ddl_log在Post-DDL階段重播並從表中刪除。mysql.innodb_ddl_log如果伺服器在DDL操作期間暫停，DDL⽇志應該只保留在表中。在這種情況下，DDL⽇志會在恢復後重播並刪除。

在恢復情況下，當伺服器重新啟動時，可能會提交或回退DDL事務。如果在重做⽇志和⼆進位日誌中存在DDL操作的提交階段期間執⾏的數據字典事務，則該操作被認為是成功的並且被前滾。否則，在InnoDB重放數據字典重做日誌時回滾不完整的數據字典事務，並且回滾DDL事務。

原⼦DDL ⽀持類型

• DROP TABLES , all tables dropped or none

• DROP SCHEMA, all entities in the schema are dropped, or none

• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES

• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)

• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back

• RENAME TABLES, all or none

• ALTER TABLE successful or not done

示例

請點擊輸入圖片描述

結論

在MySQL8.0之前，alter table 操作在server crash的情況下，會遺留.frm,.ibd文件。MySQL8.0 能實現原⼦DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會遺留.frm，.ibd臨時文件。讓我們⼀起期待MySQL8.0 GA的到來吧！

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/243640.html