As
Databases AWS

Apache ShardingSphere Proxy 5.5.3 on AWS User Guide

| Product: Apache ShardingSphere Proxy 5.5.3 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying Apache ShardingSphere Proxy 5.5.3 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other tools in the listing are described on the listing's guide hub.

Apache ShardingSphere Proxy is a database middleware server. Applications connect to it with an ordinary MySQL client or driver, and it presents one logical database while routing each statement to the right physical databases and tables underneath (sharding), rewriting and merging results as needed. It also offers read/write splitting, data encryption and masking rules, and DistSQL, a SQL dialect for managing those rules at run time.

The image installs the official Apache binary release ShardingSphere Proxy 5.5.3 (checksum pinned) on OpenJDK 17, with MySQL Server 8.0 from Ubuntu as a local backend so the sharding demo works the moment the instance boots. Point the proxy at your own MySQL servers (for example Amazon RDS) when you are ready; see Maintenance.

What is on the image:

  • ShardingSphere Proxy 5.5.3 (shardingsphere-proxy.service) speaking the MySQL protocol on 127.0.0.1:3307, with TLS 1.2/1.3 on the client listener
  • MySQL Server 8.0 (mysql.service) on 127.0.0.1:3306 as the backend, its data directory on a dedicated 20 GiB EBS volume mounted at /var/lib/shardingsphere
  • MySQL Connector/J 26.7.0, which the proxy uses to reach the backend (over TLS)
  • A logical database sharding_db whose table t_order is sharded across two backend schemas (demo_ds_0, demo_ds_1) and two tables in each (t_order_0, t_order_1)
  • Two proxy users: app (application user, sharding_db only) and admin (administrator for DistSQL, accepted from the instance itself only)

Default user: ubuntu · Architecture: x86_64 · Root volume: 8 GiB gp3 · Data volume: 20 GiB gp3

Security posture

  • No default login. ShardingSphere's sample configuration ships a root/root user; this image ships no proxy user at all and the proxy cannot start until first boot has run. First boot generates unique passwords for the proxy users app and admin, the backend MySQL user ssuser and Ubuntu's debian-sys-maint maintenance account, writes the proxy configuration and then starts the proxy. root/root is refused. The passwords are in /root/shardingsphere-credentials.txt, readable by root only.
  • The admin account is local only. admin is defined as admin@127.0.0.1, so the proxy accepts it only from the instance itself, even if you open the proxy to your network. The app user can reach sharding_db only.
  • TLS on the client listener. The proxy offers TLS 1.2 and 1.3. ShardingSphere creates the key pair and self-signed certificate in memory each time it starts, so no key is stored on disk or shared between instances. MySQL's own TLS and RSA keys, and its server UUID, are also created on each instance's first boot. The proxy's connections to MySQL use TLS.
  • Localhost only. The proxy and MySQL listen on 127.0.0.1, MySQL's X Protocol port is off, and the listing's security group opens SSH (22) only. Reach the proxy through an SSH tunnel, or open it to your application network as described in Step 7.
  • Hardened MySQL backend. root uses socket authentication only (no password exists), there are no anonymous or remote accounts, binary logging and LOCAL INFILE are off, and the data directory sits on its own EBS volume.
  • Sandboxed JVM. The proxy runs as the unprivileged shardingsphere user with no capabilities, NoNewPrivileges, a read-only view of the system (ProtectSystem=strict), no access to home directories, a private /tmp and no device access. It cannot change its own configuration.
  • No metadata access for the proxy or the database. DistSQL's REGISTER STORAGE UNIT makes the proxy open a JDBC connection to whatever address a user supplies, so the proxy, MySQL and the first-boot service are all blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0.
  • Fails closed. If first boot cannot finish, it leaves the proxy stopped rather than half configured, and tries again on the next boot.
  • No secrets in logs. Passwords are never passed on a command line. The commands in this guide read them from root-only MySQL option files.
  • Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance.

DistSQL and proxy users (upstream behaviour). ShardingSphere Proxy 5.5.3 does not limit DistSQL to administrators: any authenticated proxy user can run DistSQL, including EXPORT METADATA and EXPORT DATABASE CONFIGURATION, which print the proxy's configuration, passwords included. Treat every proxy credential as administrative. This is why the image keeps the proxy on localhost, pins admin and the backend ssuser account to the instance itself, and blocks metadata access. Open port 3307 only to application hosts you trust with that level of access.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large (2 vCPUs, 8 GiB). The proxy's Java heap defaults to half of the instance memory and the local MySQL backend uses the rest; choose a larger instance for heavy query volumes.

