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