Shulou(Shulou.com)06/01 Report--

To install pip, refer to the installation website: https://pip.pypa.io/en/stable/installing/

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

Python get-pip.py

Install git:

Yum install git-y

Install binlog2sql, refer to the website: https://github.com/danfengcao/binlog2sql

Git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sql

Pip install-r requirements.txt

For detailed usage, please refer to the official website: https://github.com/danfengcao/binlog2sql

In addition, binlog2sql does not need to be installed on every machine, but can be installed on only one machine to analyze the logs of other databases. Here are some common usage options:

1. View the specified database, and multiple databases are opened with spaces:

Python binlog2sql.py-h292.168.56.201-P3306-uroot-paired MYsql app 123'- d app-- start-file='mysql-bin.000008'

Multiple databases:

Python binlog2sql.py-h292.168.56.201-P3306-uroot-paired MYsql d app data 123'-start-file='mysql-bin.000008'

Production flashback sql plus parameter-B:

Python binlog2sql.py-B-h292.168.56.201-P3306-uroot-paired MYsql app 123'-d app-- start-file='mysql-bin.000008'

two。 View the action statements for the specified tables under the specified database, separated by commas:

Python binlog2sql.py-h292.168.56.201-P3306-uroot-paired MYsql t_user_info 123'-d app-t t_user_info-- start-file='mysql-bin.000008'

Multiple tables:

Python binlog2sql.py-h292.168.56.201-P3306-uroot-paired MYsql t_user_info t_product_info 123'-d app-t t_user_info t_product_info-- start-file='mysql-bin.000008'

Sql plus parameter-B for production flashback

Python binlog2sql.py-B-h292.168.56.201-P3306-uroot-paired MYsql t_user_info t_product_info 123'-d app-t t_user_info t_product_info-- start-file='mysql-bin.000008'

3. Specify the pospoint of binlog for flashback-- start-position=xxx-- stop-position=xxxx

Python binlog2sql.py-B-h292.168.56.201-P3306-uroot-paired MYsql app 123'- d app-- start-file='mysql-bin.000008'-- start-position=1219-- stop-position=1410

Specify the time of binglog for flashback-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-pendant MYsql app 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. Specify multiple binlog logs:

Python binlog2sql.py-B-h292.168.56.201-P3306-uroot-paired MYsql app 123'- d start-file='mysql-bin.000008'-- stop-file='mysql-bin.000009'

5.binlog2sql can also record when ddl operates, such as creating tables, modifying tables, creating users, etc., but flashback only supports dml. In fact, you can see what ddl understands, if you don't want to output ddl.

You can use the-- only-dml parameter to control:

Python binlog2sql.py-- only-dml-h292.168.56.201-P3306-uroot-pendant MYsql-123'-d app-- start-file='mysql-bin.000008'-- stop-file='mysql-bin.000010'

6. Filter the specified dml operation insert,update,delete:

Python binlog2sql.py-- only-dml-- sql-type INSERT UPDATE DELETE-h292.168.56.201-P3306-uroot-paired MYsqlcards 123'-d app-- start-file='mysql-bin.000008'-- stop-file='mysql-bin.000010'

7. The following is the output of binlog2sql for example reference:

Python binlog2sql.py-h292.168.56.201-P3306-uroot-paired MYsql t_user_info 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,' Zhang San'); # 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

For the specific usage of 8.binlog2sql, please refer to

Https://github.com/danfengcao/binlog2sql

Or. / 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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.