Connecting to your instance

Version Operating system SSH user
Apache ShardingSphere Proxy 5.5.3 on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened Adminer & Database Admin and Backup Tools AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose Apache ShardingSphere Proxy 5.5.3 on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 3307 or 3306 to the internet.
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name shardingsphere-proxy \
  --description "Apache ShardingSphere Proxy" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=shardingsphere-proxy}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required. The data volume comes with the AMI.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute or two of launch. It sets the MySQL passwords, writes the proxy configuration, starts the proxy, checks that the app user can log in over TLS and that root/root is refused, and then writes the credentials file. Show the connection details (the three password lines are left out here; read them with sudo cat /root/shardingsphere-credentials.txt and keep them in your secrets manager):

sudo grep -vE '^(SHARDINGSPHERE_APP_PASSWORD|SHARDINGSPHERE_ADMIN_PASSWORD|MYSQL_BACKEND_PASSWORD)=' /root/shardingsphere-credentials.txt

# Apache ShardingSphere-Proxy 5.5.3 - generated on first boot by apache-shardingsphere-firstboot.service
# These passwords are unique to this instance. Store them somewhere safe.

SHARDINGSPHERE_HOST=127.0.0.1
SHARDINGSPHERE_PORT=3307
SHARDINGSPHERE_LOGICAL_DB=sharding_db
SHARDINGSPHERE_APP_USER=app
SHARDINGSPHERE_ADMIN_USER=admin
MYSQL_BACKEND_USER=ssuser
MYSQL_BACKEND_PORT=3306
PRIVATE_IP=<private-ip>

# Connect on the instance (option files hold the passwords; nothing on the command line):
#   sudo mysql --defaults-extra-file=/root/.shardingsphere-app.cnf sharding_db      (application user, TLS)
#   sudo mysql --defaults-extra-file=/root/.shardingsphere-admin.cnf                (admin: DistSQL, loopback only)
# Backend MySQL as root (socket auth): sudo mysql
...

First boot also writes two MySQL option files, /root/.shardingsphere-app.cnf and /root/.shardingsphere-admin.cnf (mode 0600). They hold the user, password, host, port and ssl-mode=REQUIRED, so the commands below log in without a password on the command line.

Step 3: Confirm the proxy and MySQL are running

systemctl is-active mysql.service shardingsphere-proxy.service
ls /opt/shardingsphere-proxy/lib/shardingsphere-proxy-bootstrap-*.jar /opt/shardingsphere-proxy/ext-lib/
mysqld --version
sudo ss -Hltn

active
active
/opt/shardingsphere-proxy/lib/shardingsphere-proxy-bootstrap-5.5.3.jar

/opt/shardingsphere-proxy/ext-lib/:
mysql-connector-j-26.7.0.jar
/usr/sbin/mysqld  Ver 8.0.46-0ubuntu0.24.04.4 for Linux on x86_64 ((Ubuntu))
LISTEN 0      4096        127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      151             127.0.0.1:3306 0.0.0.0:*
LISTEN 0      4096              0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096           127.0.0.54:53   0.0.0.0:*
LISTEN 0      1024   [::ffff:127.0.0.1]:3307       *:*
LISTEN 0      4096                 [::]:22      [::]:*

The proxy listens on 127.0.0.1:3307 (shown in its IPv4-mapped IPv6 form) and MySQL on 127.0.0.1:3306; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub.

Step 4: Connect through the proxy

Log in as app and check what it can see and how it is connected:

sudo mysql --defaults-extra-file=/root/.shardingsphere-app.cnf -e 'SHOW DATABASES; SELECT CURRENT_USER();'
sudo mysql --defaults-extra-file=/root/.shardingsphere-app.cnf -e 'status' | grep -E 'SSL|Server version|TCP port'

Database
sharding_db
current_user()
app@%
SSL:            Cipher in use is TLS_AES_256_GCM_SHA384
Server version:     5.7.22-ShardingSphere-Proxy 5.5.3 Source distribution
TCP port:       3307

app sees only the logical database sharding_db, and the session is encrypted. The version string is what the proxy reports to MySQL clients.

