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

from

  https://github.com/getredash/redash
  http://kekefund.com/2018/09/12/redash-use/

start

  git clone https://github.com/getredash/redash && cd redash 

  git tag
  git checkout -b v6.0.0 v6.0.0

  cat docker-compose.production.yml 

nginx改端口

nginx:
ports:
  - "88:80"

images指定v6

server:
image: redash/redash:6.0.0.b8537

worker:
image: redash/redash:6.0.0.b8537

新增挂载

mkdir -p /home/redash/{redis,pgsql}
redis:
volumes:
  - /home/redash/redis:/data
postgres:
volumes: 
  - /home/redash/pgsql:/var/lib/postgresql/data

修改REDASH_COOKIE_SECRET

  REDASH_COOKIE_SECRET: "Q422k6vaXUk8"
  

运行

  docker-compose -f docker-compose.production.yml run --rm server create_db
  docker-compose -f docker-compose.production.yml up -d

停止

  docker-compose -f docker-compose.production.yml down

URL

  http://*.*.*.*:88/

cat nginx.conf


log_format log_json '{ "@timestamp": "$time_local", '
    '"remote_addr": "$remote_addr", '
    '"referer": "$http_referer", '
    '"request": "$request", '
    '"status": $status, '
    '"bytes": $body_bytes_sent, '
    '"agent": "$http_user_agent", '
    '"x_forwarded": "$http_x_forwarded_for", '
    '"up_addr": "$upstream_addr",'
    '"up_host": "$upstream_http_host",'
    '"up_resp_time": "$upstream_response_time",'
    '"request_time": "$request_time"'
    ' }';
tail -f access.log



{ "@timestamp": "19/Apr/2019:10:10:51 +0800", "remote_addr": "47.***.***.225", "referer": "https://www.1iwl.com/archives/265/", "request": "GET /archives/265/ HTTP/1.1", "status": 200, "bytes": 6366, "agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36", "x_forwarded": "-", "up_addr": "*.*.*.*:9000","up_host": "-","up_resp_time": "0.028","request_time": "0.028" }

QQ截图20190419101218.png

from:https://blog.csdn.net/lihao21/article/details/78784895

备份

\cp ~/.bashrc{,.bak}

创建回收目录

mkdir -p ~/.trash
cat ~/.bashrc

# .bashrc

# User specific aliases and functions

#alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias vi='vim'
alias l.='ls -d .* --color=auto'
alias ll='ls -l --color=auto'
alias ls='ls --color=auto'

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi
PS1="\[\e[37;40m\][\[\e[32;40m\]\u\[\e[37;40m\]@\h \[\e[35;40m\]\W\[\e[0m\]]\\$"

#rm防误删
alias rm=trash
alias rl='ls -al ~/.trash/'
trash()
{
    mv $@ ~/.trash/
    echo "已经移动到回收站,可rl查看"
    echo "cltr清除回收站,默认保存30天"
    echo "retr可恢复误删除"
}
retr ()
{
    mv -i ~/.trash/$@ ./
}
cltr ()
{
    find ~/.trash/ -mtime +30 -type f -name "*" -exec \rm -rf {} \;
}

生效

source ~/.bashrc