Clone A WeSQL-Server Container on Local Machine

Clone a new database from an existing source database, Once the new cloned database is started, read and write operations are allowed.

Currently, two types of data cloning methods are supported.

local clone, it will clone the source database from S3 object storage to local Minio object storage.
remote clone, it will clone the source database from S3 object storage or local Minio object storage to another S3 object storage.

1. local clone from s3

We will demonstrate how to clone a new WeSQL-Server database from S3. where the cloned database’s data is stored on a local Minio object storage.

This example clone source database from S3 bucketwesql-storage in region us-west-1 to local Minio object storage bucketwesql-storage. The source database references the database created from the guide Start A WeSQL-Server Container on Local Machine.

1.1 Create Minio Container on Local Machine

Create a Minio container on your local machine. The Minio container will be used as the local object storage for the cloned WeSQL Data Node. More information about Minio Object Storage for Container.

Create a directory for Minio object storage.

mkdir -p ~/minio/data

Start Minio container on your local machine.

docker run \
   --network host \
   -itd \
   --name minio \
   -v ~/minio/data:/data \
   -e "MINIO_ROOT_USER=minio123" \
   -e "MINIO_ROOT_PASSWORD=minio123" \
   quay.io/minio/minio server /data --console-address ":9001"

Install Minio client to create a bucket wesql-storage on Minio object storage. More information about Minio Client Quickstart Guide.

wget https://dl.min.io/client/mc/release/linux-amd64/mc
chmod +x mc
sudo mv mc /usr/local/bin/
mc alias set myminio http://127.0.0.1:9000 minio123 minio123
mc mb myminio/wesql-storage

1.2 Create Local Directory

Create a directory for storing local data for the cloned WeSQL Data Node. To create the directory, run the following command:

mkdir -p ~/wesql-local-clone-data-dir

1.3 Create Environment File

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

WESQL_OBJECTSTORE_ACCESS_KEY=minio123
WESQL_OBJECTSTORE_SECRET_KEY=minio123
WESQL_SOURCE_OBJECTSTORE_ACCESS_KEY=your-access-key
WESQL_SOURCE_OBJECTSTORE_SECRET_KEY=your-secret-key

replace your-access-key and your-secret-key with your real AWS credentials.
WESQL_SOURCE_OBJECTSTORE_ACCESS_KEY and WESQL_SOURCE_OBJECTSTORE_SECRET_KEY represents S3 access for the source database.
WESQL_OBJECTSTORE_ACCESS_KEY and WESQL_OBJECTSTORE_SECRET_KEY represents Minio access for the cloned database.

1.4 Clone and Start WeSQL-Server Data Node

In this example, when starting the data node for the first time, the cluster will automatically initialize. It will clone the source database from S3 in the us-west-1 to local Minio object storage.

docker run -itd --network host --name wesql-server-local-clone \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
  port=3406\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='minio'\n\
  objectstore_endpoint='http://127.0.0.1:9000'\n\
  objectstore_region=''\n\
  objectstore_bucket='wesql-storage'\n\
  repo_objectstore_id='tutorial'\n\
  branch_objectstore_id='main'\n\
  initialize_from_objectstore=true\n\
  initialize_smartengine_objectstore_data=true\n\
  initialize_objectstore_provider='aws'\n\
  initialize_objectstore_region='us-west-1'\n\
  initialize_objectstore_bucket='wesql-storage'\n\
  initialize_repo_objectstore_id='tutorial'\n\
  initialize_branch_objectstore_id='main'" \
    -v ~/wesql-local-clone-data-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:13406' \
    -e MYSQL_ROOT_PASSWORD=passwd \
    --env-file=./wesql-local.env \
    apecloud/wesql-server:8.0.35-0.1.0_beta2.37

replace us-west-1 with the real region of your source S3 bucket.
replace wesql-storage with the real name of your source 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-local.env with the real env file path.

1.5 Connecting to the new WeSQL data node

You can connect to the newly cloned WeSQL data node 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 -P 3406 -uroot -ppasswd

replace passwd with the real password for the root user.

2. remote clone from s3

We will demonstrate how to clone a new WeSQL-Server database from S3, where the cloned database’s data is stored on a different S3 bucket.

This example clone source database from S3 bucketwesql-storage in region us-west-1 to S3 bucket wesql-storage in region us-west-2. The source database references the database created from the guide Start A WeSQL-Server Container on Local Machine.

2.1 Prepare S3

Prepare an AWS S3 bucket wesql-storage in region us-west-2 for the cloned WeSQL.

2.2 Create Local Directory

Create a directory for storing local data for the cloned WeSQL Data Node. To create the directory, run the following command:

mkdir -p ~/wesql-remote-clone-data-dir

2.3 Create Environment File

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

WESQL_SOURCE_OBJECTSTORE_ACCESS_KEY=your-access-key
WESQL_SOURCE_OBJECTSTORE_SECRET_KEY=your-secret-key
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.
WESQL_SOURCE_OBJECTSTORE_ACCESS_KEY and WESQL_SOURCE_OBJECTSTORE_SECRET_KEY represents S3 access for the source database.
WESQL_OBJECTSTORE_ACCESS_KEY and WESQL_OBJECTSTORE_SECRET_KEY represents S3 access for the cloned database.

2.4 Clone and Start WeSQL-Server Data Node

