建站
咨詢
售后
建站咨詢
新聞中心
mysql命令行下怎樣實(shí)現(xiàn)數(shù)據(jù)的回滾操作
當(dāng)啟動(dòng)Binlog后，事務(wù)會(huì)產(chǎn)生Binlog Event，這些Event被看做事務(wù)數(shù)據(jù)的一部分。因此要保證事務(wù)的Binlog Event和InnoDB引擎中的數(shù)據(jù)的一致性。所以帶Binlog的CrashSafe要求MySQL宕機(jī)重啟后能夠保證：
創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比富裕網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式富裕網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋富裕地區(qū)。費(fèi)用合理售后完善，十多年實(shí)體公司更值得信賴。
- 所有已經(jīng)提交的事務(wù)的數(shù)據(jù)仍然存在。
- 所有沒有提交的事務(wù)的數(shù)據(jù)自動(dòng)回滾。
- 所有已經(jīng)提交了的事務(wù)的Binlog Event也仍然存在。
- 所有沒有提交事務(wù)沒有記錄Binlog Event。
這些要求很好理解，如果重啟后數(shù)據(jù)還在，但是Binlog Event沒有了，就沒辦法復(fù)制到其他節(jié)點(diǎn)上了。如果重啟后，數(shù)據(jù)沒了，但是Binlog Event還在，那么不存在的數(shù)據(jù)就會(huì)被復(fù)制到其他節(jié)點(diǎn)上，從而導(dǎo)致主從的不一致。
為了保證帶Binlog的CrashSafe，MySQL內(nèi)部使用的兩階段提交(Two Phase Commit)。
2 - MySQL的Two Phase Commit(2PC)
在開啟Binlog后，MySQL內(nèi)部會(huì)自動(dòng)將普通事務(wù)當(dāng)做一個(gè)XA事務(wù)來處理：
- 自動(dòng)為每個(gè)事務(wù)分配一個(gè)唯一的ID
- COMMIT會(huì)被自動(dòng)的分成Prepare和Commit兩個(gè)階段。
- Binlog會(huì)被當(dāng)做事務(wù)協(xié)調(diào)者(Transaction Coordinator)，Binlog Event會(huì)被當(dāng)做協(xié)調(diào)者日志。
想了解2PC，可以參考文檔：【?！?/p>
- 分布式事務(wù)ID(XID)
使用2PC時(shí)，MySQL會(huì)自動(dòng)的為每一個(gè)事務(wù)分配一個(gè)ID，叫XID。XID是唯一的，每個(gè)事務(wù)的XID都不相同。XID會(huì)分別被Binlog和InnoDB記入日志中，供恢復(fù)時(shí)使用。MySQ內(nèi)部的XID由三部分組成：
- 前綴部分
前綴部分是字符串"MySQLXid"
- Server ID部分
當(dāng)前MySQL的server_id
- query_id部分
為了保證XID的的唯一性，數(shù)字部分使用了query_id。MySQL內(nèi)部會(huì)自動(dòng)的為每一個(gè)語句分配一個(gè)query_id，全局唯一。
參考代碼：sql/xa。h的struct xid_t結(jié)構(gòu)。
- 事務(wù)的協(xié)調(diào)者Binlog
Binlog在2PC中充當(dāng)了事務(wù)的協(xié)調(diào)者（Transaction Coordinator）。由Binlog來通知InnoDB引擎來執(zhí)行prepare，commit或者rollback的步驟。事務(wù)提交的整個(gè)過程如下：
1. 協(xié)調(diào)者準(zhǔn)備階段(Prepare Phase)
告訴引擎做Prepare，InnoDB更改事務(wù)狀態(tài)，并將Redo Log刷入磁盤。
2. 協(xié)調(diào)者提交階段(Commit Phase)
2.1 記錄協(xié)調(diào)者日志，即Binlog日志。
2.2 告訴引擎做commit。
注意：記錄Binlog是在InnoDB引擎Prepare(即Redo Log寫入磁盤)之后，這點(diǎn)至關(guān)重要。
在MySQ的代碼中將協(xié)調(diào)者叫做tc_log。在MySQL啟動(dòng)時(shí)，tc_log將被初始化為mysql_bin_log對(duì)象。參考sql/binlog.cc中的init_server_components()：
if (opt_bin_log) tc_log= mysql_bin_log;
而在事務(wù)提交時(shí)，會(huì)依次執(zhí)行：
tc_log-prepare()；
tc_log-commit()；
參考代碼：sql/binlog.cc中的ha_commit_trans()。當(dāng)mysql_bin_log是tc_log時(shí)，prepare和commit的代碼在sql/binlog.cc中：
MYSQL_BIN_LOG::prepare()；
MYSQL_BIN_LOG::commit()；
-協(xié)調(diào)者日志Xid_log_event
作為協(xié)調(diào)者，Binlog需要將事務(wù)的XID記入日志，供恢復(fù)時(shí)使用。Xid_log_event有以下幾個(gè)特點(diǎn)：
- 僅記錄query_id
因?yàn)榍熬Y部分不變，server_id已經(jīng)記錄在Event Header中，Xid_log_event中只記錄query_id部分。
- 標(biāo)志事務(wù)的結(jié)束
在Binlog中相當(dāng)于一個(gè)事務(wù)的COMMIT語句。
一個(gè)事務(wù)在Binlog中看起來時(shí)這樣的：
Query_log_event("BEGIN");DML產(chǎn)生的events; Xid_log_event;
- DDL沒有BEGIN，也沒有Xid_log_event 。
- 僅InnoDB的DML會(huì)產(chǎn)生Xid_log_event
因?yàn)镸yISAM不支持2PC所以不能用Xid_log_event ，但會(huì)有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實(shí)現(xiàn)CrashSafe。
- Slave的CrashSafe不依賴Xid_log_event
事務(wù)在Slave上重做時(shí)，會(huì)重新產(chǎn)生XID。所以Slave服務(wù)器的CrashSafe并不依賴于Xid_log_event 。Xid_log_event 和Query_log_event("COMMIT")，只是作為事務(wù)的結(jié)尾，告訴Slave Applier去提交這個(gè)事務(wù)。因此二者在Slave上的影響是一樣的。
3 - 恢復(fù)(Recovery)
這個(gè)機(jī)制是如何保證MySQL的CrashSafe的呢，我們來分析一下。這里我們假設(shè)用戶設(shè)置了以下參數(shù)來保證可靠性：
- 恢復(fù)前事務(wù)的狀態(tài)
在恢復(fù)開始前事務(wù)有以下幾種狀態(tài)：
- InnoDB中已經(jīng)提交
根據(jù)前面2PC的過程，可知Binlog中也一定記錄了該事務(wù)的的Events。所以這種事務(wù)是一致的不需要處理。
- InnoDB中是prepared狀態(tài)，Binlog中有該事務(wù)的Events。
需要通知InnoDB提交這些事務(wù)。
- InnoDB中是prepared狀態(tài)，Binlog中沒有該事務(wù)的Events。
因?yàn)锽inlog還沒記錄，需要通知InnoDB回滾這些事務(wù)。
- Before InnoDB Prepare
事務(wù)可能還沒執(zhí)行完，因此InnoDB中的狀態(tài)還沒有prepare。根據(jù)2PC的過程，Binlog中也沒有該事務(wù)的events。 需要通知InnoDB回滾這些事務(wù)。
- 恢復(fù)過程
從上面的事務(wù)狀態(tài)可以看出：恢復(fù)時(shí)事務(wù)要提交還是回滾，是由Binlog來決定的。
- 事務(wù)的Xid_log_event 存在，就要提交。
- 事務(wù)的Xid_log_event 不存在，就要回滾。
恢復(fù)的過程非常簡(jiǎn)單：
- 從Binlog中讀出所有的Xid_log_event
- 告訴InnoDB提交這些XID的事務(wù)
- InnoDB回滾其它的事務(wù)
mysql 能否設(shè)置DDL語句 可以回滾？
MySQL8.0 開始支持原? DDL(atomic DDL)，數(shù)據(jù)字典的更新，存儲(chǔ)引擎操作，寫?進(jìn)制日志結(jié)合成了一個(gè)事務(wù)。在沒有原?DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會(huì)有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數(shù)據(jù)字典的區(qū)別
在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ?件外，還存在于系統(tǒng)表中（MyISAM ?事務(wù)引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原?性成為了可能
存儲(chǔ)引擎?持
目前，只有InnoDB存儲(chǔ)引擎?持原子DDL，為了實(shí)現(xiàn)原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是?個(gè)隱藏在mysql.ibd 數(shù)據(jù)字典表空間?的數(shù)據(jù)字典表。要看mysql.innodb_ddl_log 中的內(nèi)容，需要
SET GLOBAL?LOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認(rèn)為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 操作步驟
準(zhǔn)備：創(chuàng)建所需的對(duì)象并將DDL?志寫入 mysql.innodb_ddl_log表中。DDL日志定義了如何前滾和回滾DDL操作。
執(zhí)行：執(zhí)?DDL操作。例如，為CREATE TABLE操作執(zhí)?創(chuàng)建。
提交：更新數(shù)據(jù)字典并提交數(shù)據(jù)字典事務(wù)。
Post-DDL：重播并從mysql.innodb_ddl_log表格中刪除DDL?志。為確?；貪L可以安全執(zhí)??不引?不?致性，在此最后階段執(zhí)??件操作（如重命名或刪除數(shù)據(jù)文件）。這一階段還從 mysql.innodb_dynamic_metadata的數(shù)據(jù)字典表刪除的動(dòng)態(tài)元數(shù)據(jù)為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。
?論事務(wù)是提交還是回滾，DDL日志都會(huì)mysql.innodb_ddl_log在Post-DDL階段重播并從表中刪除 。mysql.innodb_ddl_log如果服務(wù)器在DDL操作期間暫停，DDL?志應(yīng)該只保留在表中。在這種情況下，DDL?志會(huì)在恢復(fù)后重播并刪除。
在恢復(fù)情況下，當(dāng)服務(wù)器重新啟動(dòng)時(shí)，可能會(huì)提交或回退DDL事務(wù)。如果在重做?志和?進(jìn)制日志中存在DDL操作的提交階段期間執(zhí)?的數(shù)據(jù)字典事務(wù)，則該操作被認(rèn)為是成功的并且被前滾。否則，在InnoDB重放數(shù)據(jù)字典重做日志時(shí)回滾不完整的數(shù)據(jù)字典事務(wù) ，并且回滾DDL事務(wù)。
原?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
示例
請(qǐng)點(diǎn)擊輸入圖片描述
請(qǐng)點(diǎn)擊輸入圖片描述
結(jié)論
在MySQL8.0之前，alter table 操作在server crash的情況下，會(huì)遺留.frm,.ibd文件。MySQL8.0 能實(shí)現(xiàn)原?DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會(huì)遺留.frm，.ibd臨時(shí)文件。讓我們?起期待MySQL8.0 GA的到來吧！
mysql中，如何設(shè)置手動(dòng)回滾事物？
關(guān)于事務(wù)
設(shè)置savepoint和回滾到savepoint
mysql如何快速回滾
我們經(jīng)常會(huì)遇到操作一張大表，發(fā)現(xiàn)操作時(shí)間過長(zhǎng)或影響在線業(yè)務(wù)了，想要回退大表操作的場(chǎng)景。在我們停止大表操作之后，等待回滾是一個(gè)很漫長(zhǎng)的過程，盡管你可能對(duì)知道一些縮短時(shí)間的方法，處于對(duì)生產(chǎn)環(huán)境數(shù)據(jù)完整性的敬畏，也會(huì)選擇不做介入。最終選擇不作為的原因大多源于對(duì)操作影響的不確定性。實(shí)踐出真知，下面針對(duì)兩種主要提升事務(wù)回滾速度的方式進(jìn)行驗(yàn)證，一種是提升操作可用內(nèi)存空間，一種是通過停實(shí)例，禁用 redo 回滾方式進(jìn)行進(jìn)行驗(yàn)證。
仔細(xì)閱讀過官方手冊(cè)的同學(xué)，一定留意到了對(duì)于提升大事務(wù)回滾效率，官方提供了兩種方法：一是增加 innodb_buffer_pool_size 參數(shù)大小，二是合理利用 innodb_force_recovery=3 參數(shù)，跳過事務(wù)回滾過程。第一種方式比較溫和，innodb_buffer_pool_size 參數(shù)是可以動(dòng)態(tài)調(diào)整的，可行性也較高。第二種方式相較之下較暴力，但效果較好。
兩種方式各有自己的優(yōu)點(diǎn)，第一種方式對(duì)線上業(yè)務(wù)系統(tǒng)影響較小，不會(huì)中斷在線業(yè)務(wù)。第二種方式效果更顯著，會(huì)短暫影響業(yè)務(wù)連續(xù)，回滾所有沒有提交的事務(wù)。
使用mysql時(shí)候不小心多update了數(shù)據(jù)，想問一下如何才能夠返回上一步？
1、首先：創(chuàng)建一個(gè)表格，插入數(shù)據(jù)。
2、查看一下表格插入的所有數(shù)據(jù)。
3、用update 的命令修改wulianwang2改為wulianwang3看圖，這是根據(jù)id的位置進(jìn)行查找的。
4、查看剛剛修改有沒有成功，也是用到剛剛的查詢語句。
5、其實(shí)就是增加了一些修改的條件，剛剛只是修改一個(gè)值，現(xiàn)在改為兩個(gè)值也是可行的，兩個(gè)值之間都好隔開，其他不變。
6、最后查看修改后的表，就完成了。
mysql如何做事件回滾
BEGIN//開始COMMIT//如果都成功，MYSQL沒錯(cuò)誤就執(zhí)行這個(gè)ROLLBACK//事件回滾 不過要注意的是，數(shù)據(jù)庫表要InnoDB這種格式。MyISAM這個(gè)格式不支持回滾的。
新聞名稱：mysql怎么才能回滾,mysql刪表能回滾嗎
本文來源：http://www.dlmjj.cn/article/hogioc.html
