binlog2sql binlog解析与回滚

以你之姓@ 2022-04-13 12:57 238阅读 0赞

*  用途

1.  数据快速回滚(闪回)
2.  主从切换后新master丢数据的修复
3.  从binlog生成标准SQL，带来的衍生功能

*  安装  
    git clone [https://github.com/danfengcao/binlog2sql.git][https_github.com_danfengcao_binlog2sql.git] && cd binlog2sql  
    pip install -r requirements.txt
 *  mysql参数设置  
    \[mysqld\]  
    server\_id = 1  
    log\_bin = /var/log/mysql/mysql-bin.log  
    max\_binlog\_size = 1G  
    binlog\_format = row  
    binlog\_row\_image = full
 *  对用户赋权  
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘glon’@’%’ IDENTIFIED BY ‘glon’
 *  通过模糊时间点解析binlog  
    python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uglon -p’glon’ -d test -t tb1 --start-file=‘mysql-bin.000052’ --start-datetime=‘2016-12-13 20:25:00’ --stop-datetime=‘2016-12-13 20:30:00’
 *  使用flashback模式生成回滚sql  
    \-------使用时间点  
    python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uglon -p’glon’ -d test -t tb1 --start-file=‘mysql-bin.000052’ --start-datetime=‘2016-12-13 20:25:00’ --stop-datetime=‘2016-12-13 20:30:00’  
    \-B > rollback.sql | cat  
    \--------使用pos点，从查询到的binlog中可以砍刀pos点范围  
    python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uglon -p’glon’ -d test -t tb1 --start-file=‘mysql-bin.000052’ --start-position=728 --stop-position=938 -B > rollback.sql | cat
 *  将要回滚的sql，source即可
 *  对比mysqlbinlog的优点  
    flashback模式下，更适合闪回实战  
    解析为标准SQL，方便理解、筛选
 *  常用sql示例

mysqlbinlog  -vv  --start-datetime="2018-11-01 08:40:00"  --stop-datetime="2018-11-01 09:07:00"  /data/tis/mysql3351/mysql-bin.000105 > /tmp/tis_1101.sql
    
    python binlog2sql.py -h -u -p -P3306  -d ecif --start-file='mysql-bin.001359' --start-datetime="2018-11-17 00:00:00"   --stop-datetime="2018-11-17 00:04:59" >./ecif_1117.sql
    
    mysqlbinlog  -v --start-datetime="2018-09-20 04:24:50" --stop-datetime="2018-09-20 04:25:10" ./mysql-bin_20180920 > ./binlog-0920-wheeljack.sql
    
    
    mysqlbinlog --no-defaults -u账号 -p密码 -h   --read-from-remote-server mysql-bin.000180 --base64-output=decode-rows -v > /data.sql
    
    python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p'123456' -dlocal -tuser --start-file='mysql-bin.000038' --sql-type=DELETE --start-datetime='2017-12-17 19:39:33' --stop-datetime='2017-12-17 19:40:01' >/**/data6.sql
    
    
    mysqldump -B pa cfg -uroot -p eo4mHsGQg2b9hQ5c > /tmp/tis.sql

[https_github.com_danfengcao_binlog2sql.git]: https://github.com/danfengcao/binlog2sql.git