Deploying WeSQL Serverless Cluster in Docker

Container Usage Introduction

Explanation of environment variables:

The WeSQL Docker container receives configuration and parameter information through environment variables. The following environment variables are supported:

WESQL_CLUSTER_ID: Cluster ID, formatted as a number. Optional, defaults to 1.
WESQL_CLUSTER_MEMBERS: Cluster member information in the following format: $host1:$raft_port1;$host2:$raft_port2;$host3:$raft_port3.
WESQL_CLUSTER_MEMBER_INDEX: The current node’s position in the cluster member information, starting from 0.
WESQL_CLUSTER_MEMBERS_PORTS: The SQL ports of the cluster members, optional. Each node defaults to port 3306. Format: $port1;$port2;$port3.
WESQL_DATA_VOLUME: Data storage directory, defaults to /data/mysql. Data is stored in $WESQL_DATA_VOLUME/data.
WESQL_LOG_DIR: Log storage directory, defaults to /data/mysql/log.
WESQL_LOGGER_MODE: Whether the node is a LOGGER node. Set to 0 or 1, defaults to 0.
WESQL_OBJECTSTORE_ACCESS_KEY: Object store access key (AK).
WESQL_OBJECTSTORE_SECRET_KEY: Object store secret key (SK).
WESQL_LOG_SIZE_THRESHOLD: Total size of database background logs. When this size is exceeded, logs are automatically deleted.
MYSQL_DATABASE: Name of the new database, optional.
MYSQL_USER/MYSQL_PASSWORD: New username/password, optional.
MYSQL_ROOT_HOST: The access range for the root user, defaults to %.
MYSQL_ROOT_PASSWORD: Initial password for the root user.
MYSQL_ALLOW_EMPTY_PASSWORD: Allows the root password to be empty. If MYSQL_ROOT_PASSWORD is not set or is empty, you must enable MYSQL_ALLOW_EMPTY_PASSWORD.
MYSQL_CUSTOM_CONFIG: General parameters, optional.

Database Parameter Configuration

Kernel parameters can be configured in two ways, and custom configurations will override default parameters:

Container environment variables: During container startup, configuration files are automatically generated based on the environment variables and loaded into the /etc/mysql/auto.conf.d/ directory.
- MYSQL_CUSTOM_CONFIG: Used to configure general parameters, optional.
Configuration file directory mapping: Map custom configuration files to the /etc/mysql/conf.d/ directory in the container. When the container starts, it automatically loads configuration files from this directory. If .cnf files are present in /etc/mysql/conf.d/, the container's environment variables related to parameters will be overridden.

Deploying WeSQL Cluster in Docker

Create Docker Network

If you plan to start multiple containers on the same host as multiple nodes in the WeSQL cluster, it's recommended to create a Docker network for inter-node communication. For this, you can refer to the docker network create command documentation and choose a network configuration other than bridge to meet your network needs.

You can use the docker network inspect command to check the subnet and assign IPs within the subnet for each node container. In the example provided in this document, the subnet is 172.21.0.0/16, and the selected IPs are 172.21.0.2, 172.21.0.3, and 172.21.0.4.

docker network create -d bridge --subnet=172.21.0.0/16 raftnet
docker network inspect raftnet

Network Port Assignment

You need to assign an SQL service port number to each node for external access. In the example provided, multiple container nodes are started on the same host with port numbers 3306, 3307, and 3308 respectively. You can also choose other Docker features to map external access ports for each container.

Additionally, each node needs to be assigned a Raft communication port for internal communication between nodes. In this example, port 13306 is uniformly used.

Create Data Storage Directory/Volume

This document uses local disk directory mounting as an example, but it's more recommended to use Docker’s volume feature for persistent storage to ensure data reliability and portability.

If necessary, you can use the chmod command to modify the permissions of the mount directory to ensure the container has proper access to the data directory.

mkdir -p ~/data/d1 ~/data/d2 ~/data/d3

Start Data Nodes

In this example, the configuration parameter file is generated using the container environment variable MYSQL_CUSTOM_CONFIG. For specific configuration parameters, refer to WeSQL Documentation.

When starting the data node for the first time, the cluster will automatically initialize. If the data storage directory/volume is empty, it will pull the latest data from S3.

docker run -itd --name wesql-data1 --net=raftnet --ip=172.21.0.2 \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
port=3306\n\
log-bin=binlog\n\
objectstore_provider='aws'\n\
objectstore_region='cn-northwest-1'\n\
objectstore_bucket='wesql-cluster-test'\n\
datadir=/data/mysql/data\n\
log-error=/data/mysql/log/mysqld-error.log" \
  -v ~/data/d1:/data/mysql \
  -e WESQL_CLUSTER_MEMBERS='172.21.0.2:13306;172.21.0.3:13306;172.21.0.4:13306' \
  -e WESQL_CLUSTER_MEMBER_INDEX=0 \
  -e WESQL_OBJECTSTORE_ACCESS_KEY='********************' \
  -e WESQL_OBJECTSTORE_SECRET_KEY='********************' \
  -e MYSQL_ROOT_PASSWORD=passwd \
  apecloud-registry.cn-zhangjiakou.cr.aliyuncs.com/apecloud/wesql-server:8.0.35-6.alpha9.20240904.ge7e5076.24

你可以看到S3 bucket里出现了一些对象

