Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use binlog2sql, a sharp tool for Binglog analysis in MYSQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

A brief introduction to binlog2sql

Binlog2sql is an open source tool that can parse the SQL you want from MySQL binlog. Depending on the options, you can get the original SQL, roll back the SQL, remove the primary key INSERT SQL, and so on.

The main uses are as follows:

(1) Quick data rollback (flashback)

(2) repair of data inconsistency after master-slave switching

(3) the derivative function of generating standard SQL from binlog

II. Binlog2sql installation

1. Download binlog2sql

Https://github.com/danfengcao/binlog2sql

2. Binlog2sql dependency package installation

Python2.6+

PyMySQL==0.7.8+

Wheel==0.24.0+

Mysql-replication==0.9+

(1) PyMySQL-0.7.10 installation

Https://pypi.python.org/pypi/PyMySQL/

[root@node1 binlogsql] # tar-xzvf PyMySQL-0.7.10.tar.gz

[root@node1 binlogsql] # cd PyMySQL-0.7.10

[root@node1 PyMySQL-0.7.10] # python setup.py install

(2) wheel-0.30.0a0 installation

Https://pypi.python.org/pypi/wheel/

[root@node1 binlogsql] # tar-xzvf wheel-0.30.0a0.tar.gz

[root@node1 binlogsql] # cd wheel-0.30.0a0

[root@node1 wheel-0.30.0a0] # python setup.py install

(3) python-mysql-replication installation

Https://github.com/noplay/python-mysql-replication

[root@node1 binlogsql] # unzip python-mysql-replication-master.zip

[root@node1 binlogsql] # cd python-mysql-replication-master

[root@node1 python-mysql-replication-master] # python setup.py install

(4) you can install the corresponding dependency package through pip

Https://pypi.python.org/pypi/pip

[root@node1 tools] # tar-xzvf pip-9.0.1.tar.gz

[root@node1 tools] # cd pip-9.0.1

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

[root@node1 binlog2sql-master] # pip install-r requirements.txt

3. Binlog2sql installation

Download and decompress directly, and run the corresponding py script

[root@node1 tools] # unzip binlog2sql-master.zip

[root@node1 tools] # cd binlog2sql-master

Set an alias to facilitate command invocation:

Alias binlog2sql='python / tools/binlogsql/binlog2sql-master/binlog2sql/binlog2sql.py'

III. Use of binlog2sql

1. Binlog2sql help manual

[root@node1 binlog2sql] # binlog2sql-- help

Usage: binlog2sql.py [- h HOST] [- u USER] [- p PASSWORD] [- P PORT]

[--start-file STARTFILE] [--start-position STARTPOS]

[--stop-file ENDFILE] [--stop-position ENDPOS]

[--start-datetime STARTTIME] [--stop-datetime STOPTIME]

