Destination SingleStore #
The extracted replicant-cli
will be referred to as the $REPLICANT_HOME
directory in the proceeding steps.
Prerequisites #
You must have a user configured in SingleStore for replication with SELECT
, INSERT
, UPDATE
, DELETE
, CREATE
, DROP
permissions on application databases.
If SingleStore user does not have create database permission then you must create a database named io_replicate
and provide SELECT
, INSERT
, UPDATE
, DELETE
, CREATE
, DROP
privileges to SingleStore user.
I. Setup Connection Configuration #
-
From
$REPLICANT_HOME
, navigate to the sample SingleStore connection configuration file:vi conf/conn/singlestore.yaml
-
For connecting to the SingleStore server, you can choose between two methods for an authenticated connection:
Connect with username and password #
For connecting to SingleStore using via basic username and password authentication, you have the following two options:
You can specify your credentials in plain form in the connection configuration file like the folowing sample:
type: SINGLESTORE host: HOSTNAME port: PORT_NUMBER username: 'USERNAME' password: 'PASSWORD' max-connections: 30 max-retries: 10 retry-wait-duration-ms: 1000
Replace the following:
HOSTNAME
: hostname of the SingleStore serverPORT_NUMBER
: port number of the SingleStore serverUSERNAME
: the SingleStore usernamePASSWORD
: the password associated withUSERNAME
If you store your connection credentials in AWS Secrets Manager, you can tell Replicant to retrieve them. For more information, see Retrieve credentials from AWS Secrets Manager.Connect using SSL #
To connect to SingleStore using SSL, follow these steps:
-
Configure the server-side requirements by following the instructions in Server Configuration for Secure Client and Intra-Cluster Connections.
-
Specify the SSL parameters to Replicant in the
ssl
section of the connection configuration file in the following format:ssl: enable: true root-cert: "PATH_TO_CA_CERTIFICATE_FILE" hostname-verification: {true|false} trust-store: path: PATH_TO_CA_TRUSTSTORE password: TRUSTSTORE_PASSWORD key-store: path: PATH_TO_KEYSTORE password: KEYSTORE_PASSWORD ssl-key-password: KEYSTORE_CERT_PASSWORD
In the preceding configuration:
-
root-cert
holds the full path to your SSL CA certificate file—for example,"/home/alex/workspace/ca-cert.pem"
. Keep in mind that thetrust-store
configuration overridesroot-cert
. -
hostname-verification
enables hostname verification against the server identity according to the specification in the server’s certificate. Defaults totrue
. -
trust-store
holds the SSL CA certificate that the client uses to authenticate the server. This configuration overridesroot-cert
.Replace
PATH_TO_CA_TRUSTSTORE
andTRUSTSTORE_PASSWORD
with the path to the TrustStore and the TrustStore password respectively. -
The server uses
key-store
to authenticate the client. ReplacePATH_TO_KEYSTORE
andKEYSTORE_PASSWORD
with the path to the KeyStore and the KeyStore password respectively. -
As an optional parameter,
ssl-key-password
holds the password of the certificate inside the KeyStore.
-
II. Setup Applier Configuration #
Edit the applier configurations if required.
-
From
$REPLICANT_HOME
, navigate to the sample Applier Configuration File:vi conf/dst/singlestore.yaml
-
The configuration file has two parts:
- Parameters related to snapshot mode.
- Parameters related to realtime mode.
Parameters related to snapshot mode #
For snapshot mode, notice the sample configuration below:
snapshot: table-store: {ROW|COLUMN} per-table-config: - catalog: tpch tables: singlestore_orders: table-store: COLUMN sort-key: [singlestore_orderkey] shard-key: [c2]
In the sample above, notice the following:
-
You can provide SingleStore LOAD configuration string by setting the
native-load-configs
parameter. These configurations will be appended to the generated LOAD SQL command. For example:native-load-configs: "ERRORS HANDLE 'orders_errors'"
-
You can specify the table store to use by setting the
table-store
parameter to any of the following two values:ROW
COLUMN
-
Replace
singlestore_orderkey
with a list of columns to be created as thesort-key
. -
Replace
c2
with a list of columns to be created as theshard-key
.
Parameters related to realtime mode #
For realtime mode, notice the following sample:
realtime: threads: 8 txn-size-rows: 10000 batch-size-rows: 1000 _oper-queue-size-rows: 20000 skip-tables-on-failures : false replay-shard-key-update-as-delete-insert: true retry-failed-txn-idempotently: true perTableConfig: - schema: tpch tables: CUSTOMER: skip-upto-cursor: '{"extractorId":0,"timestamp":1599201466000,"log":"mariadb-bin.000200","position":36574666,"logSeqNum":1000,"slaveServerId":1,"v":1}'
-
Below is a sample applier file with commonly used configuration parameters:
snapshot: threads: 32 batch-size-rows: 10_000 txn-size-rows: 1_000_000 bulk-load: enable: true type: FILE table-store: ROW per-table-config: - catalog: tpch tables: singlestore_orders: table-store: COLUMN sort-key: [singlestore_orderkey] shard-key: [c2] partsupp: table-type: REFERENCE table-store: ROW partsupp_macro_delta: table-store: COLUMN sort-key: [partkey, suppkey] realtime: threads: 8 txn-size-rows: 10000 batch-size-rows: 1000 _oper-queue-size-rows: 20000 replay-shard-key-update-as-delete-insert: true retry-failed-txn-idempotently: true
For a detailed explanation of configuration parameters in the Applier file, read: Applier Reference