Mysql Bin log解析

yinshi_2

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 1  end_log_pos 106  Start: 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 1  end_log_pos 173  Query 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 1  end_log_pos 428  Query 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 1  end_log_pos 455  Xid = 11
COMMIT;
# at 455
#100910 10:21:38 server id 1  end_log_pos 522  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085298;
BEGIN
;
# at 522
#100910 10:21:38 server id 1  end_log_pos 626  Query 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 1  end_log_pos 653  Xid = 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 1  end_log_pos 106  Start: 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 1  end_log_pos 173  Query 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 1  end_log_pos 428  Query 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 1  end_log_pos 455  Xid = 11
COMMIT;
# at 455
#100910 10:21:38 server id 1  end_log_pos 522  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1284085298;
BEGIN
;
# at 522
#100910 10:21:38 server id 1  end_log_pos 626  Query 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 1  end_log_pos 653  Xid = 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

你想说明什么？