Mysql
从binlog中恢复数据
背景:业务中的的司机被删除了。如何恢复呢?
-
binlog哪里找?
形如/data/zftMysqlData/binlog/binlog.000011
-
如何确认是哪个binlog
mysql> show master status; +---------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | binlog.000011 | 72100881 | | | +---------------+----------+--------------+------------------+
-
mysqlbinlog哪里找?
/usr/local/zftmysql/bin
-
如何恢复指定库,指定时间的数据?
sudo ./mysqlbinlog --database=db_ycc_drvcar --start-datetime=‘2019-09-05 14:00:00’ --stop-datetime=‘2019-09-05 14:30:00’ -v --base64-output=decode-rows /data/zftMysqlData/binlog/binlog.000011
- –database= 指定数据库
- –start-datetime,–stop-datetime指定开始时间,结束时间
- -v 显示出SQL语句
### DELETE FROM db_ycc_drvcar.t_driver ### WHERE ### @1=400000001 ### @2='cl' ### @3='t' ### @4='S9AppJ8f8mnbPNfZbHyP0w==' ### @5='3d74ca69-3142-4c0e-9770-6eb2917fcd93' ### @6=1 ### @7=10004 ### @8=10004 ### @9='qdc' ### @10='' ### @11=1 ### @12='12580http://ttt.tst.mauu.me/dnsrebending?d6409bc382b12125b5a01bb8d5be6cb2' ### @13='https://wyc10001-30132.pixsz.qqic.cn/static/wyc_topic/ed0fb9a29761b4824d9300b1d2e91c10af88f228' ### @14='f7418d71503a6b4b49db841bd484eefaac3d210f' ### @15=2019-09-02 16:36:18 ### @16=2019-09-03 20:21:06 ### @17='ecaea3d799d22e41c1db893784921560' ### DELETE FROM db_ycc_drvcar.t_driver ### WHERE ### @1=400000005 ### @2='三' ### @3='张' ### @4='S9AppJ8f8mnbPNfZbHyP0w==' ### @5='' ### @6=1 ### @7=10004 ### @8=10004 ### @9='hdc' ### @10='' ### @11=1 ### @12='00001' ### @13='https://wyc10002-30132.xicsz.qpic.cn/static/wyc_topic/ed0fb9a27760b4824d9300b1d2e91c10af88f228' ### @14='' ### @15=2019-09-02 20:02:07 ### @16=2019-09-02 20:02:07 ### @17='7a5873fdf221288f44ea08fab38dd275'
从上面可以看出来,被删除的数据已经找到了