MySQL Binlog(九)——MySQL字段存储格式——日期类型字段类型

Posted on 2019-12-07 Edited on 2022-02-14 In MySQL

MySQL Binlog(九)——MySQL字段存储格式——日期类型字段类型

前言

本文继续介绍binlog中日期类型字段的存储格式。

日期型字段类型

DATE,DATETIME,TMIESTAMP类型

DATE

日期。支持的范围是‘1000-01-01’到‘9999-12-31’。MySQL使用’YYYY-MM-DD’格式显示DATE值。但允许使用字符串或数字给DATE列赋值。

DATETIME[(fsp)]

日期和时间的组合。支持的范围是‘1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999’。MySQL使用’YYYY-MM-DD HH:MM:SS[.fraction]’格式显示DATETIME值，但允许使用字符串或数字给DATETIME列赋值。

从MySQL5.6.4开始，一个可选的范围从0到6的fsp值可以指定秒数的精度。值为0表示没有小数部分。如果省略的话，默认精度为0.

从MySQL5.6.5开始，自动化初始和更新到当前日期时间，可以使用DATETIME列的DEFAULT和ON UPDATE定义项。

TIMESTAMP[(fsp)]

时间戳。范围从’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。TIMESTAMP存储从纪元(‘1970-01-01 00:00:00’UTC)至今的总秒数。

TIME类型

TIME[(fsp)]

时间。范围从’-838:59:59.000000’到’838:59:59.000000’。MySQL使用’HH:MM:SS[.fraction]’格式显示TIME值。但允许使用字符串或数字给TIME列赋值。

从MySQL5.6.4开始，一个可选的范围从0到6的fsp值可以指定秒数的精度。值为0表示没有小数部分。如果省略的话，默认精度为0。

YEAR类型

YEAR[(2|4)]

两位或四位格式的年。默认是四位格式，虽然显示上YEAR(2)或YEAR(4)的格式不同，但他们具有相同的范围的值。四位格式显示为1901至2155，和0000。两位格式显示为70至69，表示1970至2069。MySQL以为YYYY或YY格式显示YEAR值，但允许使用字符串或数字赋值。

日期型数据存储格式

解析环境描述

表结构定义

