MySQL复制技术的演进

最新推荐文章于 2023-12-18 09:35:34 发布
最新推荐文章于 2023-12-18 09:35:34 发布
阅读量424 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39570637/article/details/119546024
版权

MySQL 复制技术的演进

binlog event

以mysql 5.7来深入学习binlog

环境准备

/* mysql version */
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

insert语句

/* flush logs */
CREATE DATABASE test1;
USE test1;
CREATE TABLE sbtest1(
  id INTEGER NOT NULL AUTO_INCREMENT,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
) /*! ENGINE = innodb */;

INSERT INTO sbtest1(k, c, pad) VALUES(51, '68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441', '22195207048-70116052123-74140395089-76317954521-98694025897');

/*查看binlog内容*/
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
| mysql-bin.000003 |     39366 |
| mysql-bin.000004 |      1408 |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000004' ;
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000004 |    4 | Format_desc    |     33062 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                                                                                                                                                                                                 |
| mysql-bin.000004 |  123 | Previous_gtids |     33062 |         194 | a7a4374b-f8c3-11eb-9e8c-0242ac125002:1-4                                                                                                                                                                                              |
| mysql-bin.000004 |  194 | Gtid           |     33062 |         259 | SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:5'                                                                                                                                                                     |
| mysql-bin.000004 |  259 | Query          |     33062 |         356 | CREATE DATABASE test1                                                                                                                                                                                                                 |
| mysql-bin.000004 |  356 | Gtid           |     33062 |         421 | SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:6'                                                                                                                                                                     |
| mysql-bin.000004 |  421 | Query          |     33062 |         705 | use `test1`; CREATE TABLE sbtest1(
  id INTEGER NOT NULL AUTO_INCREMENT,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
) /*! ENGINE = innodb */         |
| mysql-bin.000004 |  705 | Gtid           |     33062 |         770 | SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:7'                                                                                                                                                                     |
| mysql-bin.000004 |  770 | Query          |     33062 |         843 | BEGIN                                                                                                                                                                                                                                 |
| mysql-bin.000004 |  843 | Rows_query     |     33062 |        1094 | # INSERT INTO sbtest1(k, c, pad) VALUES(51, '68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441', '22195207048-70116052123-74140395089-76317954521-98694025897') |
| mysql-bin.000004 | 1094 | Table_map      |     33062 |        1152 | table_id: 112 (test1.sbtest1)                                                                                                                                                                                                         |
| mysql-bin.000004 | 1152 | Write_rows     |     33062 |        1377 | table_id: 112 flags: STMT_END_F                                                                                                                                                                                                       |
| mysql-bin.000004 | 1377 | Xid            |     33062 |        1408 | COMMIT /* xid=233 */                                                                                                                                                                                                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.01 sec)

分析以上binlog events的信息
每一个binlog文件开头的第一个Event_type都是Format_desc,
第二个event为Previous_gtids,表示上个gtid的值。
开启gtid后，事务的binlog都以gtid event开头
Table_map表示为gtid与表的映射关系
STMT_END_F作为一个标志位，为事务的结束标志。
XID事务提交标志
下面是详细的binlog信息

[root@chmysql1 archive]# mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
// 每一个binlog文件都以 4 开始
#210809  3:50:37 server id 33062  end_log_pos 123 CRC32 0xc584a517 	Start: binlog v 4, server v 5.7.30-log created 210809  3:50:37
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210809  3:50:37 server id 33062  end_log_pos 194 CRC32 0x0cc1ea6a 	Previous-GTIDs
# a7a4374b-f8c3-11eb-9e8c-0242ac125002:1-4
# at 194
#210809  3:51:04 server id 33062  end_log_pos 259 CRC32 0x6641a622 	GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:5'/*!*/;
# at 259
#210809  3:51:04 server id 33062  end_log_pos 356 CRC32 0x48c1890e 	Query	thread_id=204	exec_time=0	error_code=0
SET TIMESTAMP=1628481064/*!*/;
SET @@session.pseudo_thread_id=204/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE test1
/*!*/;
# at 356
#210809  3:51:56 server id 33062  end_log_pos 421 CRC32 0x71c6222e 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:6'/*!*/;
# at 421
#210809  3:51:56 server id 33062  end_log_pos 705 CRC32 0x157fc4d0 	Query	thread_id=204	exec_time=0	error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1628481116/*!*/;
CREATE TABLE sbtest1(
  id INTEGER NOT NULL AUTO_INCREMENT,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
) /*! ENGINE = innodb */
/*!*/;
# at 705
#210809  3:51:56 server id 33062  end_log_pos 770 CRC32 0xf00c966a 	GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:7'/*!*/;
# at 770
#210809  3:51:56 server id 33062  end_log_pos 843 CRC32 0x8a5e24da 	Query	thread_id=204	exec_time=0	error_code=0
SET TIMESTAMP=1628481116/*!*/;
BEGIN
/*!*/;
# at 843
#210809  3:51:56 server id 33062  end_log_pos 1094 CRC32 0xbcafd514 	Rows_query
# INSERT INTO sbtest1(k, c, pad) VALUES(51, '68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441', '22195207048-70116052123-74140395089-76317954521-98694025897')
# at 1094
#210809  3:51:56 server id 33062  end_log_pos 1152 CRC32 0x86960b51 	Table_map: `test1`.`sbtest1` mapped to number 112
# at 1152
#210809  3:51:56 server id 33062  end_log_pos 1377 CRC32 0xd97869c1 	Write_rows: table id 112 flags: STMT_END_F
### INSERT INTO `test1`.`sbtest1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=51 /* INT meta=0 nullable=0 is_null=0 */
###   @3='68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###   @4='22195207048-70116052123-74140395089-76317954521-98694025897' /* STRING(180) meta=65204 nullable=0 is_null=0 */
# at 1377
#210809  3:51:56 server id 33062  end_log_pos 1408 CRC32 0x2af2c644 	Xid = 233      // Xid
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

update语句和delete语句

mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from sbtest1;
+----+----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k  | c                                                                                                                       | pad                                                         |
+----+----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 51 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
+----+----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> begin ;update sbtest1 set c=a where id=1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;delete from sbtest1;
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000005';
mysql> show binlog events in 'mysql-bin.000005';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000005 |    4 | Format_desc    |     33062 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                             |
| mysql-bin.000005 |  123 | Previous_gtids |     33062 |         194 | a7a4374b-f8c3-11eb-9e8c-0242ac125002:1-7                          |
| mysql-bin.000005 |  194 | Gtid           |     33062 |         259 | SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:8' |
| mysql-bin.000005 |  259 | Query          |     33062 |         332 | BEGIN                                                             |
| mysql-bin.000005 |  332 | Rows_query     |     33062 |         391 | # update sbtest1 set c='a' where id=1                             |
| mysql-bin.000005 |  391 | Table_map      |     33062 |         449 | table_id: 112 (test1.sbtest1)                                     |
| mysql-bin.000005 |  449 | Update_rows    |     33062 |         747 | table_id: 112 flags: STMT_END_F                                   |
| mysql-bin.000005 |  747 | Xid            |     33062 |         778 | COMMIT /* xid=252 */                                              |
| mysql-bin.000005 |  778 | Gtid           |     33062 |         843 | SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:9' |
| mysql-bin.000005 |  843 | Query          |     33062 |         916 | BEGIN                                                             |
| mysql-bin.000005 |  916 | Rows_query     |     33062 |         959 | # delete from sbtest1                                             |
| mysql-bin.000005 |  959 | Table_map      |     33062 |        1017 | table_id: 112 (test1.sbtest1)                                     |
| mysql-bin.000005 | 1017 | Delete_rows    |     33062 |        1124 | table_id: 112 flags: STMT_END_F                                   |
| mysql-bin.000005 | 1124 | Xid            |     33062 |        1155 | COMMIT /* xid=265 */                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
14 rows in set (0.00 sec)

再来看下上个binlog文件

| mysql-bin.000004 | 1408 | Rotate         |     33062 |        1455 | mysql-bin.000005;pos=4  |

结尾多了Rotate event
binlog文件

[root@chmysql1 archive]# mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210809  5:51:43 server id 33062  end_log_pos 123 CRC32 0xebd21059 	Start: binlog v 4, server v 5.7.30-log created 210809  5:51:43
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210809  5:51:43 server id 33062  end_log_pos 194 CRC32 0xcc33622e 	Previous-GTIDs
# a7a4374b-f8c3-11eb-9e8c-0242ac125002:1-7
# at 194
#210809  5:52:25 server id 33062  end_log_pos 259 CRC32 0x8f7dedaf 	GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:8'/*!*/;
# at 259
#210809  5:52:23 server id 33062  end_log_pos 332 CRC32 0x1b755423 	Query	thread_id=205	exec_time=0	error_code=0
SET TIMESTAMP=1628488343/*!*/;
SET @@session.pseudo_thread_id=205/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 332
#210809  5:52:23 server id 33062  end_log_pos 391 CRC32 0xf746d102 	Rows_query
# update sbtest1 set c='a' where id=1
# at 391
#210809  5:52:23 server id 33062  end_log_pos 449 CRC32 0x82df281c 	Table_map: `test1`.`sbtest1` mapped to number 112
# at 449
#210809  5:52:23 server id 33062  end_log_pos 747 CRC32 0x973fd9d4 	Update_rows: table id 112 flags: STMT_END_F
### UPDATE `test1`.`sbtest1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=51 /* INT meta=0 nullable=0 is_null=0 */
###   @3='68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###   @4='22195207048-70116052123-74140395089-76317954521-98694025897' /* STRING(180) meta=65204 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=51 /* INT meta=0 nullable=0 is_null=0 */
###   @3='a' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###   @4='22195207048-70116052123-74140395089-76317954521-98694025897' /* STRING(180) meta=65204 nullable=0 is_null=0 */
# at 747
#210809  5:52:25 server id 33062  end_log_pos 778 CRC32 0xbdcbd999 	Xid = 252
COMMIT/*!*/;
# at 778
#210809  5:58:09 server id 33062  end_log_pos 843 CRC32 0x0044c7be 	GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'a7a4374b-f8c3-11eb-9e8c-0242ac125002:9'/*!*/;
# at 843
#210809  5:58:09 server id 33062  end_log_pos 916 CRC32 0x876402c1 	Query	thread_id=206	exec_time=0	error_code=0
SET TIMESTAMP=1628488689/*!*/;
BEGIN
/*!*/;
# at 916
#210809  5:58:09 server id 33062  end_log_pos 959 CRC32 0x0830ff0f 	Rows_query
# delete from sbtest1
# at 959
#210809  5:58:09 server id 33062  end_log_pos 1017 CRC32 0xe396c042 	Table_map: `test1`.`sbtest1` mapped to number 112
# at 1017
#210809  5:58:09 server id 33062  end_log_pos 1124 CRC32 0xf3267ae2 	Delete_rows: table id 112 flags: STMT_END_F
### DELETE FROM `test1`.`sbtest1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=51 /* INT meta=0 nullable=0 is_null=0 */
###   @3='a' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###   @4='22195207048-70116052123-74140395089-76317954521-98694025897' /* STRING(180) meta=65204 nullable=0 is_null=0 */
# at 1124
#210809  5:58:09 server id 33062  end_log_pos 1155 CRC32 0xc7b4535b 	Xid = 265
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

在mysql中，数据修改有个before image和after image的概念（column image 位图），可以理解为原来的数据和新的数据。
对于insert update delete语句这些DML event，有不同的对应关系。

  • INSERT 语句
    记录变更的after image（插入的数据），使用wirte set过滤

  • DELETE 语句
    记录变更的before image（删除的数据），内部使用read set过滤

  • UPDATE 语句
    记录变更的before image和after image（修改的数据），内部使用write set过滤
    受参数binlog_row_image的影响，
    参数值为MINMAL:对于before image，只记录主键或者第一个非空的唯一键到event，其他的after image值之记录修改的字段，包括auto imcremant的值。
    参数值为FULL:对应前后image，记录全部字段。对于闪回工具和诊断问题和修复数据都有帮助。同时设置为full可以让从库在选择索引的时候有更多的选择，从而提高从库应用event的效率

LOGICAL_CLOCK

LOGICAL_CLOCK依赖于mysql的组提交机制，多个事务作为一组，一起进行flush redo日志，sync binlog日志和commit，从而减少了io次数，提高性能。
给予数据库一定压力，让多个队列一起刷新。

sysbench --db-driver=mysql --mysql-host=172.18.80.2  --mysql-port=3306 \
--mysql-user=root --mysql-password=letsg0 \
--mysql-db=test \
--table_size=100000 \
--tables=10 \
--threads=200 \
--time=200000 \
oltp_read_write \
--report-interval=1 \
--db-ps-mode=disable prepare

