Set up the MySQL Connector

Step 1: Create a Dedicated Read-Only MySQL User

To securely replicate data from your MySQL database, it's recommended to create a dedicated read-only user. This ensures minimal permissions and better control over data access. Alternatively, you can use an existing MySQL user with the necessary permissions.

Run the following command to create a new user:

sqlCopy code
CREATE USER <user_name> IDENTIFIED BY 'your_password_here';

Next, grant this user read-only access to the relevant schemas and tables:

sqlCopy code
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <user_name>;

💡

Note: If you opt to use the STANDARD replication method (not recommended), the SELECT permission is sufficient.

Step 2: Enable Binary Logging on Your MySQL Server

To enable Change Data Capture (CDC) for real-time replication, you must enable binary logging (binlog) on your MySQL server. Most cloud providers, such as AWS and GCP, offer a one-click option to enable this.

If you’re self-managing your MySQL server, you will need to configure the MySQL configuration file with the following properties.

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 the server-id is already set to a non-zero value, you don't need to make any change. You can set the server-id to any value between 1 and 4294967295. For more information refer mysql doc

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 doc

binlog_format : The binlog_format must be set to ROW. For more information refer mysql doc

binlog_row_image : The binlog_row_image must be set to FULL. It determines how row images are written to the binary log. For more information refer mysql doc

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: Add Integration to Revos

Now that your MySQL server is configured, you can set up the integration in Revos.

💡

All communication from RevOS platform is done from following IP address:

34.141.79.174

Please make sure that if you open any ways for RevOS to access your data you whitelist communication from this IP address

Basic Configuration: Enter the hostname, port number, and database name for your MySQL instance.

Authentication: Provide the username and password you created in Step 1.

Configure Replication Method

Update Method: Select the desired replication method. We recommend using Read Changes using Binary Log (CDC) for efficient, real-time replication.

Initial Sync Delay: Set the initial wait time (in seconds) before checking for new data. The default is 300 seconds, but you can set any value between 120 and 1200 seconds.

MySQL Timezone: Enter the timezone for your MySQL server. This is required only if your server’s timezone doesn’t adhere to the IANNA standard.

Error Handling: Choose how Airbyte should handle stale or invalid cursor values in the Write-Ahead Log (WAL). You can either fail the sync, requiring manual intervention, or allow Airbyte to automatically re-sync data, which may result in higher cloud costs and possible data loss.

Initial Load Timeout: Configure the maximum time (in hours) for the initial data load to run before transitioning to CDC.

Step 4: Connect via SSH Tunnel (Optional)

If direct access to your MySQL server is restricted, you can connect via an SSH tunnel. This allows Airbyte to securely connect through an intermediate server (bastion) that has access to the MySQL database.

Select SSH Tunnel Option: Choose the type of authentication for the SSH tunnel:

SSH Key Authentication: Uses a private key to establish the tunnel.

Password Authentication: Uses a password to establish the tunnel.

Configure SSH Tunnel Fields:

SSH Tunnel Jump Server Host: Enter the hostname or IP address of the intermediate (bastion) server that Airbyte will use to connect.

SSH Connection Port: Enter the SSH port of the bastion server (default is 22).

SSH Login Username: Provide the username for connecting to the bastion server (this is the OS user, not the MySQL user).

Authentication Method:

If using SSH Key Authentication, provide the private key for SSH access.

If using Password Authentication, enter the password for the operating system user (not the MySQL password).

Generating a Private Key for SSH Tunneling

If you opt for SSH Key Authentication, you’ll need an RSA private key in PEM format. You can generate this key using the following command:

bashCopy code
ssh-keygen -t rsa -m PEM -f myuser_rsa

This command will generate a private key in PEM format and a public key. The public key should be added to the bastion host under the user you want Airbyte to use for SSH. The private key is then pasted into the Airbyte connector configuration to enable login via SSH.