mysql命令行下怎樣實現(xiàn)數據的回滾操作

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

創(chuàng)新互聯(lián)-專業(yè)網站定制、快速模板網站建設、高性價比富裕網站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式富裕網站制作公司更省心,省錢,快速模板網站建設找我們，業(yè)務覆蓋富裕地區(qū)。費用合理售后完善，十多年實體公司更值得信賴。

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

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

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

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

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

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

2 - MySQL的Two Phase Commit(2PC)

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

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

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

- Binlog會被當做事務協(xié)調者(Transaction Coordinator)，Binlog Event會被當做協(xié)調者日志。

想了解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結構。

- 事務的協(xié)調者Binlog

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

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

告訴引擎做Prepare，InnoDB更改事務狀態(tài)，并將Redo Log刷入磁盤。

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

2.1 記錄協(xié)調者日志，即Binlog日志。

2.2 告訴引擎做commit。

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

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

if (opt_bin_log) tc_log= mysql_bin_log;

而在事務提交時，會依次執(zhí)行：

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()；

-協(xié)調者日志Xid_log_event

作為協(xié)調者，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實現(xiàn)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的呢，我們來分析一下。這里我們假設用戶設置了以下參數來保證可靠性：

- 恢復前事務的狀態(tài)

在恢復開始前事務有以下幾種狀態(tài)：

- InnoDB中已經提交

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

- InnoDB中是prepared狀態(tài)，Binlog中有該事務的Events。

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

- InnoDB中是prepared狀態(tài)，Binlog中沒有該事務的Events。

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

- Before InnoDB Prepare

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

- 恢復過程

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

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

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

恢復的過程非常簡單：

- 從Binlog中讀出所有的Xid_log_event

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

- InnoDB回滾其它的事務

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

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

在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ?件外，還存在于系統(tǒng)表中（MyISAM ?事務引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原?性成為了可能

存儲引擎?持

目前，只有InnoDB存儲引擎?持原子DDL，為了實現(xiàn)原子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 操作步驟

準備：創(chuàng)建所需的對象并將DDL?志寫入 mysql.innodb_ddl_log表中。DDL日志定義了如何前滾和回滾DDL操作。

執(zhí)行：執(zhí)?DDL操作。例如，為CREATE TABLE操作執(zhí)?創(chuàng)建。

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

Post-DDL：重播并從mysql.innodb_ddl_log表格中刪除DDL?志。為確?；貪L可以安全執(zhí)??不引?不?致性，在此最后階段執(zhí)??件操作（如重命名或刪除數據文件）。這一階段還從 mysql.innodb_dynamic_metadata的數據字典表刪除的動態(tài)元數據為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。

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

在恢復情況下，當服務器重新啟動時，可能會提交或回退DDL事務。如果在重做?志和?進制日志中存在DDL操作的提交階段期間執(zhí)?的數據字典事務，則該操作被認為是成功的并且被前滾。否則，在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 能實現(xiàn)原?DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會遺留.frm，.ibd臨時文件。讓我們?起期待MySQL8.0 GA的到來吧！

mysql中，如何設置手動回滾事物？

關于事務

設置savepoint和回滾到savepoint

mysql如何快速回滾

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

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

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

使用mysql時候不小心多update了數據，想問一下如何才能夠返回上一步？

1、首先：創(chuàng)建一個表格，插入數據。

2、查看一下表格插入的所有數據。

3、用update 的命令修改wulianwang2改為wulianwang3看圖，這是根據id的位置進行查找的。

4、查看剛剛修改有沒有成功，也是用到剛剛的查詢語句。

5、其實就是增加了一些修改的條件，剛剛只是修改一個值，現(xiàn)在改為兩個值也是可行的，兩個值之間都好隔開，其他不變。

6、最后查看修改后的表，就完成了。

mysql如何做事件回滾

BEGIN//開始COMMIT//如果都成功，MYSQL沒錯誤就執(zhí)行這個ROLLBACK//事件回滾 不過要注意的是，數據庫表要InnoDB這種格式。MyISAM這個格式不支持回滾的。