Skip to main content

Start Three WeSQL-Server Containers in the Docker Network

In this tutorial, for simplicity, we will simulate three different WeSQL-Server nodes using containers (all running on a single server, each with a unique Docker network IP). Using the same approach, these WeSQL-Server nodes can also be deployed on three separate EC2 instances.

The three WeSQL-Server nodes include:

  • One data node: This node will provide MySQL read and write services.

  • Two logger nodes: These nodes do not provide data services, but function as logger nodes within the Raft algorithm. Their primary role is to facilitate the replication of binlogs. If the data node fails, the logger nodes will continue synchronizing the binlogs to object storage (e.g., Amazon S3), thereby ensuring data consistency. Since the logger nodes handle only logging, they require fewer resources compared to the data node.

Prerequisites​

Before we get started, let’s get a few prerequisites out of the way:

  1. Prepare an AWS S3 bucket for WeSQL backups and logging. You can apply a S3 Bucket for testing purpose.
  2. Install Docker.

Create A 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 wesql-network
docker network inspect wesql-network

Create Local Directory​

This guide uses Docker's volume mounting (mounting a directory from the host to a directory inside the container) as an example. Create directories for storing local data for each WeSQL-Server container. WeSQL will persist the binlog in these directories before uploading it to S3. Additionally, if the SSD cache feature is enabled, the WeSQL Data node will cache data read from S3 in these directories to optimize performance.

To create these directories, run the following command:

mkdir -p ~/wesql-local-dir-0 ~/wesql-local-dir-1 ~/wesql-local-dir-2

Create Environment File​

note

You can skip this step if you have already created the .env file in Tutorial 1.

In Docker, passing sensitive information (such as WESQL_OBJECTSTORE_ACCESS_KEY and WESQL_OBJECTSTORE_SECRET_KEY) directly through the -e environment variable in the command line can pose security risks, as these values may be recorded in command history, log files, or exposed in certain monitoring tools.

By storing the environment variables in an environment file, sensitive information is not directly exposed in the command line. You can create a .env file and then load the environment variables into the Docker container.

Create a .env file (for example, wesql.env) with the following content:

WESQL_OBJECTSTORE_ACCESS_KEY=your-access-key
WESQL_OBJECTSTORE_SECRET_KEY=your-secret-key
  • replace your-access-key and your-secret-key with your real AWS credentials.

Start The WeSQL-Server Data Node​

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 local directory is empty, it will pull the latest data from S3.

docker run -itd --name wesql-server-0 --net=wesql-network --ip=172.21.0.2 \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
  port=3306\n\
  log-bin=binlog\n\
  gtid_mode=ON\n\
  enforce_gtid_consistency=ON\n\
  log_slave_updates=ON\n\
  binlog_format=ROW\n\
  objectstore_provider=aws\n\
  objectstore_region=us-west-1\n\
  objectstore_bucket=wesql-storage\n\
  repo_objectstore_id=tutorial\n\
  branch_objectstore_id=main\n\
  smartengine_persistent_cache_size=1G" \
    -v ~/wesql-local-dir-0:/data/mysql \
    -e WESQL_DATA_DIR=/data/mysql/data \
    -e WESQL_LOG_DIR=/data/mysql/log \
    -e WESQL_CLUSTER_MEMBER=172.21.0.2:13306 \
    -e MYSQL_ROOT_PASSWORD=passwd \
    --env-file=./wesql.env \
    apecloud/wesql-server:8.0.35-0.1.0_beta2.37
  • replace us-west-1 with the real region of your S3 bucket.
  • replace wesql-storage with the real name of your S3 bucket.
  • set WESQL_OBJECTSTORE_ACCESS_KEY and WESQL_OBJECTSTORE_SECRET_KEY with your real AWS credentials.
  • replace passwd with the real password for the root user.

Each WeSQL server starts two ports: one is port 3306, which provides the MySQL service, and the other is port 13306, which is used for Raft protocol communication between WeSQL servers.

note

If you've already completed tutorial-1, then the S3 bucket is not empty and will have data. WeSQL allows database compute nodes to move between network environments, attach to the same S3, start up, and continue providing services with the data intact.

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_MEMBER 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-server-1 --net=wesql-network --ip=172.21.0.3 \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
  port=3306\n\
  log-bin=binlog\n\
  objectstore_provider=aws\n\
  objectstore_region=us-west-1\n\
  objectstore_bucket=wesql-storage\n\
  repo_objectstore_id=tutorial\n\
  branch_objectstore_id=main" \
    -v ~/wesql-local-dir-1:/data/mysql \
    -e WESQL_DATA_DIR=/data/mysql/data \
    -e WESQL_LOG_DIR=/data/mysql/log \
    -e WESQL_CLUSTER_MEMBER=172.21.0.3:13306 \
    -e WESQL_LOGGER_MODE=1 \
    -e MYSQL_ROOT_PASSWORD=passwd \
    --env-file=./wesql.env \
    apecloud/wesql-server:8.0.35-0.1.0_beta2.37
docker run -itd --name wesql-server-2 --net=wesql-network --ip=172.21.0.4 \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
  port=3306\n\
  log-bin=binlog\n\
  objectstore_provider=aws\n\
  objectstore_region=us-west-1\n\
  objectstore_bucket=wesql-storage\n\
  repo_objectstore_id=tutorial\n\
  branch_objectstore_id=main" \
    -v ~/wesql-local-dir-2:/data/mysql \
    -e WESQL_DATA_DIR=/data/mysql/data \
    -e WESQL_LOG_DIR=/data/mysql/log \
    -e WESQL_CLUSTER_MEMBER=172.21.0.4:13306 \
    -e WESQL_LOGGER_MODE=1 \
    -e MYSQL_ROOT_PASSWORD=passwd \
    --env-file=./wesql.env \
    apecloud/wesql-server:8.0.35-0.1.0_beta2.37

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=wesql-network --rm mysql mysql -h172.21.0.2 -uroot -ppasswd
  • replace passwd with the real password for the root user.

Wait for all three nodes in the Raft Group to complete initialization. You can check their status by querying the system tables through an SQL query:

mysql> SELECT * FROM INFORMATION_SCHEMA.WESQL_CLUSTER_HEALTH;
+-----------+------------------+----------+-----------+---------------+-----------------+
| SERVER_ID | IP_PORT          | ROLE     | CONNECTED | LOG_DELAY_NUM | APPLY_DELAY_NUM |
+-----------+------------------+----------+-----------+---------------+-----------------+
|         1 | 172.21.0.2:13306 | Leader   | YES       |             0 |               0 |
|         2 | 172.21.0.4:13306 | Follower | YES       |             0 |               0 |
|         3 | 172.21.0.3:13306 | Follower | YES       |             0 |               0 |
+-----------+------------------+----------+-----------+---------------+-----------------+
3 rows in set (0.00 sec)

Run sysbench​

Preparation​

note

If you've completed tutorial-1, the sysbench data is already written to S3. You can skip this step and proceed directly to the test.

First, create the necessary resources in MySQL for running sysbench: database, user, and the required permissions.

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

Next, prepare the data.

docker run --net=wesql-network  --rm \
apecloud/customsuites:latest \
sysbench \
--db-driver=mysql \
--report-interval=2 \
--tables=4 \
--table-size=250000 \
--threads=8 \
--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

Run The Test​

docker run --net=wesql-network --rm \
apecloud/customsuites:latest \
sysbench \
--db-driver=mysql \
--report-interval=2 \
--tables=4 \
--table-size=250000 \
--threads=8 \
--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