Start A WeSQL-Server Container on Local Machine

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 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 a directory for storing local data for the WeSQL Data Node. The WeSQL Data Node will persist the binlog in this directory before uploading it to S3. Additionally, data read from S3 will also be cached in this directory to optimize performance.

To create the directory, run the following command:

mkdir -p ~/wesql-local-dir

Create Environment File

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 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 --network host --name wesql-server \
  -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:/data/mysql \
    -e WESQL_DATA_DIR=/data/mysql/data \
    -e WESQL_LOG_DIR=/data/mysql/log \
    -e WESQL_CLUSTER_MEMBER=127.0.0.1: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.
• replace the repo id tutorial and branch id main with the real one.
• replace passwd with the real password for the root user.
• replace ./wesql.env with the real env file path.

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.

You can see that several objects are generated in the S3 bucket.

$ aws s3 ls s3://wesql-storage/tutorial/main/

                           PRE binlog/
                           PRE cluster_mgmt/
                           PRE consistent_snapshot/
                           PRE smartengine/
2024-10-21 09:14:29          0
2024-10-21 09:14:29          0 data.lock

Or browse through the S3 dashboard:

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 --network host --rm mysql mysql -h127.0.0.1 -uroot -ppasswd

• replace passwd with the real password for the root user.

Run sysbench

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 --network host --rm \
apecloud/customsuites:latest \
sysbench \
--db-driver=mysql \
--report-interval=2 \
--tables=4 \
--table-size=250000 \
--threads=8 \
--time=300 \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
/usr/share/sysbench/oltp_read_write.lua \
prepare 

Run the test

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