- 1PS提示：因為圖層已鎖定，無法編輯圖層的處理方法
- 2picacg蘋果版怎么找不到|picacg ios版是不是下架了介紹
- 3Adobe Illustrator CS5 序列號大全
- 4ACDsee注冊碼免費分享（含ACDsee18、ACDsee10等版本）
- 5PDF瀏覽器能看3D文件嗎？PDF瀏覽器看3D文件圖文詳細教程
- 6Potato(土豆聊天)怎么換頭像|Potato app更改頭像方法介紹
- 7Potato(馬鈴薯聊天)怎么注冊不了|Potato不能注冊處理方法介紹
- 8最新的Adobe Illustrator CS4序列號大全
- 9Intel i7-9700K性能跑分曝光：同代提升約12%
- 10qq郵件是否已讀怎么看 QQ郵箱已經發出去的郵件怎么知道對方是否已經查看
- 11XP系統怎么清除緩存？
- 12AMD推土機架構回顧：雖隕落卻是Zen成功的墊腳石
[摘要]update、delete的條件寫錯甚至沒有寫，導致數據操作錯誤，需要恢復被誤操作的行記錄。這種情形，其實時有發生，可以選擇用備份文件+binlog來恢復到測試環境，然后再做數據修復，但是這樣其實需要耗費一定的時間跟資源。 其實，如果binlog format為row，binlog文件中是會...
update、delete的條件寫錯甚至沒有寫，導致數據操作錯誤，需要恢復被誤操作的行記錄。這種情形，其實時有發生，可以選擇用備份文件+binlog來恢復到測試環境，然后再做數據修復，但是這樣其實需要耗費一定的時間跟資源。 其實，如果binlog format為row，binlog文件中是會詳細記錄每一個事務涉及到操作，并把每一個事務影響到行記錄均存儲起來，能否給予binlog 文件來反解析數據庫的行記錄變動情況呢？
業界已有不少相關的腳本及工具，但是隨著MySQL版本的更新、binlog記錄內容的變化以及需求不一致，大多數腳本不太適合個人目前的使用需求，所以開始著手編寫 mysql的 flash back腳本。
如果轉載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權歸 博客園 蘇家小蘿卜 所有。望各位支持！
僅在MySQL 5.6/5.7版本測試，python運行環境需要安裝pymysql模塊。
1 實現內容
根據binlog文件，對某個\些事務、某段時間某些表、某段時間全庫 做回滾操作，實現閃回功能。工具處理過程中，會把binlog中的事務修改的行記錄存儲到表格中去，通過 dml_sql 列，可以查看每一個事務內部的所有行記錄變更情況，通過 undo_sql 查看回滾的SQL內容。如下圖，然后再根據表格內容做回滾操作。
那么這個腳本有哪些優點呢？
回滾分為2個命令：第一個命令 分析binglog并存儲進入數據庫；第二個命令 執行回滾操作；
回滾的時候，可以把執行腳本跟回滾腳本統一存放到數據庫中，可以查看 更新內容以及回滾內容；
根據存儲的分析表格，方便指定事務或者指定表格來來恢復；
詳細的日志輸出，說明分析進度跟執行進度。
分析binlog的輸出截圖（分析1G的binlog文件）
回滾數據庫的輸出截圖：
2 原理
前提：實例啟動了binlog并且格式為ROW。
使用python對mysqlbinlog后的log文件進行文本分析處理，在整個處理過程中，有以下6個疑難點需要處理：
判斷一個事務的開始跟結束
同一個事務的執行順序需要反序執行
解析回滾SQL
同一個事務操作不同表格處理
轉義字符處理，比如換行符、tab符等等
timestamp數據類型參數值轉換
負數處理
單個事務涉及到行修改SQL操作了 max_allow
針對某個表格做回滾，而不是全庫回滾
2.1 事務的開始與結束
按照Xid出現的位置來判斷，從binlog文件的最開始開始讀取，遇到SQL語句則提取出來，直到遇到Xid，統一把之前提取出來的SQL匯總為一個事務，然后繼續提取SQL語句，直到遇到下一個Xid，再把這個事務的SQL匯總成一個事務，一直這樣循環，直至文件順序遍歷結束。
2.2 事務內部反序處理
同一個事務中，如果有多個表格多行記錄發生變更，在回滾的時候，應該反序回滾SQL，那么，如何將提取出來的SQL反序存儲呢？思路如下：
每行記錄的修改SQL獨立出來
將獨立出來的SQL反序存儲
假設正序的事務SQL語句存儲在變量 dml_sql 中，反序后的可以回滾的SQL存儲在變量 undo_sql中。按順序把行記錄修改的SQL抽取出來 存儲到變量 record_sql 中去，然后 賦值 undo_sql =record_sql + undo_sql ，再置空 record_sql 變量，如此，便可實現反序事務內部的執行SQL。
2.3 解析回滾SQL
首先，查看binlog的日志內容，發現行修改的SQL情形如下，提取過程中需要注意這幾個問題：
行記錄的列名配對，binlog file存儲的列序號，不能直接使用
WHERE部分 跟 SET部分 之間并無關鍵字或者符號，需要添加 AND 或者 逗號
DELETE SQL 需要反轉為 INSERT
UPDATE SQL 需要把WHERE 跟 SET的部分進行替換
INSERT SQL需要反轉為 DELETE
2.4 同事務不同表格處理
同一個事務中，允許對不同表格進行數據修改，這點在列名替換列序號的時候，需要留意處理。
每一個的行記錄前有一行記錄，含有 'Table_map' 標識，會說明這一行當行記錄是修改哪個表格，可以根據這個提示，來替換binlog里邊的列序號為列名。
2.5 轉義字符處理
binlog文件在對非空格的空白字符處理，采用轉義字符字符串存儲，比如，在表格insert一列記錄含換行符，而實際上在binlog文件中，是使用了 \x0a 替換了 換行操作，所以在回滾數據的過程中，需要對轉義字符做處理。
這里注意一個地方，039的轉義字符是沒有在函數 esc_code 中統一處理，而是單獨做另外處理。
轉移字符表相見下圖：
2.6 timestamp數據類型處理
timestamp實際在數據庫中的存儲值是 INT類型，需要使用 函數 from_unixtime轉換。
建立測試表格tbtest，只有一列timestamp的列，存儲值后查看binlog的內容，具體截圖如下：
在處理行記錄的時候，要對timestamp的value做處理，添加from_unixtime函數轉換。
2.7 負數值處理
這個一開始寫代碼的時候，并沒有考慮到。大量測試的過程中發現，所有整型的數據類型，在存儲負數的時候，都會存入一個最大范圍值。binlog在處理這塊的機制有些不是很了解。測試如下：
所以當遇到INT的各種數據類型并且VALUE為負數的時候，需要把 這個范圍值去除，才能執行執行undo_sql。
2.8 單個事務行記錄總SQL超過max_allowed_package處理
分析binlog后存儲兩種sql類型，一種是行記錄的修改SQL，即 dml_sql；一種是 行記錄的回滾sql，即 undo_sql。從代碼可知，存儲這兩個sql的列是longtext，最大可存儲4G的內容。但是 MySQL中單個會話的包大小是有限制的，限制的參數為 max_allowed_packet，默認大小為 4Mb，最大為1G，所以這個腳本使用前，請手動設置 存儲binlog file的數據庫實例以及線上的數據庫實例這個參數：
set global max_allowed_packet = 1073741824; #記得后續修改回來
萬一操作了呢？那么回滾只能分段來回滾，先回滾到 這個大事務，然后單獨執行這個大事務，緊接著繼續回滾，這部分不能使用pymysql嗲用source 文件執行，所以只能手動做這個操作。 求高能人士修改這個邏輯代碼！！！
2.9 針對性回滾
假設誤操作的沒有明確的時間點，只有一個區間，而這個區間還有其他的表格操作，那么這個時候，需要在分析binlog文件的時候，添加--database選項，先帥選到同一個數據庫中binlog文件中。
這里的處理是將這段區間的dml_sql跟undo_sql都存儲到數據庫表格中，然后再刪除不需要回滾的事務，剩余需要回滾的事務。再執行回滾操作。
3 使用說明
3.1 參數說明
這個腳本的參數稍微多些，可以 --help 查看具體說明。
本人喜歡用各種顏色來分類參數（blingbling五顏六色，看著多有趣多精神），所以，按顏色來說明這些參數。
黃色區域：這6個參數，提供的是 分析并存儲binlog file的相關值，說明存儲分析結果的數據庫的鏈接方式、binlog文件的位置以及存儲結果的表格名字；
藍色區域：這4個參數，提供 與線上數據庫表結構一致的DB實例連接方式，僅需跟線上一模一樣的表結構，不一定需要是主從庫；
綠色區域：最最重要的選項 -a，0代表僅分析binlog文件，1代表僅執行回滾操作，必須先執行0才可以執行1；
紫色區域：舉例說明。
3.2 應用場景說明
全庫回滾某段時間
需要回滾某個時間段的所有SQL操作，回滾到某一個時間點
這種情況下呢，大多數是使用備份文件+binlog解決
但是這個腳本也可以滿足，但請勿直接在線上操作，先 -a=0，看下分析結果，是否符合，符合的話，停掉某個從庫，再在從庫上執行，最后開發業務接入檢查是否恢復到指定時間點，數據是否正常。
某段時間某些表格回滾某些操作
比如，開發提交了一個批量更新腳本，各個測試層面驗證沒有問題，提交線上執行，但是執行后，發現有個業務漏測試，導致某些字段更新后影響到其他業務，現在需要緊急 把被批量更新的表格回滾到原先的行記錄
這個并不能單純從技術角度來處理，要綜合考慮
這種情況下，如何回顧tab A表格的修改操作呢？
個人覺得，這種方式比較行得通，dump tabA表格的數據到測試環境，然后再分析 binlog file 從11點-12點的undo sql，接著在測試環境回滾該表格到11點這個時刻，緊接著，由開發跟業務對比測試環境11點的數據跟線上現有的數據中，看下是哪些行哪些列需要在線上進行回滾，哪些是不需要的，然后開發再提交SQL腳本，再在線上執行。其實，這里邊，DBA僅提供一個角色，就是把 表格 tab A 在一個新的環境上，回滾到某個時間點，但是不提供直接線上回滾SQL的處理。
回滾某個/些SQL
這種情況比較常見，某個update某個delete缺少where條件或者where條件執行錯誤
這種情況下，找到對應的事務，執行回滾即可，回滾流程請參考上面一說，對的，我就是這么膽小怕事
3.3 測試案例
3.3.1 全庫回滾某段時間
假設需要回滾9點10分到9點15分間數據庫的所有操作：
準備測試環境實例存儲分析后的數據
測試環境修改set global max_allowed_packet = 1073741824
mysqlbinlog分析binlog文件
python腳本分析文件，action=0
線上測試環境修改set global max_allowed_packet = 1073741824
回滾數據，action=1
線上測試環境修改set global max_allowed_packet = 4194304
1 --測試環境（請安裝pymysql）：IP: 192.168.9.242，PORT：3310 ,數據庫：flashback，表格：tbevent 2 --具有線上表結構的db：IP：192.168.9.243 PORT:3310 3 4 5 mysql> show global variables like 'max_allowed_packet'; 6 +--------------------+----------+ 7 Variable_name Value 8 +--------------------+----------+ 9 max_allowed_packet 16777216 10 +--------------------+----------+11 1 row in set (0.00 sec)12 13 mysql> set global max_allowed_packet = 1073741824;14 Query OK, 0 rows affected (0.00 sec)15 16 [root@sutest244 ~]# mysqlbinlog --start-datetime='2017-06-19 09:00:00' --stop-datetime='2017-06-19 10:00:00' --base64-output=decode-rows -v ~/data/mysql/data/mysql-bin.007335 > /tmp/binlog.log17 18 [root@sutest242 pycharm]# python su_flashback.py -h=127.0.0.1 -P=3310 -u=root -p=**** -f=/tmp/binlog.log -t=flashback.tbevent -oh=192.168.9.244 -oP=3310 -u=root -op=**** -a=019 2017-06-19 10:59:39,041 INFO begin to assign values to parameters20 2017-06-19 10:59:39,041 INFO assign values to parameters is done:host=127.0.0.1,user=root,password=***,port=3310,fpath=/tmp/binlog.log,tbevent=flashback.tbevent21 2017-06-19 10:59:39,049 INFO MySQL which userd to store binlog event connection is ok22 2017-06-19 10:59:39,050 INFO assign values to online mysql parameters is done:host=192.168.9.244,user=,password=***,port=331023 2017-06-19 10:59:39,054 INFO MySQL which userd to analyse online table schema connection is ok24 2017-06-19 10:59:39,054 INFO MySQL connection is ok25 2017-06-19 10:59:39,055 INFO creating table flashback.tbevent to store binlog event26 2017-06-19 10:59:39,058 INFO created table flashback.tbevent 27 2017-06-19 10:59:39,060 INFO begining to analyze the binlog file ,this may be take a long time !!!28 2017-06-19 10:59:39,061 INFO analyzing...29 2017-06-19 11:49:53,781 INFO finished to analyze the binlog file !!!30 2017-06-19 11:49:53,782 INFO release all db connections31 2017-06-19 11:49:53,782 INFO All done,check the flashback.tbevent which stored binlog event on host 127.0.0.1 , port 3310 32 33 34 [root@sutest242 pycharm]# python su_flashback.py -h=127.0.0.1 -P=3310 -u=root -p=**** -f=/tmp/binlog.log -t=flashback.tbevent -oh=192.168.9.244 -oP=3310 -u=root -op=**** -a=135 2017-06-19 16:30:20,633 INFO begin to assign values to parameters36 2017-06-19 16:30:20,635 INFO assign values to parameters is done:host=127.0.0.1,user=root,password=***,port=3310,fpath=/tmp/binlog.log,tbevent=flashback.tbevent37 2017-06-19 16:30:20,865 INFO MySQL which userd to store binlog event connection is ok38 2017-06-19 16:30:20,866 INFO assign values to online mysql parameters is done:host=192.168.9.244,user=,password=***,port=331039 2017-06-19 16:30:20,871 INFO MySQL which userd to analyse online table schema connection is ok40 2017-06-19 16:30:20,871 INFO MySQL connection is ok41 2017-06-19 16:30:21,243 INFO There has 347868 transactions ,need 35 batchs ,each batche doing 10000 transactions 42 2017-06-19 16:30:21,243 INFO doing batch : 1 43 2017-06-19 16:31:01,182 INFO doing batch : 2 44 2017-06-19 16:31:16,909 INFO doing batch : 3 45 -------省空間忽略不截圖--------------46 2017-06-19 16:41:11,287 INFO doing batch : 34 47 2017-06-19 16:41:25,577 INFO doing batch : 35 48 2017-06-19 16:41:44,629 INFO release all db connections49 2017-06-19 16:41:44,630 INFO All done,check the flashback.tbevent which stored binlog event on host 127.0.0.1 , port 3310
3.3.2 某段時間某些表格回滾某些操作
準備測試環境實例存儲分析后的數據
測試環境修改set global max_allowed_packet = 1073741824
mysqlbinlog分析binlog文件
python腳本分析文件，action=0
分析帥選需要的事務，rename表格
dump 對應的表格到測試環境
回滾數據，action=1
提交給開發業務對比數據
3.3.3 回滾某個/些SQL
準備測試環境實例存儲分析后的數據
測試環境修改set global max_allowed_packet = 1073741824
mysqlbinlog分析binlog文件
python腳本分析文件，action=0
分析帥選需要的事務，rename表格
dump 對應的表格到測試環境
回滾數據，action=1
提交給開發業務對比數據
4 python腳本
腳本會不定期修復bug，若是感興趣，可以往github下載： 中的 mysql_xinysu_flashback 。
1 # -*- coding: utf-8 -*- 2 __author__ = 'xinysu' 3 __date__ = '2017/6/15 10:30' 4 5 6 7 import re 8 import os 9 import sys 10 import datetime 11 import time 12 import logging 13 import importlib 14 importlib.reload(logging) 15 logging.basicConfig(level=logging.DEBUG,format='%(asctime)s %(levelname)s %(message)s ') 16 17 import pymysql 18 from pymysql.cursors import DictCursor 19 20 usage='''\nusage: python [script's path] [option] 21 ALL options need to assign: 22 \033[1;33;40m 23 -h : host, the database host，which database will store the results after analysis 24 -u : user, the db user 25 -p : password, the db user's password 26 -P : port, the db port 27 28 -f : file path, the binlog file 29 -t : table name, the table name to store the results after analysis , {dbname}.{tbname}, 30 when you want to store in `test` db and the table name is `tbevent`,then this parameter
31 is test.tbevent 32 \033[1;34;40m 33 -oh : online host, the database host，which database have the online table schema 34 -ou : online user, the db user 35 -op : online password, the db user's password 36 -oP : online port, the db port 37 \033[1;32;40m 38 -a : action,
39 0 just analyse the binlog file ,and store sql in table;
40 1 after execute self.dotype=0, execute the undo_sql in the table 41 \033[0m
42 --help: help document 43 \033[1;35;40m 44 Example: 45 analysize binlog: 46 python su_flashback.py -h=127.0.0.1 -P=3310 -u=root -p=*** -f=/tmp/binlog.log -t=flashback.tbevent
47 -oh=192.168.9.244 -oP=3310 -u=root -op=***
48 -a=0 49 50 flash back: 51 python su_flashback.py -h=127.0.0.1 -P=3310 -u=root -p=*** -f=/tmp/binlog.log -t=flashback.tbevent
52 -oh=192.168.9.244 -oP=3310 -u=root -op=***
53 -a=1 54 \033[0m
55 ''' 56 57 class flashback: 58 def __init__(self): 59 self.host='' 60 self.user='' 61 self.password='' 62 self.port='3306' 63 self.fpath='' 64 self.tbevent='' 65 66 self.on_host='' 67 self.on_user='' 68 self.on_password='' 69 self.on_port='3306' 70 71 self.action=0 # 0 just analyse the binlog file ,and store sql in table;1 after execute self.dotype=0, execute the undo_sql in the table 72 73 self._get_db() # 從輸入參數獲取連接數據庫的相關參數值 74 75 # 連接數據庫，該數據庫是用來存儲binlog文件分析后的內容 76 logging.info('assign values to parameters is done:host={},user={},password=***,port={},fpath={},tbevent={}'.format(self.host,self.user,self.port,self.fpath,self.tbevent)) 77 self.mysqlconn = pymysql.connect(host=self.host, user=self.user, password=self.password, port=self.port,charset='utf8') 78 self.cur = self.mysqlconn.cursor(cursor=DictCursor) 79 logging.info('MySQL which userd to store binlog event connection is ok') 80 81 # 連接數據庫，該數據庫的表結構必須跟binlogfile基于對數據庫表結構一致 82 # 該數據庫用于提供 binlog file 文件中涉及到表結構分析 83 logging.info('assign values to online mysql parameters is done:host={},user={},password=***,port={}'.format(self.on_host, self.on_user, self.on_port)) 84 self.on_mysqlconn = pymysql.connect(host=self.on_host, user=self.on_user, password=self.on_password, port=self.on_port,charset='utf8') 85 self.on_cur = self.on_mysqlconn.cursor(cursor=DictCursor) 86 logging.info('MySQL which userd to analyse online table schema connection is ok') 87 88 logging.info('\033[33mMySQL connection is ok\033[0m') 89 90 self.dml_sql='' 91 self.undo_sql='' 92 93 self.tbfield_where = [] 94 self.tbfield_set = [] 95 96 self.begin_time='' 97 self.db_name='' 98 self.tb_name='' 99 self.end_time=''100 self.end_pos=''101 self.sqltype=0102 103 #_get_db用于獲取執行命令的輸入參數104 def _get_db(self):105 logging.info('begin to assign values to parameters')106 if len(sys.argv) == 1:107 print(usage)108 sys.exit(1)109 elif sys.argv[1] == '--help':110 print(usage)111 sys.exit()112 elif len(sys.argv) > 2:113 for i in sys.argv[1:]:114 _argv = i.split('=')115 if _argv[0] == '-h':116 self.host = _argv[1]117 elif _argv[0] == '-u':118 self.user = _argv[1]119 elif _argv[0] == '-P':120 self.port = int(_argv[1])121 elif _argv[0] == '-f':122 self.fpath = _argv[1]123 elif _argv[0] == '-t':124 self.tbevent = _argv[1]125 elif _argv[0] == '-p':126 self.password = _argv[1]127 128 elif _argv[0] == '-oh':129 self.on_host = _argv[1]130 elif _argv[0] == '-ou':131 self.on_user = _argv[1]132 elif _argv[0] == '-oP':133 self.on_port = int(_argv[1])134 elif _argv[0] == '-op':135 self.on_password = _argv[1]136 137 elif _argv[0] == '-a':138 self.action = _argv[1]139 140 else:141 print(usage)142 143 #創建表格，用于存儲分析后的BINLOG內容144 def create_tab(self):145 logging.info('creating table {} to store binlog event'.format(self.tbevent))146 create_tb_sql ='''147 CREATE TABLE IF NOT EXISTS {}(148 auto_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,149 binlog_name VARCHAR(100) NOT NULL COMMENT 'the binlog file path and name',150 dml_start_time DATETIME NOT NULL COMMENT 'when to start this transaction ',151 dml_end_time DATETIME NOT NULL COMMENT 'when to finish this transaction ',152 end_log_pos BIGINT NOT NULL COMMENT 'the log position for finish this transaction',153 db_name VARCHAR(100) NOT NULL COMMENT 'which database happened this transaction ',154 table_name VARCHAR(200) NOT NULL COMMENT 'which table happened this transaction ',155 sqltype INT NOT NULL COMMENT '1 is insert,2 is update,3 is delete',156 dml_sql LONGTEXT NULL COMMENT 'what sql excuted',157 undo_sql LONGTEXT NULL COMMENT 'rollback sql, this sql used for flashback',158 PRIMARY KEY (auto_id),159 INDEX sqltype(sqltype),160 INDEX dml_start_time (dml_start_time),161 INDEX dml_end_time (dml_end_time),162 INDEX end_log_pos (end_log_pos),163 INDEX db_name (db_name),164 INDEX table_name (table_name)165 )166 COLLATE='utf8_general_ci' ENGINE=InnoDB;167 TRUNCATE TABLE {};168 169 '''.format(self.tbevent,self.tbevent)170 self.cur.execute(create_tb_sql)171 logging.info('created table {} '.format(self.tbevent))172 173 #獲取表格的列順序對應的列名，并處理where set的時候，列與列之間的連接字符串是逗號還是 and174 def tbschema(self,dbname,tbname):175 self.tbfield_where = []176 self.tbfield_set = []177 178 sql_tb='desc {}.{}'.format(self.db_name,self.tb_name)179 180 self.on_cur.execute(sql_tb)181 tbcol=self.on_cur.fetchall()182 183 i = 0184 for l in tbcol:185 #self.tbfield.append(l['Field'])186 if i==0:187 self.tbfield_where.append('`'+l['Field']+'`')188 self.tbfield_set.append('`'+l['Field']+'`')189 i+=1190 else:191 self.tbfield_where.append('/*where*/ and /*where*/' + '`'+l['Field']+'`')192 self.tbfield_set.append( '/*set*/ , /*set*/'+'`'+l['Field']+'`' )193 194 # 一個事務記錄一行，若binlog file中的行記錄包含 Table_map，則為事務的開始記錄195 def rowrecord(self,bl_line):196 try:197 if bl_line.find('Table_map:') != -1:198 l = bl_line.index('server')199 m = bl_line.index('end_log_pos')200 n = bl_line.index('Table_map')201 begin_time = bl_line[:l:].rstrip(' ').replace('#', '20')202 203 self.begin_time = begin_time[0:4] + '-' + begin_time[4:6] + '-' + begin_time[6:]204 self.db_name = bl_line[n::].split(' ')[1].replace('`', '').split('.')[0]205 self.tb_name = bl_line[n::].split(' ')[1].replace('`', '').split('.')[1]206 207 self.tbschema(self.db_name,self.tb_name)208 except Exception:209 return 'funtion rowrecord error'210 211 def dml_tran(self,bl_line):212 try:213 214 215 if bl_line.find('Xid =') != -1:216 217 l = bl_line.index('server')218 m = bl_line.index('end_log_pos')219 end_time = bl_line[:l:].rstrip(' ').replace('#', '20')220 self.end_time = end_time[0:4] + '-' + end_time[4:6] + '-' + end_time[6:]221 self.end_pos = int(bl_line[m::].split(' ')[1])222 223 224 225 self.undo_sql = self.dml_sql.replace(' INSERT INTO', ';DELETE FROM_su').replace(' UPDATE ',';UPDATE').replace(' DELETE FROM', ';INSERT INTO').replace(';DELETE FROM_su', ';DELETE FROM').replace('WHERE', 'WHERE_marksu').replace('SET', 'WHERE').replace('WHERE_marksu', 'SET').replace('/*set*/ , /*set*/', ' and ').replace('/*where*/ and /*where*/',' , ')226 self.dml_sql=self.dml_sql.replace('/*set*/ , /*set*/', ' , ').replace('/*where*/ and /*where*/',' and ')227 228 if self.dml_sql.startswith(' INSERT INTO '):229 self.sqltype=1230 elif self.dml_sql.startswith(' UPDATE '):231 self.sqltype=2232 elif self.dml_sql.startswith(' DELETE '):233 self.sqltype=3234 235 record_sql = ''236 undosql_desc = ''237 238 #同個事務內部的行記錄修改SQL，反序存儲239 for l in self.undo_sql.splitlines():240 if l.startswith(' ;UPDATE') or l.startswith(' ;INSERT') or l.startswith(' ;DELETE'):241 undosql_desc = record_sql + undosql_desc242 record_sql = ''243 record_sql = record_sql + l244 else:245 record_sql = record_sql + l246 247 self.undo_sql = record_sql + undosql_desc248 self.undo_sql = self.undo_sql.lstrip()[1:]+';'249 250 #處理非空格的空白特殊字符251 self.dml_sql = self.esc_code(self.dml_sql)252 self.undo_sql = self.esc_code(self.undo_sql)253 254 #單獨處理 轉移字符: \'255 self.dml_sql = self.dml_sql.replace("'", "''").replace('\\x27',"''''") # + ';'256 self.undo_sql = self.undo_sql.replace("'", "''").replace('\\x27',"''''") # + ';'257 258 if len(self.dml_sql)>500000000:259 with open('/tmp/flashback_undosql/'+str(self.end_pos)+'.sql', 'w') as w_f:260 w_f.write('begin;' + '\n')261 w_f.write(self.undo_sql)262 w_f.write('commit;' + '\n')263 self.dml_sql=''264 self.undo_sql='/tmp/flashback_undosql/'+str(self.end_pos)+'.sql'265 logging.info("the size of this transaction is more than 500Mb ,the file location : {}".format(self.undo_file))266 267 insert_sql = "INSERT INTO {}(binlog_name,dml_start_time,dml_end_time,end_log_pos,db_name,table_name,sqltype,dml_sql,undo_sql) select '{}','{}','{}','{}','{}','{}',{},'{}','{}'".format(268 self.tbevent, self.fpath, self.begin_time, self.end_time, self.end_pos,269 self.db_name, self.tb_name, self.sqltype, self.dml_sql, self.undo_sql)270 271 self.cur.execute(insert_sql)272 self.mysqlconn.commit()273 274 self.dml_sql = ''275 self.undo_sql = ''276 except Exception:277 print( 'funtion dml_tran error')278 279 280 def analyse_binlog(self):281 try:282 sqlcomma=0283 self.create_tab()284 285 with open(self.fpath,'r') as binlog_file:286 logging.info('\033[36mbegining to analyze the binlog file ,this may be take a long time !!!\033[0m')287 logging.info('\033[36manalyzing...\033[0m')288 for bline in binlog_file:289 if bline.find('Table_map:') != -1:290 self.rowrecord(bline)291 bline=''292 elif bline.rstrip()=='### SET':293 bline = bline[3:]294 sqlcomma=1295 elif bline.rstrip()=='### WHERE':296 bline = bline[3:]297 sqlcomma = 2298 elif bline.startswith('### @'):299 len_f=len('### @')300 i=bline[len_f:].split('=')[0]301 302 #處理timestamp類型303 if bline[8+len(i):].split(' ')[2] == 'TIMESTAMP(0)':304 stop_pos = bline.find(' /* TIMESTAMP(0) meta=')305 bline = bline.split('=')[0] + '=from_unixtime(' + bline[:stop_pos].split('=')[1] + ')'306 307 #處理負數存儲方式308 if bline.split('=')[1].startswith('-'):309 stop_pos = bline.find(' /* TIMESTAMP(0) meta=')310 bline = bline.split('=')[0] + '=' + bline.split('=')[1].split(' ')[0]+'\n'311 312 if sqlcomma==1:313 bline = self.tbfield_set[int(i) - 1]+bline[(len_f+len(i)):]314 elif sqlcomma==2:315 bline = self.tbfield_where[int(i) - 1] + bline[(len_f+len(i)):]316 317 elif bline.startswith('### DELETE') or bline.startswith('### INSERT') or bline.startswith('### UPDATE'):318 bline = bline[3:]319 320 elif bline.find('Xid =') != -1:321 self.dml_tran(bline)322 bline=''323 else:324 bline = ''325 326 if bline.rstrip('\n') != '':327 self.dml_sql = self.dml_sql + bline + ' '328 except Exception:329 return 'function do error'330 331 def esc_code(self,sql):332 esc={333 '\\x07':'\a','\\x08':'\b','\\x0c':'\f','\\x0a':'\n','\\x0d':'\r','\\x09':'\t','\\x0b':'\v','\\x5c':'\\',334 #'\\x27':'\'',335 '\\x22':'\"','\\x3f':'\?','\\x00':'\0'336 }337 338 for k,v in esc.items():339 sql=sql.replace(k,v)340 return sql341 342 def binlogdesc(self):343 344 countsql='select sqltype , count(*) numbers from {} group by sqltype order by sqltype '.format(self.tbevent)345 print(countsql)346 self.cur.execute(countsql)347 count_row=self.cur.fetchall()348 349 update_count=0350 insert_couont=0351 delete_count=0352 for row in count_row:353 if row['sqltype']==1:354 insert_couont=row['numbers']355 elif row['sqltype']==2:356 update_count=row['numbers']357 elif row['sqltype']==3:358 delete_count=row['numbers']359 logging.info('\033[1;35mTotal transactions number is {}: {} inserts, {} updates, {} deletes !\033[0m(all number is accurate, the other is approximate value) \033[0m'.format(insert_couont+update_count+delete_count,insert_couont,update_count,delete_count))360 361 def undosql(self,number):362 #這里會有幾個問題：363 #1 如果一共有幾十萬甚至更多的事務操作，那么這個python腳本，極為占用內存，有可能執行錯誤；364 #2 如果單個事務中，涉及修改的行數高達幾十萬行，其binlog file 達好幾G，這里也會有內存損耗問題；365 #所以，針對第一點，這里考慮對超多事務進行一個分批執行處理，每個批次處理number個事務，避免一次性把所有事務放到python中；但是第2點，目前暫未處理366 367 tran_num=1368 id=0369 370 tran_num_sql="select count(*) table_rows from {}".format(self.tbevent)371 372 self.cur.execute(tran_num_sql)373 tran_rows=self.cur.fetchall()374 375 for num in tran_rows:376 tran_num=num['table_rows']377 378 logging.info('\033[32mThere has {} transactions ,need {} batchs ,each batche doing {} transactions \033[0m'.format(tran_num,int(tran_num/number)+1,number))379 380 while id<=tran_num:381 logging.info('doing batch : {} '.format(int(id/number)+1))382 undo_sql='select auto_id,undo_sql from {} where auto_id > {} and auto_id <= {} order by auto_id desc;'.format(self.tbevent,tran_num-(id+number),tran_num-id)383 self.cur.execute(undo_sql)384 385 undo_rows=self.cur.fetchall()386 f_sql=''387 388 for u_row in undo_rows:389 try:390 self.on_cur.execute(u_row['undo_sql'])391 self.on_mysqlconn.commit()392 except Exception:393 print('auto_id:',u_row['auto_id'])394 id+=number395 396 397 def undo_file(self,number):398 # 也可以選擇私用undo_file將undo_sql導入到文件中,然后再source399 400 tran_num=1401 id=0402 403 tran_num_sql="select count(*) table_rows from {}".format(self.tbevent)404 405 self.cur.execute(tran_num_sql)406 tran_rows=self.cur.fetchall()407 408 for num in tran_rows:409 tran_num=num['table_rows']410 411 logging.info('copy undo_sql to undo file on : /tmp/flashback_undosql/undo_file_flashback.sql')412 logging.info('\033[32mThere has {} transactions ,need {} batchs to copy ,each batche doing {} transactions \033[0m'.format(tran_num,int(tran_num/number)+1,number))413 414 with open('/tmp/flashback_undosql/undo_file_flashback.sql', 'w') as w_f:415 while id<=tran_num:416 logging.info('doing batch : {} '.format(int(id/number)+1))417 undo_sql='select auto_id,undo_sql from {} where auto_id > {} and auto_id <= {} order by auto_id desc;'.format(self.tbevent,tran_num-(id+number),tran_num-id)418 self.cur.execute(undo_sql)419 420 undo_rows=self.cur.fetchall()421 for u_row in undo_rows:422 try:423 w_f.write('begin;' + '\n')424 w_f.write('# auto_id'+str(u_row['auto_id']) + '\n')425 w_f.write(u_row['undo_sql'] + '\n')426 w_f.write('commit;' + '\n')427 except Exception:428 print('auto_id',u_row['auto_id'])429 #time.sleep(2)430 id+=number431 432 def do(self):433 if self.action=='0':434 self.analyse_binlog()435 logging.info('\033[36mfinished to analyze the binlog file !!!\033[0m')436 #self.binlogdesc()437 elif self.action=='1':438 self.undosql(10000)439 440 def closeconn(self):441 self.cur.close()442 self.on_cur.close()443 logging.info('release all db connections')444 logging.info('\033[33mAll done,check the {} which stored binlog event on host {} , port {} \033[0m'.format(self.tbevent,self.host,self.port))445 446 def main():447 p = flashback()448 p.do()449 p.closeconn()450 451 if __name__ == "__main__":452 main()以上就是mysql基于binlog回滾工具實例詳解的詳細內容，更多請關注php中文網其它相關文章！
學習教程快速掌握從入門到精通的SQL知識。
推薦資訊 總人氣榜
最新教程 本月人氣
- 1優酷軍師聯盟卡是什么？優酷軍師聯盟卡收集圖文說明教程
- 2終極3國2017百度云資源(更新至第106集)
- 3愛奇藝vip免費試用 愛奇藝vip帳號共享(2017.7.7)
- 4如何用愛奇藝下載電影？愛奇藝下載視頻圖文說明教程
- 5QQ音樂如何發彈幕？QQ音樂彈幕使用圖文說明教程
- 6我是僵尸3百度云全集資源
- 7尋人大師第二季百度云全集資源
- 8暴風影音如何加速播放？暴風影音加速播放圖文說明教程_暴風影音_下載之家
- 9免費3組騰訊視頻vip賬號共享(2017.6.29)
- 10搜狐視頻會員賬號共享2017.6.28
- 11劉亞仁相關電視劇電影綜藝百度云全集
- 12人生若如初相見百度云全集資源
- 1支付寶不能交水電費了嗎 支付寶為什么不能交水電費了
- 2人人車怎么收費 人人車收費規范
- 3平板電腦wifi無線網絡設置圖文說明教程【圖文詳細說明】
- 4ios9 bug隱藏無用應用方法
- 5獵豹瀏覽器搶票不受12306新版驗證碼影響
- 63星版iphone6s與臺積電版iphone6s續航哪一個好
- 7百度專車怎么收費 百度專車收費規范介紹
- 8華為pay與微信支付哪一個好 微信支付與huawei pay比較區別
- 9酷狗怎么設置伴唱并錄音 酷狗伴唱設置圖文說明教程
- 10中興grand x max+怎么？中興grand x max+設置評測
- 11小皮助手安裝失敗怎么辦 小皮助手安卓模擬器安裝失敗處理方法
- 12滴滴打屁怎么用 滴滴打屁app使用圖文說明教程