[--stop-never] [--help] [- d [DATABASES [DATABASES...]

[- t [TABLES [TABLES...] [- K] [- B]

Parse MySQL binlog to SQL you want

Optional arguments:

-- stop-never Wait for more data from the server. Default: stop

Replicate at the last binlog when you start binlog2sql # # continuously synchronize binlog. Optional. Do not add or synchronize to the latest binlog location when the command is executed

-- help help infomation

-K,-- no-primary-key Generate insert sql without primary key if exists # # removes the primary key from the INSERT statement. Optional.

-B,-- flashback Flashback data to start_postition of start_file # # generates a rollback statement that parses large files without memory restrictions, adding a SLEEP SELECT (1) for every thousand lines printed. Optional. Cannot be added at the same time as stop-never or no-primary-key.

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

MySQL Password to use

-P PORT,-- port PORT MySQL port to use

Range filter:

-- start-file STARTFILE

Start binlog file to be parsed # # start parsing file. Must.

-- start-position STARTPOS,-- the starting resolution position of start-pos STARTPOS # # start-file. Optional. The default is the starting position of start-file.

Start position of the-start-file

-- stop-file ENDFILE,-- end-file ENDFILE # # parse the file at the end. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option has no effect.

Stop binlog file to be parsed. Default:'--start-file'

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

Stop position of-- stop-file. Default: latest position

Of'--stop-file'

-- start-datetime STARTTIME

Start reading the binlog at first event having a

Datetime equal or posterior to the argument; the

Argument must be a date and time in the local time

Zone, in any format accepted by the MySQL server for

DATETIME and TIMESTAMP types, for example: 2004-12-25

11:25:56 (you should probably use quotes for your

Shell to set it properly). # # from which point in time the binlog should be parsed, and the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-1-1-11-1-1-1-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11. Optional. Does not filter by default.

-- stop-datetime STOPTIME

Stop reading the binlog at first event having a

Datetime equal or posterior to the argument; the

Argument must be a date and time in the local time

Zone, in any format accepted by the MySQL server for

DATETIME and TIMESTAMP types, for example: 2004-12-25

11:25:56 (you should probably use quotes for your

Shell to set it properly). # # at which time the binlog stops parsing, and the format must be datetime, such as' 2016-11-11-11-11-11-14-11-11-11-11-1-11-1-1-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11. Optional. Does not filter by default.

Schema filter:

-d [DATABASES [DATABASES...]],-- databases [DATABASES [DATABASES...]]

Dbs you want to process # # outputs only the sql of the target db. Optional. The default is empty.

-t [TABLES [TABLES...]],-- tables [TABLES [TABLES...]]

Tables you want to process # # outputs only the sql of the target tables. Optional. The default is empty.

2. Binlog2sql requires that mysql configuration parameters include the following

[mysqld]

Log-bin-index = / home/mysql/bin-index

Max_binlog_size = 1G

Binlog_format = row

Binlog_row_image = full

Log-bin = / home/mysql/mysql-bin

3. Binlog2sql parses binlog cases

(1) parse all the contents of a binlog

Click (here) to collapse or open

# # constructing binlog content

(root:localhost:Wed Mar 15 11:45:32 2017) [(none)] > flush logs

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Mar 15 11:45:36 2017) [(none)] > show master status\ G

* * 1. Row *

File: mysql-bin.000028

Position: 120

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

(root:localhost:Wed Mar 15 11:45:40 2017) [(none)] > use dbtest

Database changed

(root:localhost:Wed Mar 15 11:46:53 2017) [dbtest] > show tables

+-+

| | Tables_in_dbtest |

+-+

| | T |

| | T1 |

| | t |

| | T1 |

| | T2 |

+-+

5 rows in set (0.00 sec)

(root:localhost:Wed Mar 15 11:46:54 2017) [dbtest] > drop table T1

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Mar 15 11:46:57 2017) [dbtest] > drop table T

Query OK, 0 rows affected (0.01 sec)

(root:localhost:Wed Mar 15 11:47:01 2017) [dbtest] > drop table T1

Query OK, 0 rows affected (0.01 sec)

(root:localhost:Wed Mar 15 11:47:02 2017) [dbtest] > show tables

+-+

| | Tables_in_dbtest |

+-+

| | t |

| | T2 |

+-+

2 rows in set (0.00 sec)

(root:localhost:Wed Mar 15 11:47:07 2017) [dbtest] > drop table T2

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Mar 15 11:47:12 2017) [dbtest] > create table T1 select * from t

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

(root:localhost:Wed Mar 15 11:47:20 2017) [dbtest] > select * from t

Empty set (0.00 sec)

(root:localhost:Wed Mar 15 11:47:23 2017) [dbtest] > desc t

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | YES | | NULL |

+-+ +

1 row in set (0.00 sec)

(root:localhost:Wed Mar 15 11:47:27 2017) [dbtest] > insert into t values (1), (2), (3)

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings: 0

(root:localhost:Wed Mar 15 11:47:38 2017) [dbtest] > insert into T1 select * from t

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

(root:localhost:Wed Mar 15 11:47:45 2017) [dbtest] > update T1 set id='11' where idshow master status\ G

* * 1. Row *

File: mysql-bin.000028

Position: 38968

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

The normal statement is: python binlog2sql.py-h20.1.0.10-P3306-udb_user-pendant abc.123'-ddbtest-- start-file='mysql-bin.000028'

Because of the binlog2sql alias above, you can call the command directly through the alias:

[root@node1 binlog2sql] # binlog2sql-h20.1.0.10-P3306-udb_user-pendant abc.123'-ddbtest-- start-file='mysql-bin.000028'

USE dbtest

DROP TABLE `t1` / * generated by server * /

USE dbtest

DROP TABLE `T` / * generated by server * /

USE dbtest

DROP TABLE `T1` / * generated by server * /

USE dbtest

DROP TABLE `t2` / * generated by server * /

USE dbtest

CREATE TABLE `t1` (

`id`int (11) DEFAULT NULL

);

INSERT INTO `dbtest`.`t` (`id`) VALUES (1); # start 35104 end 35274 time 2017-03-15 11:47:38

INSERT INTO `dbtest`.`t` (`id`) VALUES (2); # start 35104 end 35274 time 2017-03-15 11:47:38

INSERT INTO `dbtest`.`t` (`id`) VALUES (3); # start 35104 end 35274 time 2017-03-15 11:47:38

INSERT INTO `dbtest`.`t1` (`id`) VALUES (1); # start 36062 end 36233 time 2017-03-15 11:47:45

INSERT INTO `dbtest`.`t1` (`id`) VALUES (2); # start 36062 end 36233 time 2017-03-15 11:47:45

INSERT INTO `dbtest`.`t1` (`id`) VALUES (3); # start 36062 end 36233 time 2017-03-15 11:47:45

UPDATE `dbtest`.`t1` SET `id` = 11 WHERE `id` = 1 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

UPDATE `dbtest`.`t1` SET `id` = 11 WHERE `id` = 2 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

UPDATE `dbtest`.`t1` SET `id` = 11 WHERE `id` = 3 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

(2) parsing the corresponding operation records of some binlog tables.

Click (here) to collapse or open

(root:localhost:Wed Mar 15 15:01:01 2017) [dbtest] > flush logs

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Mar 15 15:01:09 2017) [dbtest] > show master status\ G

