(2) Mysqlbinlog precise extraction of SQL statements

Command:

mysqlbinlog-d oldgirl mysql-bin.000046 mysqlbinlog--start-position=442--stop-position=549 mysql-bin.000046 Mysqlbinlog--start-datetime= "2016-04-11 21:42:45"--stop-datetime= "2016-04-11 21:43:22" mysql-bin.000046

1 Mysqlbinlog log logs for all libraries

Such as:

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#160411 21:00:35 Server ID 1 end_log_pos 107 start:binlog v 4, Server v 5.5.32-log created 160411 21:00:35 at startup

# Warning:this Binlog is either on use or was not closed properly.

rollback/*!*/;

BINLOG '

858lvw8baaaazwaaagsaaaabaaqans41ljmylwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaadznwtxezgnaagaegaebaqeegaavaaegggaaaaicagcaa==

‘/*!*/;

# at 107

#160411 21:01:16 Server ID 1 end_log_pos 194 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379676/*!*/;

SET @ @session. pseudo_thread_id=2/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=0/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c latin1 *//*!*/;

SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

Create DATABASE Oldboy

/*!*/;

# at 194

#160411 21:03:34 Server ID 1 end_log_pos 344 Query thread_id=2 exec_time=0 error_code=0

Use oldboy/*!*/;

SET timestamp=1460379814/*!*/;

CREATE TABLE student (ID int (4) Auto_increment primary key, name varchar (+) NOT NULL)

/*!*/;

# at 344

#160411 21:04:53 Server ID 1 end_log_pos 414 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379893/*!*/;

BEGIN

/*!*/;

# at 414

#160411 21:04:53 Server ID 1 end_log_pos 442 intvar

SET insert_id=1/*!*/;

# at 442

#160411 21:04:53 Server ID 1 end_log_pos 549 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379893/*!*/;

INSERT into student (name) VALUES (' Andy ')

/*!*/;

# at 549

#160411 21:04:53 Server ID 1 end_log_pos 576 Xid = 13

commit/*!*/;

# at 576

#160411 21:04:57 Server ID 1 end_log_pos 646 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379897/*!*/;

BEGIN

/*!*/;

# at 646

#160411 21:04:57 Server ID 1 end_log_pos 674 intvar

SET insert_id=2/*!*/;

# at 674

#160411 21:04:57 Server ID 1 end_log_pos 781 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379897/*!*/;

INSERT into student (name) VALUES (' Jack ')

/*!*/;

# at 781

#160411 21:04:57 Server ID 1 end_log_pos 808 Xid = 14

commit/*!*/;

# at 808

#160411 21:42:02 Server ID 1 end_log_pos 897 Query thread_id=3 exec_time=0 error_code=0

SET timestamp=1460382122/*!*/;

Create DATABASE Oldgirl

/*!*/;

# at 897

#160411 21:42:45 Server ID 1 end_log_pos 1048 Query thread_id=3 exec_time=0 error_code=0

Use oldboy/*!*/;

SET timestamp=1460382165/*!*/;

CREATE TABLE new_student (ID int auto_increment PRIMARY key, name varchar (+) NOT NULL)

/*!*/;

# at 1048

#160411 21:43:22 Server ID 1 end_log_pos 1118 Query thread_id=3 exec_time=0 error_code=0

SET timestamp=1460382202/*!*/;

BEGIN

/*!*/;

# at 1118

#160411 21:43:22 Server ID 1 end_log_pos 1146 intvar

SET insert_id=1/*!*/;

# at 1146

#160411 21:43:22 Server ID 1 end_log_pos 1257 Query thread_id=3 exec_time=0 error_code=0

SET timestamp=1460382202/*!*/;

Insert into New_student (name) VALUES (' Lili ')

/*!*/;

# at 1257

#160411 21:43:22 Server ID 1 end_log_pos 1284 Xid = 22

commit/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

Command 1: Specify the Library

-D,--database=name List entries for just the database (local log only).

Example:

mysqlbinlog-d Oldgirl mysql-bin.000046 > Oldgirl.sql

[email protected] mysql]# cat Oldgirl.sql

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#160411 21:00:35 Server ID 1 end_log_pos 107 start:binlog v 4, Server v 5.5.32-log created 160411 21:00:35 at startup

# Warning:this Binlog is either on use or was not closed properly.

rollback/*!*/;

BINLOG '

