百尺竿头更进一步 – 拓展 Aurora的读写能力之Gaea篇

; 配置类型，目前支持file/etcd两种方式，file方式不支持热加载，但是可以快速体验功能
; file 模式下读取file_config_path下的namespace配置文件
; etcd 模式下读取coordinator_addr/cluster_name下的namespace配置文件
config_type=file
;file config path, 具体配置放到file_config_path的namespace目录下，该下级目录为固定目录
file_config_path=/data/gaea/Gaea/etc/file
;配置中心地址，目前只支持etcd
;coordinator_addr=http://127.0.0.1:2379
;配置中心用户名和密码
;username=test
;password=test
;环境划分、test、online
environ=test
;service name
service_name=gaea_proxy
;gaea_proxy 当前proxy所属的集群名称
cluster_name=gaea_default_cluster
;日志配置
log_path=/data/gaea/Gaea/log
log_level=Notice
log_filename=gaea
log_output=file
;管理地址
admin_addr=0.0.0.0:13399
;basic auth
admin_user=admin
admin_password=admin
;代理服务监听地址
proto_type=tcp4
proxy_addr=0.0.0.0:13306
; 默认编码
proxy_charset=utf8
;慢sql阈值，单位: 毫秒
slow_sql_time=100
;空闲会话超时时间,单位: 秒
session_timeout=3600
;打点统计配置
stats_enabled=true
stats_interval=10
;encrypt key, 用于对etcd中存储的namespace配置加解密
encrypt_key=1234abcd5678efg*
;server_version 服务器版本号配置
server_version=5.7-gaea
;auth plugin mysql_native_password or caching_sha2_password or ''
;自定义认证插件，支持 5.x 和 8.x 版本认证，认证插件为 caching_sha2_password 时，不支持低版本客户端认证
auth_plugin=mysql_native_password

{
    "name": "gaea_namespace_1",
    "online": true,
    "read_only": true,
    "allowed_dbs": {
        "db_ks": true,
        "db_mycat": true
    },
    "default_phy_dbs": {
        "db_ks": "db_ks",
        "db_mycat": "db_mycat"
    },
    "slow_sql_time": "1000",
    "black_sql": [
        ""
    ],
    "allowed_ip": null,
    "slices": [
        {
            "name": "slice-1",
            "user_name": "root",
            "password": "Pass1234",
            "master": "shard-1.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com:3306",
            "slaves": ["shard-1.cluster-ro-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com:3306"],
            "capacity": 32,
            "max_capacity": 1024,
            "idle_timeout": 3600
        },
        {
            "name": "slice-2",
            "user_name": "root",
            "password": "Pass1234",
            "master": "shard-2.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com:3306",
            "slaves": ["shard-2.cluster-ro-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com:3306"],
            "capacity": 32,
            "max_capacity": 1024,
            "idle_timeout": 3600
        },
        {
            "name": "slice-3",
            "user_name": "root",
            "password": "Pass1234",
            "master": "shard-3.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com:3306",
            "slaves": ["shard-3.cluster-ro-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com:3306"],
            "capacity": 32,
            "max_capacity": 1024,
            "idle_timeout": 3600
        }
    ],
    "shard_rules": [
        {
            "db": "db_ks",
            "table": "tbl_ks",
            "type": "hash",
            "key": "id",
            "locations": [
                1,
                1,
                1
            ],
            "slices": [
                "slice-1",
                "slice-2",
                "slice-3"
            ]
        },
        {
           "db": "db_ks",
           "table": "tbl_ks2",
           "type": "linked",
           "parent_table": "tbl_ks",
           "key": "id"
        },
        {
            "db": "db_ks",
            "table": "tbl_global",
            "type": "global",
            "locations": [
                1,
                1,
                1
            ],
    "slices": [
        "slice-1",
        "slice-2",
        "slice-3"
              ]
    
        }
        
    ],
    "global_sequences": [
        
    ],
    "users": [
        {
            "user_name": "gaea",
            "password": "gaea_password",
            "namespace": "gaea_namespace_1",
            "rw_flag": 2,
            "rw_split": 1
        }
    ],
    "default_slice": "slice-1",
    "open_general_log": false,
    "max_sql_execute_time": 5000,
    "max_sql_result_size": 100000
}

