binlog2sql

https://github.com/danfengcao/binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

mysqlbinlog配置

binlog_format = row
binlog_row_image = full

如果数据库包含中文

reload(sys)
sys.setdefaultencoding('utf8')

最小权限

#select, super/replication client, replication slave
#GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT

python binlog2sql.py \
-h 127.0.0.1 \
-P 3306 \
-uliwl \
-p'123123' \
--start-file='binlog.000032'

-dtest \
-t test3 test4 

生成回滚语句

--flashback

测试update不带where

UPDATE Websites SET alexa = '5000', country = 'USA'

闪回实战.png

通过binlog2sql远程解析binlog最热dml语句

    python binlog2sql.py -h 127.0.0.1 -P 3306 -uliwl -p'123123' --start-file='binlog.000033' \
    |awk '/UPDATE|INSERT|DELETE/{gsub("###","");gsub("INSERT.*INTO","INSERT");gsub("DELETE.*FROM","DELETE");count[$1""$2]++}END{for(i in count)print i,"\t",count[i]}'|sort -k3nr|head -n 20

通过mysqlbinlog远程解析binlog最热dml语句

mysqlbinlog \
--no-defaults \
--read-from-remote-server \
-h 127.0.0.1 -P 3306 -uliwl -p123123 \
--base64-output=decode-rows \
-vv \
--start-datetime="2019-05-01 00:00:01" \
--stop-datetime="2019-05-17 23:59:59" \
binlog.000033 \
|awk '/UPDATE|INSERT|DELETE/{gsub("###","");gsub("INSERT.*INTO","INSERT");gsub("DELETE.*FROM","DELETE");count[$1""$2]++}END{for(i in count)print i,"\t",count[i]}'|sort -k3nr|head -n 20
添加新评论