858lvw8baaaazwaaagsaaaabaaqans41ljmylwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaadznwtxezgnaagaegaebaqeegaavaaegggaaaaicagcaa==

‘/*!*/;

# at 107

# at 194

# at 344

#160411 21:04:53 Server ID 1 end_log_pos 414 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379893/*!*/;

SET @ @session. pseudo_thread_id=2/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=0/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c latin1 *//*!*/;

SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

BEGIN

/*!*/;

# at 414

#160411 21:04:53 Server ID 1 end_log_pos 442 intvar

SET insert_id=1/*!*/;

# at 442

# at 549

#160411 21:04:53 Server ID 1 end_log_pos 576 Xid = 13

commit/*!*/;

# at 576

#160411 21:04:57 Server ID 1 end_log_pos 646 Query thread_id=2 exec_time=0 error_code=0

SET timestamp=1460379897/*!*/;

BEGIN

/*!*/;

# at 646

#160411 21:04:57 Server ID 1 end_log_pos 674 intvar

SET insert_id=2/*!*/;

# at 674

# at 781

#160411 21:04:57 Server ID 1 end_log_pos 808 Xid = 14

commit/*!*/;

# at 808

#160411 21:42:02 Server ID 1 end_log_pos 897 Query thread_id=3 exec_time=0 error_code=0

SET timestamp=1460382122/*!*/;

Create DATABASE Oldgirl

/*!*/;

# at 897

# at 1048

#160411 21:43:22 Server ID 1 end_log_pos 1118 Query thread_id=3 exec_time=0 error_code=0

SET timestamp=1460382202/*!*/;

BEGIN

/*!*/;

# at 1118

#160411 21:43:22 Server ID 1 end_log_pos 1146 intvar

SET insert_id=1/*!*/;

# at 1146

# at 1257

#160411 21:43:22 Server ID 1 end_log_pos 1284 Xid = 22

commit/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

Command 2: Specify the location range

--start-position

--stop-position

I just want to extract the Binlog that contains the INSERT into student (name) VALUES (' Andy ') statement

Mysqlbinlog--start-position=442--stop-position=549 mysql-bin.000046

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#160411 21:00:35 Server ID 1 end_log_pos 107 start:binlog v 4, Server v 5.5.32-log created 160411 21:00:35 at startup

# Warning:this Binlog is either on use or was not closed properly.

rollback/*!*/;

BINLOG '

858lvw8baaaazwaaagsaaaabaaqans41ljmylwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaadznwtxezgnaagaegaebaqeegaavaaegggaaaaicagcaa==

‘/*!*/;

# at 442

#160411 21:04:53 Server ID 1 end_log_pos 549 Query thread_id=2 exec_time=0 error_code=0

Use oldboy/*!*/;

SET timestamp=1460379893/*!*/;

SET @ @session. pseudo_thread_id=2/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=0/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c latin1 *//*!*/;

SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

INSERT into student (name) VALUES (' Andy ')

/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

Command three: Use time to specify (but the time is not accurate 1s clock can be many statements)

--start-datetime

--stop-datetime (statement will not be included in stop at this point in time is the cutoff line)

Mysqlbinlog--start-datetime= "2016-04-11 21:42:45"--stop-datetime= "2016-04-11 21:43:22" mysql-bin.000046

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#160411 21:00:35 Server ID 1 end_log_pos 107 start:binlog v 4, Server v 5.5.32-log created 160411 21:00:35 at startup

# Warning:this Binlog is either on use or was not closed properly.

rollback/*!*/;

BINLOG '

858lvw8baaaazwaaagsaaaabaaqans41ljmylwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaadznwtxezgnaagaegaebaqeegaavaaegggaaaaicagcaa==

‘/*!*/;

# at 897

#160411 21:42:45 server ID 1 end_log_pos 1048 Query thread_id=3 exec_time=0 error_code=0

Use oldboy/*!*/;

SET timestamp=1460382165/*!*/;

SET @ @session. pseudo_thread_id=3/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=0/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c latin1 *//*!*/;

SET @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

CREATE TABLE new_student (ID int auto_increment PRIMARY key, name varchar (+) NOT NULL)

/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

This article is from the "Tridewah operation and maintenance work Road" blog, please be sure to keep this source http://cuidehua.blog.51cto.com/5449828/1762780

(2) Mysqlbinlog precise extraction of SQL statements