binlog2sql在mariadb中简单使用

 binlog2sql在mariadb中简单使用

 

# https://github.com/danfengcao/binlog2sql
#centos7
yum -y install epel-release 
yum -y install git
yum -y install python-pip 
pip --version #查看pip版本
#若要更新pip，可以使用命令: pip install --upgrade pip
cd /opt
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

MySQL server必须设置以下参数:
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

# 添加环境变量
vim ~/.bash_profile
PATH=/opt/binlog2sql/binlog2sql:$PATH:$HOME/bin

source ~/.bash_profile

# 建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'flash_back'@'%' identified by 'flash_backpwd';

#基本用法
#解析出标准SQL
python /opt/binlog2sql/binlog2sql/binlog2sql.py -h192.168.142.238 -P3306 -uflash_back -p'flash_backpwd' -ddblww -t black_words --start-file='mybinlog.000005' > 05_full.sql

# 查看内容：
# Dummy event replacin;
DELETE FROM `dblww`.`black_words` WHERE `word`='阿扁推翻' AND `id`=1 AND `createTime`='2016-05-30 07:37:17' LIMIT 1; #start 832193 end 832409 time 2020-03-12 13:52:16

解析出回滚SQL：
python /opt/binlog2sql/binlog2sql/binlog2sql.py --flashback -h192.168.142.238 -P3306 -uflash_back -p'flash_backpwd' -ddblww -t black_words --start-file='mybinlog.000005' --start-position=832193 --stop-position=832409 > 05_flash.sql

[root@db238 logs]# cat 05_flash.sql 
INSERT INTO `dblww`.`black_words`(`word`, `id`, `createTime`) VALUES ('阿扁推翻', 1, '2016-05-30 07:37:17'); #start 832193 end 832409 time 2020-03-12 13:52:16
[root@db238 logs]#