yinshi_2 发表于 2011-5-16 17:22:35

Mysql Bin log解析

1)mysql -uroot -p -e "show binlog events \G" >b.sql
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
      Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.49-community-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
      Pos: 106
Event_type: Query
Server_id: 1
End_log_pos: 173
       Info: BEGIN
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
      Pos: 173
Event_type: Query
Server_id: 1
End_log_pos: 428
       Info: use `adm`; INSERT INTO `t_cons_consumer` (`id`,`consumer_uri`, `provider_id`, `name`, `gender`, `birthday`, `language`,`region`, `zipcode`) VALUES ('222', 'bbb', 'bb', 'bb', 'bb', 'bb', 'bb','b', 'bb')
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
      Pos: 428
Event_type: Xid
Server_id: 1
End_log_pos: 455
       Info: COMMIT
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
      Pos: 455
Event_type: Query
Server_id: 1
End_log_pos: 522
       Info: BEGIN
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
      Pos: 522
Event_type: Query
Server_id: 1
End_log_pos: 626
       Info: use `adm`; INSERT INTO `t_cons_consumer` () VALUES ()
*************************** 7. row ***************************
   Log_name: mysql-bin.000001
      Pos: 626
Event_type: Xid
Server_id: 1
End_log_pos: 653
       Info: COMMIT

2)mysqlbinlog mysql-bin.000001 >a.sql
;
;
DELIMITER ;
# at 4
#100910 10:20:13 server id 1end_log_pos 106Start: binlog v 4, server v 5.1.49-community-log created 100910 10:20:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK;
BINLOG '
3ZWJTA8BAAAAZgAAAGoAAAABAAQANS4xLjQ5LWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADdlYlMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
';
# at 106
#100910 10:21:35 server id 1end_log_pos 173Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085295;
SET @@session.pseudo_thread_id=1;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=0;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
BEGIN
;
# at 173
#100910 10:21:35 server id 1end_log_pos 428Query thread_id=1 exec_time=0 error_code=0
use adm;
SET TIMESTAMP=1284085295;
INSERT INTO `t_cons_consumer` (`id`, `consumer_uri`, `provider_id`, `name`, `gender`, `birthday`, `language`, `region`, `zipcode`) VALUES ('222', 'bbb', 'bb', 'bb', 'bb', 'bb', 'bb', 'b', 'bb')
;
# at 428
#100910 10:21:35 server id 1end_log_pos 455Xid = 11
COMMIT;
# at 455
#100910 10:21:38 server id 1end_log_pos 522Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085298;
BEGIN
;
# at 522
#100910 10:21:38 server id 1end_log_pos 626Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085298;
INSERT INTO `t_cons_consumer` () VALUES ()
;
# at 626
#100910 10:21:38 server id 1end_log_pos 653Xid = 13
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
" target=_blank>COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

c:\work\mysql\data>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.49-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master logs \G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
File_size: 653
1 row in set (0.01 sec)
ERROR:
No query specified
mysql>1)mysql -uroot -p -e "show binlog events \G" >b.sql
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
      Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.49-community-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
      Pos: 106
Event_type: Query
Server_id: 1
End_log_pos: 173
       Info: BEGIN
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
      Pos: 173
Event_type: Query
Server_id: 1
End_log_pos: 428
       Info: use `adm`; INSERT INTO `t_cons_consumer` (`id`,`consumer_uri`, `provider_id`, `name`, `gender`, `birthday`, `language`,`region`, `zipcode`) VALUES ('222', 'bbb', 'bb', 'bb', 'bb', 'bb', 'bb','b', 'bb')
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
      Pos: 428
Event_type: Xid
Server_id: 1
End_log_pos: 455
       Info: COMMIT
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
      Pos: 455
Event_type: Query
Server_id: 1
End_log_pos: 522
       Info: BEGIN
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
      Pos: 522
Event_type: Query
Server_id: 1
End_log_pos: 626
       Info: use `adm`; INSERT INTO `t_cons_consumer` () VALUES ()
*************************** 7. row ***************************
   Log_name: mysql-bin.000001
      Pos: 626
Event_type: Xid
Server_id: 1
End_log_pos: 653
       Info: COMMIT

2)mysqlbinlog mysql-bin.000001 >a.sql
;
;
DELIMITER ;
# at 4
#100910 10:20:13 server id 1end_log_pos 106Start: binlog v 4, server v 5.1.49-community-log created 100910 10:20:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK;
BINLOG '
3ZWJTA8BAAAAZgAAAGoAAAABAAQANS4xLjQ5LWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADdlYlMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
';
# at 106
#100910 10:21:35 server id 1end_log_pos 173Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085295;
SET @@session.pseudo_thread_id=1;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=0;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
BEGIN
;
# at 173
#100910 10:21:35 server id 1end_log_pos 428Query thread_id=1 exec_time=0 error_code=0
use adm;
SET TIMESTAMP=1284085295;
INSERT INTO `t_cons_consumer` (`id`, `consumer_uri`, `provider_id`, `name`, `gender`, `birthday`, `language`, `region`, `zipcode`) VALUES ('222', 'bbb', 'bb', 'bb', 'bb', 'bb', 'bb', 'b', 'bb')
;
# at 428
#100910 10:21:35 server id 1end_log_pos 455Xid = 11
COMMIT;
# at 455
#100910 10:21:38 server id 1end_log_pos 522Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085298;
BEGIN
;
# at 522
#100910 10:21:38 server id 1end_log_pos 626Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085298;
INSERT INTO `t_cons_consumer` () VALUES ()
;
# at 626
#100910 10:21:38 server id 1end_log_pos 653Xid = 13
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
" target=_blank>COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

c:\work\mysql\data>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.49-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master logs \G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
File_size: 653
1 row in set (0.01 sec)
ERROR:
No query specified
mysql>

likydba 发表于 2011-7-29 23:42:06

你想说明什么？
页: [1]
查看完整版本: Mysql Bin log解析