#210809  6:27:48 server id 33062  end_log_pos 367175355 CRC32 0xfb4abb4d 	GTID	last_committed=366	sequence_number=368	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 367185659 CRC32 0x5578af1d 	GTID	last_committed=366	sequence_number=369	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 368225717 CRC32 0x45af7a35 	GTID	last_committed=366	sequence_number=370	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 369265775 CRC32 0x11576c44 	GTID	last_committed=368	sequence_number=371	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 370305835 CRC32 0x649e6bb3 	GTID	last_committed=368	sequence_number=372	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 371345893 CRC32 0xec8b5552 	GTID	last_committed=370	sequence_number=373	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 372385951 CRC32 0x6e6c6606 	GTID	last_committed=370	sequence_number=374	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 373426009 CRC32 0xbf4bc115 	GTID	last_committed=373	sequence_number=375	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 374466067 CRC32 0x8e004694 	GTID	last_committed=373	sequence_number=376	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 375506127 CRC32 0x972a45ec 	GTID	last_committed=373	sequence_number=377	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 376546185 CRC32 0xe65e6f42 	GTID	last_committed=374	sequence_number=378	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 377586243 CRC32 0x885c9f40 	GTID	last_committed=375	sequence_number=379	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 378626301 CRC32 0x84e5b1a8 	GTID	last_committed=377	sequence_number=380	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 378636607 CRC32 0x92acbbb0 	GTID	last_committed=377	sequence_number=381	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 378636776 CRC32 0xf75ecf5d 	GTID	last_committed=379	sequence_number=382	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 379676834 CRC32 0xf81fdea4 	GTID	last_committed=380	sequence_number=383	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 380716892 CRC32 0x01ec2304 	GTID	last_committed=383	sequence_number=384	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 380727196 CRC32 0xf24c9592 	GTID	last_committed=383	sequence_number=385	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 381767254 CRC32 0x4762b22b 	GTID	last_committed=384	sequence_number=386	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 382807312 CRC32 0x48b56c79 	GTID	last_committed=384	sequence_number=387	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 383847370 CRC32 0x5acf9057 	GTID	last_committed=385	sequence_number=388	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 383857674 CRC32 0x4d54b9ae 	GTID	last_committed=385	sequence_number=389	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 384897732 CRC32 0x905dcb93 	GTID	last_committed=387	sequence_number=390	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 384908036 CRC32 0xdd32af2c 	GTID	last_committed=387	sequence_number=391	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 384918340 CRC32 0xe08abc45 	GTID	last_committed=391	sequence_number=392	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384918509 CRC32 0x107f02e3 	GTID	last_committed=391	sequence_number=393	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384918678 CRC32 0xf5f732d0 	GTID	last_committed=391	sequence_number=394	rbr_only=yes
#210809  6:27:48 server id 33062  end_log_pos 384928982 CRC32 0xda57e425 	GTID	last_committed=394	sequence_number=395	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384929153 CRC32 0x0363a3d9 	GTID	last_committed=395	sequence_number=396	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384929322 CRC32 0xce87211d 	GTID	last_committed=396	sequence_number=397	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384929491 CRC32 0xed4f7c90 	GTID	last_committed=396	sequence_number=398	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384929660 CRC32 0xf16678da 	GTID	last_committed=398	sequence_number=399	rbr_only=no
#210809  6:27:48 server id 33062  end_log_pos 384929829 CRC32 0xe2564de0 	GTID	last_committed=398	sequence_number=400	rbr_only=no

在binlog中每个事务会有多出两个标签
sequence_number：随每个事务递增的自增ID，每次新的binlog会从1开始
last_committed：当前事务所依赖的上次事务的sequence_number，每次新的binlog会从0开始
last_committed相同值的事务代表同时提交的，可以并行回放。
所以： 以last_committed= 396 398 为例，
事务397 398依赖last_committed=396
事务388 400依赖last_committed=398
可以并行回放
所以不需要管具体的事物修改的哪个表，last_committed相同值的事务代表同时提交的，可以并行回放。
优化方式通过调整master group commit size和slave的并行work线程数，提升并行效率。
理论上master组提交的size越大，slave的work线程越多，有更高的并行效率。具体的值还应根据模拟业务测试，找出最优值。

MTS相关主从报错问题

MySQL多线程复制问题处理之Error_code: 1872
从机复制报如下错误：Slave SQL for channel ‘’: Slave failed to initialize relay log info structure from the repository, Error_code: 1872
原因：机房断电，得正常关闭mysql等
正常来说，系统会走crash safe，怎么没有实现crash safe呢？其实,这主要是因为多线程复制（MTS）所引起。不知MySQL 5.7，即使MySQL 5.6也同样会遇到问题。
在MTS场景下，可能会出现以下两个问题：
gap事务：后执行的事务先回放（apply）了
Exec_Master_Log_Pos位置不准确：可能存在已经事务已经提交，但是位置还没更新（单线程复制不存在此问题）
img
由于MTS的原因，后面的事务可能比前面的事务早执行，如上图终可能事务tx2和tx4都已经提交了，但是事务tx1和tx3还未提交。这时就称为存在gap事务。在基于logical_clock的MTS场景下，用户可以通过配置参数slave_preserve_commit_order=1来保证提交的顺序性。

另一方面，这时Exec_Master_Log_Pos也是不准确的，当发生crash时，master info中依然记录的是tx1事务开始执行的位置（见上图右边的部分）。切记，即使将参数slave_preserve_commit_order设置为1，MTS场景下依然不能保证Exec_Master_Log_Pos是准确的，其称之为gap-free low-watermark。因为MTS场景下对于表slave_realy_info_log的更新并不是事务的（这个需要好好体会下）。

然而，MTS场景下引入了新的事务表slave_worker_info，用以表示发生宕机时每个线程更新到的位置，其与Worker线程的回放是事务的。因此，MySQL在恢复的时候可以通过通过Exec_Master_Log_Pos与表slave_worker_info的列Master_log_pos做对比，判断是否需要回放当前事务。

在MySQL 5.7.13版本之前，当发生宕机后需要手动执行如下操作，若直接执行CHANGE MASTER TO操作，则可能会触发上述1872错误：

START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
START SLAVE SQL_THREAD;

