binlog2sql參考

安裝pip，參照安裝網(wǎng)址：https://pip.pypa.io/en/stable/installing/

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
python get-pip.py

安裝git：
yum install git -y

安裝binlog2sql，參照網(wǎng)站：https://github.com/danfengcao/binlog2sql

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

詳細的使用方法請參考官網(wǎng)：https://github.com/danfengcao/binlog2sql
另外，binlog2sql不需每臺機器都安裝，可以只安裝在一臺機器上面，分析其它數(shù)據(jù)庫的日志，下面列出一些常用的使用選項：

1.查看指定的數(shù)據(jù)庫，多個數(shù)據(jù)庫用空格發(fā)開：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008'
多個數(shù)據(jù)庫：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app data --start-file='mysql-bin.000008'

生產(chǎn)閃回的sql加參數(shù)-B：
python binlog2sql.py -B -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008'

2.查看指定數(shù)據(jù)庫下面指定的表的操作語句，多個表用逗號隔開：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info --start-file='mysql-bin.000008'
多個表：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info t_product_info --start-file='mysql-bin.000008'

生產(chǎn)閃回的sql加參數(shù)-B
python binlog2sql.py -B -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info t_product_info --start-file='mysql-bin.000008'

3.指定binlog的pos點進行閃回--start-position=xxx --stop-position=xxxx
python binlog2sql.py -B -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --start-position=1219 --stop-position=1410

指定binglog的時間進行閃回--start-datetime="2019-01-09 15:55:41" --stop-datetime="2019-01-09 16:06:48"
python binlog2sql.py -B -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --start-datetime="2019-01-09 14:58:47" --stop-datetime="2019-01-09 15:02:37"

4.指定多個binlog日志：
python binlog2sql.py -B -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --stop-file='mysql-bin.000009'

5.binlog2sql在ddl進行操作時候也可以記錄下來，如創(chuàng)建表，修改表，創(chuàng)建用戶等，但是閃回只支持dml，其實看到ddl自己也明白了什么回事，如果不想輸出ddl，
可以使用--only-dml參數(shù)進行控制：
python binlog2sql.py --only-dml -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --stop-file='mysql-bin.000010'

6.過濾指定的dml操作insert，update，delete：
python binlog2sql.py --only-dml --sql-type INSERT UPDATE DELETE -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app --start-file='mysql-bin.000008' --stop-file='mysql-bin.000010'

7.以下為binlog2sql輸出的內(nèi)容供實例參考：
python binlog2sql.py -h292.168.56.201 -P3306 -uroot -p'MYsql#123' -d app -t t_user_info --start-file='mysql-bin.000008'
USE app;
create database app;
CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS 'FEE4A573DDC077A021A143383A52AAB67E75FA7C';
GRANT ALL PRIVILEGES ON . TO 'root'@'%';
USE app;
CREATE TABLE t_user_info (
id int NOT NULL AUTO_INCREMENT ,
name varchar(255) NULL ,
address varchar(255) NULL ,
PRIMARY KEY (id)
);
INSERT INTO app.t_user_info(address, id, name) VALUES ('shanghai', 1, '張三'); #start 1219 end 1407 time 2019-01-09 14:58:46
USE data;
DROP TABLE t_data / generated by server */;
USE app;
TRUNCATE table t_product_info;

8.binlog2sql的具體用法請參考；
https://github.com/danfengcao/binlog2sql

或者 ./binlog2sql.py --help
./binlog2sql.py --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]
[-P PORT] [--start-file START_FILE]
[--start-position START_POS] [--stop-file END_FILE]
[--stop-position END_POS] [--start-datetime START_TIME]
[--stop-datetime STOP_TIME] [--stop-never] [--help]
[-d [DATABASES [DATABASES ...]]]
[-t [TABLES [TABLES ...]]] [--only-dml]
[--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
[--back-interval BACK_INTERVAL]

Parse MySQL binlog to SQL you want

optional arguments:
--stop-never Continuously parse binlog. default: stop at the latest
event when you start.
--help help information
-K, --no-primary-key Generate insert sql without primary key if exists
-B, --flashback Flashback data to start_position of start_file
--back-interval BACK_INTERVAL
Sleep time between chunks of 1000 rollback sql. set it
to 0 if do not need sleep

connect setting:
-h HOST, --host HOST Host the MySQL database server located
-u USER, --user USER MySQL Username to log in as
-p [PASSWORD [PASSWORD ...]], --password [PASSWORD [PASSWORD ...]]
MySQL Password to use
-P PORT, --port PORT MySQL port to use

interval filter:
--start-file START_FILE
Start binlog file to be parsed
--start-position START_POS, --start-pos START_POS
Start position of the --start-file
--stop-file END_FILE, --end-file END_FILE
Stop binlog file to be parsed. default: '--start-file'
--stop-position END_POS, --end-pos END_POS
Stop position. default: latest position of '--stop-
file'
--start-datetime START_TIME
Start time. format %Y-%m-%d %H:%M:%S
--stop-datetime STOP_TIME
Stop Time. format %Y-%m-%d %H:%M:%S;

schema filter:
-d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
dbs you want to process
-t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
tables you want to process

type filter:
--only-dml only print dml, ignore ddl
--sql-type [SQL_TYPE [SQL_TYPE ...]]
Sql type you want to process, support INSERT, UPDATE,
DELETE.