关于数据库:百尺竿头更进一步-–-Amazon-Aurora-的读写能力扩展之-ShardingSphereProxy-篇

1. 前言

Amazon Aurora 是亚马逊云科技自研的一项关系数据库服务，它在提供和开源数据库 MySQL、PostgreSQL 的完整兼容性同时，也可能提供和商业数据库媲美的性能和可用性。性能方面，Aurora MySQL 可能反对到与开源规范 MySQL 等同配置下五倍的吞吐量，Aurora PostgreSQL 可能反对与开源规范 PostgreSQL 等同配置下三倍的吞吐量的晋升。在扩展性的角度，Aurora 在存储与计算、横向与纵向方面都进行了性能的加强和翻新。Aurora 反对多达 128TB 的存储容量，而且反对 10GB 为单位的存储层动静膨胀。计算方面，Aurora 提供多个读正本的可扩展性配置反对一个区域内多达 15 个读正本的扩大，提供多主的架构来反对同一个区域内 4 个写节点的扩大，提供 Serverless 无服务器化的架构实例级别的秒级纵向扩大，提供寰球数据库来实现数据库的低提早跨区域扩大。

随着用户数据量的增长，Aurora 曾经提供了很好的扩展性，那是否能够进一步解决更多的数据量、反对更多的并发拜访呢？您能够思考利用分库分表的形式，来反对底层多个 Aurora 集群的配置。基于此，蕴含这篇博客在内的系列博客会进行相应的介绍，旨在为您进行分库分表时抉择应用代理或者 JDBC 提供参考。

本篇博客会聚焦如何应用 ShardingSphere-Proxy，一个开源的分库分表中间件工具，来进行数据库集群的构建，会涵盖分库分表、读写拆散、动静配置等方面。

2.ShardingSphere-Proxy 介绍

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（布局中）这 3 款既可能独立部署，又反对混合部署配合应用的产品组成。

作为中间件，ShardingSphere-Proxy 的定位是透明化的数据库代理端。它采纳 Apache2.0 协定，继续迭代版本，最新版本为 5.1.0，目前反对 MySQL 和 PostgreSQL 版本。它对应用程序通明，兼容 MySQL/PostgreSQL 协定的客户端。MySQL 命令行 mysql，MySQL workbench 等都能够间接拜访 ShardingSphere-Proxy。

ShardingSphere-Proxy 上层能够连贯不同的数据库，这些数据库能够是同构也能够是异构的。用户能够有两种形式指定底层数据库的分库分表或者读写拆散规定：1）依据 yaml 配置文件动态指定 2）利用 ShardingSphere 提供的增强性的 DistSQL 语言来指定。因为 DistSQL 反对动态创建规定不须要重启 Proxy 自身，它成为 ShardingSphere-Proxy 将来倒退的重点。

作为数据库代理，是否可能提供连接池加强用户并发拜访的连贯解决是须要考量的一方面，ShardingSphere-Proxy 在增加数据源并进行初始化时，会反对为每个数据库配置一个 Hikari 连接池。Hikari 是业界宽泛应用的连接池，对性能损耗较小，而且被 SpringBoot 采纳为缺省连接池。ShardingSphere-Proxy 的连接池能够反对用户配置最大连接数、最大闲暇工夫以及缓存相干的信息等。除 Hikari 连接池外，ShardingSphere-Proxy 也反对其它连接池的配置。

和现有 SQL 的语法兼容性也是用户掂量数据库代理的关键因素，因为这波及到是否更改利用代码。以 MySQL 为例，ShardingSphere 反对大部分的 MySQL 语法，但也有大量不反对的语法，比方 optimize 表、资源组的治理、用户的创立和 GRANT 权限治理等。具体能够查阅 ShardingSphere 的最新文档。

上面会分享我对 ShardingSphereProxy 连贯 Aurora 的几个维度的试验测试：1）分库分表 2）动静扩大 3）读写拆散 4）多表 join 5）故障复原。

3. 环境构建

3.1 Aurora 集群搭建

首先依据 Aurora 集群创立指南创立三套 Aurora MySQL 集群，机型为 db.r5.2xlarge，每套集群有一个写节点一个读节点。

3.2 ShardingSphere-Proxy 搭建

在与 Aurora 雷同的可用区下启动一台 EC2 节点，机型为 r5.8xlarge. 而后在下面装置 ShardingSphere-Proxy。

3.2.1 下载安装包

间接下载二进制安装包，进行解压。下载最新版本 5.1.0，它对 DistSQL 反对较好。

wget https://dlcdn.apache.org/shardingsphere/5.1.0/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz
tar -xvf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz

SharingSphereProxy 自带的库里蕴含对 PostgreSQL 的 JDBC driver，但不蕴含 MySQL 的 driver。因为创立的集群是 MySQL，须要将 MySQL 的 JDBC driver 拷贝到 lib 目录。

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
cp mysql-connector-java-5.1.47.jar apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib/

3.2.2 配置 Proxy 的服务端