* * 1. Row *

File: mysql-bin.000030

Position: 120

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

(root:localhost:Wed Mar 15 15:01:16 2017) [dbtest] > delete from T1

Query OK, 3 rows affected (0.00 sec)

(root:localhost:Wed Mar 15 15:01:51 2017) [dbtest] > create table T2 (id int)

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Mar 15 15:02:03 2017) [dbtest] > insert into T2 select * from t

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings: 0

# # pay special attention to the fact that if the sql,DDL of a particular table is fully parsed, DML will filter the corresponding table SQL

[root@node1 mysql] # binlog2sql-h20.0.1.10-P3306-udb_user-pendant abc.123'-ddbtest-t T1-- start-file='mysql-bin.000028'-- stop-file='mysql-bin.000030'

USE dbtest

DROP TABLE `t1` / * generated by server * /

USE dbtest

DROP TABLE `T` / * generated by server * /

USE dbtest

DROP TABLE `T1` / * generated by server * /

USE dbtest

DROP TABLE `t2` / * generated by server * /

USE dbtest

CREATE TABLE `t1` (

`id`int (11) DEFAULT NULL

);

INSERT INTO `dbtest`.`t1` (`id`) VALUES (1); # start 36062 end 36233 time 2017-03-15 11:47:45

INSERT INTO `dbtest`.`t1` (`id`) VALUES (2); # start 36062 end 36233 time 2017-03-15 11:47:45

INSERT INTO `dbtest`.`t1` (`id`) VALUES (3); # start 36062 end 36233 time 2017-03-15 11:47:45

UPDATE `dbtest`.`t1` SET `id` = 11 WHERE `id` = 1 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

UPDATE `dbtest`.`t1` SET `id` = 11 WHERE `id` = 2 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

UPDATE `dbtest`.`t1` SET `id` = 11 WHERE `id` = 3 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

DELETE FROM `dbtest`.`t1` WHERE `id` = 11 LIMIT 1; # start 10116 end 10287 time 2017-03-15 15:01:51

