Replicating Amazon RDS MySQL to Amazon Redshift w/ Attunity CloudBeam

Bytecode IO recently helped one of its clients transition from a data infrastructure that was having some serious growing pains, causing frequent crashes and an overall loss of data insight, to a warehoused structure that could handle multiple data sources and provide improved insight. The catch was, as is so often the case, that time was an issue and the prospect of manually replicating the client data was a daunting task. Our solution was, in part, to use Attunity CloudBeam to replicate the client’s data from Amazon RDS MySQL to Amazon Redshift which turned months of manual work into just a few hours. In the months since that project we’ve been asked about the procedure a few times and have created this how-to checklist.

RDS MySQL

RDS MySQL 5.6.13 or later is needed as it supports external sources reading the binary logs.

  • Enable a backup window on the RDS MySQL instance
  • Change the following in the DB Parameter Group:
    • binlog_format=row
    • binlog_checksum=NONE
  • Create a MySQL user that has select, replication slave, replication client permissions:
    • grant select, replication slave, replication client on *.* to 'cloudbeam'@'10.1.1.%' identified by password 'PASSWORD';
  • Make sure binary logs stick around to be read by Attunity if there is an issue:
    • call mysql.rds_set_configuration('binlog retention hours', 12);
  • Verify that the RDS MySQL Security Group allows traffic on port 3306 from the Attunity Servers below
  • Any problems with RDS MySQL, check this page for Using Replication to Export MySQL 5.6 Data

Amazon S3 Bucket

  • Create a S3 bucket
  • Create a S3 Access Key and Secret Key to be used by Attunity

Amazon Redshift

  • Create and size the Redshift Cluster
  • Verify that the Redshift Security Group allows traffic on port 5439 from the Attunity Servers here.
  • Create a Redshift database and schema for the data. Run the following from the Redshift (psql) command line:
    • create database development owner masteruser;
    • c development
    • create schema ods authorization masteruser;
    • dn+
  • Create a Redshift group and user for Attunity:
    • create group db_development_group;
    • grant all on database development to group db_development_group;
    • grant all on schema ods to group db_development_group;
    • create user cloudbeam_development password '***' in group db_development_group;
    • alter user cloudbeam_development set search_path to ods

Attunity CloudBeam for Redshift

  • Two Windows EC2 instances are needed, see two server configuration:
    • m3.large Windows instance to run the Attunity AMI (hourly or BYOL)
    • m3.2xlarge Windows instance to run the Attunity Replicate software
  • Follow the Quick Start Guide to configure the instances, target, source and create the Attunity Replicate tasks
  • Verify that the Attunity Security Group allows traffic on ports 5746 (Attunity CloudBeam), 3306 (MySQL) and 5439 (Amazon Redshift)

Attunity Replicate

  • Under “Task Settings”:
    • Metadata - Schema set
    • Metadata - Limited size LOB Mode: 63
    • Full Load Settings - Target table preparation: TRUNCATE before loading

Redshift Post Full Load

  • For each table grant access to other users in the db_development_group:
    • grant select on all tables in schema ods to group db_development_group;

Leave a Reply