主頁(yè) > 知識(shí)庫(kù) > mysql如何利用binlog進(jìn)行數(shù)據(jù)恢復(fù)詳解

mysql如何利用binlog進(jìn)行數(shù)據(jù)恢復(fù)詳解

熱門標(biāo)簽:電銷機(jī)器人針對(duì)的 高德地圖標(biāo)注模式 外呼系統(tǒng)防封號(hào)違法嗎 外呼系統(tǒng)服務(wù) 400電話辦理都選易號(hào)網(wǎng) 湘潭電銷機(jī)器人咨詢電話 如何在高德地圖標(biāo)注新地址 高德地圖標(biāo)注中心個(gè)人注冊(cè) 寶應(yīng)電信400電話辦理費(fèi)用

前言

最近線上誤操作了一個(gè)數(shù)據(jù),由于是直接修改的數(shù)據(jù)庫(kù),所有唯一的恢復(fù)方式就在mysql的binlog。binlog使用的是ROW模式,即受影響的每條記錄都會(huì)生成一個(gè)sql。同時(shí)利用了binlog2sql項(xiàng)目。

MySQL Binary Log也就是常說(shuō)的bin-log, ,是mysql執(zhí)行改動(dòng)產(chǎn)生的二進(jìn)制日志文件,其主要作用有兩個(gè):

* 數(shù)據(jù)回復(fù)

* 主從數(shù)據(jù)庫(kù)。用于slave端執(zhí)行增刪改,保持與master同步。

binlog基本配置和格式

binlog基本配置

binlog需要在mysql的配置文件的mysqld節(jié)點(diǎn)中進(jìn)行配置:

# 日志中的Serverid
server-id = 1
# 日志路徑
log_bin  = /var/log/mysql/mysql-bin.log
# 保存幾天的日志
expire_logs_days = 10
# 每個(gè)binlog的大小
max_binlog_size = 1000M
#binlgo模式
binlog_format=ROW
# 默認(rèn)是所有記錄,可以配置哪些需要記錄,哪些不記錄
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

查看binlog狀態(tài)

  • SHOW BINARY LOGS; 查看binlog文件
  • SHOW VARIABLES LIKE '%log_bin%' 查看日志狀態(tài)
  • SHOW MASTER STATUS 查看日志文件位置

binlog的三種格式

1.ROW

針對(duì)行記錄日志,每行修改產(chǎn)生一條記錄。

優(yōu)點(diǎn):上下文信息比較全,恢復(fù)某條誤操作時(shí)可以直接在日志中查找到原文信息,對(duì)于主從復(fù)制支持好。

缺點(diǎn):輸出非常大,如果是Alter語(yǔ)句將產(chǎn)生大量的記錄

格式如下:

DELETE FROM `back`.`sys_user` WHERE `deptid`=27 AND `status`=1 AND `account`='admin' AND `name`='張三' AND `phone`='18200000000' AND `roleid`='1' AND `createtime`='2016-01-29 08:49:53' AND `sex`=2 AND `email`='sn93@qq.com' AND `birthday`='2017-05-05 00:00:00' AND `avatar`='girl.gif' AND `version`=25 AND `password`='ecfadcde9305f8891bcfe5a1e28c253e' AND `salt`='8pgby' AND `id`=1 LIMIT 1; #start 4 end 796 time 2018-10-12 17:03:19

2.STATEMENT

針對(duì)sql語(yǔ)句的,每條語(yǔ)句產(chǎn)生一條記錄

優(yōu)點(diǎn):產(chǎn)生的日志量比較小,主從版本可以不一致

缺點(diǎn):主從有些語(yǔ)句不能支持,像自增主鍵和UUID這種類型的

格式如下:

delete from `sys_role`;

3.MIX

結(jié)合了兩種的優(yōu)點(diǎn),一般情況下都采用STATEMENT模式,對(duì)于不支持的語(yǔ)句采用ROW模式

轉(zhuǎn)換成sql

mysql自帶的mysqlbinlog

由于binlog是二進(jìn)制的,所以需要先轉(zhuǎn)換成文本文件,一般可以采用Mysql自帶的mysqlbinlog轉(zhuǎn)換成文本。

mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > /root/binlog_2018-10-10

參數(shù)說(shuō)明

  • --no-defaults 為了防止報(bào)錯(cuò):mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
  • --base64-output='decode-rows' 和-v一起使用, 進(jìn)行base64解碼
    其他有很多用來(lái)限定范圍的參數(shù),比如數(shù)據(jù)庫(kù),起始時(shí)間,起始位置等等。這些參數(shù)在查找誤操作的時(shí)候非常有用。

binlog的基本塊如下:

# at 417750
#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1538877038/*!*/;
BEGIN

1、# at 417750

指明的當(dāng)前位置相對(duì)文件開始的偏移位置,這個(gè)在mysqlbinlog命令中可以作為--start-position的參數(shù)

2、#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0

181007 1:50:38指明時(shí)間為18年10月7號(hào)1:50:38,serverid也就是你在配置文件中的配置的,end_log_pos 417844,這個(gè)塊在417844結(jié)束。thread_id執(zhí)行的線程id,exec_time執(zhí)行時(shí)間,error_code錯(cuò)誤碼

3、SET TIMESTAMP=1538877038/!/;

BEGIN

具體的執(zhí)行語(yǔ)句

一行記錄產(chǎn)生的日志如下所示

# at 417750
#181010  9:50:38 server id 1630000  end_log_pos 417844 CRC32 0x9fc3e3cd     Query   thread_id=440109962 exec_time=0 error_code=0
SET TIMESTAMP=1539136238/*!*/;
BEGIN
/*!*/;
# at 417844
#181010  9:50:38 server id 1630000  end_log_pos 417930 CRC32 0xce36551b     Table_map: `goods`.`good_info` mapped to number 129411
# at 417930
#181010  9:50:38 server id 1630000  end_log_pos 418030 CRC32 0x5827674a     Update_rows: table id 129411 flags: STMT_END_F
### UPDATE `goods`.`good_info`
### WHERE
###   @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
###   @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
###   @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
###   @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
###   @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
###   @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
###   @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
###   @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 418030
#181010  9:50:38 server id 1630000  end_log_pos 418061 CRC32 0x468fb30e     Xid = 212760460521
COMMIT/*!*/;
# at 418061

一行記錄產(chǎn)生的日志如上所示。以SET TIMESTAMP=1539136238/*!*/;開始,以COMMIT/*!*/;結(jié)尾。我們可以根據(jù)兩個(gè)at指明的位置來(lái)限定范圍。

注意一條記錄開始的SET TIMESTAMP之前的# at 417750和結(jié)尾的COMMIT之后的# at 418061

利用binlog2sql

binlog2sql官網(wǎng)介紹:從MySQL binlog解析出你要的SQL。根據(jù)不同選項(xiàng),你可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等。

基本使用如下:

python binlog2sql.py -hlocalhost -P3306 -udev -p'\*' -d room -t room_info --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

具體的使用我就不講解了github上講解的十分清楚,主要看下很多用來(lái)篩選的條件,比如起止時(shí)間--start-datetime/--stop-datetime,表名限定-t,數(shù)據(jù)庫(kù)限定-d,語(yǔ)句限定--sql-type,主要說(shuō)說(shuō)我遇到的一些問(wèn)題。

mysql的binlog模式

這里需要設(shè)置為ROW,因?yàn)镽OW模式有原來(lái)的信息,如果可以直接利用binlog2sql反向生成回滾sql,如果是STATEMENT無(wú)法生成,需要利用的mysql定時(shí)備份的文件再去做回滾

恢復(fù)數(shù)據(jù)的具體操作

因?yàn)楫?dāng)時(shí)線上執(zhí)行的是一條update語(yǔ)句,沒(méi)有唯一鍵索引的。導(dǎo)致有兩千多條記錄被更新。語(yǔ)句如下:

update room_info set status=1 where status=2;
  • 根據(jù)操作時(shí)間先定位對(duì)應(yīng)的binlog文件
    我記得當(dāng)時(shí)操作的時(shí)間大概的是上午9多左右,所以去找對(duì)應(yīng)的binlog文件最后修改時(shí)間大于9點(diǎn)并且時(shí)間最接近的一個(gè)文件。使用linux的ll命令查看文件的修改時(shí)間。
  • 篩選具體的數(shù)據(jù)庫(kù)
    因?yàn)橐粋€(gè)mysql實(shí)例的所有binlog文件是在一個(gè)文件中的,所以我們先要去除其他不想關(guān)的數(shù)據(jù)庫(kù)。利用-d參數(shù)來(lái)指明數(shù)據(jù)實(shí)例。然后在利用開始時(shí)間(--start-datetime)和結(jié)束時(shí)間(--stop-datetime)來(lái)進(jìn)一步篩選
mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d room --start-datetime='2018-10-10 9:00:00' --stop-datetime='2018-10-10 10:00:00' mysql-bin.011012>temp.sql
  • 壓縮取回文件分析
zip temp.zip temp.sql  sz temp.zip 

取回文件在本地用文本工具如vscode分析,里面有正則匹配,根據(jù)你改動(dòng)過(guò)的特征,比如我有個(gè)房間號(hào)888888,這個(gè)不應(yīng)該被修改,你就查看這個(gè)房間號(hào)的修改記錄,ROW模式的語(yǔ)句是Where在前,set在后。利用正則room_id=888888.*show_state=1.*AND show_state=2很快就能匹配到。我當(dāng)時(shí)的語(yǔ)句影響了兩千多條記錄,你根據(jù)找到的語(yǔ)句去找開始的SET TIMESTAMP=1539136238的位置之前的at和結(jié)尾的COMMIT之后的at。

  • 利用binlog2sql生成回滾語(yǔ)句
python binlog2sql.py -hlocalhost -P3306 -udev -p'*' -d room -t room_info -B --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

另外

因?yàn)槲疫@邊是一條update影響多條的情況,如果是帶唯一鍵的情況下,影響的只有一條記錄,完全沒(méi)必要這么麻煩,直接利用binlog2sql帶上-d和-t參數(shù)限定數(shù)據(jù)庫(kù)和表,然后利用grep來(lái)查找,直接可以得出對(duì)應(yīng)的sql。mysqlbinlog少了一個(gè)限定表和限定語(yǔ)句的功能。比如精確到一張表的Delete語(yǔ)句,能減少很多的數(shù)據(jù),能快速定位。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問(wèn)大家可以留言交流,謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:
  • MySQL數(shù)據(jù)庫(kù)恢復(fù)(使用mysqlbinlog命令)
  • MySQL中的binlog相關(guān)命令和恢復(fù)技巧
  • Mysql的Binlog數(shù)據(jù)恢復(fù):不小心刪除數(shù)據(jù)庫(kù)詳解
  • 教你自動(dòng)恢復(fù)MySQL數(shù)據(jù)庫(kù)的日志文件(binlog)
  • Linux上通過(guò)binlog文件恢復(fù)mysql數(shù)據(jù)庫(kù)詳細(xì)步驟
  • 解說(shuō)mysql之binlog日志以及利用binlog日志恢復(fù)數(shù)據(jù)的方法
  • MySQL使用mysqldump+binlog完整恢復(fù)被刪除的數(shù)據(jù)庫(kù)原理解析
  • MySQL使用binlog日志做數(shù)據(jù)恢復(fù)的實(shí)現(xiàn)
  • mysql5.7使用binlog 恢復(fù)數(shù)據(jù)的方法
  • 如何利用MySQL的binlog恢復(fù)誤刪數(shù)據(jù)庫(kù)詳解

標(biāo)簽:蘭州 賀州 宿遷 馬鞍山 黃山 南充 黔南 佛山

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《mysql如何利用binlog進(jìn)行數(shù)據(jù)恢復(fù)詳解》,本文關(guān)鍵詞  mysql,如何,利用,binlog,進(jìn)行,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《mysql如何利用binlog進(jìn)行數(shù)據(jù)恢復(fù)詳解》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于mysql如何利用binlog進(jìn)行數(shù)據(jù)恢復(fù)詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章