[root@ip-172-31-29-68 etc]# ../bin/gaea -config ./test.ini
Build Version Information:Version: fbac80acdd922a3d563bc703994f7f9145c2d41b
GitRevision: fbac80acdd922a3d563bc703994f7f9145c2d41b
User: root@ip-172-31-29-68.ap-southeast-1.compute.internal
GolangVersion: go1.17.6
BuildStatus: Clean
BuildTime: 2022-02-09--07:13:01
BuildBranch: master
BuildGitDirty: 0
[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
using env: export GIN_MODE=release
using code: gin.SetMode(gin.ReleaseMode)
[GIN-debug] GET /api/proxy/ping --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).ping-fm (2 handlers)
[GIN-debug] PUT /api/proxy/config/prepare/:name --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).prepareConfig-fm (2 handlers)
[GIN-debug] PUT /api/proxy/config/commit/:name --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).commitConfig-fm (2 handlers)
[GIN-debug] PUT /api/proxy/namespace/delete/:name --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).deleteNamespace-fm (2 handlers)
[GIN-debug] GET /api/proxy/config/fingerprint --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).configFingerprint-fm (2 handlers)
[GIN-debug] GET /api/proxy/stats/sessionsqlfingerprint/:namespace --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).getNamespaceSessionSQLFingerprint-fm (2 handlers)
[GIN-debug] GET /api/proxy/stats/backendsqlfingerprint/:namespace --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).getNamespaceBackendSQLFingerprint-fm (2 handlers)
[GIN-debug] DELETE /api/proxy/stats/sessionsqlfingerprint/:namespace --> github.com/XiaoMi/Gaea/proxy/server.(AdminServer).clearNamespaceSessionSQLFingerprint-fm (2 handlers)
[GIN-debug] DELETE /api/proxy/stats/backendsqlfingerprint/:namespace --> github.com/XiaoMi/Gaea/proxy/server.(*AdminServer).clearNamespaceBackendSQLFingerprint-fm (2 handlers)
[GIN-debug] GET /api/metric/metrics --> github.com/gin-gonic/gin.WrapH.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/ --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/cmdline --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/profile --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] POST /debug/pprof/symbol --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/symbol --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/trace --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/block --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/goroutine --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/heap --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/mutex --> github.com/gin-gonic/gin.WrapF.func1 (2 handlers)
[GIN-debug] GET /debug/pprof/threadcreate --> github.com/gin-gonic/gin.WrapF.func1

[root@ip-172-31-29-68 namespace]# mysql -ugaea -pgaea_password -P13306 -h172.31.29.68
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 10001
Server version: 5.6.20-gaea MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+----------+
| Database |
+----------+
| db_ks |
| db_mycat |
+----------+
2 rows in set (0.00 sec)

