背景
MySQL内部分布式事务是通过二阶段提交和XA实现，中间件实现分布式事务有多种方式，比如参照MySQL的二阶段提交，即使用一个协调者实现，或者使用XA，即记录状态信息在table中。本文是验证MySQL内部分布式事务的现象，以及对比MySQL5.6、MySQL5.7、MGR的分布式事务差异
测试环境
- CentOS 7.4
操作步骤（单机版MySQL5.6）
情况1
root@localhost : (none) 05:14:28> use test
Database changed
root@localhost : test 05:14:29> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
root@localhost : test 05:14:30> xa start 'my1';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 05:14:35> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:14:47> xa end 'my1';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 05:14:55> exit 重新登录到MySQL
[root@my6-master /]# my
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.43-log MySQL Community Server (GPL)
...
root@localhost : (none) 05:15:17> xa recover;
Empty set (0.00 sec) binlog
[root@my6-master archive]# mysqlbinlog -vv mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190314 17:14:13 server id 3306102 end_log_pos 120 CRC32 0xbc168bcd Start: binlog v 4, server v 5.6.43-log created 190314 17:14:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
'/*!*/;
# at 120
#190314 17:14:13 server id 3306102 end_log_pos 151 CRC32 0xbcd00440 Previous-GTIDs
# [empty]
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 情况2
root@localhost : (none) 05:15:22> xa start 'my2';
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 05:15:29> use test
Database changed
root@localhost : test 05:15:32> insert into t3 values(2);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:15:33> xa end 'my2';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 05:15:38> xa prepare 'my2';
Query OK, 0 rows affected (0.59 sec)
root@localhost : test 05:15:45> exit [root@my6-master /]# my
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.43-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost : (none) 05:15:55> xa recover; Empty set (0.00 sec) [root@my6-master archive]# mysqlbinlog -vv mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190314 17:14:13 server id 3306102 end_log_pos 120 CRC32 0xbc168bcd Start: binlog v 4, server v 5.6.43-log created 190314 17:14:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120
#190314 17:14:13 server id 3306102 end_log_pos 151 CRC32 0xbcd00440 Previous-GTIDs
# [empty]
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 情况3：
root@localhost : (none) 05:15:59> xa start 'my3';
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 05:27:23> use test
Database changed
root@localhost : test 05:27:25> insert into t3 values(3);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:27:27> xa end 'my3';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 05:27:37> xa prepare 'my3';
Query OK, 0 rows affected (0.38 sec)
root@localhost : test 05:27:45> xa commit 'my3';
Query OK, 0 rows affected (0.11 sec) [root@my6-master archive]# mysqlbinlog -vv mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190314 17:14:13 server id 3306102 end_log_pos 120 CRC32 0xbc168bcd Start: binlog v 4, server v 5.6.43-log created 190314 17:14:13 at startup
# at 120
#190314 17:14:13 server id 3306102 end_log_pos 151 CRC32 0xbcd00440 Previous-GTIDs
# [empty]
# at 151
#190314 17:27:50 server id 3306102 end_log_pos 199 CRC32 0x4d06f9ac GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '269a748c-3521-11e9-bd4d-0242ac110005:1'/*!*/;
# at 199
#190314 17:27:27 server id 3306102 end_log_pos 276 CRC32 0xe3a7d664 Query thread_id=14 exec_time=0 error_code=0
SET TIMESTAMP=1552555647/*!*/;
SET @@session.pseudo_thread_id=14/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\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 276
#190314 17:27:27 server id 3306102 end_log_pos 324 CRC32 0x15130f72 Rows_query
# insert into t3 values(3)
# at 324
#190314 17:27:27 server id 3306102 end_log_pos 369 CRC32 0x36536fac Table_map: `test`.`t3` mapped to number 70
# at 369
#190314 17:27:27 server id 3306102 end_log_pos 409 CRC32 0x61db4371 Write_rows: table id 70 flags: STMT_END_F
BINLOG '
### INSERT INTO `test`.`t3`
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
# at 409
#190314 17:27:50 server id 3306102 end_log_pos 487 CRC32 0x39bc761d Query thread_id=14 exec_time=0 error_code=0
SET TIMESTAMP=1552555670/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 小结：
- MySQL 5.6 XA 事务分别在xa end和xa prepare后会话退出/崩溃，xa事务会回滚掉，xa prepare之前所有操作无法在binlog中记录
- xa recovery无法记录没有commit的事务
- MySQL 5.6 XA 事务和普通事务记录binlog的形式是一样的，没有体现 xa prepare
操作步骤（单机版MySQL5.7）
root@localhost : test 04:57:25> xa start 'my1';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:57:31> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 04:57:52> xa end 'my1';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:57:59> exit [root@master /]# my
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25-log MySQL Community Server (GPL)
...
root@localhost : (none) 04:58:14> xa recover;
Empty set (0.00 sec)
root@localhost : test 04:58:16> select * from t3;
Empty set (0.00 sec) [root@master archive]# mysqlbinlog -vv mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190314 16:57:13 server id 3306102 end_log_pos 123 CRC32 0x2035b77c Start: binlog v 4, server v 5.7.25-log created 190314 16:57:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
# at 123
#190314 16:57:13 server id 3306102 end_log_pos 154 CRC32 0x31517bb7 Previous-GTIDs
# [empty]
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*/; root@localhost : (none) 04:58:33> xa start 'my2';
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 04:58:47> use test
Database changed
root@localhost : test 04:58:50> insert into t3 values(2);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 04:58:51> xa end 'my2';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 04:58:57> xa prepare 'my2';
Query OK, 0 rows affected (0.12 sec)
root@localhost : test 04:59:06> exit;
Bye [root@master archive]# mysqlbinlog -vv mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190314 16:57:13 server id 3306102 end_log_pos 123 CRC32 0x2035b77c Start: binlog v 4, server v 5.7.25-log created 190314 16:57:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#190314 16:57:13 server id 3306102 end_log_pos 154 CRC32 0x31517bb7 Previous-GTIDs
# [empty]
# at 154
#190314 16:59:05 server id 3306102 end_log_pos 219 CRC32 0x8aa31af9 GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '27c3e750-2376-11e9-aa51-0242ac110002:1'/*!*/;
# at 219
#190314 16:58:51 server id 3306102 end_log_pos 310 CRC32 0x7f0987fa Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1552553931/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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 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/*!*/;
XA START X'6d7932',X'',1
/*!*/;
# at 310
#190314 16:58:51 server id 3306102 end_log_pos 358 CRC32 0xe885b2a1 Rows_query
# insert into t3 values(2)
# at 358
#190314 16:58:51 server id 3306102 end_log_pos 403 CRC32 0x2a2039e0 Table_map: `test`.`t3` mapped to number 109
# at 403
#190314 16:58:51 server id 3306102 end_log_pos 443 CRC32 0x0fcb989e Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t3`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
# at 443
#190314 16:59:05 server id 3306102 end_log_pos 532 CRC32 0xe69eb8b2 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1552553945/*!*/;
XA END X'6d7932',X'',1
/*!*/;
# at 532
#190314 16:59:05 server id 3306102 end_log_pos 571 CRC32 0x47bd8151 XA PREPARE X'6d7932',X'',1
XA PREPARE X'6d7932',X'',1
/*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/; [root@master /]# my
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25-log MySQL Community Server (GPL)
...
root@localhost : (none) 04:59:36> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 3 | 0 | my2 |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
root@localhost : (none) 05:00:08> use test
Database changed
root@localhost : test 05:00:12> xa commit 'my2';
Query OK, 0 rows affected (0.36 sec)
root@localhost : test 05:00:20> select * from t3;
+----+
| c1 |
+----+
| 2 |
+----+
1 row in set (0.00 sec) [root@master archive]# mysqlbinlog -vv mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190314 16:57:13 server id 3306102 end_log_pos 123 CRC32 0x2035b77c Start: binlog v 4, server v 5.7.25-log created 190314 16:57:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
'/*!*/;
# at 123
#190314 16:57:13 server id 3306102 end_log_pos 154 CRC32 0x31517bb7 Previous-GTIDs
# [empty]
# at 154
#190314 16:59:05 server id 3306102 end_log_pos 219 CRC32 0x8aa31af9 GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '27c3e750-2376-11e9-aa51-0242ac110002:1'/*!*/;
# at 219
#190314 16:58:51 server id 3306102 end_log_pos 310 CRC32 0x7f0987fa Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1552553931/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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 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/*!*/;
XA START X'6d7932',X'',1
/*!*/;
# at 310
#190314 16:58:51 server id 3306102 end_log_pos 358 CRC32 0xe885b2a1 Rows_query
# insert into t3 values(2)
# at 358
#190314 16:58:51 server id 3306102 end_log_pos 403 CRC32 0x2a2039e0 Table_map: `test`.`t3` mapped to number 109
# at 403
#190314 16:58:51 server id 3306102 end_log_pos 443 CRC32 0x0fcb989e Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t3`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
# at 443
#190314 16:59:05 server id 3306102 end_log_pos 532 CRC32 0xe69eb8b2 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1552553945/*!*/;
XA END X'6d7932',X'',1
/*!*/;
# at 532
#190314 16:59:05 server id 3306102 end_log_pos 571 CRC32 0x47bd8151 XA PREPARE X'6d7932',X'',1
XA PREPARE X'6d7932',X'',1
/*!*/;
# at 571
#190314 17:00:19 server id 3306102 end_log_pos 636 CRC32 0x2d59a180 GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= '27c3e750-2376-11e9-aa51-0242ac110002:2'/*!*/;
# at 636
#190314 17:00:19 server id 3306102 end_log_pos 728 CRC32 0x3af87320 Query thread_id=8 exec_time=1 error_code=0
SET TIMESTAMP=1552554019/*!*/;
XA COMMIT X'6d7932',X'',1
/*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/; 小结
- 通过上面的操作，发现在执行xa prepare后，xa prepare之前的操作都被记录到binlog，记录binlog的格式与其他普通事务不一致
- 在会话断开/崩溃后，通过xa recover可以查看到未提交的事务，可以通过操作xa commit使事务提交
- 如果MySQL有主从关系，主库在执行xa prepare后会话断开/崩溃；从库会回放xa prepare之前的所有操作。如果主库在恢复后没有对该xa事务commit；那么从库会在回放该xa事务后，会模拟主库断开会话，目的是解决避免该未提交xa事务堵塞后面的事务
操作步骤（MGR)
root@localhost : (none) 05:53:36> xa start 'my57'; Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 05:53:41> use test
Database changed
root@localhost : test 05:53:49> insert into test1 values(2); Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:53:51> xa end 'my57'; Query OK, 0 rows affected (0.00 sec)
root@localhost : test 05:53:54> xa prepare 'my57';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 05:54:00> exit SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:145'/*!*/;
# at 1073
#190314 17:53:51 server id 3306101 end_log_pos 1167 Query thread_id=203 exec_time=0 error_code=0
SET TIMESTAMP=1552557231/*!*/;
XA START X'6d793537',X'',1
/*!*/;
# at 1167
#190314 17:53:51 server id 3306101 end_log_pos 1214 Rows_query
# insert into test1 values(2)
# at 1214
#190314 17:53:51 server id 3306101 end_log_pos 1258 Table_map: `test`.`test1` mapped to number 119
# at 1258
#190314 17:53:51 server id 3306101 end_log_pos 1294 Write_rows: table id 119 flags: STMT_END_F
### INSERT INTO `test`.`test1`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
# at 1294
#190314 17:54:00 server id 3306101 end_log_pos 1386 Query thread_id=203 exec_time=0 error_code=0
SET TIMESTAMP=1552557240/*!*/;
XA END X'6d793537',X'',1
/*!*/;
# at 1386
#190314 17:54:00 server id 3306101 end_log_pos 1422 XA PREPARE X'6d793537',X'',1
XA PREPARE X'6d793537',X'',1
/*!*/;
# at 1422
#190314 17:55:14 server id 3306101 end_log_pos 1483 GTID last_committed=5 sequence_number=6 rbr_only=no 重新登录MySQL
[root@MGR1 ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 204
Server version: 5.7.25-log MySQL Community Server (GPL)
...
root@localhost : (none) 05:54:53> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 4 | 0 | my57 |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
root@localhost : (none) 05:55:01> xa commit 'my57';
Query OK, 0 rows affected (0.01 sec)
root@localhost : test 05:58:21> select * from test1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec) SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:146'/*!*/;
# at 1483
#190314 17:55:14 server id 3306101 end_log_pos 1574 Query thread_id=204 exec_time=0 error_code=0
SET TIMESTAMP=1552557314/*!*/;
XA COMMIT X'6d793537',X'',1
/*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/; - 结论与单机版MySQL5.7一致
- 通过测试证明MGR不支持集群内节点之间XA
![数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)