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

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

從事成都移動機房托管，服務器租用，云主機，雅安服務器托管，域名申請，CDN，網(wǎng)絡代維等服務。

- 所有已經(jīng)提交的事務的數(shù)據(jù)仍然存在。

- 所有沒有提交的事務的數(shù)據(jù)自動回滾。

- 所有已經(jīng)提交了的事務的Binlog Event也仍然存在。

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

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

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

2 - MySQL的Two Phase Commit(2PC)

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

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

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

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

想了解2PC，可以參考文檔：【?！?/p>

- 分布式事務ID(XID)

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

- 前綴部分

前綴部分是字符串"MySQLXid"

- Server ID部分

當前MySQL的server_id

- query_id部分

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

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

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

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

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

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

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

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

2.2 告訴引擎做commit。

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

在MySQ的代碼中將協(xié)調(diào)者叫做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é)調(diào)者日志Xid_log_event

作為協(xié)調(diào)者，Binlog需要將事務的XID記入日志，供恢復時使用。Xid_log_event有以下幾個特點：

- 僅記錄query_id

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

- 標志事務的結束

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

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

Query_log_event("BEGIN");DML產(chǎn)生的events; Xid_log_event;

- DDL沒有BEGIN，也沒有Xid_log_event 。

- 僅InnoDB的DML會產(chǎn)生Xid_log_event

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

Query_log_event("BEGIN");DML產(chǎn)生的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上重做時，會重新產(chǎn)生XID。所以Slave服務器的CrashSafe并不依賴于Xid_log_event 。Xid_log_event 和Query_log_event("COMMIT")，只是作為事務的結尾，告訴Slave Applier去提交這個事務。因此二者在Slave上的影響是一樣的。

3 - 恢復(Recovery)

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

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

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

- InnoDB中已經(jīng)提交

根據(jù)前面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。根據(jù)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)，數(shù)據(jù)字典的更新，存儲引擎操作，寫?進制日志結合成了一個事務。在沒有原?DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數(shù)據(jù)字典的區(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 數(shù)據(jù)字典表空間?的數(shù)據(jù)字典表。要看mysql.innodb_ddl_log 中的內(nèi)容，需要

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)建。

提交：更新數(shù)據(jù)字典并提交數(shù)據(jù)字典事務。

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

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

在恢復情況下，當服務器重新啟動時，可能會提交或回退DDL事務。如果在重做?志和?進制日志中存在DDL操作的提交階段期間執(zhí)?的數(shù)據(jù)字典事務，則該操作被認為是成功的并且被前滾。否則，在InnoDB重放數(shù)據(jù)字典重做日志時回滾不完整的數(shù)據(jù)字典事務 ，并且回滾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不小心批量update，請問如何數(shù)據(jù)回滾

首先確定執(zhí)行update語句時是否設置成的非自動提交，若設置了，直接rollback；若未設置，查看是否啟用了二進制日志文件，若是，將其恢復到執(zhí)行該時間點前；若未設置，那數(shù)據(jù)庫層面就沒辦法了。

mysql如何快速回滾

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

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

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