In this example, when starting the data node for the first time, the cluster will automatically initialize. It will clone the source database from S3 in the us-west-1 to S3 in the us-west-2.

docker run -itd --network host --name wesql-server-remote-clone \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
  port=3506\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-2'\n\
  objectstore_bucket='wesql-storage'\n\
  repo_objectstore_id='tutorial'\n\
  branch_objectstore_id='main'\n\
  initialize_from_objectstore=true\n\
  initialize_smartengine_objectstore_data=true\n\
  initialize_objectstore_provider='aws'\n\
  initialize_objectstore_region='us-west-1'\n\
  initialize_objectstore_bucket='wesql-storage'\n\
  initialize_repo_objectstore_id='tutorial'\n\
  initialize_branch_objectstore_id='main'" \
    -v ~/wesql-remote-clone-data-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:13506' \
    -e MYSQL_ROOT_PASSWORD=passwd \
    --env-file=./wesql-remote.env \
    apecloud/wesql-server:8.0.35-0.1.0_beta2.37

replace us-west-1 and us-west-2 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-remote.env with the real env file path.

2.5 Connecting to the new WeSQL data node

You can connect to the newly cloned WeSQL data node 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 -P 3506 -uroot -ppasswd

replace passwd with the real password for the root user.

3. remote clone from local

We will demonstrate how to clone a new WeSQL-Server database from local object storage, where the cloned database’s data is stored on a S3 object storage.

This guide clone source database from Minio object storage bucket wesql-storage to S3 bucket wesql-storage-1 in region us-west-2. The source database references the database created local clone from s3.

3.1 Prepare S3

Prepare an AWS S3 bucket wesql-storage-1 in region us-west-1 for the cloned WeSQL.

3.2 Create Local Directory

Create a directory for storing local data for the cloned WeSQL Data Node.

mkdir -p ~/wesql-remote-local-clone-data-dir

3.3 Create Environment File

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

WESQL_OBJECTSTORE_ACCESS_KEY=your-access-key
WESQL_OBJECTSTORE_SECRET_KEY=your-secret-key
WESQL_SOURCE_OBJECTSTORE_ACCESS_KEY=minio123
WESQL_SOURCE_OBJECTSTORE_SECRET_KEY=minio123

replace your-access-key and your-secret-key with your real AWS credentials.
WESQL_SOURCE_OBJECTSTORE_ACCESS_KEY and WESQL_SOURCE_OBJECTSTORE_SECRET_KEY represents Minio access for the source database.
WESQL_OBJECTSTORE_ACCESS_KEY and WESQL_OBJECTSTORE_SECRET_KEY represents S3 access for the cloned database.

3.4 Clone and Start WeSQL-Server Data Node

In this example, when starting the data node for the first time, the cluster will automatically initialize. It will clone the source database from local Minio object storage bucket wesql-storage to S3 in the us-west-1.

docker run -itd --network host --name wesql-server-remote-local-clone \
  -e MYSQL_CUSTOM_CONFIG="[mysqld]\n\
  port=3606\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-1'\n\
  repo_objectstore_id='tutorial'\n\
  branch_objectstore_id='main'\n\
  initialize_from_objectstore=true\n\
  initialize_smartengine_objectstore_data=true\n\
  initialize_objectstore_provider='minio'\n\
  initialize_objectstore_endpoint='http://127.0.0.1:9000'\n\
  initialize_objectstore_region=''\n\
  initialize_objectstore_bucket='wesql-storage'\n\
  initialize_repo_objectstore_id='tutorial'\n\
  initialize_branch_objectstore_id='main'" \
    -v ~/wesql-remote-local-clone-data-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:13606' \
    -e MYSQL_ROOT_PASSWORD=passwd \
    --env-file=./wesql-remote-local.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-1 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-remote-local.env with the real env file path.

3.5 Connecting to the new WeSQL data node

You can connect to the newly cloned WeSQL data node 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 -P 3606 -uroot -ppasswd

replace passwd with the real password for the root user.

1. local clone from s3​

1.1 Create Minio Container on Local Machine​

1.2 Create Local Directory​

1.3 Create Environment File​

1.4 Clone and Start WeSQL-Server Data Node​

1.5 Connecting to the new WeSQL data node​

2. remote clone from s3​

2.1 Prepare S3​

2.2 Create Local Directory​

2.3 Create Environment File​

2.4 Clone and Start WeSQL-Server Data Node​

2.5 Connecting to the new WeSQL data node​

3. remote clone from local​

3.1 Prepare S3​

3.2 Create Local Directory​

3.3 Create Environment File​

3.4 Clone and Start WeSQL-Server Data Node​

3.5 Connecting to the new WeSQL data node​

1. local clone from s3

1.1 Create Minio Container on Local Machine

1.2 Create Local Directory

1.3 Create Environment File

1.4 Clone and Start WeSQL-Server Data Node

1.5 Connecting to the new WeSQL data node

2. remote clone from s3

2.1 Prepare S3

2.2 Create Local Directory

2.3 Create Environment File

2.4 Clone and Start WeSQL-Server Data Node

2.5 Connecting to the new WeSQL data node

3. remote clone from local

3.1 Prepare S3

3.2 Create Local Directory

3.3 Create Environment File

3.4 Clone and Start WeSQL-Server Data Node

3.5 Connecting to the new WeSQL data node