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

Binlog2sql data recovery weapon! It is especially suitable for data recovery operation in the case of no backup and only binlog. It can generate standard parsing SQL and produce corresponding reverse rollback SQL, and it is more readable than traditional mysqlbinlog. This paper focuses on data recovery in Python2.6.6 and 2.7.6 environment, focusing on the use of process and notes to record instructions for future use.

The verified environment is as follows

Python version

2.6.6 、 2.7.6

MySQL version

5.6.16-log

Parameters that must be set by MySQL Server before use

[mysqld]

Server_id = 2 [not necessarily 2, but must be set]

Log_bin = / home/data/mysql3306/mysql-bin

Max_binlog_size = 1G [can not be written, default 1G]

Binlog_format = row

Binlog_row_image = full [optional, default full format]

Description of usage restrictions

MySQL Server must be enabled and cannot be parsed in offline mode

The parsing speed is not as fast as mysqlbinlog

Support DML rollback, not DDL rollback

Minimum privilege user required for rollback

Mysql > grant select,replication slave,replication client on *. * to flashbackuser@'127.0.0.1' identified by 'flashback@123'

Python 2.6.6 environment configuration

[root@backup ~] # cd / usr/local/

[root@backup local] # tar xf binlog2sql.tar.gz

[root@backup local] # cd binlog2sql/binlog2sql_dependencies/

[root@backup binlog2sql_dependencies] # tar xf setuptools-0.6c11.tar.gz

[root@backup binlog2sql_dependencies] # cd setuptools-0.6c11

[root@backup setuptools-0.6c11] # python setup.py install

[root@backup setuptools-0.6c11] # cd..

[root@backup binlog2sql_dependencies] # tar xf pip-9.0.1.tar.gz

[root@backup binlog2sql_dependencies] # cd pip-9.0.1

[root@backup pip-9.0.1] # python setup.py install

[root@backup pip-9.0.1] # cd..

[root@backup binlog2sql_dependencies] # pip install * .whl mysql-replication-0.9.tar.gz

[root@backup binlog2sql_dependencies] # pip list

Basic usage:

[root@backup ~] # cd / home/data/mysql3306/

Must be under the data directory

[root@backup mysql3306] # python / usr/local/binlog2sql/binlog2sql/binlog2sql.py-- help

Usage instruction

Parse out the standard SQL

[root@backup mysql3306] # python / usr/local/binlog2sql/binlog2sql/binlog2sql.py-uflashbackuser-pflashback@123-h227.0.0.1-dbailidb-tbl_admin-- start-file='mysql-bin.000003' > bl_admin.sql

Parse out rollback SQL

[root@backup mysql3306] # python / usr/local/binlog2sql/binlog2sql/binlog2sql.py-- flashback-uflashbackuser-pflashback@123-h227.0.0.1-dbailidb-tbl_admin-- start-file='mysql-bin.000003' > fb_bl_admin.sql

Support parsing standard DDL

[root@backup mysql3306] # python / usr/local/binlog2sql/binlog2sql/binlog2sql.py-uflashbackuser-pflashback@123-h227.0.0.1-dbailidb-tbl_admin-- start-file='mysql-bin.000003' > bl_admin_ddl.sql

Rollback of DDL is not supported

[root@backup mysql3306] # python / usr/local/binlog2sql/binlog2sql/binlog2sql.py-- flashback-uflashbackuser-pflashback@123-h227.0.0.1-dbailidb-tbl_admin-- start-file='mysql-bin.000003' > fb_bl_admin_ddl.sql

Python2.7.6 environment configuration

[root@backup ~] # cd / usr/local/

[root@backup local] # tar xf Python-2.7.6.tar

[root@backup local] # cd Python-2.7.6/

[root@backup Python-2.7.6] #. / configure-prefix=/usr/local/python2

[root@backup Python-2.7.6] # echo $?

[root@backup Python-2.7.6] # make & & make install

[root@backup Python-2.7.6] # cd / usr/local/binlog2sql/binlog2sql_dependencies/

[root@backup binlog2sql_dependencies] # cd setuptools-0.6c11

[root@backup setuptools-0.6c11] # / usr/local/python2/bin/python2 setup.py install

[root@backup setuptools-0.6c11] # cd.. /

[root@backup binlog2sql_dependencies] # cd pip-9.0.1

[root@backup pip-9.0.1] # / usr/local/python2/bin/python2 setup.py install

[root@backup pip-9.0.1] # / usr/local/python2/bin/pip-- version

[root@backup pip-9.0.1] # cd.. /

[root@backup binlog2sql_dependencies] # / usr/local/python2/bin/pip install * .whl mysql-replication-0.9.tar.gz

[root@backup binlog2sql_dependencies] # / usr/local/python2/bin/pip list

[root@backup mysql3306] # vim / root/.bashrc

Alias python2='/usr/local/python2/bin/python2'

[root@backup mysql3306] # source / root/.bashrc

Basic usage:

Parse out the standard SQL:

[root@backup mysql3306] # python2 / usr/local/binlog2sql/binlog2sql/binlog2sql.py-uflashbackuser-pflashback@123-h227.0.0.1-dbailidb-tbl_admin-- start-file='mysql-bin.000003' > bl_admin.sql

Resolve the rollback SQL:

[root@backup mysql3306] # python2 / usr/local/binlog2sql/binlog2sql/binlog2sql.py-- flashback-uflashbackuser-pflashback@123-h227.0.0.1-dbailidb-tbl_admin-- start-file='mysql-bin.000003' > fb_bl_admin.sql

Parameter description

[root@backup ~] # python / usr/local/binlog2sql/binlog2sql/binlog2sql.py-- help

Usage: binlog2sql.py

Mysql connection configuration

-h host;-P port;-u user;-p password

Analytic mode

-- stop-never continuously parses binlog. Optional. The default False, which synchronizes to the latest binlog location when the command is executed.

-K,-- no-primary-key removes the primary key from the INSERT statement. Optional. Default False

-B,-- flashback generates rollback SQL, which can parse large files without memory restrictions. Optional. Default False. Cannot be added at the same time as stop-never or no-primary-key.

In back-interval-B mode, roll back SQL for every thousand lines printed, add a word SLEEP for how many seconds. If you do not want to add SLEEP, please set it to 0. Optional. The default is 1.0.

Analytical range control

-- start-file starts parsing the file. You only need the file name, not the full path. Must.

-- the starting resolution position of start-position/--start-pos. Optional. The default is the starting position of start-file.

-- stop-file/--end-file terminates the resolution file. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option has no effect.

-- stop-position/--end-pos terminates the resolution location. Optional. The default is the last location of stop-file; if the parsing mode is stop-never, this option has no effect.

-- the starting parsing time of start-datetime, in the format of'% Y-%m-%d% HRV% MRV% S'. Optional. Does not filter by default.

-- stop-datetime terminates the resolution time, in the format'% Y-%m-%d% HRV% MRV% S'. Optional. Does not filter by default.

Object filtering

-d,-- databases only parses the sql of the target db, and multiple libraries are separated by spaces, such as-d db1 db2. Optional. The default is empty.

-t,-- tables only parses the sql of the target table, and multiple tables are separated by spaces, such as-t tbl1 tbl2. Optional. The default is empty.

-- only-dml parses only dml and ignores ddl. Optional. Default False.

-- sql-type parses only specified types, and supports INSERT, UPDATE, and DELETE. Multiple types are separated by spaces, such as-- sql-type INSERT DELETE. Optional. The default is to resolve all additions, deletions and changes. If this parameter is used but no type is filled in, none of the three will be parsed.

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.