在 ShardingSphere-Proxy 的根目录下，有个配置文件目录为 conf，外面有一个文件是 server.yaml，用来配置 ShardingSphere-Proxy 本人作为代理对外提供服务的信息以及元信息寄存等。上面是一个配置示例，外面配置了用户权限信息，特定属性信息，以及元信息以集群模式寄存在 zookeeper 里。

rules:
  - !AUTHORITY
    users:  // 拜访 Proxy 的用户名和明码信息
      - root@%:root
      - sharding@:sharding
    provider:  // 管制用户对 schema 的登陆权限
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION  // 事务类型配置，反对本地事务、XA 两阶段事务、BASE 柔性事务
    defaultType: XA
    providerType: Atomikos

props:  // 特定属性配置
  max-connections-size-per-query: 1
  proxy-hint-enabled: true // 为强制路由应用，默认值为 false

mode: // 元信息寄存的配置，shardingsphereProxy 反对三种模式：内存、单机和集群
  type: Cluster
  repository:
    type: ZooKeeper // 能够设置为 zookeeper、etcd 等
    props:
      namespace: shardingproxy
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false

3.3 启动 Proxy

间接在 ShardingSphereProxy 根目录下的 bin 对应着启动和进行脚本。运行时的日志在目录 logs 下。启动 Proxy

bin/start.sh
bin/stop.sh

3.4 验证连贯

如无非凡配置，ShardingSphereProxy 默认应用 3307 端口。应用 3.2.2 中配置的用户名和明码登录 proxy。在 EC2 上运行 mysql 命令行工具进行连贯，连贯胜利。留神这里没有任何数据库，因为咱们没有应用 YAML 配置文件事后配置数据源。

[ec2-user@ip-111-22-3-123 bin]$ mysql -h 127.0.0.1 -uroot --port 3307 -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.22-ShardingSphere-Proxy 5.1.0 

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;
Empty set (0.01 sec)

4. 功能测试

4.1 DistSQL 创立分片规定和数据分片测试

本节来验证 ShardingSphere 的根本的分库分表能力。ShardingSphereProxy 反对两种形式创立分片规定和读写拆散规定，YAML 和 DistSQL。DistSQL 扩大了 SQL 语法，能够反对在线创立数据源、创立和更改建表规定，较为灵便，本文只介绍 DistSQL 的用例。

4.1.1 创立数据库

连贯到 ShardingSphere-Proxy，去创立数据库，作为逻辑的分布式数据库。

MySQL [(none)]> create database distsql_sharding_db;
Query OK, 0 rows affected (0.90 sec)

在各个 Aurora 集群上创立数据库，作为数据库源进行连贯。其中，rshard1,rshard2,rshard3 是我本人定义的连贯 Aurora 数据库的 alias。

alias rshard1=’mysql -h $dbname -u$username -p$password’

[ec2-user@ ip-111-22-3-123 bin]$ rshard1 -e "create database dist_ds";
[ec2-user@ ip-111-22-3-123 bin]$ rshard2 -e "create database dist_ds;"
[ec2-user@ ip-111-22-3-123 bin]$ rshard3 -e "create database dist_ds;"

4.1.2 创立数据源

在 ShadingSphereProxy 中运行上面 DistSQL 语句创立 3 个数据源，别离指向 3 个不同 Aurora 集群

MySQL [distsql_sharding_db]> add resource ds_0(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/dist_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.03 sec)

MySQL [distsql_sharding_db]> add resource ds_1(url="jdbc:mysql://aurora-2-07-7-shard2.cluster-12345678.us-east-1.rds.amazonaws.com:3306/dist_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.06 sec)

MySQL [distsql_sharding_db]> add resource ds_2(url="jdbc:mysql://aurora-2-07-7-shard3.cluster-12345678.us-east-1.rds.amazonaws.com:3306/dist_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.05 sec)

4.1.3 创立分片规定

这里指明 t_order 表的分片规定，留神分片规定的表名和后续要创立的表表名统一。具体规定为：对底层的 3 个数据源（Aurora 集群）依照 order_id 对表进行 hash 分片，分成 6 份。另外，对 order_id 采纳值主动生成的策略，采纳策略为 snowflake 算法。ShardingSphere 反对两种分布式主键生成策略：UUID 和雪花算法 SNOWFLAKE。应用雪花算法生成的主键，二进制示意模式蕴含 4 局部，从高位到低位分表为：1bit 符号位、41bit 工夫戳位、10bit 工作过程位以及 12bit 序列号位。在 ShardingSphereProxy 中运行上面 DistSQL 语句建设分片规定：

MySQL [distsql_sharding_db]> CREATE SHARDING TABLE RULE t_order(→ RESOURCES(ds_0,ds_1, ds_2),
→ SHARDING_COLUMN=order_id,
→ TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)),
→ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
→ );
Query OK, 0 rows affected (0.02 sec)

4.1.4 建表

建表语句和一般 MySQL 建表语句统一。在 ShardingSphereProxy 中运行上面语句建表：