MySQL 5.7.13版本后，上述问题将有MySQL自动修复。简单来说，即使发生了宕机，也能准确并自动地恢复复制的运行状态。

gap事务比较好理解，因为不论是基于database级别的MTS，还是基于logical_clock的MTS，都可能存在下面的这种场景：

复制技术演进

5.6 MTS based on schema

在MySQL 5.6版本之前，Slave服务器上有两个线程I/O线程和SQL Thread线程。I/O线程负责接收二进制日志(Binary Log,更准确的说是二进制日志的event)，SQL线程进行回放二进制日志。

7262b2180f1b649287ba996353e3807b.png

如果在MySQL 5.6版本开启并行复制功能，那么SQL线程就变为了coordinator(协调者)线程:

335e82cc4fe8acf13c27beb26da08864.png

coordinator线程主要负责以前两部分的内容：

  • 若判断可以并行执行，那么选择worker线程执行事务的二进制日志。

  • 若判断不可以并行执行，如该操作是DDL，亦或者是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志。

这意味着coordinator线程并不是仅将日志发送给worker线程，自己也可以回放日志，但是所有可以并行的操作交付由worker线程完成。coordinator线程与worker是典型的生产者与消费者模型。

5.6版本存在的问题

上述机制实现了基于schema的并行复制存在两个问题:

  • crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。从代码上看，5.6这里引入了Low-Water-Mark标记来解决该问题，从设计上看(WL#5569)，其是希望借助于日志的幂等性来解决该问题，不过5.6的二进制日志回放还不能实现幂等性。

  • 另一个最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而单库多表是比多库多表更为常见的一种情形。

总结：

涉及不同 schema 的 DML 操作，在 slave 端可以按 schema 粒度并行回放，弱点也很明显，如果实例中的 schema 较少，并行回放效果并不理想。
其优化方式也比较简单 slave_parallel_workers 小于等于 master 的 schema 数量

5.7 LOGICAL_CLOCK

MySQL 5.7 增加了基于 group commit 的并行回放策略，使得 slave 可以接近 master 同样并发回放事务（依赖于组提交机制last commit相同的可以并行回放），master 并发越高，slave 并行回放效果越明显。

为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：DATABASE(默认值，基于库的并行复制方式)、LOGICAL_CLOCK(基于组提交的并行复制方式)。

支持并行复制的GTID

MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。

如果用户没有开启GTID功能，即将参数gtid_mode设置为OFF呢？故MySQL 5.7又引入了称之为Anonymous_Gtid(ANONYMOUS_GTID_LOG_EVENT)的二进制日志event类型

+------------------+-----+----------------+-----------+-------------+--------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------

| mysql-bin.000006 | 4 | Format_desc | 88 | 123 | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |

| mysql-bin.000006 | 123 | Previous_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |

| mysql-bin.000006 | 194 | Anonymous_Gtid | 88 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000006 | 259 | Query | 88 | 330 | BEGIN |

| mysql-bin.000006 | 330 | Table_map | 88 | 373 | table_id: 108 (aaa.t) |

| mysql-bin.000006 | 373 | Write_rows | 88 | 413 | table_id: 108 flags: STMT_END_F |

GTID_LOG_EVENT

当开启GTID时，每一个操作语句(DML/DDL)执行前就会添加一个GTID事件，记录当前全局事务ID；同时在MySQL 5.7版本中，组提交信息也存放在GTID事件中，有两个关键字段last_committed，sequence_number就是用来标识组提交信息的。在InnoDB中有一个全局计数器(global counter)，在每一次存储引擎提交之前，计数器值就会增加。在事务进入prepare阶段之前，全局计数器的当前值会被储存在事务中，这个值称为此事务的commit-parent(也就是last_committed)。

这意味着在MySQL 5.7版本中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid，而这个Anonymous_Gtid事件中就存在着组提交的信息。反之，如果开启了GTID后，就不会存在这个Anonymous_Gtid了，从而组提交信息就记录在非匿名GTID事件中。

组提交机制

976fa078d75f11d5ea3fe1814944fb10.png

e从图中可以看到，只要事务提交(调用ordered_commit)，就都会先加入队列中。而提交有三个步骤，包括FLUSH、SYNC及COMMIT，相应地也有三个队列。

首先要加入的是FLUSH队列，如果某个事务加入时，队列还是空的，则这个事务就担任队长，来代表其他事务执行提交操作。而在其他事务继续加入时，就会发现此时队列已经不为空了，那么这些事务就会等待队长帮它们完成提交操作。

在上图中，事务2-6都是这种坐享其成之辈，事务1就是队长了。不过这里需要注意一点，不是说队长会一直等待要提交的事务不停地加入，而是有一个时限，只有在这个时限之内成功加入到队列的，才能帮它提交。这个时限就是从队长加入开始，到它去处理队列的时间，这个时间实际非常小，基本上就是程序从这行到哪行的一个过程，也没有刻意去等待。

只要对长将这个队列中的事务取出，其他事务就可以加入这个队列了。第一个加入的还是队长，但此时必须要等待。因为此时有事务正在做FLUSH，做完FLUSH之后，其他的对长才能带着队员做FLUSH。而在同一时刻，只能有一个组在做FLUSH。这就是上图中所示的等待事务组2和等待事务组3，此时队长会按照顺序依次做FLUSH，做FLUSH的过程中，有一些重要的事务需要去做，如下：

要保证顺序必须是提交加入到队列的顺序。

如果有新的事务提交，此时队列为空，则可以加入到FLUSH队列中。不过，因为此时FLUSH临界区正在被占用，所以新事务组必须要等待。

给每个事务分配sequence_number，如果是第一个事务，则将这个组的last_committed设置为sequence_number-1.

将带着last_committed与sequence_number的GTID事件FLUSH到Binlog文件中。

将当前事务所产生的Binlog内容FLUSH到Binlog文件中。

这样，一个事务的FLUSH就完成了。接下来，依次做完组内所有事务的FLUSH，然后做SYNC。如果SYNC的临界区是空的，则直接做SYNC操作，而如果已经有事务组在做，则必须要等待。同样地，做完FLUSH之后，FLUSH临界区会空闲出来，哪儿此时再等待这个临界区的组就可以做FLUSH操作了。总而言之，每个步骤都会有事务组在做， 就像一个流水线一样。完成一件产品需要三个工序，每个工序都可以批量来做，那么每个工序车间都不会闲着，都一直重复着相同的事情，最终每个产品都是以完全相同的顺序完成。

到COMMIT时，实际做的是存储引擎提交，参数binlog_order_commits会影响提交行为。如果设置为ON，那么此时提交就变为串行操作了，就以队列的顺序为提交顺序。而如果设置为OFF，提交就不会在这里进行，而会在每个事务(包括队长和队员)做finish_commit(FINISH)时各自做存储引擎的提交操作。组内每个事务做finish_commit是在队长完成COMMIT工序之后进行，到步骤DONE时，便会唤醒每个等待提交完成的事务，告诉他们可以继续了，那么每个事务就会去做finish_commit。而后，自己再去做finish_commit。这样，一个组的事务就都按部就班地提交完成了。现在也可以知道，与这个组中同时在做提交的最多还有另外两个事务，一个是在做FLUSH，一个是在做SYNC。

这就是order commit的原理，这也是LOGICAL_CLOCK并行复制的基础。因为order commit使得所有的事务分了组，并且有了序列号，从库拿到这些信息之后，就可以根据序号放心大胆地做分发了。

但是有没有发现一个问题，每个组的事务数都没有做过特殊处理。因为从时间上说，从队长开始入队，到取队列中的所有事务出来，这之间的时间是非常非常小的，其实就是几行代码的事，也不会有任何费时间的操作，所以在这段时间内其实不会有多少个事务。只有在压力很大，提交的事务非常多的时候，才会提高并发度(组内事务数变大)。不过这个问题也可以解释得通，主库压力小的时候，从库何必要那么大的并发度呢？只有主库压力大的时候，从库才会延迟。

这种情况下也可以通过调整主服务器上的参数binlog_group_commit_sync_delay、binlog_group_commit_sync_no_delay_count。前者表示事务延迟提交多少时间来加大整个组提交的事务数量，从而减少进行磁盘刷盘sync的次数，单位为1/1000000秒，最大值1000000也就是1秒；后者表示组提交的事务数量凑齐多少此值时就跳出等待，然后提交事务，而无需等待binlog_group_commit_sync_delay的延迟时间；但是binlog_group_commit_sync_no_delay_count也不会超过binlog_group_commit_sync_delay设置。几个参数都是为了增加主服务器组提交的事务比例，从而增大从机MTS的并行度。

从库多线程复制分发原理

知道了order commit原理之后，现在很容易可以想到在从库端是如何分发的，从库以事务为单位做APPLY的，每个事务有一个GTID事件，从而都有一个last_committed及sequence_number值，分发原理如下。

从库SQL线程拿到一个新事务，取出last_committed及sequence_number值。

判断当前last_committed是不是大于当前已经执行的sequence_number的最小值(low water mark，下面称lwm)。

如果大于，则说明上一个组的事务还没有完成。此时等待lwm变大，直到last_committed与lwm相等，才可以继续。

如果小于或等于，则说明当前事务与正在执行的组是同一个组，不需要等待。

SQL线程通过统计，找到一个空闲的worker线程，如果没有空闲，则SQL线程转入等待状态，直到找到一个为止。

将当前事务打包，交给选定的worker，之后worker线程会去APPLY这个事务，此时的SQL线程就会处理下一个事务。

说明：上面的步骤是以事务为单位介绍的，其实实际处理中还是一个事件一个事件地分发。如果一个事务已经选定了worker，而新的event还在那个事务中，则直接交给那个worker处理即可。

从上面的分发原理来看，同时执行的都是具有相同last_committed值的事务，这样的执行方式有点如下图所示：

d1a64a958622e2bcd289bd81d42e51ee.png

可以看出，事务都是随机分配到了worker线程中，但是执行的话，必须是一行一行地执行。一行事务个数越多，并行度越高，也说明主库瞬时压力越大。

并行复制配置与调优

  • master_info_repository
master_info_repository
开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。

  • slave_parallel_workers

  • 如果主机上的负载不大，那么组提交的效率就不高，很有可能发生每组提交的事务数量仅有1个，那么在从机的回放时，虽然开启了并行复制，但会出现性能反而比原先的单线程还要差的现象，即延迟反而增大了。

  • slave_preserve_commit_order

MySQL 5.7后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为LOGICAL_CLOCK，但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。开启该参数后，执行线程将一直等待, 直到提交之前所有的事务。当从线程正在等待其他工作人员提交其事务时, 它报告其状态为等待前面的事务提交。所以虽然MySQL 5.7添加MTS后，虽然slave可以并行应用relay log，但commit部分仍然是顺序提交，其中可能会有等待的情况。
当开启slave_preserve_commit_order参数后，slave_parallel_type只能是LOGICAL_CLOCK，如果你有使用级联复制，那LOGICAL_CLOCK可能会使离master越远的slave并行性越差。
但是经过测试，这个参数在MySQL 5.7.18中设置之后，也无法保证slave上事务提交的顺序与relay log一致。 在MySQL 5.7.19设置后，slave上事务的提交顺序与relay log中一致(所以生产要想使用MTS特性，版本大于等于MySQL 5.7.19才是安全的)。

开启MTS需要设置如下参数：

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
slave_pending_jobs_size_max = 2147483648
slave_preserve_commit_order=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
## MySQL 5.7在performance_schema架构下多了以下这些元数据表，用户可以更细力度的进行监控：
mysql> show tables like 'replication%';
+---------------------------------------------+
| Tables_in_performance_schema (replication%) |
+---------------------------------------------+
| replication_applier_configuration           |
| replication_applier_status                  |
| replication_applier_status_by_coordinator   |
| replication_applier_status_by_worker        |
| replication_connection_configuration        |
| replication_connection_status               |
| replication_group_member_stats              |
| replication_group_members                   |
+---------------------------------------------+
8 rows in set (0.00 sec)

## 通过replication_applier_status_by_worker可以看到worker进程的工作情况：
root@localhost : performance_schema 16:04:35> select * from replication_applier_status_by_worker;
+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION                      | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+
|              |         1 |        48 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23537 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         2 |        49 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23538 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         3 |        50 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23540 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         4 |        51 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23539 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         5 |        52 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23541 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         6 |        53 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23542 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         7 |        54 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23543 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         8 |        55 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23544 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         9 |        56 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23529 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        10 |        57 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23530 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        11 |        58 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23531 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        12 |        59 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23532 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        13 |        60 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23533 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        14 |        61 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23534 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        15 |        62 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23535 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        16 |        63 | ON            | daa22a47-f8e6-11eb-b083-c6b7f5f6606c:23536 |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+
16 rows in set (0.00 sec)

那么怎样知道从机MTS的并行程度又是一个难度不小。简单的一种方法(姜承尧给出的)，可以使用performance_schema库来观察，比如下面这条SQL可以统计每个Worker Thread执行的事务数量，在此基础上再做一个聚合分析就可得出每个MTS的并行度:

root@localhost : (none) 16:27:21> SELECT thread_id,count_star FROM performance_schema.events_transactions_summary_by_thread_by_event_name WHERE thread_id IN ( SELECT thread_id FROM performance_schema.replication_applier_status_by_worker);
+-----------+------------+
| thread_id | count_star |
+-----------+------------+
|        85 |       2088 |
|        86 |       1913 |
|        87 |       1810 |
|        88 |       1689 |
|        89 |       1641 |
|        90 |       1611 |
|        91 |       1571 |
|        92 |       1541 |
|        93 |       1510 |
|        94 |       1472 |
|        95 |       1457 |
|        96 |       1426 |
|        97 |       1412 |
|        98 |       1383 |
|        99 |       1343 |
|       100 |       1323 |
+-----------+------------+
16 rows in set (0.00 sec)

总结：

优化方式通过调整 master group commit size 和 slave 的并行 work 线程数，提升并行效率。

master group commit size 和并发压力，以及下面两个参数相关

binlog_group_commit_sync_delay 表示 binlog 提交事务前等待多少微秒

binlog_group_commit_sync_no_delay_count 表示同步队列最大允许的事务数，当等待提交的线程达到多少时, 就不在等待

在 master 低并发的负载下，并行回放效果就不好了，如果想要提高并行度，需要增加 binlog_group_commit_sync_delay，积累较多的分组大小，副作用是拉低 master 吞吐量。

如果MySQL 5.7要使用MTS功能，必须使用最新版本，最少升级到5.7.19版本，修复了很多Bug。

8.0 Write set

MySQL 8.0.1 & 5.7.22 在 group commit 基础上进一步改善了并行复制效率，增加了新的跟踪事务依赖关系的机制。相对于基于事务 commit timestamp，即使 master 低并发场景下也能使 slave 根据事务依赖关系并行重放，充分利用硬件资源，不需要像 MySQL 5.7 增加 binlog_group_commit_sync_delay 延迟的方式，增加可并行的事务，降低复制延迟。

在master上满足以下条件

  1. binlog_format=row
  2. 开启transaction_write_set_extraction=XXHASH64
  3. 更新表必须有主键，如果更新事务包含外键，则退回commit_order方式
  4. binlog_transaction_dependency_tracking = [COMMIT_ORDER | WRITESET | WRITESET_SESSION]

slave上开启slave_parallel_workers

COMMIT_ORDER 基于commit timestamp

不同会话在相同时间执行可以并行回放

WRITESET 变更不同行的操作都可以并行

  • 无主键 退回commit_order模式

开启writeset，测试

可以看到last commit是乱序的，且有重复值

#210809 11:25:13 server id 3306100  end_log_pos 9552066 CRC32 0xb74fd1f7 	GTID	last_committed=1	sequence_number=4158	rbr_only=yes	original_committed_timestamp=1628508314013142	immediate_commit_timestamp=1628508314013142	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9554363 CRC32 0x696a3109 	GTID	last_committed=2894	sequence_number=4159	rbr_only=yes	original_committed_timestamp=1628508314013158	immediate_commit_timestamp=1628508314013158	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9556660 CRC32 0x9365dff9 	GTID	last_committed=3637	sequence_number=4160	rbr_only=yes	original_committed_timestamp=1628508314013180	immediate_commit_timestamp=1628508314013180	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9558957 CRC32 0xd37f5a19 	GTID	last_committed=2575	sequence_number=4161	rbr_only=yes	original_committed_timestamp=1628508314013190	immediate_commit_timestamp=1628508314013190	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9561254 CRC32 0x898123a6 	GTID	last_committed=3170	sequence_number=4162	rbr_only=yes	original_committed_timestamp=1628508314013200	immediate_commit_timestamp=1628508314013200	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9563551 CRC32 0xc7f8b695 	GTID	last_committed=3528	sequence_number=4163	rbr_only=yes	original_committed_timestamp=1628508314013210	immediate_commit_timestamp=1628508314013210	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9565848 CRC32 0xed6f90a2 	GTID	last_committed=2792	sequence_number=4164	rbr_only=yes	original_committed_timestamp=1628508314013233	immediate_commit_timestamp=1628508314013233	transaction_length=2303
#210809 11:25:13 server id 3306100  end_log_pos 9568151 CRC32 0xdb1acaf2 	GTID	last_committed=3661	sequence_number=4165	rbr_only=yes	original_committed_timestamp=1628508314013243	immediate_commit_timestamp=1628508314013243	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9570448 CRC32 0x9d8ea13d 	GTID	last_committed=1	sequence_number=4166	rbr_only=yes	original_committed_timestamp=1628508314013254	immediate_commit_timestamp=1628508314013254	transaction_length=2299
#210809 11:25:13 server id 3306100  end_log_pos 9572747 CRC32 0x9355034d 	GTID	last_committed=1713	sequence_number=4167	rbr_only=yes	original_committed_timestamp=1628508314013276	immediate_commit_timestamp=1628508314013276	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9575044 CRC32 0xb40ae858 	GTID	last_committed=3519	sequence_number=4168	rbr_only=yes	original_committed_timestamp=1628508314013285	immediate_commit_timestamp=1628508314013285	transaction_length=2299
#210809 11:25:13 server id 3306100  end_log_pos 9577343 CRC32 0xac8c38ef 	GTID	last_committed=2266	sequence_number=4169	rbr_only=yes	original_committed_timestamp=1628508314013295	immediate_commit_timestamp=1628508314013295	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9579640 CRC32 0xaa5a0553 	GTID	last_committed=1	sequence_number=4170	rbr_only=yes	original_committed_timestamp=1628508314013305	immediate_commit_timestamp=1628508314013305	transaction_length=2301
#210809 11:25:14 server id 3306100  end_log_pos 9581941 CRC32 0xac3581b2 	GTID	last_committed=1	sequence_number=4171	rbr_only=yes	original_committed_timestamp=1628508314013329	immediate_commit_timestamp=1628508314013329	transaction_length=2297
#210809 11:25:13 server id 3306100  end_log_pos 9584238 CRC32 0x24d8bf49 	GTID	last_committed=3547	sequence_number=4172	rbr_only=yes	original_committed_timestamp=1628508314013340	immediate_commit_timestamp=1628508314013340	transaction_length=2297
#210809 11:25:14 server id 3306100  end_log_pos 9586535 CRC32 0x7cd55894 	GTID	last_committed=3212	sequence_number=4173	rbr_only=yes	original_committed_timestamp=1628508314013350	immediate_commit_timestamp=1628508314013350	transaction_length=2301
#210809 11:25:14 server id 3306100  end_log_pos 9588836 CRC32 0x495b4c4d 	GTID	last_committed=1532	sequence_number=4174	rbr_only=yes	original_committed_timestamp=1628508314028491	immediate_commit_timestamp=1628508314028491	transaction_length=2297
#210809 11:25:14 server id 3306100  end_log_pos 9591133 CRC32 0xf317ec3a 	GTID	last_committed=3817	sequence_number=4175	rbr_only=yes	original_committed_timestamp=1628508314028514	immediate_commit_timestamp=1628508314028514	transaction_length=2297
#210809 11:25:14 server id 3306100  end_log_pos 9593430 CRC32 0xff89ff70 	GTID	last_committed=2656	sequence_number=4176	rbr_only=yes	original_committed_timestamp=1628508314028535	immediate_commit_timestamp=1628508314028535	transaction_length=2299
#210809 11:25:14 server id 3306100  end_log_pos 9595729 CRC32 0xed449ec3 	GTID	last_committed=3735	sequence_number=4177	rbr_only=yes	original_committed_timestamp=1628508314028547	immediate_commit_timestamp=1628508314028547	transaction_length=2297
#210809 11:25:14 server id 3306100  end_log_pos 9598026 CRC32 0x71c63ccd 	GTID	last_committed=4043	sequence_number=4178	rbr_only=yes	original_committed_timestamp=1628508314028582	immediate_commit_timestamp=1628508314028582	transaction_length=2299
#210809 11:25:14 server id 3306100  end_log_pos 9600325 CRC32 0x4a5d77e5 	GTID	last_committed=4070	sequence_number=4179	rbr_only=yes	original_committed_timestamp=1628508314028597	immediate_commit_timestamp=1628508314028597	transaction_length=2297
#210809 11:25:14 server id 3306100  end_log_pos 9602622 CRC32 0x93166e61 	GTID	last_committed=3632	sequence_number=4180	rbr_only=yes	original_committed_timestamp=1628508314034720	immediate_commit_timestamp=1628508314034720	transaction_length=2297

writeset原理

对主库来说，writeset对LOGICAL_CLOCK多线程复制的优化，并不是在binlog的写法格式上，而是在写binlog生成last committed的时候，做了大量优化。

对比：

  • commit order

    基于上一个group commit的最大的seq number值，作为本次last commit的值（即主库事务进入prepare阶段时获取的已提交的事物的最大sequment_number）的值。

    一个group commit的所有事务可以并行回放

  • write set

    通过唯一键来区分不同的记录，然后和行记录的库表属性以及数据属性一起计算得到hash值，计算出的writeset值存放到一张hash表中。

    后面的新的事务如果计算出的hash值在hash表中无匹配记录，那么此新的事务不会产生新的last committed值，相当于复用上一个事务的last committed值

    ## 抽象成代码
## 计算WRITESET
def get_writeset(index_name,db_name,db_name_length,table_name,table_name_length,value,value_length):
  ... ...
  WRITESET=hash(index_name,db_name,db_name_length,table_name,table_name_length,value,value_length)
  ... ...
  AddWriteCollection.append(WRITESET)
  return WRITESET

## NEW TRX ... GROUP COMMIT ... WRITE BINLOG FILE
trx_writeset=get_writeset(index_name,db_name,db_name_length,table_name,table_name_length,value,value_length)
if trx_writeset in AddWriteCollection :
    //不存在事务冲突，可以跟上一个事务写到一个binlog group
else :
  //匹配到记录，存在事务冲突，产生新的last committed（产生了一个新的binlog group）

    根据系统变量binlog_transaction_dependency_tracking的设置，采取不同的依赖模式生成last_commited

    相同的last commit值的trx不存在事务冲突，所以可以并行回放。

    使用场景

    高并发，大事务，使用writeset多线程复制中的WRITESET、WRITESESSION依赖模式能欧大大提高主库二进制日志中last committed的重复率。提高多线程复制的效率。

    主从库的设置

    主库

    slave_parallel_type : LOGICAL_CLOCK
transaction_write_set_extraction : 
binlog_transaction_dependency_tracking : WRITESET | WRITESET_SESSION ## 设置一个即可。 WRITESET_SESSION在WRITESET的基础上，保证同一个会话内的事务不可并行。
binlog_transaction_dependency_history_size : 25000

    从库

    slave_preserve_commit_order : ON
slave_parallel_workers : N
slave_parallel_type : LOGICAL_CLOCK

参考文章：
https://blog.csdn.net/ActionTech/article/details/106688185
https://www.modb.pro/db/27705
https://www.cnblogs.com/DataArt/p/10240093.html
https://www.percona.com/blog/tag/parallel-replication/
《数据生态：MySQL复制技术与生产实践》 罗小波 沈刚
《深入理解MySQL主从原理》 高鹏

陈同学别秀了
关注
专栏目录
阿里SQL又爆神作数据生态：MySQL复制技术与生产实践笔记
lt_xiaodou的博客
10-14 547
1.1 适用场景1.2 数据同步方法1.3 数据同步类型1.4 复制格式32.1 操作环境信息32.2 主从库的表字段数不同时如何复制32.3 不同类型字段的复制32.4 小结。
mysql复制技术_MySQL-复制技术演进过程
weixin_30125497的博客
02-02 101
复制技术的演进可以分为:基于数据安全的复制,基于效率的复制基于数据安全的复制异步复制参考:首先确保master数据库上开启了二进制日志，这是复制的前提。在slave准备开始复制时，首先要执行change master to语句设置连接到master服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制binlog，这些信息在连接的时候会记录到slave的datadir下的ma...
MySQL并发复制演进
MYSQL轻松学的专栏
11-09 231
​MySQL5.5及以前的复制 一般主从复制有三个线程且都是单线程： Binlog Dump（主） --> IO Thread（从） --> SQL Thread（从）。 1、master节点的Binlog dump Thread，当slave节点与master正常连接的时候，master把更新的binlog内容推送到slave节点。 2、slave节点的I/O Thread ，该线程通过读取master节点binlog日志名称以及偏移量信息将其拷贝到本地relay l...
高性能MySql进化论(十四):复制(Replication)
不积跬步，无以至千里；不积小流，无以成江海！
01-21 1927
复制概述      Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的
MySQL5.7并发复制演进
MYSQL轻松学的专栏
08-17 1176
MySQL5.5及以前的复制 一般主从复制有三个线程且都是单线程： Binlog Dump（主） --> IO Thread（从） -->  SQL Thread（从）。 1、master节点的Binlog dump Thread，当slave节点与master正常连接的时候，master把更新的binlog内容推送到slave节点。 2、slave节点的I
MySQL 复制技术的发展
鸭梨的博客
08-05 309
MySQL 由于其单机机能的限制，很早就发展了数据复制技术以提高性能。同时依赖该技术，MySQL 可用性也得到了长足的发展。 截止到现在，该技术经历了四代的发展。 第一代为传统复制，使用 MHA（Master High Available）架构； 第二代是基于 GTID 的复制，即 GTID+Binlog server 的模式； 第三代为增强半同步复制，GTID+增强半同步复制； 第四代为 MySQL 原生高可用，即 MySQL InnoDB Cluster。 数据库的复制技术需要考虑两个因素：数据一致
MySQL复制技术与MySQL Fabric未来路线图
"CON2615_Thalmann_Soares_...由于提供的内容有限，具体的细节和技术讨论无法展开，但可以肯定的是，这份文档对于理解MySQL复制技术的演进和未来趋势具有重要价值，特别是对于那些依赖于MySQL的企业和开发者来说。
MySQL/MariaDB 并发复制架构演变
06-09
MySQL/MariaDB数据库实战教学视频，该课程主要围绕MySQL/MariaDB 主备复制架构原理；Binlog 二阶段和组提提交原理；MySQL/MariaDB Enhanced Multi-threaded Slaves。SDCC秉承干货实料的原则，旨在为技术人提供高品质...
MySQL主从复制技术演进趋势：从业者必知，把握技术前沿
[MySQL主从复制技术演进趋势：从业者必知，把握技术前沿](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d6529adde3ab4eeaa5ea2ce3554e2ed0~tplv-k3u1fbpfcp-zoom-in-crop-mark:1512:0:0:0.awebp?) # 1. MySQL...
高性能MySQL分析
陈晨_软件五千言
05-19 862
文章目录Schema与数据类型优化选择优化的数据类型Scheme设计中的陷阱创建高性能索引高性能的索引策略查询性能优化慢查询基础：优化数据访问是否请求了不需要的数据是否在扫描额外的记录重构查询的方式一个复杂查询还是多个简单查询切分查询分解关联查询执行查询的基础Mysql客户端/服务器通信协议查询状态查询缓存查询优化处理语法解析器和预处理查询优化器MySQL执行关联查询关联查询优化器排序优化查询执行引擎查询优化的局限性优化特定类型的查询优化count查询优化关联查询优化子查询优化group by和distin
数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
最新发布
Hanko的专栏
12-18 2413
数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》数据意外删除是数据库管理中常见的问题之一。MySQL作为广泛使用的数据库管理系统，当数据意外删除时，有几种方法可以尝试恢复数据。
mysql数据库f复制 语句_MYSQL 复制详解
weixin_39983350的博客
01-18 409
MySql 复制介绍MySQL复制允许将主实例(master)上的数据同步到一个或多个从实例(slave)上，默认情况下复制是异步进行的，从库也不需要一直连接到主库来同步数据MySQL复制的数据粒度可以是主实例上所有的数据库，也可以是指定的一个或多个数据库，也可以是一个数据库里的指定的表MySQL复制所带来的优势在于：扩展能力：通过复制功能可以将MySQL的性能压力分担到一个或多个slave上。这...
mysqlbinlog命令介绍（远程拉取binlog日志）
lgq2016的博客
07-20 3165
远程拉取binlog日志
MySql之在windows MySql5.5开启binlog步骤及注意事项
MarkHome
03-25 3711
查看是否开启了binlog：show binary logs; 查看binlog日志开关是否打开:（log_bin=OFF表示关闭，ON表示打开） mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name ...
mysql回滚update_MySQL【Update误操作】回滚
weixin_30542979的博客
01-19 2104
mysql5.7binlog/*!*/;#at15937710#at15937814#17052613:00:15serverid1end_log_pos15938129CRC320x06901892Table_map:`service`.`apply`mappedtonumber108#at15938129#17052613:00:15server...
mysql差异备份
从此踏上一条不归路
02-22 3368
mysql差异备份1. mysql差异备份 1. mysql差异备份 //开启mysql服务的二进制日志功能 [root@xaii-59 ~]# vim /etc/my.cnf [root@xaii-59 ~]# tail /etc/my.cnf basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock por...
再说MySQL中的 table_id
我的Oracle DBA之路
06-18 955
最近线上一个实例出现了主从数据不一致的情况,也即从库丢失数据的情况。根本原因:"由于table_list->table_id为uint,而m_table_id为ulong，主库上assign的table map id 总是一直递增的  当超过2^32后，备库出现溢出，导致row模式下备库对应table id的事件全部丢失,产生主备不一致。"  【问题分析】  一 table_id 介绍 
浅析MySQL基于ROW格式的二进制日志
weixin_33895695的博客
08-18 146
上文分析的二进制日志实际上是基于STATEMENT格式的，下面我们来看看基于ROW格式的二进制日志，毕竟，两者对应的binlog事件类型也不一样，同时，很多童鞋反映基于ROW格式的二进制日志无法查到原生的DML语句，关于这个问题，其实官方也给出了解决方案，下面，将一一揭晓。   首先，来几条测试数据 mysql> set binlog_format=row; Query OK, ...
Oracle MySQL复制技术进展与应用概览
本资源是一份关于MySQL复制技术的详细讲解，由Oracle的首席技术顾问Ivan Ma撰写于2015年12月19日。文章标题是"2-IvanMa-MySQL複製的演進和應用-TWN- V1"，并包含了一个安全港声明，指出内容仅为一般产品方向的概述，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值