Step 5: Watch the proxy shard your data

Insert four orders through the proxy and read them back as if t_order were one table:

APP="sudo mysql --defaults-extra-file=/root/.shardingsphere-app.cnf sharding_db"
$APP -e "INSERT INTO t_order (user_id, status) VALUES (1,'NEW'),(2,'NEW'),(3,'PAID'),(4,'PAID');"
$APP -e "SELECT user_id, status FROM t_order ORDER BY user_id;"
$APP -e "PREVIEW SELECT * FROM t_order WHERE user_id = 3;"

user_id status
1   NEW
2   NEW
3   PAID
4   PAID
data_source_name    actual_sql
demo_ds_1   SELECT * FROM t_order_0 WHERE user_id = 3 UNION ALL SELECT * FROM t_order_1 WHERE user_id = 3

order_id is generated by the proxy (Snowflake). The rule sends each row to schema demo_ds_<user_id % 2> and table t_order_<order_id % 2>. PREVIEW (DistSQL) shows how the proxy routes and rewrites a statement without running it. Look at the backend directly as MySQL root to see where the rows landed, then delete the demo rows:

for db in demo_ds_0 demo_ds_1; do for t in t_order_0 t_order_1; do printf '%s.%s rows: ' "$db" "$t"; sudo mysql -N -e "SELECT COUNT(*) FROM $db.$t"; done; done
sudo mysql --defaults-extra-file=/root/.shardingsphere-app.cnf sharding_db -e "DELETE FROM t_order WHERE status IN ('NEW','PAID');"

demo_ds_0.t_order_0 rows: 0
demo_ds_0.t_order_1 rows: 2
demo_ds_1.t_order_0 rows: 2
demo_ds_1.t_order_1 rows: 0

The exact split between t_order_0 and t_order_1 depends on the generated order IDs.

Step 6: Inspect the rules with DistSQL as admin

admin manages the proxy with DistSQL. It is accepted only from the instance itself:

ADMIN="sudo mysql --defaults-extra-file=/root/.shardingsphere-admin.cnf"
$ADMIN -e "SHOW SHARDING TABLE RULES FROM sharding_db\G" | grep -E 'table:|actual_data_nodes|sharding_column|algorithm_props|key_generator_type'
$ADMIN -e "SHOW STORAGE UNITS FROM sharding_db;" | cut -f1-5
$ADMIN -e "SHOW DIST VARIABLE WHERE name = proxy_frontend_ssl_enabled;"

                            table: t_order
                actual_data_nodes: demo_ds_${0..1}.t_order_${0..1}
         database_sharding_column: user_id
database_sharding_algorithm_props: {"algorithm-expression":"demo_ds_${user_id % 2}"}
            table_sharding_column: order_id
   table_sharding_algorithm_props: {"algorithm-expression":"t_order_${order_id % 2}"}
               key_generator_type: SNOWFLAKE
name    type    host    port    db
demo_ds_0   MySQL   127.0.0.1   3306    demo_ds_0
demo_ds_1   MySQL   127.0.0.1   3306    demo_ds_1
variable_name   variable_value
proxy_frontend_ssl_enabled  true

The proxy runs in Standalone mode, and the YAML files in /opt/shardingsphere-proxy/conf are its configuration. Rules you change with DistSQL apply straight away but are not written back to those files, so a restart returns to the YAML. Make lasting changes in the YAML files (see Maintenance).

Step 7: Connect from your application

The proxy and MySQL accept connections on localhost only. Two ways to reach the proxy:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 3307:127.0.0.1:3307 ubuntu@<public-ip>, then connect any MySQL client or driver to 127.0.0.1:3307 as app with TLS required.
  • Open it to your application network. Set the listener in /etc/default/shardingsphere-proxy to this instance's private IP (or 0.0.0.0), restart the proxy, then add an inbound rule for 3307 to the instance's security group from your application subnet only (never 0.0.0.0/0). Use the app user and require TLS in the client. The admin user is refused from any address other than the instance itself. MySQL stays on localhost behind the proxy.
sudo sed -i 's/^SS_BIND_ADDRESS=.*/SS_BIND_ADDRESS=0.0.0.0/' /etc/default/shardingsphere-proxy
sudo systemctl restart shardingsphere-proxy.service

Client examples through the proxy:

mysql -h <private-ip> -P 3307 -u app -p --ssl-mode=REQUIRED sharding_db
jdbc:mysql://<private-ip>:3307/sharding_db?sslMode=REQUIRED&user=app&password=<SHARDINGSPHERE_APP_PASSWORD>

The proxy's certificate is self-signed and is created again each time the proxy starts, so use REQUIRED (encryption) rather than certificate verification (VERIFY_CA or VERIFY_IDENTITY). If you need verified TLS from remote clients, put the proxy behind a TLS terminator that holds your own certificate, or keep using an SSH tunnel.

Step 8: Check the security controls

Every service carries the metadata block, the proxy runs sandboxed, the sample root/root login is refused, and the MySQL accounts are local only:

systemctl show -p IPAddressDeny mysql.service shardingsphere-proxy.service apache-shardingsphere-firstboot.service
systemctl show -p User -p NoNewPrivileges -p ProtectSystem -p ProtectHome -p PrivateTmp -p CapabilityBoundingSet shardingsphere-proxy.service
OUT="$(MYSQL_PWD=root mysql -h127.0.0.1 -P3307 -uroot -e 'SELECT 1' 2>&1 || true)"; echo "$OUT"
sudo mysql -N -e "SELECT user, host, plugin FROM mysql.user WHERE user IN ('root','ssuser','debian-sys-maint') ORDER BY user;"

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
CapabilityBoundingSet=
User=shardingsphere
PrivateTmp=yes
ProtectHome=yes
ProtectSystem=strict
NoNewPrivileges=yes
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
debian-sys-maint    localhost   caching_sha2_password
root    localhost   auth_socket
ssuser  127.0.0.1   caching_sha2_password

Maintenance

  • Configuration files. /opt/shardingsphere-proxy/conf/global.yaml holds the proxy users, privileges and properties; database-sharding.yaml holds the sharding_db data sources and sharding rule. Both are owned by root and readable by the shardingsphere group only. Edit them with sudo, then run sudo systemctl restart shardingsphere-proxy.service. Add another logical database by creating another database-<name>.yaml in the same directory (the commented samples there show read/write splitting, encryption, masking and shadow rules).
  • Change a proxy password. Edit the user's password: line in global.yaml, restart the proxy, then update the matching option file (/root/.shardingsphere-app.cnf or /root/.shardingsphere-admin.cnf), the credentials file and your secrets manager.
  • Change the backend ssuser password. Run sudo mysql and enter ALTER USER 'ssuser'@'127.0.0.1' IDENTIFIED BY '<new-password>'; at the mysql> prompt (typed at the prompt, never on a command line), put the same password in both password: lines of database-sharding.yaml, and restart the proxy.
  • Use your own MySQL servers. Replace the url, username and password of each data source in database-sharding.yaml (for example with Amazon RDS for MySQL endpoints, keeping sslMode=REQUIRED), create the physical tables on those servers, and restart the proxy. The local MySQL can then be stopped and disabled.
  • Add a sharded table. Create its physical tables in every backend schema, add the table to the rules section of database-sharding.yaml, and restart the proxy.
  • Java heap. The proxy's start script sizes the heap at half of the instance memory. To cap it, add for example -Xmx2g to CGROUP_MEM_OPTS in /etc/default/shardingsphere-proxy and restart the proxy.
  • Backups. Take EBS snapshots of the data volume, or dump the backend schemas: sudo mysqldump --databases demo_ds_0 demo_ds_1 > backend.sql.
  • Updates. sudo apt update && sudo apt upgrade installs Ubuntu security updates, including MySQL 8.0 and OpenJDK 17. The proxy is the Apache binary release in /opt/shardingsphere-proxy; to move to a newer ShardingSphere release, unpack it alongside, copy conf/ and ext-lib/ across and update shardingsphere-proxy.service.
  • Logs. journalctl -u shardingsphere-proxy.service (the proxy logs to the journal), /var/log/mysql/error.log and journalctl -u mysql.service; first boot logs to journalctl -u apache-shardingsphere-firstboot.service.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (Apache ShardingSphere Proxy 5.5.3 on Ubuntu 24.04) and what you were doing.

Apache ShardingSphere, ShardingSphere and Apache are trademarks of The Apache Software Foundation. MySQL is a trademark of Oracle Corporation and/or its affiliates. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.