CREATE TABLE `time_table` (
  `col1` date DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` datetime(3) DEFAULT NULL,
  `col4` timestamp NULL DEFAULT NULL,
  `col5` timestamp(4) NULL DEFAULT NULL,
  `col6` time DEFAULT NULL,
  `col7` time(5) DEFAULT NULL,
  `col8` year(4) DEFAULT NULL,
  `col9` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

表数据展示

root@localhost : gangshen 07:04:33> insert into time_table values('2017-12-14','2017-12-14 09:54:00','2017-12-14 09:54:00.112','2017-12-14 09:54:00','2017-12-14 09:54:00.1113','09:54:00','09:54:00.00000','2017','2017');
Query OK, 1 row affected (0.03 sec)

root@localhost : gangshen 07:04:47> select * from time_table;                                                                                                          +------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
| col1      | col2                | col3                    | col4                | col5                    | col6    | col7          | col8 | col9 |
+------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
| 2017-12-14 | 2017-12-14 09:54:00 | 2017-12-14 09:54:00.112 | 2017-12-14 09:54:00 | 2017-12-14 09:54:00.1113 | 09:54:00 | 09:54:00.00000 | 2017 | 2017 |
+------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
1 row in set (0.00 sec)

然后从binlog文件中拿到，对应的Table_map_event和Writes_rows_event对应的字节内容，开始解析

元数据解析

Table_map_event字节数据解析

公共头部部分省略：
4e 00 00 00 00 00 //table_id :小端存储，16进制转换为10进制为78
01 00 //flag :
08 67 61 6e 67 73 68 65 6e 00 //database_name :1个字节是字符串长度，后接一个null-terminated string 第一个字节表示字符串长度为8，后面内容将16进制转换为ascii字符为gangshen
0a 74 69 6d 65 5f 74 61 62 6c 65 00 //table_name: 1个字节是字符串长度，后接一个null-terminated string第一个字节表示字符串长度为5，后面内容将16进制转换为ascii字符为time_table
09 //columns count :packet integer类型，转换之后，数值为9 表示表中有9个字段
0a 12 12 11 11 13 13 0d 0d //column type
06 //metadata_length: packet integer类型,转换之后，数值为9，表示记录表中的metadata内容占用9个字节
00 //col2
03 //col3
00 //col4
04 //col5
00 //col6
05 //col7
ff 01 //null_bits :int((column_count + 7) / 8)个字节 一个bit表示一个字段是否可以为null
38 9b 0a 26 //checksum

从Table_map_event中可以按照上面的讲述，解析出表中所有的字段类型以及对应的元数据，按照顺序分别是：

第一个字段：0x0a=MYSQL_TYPE_DATE 无元数据

第二个字段：0x12=MYSQL_TYPE_DATETIME2 元数据0x00表示该字段的时间精度为0

第三个字段：0x12=MYSQL_TYPE_DATETIME2 元数据0x03表示该字段的时间精度为3

第四个字段：0x11=MYSQL_TYPE_TIMESTAMP2 元数据0x00表示该字段的时间精度为0

第五个字段：0x11=MYSQL_TYPE_TIMESTAMP2 元数据0x04表示该字段的时间精度为4

第六个字段：0x13=MYSQL_TYPE_TIME2 元数据0x00表示该字段的时间精度为0

第七个字段：0x13=MYSQL_TYPE_TIME2 元数据0x05表示该字段的时间精度为5

第八个字段：0x0d=MYSQL_TYPE_YEAR 无元数据

第九个字段：0x0d=MYSQL_TYPE_YEAR 无元数据

日期型数据“值”解析

Write_rows_log_event字节数据解析

公共头部部分省略
4e 00 00 00 00 00 //table_id: 小端存储，16进制转换为10进制为78
01 00 //flag:
02 00 //var header length :小端存储，16进制转换为10进制为2
09 //m_width :packet integer，表示表中字段数量
ff ff //before image: (m_width + 7) / 8字节
00 fe //bitmap_bits :表中两个字段，插入的值都不为NULL
8e c3 0f //col1
99 9e 5c 9d 80 //col2
99 9e 5c 9d 80 04 60 //col3
5a 31 d9 b8 //col4
5a 31 d9 b8 04 59 //col5
80 9d 80 //col6
80 9d 80 00 00 00 //col7
75 //col8
75 //col9
7e da 55 ce //checksum

MYSQL_TYPE_DATE字段解析

MYSQL_TYPE_DATE类型使用3个字节存储，小端存储。3个字节一共24个比特位，其中从低到高（从右到左）前5位，表示日期，接着的从低到高4位表示月份，剩余的位数表示年份。

在上面的例子中，col1的类型就是MYSQL_TYPE_DATE，所以在Write_rows_log_event中占用3个字节的内容，为0x8ec30f，因为是小端存储，所以实际顺序为0x0fc38e,3个字节一共24个比特位，从右往左，5位表示日期，4位表示月份，15位表示年份。将0x0fc38e转换成二进制是b’0000 1111 1100 0011 1000 1110’，所以day=b’01110’=14，month=b‘1100’=12，year=’0000 1111 1100 001’=2017，所以对应的字段的值是‘2017-12-14’

MYSQL_TYPE_DATETIME2字段解析

MYSQL_TYPE_DATETIME2类型使用5个字节存储，并且根据时间精度的不同，额外需要不同的字节存储数据,时间精度部分为大端存储。

年月日时分秒的内容存储在基础的5个字节中，5个字节一共40位，从左往右前18位表示年月（年份 * 13 + 月份），其中最高位表示符号，最高位为1表示是正的，为0表示是负的，接着的5位表示日期，剩余的17位中，按照顺序，5位表示时，6位表示分，6位表示秒。因为时间精度最多为6位，一个字节表示两位，所以时间精度为1或者2的时候，使用一个额外的字节表示时间精度，时间精度为3或者4的时候，使用两个额外的字节表示时间精度，时间精度为5或者6的时候，使用三个额外的字节表示时间精度。

时间精度	所需字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3

在上面的例子中，col2和col3的类型就是MYSQL_TYPE_DATETIME2，因为col2的时间精度为0，所以col2在Write_rows_log_event中占用5个字节的内容，为0x999e5c9d80，因为col3的时间精度为3，所以col3在Write_rows_log_event中占用（5+2）个字节的内容，为0x999e5c9d800460。

我们先解析col2字段的值，从上面的描述中，5个字节（40位）内容，前18位表示年月（年份 * 13+月份），5位表示日期，5位表示时，6位表示分，6位表示秒。0x999e5c9d80转换为二进制是b‘1001 1001 1001 1110 0101 1100 1001 1101 1000 0000’，年月=b’1001 1001 1001 1110 01’ ，日期=b’01110’，时=b’01001’，

分=b’110110’，秒=b’000000’。年月中最高位符号位（正数最高位为1，负数最高位为0），所以在计算数值的时候，要排除最高位的影响（将基础的5个字节减去0x8000000000），即年月值的实际二进制为b’0001 1001 1001 1110 01’，转换为10进制为26233。年=26233/13，月=26233%13,日=b’01110’=14,时=b’01001’=9,分=b’110110’=54,秒=b’000000’=0。所以col2字段的值为2017-12-14 09:54:00

接着解析col3字段值，因为col2和col3字段的基础5个字节的内容一致，所以直接跳过，解析额外的时间精度部分，时间精度部分为0x0460,因为是大端存储，所以转换为10进制结果为1120，即时间精度部分为1120。所以col3字段的值为2017-12-14 09:54:00.1120

MYSQL_TYPE_TIMESTAMP2字段解析

MYSQL_TYPE_TIMESTAMP2类型使用4个字节存储,大端存储，并且根据时间精度的不同，额外需要不同的字存储数据，时间精度部分也使用大端存储；如果时间精度为1或者2，则4个字节之外还需要1个字节存储数据；如果时间精度为3或者4，则4个字节之外还需要2个字节存储数据；如果时间精度为5或者6，则4个字节之外还需要3个字节存储数据。

时间精度	所需字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3

在上面的例子中，col4和col5的类型就是MYSQL_TYPE_TIMESTAMP2，因为col4的时间精度为0，所以col4在Write_rows_log_event中占用4个字节的内容，为0x5a31d9b8，因为col5的时间精度为4，所以col5在Write_rows_log_event中占用（4+2）=6个字节，为0x5a31d9b80459。

我们先解析col4字段的值，4个字节，大端存储，所以0x5a31d9b8转换为10进制数为1513216440，即col4的时间戳为1513216440（时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数），转换为时间为2017-12-14 09:54:00

接着解析col5字段的值，因为col4和col5字段前4个字节内容相同，故不重新解析，直接解析时间精度部分，时间精度为0x0459，因为是大端存储，所以转换为10进制为1113，即时间精度部分为1113。所以col5字段的值为2017-12-14 09:54:00.1113

MYSQL_TYPE_TIME2字段解析

MYSQL_TYPE_TIME2类型使用3个字节存储,大端存储，并且根据时间精度的不同，额外需要不同的字节存储数据,时间精度部分使用大端存储。

时分秒的内容存储在基础的3个字节中，3个字节一共24位，从左往右，前12位表示时，中间6位表示分，最后6位表示秒，额外的字节存储秒的时间精度部分。因为时间精度最多为6位，一个字节表示两位，所以时间精度为1或者2的时候，使用一个额外的字节表示时间精度，时间精度为3或者4的时候，使用两个额外的字节表示时间精度，时间精度为5或者6的时候，使用三个额外的字节表示时间精度。

时间精度	所需字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3

MYSQL_TYPE_TIME2类型字段值可以为负数。那么MYSQL_TYPE_TIME2类型是如何存储正负值的呢。

没有时间精度

在没有时间精度的情况下，MYSQL_TYPE_TIME2类型使用3个字节存储时分秒内容(大端存储)。MySQL使用0x800000作为时间原点（00:00:00），比0x800000大的为正的时间值，比0x800000小的为负的时间值。即时分秒的内容是与0x800000做减法之后的绝对值。

时间精度为1或者2

在时间精度为1或者2的情况下，MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储)，使用一个额外的字节存储时间精度。MySQL使用0x80000000作为时间原点（00:00:00.00），比0x80000000大的为正的时间值，比0x80000000小的为负的时间值，即时分秒的即时间精度的内筒是与0x80000000做减法之后的绝对值。

时间精度为3或者4

在时间精度为1或者2的情况下，MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储)，使用两个额外的字节存储时间精度。MySQL使用0x8000000000作为时间原点（00:00:00.0000），比0x8000000000大的为正的时间值，比0x8000000000小的为负的时间值，即时分秒的即时间精度的内筒是与0x8000000000做减法之后的绝对值。

时间精度为5或者6

在时间精度为1或者2的情况下，MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储)，使用三个额外的字节存储时间精度。MySQL使用0x800000000000作为时间原点（00:00:00.000000），比0x800000000000大的为正的时间值，比0x800000000000小的为负的时间值，即时分秒的时间精度是与0x800000000000做减法之后的绝对值。

在上面的例子中，col6和col7的类型就是MYSQL_TYPE_TIME2，因为col6的时间精度为0，所以col6在Write_rows_log_event中占用3个字节的内容，为0x809d80，因为col7的时间精度为5，所以col7在Write_rows_log_event中占用（3+3）=6个字节的内容，为0x809d80000000。

我们先解析col6的值，按照上面的描述，col6字段类型为MYSQL_TYPE_TIME2，时间精度为0，所以使用3个字节存储内容，在Write_rows_log_event中占用3个字节的内容，为0x809d80。因为时间精度为0的情况下，MySQL是以0x800000为时间原点，具体的时分秒为0x809d80与0x800000差值的绝对值=0x009d80。且0x809d80比0x800000大，所以字段值符号为正。时分秒=0x009d80=b‘0000 0000 1001 1101 1000 0000’，时=前12位=b’0000 0000 1001’=9，分=中间6位=b’1101 10’=54，秒=最后6位=b’00 0000’=0，即col6字段的值为09:54:00

接着解析col7的值，按照上面的描述，col7字段类型为MYSQL_TYPE_TIME2，时间精度为5，所以使用3+3=6个字节存储内容，在Write_rows_log_event中占用6个字节的内容，为0x809d80000000，因为时间精度为5的情况下，MySQL是以0x800000000000为时间原点，具体的时分秒以及时间精度存储为0x809d80000000与0x800000000000差值的绝对值=0x009d80000000。且0x809d80000000比0x800000000000大，所以字段值符号为正。时分秒=0x009d80=b‘0000 0000 1001 1101 1000 0000’，时=前12位=b’0000 0000 1001’=9，分=中间6位=b’1101 10’=54，秒=最后6位=b’00 0000’=0，时间精度=0x000000=0，即col7字段的值为09:54:00.00000

MYSQL_TYPE_YEAR字段解析

MYSQL_TYPE_DATA类型使用1个字节存储。表示的值是（要表示的年份-1900）

在上面的例子中，col8和col9的类型就是MYSQL_TYPE_YEAR，col8和col9都在Write_rows_log_event中占用1个字节的内容，分别为0x75和0x75，转换为10进制为117，将结果加上1900，则最终col8和col9字段的值为2017。

MySQL Binlog(八)——MySQL字段存储格式——数值类型字段类型

Posted on 2019-12-07 Edited on 2022-02-14 In MySQL

MySQL Binlog(八)——MySQL字段存储格式——数值类型字段类型

前言

从本文开始，我会开始介绍MySQL中常见类型字段具体的存储格式，了解这部分内容之后，我们可以用于解析前面讲到的Write_rows_log_event、Update_rows_event、Delete_rows_event中具体关于数据的部分了，可以解析出某个字段具体记录了哪些值，具体的值是什么。

我们首先从数值型字段类型开始，数值型字段类型又可以分为整数类型、定点数数据类型、浮点数数据类型、比特数据类型。

数值型字段类型介绍

整数类型

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

整数，用一个字节表示。分为有符号类型和无符号类型，有符号类型的TINYINT范围是-128到127，无符号类型的TINYINT范围是0到255。

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

整数，用两个字节表示。分为有符号类型和无符号类型，有符号类型的SMALLINT范围是-32768到32767，无符号类型的SMALLINT范围是0到65535

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

整数，用三个字节表示。分为有符号类型和无符号类型，有符号类型的MEDIUMINT范围是-8388608到8388607，无符号类型的MEDIUMINT范围是0到16777215

INT[M] [UNSIGNED] [ZEROFILL]

整数，用四个字节表示。分为有符号类型和无符号类型，有符号类型的INT范围是-2147483648到2147483647，无符号类型的INT范围是0到4294967295

BIGINT[M] [UNSIGNED] [ZEROFILL]

整数，用八个字节表示。分为有符号类型和无符号类型，有符号类型的BIGINT范围是-9223372036854775808到9223372036854775807，无符号类型的BIGINT范围是0到18446744073709551615

SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名

BOOL,BOOLEAN

TINYINT(1)的别名。

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

INT的别名

定点数数据类型 DECIMAL,NUMERIC

DECIMAL[(M|,D)] [UNSIGNED] [ZEROFILL]

小数，M表示数据的精度，表示数据的总长度，小数点和符号不占长度；D表示数据的标度，指小数点后面数字的位数。

M的范围是0到65，默认为10；D的范围是0到30，默认为0。

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

DECIMAL的别名

浮点数数据类型 FLOAT,DOUBLE

FLOAT[M,D] [UNSIGNED] [ZEROFILL]

单精度浮点数，用四个字节表示。允许的值是-3.402823466E+38 到-1.175494351E-38, 0, 1.175494351E-38 到 3.402823466E+38。但是这只是理论的数值范围，实际的范围区间取决于硬件平台。

M表示数据的精度，表示数据的总长度，小数点和符号不占长度；D表示数据的标度，指小数点后面数字的位数。M和D的默认值取决于硬件平台。单精度的浮点数大约是7个小数点。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数，用八个字节表示。允许的值是-1.7976931348623157E+308 到 -2.2250738585072014E-308, 0, 和 2.2250738585072014E-308 到 1.7976931348623157E+308

。但是这只是理论的数值范围，实际的范围区间取决于硬件平台。

M表示数据的精度，表示数据的总长度，小数点和符号不占长度；D表示数据的标度，指小数点后面数字的位数。M和D的默认值取决于硬件平台。双精度的浮点数大约是15个小数点。

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL

DOUBLE的别名

比特数据类型 BIT

BIT[(M)]

比特数据类型。M表示每个值占用的bit位数，M的范围是1到64。如果M没有指定，则默认值为1。

数值型数据解析

解析环境描述

表结构定义

CREATE TABLE `number_table` (
  `col1` tinyint(4) DEFAULT NULL,
  `col2` smallint(6) DEFAULT NULL,
  `col3` mediumint(9) DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` bigint(20) DEFAULT NULL,
  `col6` decimal(25,10) DEFAULT NULL,
  `col7` float DEFAULT NULL,
  `col8` double DEFAULT NULL,
  `col9` bit(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

表数据展示

root@localhost : gangshen 02:59:24> insert into number_table values(2,-22,222,-2222,22222,123123123123.112233,123.1,123.2,b'00110');
Query OK, 1 row affected (0.04 sec)

root@localhost : gangshen 03:03:39> select * from number_table;
+------+------+------+-------+-------+-------------------------+-------+-------+------+
| col1 | col2 | col3 | col4  | col5  | col6                    | col7  | col8  | col9 |
+------+------+------+-------+-------+-------------------------+-------+-------+------+
|    2 |  -22 |  222 | -2222 | 22222 | 123123123123.1122330000 | 123.1 | 123.2 |    |
+------+------+------+-------+-------+-------------------------+-------+-------+------+
1 row in set (0.00 sec)

然后从binlog文件中拿到对应的Table_map_event和Writes_rows_event对应的字节内容，开始解析

元数据存储格式

Table_map_event字节数据解析

公共头部部分省略
5d 00 00 00 00 00 //table_id
01 00 //flag
08 67 61 6e 67 73 68 65 6e 00 //database_name :1个字节是字符串长度，后接一个null-terminated string 第一个字节表示字符串长度为8，后面内容将16进制转换为ascii字符为gangshen
0c 6e 75 6d 62 65 72 5f 74 61 62 6c 65 00 //table_name: 1个字节是字符串长度，后接一个null-terminated string第一个字节表示字符串长度为12，后面内容将16进制转换为ascii字符为number_table
09 //columns count :packet integer类型，转换之后，数值为9 表示表中有9个字段
01 02 09 03 08 f6 04 05 10 //column types
06 //metadata_length
19 0a //col6 metadata
04 //col7 metadata
08 //col8 metadata
05 00 //col9 metadata
ff 01 //null_bits :int((column_count + 7) / 8)个字节 一个bit表示一个字段是否可以为null
e2 06 a7 b1 //checksum

从Table_map_event中可以参照上节描述，解析出表中所有的字段类型以及对应的元数据，按照顺序分别是：

第一个字段：0x01=MYSQL_TYPE_TINY 无元数据

第二个字段：0x02=MYSQL_TYPE_SHORT 无元数据

第三个字段：0x09=MYSQL_TYPE_INT24 无元数据

第四个字段：0x03=MYSQL_TYPE_LONG 无元数据

第五个字段：0x08=MYSQL_TYPE_LONGLONG 无元数据

第六个字段：0xf6=MYSQL_TYPE_NEWDECIMAL 元数据表示精度为25(0x19)，标度为10(0x0a)

第七个字段：0x04=MYSQL_TYPE_FLOAT 元数据表示该字段需要使用4(0x04)个字节表示

第八个字段：0x05=MYSQL_TYPE_DOUBLE 元数据表示该字段需要使用8(0x08)个字节表示

第九个字段：0x10=MYSQL_TYPE_BIT 元数据表示该字段中一共有5(0x0500)个比特位

数值型数据“值”解析

Write_rows_log_event 字节数据解析

公共头部部分省略。。。
5d 00 00 00 00 00 //table_id:
01 00 //flag
02 00 //var header length
09 //m_width :packet integer，表示表中字段数量
ff ff //before image: (m_width + 7) / 8字节
00 fe //bitmap_bits :表中两个字段，插入的值都不为NULL
02 //col1
ea ff //col2
de 00 00 //col3
52 f7 ff ff //col4
ce 56 00 00 00 00 00 00 //col5
80 00 7b 07 56 b5 b3 06 b0 8a 28 00 //col6
33 33 f6 42 //col7
cd cc cc cc cc cc 5e 40 //col8
06 //col9
5e 6d 11 fa //checksum

接下来，对应write_row_event中各个字段的二进制，我们按照顺序解析各个字段的值

MYSQL_TYPE_TINY字段解析

MYSQL_TYPE_TINY需要使用1个字节表示，小端存储。
上面的例子中，col1的类型是MYSQL_TYPE_TINY，在所以在Write_rows_log_event中字段值的内容占用1个字节，为0x02，转换成10进制就是2

MYSQL_TYPE_SHORT字段解析

MYSQL_TYPE_SHORT需要使用2个字节表示，小端存储。

上面的例子中，col2的类型是MYSQL_TYPE_SHORT，所以在Write_rows_log_event中字段值的内容占用2个字节，为0xEAFF，因为是小端存储，所以实际的顺序为0xFFEA，数据采用补码的方式表示有符号数，所以转换成10进制数值为-22

MYSQL_TYPE_INT24字段解析

MYSQL_TYPE_INT24需要使用3个字节表示，小端存储。

上面的例子中，col3的类型是MYSQL_TYPE_INT24，所以在Write_rows_log_event中字段值的内容占用3个字节，为0xDE0000，因为是小端存储，所以实际的顺序为0x0000DE，数据采用补码的方式表示有符号数，所以转换成10进制数值为222

MYSQL_TYPE_LONG字段解析

MYSQL_TYPE_LONG需要使用4个字节表示，小端存储。

上面的例子中，col4的类型是MYSQL_TYPE_LONG，所以在Write_rows_log_event中字段值的内容占用4个字节，为0x52F7FFFF，因为是小端存储，所以实际的顺序为0xFFFFF752，数据采用补码的方式表示有符号数，所以转换成10进制数值为-2222

MYSQL_TYPE_LONGLONG 字段解析

MYSQL_TYPE_LONGLONG需要使用8个字节表示，小端存储。

上面的例子中，col5的类型是MYSQL_TYPE_LONGLONG，所以在Write_rows_log_event中字段值的内容占用8个字节，为0xCE56000000000000，因为是小端存储，所以实际的顺序为0x00000000000056CE，数据采用补码的方式表示有符号数，所以转换成10进制数值为22222

MYSQL_TYPE_DECIMAL字段解析

MYSQL_TYPE_DECIMAL采用压缩存储的方式，将9个数字（十进制）使用4个字节来表示；每9位数字使用4个字节表示，剩余未满9位数字的，所需字节数参照下表。并且DECIMAL中整数部分与小数部分是单独分开表示的。

剩余位数	所需字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

MYSQL_TYPE_DECIMAL类型的字段，在存储数值的时候，首先将数值按照整数部分以及小数部分划分。

整数部分从低位到高位，每9位数字使用4个字节存储，将每9位数字与正负号（正数：0，负数：-1）做异或运算之后，以大端、有符号整数存储，对于剩余不足9位数字的部分，参照表格存储，将剩余数字与正负号（正数：0，负数：-1）做异或运算之后并异或上一个0x80，以大端、有符号整数存储。

小数部分从高位到低位，每9位数字使用4个字节存储，将每9位数字与正负号（正数：0，负数：-1）做异或运算之后，以大端、有符号整数存储，对于剩余不足9位数字的部分，参照表格存储，将剩余数字与正负号（正数：0，负数：-1）做异或运算之后并异或上一个0x80，以大端、有符号整数存储。

例如数字12.34，整数部分12，2位数字需要一个字节，0x0C与0做异或操作结果是0x0C，小数部分34，2位数字需要一个字节，0x22与0做异或操作结果是0x22，所以数字12.34的MYSQL_TYPE_DECIMAL在binlog中以0xOC22方式存储。

上面的例子中，col6的类型是MYSQL_TYPE_NEWDECIMAL，按照MYSQL_TYPE_NEWDECIMAL存储要求，需要（(15/9)4+3 + (10/9)4+1）=12个字节，所以在Write_rows_log_event中字段值的内容占用12个字节，为0x80007b0756b5b306b08a2800，其中整数部分使用7个字节存储，内容为0x80007b0756b5b3，小数部分使用5个字节存储，内容为0x06b08a2800。

首先解析整数部分

MYSQL_TYPE_NEWDECIMAL类型值的符号可以从第一个字节中获取到，将第一个字节和0x80做与操作，如果结果不为0表示该值为正数，记mask=0；如果结果为0表示该值为负数，记mask=-1。

因为MYSQL_TYPE_NEWDECIMAL类型使用第一个字节的最高位来表示值的正负，所以在解析具体的值的时候，需要将该符号位忽略，所以真实的数值内容需要将第一个字节与0x80做异或运算。即上述内容中，整数部分数值内容为0x00007b0756b5b3，小数部分数值内容为0x06b08a2800。

整数部分因为最多存储15位数据，所以使用4+6=7个字节存储。因为整数部分是从低位到高位（从右往左）每9位数字使用4个字节存储，剩余位数按照要求存储，所以这7个字节中，前3个字节表示前（15-9）=6位，剩余4个字节表示后9位。将前三个字节0x00007b转换成10进制为123，因为存储的时候将数值与正负号（正数：0，负数：-1）做过异或运算，所以解析的时候需要将解析出来的123与mask做异或运算，即实际前6位数值为123。后4个字节0x0756b5b3转换成10进制为123123123，因为存储的时候将数值与正负号（正数：0，负数：-1）做过异或运算，所以解析的时候需要将解析出来的123与mask做异或运算，即后9位数字为123123123。将整数部分连接起来就是123123123123。

接着解析小数部分

小数部分因为最多存储10位数据，所以使用4+1=5个字节存储。因为小数部分是从高位到低位（从左往右）每9位数字使用4个字节存储，剩余位数按照要求存储，所以这5个字节中，前4个字节表示9位数字，剩余1个字节表示（10-9）=1位。前4个字节0x0ab08a28转换成10进制是112233000，因为存储的时候将数值与正负号（正数：0，负数：-1）做异或运算，所以解析的时候需要将解析出来的112233000与mask做异或运算，即实际前9位数字为112233000。后一个字节0x00转换成10进制为0，因为存储的时候将数值与正负号（正数：0，负数：-1）做异或运算，所以解析的时候需要将解析出来的0与mask做异或运算，即实际后1位数字为0。将小数部分连接起来就是1122330000。

将解析出来的整数部分与小数部分连接在一起就是123123123123.1122330000

MYSQL_TYPE_FLOAT字段解析

MYSQL_TYPE_FLOAT类型需要四个字节表示。

上面的例子中，col7的类型是MYSQL_TYPE_FLOAT，所以在Write_rows_log_event中字段值的内容占用4个字节，为0x3333f642，将字节内容强转成float类型的数据(MySQL在解析binlog的时候，也是将字节内容强转成float类型)，得到的结果取决于不同的编程语言。比如在Python中，0x3333f642内容强转成float的结果就是123.099998。

MYSQL_TYPE_DOUBLE字段解析

MYSQL_TYPE_DOUBEL类型需要八个字节表示

上面的例子中，col8的类型是MYSQL_TYPE_DOUBLE，所以在Write_rows_log_event中字段值的内容占用8个字节，为0xcdcccccccccc5e40，将字节内容强转成double类型的数据（MySQL在解析binlog的时候，也是将字节内容强转成double类型），得到的结果取决于不同的编程语言。比如在Python中，0xcdcccccccccc5e40内容强转成double类型就是123.200000。

MYSQL_TYPE_BIT字段解析

MYSQL_TYPE_BIT类型需要（M+7）/8，M是字段定义的比特位数。

上面的例子中，col9类型是MYSQL_TYPE_BIT，对应的元数据中定义该字段为5个比特位，所以需要（5+7）/8=1个字节存储数值。所以在Write_row_event中字段值的内容占用1个字节，为0x06，对应的二进制表示方式为b’00110’，所以该字段的值为b’00110’。

MySQL Binlog(七)——MySQL常见binlog event解析（下）

Posted on 2019-12-07 Edited on 2022-02-14 In MySQL

MySQL Binlog(七)——MySQL常见binlog event解析（下）

前言

本文继续介绍binlog中常见event类型：Gtid_log_event、Previous_gtid_log_event、Anonymous_gtid_log_event。

Gtid_log_event

开启GTID模式的场景下，每次事务commit提交时，MySQL会在binlog中事务开始写入一个Gtid_log_event，记录该事务的GTID事务号。

post-header部分

字段	字节数	描述
commit_flag	1字节	标记事务是否提交，非0为已提交，为0表示，事务未提交
sid	16字节	记录GTID中uuid的部分（不记录‘-’），每1个字节表示uuid中2个字符
gno	8字节	小端存储，GTID中的事务号部分

event-body部分

字段	字节数	描述
checksum	4字节	校验码

字节解析示例

使用mysqlbinlog工具解析binlog文件

# at 191
#180109 18:31:08 server id 330619  end_log_pos 239 CRC32 0xd20330e8    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '89fbcea2-da65-11e7-a851-fa163e618bac:5'/*!*/;
# at 239

解析二进制文件：

公共头部部分省略。。。。
01 //commit_flag:标记事务是否已提交，表示已提交
89 fb ce a2 da 65 11 e7 a8 51 fa 16 3e 61 8b ac //sid:GTID中uuid部分，转换为uuid为89fbcea2-da65-11e7-a851-fa163e618bac
05 00 00 00 00 00 00 00 //gno:GTID中的事务号，小端存储，转换为10进制为5
e8 30 03 d2 //checksum

Previous_gtid_log_event

在开启GTID的模式下，每个binlog文件开始会记录一个Previous_gtid_log_event，Previous_gtid_log_event中会包含当前binlog之前所有binlog中的GTID集合。

post-header部分

字段	字节数	描述
n_sids	8字节	小端存储，记录之后有几个GTID中的uuid号
sid	16字节	记录GTID中uuid的部分（不记录‘-’），每1个字节表示uuid中2个字符
n_intervals	8字节	记录每个sid对应有几个间隔，指的是事务号间隔
start	8字节	每个事务号间隔中的起始事务号
end	8字节	每个事务号间隔中的结束事务号+1

event-body部分

字段	字节数	描述
checksum	4字节	校验码

字节解析示例

使用mysqlbinlog工具解析binlog文件

# at 120
#180111 14:10:27 server id 330619  end_log_pos 279 CRC32 0x94a92478    Previous-GTIDs
# 89fbcea2-da65-11e7-a851-fa163e618bac:1-5:999:1050-1052,
# aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2:5-7
DELIMITER ;

解析二进制文件：

公共头部部分省略。。。
02 00 00 00 00 00 00 00 //n_sids:记录之后有几个GTID中的uuid号，小端存储，转换为10进制为2，表后后续有2个uuid号
89 fb ce a2 da 65 11 e7 a8 51 fa 16 3e 61 8b ac //sid:GTID中的uuid号转换为uuid为89fbcea2-da65-11e7-a851-fa163e618bac
03 00 00 00 00 00 00 00 //n_intervals:对应的sid中中对应几个事务号间隔，小端存储，转换为10进制为3，表示有3个事务号间隔
01 00 00 00 00 00 00 00 //start:事务号间隔中的起始事务号，小端存储，转换为10进制为1
06 00 00 00 00 00 00 00 //end:事务号间隔中的结束事务号+1，小端存储，转换为10进制为6，实际的间隔结束事务号为5
e7 03 00 00 00 00 00 00 //start:事务号间隔中的起始事务号，小端存储，转换为10进制为999
e8 03 00 00 00 00 00 00 //end:事务号间隔中的结束事务号+1，小端存储，转换为10进制为1000，实际的间隔结束事务号为999
1a 04 00 00 00 00 00 00 //start:事务号间隔中的起始事务号，小端存储，转换为10进制为1050
1d 04 00 00 00 00 00 00 //end:事务号间隔中的结束事务号+1，小端存储，转换为10进制为1053，实际的间隔结束事务号为1052
aa aa aa aa aa aa aa aa aa aa aa aa aa aa aa aa //sid:GTID中的uuid号转换为uuid为aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
02 00 00 00 00 00 00 00 //n_intervals:对应的sid中中对应几个事务号间隔，小端存储，转换为10进制为2，表示有2个事务号间隔
01 00 00 00 00 00 00 00 //start:事务号间隔中的起始事务号，小端存储，转换为10进制为1
03 00 00 00 00 00 00 00 //end:事务号间隔中的结束事务号+1，小端存储，转换为10进制为3，实际的间隔结束事务号为2
05 00 00 00 00 00 00 00 //start:事务号间隔中的起始事务号，小端存储，转换为10进制为5
08 00 00 00 00 00 00 00 //end:事务号间隔中的结束事务号+1，小端存储，转换为10进制为8，实际的间隔结束事务号为7
78 24 a9 94 //checksum

Anonymous_gtid_log_event

MySQL在binlog中记录每一个匿名事务之前会记录一个Anonymous_gtid_log_event表示接下来的事务是一个匿名事务。
注意：因为在5.6.34中并不会产生Anonymous_gtid_log_event，所以以下内容是基于5.7.19版本解析

post-header部分

字段	字节数	描述
gtid_flags	1字节	记录binlog格式，如果gtid_flags值为1，表示binlog中可能有以statement方式记录的binlog，如果为0表示，binlog中只有以row格式记录的binlog
sid	16字节	记录GTID中uuid的部分（不记录‘-’），每1个字节表示uuid中2个字符
gno	8字节	小端存储，GTID中的事务号部分
logical_timestamp_typecode	1字节	判断是否有last_commit和sequence_no，在logical_tmiestamp_typecode=2的情况下，有last_commit和sequence_no
last_commit	8字节	小端存储，上次提交的事务号
sequence_no	8字节	小端存储，本次提交的序列号

event-body部分

字段	字节数	描述
checksum	4字节	校验码

字节解析示例

使用mysqlbinlog工具解析binlog文件

#180109 10:53:54 server id 9999  end_log_pos 5681 CRC32 0x46be0639    Anonymous_GTID    last_committed=20    sequence_number=21    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 5681

解析二进制文件：

公共头部部分省略。。。
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 //sid:
00 00 00 00 00 00 00 00 //gno:
02 //logical_timestamp_typecode:判断是否有last_commit和sequence_no，在logical_tmiestamp_typecode=2的情况下，有last_commit和sequence_no，所以这边的话，后续有last_commit和sequence_no部分的内容
14 00 00 00 00 00 00 00 //last_commit:上次提交的事务号，小端存储，转换为10机制为20
15 00 00 00 00 00 00 00 //sequence_no:本次提交的序列号，小端存储，转换为10进制为21
39 06 be 46 //checksum:校验码

MySQL Binlog(六)——MySQL常见binlog event解析（中）

Posted on 2019-12-07 Edited on 2022-02-14 In MySQL

MySQL Binlog(六)——MySQL常见binlog event解析（中）

前言

本文继续介绍binlog中常见的event：Table_map_event、Write_rows_log_event、Update_rows_log_event、Delete_rows_log_event、Xid_event。

Table_map_event

TABLE_MAP_EVENT只有在binlog文件是以ROW格式记录的时候，才会使用。binlog中记录的每个更改的记录之前都会有一个对应要操作的表的TABLE_MAP_EVENT。TABLE_MAP_EVENT中记录了表的定义（包括database name,table name，字段定义），并且会将这个表的定义对应于一个数字，称为table_id。设计TABLE_MAP_EVENT类型event的目的是为了当主库和从库之间有不同的表定义的时候，复制仍能进行。如果一个事务中操作了多个表，多行记录，在binlog中会将对多行记录的操作event进行分组，每组行记录操作event前面会出现对应表的TABLE_MAP_EVENT。

post-header部分

字段	字节数	描述
table_id	6字节	操作的表的table_id
flags	2字节	目前版本没有用，都是0，保留给之后的版本使用

event-body部分

字段	字节数	描述
database_name	1个字节表示字符串长度，之后接一个null-terminated类型的字符串		操作的数据库的名称
table_name	1个字节表示字符串长度，之后接一个null-terminated类型的字符串	操作的表的名称
column_count	packed integer(1或3或4或9个字节)	对应表中的字段数量
column_type	每个字段占用一个字节，字段类型定义在enum_field_types中	字段类型
metadata_length	packed integer(1或3或4或9个字节)	对应字段的元数据信息的长度
metadata	根据enum_field_type中的定义确定不同字段的元数据，如果某字段类型没有元数据，则不记录	每个字段的元数据信息，比如varchar字段需要记录最长长度
null_bits	int((column_count + 7)/8) 字节	一个bit表示一个字段是否可以为NULL，顺序是：第一个字节的最低位开始向最高位增长，之后第二个字节的最低位开始向最高位增长，以此类推
check_sum	4字节	校验码

字节解析示例

建表语句:

CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

二进制内容解析：

公共头部部分省略
6c 00 00 00 00 00 //table_id :小端存储，16进制转换为10进制为108
01 00 //flag :
08 67 61 6e 67 73 68 65 6e 00 //database_name :1个字节是字符串长度，后接一个null-terminated string 第一个字节表示字符串长度为8，后面内容将16进制转换为ascii字符为gangshen
05 74 65 73 74 31 00 //table_name: 1个字节是字符串长度，后接一个null-terminated string第一个字节表示字符串长度为5，后面内容将16进制转换为ascii字符为test1
02 //columns count :packet integer类型，转换之后，数值为2 表示表中有两个字段
03 0f //column type : 一个字节表示一个字段的类型，字段类型定义在enum enum_field_types ，分别是一个int类型以及一个varchar类型,具体的字段类型及记录格式等信息可以查看《MySQL中Rows_event中字段表示》文档
02 //metadata length : packet integer类型,转换之后，数值为2，表示记录表中的metadata内容占用2个字节
14 00 // :varchar 的max length 因为int没有metadata所以跳过
02 //null_bits :int((column_count + 7) / 8)个字节 一个bit表示一个字段是否可以为null 
52 53 4a d9 checksum

Write_rows_log_event

在以ROW格式记录的binlog文件中，Write_rows_log_event记录了插入的行记录。

post-header部分

字段	字节数	描述
table_id	6字节	操作的表的table_id
flags	2字节	目前版本没有用，都是0，保留给之后的版本使用

event-body部分

字段	字节数	描述
var_header	2字节
m_width	packed integer(1或3或4或9个字节)	表中字段数量
before_image	(m_width + 7)/8字节
after_bitmap_bits	(m_with +7)/8字节	字段值是否为空标记，一个bit表示一个字段是否为NULL，顺序是：第一个字节的最低位开始向最高位增长，之后第二个字节的最低位开始向最高位增长，以此类推
after_column_content	不定	按照顺序每个字段的内容
check_sum	4字节	校验码

字节解析示例

建表语句

CREATE TABLE `int_table` (
  `col1` tinyint(4) DEFAULT NULL,
  `col2` smallint(6) DEFAULT NULL,
  `col3` mediumint(9) DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` bigint(20) DEFAULT NULL,
  `col6` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

插入数据:

1	insert into int_table values(1,11,111,1111,11111,true);

对应Write_rows_log_event使用mysqlbinlog解析

# at 340
#180103 10:21:20 server id 330619  end_log_pos 395 CRC32 0x50177e10    Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `gangshen`.`int_table`
### SET
###  @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###  @2=11 /* SHORTINT meta=0 nullable=1 is_null=0 */
###  @3=111 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
###  @4=1111 /* INT meta=0 nullable=1 is_null=0 */
###  @5=11111 /* LONGINT meta=0 nullable=1 is_null=0 */
###  @6=1 /* TINYINT meta=0 nullable=1 is_null=0 */
# at 395

解析二进制文件:

公共头部部分省略
64 00 00 00 00 00 //table_id: 小端存储，16进制转换为10进制为100
01 00 //flag:
02 00 //var header length :小端存储，16进制转换为10进制为2
06 //m_width :packet integer，表示表中字段数量
ff //before image: (m_width + 7) / 8字节
c0 //after_bitmap_bits :insert插入之后的记录的NULL标记，表中六个字段，插入的值都不为NULL
//after_columns_content：insert插入记录之后的记录值
01 
0b 00 
6f 00 00 
57 04 00 00
67 2b 00 00 00 00 00 00
01 
10 7e 17 50 //checksum

Update_rows_log_event

在以ROW格式记录的binlog文件中，Update_rows_log_event记录了更新的行记录。

post-header部分

字段	字节数	描述
table_id	6字节	操作的表的table_id
flags	2字节	目前版本没有用，都是0，保留给之后的版本使用

event-body部分

字段	字节数	描述
var_header	2字节
m_width	packed integer(1或3或4或9个字节)	表中字段数量
before_image	(m_with+7)/8字节
after_image	(m_with+7)/8字节
before_bitmap_bits	(m_with+7)/8字节	before_bitmap_bits记录的是update更改之前的记录中，字段值是否为NULL标记，一个bit表示一个字段是否为NULL，顺序是：第一个字节的最低位开始向最高位开始增长，之后第二个字节的最低位开始向最高位增长，以此类推
before_column_content	不定	update更新之前的记录值，按照顺序每个字段的内容
after_bitmap_bits	(m_with+7)/8字节	after_bitmap_bits记录的是update更改之前的记录中，字段值是否为NULL标记，一个bit表示一个字段是否为NULL，顺序是：第一个字节的最低位开始向最高位开始增长，之后第二个字节的最低位开始向最高位增长，以此类推
after_column_content	不定	update更新之后的记录值，按照顺序每个字段的内容
check_sum	4字节	校验码

字节解析示例

建表语句

CREATE TABLE `int_table` (
  `col1` tinyint(4) DEFAULT NULL,
  `col2` smallint(6) DEFAULT NULL,
  `col3` mediumint(9) DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` bigint(20) DEFAULT NULL,
  `col6` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

插入数据并更新:

root@localhost : gangshen 10:20:49> insert into int_table values(1,11,111,1111,11111,true);
Query OK, 1 row affected (0.03 sec)

root@localhost : gangshen 10:21:20> update int_table set col2=22,col3=222 where col1=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost : gangshen 10:21:44> select * from int_table;
+------+------+------+------+-------+------+
| col1 | col2 | col3 | col4 | col5  | col6 |
+------+------+------+------+-------+------+
|    1 |  22 |  222 | 1111 | 11111 |    1 |
+------+------+------+------+-------+------+
1 row in set (0.00 sec)

对应Update_rows_log_event使用mysqlbinlog解析

# at 644
#180103 10:41:37 server id 9999  end_log_pos 720 CRC32 0x5576b52f    Update_rows: table id 231 flags: STMT_END_F
### UPDATE `gangshen`.`int_table`
### WHERE
###  @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###  @2=11 /* SHORTINT meta=0 nullable=1 is_null=0 */
###  @3=111 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
###  @4=1111 /* INT meta=0 nullable=1 is_null=0 */
###  @5=11111 /* LONGINT meta=0 nullable=1 is_null=0 */
###  @6=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### SET
###  @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###  @2=22 /* SHORTINT meta=0 nullable=1 is_null=0 */
###  @3=222 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
###  @4=1111 /* INT meta=0 nullable=1 is_null=0 */
###  @5=11111 /* LONGINT meta=0 nullable=1 is_null=0 */
###  @6=1 /* TINYINT meta=0 nullable=1 is_null=0 */
# at 720

解析二进制文件:

公共头部部分省略。。。
64 00 00 00 00 00 //table_id: 小端存储，16进制转换为10进制为100
01 00 //flag:
02 00 //var header length :小端存储，16进制转换为10进制为2
06 //m_width :packet integer，表示表中字段数量
ff //before image: (m_width + 7) / 8字节
ff //after image: (m_width + 7) / 8字节
c0 //before_bitmap_bits :update更新之前记录中NULL标记，表中六个字段，值都不为NULL
//before_column_content接下来是update更新之前记录的值
01 
0b 00
6f 00 00 
57 04 00 00
67 2b 00 00 00 00 00 00
01 
c0 //after_bitmap_bits :update更新之后记录中NULL标记，表中六个字段，值都不为NULL
//after_column_content接下来是update更新之前记录的值
01 
16 00 
de 00 00 
57 04 00 00
67 2b 00 00 00 00 00 00 
01 
16 a3 de bb //checksum

Delete_rows_log_event

在以ROW格式记录的binlog文件中，Delete_rows_log_event记录了删除的行记录。

post-header部分

字段	字节数	描述
table_id	6字节	操作的表的table_id
flags	2字节	目前版本没有用，都是0，保留给之后的版本使用

event-body部分

字段	字节数	描述
var_header	2字节
m_width	packed integer(1或3或4或9个字节)	表中字段数量
after_image	(m_width + 7)/8字节
before_bitmap_bits	(m_with+7)/8字节	before_bitmap_bits记录的是update更改之前的记录中，字段值是否为NULL标记，一个bit表示一个字段是否为NULL，顺序是：第一个字节的最低位开始向最高位开始增长，之后第二个字节的最低位开始向最高位增长，以此类推
before_column_content	不定	delete删除之前的记录值，按照顺序每个字段的内容
check_sum	4字节	校验码

字节解析示例

建表语句

CREATE TABLE `int_table` (
  `col1` tinyint(4) DEFAULT NULL,
  `col2` smallint(6) DEFAULT NULL,
  `col3` mediumint(9) DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` bigint(20) DEFAULT NULL,
  `col6` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

删除数据:

1	delete from int_table where col1=1;

对应Delete_rows_log_event使用mysqlbinlog解析

# at 320
#180103 14:24:54 server id 330619  end_log_pos 375 CRC32 0xce4818b2    Delete_rows: table id 100 flags: STMT_END_F
### DELETE FROM `gangshen`.`int_table`
### WHERE
###  @1=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###  @2=22 /* SHORTINT meta=0 nullable=1 is_null=0 */
###  @3=222 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
###  @4=1111 /* INT meta=0 nullable=1 is_null=0 */
###  @5=11111 /* LONGINT meta=0 nullable=1 is_null=0 */
###  @6=1 /* TINYINT meta=0 nullable=1 is_null=0 */
# at 375

解析二进制文件:

公共头部部分省略
64 00 00 00 00 00 //table_id: 小端存储，16进制转换为10进制为100
01 00 //flag:
02 00 //var header length :小端存储，16进制转换为10进制为2
06 //m_width :packet integer，表示表中字段数量
ff //after image: (m_width + 7) / 8字节
c0 //before_bitmap_bits :delete删除之前的记录的NULL标记，表中六个字段，值都不为NULL
//before_columns_content：delete删除记录之前的记录值
01 
16 00 
de 00 00 
57 04 00 00
67 2b 00 00 00 00 00 00
01 
10 7e 17 50 //checksum

Xid_event

表示支持内部XA的存储引擎上的事务提交。正常的事务是通过QUERY_EVENT来发送一个BEGIN语句并且通过QUERY_EVENT来发送一个COMMIT语句（如果事务回滚则发送的是ROLLBACK）实现。

post-header部分

无

event-body部分

字段	字节数	描述
xid	8字节	xid号，小端存储，无符号数
check_sum	4字节	校验码

字节解析示例

使用mysqlbinlog工具解析对应的Xid_event

# at 1691
#180103 15:30:45 server id 330619  end_log_pos 1722 CRC32 0x8816181c    Xid = 2698
COMMIT/*!*/;
# at 1722

解析二进制文件：

1
2
3

公共头部部分省略...
8a 0a 00 00 00 00 00 00 //xid,因为是小端存储，所以实际为0x 00 00 00 00 00 00 0a 8a，转换为10进制为2698
1c 18 16 88 //check_sum

MySQL Binlog(五)——MySQL常见binlog event解析（上）

Posted on 2019-12-07 Edited on 2022-02-14 In MySQL

MySQL Binlog(五)——MySQL常见binlog event解析（上）

前言

前面讲了binlog event的基础知识，从这篇文章开始我们就具体来解析binlog中常见的event，了解其具体代表的意义。本文会介绍Format_description_event、Rotate_event、Query_log_event、Rows_query_log_event几种event。

Format_description_event

Format_description_event是每个binlog文件开头的一个描述信心的event。

post-header部分

字段	字节数	描述
binlog_version	2字节	binlog结构的版本,v1,v2,v4
server_version	50字节	MySQL server的数据库版本
timestamp	4字节	创建该binlog文件的时间,单位为秒
header_length	1字节	指定公共头部的长度，v4版本中这个值一直为19，说明其他所有的event的extra_header部分都是空,extra_header长度为header_length-19
event_post_header_length	variable size，跟各个版本支持的event类型总数一致	每个event类型，占用一个字节，表示该event类型post-header部分的长度

event-body部分

无

字节解析示例

公共头部部分省略
04 00 //binlog_version :v4版本
35 2e 36 2e 33 34 2d 6c 6f 67 
00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 
00 00 00 00 00 00 00 00 00 00 
00 00 00 00 00 00 00 00 00 00  //mysql server version :16进制转换为ascii之后，值为5.6.34-log
00 00 00 00 //timestamp:
13 //header length :19,说明该版本的event类型公共头部长度都为19，extra_header长度为header_length-19
38 0d 00 08 00 12 00 04 04 04 
04 12 00 00 5c 00 04 1a 08 00 
00 00 08 08 08 02 00 00 00 0a 
0a 0a 19 19 00 //event_post_header_len:各个类型event的post-header部分长度
01 2e ef 23 e2

Rotate_event

当MySQL切换至新的binlog文件的时候，MySQL会在旧的binlog文件中写入一个ROTATE_EVENT，其内容包含新的binlog文件的文件名以及第一个偏移地址。当在数据库中执行FLUSH LOGS语句或者binlog文件的大小超过max_binlog_size参数设定的值就会切换新的binlog文件。

post-header部分

字段	字节数	描述
next_binlog_po	8字节	下一个binlog文件起始的偏移地址

event-body部分

字段	字节数	描述
next_binlog_filename	variable string	下一个binlog文件的文件名

字节解析示例

公共头部部分省略
04 00 00 00 00 00 00 00 //next_binlog_pos：下一个binlog的起始偏移地址,小端存储，16进制转换为10进制之后为4
6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 30 32  //next_binlog_filename：下一个binlog的文件名,16进制转换为ascii之后，值为mysql-bin.000002
b3 db 0b 9a checksum

Query_log_event

记录更新操作的语句。

post-header部分

字段	字节数	描述
thread_id	4字节	小端存储，执行语句的线程ID号
exec_time	4字节	小端存储，语句执行的时间
db_len	1字节	database名的长度
error_code	2字节	错误号
status_vars_len	2字节	小端存储，这部分，在v1和v3版本的event中是没有的，指定状态值的长度

event-body部分

字段	字节数	描述
status_vars	status_vars_len字节	记录状态值，具体的解析见下表
database_name	db_len+1字节	null-terminaled类型的字符串，记录database的名字
query_statement	不定	执行的语句
check_sum	4字节	校验码

status_vars的解析是，一个字节表示状态的类型，在类型之后按照类型不同紧接着不同字节数的状态值，状态的类型一共有：

enum Query_event_status_vars
  {
    Q_FLAGS2_CODE= 0,
    Q_SQL_MODE_CODE,
    Q_CATALOG_CODE,
    Q_AUTO_INCREMENT,
    Q_CHARSET_CODE,
    Q_TIME_ZONE_CODE,
    Q_CATALOG_NZ_CODE,
    Q_LC_TIME_NAMES_CODE,
    Q_CHARSET_DATABASE_CODE,
    Q_TABLE_MAP_FOR_UPDATE_CODE,
    Q_MASTER_DATA_WRITTEN_CODE,
    Q_INVOKER,
    Q_UPDATED_DB_NAMES,
    Q_MICROSECONDS,
    Q_COMMIT_TS,
    Q_COMMIT_TS2,
    Q_EXPLICIT_DEFAULTS_FOR_TIMESTAMP
  };

不同状态对应的状态值的字节数

状态	对用的code	状态值占用的字节数
Q_FLAGS2_CODE	0	4字节
Q_SQL_MODE_CODE	1	8字节
Q_CATALOG_CODE	2	第一个字节表示catalog_len，总共catalog_len+2个字节
Q_AUTO_INCREMENT	3	4字节
Q_CHARSET_CODE	4	6字节
Q_TIME_ZONE_CODE	5	第一个字节表示time_zone_len，总共time_zone_len+1字节
Q_CATALOG_NZ_CODE	6	第一个字节表示catalog_len，总共catalog_len+1个字节
Q_LC_TIME_NAMES_CODE	7	2字节
Q_CHARSET_DATABASE_CODE	8	2字节
Q_TABLE_MAP_FOR_UPDATE_CODE	9	8字节
Q_MASTER_DATA_WRITTEN_CODE	10	4字节
Q_INVOKER	11	包含两部分，一部分是user，一部分是host。user部分，一个字节表示user_len，接着user_len个字节表示user。host部分，一个字节表示host_len，接着host_len个字节表示host。
Q_UPDATED_DB_NAMES	12
Q_MICROSECONDS	13	3字节
Q_COMMIT_TS	14
Q_COMMIT_TS2	15
Q_EXPLICIT_DEFAULTS_FOR_TIMESTAMP	16	1字节

字节解析示例

执行语句:

insert into test1(`name`) values('beijing');

root@localhost : (none) 06:07:04> show binlog events  in 'mysql-bin.000012';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| mysql-bin.000012 |  4 | Format_desc |    330619 |        120 | Server ver: 5.6.34-log, Binlog ver: 4                      |
| mysql-bin.000012 | 120 | Query      |    330619 |        212 | BEGIN                                                      |
| mysql-bin.000012 | 212 | Intvar      |    330619 |        244 | INSERT_ID=28                                                |
| mysql-bin.000012 | 244 | Query      |    330619 |        374 | use `gangshen`; insert into test1(`name`) values('beijing') |
| mysql-bin.000012 | 374 | Xid        |    330619 |        405 | COMMIT /* xid=2961 */                                      |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)

使用mysqlbinlog工具解析binlog文件

# at 244
#180105 15:20:29 server id 330619  end_log_pos 244 CRC32 0xf41b5eaa    Intvar
SET INSERT_ID=28/*!*/;
#180105 15:20:29 server id 330619  end_log_pos 374 CRC32 0x98379221    Query    thread_id=106404    exec_time=0    error_code=0
use `gangshen`/*!*/;
SET TIMESTAMP=1515183629/*!*/;
insert into test1(`name`) values('beijing')
/*!*/;
# at 374

解析二进制文件：

公共头部部分省略。。。。
a4 9f 01 00 //thread_id:执行语句的线程号，小端存储，转换为16进制为106404
00 00 00 00 //exec_time:执行语句的时间，小端存储，转化为16进制为0
08 //db_len:database name的长度，转换为10进制为8
00 00 //error_code:错误号，小端存储，转换为10进制为0
2a 00 //status_vars_len:状态值的长度，小端存储，转换为10进制为42，表示后续的42个字节为状态值的内容
//status_vars_len
00 00 00 00 00 
01 00 00 20 40 00 00 00 00
06 03 73 74 64 
03 02 00 02 00
04 21 00 21 00 53 00 
0c 01 67 61 6e 67 73 68 65 6e 00 
67 61 6e 67 73 68 65 6e 00 //database_name:数据库名称，null-terminaled string类型 ，转换为字符串为gangshen
696e7365727420696e746f20746573743128606e616d6560292076616c75657328276265696a696e672729 //query_statement:执行的语句，转换为字符串为:insert into test1(`name`) values('beijing')
21 92 37 98//checksum

Rows_query_log_event

在row格式复制模式下，将query以语句形式记录。在5.6.2版本之后，可以通过设置binlog_rows_query_log_events参数来控制在row格式复制模式下是否需要将query语句记录到binlog文件中。

post-header部分

无

event-body部分

字段	字节数	描述
str_len	1字节	记录语句长度
statement	str_len字节	对应的语句
checksum	4字节	校验码

字节解析示例

执行语句:

root@localhost : gangshen 09:11:56> insert into test1(`name`) values('rows_query');
Query OK, 1 row affected (0.04 sec)

root@localhost : gangshen 09:12:06> show binlog events in 'mysql-bin.000014';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                            |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------+
| mysql-bin.000014 |  4 | Format_desc |    330619 |        120 | Server ver: 5.6.34-log, Binlog ver: 4            |
| mysql-bin.000014 | 120 | Query      |    330619 |        201 | BEGIN                                            |
| mysql-bin.000014 | 201 | Rows_query  |    330619 |        271 | # insert into test1(`name`) values('rows_query') |
| mysql-bin.000014 | 271 | Table_map  |    330619 |        326 | table_id: 98 (gangshen.test1)                    |
| mysql-bin.000014 | 326 | Write_rows  |    330619 |        377 | table_id: 98 flags: STMT_END_F                  |
| mysql-bin.000014 | 377 | Xid        |    330619 |        408 | COMMIT /* xid=3032 */                            |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------+
6 rows in set (0.00 sec)

使用mysqlbinlog工具解析binlog文件

# at 201
#180108  9:12:06 server id 330619  end_log_pos 271 CRC32 0xdc68af8a    Rows_query
# insert into test1(`name`) values('rows_query')
# at 271

解析二进制文件：

公共头部部分省略。。。
2e //str_len:执行语句的长度，转换为10进制为46
696e7365727420696e746f20746573743128606e616d6560292076616c7565732827726f77735f71756572792729  //statement:执行的语句，转换为ascii为insert into test1(`name`) values('rows_query')
8a af 68 dc //check_sum

参考链接

https://dev.mysql.com/doc/internals/en/event-meanings.html

MySQL Binlog(四)—— MySQL binlog event解析——基础知识

Posted on 2019-12-07 Edited on 2022-02-14 In MySQL

MySQL Binlog(四)—— MySQL binlog event解析——基础知识

前言

MySQL复制实质上就是讲binlog同步到从库上进行应用，前面的文章已经讲了如何建立复制连接，从这篇文章开始，我们来讲讲binlog里面的内容，看看主从之间同步具体同步了哪些内容，首先会介绍一些关于binlog event的基础知识，之后的文章中会详细对binlog event进行分析。

event基础知识

event类型

5.6.34版本的MySQL的event类型有：

enum Log_event_type
{ UNKNOWN_EVENT= 0,
  START_EVENT_V3= 1,
  QUERY_EVENT= 2,
  STOP_EVENT= 3,
  ROTATE_EVENT= 4,
  INTVAR_EVENT= 5,
  LOAD_EVENT= 6,
  SLAVE_EVENT= 7,  
  CREATE_FILE_EVENT= 8,
  APPEND_BLOCK_EVENT= 9,
  EXEC_LOAD_EVENT= 10,
  DELETE_FILE_EVENT= 11,
  NEW_LOAD_EVENT= 12,
  RAND_EVENT= 13,
  USER_VAR_EVENT= 14,
  FORMAT_DESCRIPTION_EVENT= 15,
  XID_EVENT= 16,
  BEGIN_LOAD_QUERY_EVENT= 17,
  EXECUTE_LOAD_QUERY_EVENT= 18,
  TABLE_MAP_EVENT = 19,
  PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21,
  PRE_GA_DELETE_ROWS_EVENT = 22,
  WRITE_ROWS_EVENT_V1 = 23,
  UPDATE_ROWS_EVENT_V1 = 24,
  DELETE_ROWS_EVENT_V1 = 25,
  INCIDENT_EVENT= 26,
  HEARTBEAT_LOG_EVENT= 27,
  IGNORABLE_LOG_EVENT= 28,
  ROWS_QUERY_LOG_EVENT= 29,
  WRITE_ROWS_EVENT = 30,
  UPDATE_ROWS_EVENT = 31,
  DELETE_ROWS_EVENT = 32,
  GTID_LOG_EVENT= 33,
  ANONYMOUS_GTID_LOG_EVENT= 34,
  PREVIOUS_GTIDS_LOG_EVENT= 35,
  ENUM_END_EVENT /* end marker */
};

event类型含义

这边话介绍几个常见的event类型所对应的含义，对于不常见或者现在基本上已经不用了的event类型，这边不做过多的描述，想了解详细的内容，可以查看链接:
https://dev.mysql.com/doc/internals/en/event-meanings.html

FORMAT_DESCRIPTION_EVENT

FORMAT_DESCRIPTION_EVENT是每个binlog文件开头的一个event。

ROTATE_EVENT

当MySQL切换至新的binlog文件的时候，MySQL会在旧的binlog文件中写入一个ROTATE_EVENT，表示新的binlog文件的文件名，以及第一个偏移地址。当在数据库中执行FLUSH LOGS语句或者binlog文件的大小超过max_binlog_size就会切换新的binlog文件。

TABLE_MAP_EVENT

QUERY_EVENT

记录更新操作的语句。

WRITE_ROWS_EVENT

在以ROW格式记录的binlog文件中，WRITE_ROWS_EVENT记录了插入的行记录。

UPDATE_ROWS_EVENT

在以ROW格式记录的binlog文件中，UPDATE_ROWS_EVENT记录了更新的行记录。

DELETE_ROWS_EVENT

在以ROW格式记录的binlog文件中，DELETE_ROWS_EVENT记录了删除的行记录。

event字节解析

在MySQL的发展过程中，一共出现过三个版本的event结构：

v1:在MySQL 3.23中使用
v2:在MySQL4.0.2至4.1中使用
v4:在MySQL5.0及以上版本中使用

因为目前MySQL数据库版本基本上都是5.0版本以上的，所以本文就只介绍v4版本的event架构。

event组织架构

v4版本的event主要由event header部分和post-header以及variable part部分组成。

event header是每个event都会有的部分，并且每个event header的格式是相同的，固定为19个字节长度，在event header 中记录了event_length、type_code等信息，可以表示event的长度，event的类型，下一个event的偏移位置等信息，都可以再event header中获取到。
extra_headers指定了除公共头部外的内容，但是目前版本的binlog格式中，这一部分不存在的
fixed_part部分在某些文章中也被称做post-header,每个类型的event之间的post-header部分是不相同的，但是同一类型的event占用的post-header

字节数是一样的。每个类型的event占用多少字节为post-header，这个定义在了Format_description_event中，下面讲Format_description_event的章节中会介绍。

variable part是event真实记录具体信息的部分。

event字节结构表示如下图：

x:y中x表示该部分从多少字节偏移开始，y表示该部分占用多少字节。

timestamp记录的是该事务记录的时间
type_code，记录的是该event的类型，具体的event类型，见上面的Log_event_type
server_id，记录的是执行该event的server的server_id号
event_length，该event的长度，共占多少字节
next_position，下一个event在binlog文件中的偏移位置
flag:标记
extra_headers，额外的头部内容，现在是空的，不存在
fixed part有些时候也被称作post-header
variable part有些时候也被称作payload或者body

event公共头部

从上述的event结构中，可以看到，每个event的header部分的内容结构是一致的，所有的event，都会记录这些信息，这是所有的event的公共头部。所以我们把这块内容单独拿出来讲。

字段	字节数	描述
timestamp	4字节	时间戳
type_code	1字节	记录event类型
server_id	4 字节	记录event生成的MySQL所对应的server_id
event_length	4字节	这个event的字节长度，包括event header
next_position	4字节	下一个event在binlog文件中的偏移位置
flags	2字节

示例:我们拿一个Write_rows_event的字节内容作为示例，解析event内容:

b6 1e 1c 5a //timestamp:小端存储，将16进制转换成10进制为1511792310，将时间戳转换为时间为2017-11-27 22:18:30
1e //type_code:event类型，0x1e转换为10进制为30，查看Log_event_type中，看到30的为WRITE_ROWS_EVENT
0f 27 00 00 //server_id :小端存储，将16进制转换为10进制为9999
31 00 00 00 //event_length :小端存储，将16进制转换为10进制为49
b1 03 00 00 //next_position:小端存储，将16进制转换为10进制为945
00 00 //flags:
body部分省略

参考链接

MySQL Binlog(三)——MySQL 复制流程详解

Posted on 2019-12-01 Edited on 2022-02-14 In MySQL

MySQL Binlog(三)——MySQL 复制流程详解

前言

上一篇文章讲了MySQL协议的基础内容，在本文中我们通过分析MySQL复制流程看看MySQL协议在复制中的使用。

MySQL复制流程概述

按照复制原理中描述的，在备库上提交change master to请求主备数据连接以后

TCP连接阶段：备库和主库之间会建立TCP链接（TCP/IP的三次握手建立连接过程不在本文讨论范畴，这里不详细描述）
用户认证阶段：主库会对备库的用户名密码进行认证，进行MySQL应用层的三次握手
register_slave注册slave阶段：备库通过register_slave将自己注册到主库上（该步骤可忽略），以便master在show slave host时可以查看到哪些slave连接上来了
request_dump请求binlog阶段：备库通过request_dump，请求从主库指定的二进制日志和文件偏移量开始获取所有的二进制日志。
master发送Binlog Event阶段：Master启动binlog dump线程，从指定的二进制日志的对应文件偏移量开始向备库的IO线程发送二进制日志Event。（该步骤在主库上就是读取文件和网络发送的过程，本文档中不详细描述）
备库接收存储Relaylog阶段：备库的IO线程持续接收Event并将Event存储relay log中。具体Event的存储格式以及各种类型的字段在Binlog Event的存储格式请参考后续文章。

接下来的几节将详细对以上2，3，4步的网络协议字节格式、及可能取值等方面进行详细描述

用户认证过程详解

用户认证时序图

1
2
3

Master->Slave:Server Greeting 开始MySQL握手协议
Slave->Master:Login Request 进行账号密码验证登陆
Master->Slave:Response OK 账号密码验证通过

如图所示，

master先发送了greeting网络包；
slave接收到该网络包以后提交帐号密码进行验证；
master接收到用户名密码，验证通过后会通过Response OK 返回表示认证通过。

普通的客户端连接MySQL同样需要经历这三次握手协议。

异常情况

第1步如果slave没有接收到greeting网络包，在超时等待后会报错退出
第2步，如果master没有接收到slave提交的帐号密码，此时，可以在master数据库中看到类似于如下信息：

可以看到当前数据库中会有一个连接连上来，但是在User字段显示是的unauthenticated user，这就是表示连接已经建立，但是用户认证还没有完成。该线程的状态是Receiving from client，表示正在等待client发送用户认证的数据信息。超时等待后，master也会报错并中止对应的线程.

第3步，如果master密码认证错误，会直接发送一个错误包，slave和Master都需要中止这个连接。

接下来几节将详细描述用户认证时序图中1，2，3三个网络包的具体字节分段含义。

Server Greeting步骤

Greeting数据包格式

类型	名字	描述
int<3>	payload_length	数据包中有效信息的长度，不包括数据包头部4个字节的长度
int<1>	sequence_id	数据包序列号
int<1>	协议版本号	表示当前连接使用的协议版本
string< null-terminated >	服务器版本信息	表示当前server端服务器版本
int<4>	thread_id	表示当前连接，在server上的线程ID
string< null-terminated >	scramble data part1	用于密码校验的随机数部分一
int<2>		无意义
int<1>	server_charset	服务器的字符集
int<2>	server_status	服务器状态
int<2>	server_capability	服务器权能标志
int<1>	pkt_scramble_length
string[10]		无意义
string< null-terminated >	scramble data part2	用于密码校验的随机数部分二
string< null-terminated >	scramble plugin name	用于密码校验的插件名称

数据包解析示例

数据包示例：

4e 00 00 数据包长度 //88
00 数据包序号 //0
0a 协议版本号//10
35 2e 37 2e 31 39 2d 6c 6f 67 00 服务器版本信息 //5.7.19-log
72 00 00 00 线程ID //114
7e 1a 15 11 07 0a 7e 1c 00 scramble data part1
ff f7 
53 服务器字符集 //
02 00 服务器状态
ff 81 服务器权能标志
15 pkt_scramble_length
00 00 00 00 00 00 00 00 00 00 无用
0b 2c 62 79 75 7a 3d 01 61 25 6f 22 00 scramble data part2
6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 6f 72 64 00 scramble插件名字 //mysql_native_password

各分段含义解释：

数据包长度：MySQL协议中固定格式，前三个字节表示数据包长度，这个长度不包括固定格式的4个字节
数据包序号：MySQL协议中固定格式，用一个字节表示数据包的序号
协议版本号：MySQL协议的版本序号
服务器版本信息：服务器的版本，这边为5.7.19-log
线程ID：表示master分配给该连接的线程的ID号，与show processlist中的thread id对应
scramble data part1:master 生成的随机数，用于加密密码
服务器字符集：表示master使用的字符集编码
服务器状态:表示服务器状态

在/include/mysql_com.h定义了服务器的状态

#define SERVER_STATUS_IN_TRANS     1
#define SERVER_STATUS_AUTOCOMMIT   2	/* Server in auto_commit mode */
#define SERVER_MORE_RESULTS_EXISTS 8    /* Multi query - next query exists */
#define SERVER_QUERY_NO_GOOD_INDEX_USED 16
#define SERVER_QUERY_NO_INDEX_USED      32
#define SERVER_STATUS_CURSOR_EXISTS 64
#define SERVER_STATUS_LAST_ROW_SENT 128
#define SERVER_STATUS_DB_DROPPED        256 /* A database was dropped */
#define SERVER_STATUS_NO_BACKSLASH_ESCAPES 512
#define SERVER_STATUS_METADATA_CHANGED 1024
#define SERVER_QUERY_WAS_SLOW          2048
#define SERVER_PS_OUT_PARAMS            4096
#define SERVER_STATUS_IN_TRANS_READONLY 8192
#define SERVER_SESSION_STATE_CHANGED (1UL << 14)

服务器权能标志：用于与客户端协商协议方式

在/include/mysql_com.h定义

#define CLIENT_LONG_PASSWORD	1	/* new more secure passwords */
#define CLIENT_FOUND_ROWS	2	/* Found instead of affected rows */
#define CLIENT_LONG_FLAG	4	/* Get all column flags */
#define CLIENT_CONNECT_WITH_DB	8	/* One can specify db on connect */
#define CLIENT_NO_SCHEMA	16	/* Don't allow database.table.column */
#define CLIENT_COMPRESS	32	/* Can use compression protocol */
#define CLIENT_ODBC	64	/* Odbc client */
#define CLIENT_LOCAL_FILES	128	/* Can use LOAD DATA LOCAL */
#define CLIENT_IGNORE_SPACE	256	/* Ignore spaces before '(' */
#define CLIENT_PROTOCOL_41	512	/* New 4.1 protocol */
#define CLIENT_INTERACTIVE	1024	/* This is an interactive client */
#define CLIENT_SSL              2048	/* Switch to SSL after handshake */
#define CLIENT_IGNORE_SIGPIPE   4096    /* IGNORE sigpipes */
#define CLIENT_TRANSACTIONS	8192	/* Client knows about transactions */
#define CLIENT_RESERVED         16384   /* Old flag for 4.1 protocol  */
#define CLIENT_RESERVED2        32768   /* Old flag for 4.1 authentication */
#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */
#define CLIENT_MULTI_RESULTS    (1UL << 17) /* Enable/disable multi-results */
#define CLIENT_PS_MULTI_RESULTS (1UL << 18) /* Multi-results in PS-protocol */
#define CLIENT_PLUGIN_AUTH  (1UL << 19) /* Client supports plugin authentication */
#define CLIENT_CONNECT_ATTRS (1UL << 20) /* Client supports connection attributes */
/* Enable authentication response packet to be larger than 255 bytes. */
#define CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA (1UL << 21)
/* Don't close the connection for a connection with expired password. */
#define CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS (1UL << 22)
/**
  Capable of handling server state change information. Its a hint to the
  server to include the state change information in Ok packet.
*/
#define CLIENT_SESSION_TRACK (1UL << 23)
/* Client no longer needs EOF packet */
#define CLIENT_DEPRECATE_EOF (1UL << 24)
#define CLIENT_SSL_VERIFY_SERVER_CERT (1UL << 30)
#define CLIENT_REMEMBER_OPTIONS (1UL << 31)

scramble data part2：与scramble data part1组成为随机数，用于加密密码
scramble插件名字：指定用于scramble data加密方式的插件,这边为native_mysql_password

本节主要描述slave发送的用户认证的数据包(slave->master)过程

数据包格式

类型	名字	描述
int<3>	payload_length	数据包中有效信息的长度，不包括数据包头部4个字节的长度
int<1>	sequence_id	数据包序列号
int<4>	client_flag	client的标记
int<4>	max_packet_size	允许发送的最大数据包的大小
int<1>	client_charset	client使用的字符集
string[23]		无用
string< null-terminated >	user_name	登陆的用户名
int<1>	client_capability
string[20]	加密后的scramble_data
string< null-terminated >	database_name	要连接的数据库的名称
string< null-terminated >	scramble_plugin_name	用于密码校验的插件名称

数据包解析

数据包示例

58 00 00 数据包长度 //98
01 数据包序号 //1
0d a2 2b 00 client flag
01 00 00 00 max_packet_size
08 client使用的字符集编码 //latin1
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 无用
71 62 65 6e 63 68 00 用户名 //qbench
14 client权能标识
f2 4c b6 e9 4c 0d d7 9d 07 30 c9 21 07 4f 23 51 09 a1 af 47 加密之后的scramble data
6d 79 73 71 6c 00 连接的数据库 //mysql
6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 6f 72 64 00 加密的插件名称 //mysql_native_password

各分段数据含义：

数据包长度：MySQL协议中固定格式，前三个字节表示数据包长度，这个长度不包括固定格式的4个字节
数据包序号：MySQL协议中固定格式，用一个字节表示数据包的序号
client flag ：指定客户端发送数据的一些规范，
max_packet_size:指定客户端发送数据包的最大值，
client使用的字符集编码：client所使用的字符集编码
用户名：用于用户登陆的用户名
client权能标识：client capability
加密之后的scramble data：将用于登陆的密码加密之后得到的结果

参考淘宝内核月报关于native_mysql_password插件加密方式：

client:
hash_stage1 = sha1(password) hash_stage2 = sha1(hash_stage1) reply = sha1(scramble, hash_stage2) ^ hash_stage1
server: (逻辑位于sql/password.c:check_scramble_sha1中, 下文亦有提及)
// mysql.user表中, 对应user的passwd实际上是hash_stage2 res1 = sha1(scramble, hash_stage2) hash_stage1 = reply ^ res1 hash_stage2_reassured = sha1(hash_stage1) 再根据hash_stage2_reassured == hash_stage2(from mysql.user)是否一致来判定是否合法

数据库名称：指定要连接的schema的名称
加密插件的名称：进行加密的插件的名称，这边为native_mysql_password

异常情况

在用户认证过程中，master发送给slave一个Greeting包之后，slave发送一个auth包给master，因为我们是模拟IO线程的过程，所以这个auth包的内容是我们自己组织的，在发送将auth包发送给master进行用户认证的过程中，可能会出现一些异常情况，这样会导致master返回一个非OK包，这边列举可能出现的异常情况。

slave发送的auth包中，用户名和密码错误
master会返回一个ERR包，ERR包中会包含‘ACCESS DENIED’的信息 1045号错误。
slave发送的auth包中，数据组织格式有问题
master会返回一个EOF包，并中断连接。

response ok步骤详解

认证没有问题master将直接返回一个OK包。

register_slave注册slave过程过程详解

register slave 认证时序图

Slave->Master:Request Query 设置master_binlog_checksum（必须）
Master->Slave:Response OK 请求执行成功
Slave->Master:Request Query 设置slave_uuid（非必须）
Master->Slave:Response OK 请求执行成功
Slave->Master:Request Register Slave 发送register slave命令（非必须）
Master->Slave:Response OK 注册slave成功

如图所示，注册slave的过程如下

slave客户端直接SET @master_binlog_checksum= @@global.binlog_checksum
设置正确，返回Ok Packet
设置其他相关配置，比如slave_uuid等
设置正确，返回Ok Packet
slave发送register_slave 注册slave请求
设置正确，返回Ok Packet

注册slave步骤主要是为了方便主库能获得slave的IP/Port等信息，第5步可忽略

异常情况

第1步，第3步如果设置的命令有误，Master将返回Error Packet。
第5步，如果填充的register_slave Packet包错误，Master同样将返回Error Packet

接下来几节将详细描述上述第5步网络包的具体字节分段含义。

Register Slave 步骤

数据包格式

类型	名字	描述
int<3>	payload_length	数据包中有效信息的长度，不包括数据包头部4个字节的长度
int<1>	sequence_id	数据包序列号
int<1>	command_type	发送的请求的类型
int<4>	server_id	slave的server_id
string< null-terminated >	report_host
string< null-terminated >	report_user
string< null-terninated >	report_password
int<2>	port	端口号
string[8]		无用

数据包解析

数据包示例：

12 00 00 数据包长度
00 数据包序号
15 发送的command类型 0x15表示的是COM_REGISTER_SLAVE类型的命令
0f 27 00 00 slave的server_id
00 report host
00 report user
00 report password
ea 0c 端口号
00 00 00 00 00 00 00 00 无用

各分段数据含义：

数据包长度：MySQL协议中固定格式，前三个字节表示数据包长度，这个长度不包括固定格式的4个字节
数据包序号：MySQL协议中固定格式，用一个字节表示数据包的序号
COMMAND类型：表示发送的请求的类型

在my_command.h文件中定义了command的类型：

enum enum_server_command
{
  COM_SLEEP,COM_QUIT,
  COM_INIT_DB,
  COM_QUERY,
  COM_FIELD_LIST,
  COM_CREATE_DB,
  COM_DROP_DB,
  COM_REFRESH,
  COM_SHUTDOWN,
  COM_STATISTICS,
  COM_PROCESS_INFO,
  COM_CONNECT,
  COM_PROCESS_KILL,
  COM_DEBUG,
  COM_PING,
  COM_TIME,
  COM_DELAYED_INSERT,
  COM_CHANGE_USER,
  COM_BINLOG_DUMP,
  COM_TABLE_DUMP,
  COM_CONNECT_OUT,
  COM_REGISTER_SLAVE,
  COM_STMT_PREPARE,
  COM_STMT_EXECUTE,
  COM_STMT_SEND_LONG_DATA,
  COM_STMT_CLOSE,
  COM_STMT_RESET,
  COM_SET_OPTION,
  COM_STMT_FETCH,
  COM_DAEMON,
  COM_BINLOG_DUMP_GTID,
  COM_RESET_CONNECTION,
  COM_END
}

report host:指定report的IP地址
report user:指定report的user
report password：指定report的账户的密码
端口号：指定连接的端口号，等于change master to语句中的port选项

异常情况

如果填充的register_slave Packet包错误，Master同样将返回Error Packet

request dump请求binlog过程详解

request dump 请求时序图

Slave->Master:Request Send Binlog 请求发送binlog
Master->Slave:Rotate Event 主库发送一个Rotate Event内容
Master->Slave:Format Description Event 主库发送一个Format Description Event内容
Master->Slave:··················Binlog Event
Master->Slave:··················Binlog Event
Master->Slave:··················Binlog Event

如图所示，请求binlog的过程如下

slave客户端发送request dump请求，请求中包含binlog dump开始发送二进制文件名称和偏移量
master接收到请求，位置正确的话，会先发送Rotate Event表示接下来的二进制日志将从指定的二进制文件和偏移量开始发送
master紧接着会从指定的二进制文件头读取Format Description Event 并发送给slave，用于slave 确认各个event的header长度等信息。
master从二进制文件和偏移量开始读取二进制数据并通过网络发给Slave

异常情况下：

第1步，如果slave请求的二进制文件或者偏移量不正确，master将返回ERR Packet

Request dump 步骤

数据包格式

类型	名字	描述
int<3>	payload_length	数据包中有效信息的长度，不包括数据包头部4个字节的长度
int<1>	sequence_id	数据包序列号
int<1>	command_type	发送的请求的类型
int<4>	binlog_pos	请求的binlog位置点
int<2>	binlog_flag
int<4>	server_id	slave的server_id
string< reset-of_packet >	binlog_file_name	请求的binlog文件名

数据包解析

1b 00 00 数据包长度
00 数据包序号
12 发送的command类型 0x12表示的是COM_BINLOG_DUMP
9a 00 00 00 binlog位置点
00 00 binlog flag
0f 27 00 00 slave_server_id
6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 30 31binlog文件名

数据包长度：MySQL协议中固定格式，前三个字节表示数据包长度，这个长度不包括固定格式的4个字节
数据包序号：MySQL协议中固定格式，用一个字节表示数据包的序号
COMMAND类型：表示发送的请求的类型
binlog位置点：表示请求的binlog位置
binlog flag：
slave_server_id：表示slave的server_id
binlog文件名：表示请求的binlog的文件名

异常情况

binlog 请求的位置点大于对应binlog_file中最大的位置点
Master返回一个ERR数据包，Err Packet格式参见“3.3.2 ”

示例数据包：

4f 00 00 数据包长度
01 数据包序号
ff ERR数据包标识
d4 04 error code
23 48 59 30 30 30 43 6c 69 65 6e 74 20 72 65 71 75 65 73 74 65 64 20 6d 61 73 74 65 72 20 74 6f 20 73 74 61 72 74 20 72 65 70 6c 69 63 61 74 69 6f 6e 20 66 72 6f 6d 20 70 6f 73 69 74 69 6f 6e 20 3e 20 66 69 6c 65 20 73 69 7a 65 错误信息

各分段数据包含义：

错误号为：1236
错误信息为：Client requested master to start replication from position>filesize

binlog 请求的位置点不是event起始位置点

Master返回一个ERR数据包，Err Packet格式参见上一篇文章中ERR_Packet部分内容。

数据包示例：

e1 00 00 数据包长度
03 数据包序号
ff ERR数据包标识
d4 04 error code
23 48 59 30 30 30 62 6f 67 75 73 20 64 61 74 61 20 69 6e 20 6c 6f 67 20 65 76 65 6e 74 3b 20 74 68 65 20 66 69 72 73 74 20 65 76 65 6e 74 20 27 6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 30 31 27 20 61 74 20 31 36 30 2c 20 74 68 65 20 6c 61 73 74 20 65 76 65 6e 74 20 72 65 61 64 20 66 72 6f 6d 20 27 2f 6f 70 74 2f 6d 79 73 71 6c 2f 64 61 74 61 2f 62 69 6e 6c 6f 67 2f 6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 30 31 27 20 61 74 20 31 32 33 2c 20 74 68 65 20 6c 61 73 74 20 62 79 74 65 20 72 65 61 64 20 66 72 6f 6d 20 27 2f 6f 70 74 2f 6d 79 73 71 6c 2f 64 61 74 61 2f 62 69 6e 6c 6f 67 2f 6d 79 73 71 6c 2d 62 69 6e 2e 30 30 30 30 30 31 27 20 61 74 20 31 37 39 2e 错误信息

各分段含义：

错误号为：1236
错误信息为：bogusdatain log event;the first event’mysql-bin.000001’at160,the last event read from’/opt/mysql/data/binlog/mysql-bin.000001’at123,the last byte read from’/opt/mysql/data/binlog/mysql-bin.000001’at179.

binlog 请求的binlog filename 不存在

Master返回一个ERR数据包

数据包示例：

44 00 00 数据包长度
01 数据包序号
ff ERR数据包标识
d4 04 error code
23 48 59 30 30 30 43 6f 75 6c 64 20 6e 6f 74 20 66 69 6e 64 20 66 69 72 73 74 20 6c 6f 67 20 66 69 6c 65 20 6e 61 6d 65 20 69 6e 20 62 69 6e 61 72 79 20 6c 6f 67 20 69 6e 64 65 78 20 66 69 6c 65 错误信息

各分段数据含义：

错误号为：1236
错误信息为：Could not find first log filename in binary log index file

整体流程回顾

最后我们再整体来看一下MySQL复制链接在建立连接的完整过程

Master与Slave建立TCP/IP连接
Master发送第一个MySQL协议的Greeting包给Slave
Slave根据发送的数据包，加密自己的账户及密码等信息，发送给Master
Master根据Slave发送的auth数据包进行认证，认证成功，则发送一个Response OK数据包给Slave，Slave完成用户登录
Slave发送一个Query的请求，设置当前连接的master_binlog_checksum为@@global.binlog_checksum这一步是必须的，这是设置校验码
Master处理完Slave的Query请求之后，发送一个Response OK数据包给Slave
Slave发送一个Query请求，设置当前连接的slave_uuid为从库的uuid，这一步非必须
Master处理完Slave的Query请求之后，发送一个Response OK数据包给Slave
Slave向Master发送一个Register Slave的数据包，进行slave线程的注册
Master将slave线程注册完成之后，返回一个Response OK数据包
Slave在注册成功之后，向Master发送Binlog Dump命令，发送请求的binlog的文件名以及pos点
Master在收到Slave的Binlog Dump命令之后，首先发送一个Rotate Event的内容给Slave，告诉Slave当前发送的binlog的文件名为什么
Master之后再发送一个Format Description Event内容给Slave，包括版本信息，位置点等信息
接着Master按照Slave的Binlog Dump请求中携带的pos点的信息，从该binlog文件偏移位置为pos的地方将Event内容取出来，发送给Slave

参考链接

MySQL协议官方文档https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html
http://mysql.taobao.org/monthly/2017/08/05/

MySQL Binlog(二)——MySQL 协议基础

Posted on 2019-11-17 Edited on 2022-02-14 In MySQL

MySQL Binlog(二)——MySQL 协议基础

1. 前言

上篇讲了MySQL的复制原理，这篇文章来讲讲建立复制过程中使用的MySQL通讯协议。MySQL协议是用于MySQL中Server端与Client端进行通讯的协议，MySQL主备复制也遵守Server和Client端通讯协议。

2. 基础数据类型

MySQL协议传输中的数据包含两种类型，一种是数值型数据，另一种是字符型数据。

2.1 数值型数据

Protocol::FixedLengthInteger

MySQL使用Protocol::FixedLenthInteger 表示固定长度的整数

类型	长度	对应函数	实际对应类型
int<1>	1 byte
int<2>	2 byte	int2store()	uint16
int<3>	3 byte	int3store()	uint
int<4>	4 byte	int4store()	uint32
int<6>	6 byte	int6store()	ulonglong
int<8>	8 byte	int8store()	ulonglong

Protocol::LengthEncodedInteger

使用Protocol::LengthEncodedInteger类型表示的整数，会根据表示的整数大小占用不同的字节数（1，3，4或9个字节）

整数范围	表示方式	示例
0≤N＜251	1 byte	0x08 表示整数8
251≤N＜2^16	0xFC + 2 byte	0xFC0008 = 0xFC + 0x0008 表示整数260
2^16≤N＜2^24	0xFD + 3 byte	0xFD123456 = 0xFD + 0x123456 表示整数1193299
2^24≤N＜2^64	0xFE + 8 byte	0xFE0000000000000009 = 0xFE + 0x0000000000000009 表示整数263

2.2 字符型数据

Protocol::FixedLengthString 固定长度的字符串

字符串用固定长度表示，例如ERR_Packet中的 sql_state 部分就是固定5个字节长度

Protocol::NullTerminatedString
以0x00结尾的字符串
Protocol::VariableLengthString

在程序运行过程中动态确定长度的字符串

Protocol::LengthEncodedString

在字符串开始用Protocol::LengthEncodedInteger类型整数表示字符串的长度

Protocol::RestOfPacketString

字符串是整个数据包的最后一个组成部分，字符串的长度等于整个数据包的长度减去当前位置

3. 基本协议数据包格式

MySQL中server端与client端进行数据交换的时候，会将要交换的数据，按照上述的协议数据类型封装成数据包，然后通过MySQL协议进行数据包的传输。

3.1 基础数据包的格式定义

3.2 基础数据包的格式解析

MySQL协议中数据包固定由 payload_length + sequence_id + payload 组成。每个数据包前3个字节固定存储payload_length信息，即数据包中有效信息payload的长度，第4个字节表示该数据包在此次通信中的序号。因为payload_length为int<3>类型，所以能表示的最大payload部分长度为2^24-1个字节，也就是说一个数据包能传输的数据量最大为2^24-1字节（合16MB）。

那么对于一次请求中，数据量超过16MB的数据，MySQL是如何处理的呢？

对于payload部分大于等于16MB的，MySQL会将数据进行切分，每16MB为一部分，然后将切分的数据按照数据包的格式封装，进行发送，直到切分的数据部分长度小于16MB。

4. 常见数据包示例

上面介绍了MySQL协议中数据包的基础格式，这一节介绍MySQL协议中常见的数据包类型，不同类型的数据包，其格式都是符合上述的基础数据包格式定义的。但是对于不同数据包类型，其payload部分的数据格式是不同的。

4.1 OK_Packet

对于Client发送的请求，如果执行成功，则Server端会向Client端发送OK_Packet告诉Client端请求执行成功。但是在5.7.5版本之后，OK Packet也可以用于表示EOF信息，原先的 EOF_Packet被废弃了，EOF Packet后面章节会详细描述。

4.1.1 OK_Packet的格式定义

4.1.2 OK Packet的格式解析

上面讲到5.7.5版本之后，OK Packet既可以表示OK Packet也可以表示EOF Packet，那么在5.7.5之后如何区分是OK Packet 还是 EOF Packet呢？

OK_Packet:头部部分是0x00，并且数据包的长度是大于7
EOF_Packet:头部部分是0xFE，并且数据包的长度是小于9

为了保证5.7.5之前版本与之后版本的兼容性，5.7.5之后的MySQL 版本中的client端都会发送CLIENT_DEPRECATE_EOF标志。旧版本client不会发送该标志，所以Server端还是采用专门的EOF_Packet（下面会具体介绍）表示EOF信息，新版本的client在向Server端发送请求时会带上该标志，但是对于旧版本的Server端，无法识别该标志，所以依然是采用专门的EOF_Packet表示EOF信息。

client端发送带CLIENT_PROTOCOL_41标志的数据包示例（内容为16进制格式）：

07 00 00 #数据包长度
02           #数据包序号
00           #头部
00          #影响的记录行数
00          #上一次插入的记录自增值
02 00     #状态标志
00 00     #警告数量

各分段数据含义：

数据包长度：MySQL协议中固定格式，示例中07 00 00表示0x000007，代表数据包长度为7个字节
数据包序号：MySQL协议中固定格式，示例中00表示数据包序号为2
头部：示例中ff表示该数据包为OK_Packet
影响的记录行数：示例中00表示0x00，代表本次client请求共影响0行记录
上一次插入的记录自增值：实例中00表示0x00
状态标志：因为是小端存储，所以示例中02 00表示0x0002，代表Server端开启了auto_commit
警告的数量：因为是小端存储，所以示例中00 00表示0x0000，表示告警数量为0

4.1.3 Session State Information

OK_Packet中也可以记录会话级别的状态改变信息，包括系统变量信息、当前会话使用的schema信息、会话级别状态改变信息、事务特性、事务状态信息。当Client端发送的请求中带有SERVER_SESSION_STATE_CHANGED标志，则在Server端返回的OK_Packet中会带有这些信息。OK_Packet一共支持如下几种会话级别状态信息：

enum enum_session_state_type
{
  SESSION_TRACK_SYSTEM_VARIABLES, /* Session system variables */
  SESSION_TRACK_SCHEMA, /* Current schema */
  SESSION_TRACK_STATE_CHANGE, /* track session state changes */
  SESSION_TRACK_GTIDS,
  SESSION_TRACK_TRANSACTION_CHARACTERISTICS, /* Transaction chistics */
  SESSION_TRACK_TRANSACTION_STATE /* Transaction state */
};

具体可参考官方文档：https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html

4.2 ERR_Packet

该数据包用于返回出现错误信息。
相关代码位置：

1	sql/protocol_classic.cc -> net_send_error_packet()函数

4.2.1 ERR_Packet的格式定义

在5.7.5之前版本中，ERR_Packet采用单独表示格式，5.7.5之后，ERR_Packet与OK_Packet复用相同格式。以下是针对5.7.5之前版本介绍ERR_Packet格式定义。

名字	基本数据类型	类型	描述
header	Protocol::FixedLengthInteger	int<1>	用0xFF表示该数据包是一个ERR Packet
error_no	Protocol::FixedLengthInteger	int<2>	表示错误的类型，小端存储
sql_state_marker	Protocol::VariableLengthString	string[1]	如果Client发送的请求中带有CLIENT_PROTOCOL_41标志，会有这一部分，否则为空
sql_state	Protocol::VariableLengthString	string[5]	如果Client发送的请求中带有CLIENT_PROTOCOL_41标志，会有这一部分，否则为空
err_message	Protocol::RestOfPacketString	string	可读的错误信息详情

4.2.2 ERR_Packet的格式解析

数据包示例（内容为16进制格式）：

4b 00 00  #数据包长度
02            #数据包序号
ff              #ERR_Packet标识
15 04       #错误号
23 32 38 30 30 30 41 63 63 65 73 73 20 64 65 6e 69 65 64 20 66 6f 72 20 75 73 65 72 20 27 72 65 70 6c 27 40 27 31 32 31 2e 31 32 31 2e 30 2e 36 34 27 20 28 75 73 69 6e 67 20 70 61 73 73 77 6f 72 64 3a 20 59 45 53 29  #错误信息

各分段数据含义：

数据包长度：MySQL协议中固定格式，示例中4b 00 00表示0x00004b，代表数据包长度为75个字节
数据包序号：MySQL协议中固定格式，示例中00表示数据包序号为0
头部：示例中ff表示该数据包为ERR_Packet
错误号：因为是小端存储，所以示例中15 04对应16进制0x0415，转换为10进制为1045
错误信息：示例中的内容可以转换为Access denied for user 'repl'@'121.121.0.64'(usingpassword:YES)

4.3 EOF_Packet

表示当前会话可以结束了，结束连接的信号。
相关代码位置:

1	sql/protocol_classic.cc -> write_eof_packet()函数

4.3.1 EOF_Packet的格式定义

名字	基本数据类型	类型	描述
header	Protocol::FixedLengthInteger	int<1>	用0xFE表示该数据包是一个ERR_Packet
warnings	Protocol::FixedLengthInteger	int<2>	警告的数量，如果Client发送的请求中带有CLIENT_PROTOCOL_41标志，会有这一部分，否则为空，小端存储
server_status	Protocol::FixedLengthInteger	int<2>	Server端的状态信息，如果Client发送的请求中带有CLIENT_PROTOCOL_41标志,会有这一部分，否则为空，小端存储

其中server_status内容参照OK_Packet中status_flags字段。

4.3.2 EOF_Packet的格式解析

数据包示例（内容为16进制格式）：

05 00 00  #数据包长度
05            #数据包序号
fe             #头部
00 00       #警告的数量
02 00       #Server端的状态信息

各分段数据含义：

数据包长度：MySQL协议中固定格式，示例中05 00 00表示0x000005，代表数据包长度为5个字节
数据包序号：MySQL协议中固定格式，示例中05表示数据包序号为5
头部：标识是否是EOF数据包，0xFE标识该数据包是EOF_Packet
警告的数量：因为是小端存储，所以示例中00 00表示0x0000，表示告警数量为0
Server端的状态信息：因为是小端存储，所以示例中02 00表示0x0002，代表Server端开启了auto_commit

4.4 Query_Packet

4.4.1 Query_Packet的格式定义

名字	基本数据类型	类型	描述
payload_length	Protocol::FixedLengthInteger	int<3>	数据包中有效信息的长度，不包括数据包头部4个字节的长度，小端存储
sequence_id	Protocol::FixedLengthInteger	int<1>	数据包序列号
command_type	Protocol::FixedLengthInteger	int<1>	要发送的请求的类型
command	Protocol::RestOfPacketString	string	请求内容

4.4.2 Query_Packet的格式解析

以SET @master_binlog_checksum语句请求为例

数据包示例（内容为16进制格式）：

36 00 00    #数据包长度
00              #数据包序号
03              #发送的command类型 0x03表示的是COM_QUERY类型的命令
53 45 54 20 40 6d 61 73 74 65 72 5f 62 69 6e 6c 6f 67 5f 63 68 65 63 6b 73 75 6d 3d 20 40 40 67 6c 6f 62 61 6c 2e 62 69 6e 6c 6f 67 5f 63 68 65 63 6b 73 75 6d   #要发送的请求语句 //SET @master_binlog_checksum=@@global.binlog_checksum

各分段数据含义：

数据包长度：MySQL协议中固定格式，示例中36 00 00表示0x000036，代表数据包长度为36个字节
数据包序号：MySQL协议中固定格式，示例中00表示数据包序号为0
COMMAND类型：表示发送的请求的类型，MySQL支持的命令类型如下：

enum enum_server_command
{
  COM_SLEEP,COM_QUIT,
  COM_INIT_DB,
  COM_QUERY,
  COM_FIELD_LIST,
  COM_CREATE_DB,
  COM_DROP_DB,
  COM_REFRESH,
  COM_SHUTDOWN,
  COM_STATISTICS,
  COM_PROCESS_INFO,
  COM_CONNECT,
  COM_PROCESS_KILL,
  COM_DEBUG,
  COM_PING,
  COM_TIME,
  COM_DELAYED_INSERT,
  COM_CHANGE_USER,
  COM_BINLOG_DUMP,
  COM_TABLE_DUMP,
  COM_CONNECT_OUT,
  COM_REGISTER_SLAVE,
  COM_STMT_PREPARE,
  COM_STMT_EXECUTE,
  COM_STMT_SEND_LONG_DATA,
  COM_STMT_CLOSE,
  COM_STMT_RESET,
  COM_SET_OPTION,
  COM_STMT_FETCH,
  COM_DAEMON,
  COM_BINLOG_DUMP_GTID,
  COM_RESET_CONNECTION,
  COM_END
};

要发送的请求语句：将要发送的语句转换为ASCII对应的16进制，示例中的内容可以转换为：SET @master_binlog_checksum=@@global.binlog_checksum

5. 参考链接

MySQL Binlog(一)——MySQL复制原理基础

Posted on 2019-11-17 Edited on 2022-02-14 In MySQL

MySQL Binlog(一)——MySQL复制原理基础

前言

复制作为MySQL DBA必备技能之一，从本章开始，我们将开启一个文章系列，从MySQL复制原理、MySQL协议、MySQL字段存储以及MySQL binlog event存储几个方面展开介绍。本系列文章主要参考官方文档以及官方Internals Manual，部分通过查看5.6.34以及5.7.22版本源码补全。

本文作为此系列文章第一篇，介绍一下MySQL复制原理的基础知识。

复制原理

如下图所示，MySQL主备复制基于二进制日志binlog。任何数据更改都会写入二进制日志。

数据库管理员搭建主备复制时，只需要在备库change master to指定主库的IP、端口、同步开始的二进制文件和文件偏移量（MySQL 5.6以后支持GTID模式，二进制文件和文件偏移量可以用GTID号集合替换）就可以了。

备库通过IO线程连接主库，接收主库推送过来的二进制日志，并记录到本地的中继日志relaylog；同时也会启动SQL线程将中继日志的数据变更应用到备库本地数据库中.

主库接受到备库IO线程的请求，会专门对该slave启用独立的binlog dump线程，从IO线程指定的二进制文件和文件偏移量开始发送二进制日志；并且在主库有任何新的变更后，在记录到自己的二进制日志的同时也会通过网络推送给备库的IO线程。

复制线程

Master线程

binlog dump线程
dump线程的作用是读取主库上二进制日志中的事件。在复制线程处于正常运行状态时，当事务提交的时候，binlog日志sync到磁盘上之后，MySQL会调用signal_update()函数，这个函数的作用是通知binlog dump线程，binlog日志有更新了，dump线程将产生的增量binlog推送到从库的IO线程；在主从之间建议复制连接的时候，从库IO线程将binlog文件名以及位置点（GTID模式下是发送GTID集合）发送给主句dump线程拉取从库所需binlog。
对于一主多从的情况，master上会有多个binlog dump线程。

Slave线程

I/O线程：
I/O线程的作用就是拉取主库上的binlog日志，在从库上存贮为relay log日志。方便SQL线程中relay log中重放事务。
I/O线程在与主库建立连接的时候，超过slave_net_timeout时间没有建立连接成功，从库就认为这次连接失败，需要重试连接，重试连接的次数由MASTER_RETRY_COUNT决定。
I/O线程在与主库成功建立连接之后，针对可能主库很长时间都没有更新数据的情况，I/O线程采用了心跳机制，I/O线程在空闲的时候，每隔MASTER_HEARTBEAT_PERIOD时间间隔，I/O线程就向主库发送一个心跳包，测试与主库的连接是否正常。
使用mysqlbinlog工具也可以解析relay log日志。
SQL线程：
读取relay log，并且重放relay log中的事务，以达到复制的目的。

复制格式

binlog的格式可以分为三种，从某种意义上来讲也可以说对应着三种复制模式，使用binlog_format控制binlog的格式，该参数的不同值代表了不同的复制模式，下面以该参数的三个值对复制格式进行简单的阐述：

statement：5.1.5之前只支持statement格式（俗称：statement binary replication，缩写：SBR），简单实现了数据同步，但在执行跨裤更新等SQL语句时容易出现主从的数据不一致
row：5.1.5及其之后，新增支持row格式（俗称：row binary replication，缩写：RBR），不再是简单记录SQL执行顺序，而是逐行记录了存储引擎的数据如何变更的，主从之间的数据一致性保障得到大幅度提升
mixed：5.1.8及其之后新增支持mixed格式（俗称：mixed binary replication，缩写：MBR），本质上是让MySQL Server自行根据SQL语句的不同来判断是否需要使用row格式，当出现可能造成主从数据不一致的SQL时（例如：用户自定义函数，跨库SQL等），binlog自动转为row格式记录，否则默认使用statement格式记录
MySQL binlog

什么是binlog

二进制日志（binary log简称binlog）是MySQL中的日志文件，会记录MySQL数据库执行的更改操作。但是只记录更改操作，如SELECT、SHOW等操作，不会被记录到binlog中。binlog对于MySQL而言是很重要的日志文件，它可以有以下功能：

搭建复制

MySQL的复制完全依赖于binlog搭建，复制的原理本质上就是从库从主库上拉取主库的二进制日志，然后将拉取到的日志在从库上进行重放，实现数据的同步。

数据恢复

一般的备份方案，都是特定的时刻对数据库进行备份，没有对数据库进行实时的备份。如果数据库在两个备份时间点之间出现故障，那么恢复数据的时候，最多只能恢复到最近一个备份时间点。但是binlog日志可以基于时间点或者位置点进行数据恢复。在上述故障情况的时候，可以拿最近一个时间点的备份+binlog将数据恢复到故障时刻。

审计

用户可以通过二进制日志中的信息来进行审计，判断是否有对数据局进行注入的攻击。

binlog文件

binlog文件主要包括：

1 2	mysql-bin.00000x mysql-bin.index

mysql-bin.index记录了数据库中还未被purge的binlog文件名。

[root@10-10-90-177 binlog]# cat mysql-bin.index
/home/mysql/data/mysqldata1/binlog/mysql-bin.000001
/home/mysql/data/mysqldata1/binlog/mysql-bin.000002
/home/mysql/data/mysqldata1/binlog/mysql-bin.000003

mysql-bin.00000x是真正的binlog文件，x表示binlog文件的序号，这些二进制文件里面的数据是以二进制的形式保存，可以通过特殊的工具(mysqlbinlog)进行解析，获取人类可读的信息。

binlog文件组成

binlog文件格式有以下特点：

binlog是由event组成，event是binlog的最小组成单元
binlog文件头部固定以4个字节开头，这四个字节称为BINLOG_MAGIC(fe 62 69 6e)
每个binlog文件以一个Format_desc类型的event开始
每个binlog文件以一个Rotate类型的event结束（但也有特殊情况，当数据库出现宕机的情况，重新启动数据库会生成一个新的binlog文件，但是宕机之前的最新binlog文件中，不是以ROTATE_EVENT结束的）
在FORMAT_DESCRIPTION_EVENT和ROTATE_EVENT之间是各种不同event，每个event代表Master上不同的操作。

BINLOG_MAGIC

每个binlog文件以固定的4个字节（fe 62 69 6e）开始，以表示是一个binlog文件，在Linux环境下，我们可以通过hexdump命令查看binlog文件的字节组成。

event

我们有两种方式可以查看，binlog文件中的event组成方式。一种是在MySQL数据库中通过SHOW BINLOG EVENTS IN 'binlog-file-name'的方式查看，还有一种就是通过mysqlbinlog工具查看binlog文件的组成。

在数据库中查看

root@localhost : (none) 10:50:48> show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                                      |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------+
| mysql-bin.000004 |  4 | Format_desc |  3306114 |        120 | Server ver: 5.6.34-log, Binlog ver: 4                    |
| mysql-bin.000004 | 120 | Query      |  3306114 |        201 | BEGIN                                                    |
| mysql-bin.000004 | 201 | Rows_query  |  3306114 |        265 | # insert into test1(`name`) values('woqu')                |
| mysql-bin.000004 | 265 | Table_map  |  3306114 |        320 | table_id: 70 (gangshen.test1)                            |
| mysql-bin.000004 | 320 | Write_rows  |  3306114 |        365 | table_id: 70 flags: STMT_END_F                            |
| mysql-bin.000004 | 365 | Xid        |  3306114 |        396 | COMMIT /* xid=24 */                                      |
| mysql-bin.000004 | 396 | Query      |  3306114 |        477 | BEGIN                                                    |
| mysql-bin.000004 | 477 | Rows_query  |  3306114 |        556 | # update test1 set name='woqu-change' where name = 'woqu' |
| mysql-bin.000004 | 556 | Table_map  |  3306114 |        611 | table_id: 70 (gangshen.test1)                            |
| mysql-bin.000004 | 611 | Update_rows |  3306114 |        674 | table_id: 70 flags: STMT_END_F                            |
| mysql-bin.000004 | 674 | Xid        |  3306114 |        705 | COMMIT /* xid=27 */                                      |
| mysql-bin.000004 | 705 | Query      |  3306114 |        786 | BEGIN                                                    |
| mysql-bin.000004 | 786 | Rows_query  |  3306114 |        854 | # delete from test1 where name = 'woqu-change'            |
| mysql-bin.000004 | 854 | Table_map  |  3306114 |        909 | table_id: 70 (gangshen.test1)                            |
| mysql-bin.000004 | 909 | Delete_rows |  3306114 |        961 | table_id: 70 flags: STMT_END_F                            |
| mysql-bin.000004 | 961 | Xid        |  3306114 |        992 | COMMIT /* xid=28 */                                      |
| mysql-bin.000004 | 992 | Rotate      |  3306114 |        1039 | mysql-bin.000005;pos=4                                    |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------+
17 rows in set (0.00 sec)

可以看到mysql-bin.000004文件中，是以一个Format_desc类型的event开头，以一个Rotate类型的event结尾，中间是各种类型event，这是看binlog文件中event的组成，如果想要查看更详细的event信息，可以通过mysqlbinlog工具来查看

mysqlbinlog工具查看

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171205 10:35:43 server id 3306114  end_log_pos 120 CRC32 0x7d7e496c    Start: binlog v 4, server v 5.6.34-log created 171205 10:35:43
# at 120
#171205 10:49:25 server id 3306114  end_log_pos 201 CRC32 0x24d309ef    Query  thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1512442165/*!*/;
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=1075838976/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 201
#171205 10:49:25 server id 3306114  end_log_pos 265 CRC32 0xef0e9a3a    Rows_query
# insert into test1(`name`) values('woqu')
# at 265
#171205 10:49:25 server id 3306114  end_log_pos 320 CRC32 0xf86a076e    Table_map: `gangshen`.`test1` mapped to number 70
# at 320
#171205 10:49:25 server id 3306114  end_log_pos 365 CRC32 0x653b8de4    Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `gangshen`.`test1`
### SET
###  @1=4 /* INT meta=0 nullable=0 is_null=0 */
###  @2='woqu' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 365
#171205 10:49:25 server id 3306114  end_log_pos 396 CRC32 0x53ca8e9d    Xid = 24
COMMIT/*!*/;
............
# at 705
#171205 10:50:20 server id 3306114  end_log_pos 786 CRC32 0x815b7a14    Query  thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1512442220/*!*/;
BEGIN
/*!*/;
# at 786
#171205 10:50:20 server id 3306114  end_log_pos 854 CRC32 0xc265e50d    Rows_query
# delete from test1 where name = 'woqu-change'
# at 854
#171205 10:50:20 server id 3306114  end_log_pos 909 CRC32 0x3a62ecaf    Table_map: `gangshen`.`test1` mapped to number 70
# at 909
#171205 10:50:20 server id 3306114  end_log_pos 961 CRC32 0x2bf4c689    Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `gangshen`.`test1`
### WHERE
###  @1=4 /* INT meta=0 nullable=0 is_null=0 */
###  @2='woqu-change' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 961
#171205 10:50:20 server id 3306114  end_log_pos 992 CRC32 0x412de6a3    Xid = 28
COMMIT/*!*/;
# at 992
#171205 10:50:23 server id 3306114  end_log_pos 1039 CRC32 0x8372f001  Rotate to mysql-bin.000005  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到第一个event是FORMAT_DESCRIPTION_EVENT,记录了binlog的版本为v4。最后一个event，是ROTATE_EVENT，它记录了下一个binlog文件的文件名以及对应的位置点。

参考链接

官方文档复制章节：https://dev.mysql.com/doc/refman/5.7/en/replication.html

MySQL日常问题两则

Posted on 2019-08-12 Edited on 2022-02-14 In MySQL

1. 前言

近期在给开发小伙伴解决问题的时候，收集了两个个人觉得比较有意思的问题给大家分享一下。一个是在执行ALTER TABLE ADD COLUMN语句时，报了Duplicate entry的错误；另一个是关于在MySQL中正确存取emoji表情的问题。

2. ALTER TABLE ADD COLUMN报Duplicate entry错误

2.1 问题描述

某日系统上线，接到开发小伙伴电话说在上线时，执行一个增加字段的DDL语句脚本时，报错了，错误如下:
ERROR 1062 (23000) at line 1: Duplicate entry 'UR000021426347' for key 'T_CAP_CUST_MIDDLE_INFO_UNIQ_INDEX'
根据错误提示的条件去数据库中查询却只能查到一条记录，并没有重复记录。DDL脚本无法执行，影响后续上线步骤了。当时由于不在现场，了解到的信息只有：

DDL语句脚本中只有两条DDL语句，且都是添加字段的语句
脚本正常运行只需要40-70秒，
当时并不是停业窗口。

考虑到数据库版本为5.6.34，添加字段并不会阻塞DML操作，让开发小伙伴再运行一次试试，结果这次执行成功了，并没有报冲突的错误。线上问题顺利解决，具体原因得线下分析了。虽说解决问题是主要矛盾，但是搞清楚问题原因有着更深层次的意义。

2.2 原因定位

下面就是到了寻找问题原因的时候了，为什么同样的DDL语句脚本第一次执行的时候报了Duplicate entry错误，第二次却顺利运行了。其实问题原因很好找，打开Google，输入关键字mysql alter table add column duplicate entry，搜索结果中很多关键字完全匹配的链接，说明很多人遇到过相同问题。搜索结果中一眼就看到一个链接MySQL Bugs:#76895:Adding new column OR Drop column causes duplicate PK error，看到MySQL Bug就莫名兴奋。通过该Bug链接了解到该问题是Online DDL的一个限制问题，官方认为该问题是一种限制，并不是Bug，所以目前为止还没有得到解决。

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

解释一下就是当执行Oline DDL操作时，MySQL实际上是将DML缓存（该缓存大小由变量 innodb_online_alter_log_max_size控制，默认128M）起来，等DDL执行完成后再将缓存中的DML重新应用到表上。如果有别的线程执行了DML操作，在DDL完成后，应用DML时，可能会出现duplicate entry错误。

2.3 实验验证

上面通过Google找到了理论上可能能解释问题的原因描述，但是还没有实际验证，所以接下来就是线下复现环节。先去找开发同事问了下线上报错的表只有一种操作insert into ... on duplicate key...，且报Duplicate entry的字段上有唯一索引。如果没有冲突的记录则插入，否则就更新。那么验证测试步骤也比较简单了，找一张测试表，执行ALTER TABLE ADD COLUMN操作，并同时执行insert into…on duplicate key…操作，观察DDL语句是否会有报错。

2.3.1 实验环境

redhat-6.7/redhat-7.4
MySQL-5.6.34/MySQL-5.7.22

2.3.2 操作步骤

准备测试环境

col1字段的值与id字段的值是一致的，test表中共有1600W+条记录。

执行DDL同时执行insert into … on duplicate key…操作

序号	会话1	会话2
1	select * from test limit 10;	-
2	-	alter table test add column address varchar(50) default ‘HangZhou’;
3	insert into test (name,col1) values(‘san.zhang’,7) on duplicate key update col1=29; ERROR 1062 (23000): Duplicate entry ‘29’ for key ‘col1’	-
4	-	DDL执行报错，检查发现DDL执行失败 ERROR 1062 (23000): Duplicate entry ‘7’ for key ‘col1’
5	查询test表中数据	-

顺利的复现了线上的问题现象，那说明当时线上就是因为DML更新了相同的唯一属性字段键值导致DDL执行失败，报错。测试过程中想到insert into… on duplicate key…不行，那么replace into 会不会也一样导致问题呢，于是就同样对replace into语句进行了测试。

执行DDL同时执行replace into操作

序号	会话1	会话2
1	select * from test limit 10;	-
2	-	alter table test add column address varchar(50) default ‘HangZhou’;
3	replace into test(id,name,col1) values(1,’gang.shen’,13); Query OK, 3 rows affected (0.00 sec)	-
4	-	DDL执行报错，检查发现DDL执行失败 ERROR 1062 (23000): Duplicate entry ‘13’ for key ‘col1’
5	select * from test where col1=13;	-

后续以同样方式测试了UPDATE、INSERT操作，实验证明都会影响DDL操作的正确执行，在5.7.22版本上也是一样的现象。这边出于篇幅考虑就不将测试过程给出了，感兴趣的读者可以自己实验一把，并且可以验证下在8.0版本中是否仍然存在该问题。

2.4 小结

在这对这个问题做几点总结：

问题原因：Oline DDL的原理简单一点理解就是将DML操作缓存起来，等到DDL执行完成后重新应用缓存中的DML语句，如果在Oline DDL执行过程中，DML操作产生了Duplicate entry错误，并不会直接影响DDL操作，而是在DDL执行完成最终应用DML时报错，导致DDL执行失败。关于Oline DDL执行步骤可以参考：https://yq.aliyun.com/articles/282290。
在MySQL Bug网站上，官方人员回复该现象并不是Bug，而是一种限制。但是个人认为是可以做一些改善的，因为在测试insert into … on duplicate key…以及update和insert语句时，对于执行DML操作的客户端已经直接返回报错了，但是从现象上看MySQL仍然将报错的DML语句放到了Oline DDL的缓存中，如果直接将报错语句从缓存中去除则不会影响DDL的正常执行。这只是个人简单认为，深入的话需要可以通过代码去确认。
Google是位好老师

3. MySQL存取emoji的正确姿势

3.1 问题描述

开发小伙伴在测试环境测试过程中，需要往MySQL数据库中插入emoji表情，但是发现一套测试环境可以插入成功，另一套测试环境不行，且插入成功的那套环境数据库将数据查询出来是????乱码。经过与开发确认后了解到以下几点信息：

表中只有一个字段需要存放emoji表情，开发对该字段单独设置了utf8mb4字符集
可以成功插入成功的测试环境数据库版本为5.6.34，无法插入的测试环境数据库版本为5.7.22
应用连接代码中使用的是utf8字符集

那其实需要解决的问题其实是两个问题：1、5.6版本下emoji存取乱码问题 2、5.7版本emoji无法插入的问题

3.2 实验验证

3.2.1 MySQL-5.6 emoji存取乱码问题

我们先来看5.6版本下emoji存储乱码的问题，理论上从数据库角度考虑，字段字符集已经设置为utf8mb4，应用使用的是utf8字符集连接，插入emoji需要utf8mb4字符集，多半是连接字符集设置的问题。大胆假设已经完成，接下来就是小心求证的过程。

实验环境

数据库版本：5.6.44

sql_mode=’NO_ENGINE_SUBSTITUTION’

default-character-set=utf8

使用utf8字符集连接MySQL，并检查参数设置

创建测试表

插入emoji表情

表情的确能插入数据库，但是insert时有两条warning记录提示是invalid的字符串，并且select查询出来的数据也是一样????乱码

使用utf8mb4字符集插入emoji表情并查询

可以看到连接使用utf8mb4字符集插入、查询emoji表情都是正常的，插入时也没有warning提示，那说明emoji乱码的问题就是因为连接字符集设置不合理导致的。

3.2.2 MySQL-5.7 emoji无法插入的问题

定位了乱码的问题，再来看看emoji无法插入的问题。

实验环境

数据库版本：5.7.22

sql_mode=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

default-character-set=utf8

使用utf8字符集连接MySQL，并检查参数设置

创建测试表

插入emoji表情

插入的时候直接报错了，同样的字符集，同样的语句，5.6版本下emoji就能插入，5.7就直接报错了。5.6->5.7数据库版本问题，如果踩坑比较多的话，还是比较容易联想到是不是sql_mode参数的问题，因为5.7中sql_mode参数默认值多了很多项，对语句的限制加强了很多。

查看sql_mode参数默认值

mysql> show variables like '%sql_mode%'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

查看官方文档关于各个sql_mode选项的值，找到了怀疑对象STRICT_TRANS_TABLES，表示开启严格模式，严格模式下如果插入的数据不在范围之类会报错中断语句。

修改sql_mode参数值，再次插入emoji表情测试

1 2	mysql> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected, 1 warning (0.00 sec)

可以看到修改sql_mode值后，现象与5.6版本下一致

使用utf8mb4字符集插入emoji表情并查询

3.3 小结

在此对emoji问题做一下小结：

MySQL中存储emoji不仅需要将表结构中字段字符集设置为utf8mb4，还需要考虑连接字符集的问题
建议数据库使用5.7.22及以上版本，并且sql_mode开启严格模式，这样当数据出现异常可以及时发现
在sql_mode开启严格模式的情况下，应用端连接数据库也需要调整为utf8mb4字符集才可以正常插入emoji表情
建议将默认字符集设置为utf8mb4，做到库、表、字段字符集都统一，utf8mb4是utf8的超集，能兼容更多的字符，而且在MySQL-8.0中已经将utf8mb4设置为了默认的字符集。

博客地址：https://win-man.github.io/
公众号：欢迎关注