$ aws s3 ls s3://wesql-cluster-test --region cn-northwest-1
                           PRE binlog/
2024-09-18 10:38:39    2097152 17179869184
2024-09-18 16:27:18    2097152 21474836480
2024-09-18 10:35:16    2097152 4294967296
2024-09-18 10:36:10    2097152 4294967297
2024-09-18 10:36:11    2097152 4294967298
2024-09-18 10:36:13    2097152 4294967299
2024-09-18 10:36:07    2097152 8589934592
2024-09-18 10:38:36    2097152 8589934593
2024-09-18 10:38:34    2097152 8589934594
2024-09-18 10:38:30    2097152 8589934595
2024-09-18 10:37:20         81 consistent_snapshot.index
2024-09-18 10:37:17         22 innodb_archive.index
2024-09-18 10:36:46   82053120 innodb_archive_000001.tar
2024-09-18 10:37:19         18 se_archive.index
2024-09-18 10:37:18    2119680 se_archive_000001.tar

或者在S3 dashboard里浏览：

Start Logger Nodes

The logger node must be started after the successful startup of the data node for the first time. When starting the logger node for the first time, it will automatically join the cluster. Ensure that the WESQL_CLUSTER_MEMBERS environment variable includes all nodes in the cluster, including the current node.

To scale logger nodes, simply start new logger nodes, and expansion is completed. If the data storage directory is empty, the latest logs will be pulled from S3.

docker run -itd --name wesql-logger1 --net=raftnet --ip=172.21.0.3 \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
port=3306\n\
log-bin=binlog\n\
objectstore_provider='aws'\n\
objectstore_region='cn-northwest-1'\n\
objectstore_bucket='wesql-cluster-test'\n\
datadir=/data/mysql/data\n\
log-error=/data/mysql/log/mysqld-error.log" \
  -v ~/data/d2:/data/mysql \
  -e WESQL_CLUSTER_MEMBERS='172.21.0.2:13306;172.21.0.3:13306;172.21.0.4:13306' \
  -e WESQL_CLUSTER_MEMBER_INDEX=1 \
  -e WESQL_LOGGER_MODE=1 \
  -e WESQL_OBJECTSTORE_ACCESS_KEY='********************' \
  -e WESQL_OBJECTSTORE_SECRET_KEY='********************' \
  -e MYSQL_ROOT_PASSWORD=passwd \
  apecloud-registry.cn-zhangjiakou.cr.aliyuncs.com/apecloud/wesql-server:8.0.35-6.alpha9.20240904.ge7e5076.24

docker run -itd --name wesql-logger2 --net=raftnet --ip=172.21.0.4 \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
log-bin=binlog\n\
port=3306\n\
objectstore_provider='aws'\n\
objectstore_region='cn-northwest-1'\n\
objectstore_bucket='wesql-cluster-test'\n\
datadir=/data/mysql/data\n\
log-error=/data/mysql/log/mysqld-error.log" \
  -v ~/data/d3:/data/mysql \
  -e WESQL_CLUSTER_MEMBERS='172.21.0.2:13306;172.21.0.3:13306;172.21.0.4:13306' \
  -e WESQL_CLUSTER_MEMBER_INDEX=2 \
  -e WESQL_LOGGER_MODE=1 \
  -e WESQL_OBJECTSTORE_ACCESS_KEY='********************' \
  -e WESQL_OBJECTSTORE_SECRET_KEY='********************' \
  -e MYSQL_ROOT_PASSWORD=passwd \
  apecloud-registry.cn-zhangjiakou.cr.aliyuncs.com/apecloud/wesql-server:8.0.35-6.alpha9.20240904.ge7e5076.24

Connecting to the Cluster

You can connect to the WeSQL cluster using a MySQL client or driver from any environment that can access the SQL listening port.

docker run -it --net=raftnet --rm mysql mysql -h172.21.0.2 -uroot -ppasswd

Run sysbench

首先在MySQL里创建运行sysbench需要的资源：库、用户、必要的权限

mysql> CREATE SCHEMA sbtest;
mysql> CREATE USER sbtest@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON sbtest.* to sbtest@'%';

然后，准备数据

docker run --net=raftnet --rm \
apecloud/customsuites:latest \
sysbench \
--db-driver=mysql \
--report-interval=2 \
--tables=4 \
--table-size=250000 \
--threads=4 \
--time=300 \
--mysql-host=172.21.0.2 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
/usr/share/sysbench/oltp_read_write.lua \
prepare 

运行测试

docker run --net=raftnet --rm \
apecloud/customsuites:latest \
sysbench \
--db-driver=mysql \
--report-interval=2 \
--tables=4 \
--table-size=250000 \
--threads=4 \
--time=300 \
--mysql-host=172.21.0.2 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
/usr/share/sysbench/oltp_read_write.lua \
run

Stopping the Cluster

To stop the cluster, you can use the standard Docker commands to stop the running containers.

Deploying WeSQL Serverless Cluster in Docker

Container Usage Introduction​

Explanation of environment variables:​

Database Parameter Configuration​

Deploying WeSQL Cluster in Docker​

Create Docker Network​

Network Port Assignment​

Create Data Storage Directory/Volume​

Start Data Nodes​

Start Logger Nodes​

Connecting to the Cluster​

Run sysbench​

Stopping the Cluster​