Start A WeSQL-Server Container on Local Machine
Prerequisites
Before we get started, let’s get a few prerequisites out of the way:
- Prepare an AWS S3 bucket for WeSQL backups and logging. You can apply a S3 Bucket for testing purpose.
- 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
andyour-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_beta3.38
- 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 idmain
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