DELETE FROM `dbtest`.`t1` WHERE `id` = 11 LIMIT 1; # start 10116 end 10287 time 2017-03-15 15:01:51

DELETE FROM `dbtest`.`t1` WHERE `id` = 11 LIMIT 1; # start 10116 end 10287 time 2017-03-15 15:01:51

USE dbtest

Create table T2 (id int)

(3) Database rollback

# # parsing the rolled-back SQL through-B or-- flashback parameters, but only parsing DML in terms of parsing content

[root@node1 mysql] # binlog2sql-- flashback-h20.0.1.10-P3306-udb_user-pendant abc.123'-ddbtest-tt-tt1-- start-file='mysql-bin.000028'-- stop-file='mysql-bin.000030'

INSERT INTO `dbtest`.`t1` (`id`) VALUES (11); # start 10116 end 10287 time 2017-03-15 15:01:51

INSERT INTO `dbtest`.`t1` (`id`) VALUES (11); # start 10116 end 10287 time 2017-03-15 15:01:51

INSERT INTO `dbtest`.`t1` (`id`) VALUES (11); # start 10116 end 10287 time 2017-03-15 15:01:51

UPDATE `dbtest`.`t1` SET `id` = 3 WHERE `id` = 11 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

UPDATE `dbtest`.`t1` SET `id` = 2 WHERE `id` = 11 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

UPDATE `dbtest`.`t1` SET `id` = 1 WHERE `id` = 11 LIMIT 1; # start 36625 end 36812 time 2017-03-15 11:48:12

DELETE FROM `dbtest`.`t1` WHERE `id` = 3 LIMIT 1; # start 36062 end 36233 time 2017-03-15 11:47:45

DELETE FROM `dbtest`.`t1` WHERE `id` = 2 LIMIT 1; # start 36062 end 36233 time 2017-03-15 11:47:45

DELETE FROM `dbtest`.`t1` WHERE `id` = 1 LIMIT 1; # start 36062 end 36233 time 2017-03-15 11:47:45

IV. Knowledge expansion

1. The types of binlog and their advantages and disadvantages.

Reference:

(1) Statement: every sql that modifies the data is recorded in the binlog.

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO, and improve performance. (how much performance and log volume can be saved compared with row, which depends on the SQL of the application. The log volume generated by modifying or inserting the row format of the same record is still less than that generated by Statement, but considering that if the conditional update operation, as well as the whole table deletion, alter table and other operations, ROW format will generate a large number of logs, so when considering whether to use ROW format logs should be based on the actual situation of the application. How much more logs will be generated, and the resulting IO performance problems.)

Disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some information about the execution of each statement to ensure that all statements get the same results in slave as they are executed on the masterside. In addition, the replication of mysql, like some specific functions, slave can be consistent with the master will have a lot of related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) will have problems). Statements that use the following functions cannot be copied: LOAD_FILE (), UUID (), USER (), FOUND_ROWS (), SYSDATE () (unless the-- sysdate-is-now option is enabled at startup), and INSERT. SELECT produces more row-level locks than RBR

(2) Row: do not record sql statement context-related information, only save which record is modified.

Pros: binlog does not record context-sensitive information about executed sql statements, but only needs to record what that record has been modified to. So the log content of rowlevel will clearly record the details of each line of data modification. And there will be no problems that stored procedures, or function, and the calls and triggers of trigger can not be copied correctly in certain cases.

Disadvantages: when all executed statements are recorded in the log, they will be recorded as changes in each row, which may result in a large amount of log content, such as a update statement. If multiple records are modified, each change in binlog will be recorded, resulting in a large number of binlog logs, especially when executing statements such as alter table, each record will be changed due to table structure changes. Then each record in the table is recorded in the log.

(3) Mixed: is the mixed use of the above two kinds of level. General statement modification uses statment format to save binlog. For example, if some functions, statement cannot complete the master-slave copy operation, saving binlog,MySQL in row format will distinguish the log form of records according to each specific sql statement executed, that is, choose one between Statement and Row. The new version of MySQL Squadron row level mode is also optimized. Not all changes are recorded in row level, such as statement mode in the event of table structure changes. Statements that modify data, such as update or delete, still record changes to all rows.