MySQL [distsql_sharding_db]> CREATE TABLE `t_order` (`order_id` bigint NOT NULL, `user_id` int NOT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    -> ;
Query OK, 0 rows affected (0.22 sec)

在 ShardingSphereProxy 上查看表的状态。

MySQL [distsql_sharding_db]> show tables;
+-------------------------------+------------+
| Tables_in_distsql_sharding_db | Table_type |
+-------------------------------+------------+
| t_order                       | BASE TABLE |
+-------------------------------+------------+
1 row in set (0.00 sec)

别离连贯到 3 个 Aurora 集群上查看表是否主动创立。能够看到每个底层数据库集群上都创立了两张表，一共是 6 张表。而且表名是以“t_oder_”数字排序的

[ec2-user@ ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "show tables;"
+-------------------+
| Tables_in_dist_ds |
+-------------------+
| t_order_0         |
| t_order_3         |
+-------------------+
[ec2-user@ ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "show tables;"
+-------------------+
| Tables_in_dist_ds |
+-------------------+
| t_order_1         |
| t_order_4         |
+-------------------+
[ec2-user@ ip-111-22-3-123 bin]$ rshard3 -Ddist_ds -e "show tables;"
+-------------------+
| Tables_in_dist_ds |
+-------------------+
| t_order_2         |
| t_order_5         |
+-------------------+

4.1.5 插入和查找数据

在 ShardingSphere-Proxy 中插入并查找数据，数据能够失常插入和查找胜利。在 ShardingSphere-Proxy 中运行：

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (1, 'ok');
insert into t_order(user_id, status) values (2, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (2, 'abc');
insert into t_order(user_id, status) values (3, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (3, 'abc');
insert into t_order(user_id, status) values (4, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (4, 'abc');
insert into t_order(user_id, status) values (5, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (5, 'abc');
insert into t_order(user_id, status) values (6, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (6, 'abc');
insert into t_order(user_id, status) values (7, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (7, 'abc');
insert into t_order(user_id, status) values (8, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (8, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (9, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161915748353 |       2 | abc    |
| 708700161995440128 |       5 | abc    |
| 708700169725542400 |       9 | abc    |
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
| 708700161970274305 |       4 | abc    |
| 708700162016411649 |       6 | abc    |
| 708700162058354689 |       8 | abc    |
+--------------------+---------+--------+
9 rows in set (0.01 sec)

去各个 Aurora 集群中查找子表插入的数据, 能够看到在 Proxy 插入的 9 条记录被打散到底层的 6 张表中。因为 order_id 为 snowflake 算法生成而数据量比拟小，这里的数据并不平均。

[ec2-user@ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "select * from t_order_0;"
[ec2-user@ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "select * from t_order_3;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161915748353 |       2 | abc    |
+--------------------+---------+--------+
[ec2-user@ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "select * from t_order_1;"
[ec2-user@ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "select * from t_order_4;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161995440128 |       5 | abc    |
| 708700169725542400 |       9 | abc    |
+--------------------+---------+--------+
[ec2-user@111-22-3-123 bin]$ rshard3 -Ddist_ds -e "select * from t_order_2;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
+--------------------+---------+--------+
[ec2-user@ip-111-22-3-123 bin]$ rshard3 -Ddist_ds -e "select * from t_order_5;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161970274305 |       4 | abc    |
| 708700162016411649 |       6 | abc    |
| 708700162058354689 |       8 | abc    |
+--------------------+---------+--------+</code></pre></div>

上述试验验证了 ShardingSphere-Proxy 具备创立逻辑库、连贯数据源、创立分片规定、创立逻辑表时会主动在底层数据库上创立子表、可能执行查问的散发以及聚合能力。

4.2 动静伸缩验证（在线扩大分片）

本节来验证 ShardingSphere-Proxy 是否具备动静更改表的分片规定的能力。

ShardingSphere-Proxy 提供在线更改分片规定的能力，然而如果子表曾经依照之前的规定创立胜利，则不会有新的子表随着分片数目的增多被创立进去，也不会有原来的子表随着分片数目的缩小而被删除。所以须要手动在底层分片数据库上创立表名和迁徙数据。

将 4.1 节里的表的分片数从 6 调高到 9，批改分片规定自身可能胜利，然而后续查找会出错，因为没有新的子表创立进去。在 ShardingSphere-Proxy 上运行上面 DistSQL：

MySQL [distsql_sharding_db]> alter SHARDING TABLE RULE t_order(-> RESOURCES(ds_0,ds_1, ds_2),
    -> SHARDING_COLUMN=order_id,
    -> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=9)),
    -> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
    -> );
Query OK, 0 rows affected (0.01 sec)

MySQL [distsql_sharding_db]> select * from t_order;
ERROR 1146 (42S02): Table 'dist_ds.t_order_6' doesn't exist

如果此时在子集群上别离创立好对应的子表，再在 ShardingSphere-Proxy 上查找就不会再出错。连贯到 3 个 Aurora 集群，手动创立子表

[ec2-user@ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "create table t_order_6(order_id bigint not null, user_id int not null, status varchar(45) default null, primary key(order_id)) engine=innodb default charset=utf8mb4;"
[ec2-user@ ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "create table t_order_7(order_id bigint not null, user_id int not null, status varchar(45) default null, primary key(order_id)) engine=innodb default charset=utf8mb4;"
[ec2-user@ip-111-22-3-123 bin]$ rshard3 -Ddist_ds -e "create table t_order_8(order_id bigint not null, user_id int not null, status varchar(45) default null, primary key(order_id)) engine=innodb default charset=utf8mb4;"

Proxy 查找整个逻辑表不再报错。在 ShardingSphere-Proxy 上运行上面 SQL：

MySQL [distsql_sharding_db]> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161915748353 |       2 | abc    |
| 708700161995440128 |       5 | abc    |
| 708700169725542400 |       9 | abc    |
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
| 708700161970274305 |       4 | abc    |
| 708700162016411649 |       6 | abc    |
| 708700162058354689 |       8 | abc    |
+--------------------+---------+--------+
9 rows in set (0.01 sec)

如果有新的数据插入，会依照新的分片规定进行到子表的映射。在 ShardingSphere-Proxy 上查看 SQL 语句的查问打算：

MySQL [distsql_sharding_db]> preview insert into t_order values(7, 100, 'new');
+------------------+---------------------------------------------+
| data_source_name | sql                                         |
+------------------+---------------------------------------------+
| ds_1             | insert into t_order_7 values(7, 100, 'new') |
+------------------+---------------------------------------------+
1 row in set (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order values(7, 100, 'new');
Query OK, 1 row affected (0.00 sec)

登录到 Aurora 子集群上查看子表，能够看到数据曾经胜利插入。

[ec2-user@ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "select * from t_order_7;"
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        7 |     100 | new    |
+----------+---------+--------+

再来看下在线缩小分片的状况。如果将分片数目调小，比方调到 3，表里的已有数据不会被迁徙，查找整张表时只能拿到局部数据。在 ShardingSphere-Proxy 上运行上面 DistSQL 和 SQL 语句：

MySQL [distsql_sharding_db]> alter SHARDING TABLE RULE t_order(->     RESOURCES(ds_0,ds_1, ds_2),
    ->     SHARDING_COLUMN=order_id,
    ->     TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=3)),
    ->     KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
    ->     );
Query OK, 0 rows affected (0.02 sec)
MySQL [distsql_sharding_db]> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
+--------------------+---------+--------+
3 rows in set (0.00 sec)

通过下面验证，咱们的论断是 ShardingSphereProxy 的分片规定是能够在线更改的，但子表的创立和数据的从新散布须要手动去实现。

4.3 绑定表和播送表的测试

本节来验证 ShardingSphere-Proxy 对于多表 join 的反对。只管 OLTP 的数据库中的操作通常较为简单，但也有可能会波及到多表 join 的状况。ShardingSphereProxy 针对多表 join 的优化有反对绑定表和播送表。如果两张表是绑定表而且 join 时采纳的是 shard key，能够进行两张表的 join。播送表通过把小表复制到各个节点，能够实现大表和小表的疾速 join。

4.3.1 绑定表

ShardingSphereProxy 的绑定表能够通过 DistSQL 里的 CREATE SHARDING BINDING TABLE RULES 来绑定两张表。这里以 4.1 节中提到的 t_order 表和新创建的一张表 t_order_item 为例进行开展。

连贯到 ShardingSphere-Proxy 上运行上面 DistSQL 和 SQL 语句。

MySQL [distsql_sharding_db]> CREATE SHARDING TABLE RULE t_order_item(->  RESOURCES(ds_0,ds_1, ds_2),
    ->  SHARDING_COLUMN=order_id,
    -> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)));
Query OK, 0 rows affected (0.04 sec)

MySQL [distsql_sharding_db]> CREATE TABLE `t_order_item` (`order_id` bigint NOT NULL, `item_id` int NOT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected (0.08 sec)

创立了 binding rule 当前，查看 join 打算，咱们看到 join 下推到对应子表和子表的 join 上。在 ShardingSphere-Proxy 上运行：

MySQL [distsql_sharding_db]>  CREATE SHARDING BINDING TABLE RULES (t_order,t_order_item);
Query OK, 0 rows affected (0.04 sec)

MySQL [distsql_sharding_db]> preview select * from t_order, t_order_item where t_order.order_id=t_order_item.order_id;
+------------------+------------------------------------------------------------------------------------------+
| data_source_name | sql                                                                                      |
+------------------+------------------------------------------------------------------------------------------+
| ds_0             | select * from t_order_0, t_order_item_0 where t_order_0.order_id=t_order_item_0.order_id |
| ds_0             | select * from t_order_3, t_order_item_3 where t_order_3.order_id=t_order_item_3.order_id |
| ds_1             | select * from t_order_1, t_order_item_1 where t_order_1.order_id=t_order_item_1.order_id |
| ds_1             | select * from t_order_4, t_order_item_4 where t_order_4.order_id=t_order_item_4.order_id |
| ds_2             | select * from t_order_2, t_order_item_2 where t_order_2.order_id=t_order_item_2.order_id |
| ds_2             | select * from t_order_5, t_order_item_5 where t_order_5.order_id=t_order_item_5.order_id |
+------------------+------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

4.3.2 播送表

播送表是指每张表在每个库里都有一个残缺的备份，能够通过 CREATE SHARDING BROADCAST TABLE RULES 来指定。

MySQL [distsql_sharding_db]> CREATE SHARDING BROADCAST TABLE RULES (t_user);
Query OK, 0 rows affected (0.03 sec)

MySQL [distsql_sharding_db]> create table t_user (user_id int, name varchar(100));
Query OK, 0 rows affected (0.04 sec)

登录到各个 shard Aurora 集群查看创立的表。能够看到与分片表的子表名开端有数字序号不同的是，播送表对应的每个库上的名字是雷同的，就是逻辑表名自身。

[ec2-user@ip-111-22-3-123 bin]$ rshard1 -D dist_ds -e "show tables like'%user%';"
+----------------------------+
| Tables_in_dist_ds (%user%) |
+----------------------------+
| t_user                     |
+----------------------------+
[ec2-user@ip-111-22-3-123 bin]$ rshard2 -D dist_ds -e "show tables like'%user%';"
+----------------------------+
| Tables_in_dist_ds (%user%) |
+----------------------------+
| t_user                     |
+----------------------------+
[ec2-user@ip-111-22-3-123 bin]$ rshard3 -D dist_ds -e "show tables like'%user%';"
+----------------------------+
| Tables_in_dist_ds (%user%) |
+----------------------------+
| t_user                     |
+----------------------------+

在 ShardingSphereProxy 中运行播送表和其它表的 join，采纳的是本地 join 的形式。

MySQL [distsql_sharding_db]> preview select * from t_order, t_user where t_order.user_id=t_user.user_id;
+------------------+------------------------------------------------------------------------+
| data_source_name | sql                                                                    |
+------------------+------------------------------------------------------------------------+
| ds_0             | select * from t_order_0, t_user where t_order_0.user_id=t_user.user_id |
| ds_0             | select * from t_order_3, t_user where t_order_3.user_id=t_user.user_id |
| ds_1             | select * from t_order_1, t_user where t_order_1.user_id=t_user.user_id |
| ds_1             | select * from t_order_4, t_user where t_order_4.user_id=t_user.user_id |
| ds_2             | select * from t_order_2, t_user where t_order_2.user_id=t_user.user_id |
| ds_2             | select * from t_order_5, t_user where t_order_5.user_id=t_user.user_id |
+------------------+--------

下面试验验证了 ShardingSphere-Proxy 是能够反对两张绑定表的 join，以及播送表和分片表的 join 的。对于非绑定的两张分片表的 join，ShardingSphere-Proxy 有一个 Federation 的性能是在反对的，但还不是很成熟，倡议后续继续关注。

4.4 读写拆散性能验证

本节来验证 ShardingSphere-Proxy 对于读写拆散的反对。随着业务增长，写和读的负载别离在不同的数据库节点上可能无效提供整个数据库集群的解决能力。Aurora 通过读 / 写的 endpoint 能够满足用户写和强一致性读的需要，只读的 endpoint 能够满足用户非强一致性读的需要。Aurora 的读写提早在毫秒级别，比 MySQL 基于 binlog 的逻辑复制要低得多，所以有很多负载是能够间接打到只读 endpoint 的。

ShardingSphereProxy 提供的读写拆散的个性能够进一步能够封装 Aurora 的读 / 写端点和只读端点。用户能够间接连贯到 Proxy 的端点，即可进行主动的读写拆散。ShardingSphereProxy 对非凡状况的解决逻辑是：1）同一线程且同一数据库连贯内，如果有写入操作，则后续的读操作均从主库读取 2）能够通过 Hint 的机制强制把读申请发到写节点（主库）。上面会以 Aurora3 个集群中的第一个集群来验证 ShardingSphere-Proxy 读写拆散的能力。

4.4.1 查看 Aurora 集群读 / 写端点和只读端点

Aurora 集群有两个端点，写的端点和读的端点。

4.4.2 在 Aurora 集群中创立数据库

连贯到 Aurora 集群中运行：

[ec2-user@ip-111-22-3-123 ~]$ rdbw -e "create database wr_ds;"

4.4.3 数据源配置

在 ShardingSphere-Proxy 上创立数据源，写的数据源指向 Aurora 的读写 endpoint，读的数据源指向 Aurora 的只读 endpoint。留神：对域名的状况，ShardingSphereProxy 只反对通过 url 的形式创立数据源，尚未反对通过 HOST、Port 的形式。连贯到 ShardingSphere-Proxy 上创立逻辑数据库 distsql_rwsplit_db 并在改数据库中增加数据源：

MySQL [(none)]> create database distsql_rwsplit_db;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> use distsql_rwsplit_db;
Database changed
MySQL [distsql_rwsplit_db]> add resource write_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678), read_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-ro-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.08 sec)

4.4.4 读写拆散规定配置

创立读写拆散规定，写申请发到写的数据源，读申请发到读的数据源。与分库分表规定要求 RULE 前面必须是表名不同的是，这里的 RULE 前面跟的是数据源的名字，实用于在这个数据库里创立的所有的表。在 ShardingSphere-Proxy 上运行上面 DistSQL 语句：

MySQL [distsql_ rwsplit_db]> CREATE READWRITE_SPLITTING RULE wr_ds (
    -> WRITE_RESOURCE=write_ds,
    -> READ_RESOURCES(read_ds),
    -> TYPE(NAME=random)
    -> );
Query OK, 0 rows affected (0.36 sec)

4.4.5 建表

创立一张一般表，建表语句和 MySQL 建表语句统一。在 ShardingSphere-Proxy 上运行上面 SQL 语句：

MySQL [distsql_ rwsplit_db]> create table wr_table (a int, b int, c varchar(20));
Query OK, 0 rows affected (0.17 sec)

4.4.6 查看读写拆散是否实现

在 ShardingSphere-Proxy 上运行上面语句查看查问打算，查看语句是发送到底层哪个数据源。能够看到：写申请发送到写节点，读申请会发送到读写点。

MySQL [distsql_rwsplit_db]> preview insert into wr_table values(1,1,'ab');
+------------------+---------------------------------------+
| data_source_name | sql                                   |
+------------------+---------------------------------------+
| write_ds         | insert into wr_table values(1,1,'ab') |
+------------------+---------------------------------------+
1 row in set (0.10 sec)
MySQL [distsql_rwsplit_db]> preview select * from wr_table;
+------------------+------------------------+
| data_source_name | sql                    |
+------------------+------------------------+
| read_ds          | select * from wr_table |
+------------------+------------------------+
1 row in set (0.02 sec)

运行一个脚本来屡次操作，再去 Aurora 集群指标监控中去验证。该脚本是一个循环，运行 1000 次，每次会插入一条记录，并查找表的记录总条数。

[ec2-user@ip-111-22-3-123 shardingproxy]$ cat testReadWrite.sh 

#!/bin/bash
n=1
while [$n -le 1000]
do
    mysql -h 127.0.0.1 -uroot --port 3307 -proot -Ddistsql_rwsplit_db -e "insert into wr_table values($n,$n,'ok');"
    mysql -h 127.0.0.1 -uroot --port 3307 -proot -Ddistsql_rwsplit_db -e "select count(*) from wr_table;"
    let n++
done  

查看 Aurora 集群的写节点和读节点的读写提早，能够看到写提早只在写节点上产生，读提早只在读节点上产生。阐明读写拆散规定失效。

只管 Aurora 的写和读节点之间的复制提早很低在毫秒级别，但某些利用还是会有强一致性的需要，即要求写后立即能够读。这时候，能够采纳强制将读申请发送到写节点的形式。ShardingSphereProxy 通过 hint 的形式来反对。首先须要在后面提到的 conf/server.yaml 里增加一个属性 proxy-hint-enabled: true。而后在连贯中显式设置 readwrite_splitting hint source 值为 write 来开启强制路由到写节点通过设置值为 auto 或者 clear hint 能够采纳默认的规定。readwrite_splitting hint source 能够在 session 级别失效。

在 ShardingSphere-Proxy 上顺次运行上面语句。能够看到默认的读申请是发送到读节点，将 readwrite_splitting hint source 设置为 write 当前，会发送到写节点，再设成 auto，能够发回至读写点。

MySQL [distsql_rwsplit_db]> preview select count(*) from wr_table;
+------------------+-------------------------------+
| data_source_name | sql                           |
+------------------+-------------------------------+
| read_ds          | select count(*) from wr_table |
+------------------+-------------------------------+
1 row in set (0.01 sec)

MySQL [distsql_rwsplit_db]> set readwrite_splitting hint source = write;
Query OK, 0 rows affected (0.00 sec)

MySQL [distsql_rwsplit_db]> preview select count(*) from wr_table;
+------------------+-------------------------------+
| data_source_name | sql                           |
+------------------+-------------------------------+
| write_ds         | select count(*) from wr_table |
+------------------+-------------------------------+
1 row in set (0.01 sec)
MySQL [distsql_rwsplit_db]> set readwrite_splitting hint source = auto;
Query OK, 0 rows affected (0.00 sec)

MySQL [distsql_rwsplit_db]> preview select count(*) from wr_table;
+------------------+-------------------------------+
| data_source_name | sql                           |
+------------------+-------------------------------+
| read_ds          | select count(*) from wr_table |
+------------------+-------------------------------+
1 row in set (0.00 sec)

另外不应用 YAML 文件更改的形式是间接在 DistSQL 里先后设置两个变量 proxy_hint_enabled 和 readwrite_splitting hint source。

MySQL [distsql_rwsplit_db]> set variable proxy_hint_enabled=true;
Query OK, 0 rows affected (0.01 sec)
MySQL [distsql_rwsplit_db]> set readwrite_splitting hint source = write;
Query OK, 0 rows affected (0.01 sec)
MySQL [distsql_rwsplit_db]> preview select * from wr_table;
+------------------+------------------------+
| data_source_name | sql                    |
+------------------+------------------------+
| write_ds         | select * from wr_table |
+------------------+------------------------+
1 row in set (0.00 sec)

以上试验验证了 ShardingSphere-Proxy 有良好的读写拆散的能力。它验证了底下连贯单个 Aurora 集群进行读写拆散的场景。如果既须要分库分表又须要读写拆散，ShardingSphere-Proxy 也是反对的。比方先分到 3 个 Aurora 集群，而后每个集群须要提供读写拆散的能力，咱们能够间接将读写拆散规定前面定义的数据源名称（4.4.4 里的 wr_ds）放在分库分表规定对每张表指定的数据源里（4.1.3 里的 ds_0，ds_1,ds_2）。

4.5 故障复原验证

本节来验证 ShardingSphere-Proxy 对于 Aurora 集群故障切换的感知能力。在 Aurora 集群产生主备切换时，如果 Proxy 可能动静检测到主备切换并连贯到新的主数据库是比拟现实的。本节试验依然是验证第一个 Aurora 集群。

测试脚本如下，它会继续连贯到写节点并发送 update 申请，每次申请距离 1 秒钟。

[ec2-user@ip-111-22-3-123 shardingproxy]$ cat testFailover.sh 
#!/bin/bash
while true
do
    mysql -h 127.0.0.1 -uroot --port 3307 -proot -Ddistsql_rwsplit_db -e "update wr_table set c='failover'where a = 1;"
    now=$(date +"%T")
    echo "update done: $now"
    sleep 1
done   

运行脚本，而后在 Aurora 集群的写节点上点击 Action->Failover。会启动 Aurora 写节点和读节点的主动切换。在切换过程中，整个集群的读 / 写 endpoint 和只读 endpoint 维持不变，只是底层映射的节点发生变化。

通过观测 Aurora 的 Event（事件），能够看到整个故障切换在 30 秒左右实现。

遗憾的是，应用程序间接连贯 ShardingSphereProxy 也就是后面的运行脚本不能主动监测到底层的 IP 变动。运行脚本始终抛错：

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 15:04:04
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 15:04:05
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 15:04:06

间接在 MySQL 命令行连贯到 Proxy 也是会有一样的谬误。

MySQL [distsql_rwsplit_db]> update wr_table set c="failover" where a =2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

剖析起因在于 Aurora 产生故障切换的时候，读写 endpoint 和 IP 的映射会发生变化，而 ShardingSphere 的连接池在连贯 Aurora 的时候，没有更新到新的 IP 上。咱们能够采纳上面的 workaround 能够使 ShardingSphereProxy 指向新的写节点，即从新创立数据源。只管数据源自身定义没有发生变化，然而通过重建数据源 alter resource 的操作，ShardingSphereProxy 会从新拿取一遍 endpoint 到 IP 的映射，所以可能胜利运行。

MySQL [distsql_rwsplit_db]> alter resource write_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678), read_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-ro-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.05 sec)

MySQL [distsql_rwsplit_db]> update wr_table set c="failover" where a =2;
Query OK, 1 row affected (0.01 sec)

每次 Aurora 故障切换时，咱们能够检测故障切换的 event，或者是在利用收到 read-only 报错时显式调用下面语句。为了升高对利用的影响，咱们能够采纳 Lambda 的形式将 failover 重置数据源的操作自动化。因为 Aurora 的 failover 的事件是能够被监测到的，咱们能够写一个 Lambda 函数，在监测到 failover 胜利当前，显示调用更改 resource 的操作。

总体的思路是：RDS 通过 Event Subscription 将 Event 的告诉信息传递给 SNS topic，再由 SNS topic 传递给 Lambda 办法，而后在 Lambda 办法里显式连贯 ShardingProxy 调用 alter resource 的 DistSQL 语句。

具体步骤如下：

4.5.1 创立 SNS

依照 SNS 创立指南创立 SNS。关上 SNS 的 dashboard，点击创立 SNS topic，抉择 Standard 规范类型。其它抉择默认或者依据须要调整。

4.5.2 对要进行的 Aurora 集群创立 Event Subscription

在 RDS 的 Event Subscriptions 上，点击“Create Event Subscription”，在弹出的选项卡中抉择 Target 为上一步骤创立的 SNS，Source type 抉择为 Cluster，cluster 外面选中咱们须要关注的 Aurora 的集群，事件抉择 Failover 事件。

4.5.3 创立 Lamdba 办法

因为 Lambda 要调用 VPC 里的 EC2 上部署的 ShardingProxy，应该给它绑定一个专门的 Role，这个 Role 有权限在 VPC 里执行 Lambda 办法: AWSLambdaVPCAccessExecutionRole 依照 IAM Role 创立文档创立 Role 和 Policy，使 failoverlambda 的 role 有 AWSLambdaVPCAccessExecutionRole 的权限。

接下来依照 Lambda 文档创立 Lambda 办法

创立好 Lambda 办法当前，点击 Trigger，指明为 SNS，并指明在 4.5.1 里创立的 SNS topic。

4.5.4 编写 Lambda 办法

import os
import json
import pymysql

# connect to ShardingProxy to reset the data source    
def resetDataSource():
    db = pymysql.connect(host='111.22.3.123', user='root', password='root', port=3307, database='distsql_rwsplit_db')
    
    cur = db.cursor()
    SQL = "alter resource write_ds(url=\"jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false\",user=admin,password=12345678), read_ds(url=\"jdbc:mysql://aurora-2-07-7-shard1.cluster-ro-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false\",user=admin,password=12345678);"
    print (SQL)
    cur.execute(SQL)
    result = cur.fetchall()
    for x in result:
        print(x)
    db.close()

def lambda_handler(event, context):
    wholeMessage = event['Records'][0]['Sns']['Message']
    print ("whole message" + wholeMessage)
    
    wholeMessageInJson = json.loads(wholeMessage)
    eventMessage = wholeMessageInJson['Event Message']
    print ("event message:" + eventMessage)
    
    isFailover = eventMessage.startswith('Completed failover to DB instance')
    if isFailover == True:
        print ("Failover completed!" + eventMessage)
        resetDataSource()

    return {
        'statusCode': 200,
        'body': Lambda Invocation Successful!'
    }

Lambda 办法是 Python 语言书写的，在拜访 ShardingSphereProxy 时以 MySQL 形式拜访，所以须要引入 pymysql 的 lib 库。具体方法为：

1）在 Linux 上装置 pymysql，以 Amazon-Linux 虚拟机为例，会默认装置到目录./home/ec2-user/.local/lib/python3.7/site-packages/pymysql 下

2）将 pymysql 目录拷贝到长期目录 /tmp

3）写 Lambda 办法，存储到 lambda_function.py 文件中

4）打包 zip -r lambda.zip pymysql lambda_function.py

5）在控制台通过 S3 或者本地上传。

4.5.5 设置 ShardingSphereProxy 所在 EC2 的 security group

因为 Lambda 要在 VPC 里拜访 ShardingSphereProxy，而 ShardingSphereProxy 以 3307 端口运行，应该配置相应 secuity group，关上 3307 端口给同一个 VPC 外部的拜访。根据平安组配置文档配置成的 security group 如下：

4.5.6 验证 failover

重复本大节开始的操作，运行 testFailover.sh，而后手动在 RDS console 页 failover Aurora 节点，会发现 testFailover.sh 继续稳固输入, 不会再呈现 read-only 的谬误。

update done: 13:44:44
…
update done: 13:44:47
update done: 13:45:00
update done: 13:45:01
…
update done: 13:45:17

去 cloudwatch 里查看 Lambda function 的日志，会发现 Lambda 被胜利调用。

以上试验验证了 ShardingSphere-Proxy 对于 Aurora 集群故障切换的感知能力。只管 ShardingSphere-Proxy 本人没有提供良好的匹配性，通过监测 Aurora 集群事件触发 Lamdba 办法来显式重置 ShardingSphere-Proxy 数据源的形式，咱们能够实现 ShardingSphere-Proxy 与 Aurora 联合的故障切换能力。

5. 结语

本篇文章通过数据库中间件 ShardingSphere-Proxy 拓展了 Aurora 的分库分表能力和读写拆散的能力。

ShardingSphere-Proxy 内置连接池，对 MySQL 语法反对较强，在分库分表和读写拆散上表现出色。它对多表 join 上，能够反对分片规定雷同的表的 join，以及小表和大表的 join，根本能满足 OLTP 场景的需要。在动静分片上，ShardingSphere-Proxy 提供在线更改分片规定的能力，但须要用户在底层 Aurora 集群手动操作子表创立及数据迁徙，须要肯定工作量。故障切换维度，ShardingSphere-Proxy 与 Aurora 的交融不是很好，然而能够通过本文提供的 Aurora 故障切换 Event 调用 Lambda 办法来显式重置数据源的形式，实现 ShardingSphere-Proxy 对 Aurora 集群故障切换对感知。

总体而言，ShardingSphere-Proxy 这个中间件产品还是能与 Aurora 集群进行一个良好匹配，进一步晋升 Aurora 集群的读写能力的。它有良好的文档，也是比拟受关注的开源产品，倡议读者在思考 Aurora 分库分表实现时，评估下这个产品。后续咱们会持续推出对其余中间件以及 JDBC 方面的拓展和钻研系列博客。

本篇作者

马丽丽
亚马逊云科技数据库解决方案架构师，十余年数据库行业教训，先后涉猎 NoSQL 数据库 Hadoop/Hive、企业级数据库 DB2、分布式数仓 Greenplum/Apache HAWQ 以及亚马逊云原生数据库的开发和钻研。

点击链接，理解更多内容：

• Apache ShardingSphere GitHub 地址：https://github.com/apache/sha…
• SphereEx 官网：https://www.sphere-ex.com