CREATE TABLE IF NOT EXISTS tbl_ks_0000(
id INT UNSIGNED AUTO_INCREMENT,
col1 VARCHAR(100) NOT NULL,
col2 VARCHAR(40) NOT NULL,
col_date DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL [db_ks]> select * from tbl_ks;
+----+-------+-------+------------+
| id | col1 | col2 | col_date |
+----+-------+-------+------------+
| 3 | name3 | addr3 | 2022-02-11 |
| 1 | name1 | addr1 | 2022-02-11 |
| 4 | name4 | addr4 | 2022-02-11 |
| 2 | name2 | addr2 | 2022-02-11 |
+----+-------+-------+------------+
4 rows in set (0.03 sec)

[root@ip-172-31-29-68 ec2-user]# mysql -uroot -pPass1234 -hshard-1.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com -P3306 -e "select * from db_ks.tbl_ks_0000"
+----+-------+-------+------------+
| id | col1 | col2 | col_date |
+----+-------+-------+------------+
| 3 | name3 | addr3 | 2022-02-11 |
+----+-------+-------+------------+
[root@ip-172-31-29-68 ec2-user]# mysql -uroot -pPass1234 -hshard-2.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com -P3306 -e "select * from db_ks.tbl_ks_0001"
+----+-------+-------+------------+
| id | col1 | col2 | col_date |
+----+-------+-------+------------+
| 1 | name1 | addr1 | 2022-02-11 |
| 4 | name4 | addr4 | 2022-02-11 |
+----+-------+-------+------------+
[root@ip-172-31-29-68 ec2-user]# mysql -uroot -pPass1234 -hshard-3.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com -P3306 -e "select * from db_ks.tbl_ks_0002"
+----+-------+-------+------------+
| id | col1 | col2 | col_date |
+----+-------+-------+------------+
| 2 | name2 | addr2 | 2022-02-11 |

 {
            "db": "db_ks",
            "table": "tbl_global",
            "type": "global",
            "locations": [
                1,
                1,
                1
            ],
    "slices": [
        "slice-1",
        "slice-2",
        "slice-3"
    ]

CREATE TABLE IF NOT EXISTS tbl_global(
   id INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY ( id )
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL [db_ks]> insert into tbl_global (id) values (1);
Query OK, 3 rows affected (0.02 sec)
MySQL [db_ks]> insert into tbl_global (id) values (2);
Query OK, 3 rows affected (0.02 sec)
MySQL [db_ks]> insert into tbl_global (id) values (3);
Query OK, 3 rows affected (0.02 sec)

[ec2-user@ip-172-31-29-68 ~]$ mysql -uroot -pPass1234 -hshard-1.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com -e "select * from db_ks.tbl_global"
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

MySQL [db_ks]> select * from tbl_global,tbl_ks where tbl_ks.id=tbl_global.id;
+----+----+-------+-------+------------+
| id | id | col1  | col2  | col_date   |
+----+----+-------+-------+------------+
|  2 |  2 | name2 | addr2 | 2022-02-11 |
|  3 |  3 | name3 | addr3 | 2022-02-11 |
|  1 |  1 | name1 | addr1 | 2022-02-11 |
+----+----+-------+-------+------------+
3 rows in set (0.05 sec)

MySQL [db_ks]> explain select * from tbl_global,tbl_ks where tbl_ks.id=tbl_global.id;
+-------+---------+-------+--------------------------------------------------------------------------------------------+
| type  | slice   | db    | sql                                                                                        |
+-------+---------+-------+--------------------------------------------------------------------------------------------+
| shard | slice-1 | db_ks | SELECT * FROM (`tbl_global`) JOIN `tbl_ks_0000` WHERE `tbl_ks_0000`.`id`=`tbl_global`.`id` |
| shard | slice-2 | db_ks | SELECT * FROM (`tbl_global`) JOIN `tbl_ks_0001` WHERE `tbl_ks_0001`.`id`=`tbl_global`.`id` |
| shard | slice-3 | db_ks | SELECT * FROM (`tbl_global`) JOIN `tbl_ks_0002` WHERE `tbl_ks_0002`.`id`=`tbl_global`.`id` |
+-------+---------+-------+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

# 在分片1 手动插入id=4 制造不一致
mysql -uroot -pPass1234 -hshard-1.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com -e "insert into db_ks.tbl_global values (4)"
# 在proxy insert id=4 会报错
MySQL [db_ks]>  insert into tbl_global (id) values (4);
ERROR 1105 (HY000): unknown error: execute in InsertPlan error: ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
# 在分片2 和3 查看全局表能发现id=4 这条数据
[ec2-user@ip-172-31-29-68 ~]$ mysql -uroot -pPass1234 -hshard-2.cluster-c3luzotrlr7v.ap-southeast-1.rds.amazonaws.com -e "select * from db_ks.tbl_global"
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

# 连接proxy手动插入3条数据到
MySQL [db_ks]> insert into tbl_ks2 (id) values (1);
Query OK, 1 row affected (0.01 sec)
MySQL [db_ks]>
MySQL [db_ks]> insert into tbl_ks2 (id) values (2);
Query OK, 1 row affected (0.01 sec)
MySQL [db_ks]> insert into tbl_ks2 (id) values (3);
Query OK, 1 row affected (0.01 sec)

MySQL [db_ks]> explain select * from tbl_ks2;
+-------+---------+-------+------------------------------+
| type  | slice   | db    | sql                          |
+-------+---------+-------+------------------------------+
| shard | slice-1 | db_ks | SELECT * FROM `tbl_ks2_0000` |
| shard | slice-2 | db_ks | SELECT * FROM `tbl_ks2_0001` |
| shard | slice-3 | db_ks | SELECT * FROM `tbl_ks2_0002` |
+-------+---------+-------+------------------------------+
3 rows in set (0.00 sec)

# 先看看父表的数据
MySQL [db_ks]> select * from tbl_ks;
+----+----------+-------+------------+
| id | col1     | col2  | col_date   |
+----+----------+-------+------------+
|  2 | name2    | addr2 | 2022-02-11 |
|  3 | name3    | addr3 | 2022-02-11 |
|  1 | failover | addr1 | 2022-02-11 |
|  4 | name4    | addr4 | 2022-02-11 |
+----+----------+-------+------------+
4 rows in set (0.04 sec)

MySQL [db_ks]> select * from tbl_ks,tbl_ks2 where tbl_ks.id=tbl_ks2.id;
+----+----------+-------+------------+----+
| id | col1     | col2  | col_date   | id |
+----+----------+-------+------------+----+
|  3 | name3    | addr3 | 2022-02-11 |  3 |
|  1 | failover | addr1 | 2022-02-11 |  1 |
|  2 | name2    | addr2 | 2022-02-11 |  2 |
+----+----------+-------+------------+----+
MySQL [db_ks]> explain select * from tbl_ks,tbl_ks2 where tbl_ks.id=tbl_ks2.id;
+-------+---------+-------+------------------------------------------------------------------------------------------------+
| type  | slice   | db    | sql                                                                                            |
+-------+---------+-------+------------------------------------------------------------------------------------------------+
| shard | slice-2 | db_ks | SELECT * FROM (`tbl_ks_0001`) JOIN `tbl_ks2_0001` WHERE `tbl_ks_0001`.`id`=`tbl_ks2_0001`.`id` |
| shard | slice-3 | db_ks | SELECT * FROM (`tbl_ks_0002`) JOIN `tbl_ks2_0002` WHERE `tbl_ks_0002`.`id`=`tbl_ks2_0002`.`id` |
| shard | slice-1 | db_ks | SELECT * FROM (`tbl_ks_0000`) JOIN `tbl_ks2_0000` WHERE `tbl_ks_0000`.`id`=`tbl_ks2_0000`.`id` |
+-------+---------+-------+------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#!/bin/bash
while true;do
mysql -ugaea -pgaea_password -P13306 -h172.31.29.68 -e "use db_ks;select  * from tbl_ks;"
sleep 0.1
done

[root@ip-172-31-29-68 ec2-user]# cat test_failover.sh
#!/bin/bash
while true
do
     mysql -ugaea -pgaea_password -P13306 -h172.31.29.68 -e "use db_ks; update  tbl_ks set col1='failover' where id = 1;"
    now=$(date +"%T")
    echo "update done: $now"
    sleep 1
done 

ERROR 1105 (HY000) at line 1: unknown error: execute in UpdatePlan error: ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 11:52:09
ERROR 1105 (HY000) at line 1: unknown error: execute in UpdatePlan error: ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement