MySQL中间件ProxySQL的事务测试
--2022-06-29 春雷
1、前言
为了验证ProxySQL的几个autocommit参数对：set autocommit=0 的方式开事务的影响，做如下测试。
2、基本信息
分类 |
具体 |
|---|---|
ProxySQL |
2.3.2版本 2.3.2-10-g8cd66cf 端口：5555 |
MySQL |
5.7.22版本 |
MySQL集群 |
端口：5555 10.10.10.10 主实例 10.10.10.20 从实例 10.10.10.30 从实例 10.10.10.40 从实例 10.10.10.50 从实例 |
3、测试
3.1、确认参数
【配置参数：global_variables】：
select * from runtime_global_variables where variable_name='mysql-forward_autocommit' limit 5;select * from runtime_global_variables where variable_name='mysql-enforce_autocommit_on_reads' limit 5;select * from runtime_global_variables where variable_name='mysql-autocommit_false_not_reusable' limit 5;select * from runtime_global_variables where variable_name='mysql-autocommit_false_is_transaction' limit 5;
【账号配置表：mysql_users】：
select * from runtime_mysql_users;
【规则配置表：mysql_query_rules】
select * from runtime_mysql_query_rules;
3.2、访问测试
3.2.1、事务测试1
SQL |
连接具体 |
|---|---|
select * from test limit 1; |
连接从实例 |
begin; |
连接主实例 |
select * from test limit 2; |
连接主实例 |
select * from test limit 3; |
连接主实例 |
commit; |
连接主实例 |
select * from test limit 4; |
连接从实例 |
select * from test limit 5; |
连接从实例 |
【测试详情】：
mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.01 sec)
mysql> begin;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> select * from test limit 3;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.01 sec)
mysql> commit;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 4;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
mysql> select * from test limit 5;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":,"endtime":"2022-06-29 12:30:40.851065","endtime_timestamp_us":1656477040851065,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:30:40.851065","starttime_timestamp_us":1656477040851065,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":238,"endtime":"2022-06-29 12:30:45.697812","endtime_timestamp_us":1656477045697812,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:30:45.697574","starttime_timestamp_us":1656477045697574,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xA592C94A099E89DC","duration_us":1354,"endtime":"2022-06-29 12:30:48.062811","endtime_timestamp_us":1656477048062811,"event":"COM_QUERY","hostgroup_id":1,"query":"begin","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:48.061457","starttime_timestamp_us":1656477048061457,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1531,"endtime":"2022-06-29 12:30:49.970893","endtime_timestamp_us":1656477049970893,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:49.969362","starttime_timestamp_us":1656477049969362,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":14844,"endtime":"2022-06-29 12:30:54.036205","endtime_timestamp_us":1656477054036205,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:54.021361","starttime_timestamp_us":1656477054021361,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1382,"endtime":"2022-06-29 12:30:56.994996","endtime_timestamp_us":1656477056994996,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:30:56.993614","starttime_timestamp_us":1656477056993614,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":234,"endtime":"2022-06-29 12:30:59.321199","endtime_timestamp_us":1656477059321199,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:30:59.320965","starttime_timestamp_us":1656477059320965,"thread_id":1097299,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":351,"endtime":"2022-06-29 12:31:00.976829","endtime_timestamp_us":1656477060976829,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:31:00.976478","starttime_timestamp_us":1656477060976478,"thread_id":1097299,"username":"test"}
3.2.2、事务测试2
SQL |
连接具体 |
|---|---|
select * from test limit 1; |
连接从实例 |
start transaction; |
连接主实例 |
select * from test limit 2; |
连接主实例 |
select * from test limit 3; |
连接主实例 |
commit; |
连接主实例 |
select * from test limit 4; |
连接从实例 |
select * from test limit 5; |
连接从实例 |
【测试详情】：
mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.00 sec)
mysql> start transaction;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> select * from test limit 3;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.01 sec)
mysql> commit;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 4;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
mysql> select * from test limit 5;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":,"endtime":"2022-06-29 12:37:15.508211","endtime_timestamp_us":1656477435508211,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:37:15.508211","starttime_timestamp_us":1656477435508211,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":215,"endtime":"2022-06-29 12:37:18.111819","endtime_timestamp_us":1656477438111819,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:37:18.111604","starttime_timestamp_us":1656477438111604,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x326F4F2B935EC266","duration_us":1409,"endtime":"2022-06-29 12:37:27.160270","endtime_timestamp_us":1656477447160270,"event":"COM_QUERY","hostgroup_id":1,"query":"start transaction","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:27.158861","starttime_timestamp_us":1656477447158861,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1569,"endtime":"2022-06-29 12:37:29.936428","endtime_timestamp_us":1656477449936428,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:29.934859","starttime_timestamp_us":1656477449934859,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1546,"endtime":"2022-06-29 12:37:31.208729","endtime_timestamp_us":1656477451208729,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:31.207183","starttime_timestamp_us":1656477451207183,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1404,"endtime":"2022-06-29 12:37:35.154422","endtime_timestamp_us":1656477455154422,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:37:35.153018","starttime_timestamp_us":1656477455153018,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":255,"endtime":"2022-06-29 12:37:37.824428","endtime_timestamp_us":1656477457824428,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:37:37.824173","starttime_timestamp_us":1656477457824173,"thread_id":1097452,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":231,"endtime":"2022-06-29 12:37:40.021663","endtime_timestamp_us":1656477460021663,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:37:40.021432","starttime_timestamp_us":1656477460021432,"thread_id":1097452,"username":"test"}
3.2.3、事务测试3
SQL |
连接具体 |
|---|---|
select * from test limit 1; |
连接从实例 |
SET AUTOCOMMIT=0 |
中间件直接返回，不转发 |
select * from test limit 2; |
连接从实例 |
commit; |
中间件直接返回，不转发 |
select * from test limit 3; |
连接从实例 |
【测试详情】：
mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT=0 -> ;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> commit;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 3;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.01 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":263,"endtime":"2022-06-29 12:01:44.094820","endtime_timestamp_us":1656475304094820,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:01:44.094557","starttime_timestamp_us":1656475304094557,"thread_id":1096635,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":,"endtime":"2022-06-29 12:02:12.237275","endtime_timestamp_us":1656475332237275,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:02:12.237275","starttime_timestamp_us":1656475332237275,"thread_id":1096635,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":282,"endtime":"2022-06-29 12:02:22.701424","endtime_timestamp_us":1656475342701424,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:02:22.701142","starttime_timestamp_us":1656475342701142,"thread_id":1096635,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":,"endtime":"2022-06-29 12:02:33.428068","endtime_timestamp_us":1656475353428068,"event":"COM_QUERY","hostgroup_id":-1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:02:33.428068","starttime_timestamp_us":1656475353428068,"thread_id":1096635,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":312,"endtime":"2022-06-29 12:02:47.178428","endtime_timestamp_us":1656475367178428,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 3","rows_affected":,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:02:47.178116","starttime_timestamp_us":1656475367178116,"thread_id":1096635,"username":"test"}
3.2.4、事务测试4
窗口1 SQL |
连接具体 |
窗口2 SQL |
|---|---|---|
select * from test limit 1; |
连接从实例 |
|
SET AUTOCOMMIT=0 |
中间件直接返回，不转发 |
|
select * from test limit 2; |
连接从实例 |
|
insert into test values (10,'ee'); |
连接主实例 |
|
|
select * from test; 看不到插入的数据 |
||
select * from test limit 10; |
连接主实例 |
|
commit; |
连接主实例 |
|
|
select * from test; 可以看到插入的数据 |
||
select * from test limit 11; |
连接主实例 |
|
【测试详情】：
窗口1：mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT=0;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> insert into test values (10,'ee');Query OK, 1 row affected (0.01 sec)
mysql> select * from test limit 10;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.01 sec)
mysql> commit;Query OK, rows affected (0.00 sec)mysql> select * from test limit 11;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
窗口2：连接MySQL主实例查询(test:15038)@[(none)]>use db_testDatabase changed(test:15038)@[db_test]>select * from test;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.01 sec)
窗口1提交后，再次查询可以看到
(test:15038)@[db_test]>select * from test;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":249,"endtime":"2022-06-29 12:03:35.890621","endtime_timestamp_us":1656475415890621,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:03:35.890372","starttime_timestamp_us":1656475415890372,"thread_id":1096678,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":,"endtime":"2022-06-29 12:03:47.043537","endtime_timestamp_us":1656475427043537,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:03:47.043537","starttime_timestamp_us":1656475427043537,"thread_id":1096678,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":308,"endtime":"2022-06-29 12:03:52.331911","endtime_timestamp_us":1656475432331911,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:03:52.331603","starttime_timestamp_us":1656475432331603,"thread_id":1096678,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x6D19DF2B6E1C4673","duration_us":2949,"endtime":"2022-06-29 12:04:10.740577","endtime_timestamp_us":1656475450740577,"event":"COM_QUERY","hostgroup_id":1,"query":"insert into test values (10,'ee')","rows_affected":1,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:04:10.737628","starttime_timestamp_us":1656475450737628,"thread_id":1096678,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1573,"endtime":"2022-06-29 12:04:52.526755","endtime_timestamp_us":1656475492526755,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 10","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:04:52.525182","starttime_timestamp_us":1656475492525182,"thread_id":1096678,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1535,"endtime":"2022-06-29 12:04:59.325673","endtime_timestamp_us":1656475499325673,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:04:59.324138","starttime_timestamp_us":1656475499324138,"thread_id":1096678,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1553,"endtime":"2022-06-29 12:23:29.899417","endtime_timestamp_us":1656476609899417,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 11","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:23:29.897864","starttime_timestamp_us":1656476609897864,"thread_id":1096678,"username":"test"}
4、测试2
4.1、更改配置
update global_variables set variable_value='false' where variable_name='mysql-enforce_autocommit_on_reads';update global_variables set variable_value='false' where variable_name='mysql-autocommit_false_not_reusable';update global_variables set variable_value='false' where variable_name='mysql-autocommit_false_is_transaction';load mysql variables to runtime;save mysql variables to disk;确认参数：
4.2、测试
4.2.1、事务测试1
SQL |
连接具体 |
|---|---|
select * from test limit 1; |
连接从实例 |
begin; |
连接主实例 |
select * from test limit 2; |
连接主实例 |
select * from test limit 3; |
连接主实例 |
commit; |
连接主实例 |
select * from test limit 4; |
连接从实例 |
select * from test limit 5; |
连接从实例 |
【测试详情】：
mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.00 sec)
mysql> begin;Query OK, rows affected (0.01 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> select * from test limit 3;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.01 sec)
mysql> commit;Query OK, rows affected (0.01 sec)
mysql> select * from test limit 4;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
mysql> select * from test limit 5;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":,"endtime":"2022-06-29 12:54:17.157391","endtime_timestamp_us":1656478457157391,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:54:17.157391","starttime_timestamp_us":1656478457157391,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":218,"endtime":"2022-06-29 12:54:19.628804","endtime_timestamp_us":1656478459628804,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:54:19.628586","starttime_timestamp_us":1656478459628586,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xA592C94A099E89DC","duration_us":1364,"endtime":"2022-06-29 12:54:26.730899","endtime_timestamp_us":1656478466730899,"event":"COM_QUERY","hostgroup_id":1,"query":"begin","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:26.729535","starttime_timestamp_us":1656478466729535,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1602,"endtime":"2022-06-29 12:54:32.606209","endtime_timestamp_us":1656478472606209,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:32.604607","starttime_timestamp_us":1656478472604607,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1535,"endtime":"2022-06-29 12:54:33.842144","endtime_timestamp_us":1656478473842144,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:33.840609","starttime_timestamp_us":1656478473840609,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1441,"endtime":"2022-06-29 12:54:36.311918","endtime_timestamp_us":1656478476311918,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:54:36.310477","starttime_timestamp_us":1656478476310477,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":284,"endtime":"2022-06-29 12:54:37.782841","endtime_timestamp_us":1656478477782841,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:54:37.782557","starttime_timestamp_us":1656478477782557,"thread_id":1097841,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":243,"endtime":"2022-06-29 12:54:39.138714","endtime_timestamp_us":1656478479138714,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:54:39.138471","starttime_timestamp_us":1656478479138471,"thread_id":1097841,"username":"test"}
4.2.2、事务测试2
SQL |
连接具体 |
|---|---|
select * from test limit 1; |
连接从实例 |
start transaction; |
连接主实例 |
select * from test limit 2; |
连接主实例 |
select * from test limit 3; |
连接主实例 |
commit; |
连接主实例 |
select * from test limit 4; |
连接从实例 |
select * from test limit 5; |
连接从实例 |
【测试详情】：
mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.01 sec)
mysql> start transaction;Query OK, rows affected (0.01 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> select * from test limit 3;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.00 sec)
mysql> commit;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 4;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
mysql> select * from test limit 5;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee || 20 | ff |+----+------+5 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":,"endtime":"2022-06-29 13:06:05.368931","endtime_timestamp_us":1656479165368931,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 13:06:05.368931","starttime_timestamp_us":1656479165368931,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":271,"endtime":"2022-06-29 13:06:23.665297","endtime_timestamp_us":1656479183665297,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 13:06:23.665026","starttime_timestamp_us":1656479183665026,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x326F4F2B935EC266","duration_us":1425,"endtime":"2022-06-29 13:06:29.046246","endtime_timestamp_us":1656479189046246,"event":"COM_QUERY","hostgroup_id":1,"query":"start transaction","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:29.044821","starttime_timestamp_us":1656479189044821,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1577,"endtime":"2022-06-29 13:06:34.582828","endtime_timestamp_us":1656479194582828,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:34.581251","starttime_timestamp_us":1656479194581251,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1488,"endtime":"2022-06-29 13:06:35.597908","endtime_timestamp_us":1656479195597908,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 3","rows_affected":,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:35.596420","starttime_timestamp_us":1656479195596420,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1419,"endtime":"2022-06-29 13:06:39.290401","endtime_timestamp_us":1656479199290401,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 13:06:39.288982","starttime_timestamp_us":1656479199288982,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":250,"endtime":"2022-06-29 13:06:41.400123","endtime_timestamp_us":1656479201400123,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 4","rows_affected":,"rows_sent":4,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 13:06:41.399873","starttime_timestamp_us":1656479201399873,"thread_id":1098117,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":262,"endtime":"2022-06-29 13:06:42.667775","endtime_timestamp_us":1656479202667775,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 5","rows_affected":,"rows_sent":5,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 13:06:42.667513","starttime_timestamp_us":1656479202667513,"thread_id":1098117,"username":"test"}
4.2.3、事务测试3
SQL |
连接具体 |
|---|---|
select * from test limit 1; |
连接从实例 |
SET AUTOCOMMIT=0 |
中间件直接返回，不转发 |
select * from test limit 2; |
连接从实例 |
commit; |
中间件直接返回，不转发 |
select * from test limit 3; |
连接从实例 |
【测试详情】：
mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.01 sec)
mysql> SET AUTOCOMMIT=0;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> commit;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 3;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc |+----+------+3 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":231,"endtime":"2022-06-29 12:55:56.231303","endtime_timestamp_us":1656478556231303,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:55:56.231072","starttime_timestamp_us":1656478556231072,"thread_id":1097872,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":,"endtime":"2022-06-29 12:56:09.079890","endtime_timestamp_us":1656478569079890,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:56:09.079890","starttime_timestamp_us":1656478569079890,"thread_id":1097872,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":321,"endtime":"2022-06-29 12:56:11.078580","endtime_timestamp_us":1656478571078580,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:56:11.078259","starttime_timestamp_us":1656478571078259,"thread_id":1097872,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":,"endtime":"2022-06-29 12:56:22.560071","endtime_timestamp_us":1656478582560071,"event":"COM_QUERY","hostgroup_id":-1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:56:22.560071","starttime_timestamp_us":1656478582560071,"thread_id":1097872,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":316,"endtime":"2022-06-29 12:56:25.574068","endtime_timestamp_us":1656478585574068,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 3","rows_affected":,"rows_sent":3,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:56:25.573752","starttime_timestamp_us":1656478585573752,"thread_id":1097872,"username":"test"}
4.2.4、事务测试4
窗口1 SQL |
连接具体 |
窗口2 SQL |
|---|---|---|
select * from test limit 1; |
连接从实例 |
|
SET AUTOCOMMIT=0 |
中间件直接返回，不转发 |
|
select * from test limit 2; |
连接从实例 |
|
insert into test values (11,'ff'); |
连接主实例 |
|
|
select * from test; 看不到插入的数据 |
||
select * from test limit 10; |
连接主实例 |
|
commit; |
连接主实例 |
|
|
select * from test; 可以看到插入的数据 |
||
select * from test limit 11; |
连接主实例 |
|
【测试详情】：
窗口1：mysql -utest -ptest123 -h中间件IP -P5555 db_testmysql> select * from test limit 1;+----+------+| id | name |+----+------+| 1 | aa |+----+------+1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT=0;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 2;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb |+----+------+2 rows in set (0.00 sec)
mysql> insert into test values (20,'ff');Query OK, 1 row affected (0.01 sec)
mysql> select * from test limit 10;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee || 20 | ff |+----+------+5 rows in set (0.01 sec)
mysql> commit;Query OK, rows affected (0.00 sec)
mysql> select * from test limit 11;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee || 20 | ff |+----+------+5 rows in set (0.00 sec)
窗口2：连接MySQL主实例查询
(test:15038)@[db_test]>select * from test ;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee |+----+------+4 rows in set (0.00 sec)
(test:15038)@[db_test]>select * from test ;+----+------+| id | name |+----+------+| 1 | aa || 2 | bb || 3 | cc || 10 | ee || 20 | ff |+----+------+5 rows in set (0.00 sec)
中间件访问日志：{"client":"10.1.1.1:2222","digest":"0x226CD90D52A2BA0B","duration_us":,"endtime":"2022-06-29 12:58:00.910345","endtime_timestamp_us":1656478680910345,"event":"COM_QUERY","hostgroup_id":-1,"query":"select @@version_comment limit 1","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:58:00.910345","starttime_timestamp_us":1656478680910345,"thread_id":1097933,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":245,"endtime":"2022-06-29 12:58:05.203742","endtime_timestamp_us":1656478685203742,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 1","rows_affected":,"rows_sent":1,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:58:05.203497","starttime_timestamp_us":1656478685203497,"thread_id":1097933,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x6447D76C73F798EB","duration_us":,"endtime":"2022-06-29 12:58:07.786478","endtime_timestamp_us":1656478687786478,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET AUTOCOMMIT=0","rows_affected":,"rows_sent":,"schemaname":"db_test","starttime":"2022-06-29 12:58:07.786478","starttime_timestamp_us":1656478687786478,"thread_id":1097933,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":304,"endtime":"2022-06-29 12:58:10.838779","endtime_timestamp_us":1656478690838779,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 2","rows_affected":,"rows_sent":2,"schemaname":"db_test","server":"10.10.10.50:5555","starttime":"2022-06-29 12:58:10.838475","starttime_timestamp_us":1656478690838475,"thread_id":1097933,"username":"test"}{"client":"10.1.1.1:2222","digest":"0x6D19DF2B6E1C4673","duration_us":2958,"endtime":"2022-06-29 12:58:24.516120","endtime_timestamp_us":1656478704516120,"event":"COM_QUERY","hostgroup_id":1,"query":"insert into test values (20,'ff')","rows_affected":1,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:58:24.513162","starttime_timestamp_us":1656478704513162,"thread_id":1097933,"username":test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":1579,"endtime":"2022-06-29 12:58:37.976418","endtime_timestamp_us":1656478717976418,"event":"COM_QUERY","hostgroup_id":1,"query":"select * from test limit 10","rows_affected":,"rows_sent":5,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:58:37.974839","starttime_timestamp_us":1656478717974839,"thread_id":1097933,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xDB3A841EF5443C35","duration_us":1522,"endtime":"2022-06-29 12:58:44.071517","endtime_timestamp_us":1656478724071517,"event":"COM_QUERY","hostgroup_id":1,"query":"commit","rows_affected":,"rows_sent":,"schemaname":"db_test","server":"10.10.10.10:5555","starttime":"2022-06-29 12:58:44.069995","starttime_timestamp_us":1656478724069995,"thread_id":1097933,"username":"test"}{"client":"10.1.1.1:2222","digest":"0xD8BDF2ED80FC3809","duration_us":295,"endtime":"2022-06-29 12:58:55.660222","endtime_timestamp_us":1656478735660222,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from test limit 11","rows_affected":,"rows_sent":5,"schemaname":"db_test","server":"10.10.10.40:5555","starttime":"2022-06-29 12:58:55.659927","starttime_timestamp_us":1656478735659927,"thread_id":1097933,"username":"test"}
5、总结
begin 或 start transaction 均可以强制发送到主实例
set auocommit=0 不可以被转发，在中间件层会直接返回
事务内的SQL会统一发到相同的实例
set auocommit=0 会保证事务，未提交的变更，其他事务看不到
set auocommit=0 方式连接，随后查询，会路由到从实例，根据此数据进行后续更新等，可能带来一定的数据准确性问题。
不管是否开启事务，insert 类等变更SQL是会转发到主实例上的。
参数：
mysql-autocommit_false_not_reusable 为true
mysql-autocommit_false_is_transaction 为true
与：
mysql-autocommit_false_not_reusable 为false
mysql-autocommit_false_is_transaction 为false
表现一样
综上：还是推荐大家使用begin 或 start transaction 方式开启事务。
代码里面实现，大致是：
1、创建连接
2、执行SQL：begin
3、执行SQL：查询、写入等
4、支持SQL：commit
5、释放连接(或者不释放也可以)
这样就能转发到主实例了
文章来源：公众号-雷雷DBA
原文链接：https://mp.weixin.qq.com/s/fb0SulmHH_fQXMnGrPFMrQ
