MySQL刪除數(shù)據(jù)之后回滾數(shù)據(jù)
場景
1. 用Navicat數(shù)據(jù)遷移功能
1. 數(shù)據(jù)傳輸產生數(shù)據(jù)丟失
| 主要執(zhí)行的信息 | |
|---|---|
Data Transfer start |
數(shù)據(jù)傳送速率 |
Getting tables |
準備開始 |
Get table data for TableName |
獲取TableName的表數(shù)據(jù) |
Drop table: TableName |
把整張表干掉 |
Create table: TableName |
創(chuàng)建表 |
Begin transaction on target server |
在目標服務器上開始事務 |
Start transfer data for table: TableName |
開始為表傳輸數(shù)據(jù): TableName
|
End transaction on target server |
在目標服務器上結束事務 |
Finished successfully |
成功完成了 |
由Navicat的執(zhí)行過程可以看到, Navicat的數(shù)據(jù)傳輸操作是先執(zhí)行Drop操作, 將整張表干掉
產生的后果
- 會導致整張表被刪除, 沒有記錄刪除數(shù)據(jù)的SQL, binlog只會記錄Drop這一句SQL, 此時binlog是沒有辦法恢復數(shù)據(jù)的
- 你們公司只有定時開啟備份數(shù)據(jù)庫功能才可以回滾
注意: 在公司開發(fā)中一定要禁用Navicat的數(shù)據(jù)傳輸, 這樣會導致數(shù)據(jù)不可恢復, binlog也救不了你
注意: 在公司開發(fā)中一定要禁用Navicat的數(shù)據(jù)傳輸, 這樣會導致數(shù)據(jù)不可恢復, binlog也救不了你
注意: 在公司開發(fā)中一定要禁用Navicat的數(shù)據(jù)傳輸, 這樣會導致數(shù)據(jù)不可恢復, binlog也救不了你
2.不小心手動刪除了數(shù)據(jù)怎么辦
1. 如果你們公司電腦開啟了binlog, 那么不要怕, 可以直接利用binlog進行回滾
相關操作
show binary logs |
獲取binlog文件列表 |
show binlog events |
獲取第一個binlog的內容 |
show binlog events in 'mysql-binName' |
查看指定binlog文件的內容 |
show master status\G |
查看當前正在寫入的binlog文件 |
要查詢某個時間段內某個數(shù)據(jù)庫內的操作日志, 將SQL輸入到某個文件中
mysqlbinlog --no-defaults --database=#數(shù)據(jù)庫名# --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" #binlog文件名# > #輸出的文件名稱及地址#
如果是本地查詢命令
mysqlbinlog --no-defaults --database=#數(shù)據(jù)庫名# --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" #binlog文件名#
如果需要過濾
mysqlbinlog --no-defaults --database=#數(shù)據(jù)庫名# #binlog文件名# |grep insert/update/delete |more
| 參數(shù) | 含義 |
|---|---|
| start-datetime | 開始時間 |
| stop-datetime | 結束時間 |
| database | 數(shù)據(jù)庫名稱 |
恢復操作
https://github.com/danfengcao/binlog2sql
訪問這個地址, 將代碼拉下來之后, 有詳細的文檔, 可以查看
環(huán)境
| Python | 2.7, 3.4+ |
|---|---|
| MySQL | 5.6, 5.7 |
| PIP | 自己安裝 |
python -m pip install --upgrade pip #安裝pip
pip install pymysql #安裝pymysql
MYSQL Server必須設置以下參數(shù)開啟binlog
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
賬戶需要的最小權限
| 權限 | |
|---|---|
select |
需要讀取server端information_schema.COLUMNS表，獲取表結構的元信息，拼接成可視化的sql語句 |
super/replication client |
兩個權限都可以，需要執(zhí)行'SHOW MASTER STATUS', 獲取server端的binlog列表 |
replication slave |
通過BINLOG_DUMP協(xié)議獲取binlog內容的權限 |
解析出標準SQL
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dtest -t test3 test4 --start-file=#mysql-bin文件名稱# > 輸出文件地址
解析出回滾SQL
python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -proot -dtest -ttest3 --start-file=#mysql-bin文件名稱# --start-position=763 --stop-position=1147 > 輸出文件地址
參數(shù)
| mysql連接配置 | |
|---|---|
-u |
連接ip |
-P |
端口號 |
-u |
用戶名 |
-p |
密碼 |
start-position |
起始節(jié)點 |
stop-position |
結束節(jié)點 |
| 解析模式 | |
|---|---|
--stop-never |
持續(xù)解析binlog。可選。默認False，同步至執(zhí)行命令時最新的binlog位置。 |
-K, --no-primary-key |
對INSERT語句去除主鍵?？蛇x。默認False |
-B, --flashback |
生成回滾SQL，可解析大文件，不受內存限制?？蛇x。默認False。與stop-never或no-primary-key不能同時添加。 |
--back-interval |
-B模式下，每打印一千行回滾SQL，加一句SLEEP多少秒，如不想加SLEEP，請設為0?？蛇x。默認1.0。 |
| 解析范圍控制 | |
|---|---|
--start-file |
起始解析文件，只需文件名，無需全路徑 。必須。 |
--start-position/--start-pos |
起始解析位置。可選。默認為start-file的起始位置。 |
--stop-file/--end-file |
終止解析文件。可選。默認為start-file同一個文件。若解析模式為stop-never，此選項失效。 |
--stop-position/--end-pos |
終止解析位置?？蛇x。默認為stop-file的最末位置；若解析模式為stop-never，此選項失效。 |
--start-datetime |
起始解析時間，格式'%Y-%m-%d %H:%M:%S'。可選。默認不過濾。 |
--stop-datetime |
終止解析時間，格式'%Y-%m-%d %H:%M:%S'。可選。默認不過濾。 |
| 對象過濾 | |
|---|---|
-d, --databases |
只解析目標db的sql，多個庫用空格隔開，如-d db1 db2?？蛇x。默認為空。 |
-t, --tables |
只解析目標table的sql，多張表用空格隔開，如-t tbl1 tbl2?？蛇x。默認為空。 |
--only-dml |
只解析dml，忽略ddl。可選。默認False。 |
--sql-type |
只解析指定類型，支持INSERT, UPDATE, DELETE。多個類型用空格隔開，如--sql-type INSERT DELETE?？蛇x。默認為增刪改都解析。用了此參數(shù)但沒填任何類型，則三者都不解析。 |
限制(對比mysqlbinlog)
- mysql server必須開啟，離線模式下不能解析
- 參數(shù) binlog_row_image 必須為FULL，暫不支持MINIMAL
- 解析速度不如mysqlbinlog
優(yōu)點(對比mysqlbinlog)
- 純Python開發(fā)，安裝與使用都很簡單
- 自帶flashback、no-primary-key解析模式，無需再裝補丁
- 解析為標準SQL，方便理解、篩選
- 代碼容易改造，可以支持更多個性化解析
