Analysis of frm files (Continuous updates) and mysqlbingfrm files to solve the problem of mysqlbing translation table fields

Analysis of frm files (Continuous updates) and mysqlbingfrm files to solve the problem of mysqlbing translation table fields

Source: kelvin19840813 blog http://www.cnblogs.com/kelvin19840813/

Your support is the greatest encouragement to bloggers. Thank you for reading it carefully. This article is copyrighted by the author. You are welcome to repost it, but please keep this statement.

 

Many people know that mysqlbinlog interprets the binlog insert. update is the opposite, and the table fields are all @ 1... @ numbers. This is disgusting !!!

But I didn't see any questions raised when I asked for the mysql8.0 feature.

Only by interpreting the frm file, obtaining the table field name and field, and then posting the SQL statement that translates the binlog, this is not a deep topic. It is only a log.

0000 ~ 0001: FE 01 identify frm files, fixed

0002: 0A is the location identified by mysql version. cc FRM_VER + 3 + MY_TEST (create_info-> varchar); 6 is mysql 5.0 +, 9 or 10 is a frm file with varchar

0003: 0C ing the enumerated values from the handler. h file legacy_db_type. 0C is 12, which is exactly the innodb type, as shown below:

Enum legacy_db_type
{
DB_TYPE_UNKNOWN = 0, DB_TYPE_DIAB_ISAM = 1,

......, DB_TYPE_INNODB,
......
};

0004 ~ 0005: 03 00. On the contrary, mysql 5.0 + is always 3. If not 3, forget it .....

0006 ~ 0007: 00 10. Check 10 00 IO_SIZE: 4096.

0008 ~ 0009: 01 00 what is unknown

000a ~ 000d: 00 40 00 00 unknown

000e ~ 000f: F9 16 tmp_key_length; if it is equal to 0xffff, then the key length is 4-byte integer offset 0x002f

0010 ~ 0011: 18 08 rec_length; this is a default value for storing string bytes

0012 ~ 0015: 00 00 00 is the MAX_ROWS option of the create table statement.

0016 ~ 0019: 00 00 00 is the min_rows option of the create table statement, In the handler. h file

 

Typedef struct st_ha_create_information
{

.......

Ulonglong max_rows, min_rows;

...}

 

001a: 00 useless

001b: 02 always 2, use the long package Field

001c ~ 001d: A3 02 key_info_length-keyinfo Part Size

001e ~ 001f: 69 00 is a variable such as HA_OPTION _ * in the my_base.h file, which is the value of create table [table_option ].

0020: useless

0021: 05 is the version number of the frm File

0022 ~ 0025: 00 00 00 00 is the avg_row_length specified by create table [table_option]

0026: create table [table_option] specifies the character set encoding [DEFAULT] CHARACTER SET

0027: 00 unknown

0028: 02 is the ROW_FORMAT of create table [table_option]

Rem0types. h file

Enum rec_format_enum {

REC_FORMAT_REDUNDANT = 0 ,/*! <REDUNDANT row format */
REC_FORMAT_COMPACT = 1 ,/*! <COMPACT row format */
REC_FORMAT_COMPRESSED = 2 ,/*! <COMPRESSED row format */
REC_FORMAT_DYNAMIC = 3 /*! <DYNAMIC row format */
};

0029: 00 RAID_TYPE mysql 5 before the create table [table_option], I have not studied why.

002a: 00 create table [table_option] Before RAID_CHUNKS mysql 5. Why not?

002b ~ 002e: 00 00 00 00 RAID_CHUNKSIZE create table [table_option] Before mysql 5. Why didn't I study it?

002f ~ 0032: F9 16 00 00 index metadata keyinfo part bytes size

0033 ~ 0036: BF C5 00 00 mysql version, which involves a bytecode problem. The machine is x86, so it is Little-Endian. The link is as follows:

Http://baike.baidu.com/link? Url = Dd2Z3o5aNoUYQPG6xqIgNwr-jrNS8BTK72r42WVxr-SHhva4TnMsHrEXTcE_N4F6ZEhhe18Akg3hOBe2Eb-M9K

All are unsigned int (4 bytes), little-endian

Generate MYSQL_VERSION_MAJOR, MYSQL_VERSION_MINOR, and MYSQL_VERSION_PATCH

0037 ~ 003a: 33 00 00 00 create table [table_option] extra_info Information

• CONNECTION
• ENGINE
• PARTITION
• WITH PARSER
• Table COMMENT

003b ~ 003c: 00 00 extra_rec_buf_length

003d: 00 if it is 0C, It is a partition table. In the handler. h file, DB_TYPE_PARTITION_DB = 12

003e ~ 003f: 00 00 create table [table_option] KEY_BLOCK_SIZE in the ulong key_block_size of handler. h file;

Next, locate forminfo_offset, which is the start position of the table field.

 

1. Prepare knowledge in advance, struct

Format	C	Python	Bytes
X	Pad byte	No value	1
C	Char	String of length 1	1
B	Signed char	Integer	1
B	Unsigned char	Integer	1
?	_ Bool	Bool	1
H	Short	Integer	2
H	Unsigned short	Integer	2
I	Int	Integer	4
I	Unsigned int	Integer or long	4
L	Long	Integer	4
L	Unsigned long	Long	4
Q	Long	Long	8
Q	Unsigned long	Long	8
F	Float	Float	4
D	Double	Float	8
S	Char []	String	1
P	Char []	String	1
P	Void *	Long

2. little-endian & big-endian. Generally, x86 is little-endian.

The following are fixed lengths. The problem is to find forminfo_offset, metadata_length, and metadata_offset:

Forminfo_offset indicates the start position of the table field,Variable

Metadata_offset = forminfo_offset + forminfo_length + screens_length

Metadata_length = column_count * 17 bytes: distance from the first field in the table + metadata_offset

Forminfo_length = 288
Header_size = 64

Forminfo_names_length = File Location offset: 0x0004 (H). Versions later than 3.23 are 3, accounting for 2 bytes.

Column_count = (forminfo_offset + 258 )(H) Total number of fields, accounting for 2 bytes
The number of bytes of the screens_length field metadata that reaches forminfo, accounting for 2 bytes.
Screens_length = (forminfo_offset + 260 )(H)

Forminfo_offset: 0x3000

Metadata_offset: 0x328C

Metadata_length: 0x3446

After positioning 0x3446, you can know the location of the table's 1st fields and convert the binary into characters. php and python both have the struct pack and unpack functions. c is a little more troublesome, use 0xff as the field name as the separator:

 

 

Index information and comment information to be supplemented tomorrow ............