MySQL
Zenskar's MySQL connector offers the following features:
- Multiple methods of syncing data, including Change Data Capture (CDC) using the binlog.
- All available sync modes.
- Checkpointing and chunking of database reads for reliable replication at any table size with
🐕🦺 Setup guide
⚙️ Step 1: users and permissions
Create a dedicated read-only database user for replicating data. Alternatively, you may use an existing MySQL user in your database.
CREATE USER 'zenskar'@'%' IDENTIFIED BY 'your_password_here';
For CDC replication method, SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT
permissions are required.
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zenskar'@'%';
For STANDARD replication method (not recommended), only the SELECT
permission is required.
GRANT SELECT ON <database name>.* TO 'zenskar'@'%';
set global slave_net_timeout = 120;set global thread_pool_idle_timeout = 120;
⚙️ Step 2: enable binary logging on your MySQL server
You must enable binary logging for MySQL replication using CDC. Most cloud providers (AWS, GCP, etc.) provide easy one-click options for enabling the binlog on your source MySQL database.
If you are self-managing your MySQL server, configure your MySQL server configuration file with the following properties:
Configuring MySQL server config files to enable binlog
server-id = 223344
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 864000
- server-id : The value for the server-id must be unique for each server and replication client in the MySQL cluster. The
server-id
should be a non-zero value. If theserver-id
is already set to a non-zero value, you don't need to make any change. You can set theserver-id
to any value between 1 and 4294967295. For more information refer MySQL documentation - log_bin : The value of
log_bin
is the base name of the sequence of binlog files. If the log_bin is already set, you don't need to make any change. For more information refer MySQL documentation - binlog_format : The
binlog_format
must be set toROW
. For more information refer MySQL documentation - binlog_row_image : The
binlog_row_image
must be set toFULL
. It determines how row images are written to the binary log. For more information refer MySQL documentation - binlog_expire_logs_seconds : This is the number of seconds for automatic binlog file removal. We recommend 864000 seconds (10 days) so that in case of a failure in sync or if the sync is paused, we still have some bandwidth to start from the last point in incremental sync. We also recommend setting frequent syncs for CDC.
⚙️ Step 3: create a MySQL data source in Zenskar
Set up data source
- Log into your Zenskar account.
- In the left navigation bar, click Metering > Data Sources. In the top-right corner, click + ADD DATA SOURCE.
- In the Set Up Source section of the Add New Data Source page, enter a name for the Google Sheets data source connection.
- Select MySQL from the Source Type dropdown.
Configure data source
In the Source Config section of the Add New Data Source page, add the following details:
- SSL Connection: select Yes or No depending on whether you would like Zenskar to establish an SSL connection with the data source
- Host: database hosting server
- Port: the port at which MySQL listens for incoming requests
- Database: the database name
- Password: the database password
- SSL Modes: select from
prefer
,require
,verify-ca
, andverify-full
. Refer the section on SSL modes for more details. - Username: the read-only database user
- Refer the section on SSH tunneling for details on all SSH-related configuration parameters.
- Replication Method: select from Standard and Logical Replication (CDC). Refer the section on MySQL replication modes for more details.
Note
You must allow inbound traffic from Zenskar IP addresses.
📖 MySQL Replication Modes
Change Data Capture (CDC)
Zenskar uses logical replication of the MySQL binlog to incrementally capture deletes. We recommend configure your MySQL source with CDC whenever possible, as it provides:
- A record of deletions, if needed.
- Scalable replication to large tables (1 TB and more).
- A reliable cursor not reliant on the nature of your data. For example, if your table has a primary key but doesn't have a reasonable cursor field for incremental syncing (i.e. updated_at), CDC allows you to sync your table incrementally.
Standard
Zenskar offers incremental replication using a custom cursor available in your source tables (e.g. updated_at
). We generally recommend against this replication method, but it is well suited for the following cases:
- Your MySQL server does not expose the binlog.
- Your data set is small, and you just want snapshot of your table in the destination.
📖 Connect with SSL or SSH Tunneling
SSL Modes
Here is a breakdown of available SSL connection modes:
disable
to disable encrypted communication between Zenskar and the sourceallow
to enable encrypted communication only when required by the sourceprefer
to allow unencrypted communication only when the source doesn't support encryptionrequire
to always require encryption. Note: The connection will fail if the source doesn't support encryption.verify-ca
to always require encryption and verify that the source has a valid SSL certificateverify-full
to always require encryption and verify the identity of the source
Connect via SSH Tunnel
You can connect to a MySQL server via an SSH tunnel.
When using an SSH tunnel, you are configuring Zenskar to connect to an intermediate server (also called a bastion or a jump server) that has direct access to the database. Zenksar connects to the bastion and then asks the bastion to connect directly to the server.
To connect to a MySQL server via an SSH tunnel:
-
While setting up the MySQL source connector, from the SSH tunnel dropdown, select:
- SSH Key Authentication to use a private as your secret for establishing the SSH tunnel
- Password Authentication to use a password as your secret for establishing the SSH Tunnel
-
For SSH Tunnel Jump Server Host, enter the hostname or IP address for the intermediate (bastion) server that Zenskar will connect to.
-
For SSH Connection Port, enter the port on the bastion server. The default port for SSH connections is 22.
-
For SSH Login Username, enter the username to use when connecting to the bastion server.
Note
This is the operating system username and not the MySQL username.
- For authentication:
- If you selected SSH Key Authentication, set the SSH Private Key to the private key that you are using to create the SSH connection.
- If you selected Password Authentication, enter the password for the operating system user to connect to the bastion server.
Note
This is the operating system password and not the MySQL password.
Generating a private key for SSH Tunneling
The connector expects an RSA key in PEM format. To generate this key:
ssh-keygen -t rsa -m PEM -f myuser_rsa
This produces the private key in pem format, and the public key remains in the standard format used by the authorized_keys
file on your bastion host. The public key should be added to your bastion host to whichever user you want to use with Zenskar. The private key is provided via copy-and-paste to the Zenskar connector configuration screen, so it may log in to the bastion.
📖 Data-type mapping
MySQL data types are mapped to the following data types when synchronizing data. You can check test example values here. If you can't find the data type you are looking for, feel free to add a new test. If you do not see a type in this list, assume that it is coerced into a string. We are happy to take feedback on preferred mappings.
Any database or table encoding combination of charset and collation is supported. Charset setting however will not be carried over to destination and data will be encoded with whatever is configured by the destination. Please note that byte arrays are not yet supported.
MySQL data-type mapping
MySQL Type | Resulting Type | Notes |
---|---|---|
bit(1) | boolean | |
bit(>1) | base64 binary string | |
boolean | boolean | |
tinyint(1) | boolean | |
tinyint(>1) | number | |
tinyint(>=1) unsigned | number | |
smallint | number | |
mediumint | number | |
int | number | |
bigint | number | |
float | number | |
double | number | |
decimal | number | |
binary | string | |
blob | string | |
date | string | ISO 8601 date string. ZERO-DATE value will be converted to NULL. If column is mandatory, convert to EPOCH. |
datetime , timestamp | string | ISO 8601 datetime string. ZERO-DATE value will be converted to NULL. If column is mandatory, convert to EPOCH. |
time | string | ISO 8601 time string. Values are in range between 00:00:00 and 23:59:59. |
year | year string | Doc |
char , varchar with non-binary charset | string | |
tinyblob | base64 binary string | |
blob | base64 binary string | |
mediumblob | base64 binary string | |
longblob | base64 binary string | |
binary | base64 binary string | |
varbinary | base64 binary string | |
tinytext | string | |
text | string | |
mediumtext | string | |
longtext | string | |
json | serialized json string | E.g. {"a": 10, "b": 15} |
enum | string | |
set | string | E.g. blue,green,yellow |
geometry | base64 binary string |
Updated 3 days ago