2. Binlog-related configuration parameters

Log_bin: set this parameter to enable the binlog feature and specify the path name

Log_bin_index: set this parameter to specify the path and name of the binary index file

Binlog_do_db: this parameter indicates that only the binary log of the specified database is recorded

Binlog_ignore_db: this parameter indicates that the binary log of the specified database is not recorded

Max_binlog_cache_size: this parameter indicates the maximum size of memory used by binlog

Binlog_cache_size: this parameter represents the amount of memory used by binlog and can be tested through the state variables binlog_cache_use and binlog_cache_disk_use.

Binlog_cache_use: number of transactions that use binary log caching

Binlog_cache_disk_use: the number of transactions that use binary log caching but exceed the binlog_cache_ size value and use temporary files to save statements in the transaction

The maximum, maximum and default value of max_binlog_size:Binlog is 1GB. This setting does not strictly control the size of Binlog, especially when Binlog is close to the maximum and encounters a larger transaction. In order to ensure the integrity of the transaction, it is impossible to switch logs, so all SQL of the transaction can only be recorded in the current log until the end of the transaction.

Sync_binlog: this parameter directly affects the performance and integrity of mysql. Sync_binlog=0 says that when a transaction commits, Mysql simply writes data from binlog_cache to the Binlog file, but does not execute disk synchronization instructions such as fsync to tell the file system to flush the cache to disk, leaving Filesystem to decide when to synchronize, which is the best performance. Sync_binlog=n, after n transaction commits, Mysql will execute a disk synchronization instruction such as fsync, and the gay file system flushes the Binlog file cache to disk. The default setting in Mysql is sync_binlog=0, that is, no mandatory disk refresh instructions are made, which is the best performance, but also the greatest risk. Once the system tightens the Crash, all Binlog information in the file system cache will be lost

Binlog_format:binlog log format, statement/row/mixed

Binlog_row_image: there are parameters after 5.6. FULL records the changes to each line, and minimal records only the affected rows, provided that the row mode.

3. Binlog deletion method

Reset master; # # Delete the binlog of master

Reset slave; # # Delete the relay log of slave, reset slave all

Purge master logs before '2016-03-15 10 binlog log files in the log index before the specified date

Purge master logs to 'binlog.000001'; # # Delete the binlog log file in the log index of the specified log file

4. Mysqlbinlog parses the binlog log of row mode

Mysqlbinlog-- base64-output=decode-rows-v-- start-datetime='2016-07-13 10 start-datetime='2016 00'-- stop-datetime='2016-07-13 10 10 start-datetime='2016 40lo 00' mysql-bin.000021

5. Contents of master-slave binlog and relaylog files

Main library:

File: binlog.007247 # # the binlog file currently written by the main library

Position: 132941350 # # binlog file position currently written to the main library

From the library:

Master_Log_File: binlog.007247 # # the main library binlog file currently read from the library io thread

Read_Master_Log_Pos: 100309902 # # position of the main library binlog file currently read from library io thread

Relay_Log_File: mysql-relay-bin.025950 # # the relay-log file currently written from the library SQL thread

Relay_Log_Pos: 29008629 # # position of the replay-log file currently written from the library SQL thread

Relay_Master_Log_File: binlog.007243 # # the main library binlog file corresponding to the currently executed replay-log

Exec_Master_Log_Pos: 565883515 # # position of the currently executed binlog file

Relay_Log_Space: 4395318217 # # relay-log interval between read and execution

Cat relay-log.info # # relay-log executes binlog information

. / mysql-relay-bin.025950 # # the relay-log file currently written by SQL thread

29008629 # # position of the relay-log file currently written by SQL thread

Binlog.007243 # # the binlog file corresponding to the currently executed relay-log file

565883515 # # position of the binlog file corresponding to the currently executed relay-log file

Cat master.info # # Master-Slave synchronization Information record File

Binlog.007247 # # currently read the main library binlog file

372008635 # # position corresponding to the main library binlog file currently read

This is the end of this article on "how to use binlog2sql, a sharp tool for Binglog analysis in MYSQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.