Mysql Binary Log (2)

如果binlog_format是為ROW的，可以用--base64-output=DECODE-ROWS引數。

（--verbose, -v

 Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata.）

但是如果使用了該引數，start-position等將無法正常使用。

[root@testdb arch]# mysqlbinlog -v --start-position=422 --base64-output=DECODE-ROWS mysql-bin.000017

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 422

#130226 14:26:01 server id 1  end_log_pos 449   Xid = 13

COMMIT/*!*/;

# at 449

#130226 14:26:09 server id 1  end_log_pos 517   Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1361859969/*!*/;

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

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 utf8 *//*!*/;

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

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

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

BEGIN

/*!*/;

# at 517

ERROR: malformed binlog: it does not contain any Format_description_log_event. I now found a Table_map event, which is not safe to process without a Format_description_log_event.

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

去掉--base64-output=DECODE-ROWS後正常：

[root@testdb arch]# mysqlbinlog --start-position=449  mysql-bin.000017

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#130226 14:18:11 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.25a-log created 130226 14:18:11

BINLOG '

o1MsUQ8BAAAAZwAAAGsAAAAAAAQANS41LjI1YS1sb2cAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

# at 449

#130226 14:26:09 server id 1  end_log_pos 517   Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1361859969/*!*/;

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

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 utf8 *//*!*/;

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

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

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

BEGIN

/*!*/;

# at 517

# at 561

#130226 14:26:09 server id 1  end_log_pos 561   Table_map: `test`.`tb1` mapped to number 33

#130226 14:26:09 server id 1  end_log_pos 593   Write_rows: table id 33 flags: STMT_END_F

BINLOG '

gVUsURMBAAAALAAAADECAAAAACEAAAAAAAEABHRlc3QAA3RiMQABDwIyAAE=

gVUsURcBAAAAIAAAAFECAAAAACEAAAAAAAEAAf/+AWM=

'/*!*/;

# at 593

#130226 14:26:09 server id 1  end_log_pos 620   Xid = 14

COMMIT/*!*/;

# at 620

#130226 14:31:05 server id 1  end_log_pos 663   Rotate to mysql-bin.000018  pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

關於stop-position等的解釋：

start-position=bytepos

The byte position of the first event to dump. Note that if several binlog files are

supplied to mysqlbinlog, this position will be interpreted as the position in the

first file in the sequence.

If an event does not start at the position given, mysqlbinlog will still try to interpret the bytes starting at that position as an event, which usually leads to garbage output.

stop-position=bytepos

The byte position of the last event to print. If no event ends at that position, the last event printed will be the event with a position that precedes bytepos. If multiple binlog files are given, the position will be the position of the last file in the sequence.

start-datetime=datetime

Prints only events that have a timestamp at or after datetime. This will work correctly when multiple files are given—if all events of a file are before the datetime,all events will be skipped—but there is no checking that the events are printed in order according to their timestamps.

stop-datetime=datetime

Prints only events that have a timestamp before datetime. This is an exclusive range,

meaning that if an event is marked 2010-01-24 07:58:32 and that exact datetime is

given, the event will not be printed.

Note that since the timestamp of the event uses the start time of the statement but

events are ordered in the binary log based on the commit time, it is possible to have

events with a timestamp that comes before the timestamp of the preceding event.

Since mysqlbinlog stops at the first event with a timestamp outside the range, there

might be events that aren’t displayed because they have timestamps before

datetime.

Events common header:

Timestamp

The timestamp of the event as an integer, stored in little-endian format.

Type

A single byte representing the type of the event. The event types in MySQL version

5.1.41 and later are given in the MySQL Internals guide.

Master ID

The server ID of the server that wrote the event, written as an integer. For the event

shown in Example 3-17, the server ID is 1.

Size

The size of the event in bytes, written as an integer.

Master Pos

The same as end_log_pos; that is, the start of the event following this event.

Flags

This field has 16 bits reserved for general flags concerning the event. The field is

mostly unused, but it stores the binlog-in-use flag. As you can see in Example

3-17, the binlog-in-use flag is set, meaning that the binary log is not closed

properly (in this case, because we didn’t flush the logs before calling mysqlbinlog).

After the common header come the post header and body for the event. As already

mentioned, an exhaustive coverage of all the events is beyond the scope of this book,

but we will cover the most important and commonly used events: the Query and

Format_description log events.

Query event post header and body

Query event post header and body

The Query event is by far the most used and also the most complicated event issued by the server. Part of the reason is that it has to carry a lot of information about the context of the statement when it was executed. As already demonstrated, integer variables, user variables, and random seeds are covered using specific events, but it is also necessary to provide other information, which is part of this event.

The post header for the Query event consists of five fields. Recall that these fields are of fixed size and that the length of the post header is given in the Format description event for the binlog file, meaning that later MySQL versions may add additional fields if the need should arise.

Thread ID

A four-byte unsigned integer representing the thread ID that executed the statement.

Even though the thread ID is not always necessary to execute the statement

correctly, it is always written into the event.

Execution time

The number of seconds from the start of execution of the query to when it was

written to the binary log, expressed as a four-byte unsigned integer.

Database name length

The length of the database name, stored as an unsigned one-byte integer. The

database name is stored in the event body, but the length is given here.

Error code

The error code resulting from execution of the statement, stored as a two-byte

unsigned integer. This field is included because, in some cases, statements have to

be logged to the binary log even when they fail.

Status variables length

The length of the block in the event body storing the status variables, stored as a

two-byte unsigned integer. This status block is sometimes used with a Query event

to store various status variables, such as SQL_MODE.

The event body consists of the following fields, which are all of variable length.

Status variables

A sequence of status variables. Each status variable is represented by a single integer

followed by the value of the status variable. The interpretation and length of each

status variable value depends on which status variable it concerns. Status variables

are not always present; they are added only when necessary. Some examples of

status variables follow:

Q_SQL_MODE_CODE

The value of SQL_MODE used when executing the statement.

Q_AUTO_INCREMENT

This status variable contains the values of auto_increment_increment and

auto_increment_offset used for the statement, assuming that they are not the

default of 1.

Q_CHARSET

This status variable contains the character set code and collation used by the

connection and the server when the statement was executed.

Current database

The name of the current database, stored as a null-terminated string. Notice that

the length of the database name is given in the post header.

Statement text

The statement that was executed. The length of the statement can be computed

from the information in the common header and the post header. This statement

is normally identical to the original statement written, but in some cases, the statement

is rewritten before it is stored in the binary log. For instance, as you saw earlier

in this chapter , triggers and stored procedures are stored with DEFINER clauses

specified.