MySQL CDC Connector Prerequisites

Prerequisites

  • Setup your database credentials
  • Enable binary logging on each MySQL slave
  • Make sure your database is accessible from your Datacoral VPC

1. Setup your database credentials

  • Use an existing database user OR create a new user for Datacoral. If creating a new user, please run the following statement on your MySQL database, replacing datacoral-password with a password of your choice.

Create user

CREATE USER datacoral@'%' IDENTIFIED BY 'datacoral-password';

Encrypt password (recommended)

All customer data is encrypted at rest as well as in motion using customer managed KMS keys. Datacoral’s cross-account role does not have decrypt permissions on the KMS keys. However, we do recommend using pre-encrypted credentials, as it allows for you to source control configuration files to automate installations for Data Ops and share configurations without compromising security. Please refer to the instructions on encrypting your password before adding them to encrypt your passwords.

Grant permissions

Grant the datacoral user (or the existing database user that Datacoral can use for connections) read-only access to all tables you need to replicate. Please change the command below appropriately to grant the user access to only the schemas that you wish to replicate.

If you created datacoral user, please run the statement below

GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO datacoral@'%';

If you are using an existing database user for Datacoral to use, please run the statement below after substituting 'dbuser' with the existing database user name

GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'dbuser'@'%';

2. Enable binary logging on each MySQL slave

Amazon RDS/Aurora MySQL instructions

  • If you are using Amazon RDS/Aurora and the default RDS DB parameter group, then create a new custom DB parameter group. In the custom parameter group change the values for the parameters below:
    • binlog_format to ROW
    • binlog_row_image parameter to FULL
    • binlog_checksum parameter to CRC32
  • Modify MySQL instances of both the master and replica to use this new parameter group, and check “apply immediately” box.
  • Reboot the instances to have the parameter take effect.

Generic MySQL instructions

You will need to enable the following on your MySQL slaves [We recommend that you setup a read replica and then perform the below actions on the read replica itself]: Note: You will need privileges sufficient to set Global system variables.

  • Ensure you have read replica setup
  • In the MySQL read replicas and master where CDC needs to be enabled, set binlog format to ROW
mysql> set GLOBAL binlog_format = 'ROW';
  • Set the binlog row image setting to FULL, this ensures all columns in the before and after image are logged
mysql> set GLOBAL binlog_row_image = 'FULL';
  • Set the binlog checksum setting to CRC32, this adds and extra level of safety to the logs and the replication process
mysql> set GLOBAL binlog_checksum = 'CRC32';
  • Setup retention period to 48 hours. This ensures that binlogs will be retained for 48 hours in the replica, which is then helpful to recover in case of any downtime of the replica. Ensure you have enough disk space for 48 hours worth of bin logs
mysql> call mysql.rds_set_configuration('binlog retention hours', 48);

Note: You may have to restart the slave after making the above changes

3. Make sure your database is accessible from your Datacoral VPC

Database accessible via the internet

First, you need to locate Datacoral's Elastic IP for your installation. Please follow the steps listed here to get the Elastic IP.

If your database is hosted on AWS and is not publicly accessible, need to authorize ingress for Datacoral's Elastic IP to the db security group. You can do this in the AWS RDS console or via AWS CLI:

aws ec2 authorize-security-group-ingress \
--group-id <security group associated with rds instance> \
--protocol tcp \
--port <db port> \
--cidr <elastic ip>

Here are the steps to add Datacoral's elastic IP to the db security group through the AWS console

  1. Click on this link to access AWS RDS home and select the appropriate AWS region and click on the source MySQL database
  2. You will land on the below page Scroll down to find the Security group rules and click on the Security Group
  3. On Security group page, go to Inbound rules tab and click on Edit inbound rules
  4. On the Edit inbound rule page, click on Add rule
  • Add the Type as MySQL or Custom TCP
  • Add the port as the port of your DB. The port number will be 3306 unless you have changed the default
  • Source as custom
  • Copy paste the elastic IP
    note

    A CIDR block is expected here, so please add '/32' at the end of Datacoral elastic IP before pasting. For example, if the elastic IP address is 35.165.137.20, then please paste 35.165.137.20/32

and click on Save rules

Database accessible via VPC Peering

If you don't want any traffic from your database to go over the internet, you can setup VPC Peering between the Datacoral Installation's VPC and your database's VPC. See VPC peering for how to set it up.

You also would have to modify the subnet group and security groups so that network connections are allowed to the Datacoral VPC.

  • For the RDS subnet group, the Network ACL should allow inbound and outbound connection to the Datacoral VPC.
  • For the RDS security group, we would need an inbound and outbound rule to/from the Datacoral VPC.

Other

If your database is hosted outside of AWS, please follow the appropriate networking setup to allow for ingress traffic for Datacoral to access your database. Please note that you will have to do this for both master as well as the replica.

Next steps